Repository navigation
How to force export function use backtick (`) in create table syntax? #629
Description
Hi,
I need export my SQLite database to sql file with MySQL compatibility, hence I want to use always backtick (`) for CREATE TABLE syntax. For example:
CREATE TABLE `Table1` (`ID` INTEGER,`Name` TEXT);
however, in the exported sql, the create statement contains backtick (`) as well as double quote (") and single quote (') , which seems to be a random behavior.
for example I have a table with schema:
CREATE TABLE `Source` (`Source_ID` INTEGER, `Name_Source` TEXT);
if I export this table, it become to:
CREATE TABLE "Source" (`Source_ID` INTEGER, `Name_Source` TEXT)
or
CREATE TABLE 'Source' ('Source_ID' INTEGER, 'Name_Source' TEXT);
And it's also unpredictable in the CREATE INDEX statements:
in this case I want no quote around the columns like:
CREATE INDEX Source_Source_ID ON Source (Source_ID);
but the exported sql is:
CREATE INDEX 'Source_Source_ID' ON 'Source' ('Source_ID' );
So when I import the sql to MYSQL database, I got syntax errors
Activity
justinclift commentedon Jun 21, 2016
Interesting. This shouldn't be too hard to implement. 😄
lvbeck commentedon Jun 21, 2016
@justinclift it would be great to have user defined quote character for export
MKleusberg commentedon Jan 27, 2017
This should really be fixed I think 😃 @lvbeck Can you test the latest version (3.9.1 or the nightly builds, whatever you prefer)? I'm pretty sure we quote all names using the
`quotes.MKleusberg commentedon Jan 27, 2017
Oh no I'm sorry, I was wrong. For the SQL dump we take the CREATE statement as is. This means it's quoted in the SQL export just like it's quoted in the original database. But I can fix that 😃
Pretty-print CREATE statements in SQL export
MKleusberg commentedon Jan 27, 2017
@lvbeck Sorry for the confusion 😃 If you're still interested in this, can you test tomorrow's nightly build and see if it's improving the situation for you? For triggers and views we don't have a chance to do this (yet) but tables and indices should be much better now.
lvbeck commentedon Jan 27, 2017
@MKleusberg it would be great if you can implement this feature(tables and indices are enough for me), however I am not able to test the nightly build this week, I will check later ASAP, thank you.
MKleusberg commentedon Jan 27, 2017
No worries 😃 Just test it whenever you have the time for it. Thank you for your effort! 😃
justinclift commentedon Mar 3, 2017
@lvbeck ping?
lvbeck commentedon Mar 3, 2017
@justinclift @MKleusberg thanks, it's now working! I will close the issue
justinclift commentedon Mar 3, 2017
@lvbeck Excellent, thanks for checking and letting us know. 😄
lvbeck commentedon May 8, 2017
@MKleusberg just another tiny feature request, could you please add:
before CREATE TABLE syntax?
This would be very helpful if the table existed in database. Thank you!
Some improvements to the Export SQL dialog
MKleusberg commentedon May 12, 2017
@lvbeck Sure, no problem 😃 I've added an additional option to the Export SQL dialog to enable this feature. So you'll need to set it up like this:
Can you download the next nightly build (tomorrow) and check if it's working for you as expected?
lvbeck commentedon May 15, 2017
@MKleusberg I tested nightly build "DB Browser for SQLite-2017-05-15-win64.exe", it work exactly as expected, thank you for the amazing work!
justinclift commentedon May 15, 2017
Awesome. 😄