Migrate database engine from MySQL to SQLite - Revision history

Planetoid: /* Steps of migration database engine from MySQL to SQLite */

2019-11-12T23:42:12Z

Steps of migration database engine from MySQL to SQLite

← Older revision		Revision as of 07:42, 13 November 2019
Line 4:		Line 4:

	# Migrate database data from MySQL to SQLite using the [[SQLite management software]]		# Migrate database data from MySQL to SQLite using the [[SQLite management software]]
	# Modify the scripts of database ~~access~~		# Modify the scripts of database connection and SQL query syntax because some MySQL functions are not compatible in SQLite
	# Testing the scripts		# Testing the scripts

Planetoid at 02:22, 12 November 2019

2019-11-12T02:22:29Z

← Older revision		Revision as of 10:22, 12 November 2019
Line 23:		Line 23:
	</pre>		</pre>

	* Other functions are not supported such as: SET		* Other functions are not supported such as: SET, Truncate<ref>[https://www.runoob.com/sqlite/sqlite-truncate-table.html SQLite Truncate Table \| 菜鸟教程]</ref>, INGORE

	== References ==		== References ==

Planetoid at 06:57, 9 November 2019

2019-11-09T06:57:15Z

← Older revision		Revision as of 14:57, 9 November 2019
Line 3:		Line 3:
	== Steps of migration database engine from MySQL to SQLite ==		== Steps of migration database engine from MySQL to SQLite ==

	# Migrate database data from MySQL to SQLite using the SQLite management software		# Migrate database data from MySQL to SQLite using the [[SQLite management software]]
	# Modify the scripts of database access		# Modify the scripts of database access
	# Testing the scripts		# Testing the scripts
Line 24:		Line 24:

	* Other functions are not supported such as: SET		* Other functions are not supported such as: SET

	~~== SQLite management software ==~~
	~~Comparison of SQLite management softwares~~

	~~[https://sqlitebrowser.org/ DB Browser for SQLite] v. 3.11.2~~
	* Import: (1) database from SQL file (2) table from CSV file
	* Export: (1) database to SQL file (2) table(s) as CSV file (3) table(s) as JSON file
	* Export the query result: ok. Steps (1) execute SQL (2) click "Save the results" as CSV file

	~~''$'' [https://www.navicat.com/cht/products/navicat-premium Navicat Premium]~~
	* Import: (1) database from SQL file (2) table from CSV/Excel file
	* Export: (1) database to SQL file (2) table as CSV/Excel file
	* Export the query result: ok

	== References ==		== References ==

Planetoid at 06:41, 9 November 2019

2019-11-09T06:41:31Z

@@ Line 1: / Line 1: @@
-== Notes ==
+Steps of migration database engine from MySQL to SQLite
 * Remove database name from SQL query: SQLite will consider the database name as table name
 * In MySQL, string are able to quoted with single quote symbol {{kbd | key=<nowiki>'</nowiki>}} or double quote symbol {{kbd | key=<nowiki>"</nowiki>}}. In SQLite the string quoted with double quote symbol will be considered as '''column name'''! It will cause the problem: "no such column: xxx"

Planetoid: /* SQLite management software */

2019-11-07T10:00:40Z

SQLite management software

← Older revision		Revision as of 18:00, 7 November 2019
Line 27:		Line 27:
	''$'' [https://www.navicat.com/cht/products/navicat-premium Navicat Premium]		''$'' [https://www.navicat.com/cht/products/navicat-premium Navicat Premium]
	* Import: (1) database from SQL file (2) table from CSV/Excel file		* Import: (1) database from SQL file (2) table from CSV/Excel file
	* Export: (1) database to SQL file (2) table~~(s)~~ as CSV/Excel file		* Export: (1) database to SQL file (2) table as CSV/Excel file
	* Export the query result: ok		* Export the query result: ok


	== References ==		== References ==

Planetoid at 09:51, 7 November 2019

2019-11-07T09:51:19Z

@@ Line 16: / Line 16: @@
 * Other functions are not supported such as: SET
 == References ==
@@ Line 23: / Line 37: @@
 [[Category:MySQL]]
 [[Category:SQLite]]

Planetoid at 10:53, 6 November 2019

2019-11-06T10:53:07Z

← Older revision		Revision as of 18:53, 6 November 2019
Line 15:		Line 15:
	</pre>		</pre>

	* Other functions not supported: SET		* Other functions are not supported such as: SET

	== References ==		== References ==

Planetoid at 08:41, 4 November 2019

2019-11-04T08:41:52Z

← Older revision		Revision as of 16:41, 4 November 2019
Line 4:		Line 4:
	* Replace the following functions		* Replace the following functions
	** Replace MySQL [https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char-length CHAR_LENGTH] function with SQLite [https://www.sqlitetutorial.net/sqlite-functions/sqlite-length/#targetText=SQLite%20Length,returns%20the%20number%20of%20bytes. Length] function<ref>[https://github.com/owncloudarchive/mozilla_sync/issues/47 sqlite does not have CHAR_LENGTH function · Issue #47 · owncloudarchive/mozilla_sync]</ref>		** Replace MySQL [https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char-length CHAR_LENGTH] function with SQLite [https://www.sqlitetutorial.net/sqlite-functions/sqlite-length/#targetText=SQLite%20Length,returns%20the%20number%20of%20bytes. Length] function<ref>[https://github.com/owncloudarchive/mozilla_sync/issues/47 sqlite does not have CHAR_LENGTH function · Issue #47 · owncloudarchive/mozilla_sync]</ref>
	** Replace MySQL {{kbd \| key=IF}} function with SQLite {{kbd \| key=CASE}} function		** Replace MySQL {{kbd \| key=IF}} function with SQLite {{kbd \| key=CASE}} function<ref>[https://www.sqlitetutorial.net/sqlite-case/ SQLite CASE Expression: Simple CASE & Search CASE]</ref>
	** Replace MySQL {{kbd \| key=LOCATE}} function<ref>[https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate MySQL :: MySQL 8.0 Reference Manual :: 12.5 String Functions and Operators]</ref> with SQLite INSTR {{kbd \| key=CASE}} function<ref>[https://www.sqlitetutorial.net/sqlite-functions/sqlite-instr/ SQLite INSTR]</ref>		** Replace MySQL {{kbd \| key=LOCATE}} function<ref>[https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_locate MySQL :: MySQL 8.0 Reference Manual :: 12.5 String Functions and Operators]</ref> with SQLite INSTR {{kbd \| key=CASE}} function<ref>[https://www.sqlitetutorial.net/sqlite-functions/sqlite-instr/ SQLite INSTR]</ref>

Planetoid: /* Notes */

2019-11-02T07:38:21Z

Notes

← Older revision		Revision as of 15:38, 2 November 2019
Line 14:		Line 14:
	INSTR(string, substring);		INSTR(string, substring);
	</pre>		</pre>

			* Other functions not supported: SET

	== References ==		== References ==

Planetoid at 06:13, 1 November 2019

2019-11-01T06:13:39Z

← Older revision		Revision as of 14:13, 1 November 2019
Line 1:		Line 1:
	== Notes ==		== Notes ==
	* Remove database name from SQL query: SQLite will consider the database name as table name		* Remove database name from SQL query: SQLite will consider the database name as table name
	* In MySQL, string are able to quoted with single quote symbol or double quote symbol. In SQLite the string quoted with double quote symbol will be considered as '''column name'''! It will cause the problem: "no such column: xxx"		* In MySQL, string are able to quoted with single quote symbol {{kbd \| key=<nowiki>'</nowiki>}} or double quote symbol {{kbd \| key=<nowiki>"</nowiki>}}. In SQLite the string quoted with double quote symbol will be considered as '''column name'''! It will cause the problem: "no such column: xxx"
	* Replace the following functions		* Replace the following functions
	** Replace MySQL [https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char-length CHAR_LENGTH] function with SQLite [https://www.sqlitetutorial.net/sqlite-functions/sqlite-length/#targetText=SQLite%20Length,returns%20the%20number%20of%20bytes. Length] function<ref>[https://github.com/owncloudarchive/mozilla_sync/issues/47 sqlite does not have CHAR_LENGTH function · Issue #47 · owncloudarchive/mozilla_sync]</ref>		** Replace MySQL [https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char-length CHAR_LENGTH] function with SQLite [https://www.sqlitetutorial.net/sqlite-functions/sqlite-length/#targetText=SQLite%20Length,returns%20the%20number%20of%20bytes. Length] function<ref>[https://github.com/owncloudarchive/mozilla_sync/issues/47 sqlite does not have CHAR_LENGTH function · Issue #47 · owncloudarchive/mozilla_sync]</ref>