Views not being created

[dbudwin]

This script seems to work very well for my needs except for the fact that it isn't generating views. My database only has one view, and it's part of my mysqldump SQL:

When I inspect my SQLite file using "DB Browser for SQLite," it says there are 0 views.

Following is my SQL:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DeviceTypes` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Type` varchar(50) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Type` (`Type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `DeviceTypes` VALUES (1,'RF');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Devices` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Description` varchar(255) NOT NULL,
  `Type` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID` (`ID`),
  KEY `ID_2` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `RFDevice` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DeviceID` int(11) NOT NULL,
  `OnCode` int(11) NOT NULL,
  `OffCode` int(11) NOT NULL,
  `PulseLength` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `RFDevice_fk0` (`DeviceID`),
  CONSTRAINT `RFDevice_fk0` FOREIGN KEY (`DeviceID`) REFERENCES `Devices` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `UserDevices` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `UserID` int(11) NOT NULL,
  `DeviceID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `UserDevices_fk0` (`UserID`),
  KEY `UserDevices_fk1` (`DeviceID`),
  CONSTRAINT `UserDevices_fk0` FOREIGN KEY (`UserID`) REFERENCES `Users` (`ID`),
  CONSTRAINT `UserDevices_fk1` FOREIGN KEY (`DeviceID`) REFERENCES `Devices` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `UserDevicesView` AS SELECT 
 1 AS `UserDevices_UserID`,
 1 AS `DeviceID`,
 1 AS `Users_Name`,
 1 AS `Email`,
 1 AS `Users_UserID`,
 1 AS `Devices_Name`,
 1 AS `Description`,
 1 AS `Devices_Type`,
 1 AS `DeviceTypes_Type`*/;
SET character_set_client = @saved_cs_client;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `UserID` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UserID` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `Users` VALUES (1,'Abc','[email protected]','amzn1.account.a8b7c7s888aeff7bc7s7901a1');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sessions` (
  `session_id` varchar(40) NOT NULL DEFAULT '',
  `data` text,
  `ip` varchar(40) DEFAULT NULL,
  `agent` varchar(255) DEFAULT NULL,
  `stamp` int(11) DEFAULT NULL,
  PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50001 DROP VIEW IF EXISTS `UserDevicesView`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_unicode_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `UserDevicesView` AS select `UserDevices`.`UserID` AS `UserDevices_UserID`,`UserDevices`.`DeviceID` AS `DeviceID`,`Users`.`Name` AS `Users_Name`,`Users`.`Email` AS `Email`,`Users`.`UserID` AS `Users_UserID`,`Devices`.`Name` AS `Devices_Name`,`Devices`.`Description` AS `Description`,`Devices`.`Type` AS `Devices_Type`,`DeviceTypes`.`Type` AS `DeviceTypes_Type` from (((`UserDevices` join `Users` on((`UserDevices`.`UserID` = `Users`.`ID`))) join `Devices` on((`UserDevices`.`DeviceID` = `Devices`.`ID`))) join `DeviceTypes` on((`Devices`.`Type` = `DeviceTypes`.`ID`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

When I run this script, the only output I get is "memory"

$ ./mysql2sqlite schema.sql | sqlite3 mysqlite.db
memory

[dumblob]

Hi @dbudwin, thank you for bringing this up.

You're right, that mysql2sqlite does not currently support views. It's though planned (see line 31) to easily add support for them as their syntax (at least the beginning) should be the same as for tables. I'll try to get to it in the next 2 weeks and test it on your example.

[dbudwin]

Thanks @dumblob! I did see the mention of views in the script, but when I looked more closely I did notice the code pertained to tables. I'm looking forward to this feature, let me know how I can help and I'll gladly test anything for you as well!

[dbudwin]

Hello @dumblob, I was wondering if you were still planning on implementing this? It still adds value to my project!

[dumblob]

Hi @dbudwin, I'm sorry for not pushing this forward. I have a proof of concept implementation. I'm also preparing some unit tests and way more other patches from regarding other issues. I'm though lacking enough time.

In case you'd like to help, please post here short (max. 10 lines) correct MySQL statements covering edge cases of views syntax usage.

[dbudwin]

No worries, glad to hear it's still on your radar. The only case of a view that I have is the one in my original description of the problem which I think is a pretty standard view.

[dumblob]

I did not forget, but I'm still busy... getting slowly towards this. Stay tuned!

[peterdolan]

Hello @dumblob ! This is a fantastic script, thanks for working on it. Just wondering if there was any update here?

[dumblob]

@peterdolan I'm sorry to say that, but it seems it won't happen soon as I'm still quite busy. Feel free to propose some code and I'll try to comment on that (it's way faster than writing it myself and testing it thoroughly).

If this missing feature prevents you from using this script completely, please tell me and I'll try to accommodate my schedule.

[peterdolan]

@dumblob not a problem! I managed a workaround -- I might take a stab at this at some point, but my awk skills are fairly limited. Thanks for maintaining!