Details for the issue

I was searching the web for Sql standards with respect to quoting identifiers. Come across this summary, read the relevant page on sqlite.org and started to do some tests on sqlitebrowser.

SQL Standard and SQLite are on par wrt. quoting identifiers. sqlite.org states:

• 'keyword' : A keyword in single quotes is a string literal.
• "keyword": A keyword in double-quotes is an identifier.
• [keyword]: A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
• `keyword`: A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

TL;DR: Results of tests:

• sqlitebrowser does not adhere to SQLite documentation.
• sqlitebrowser does not respect explicitely added quotes by the user:
  • it may add extra quotes
  • it may remove/replace quotes.

This happens inconsistently between tables created and modified using Create Table resp. Modify Table, manually created tables and export of schema.

Suggestion:

• sqlitebrowser should not add grave accents automatically.
  It's not according SQL Standards and the SQLite documentation.
• sqlitebrowser should not touch quotes explicitely added or not added by the user.
  The explicit use or non use of quotes is a choice of the user.

And here are the results: (yes it is a long read):

Creating tables using Create Table wizard

1. No Quotes
   Copy Create statement: CREATE TABLE `noQuotes` ( `noQuotes` INTEGER )
2. With Quotes
   Copy Create statement: CREATE TABLE `"withQuotes"` ( `"withQuotes"` INTEGER )
3. With Single Quotes
   Copy Create statement: CREATE TABLE `'withSingleQuotes'` ( `'withSingleQuotes'` INTEGER )
4. With Grave Quotes
   Copy Create statement: CREATE TABLE ```withGraveQuotes``` ( ```withGraveQuotes``` INTEGER )

Note: Every identifier is surrounded with extra grave quotes.

Export tables

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `noQuotes` ( `noQuotes` INTEGER );
CREATE TABLE IF NOT EXISTS `"withQuotes"` ( `"withQuotes"` INTEGER );
CREATE TABLE IF NOT EXISTS `'withSingleQuotes'` ( `'withSingleQuotes'` INTEGER );
CREATE TABLE IF NOT EXISTS ```withGraveQuotes``` ( ```withGraveQuotes``` INTEGER );
COMMIT;

Note: Same quotations as above.

Modify tables using Modify Table wizard

1. No Quotes
   Copy Create statement: CREATE TABLE "modifiedNoQuotes" ( `modifiedNoQuotes` INTEGER )
2. With Quotes
   Copy Create statement: CREATE TABLE """modifiedWithQuotes""" ( `"modifiedWithQuotes"` INTEGER )
3. With Single Quotes
   Copy Create statement: CREATE TABLE "'modifiedWithSingleQuotes'" ( `'modifiedWithSingleQuotes'` INTEGER )
4. With Grave Quotes
   Copy Create statement: CREATE TABLE "`modifiedWithGraveQuotes`" ( ```modifiedWithGraveQuotes``` INTEGER )

Note: Every table identifier is surrounded with extra double quotes, while columns are surrounded with extra grave quotes.

Export modified tables

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `noQuotes` ( `noQuotes` INTEGER );
CREATE TABLE IF NOT EXISTS `"withQuotes"` ( `"withQuotes"` INTEGER );
CREATE TABLE IF NOT EXISTS `'withSingleQuotes'` ( `'withSingleQuotes'` INTEGER );
CREATE TABLE IF NOT EXISTS ```withGraveQuotes``` ( ```withGraveQuotes``` INTEGER );
COMMIT;

Note: Same as previous export

Created tables manualy

1. No Quotes
   Copy Create statement: CREATE TABLE noQuotes ( noQuotes INTEGER )
2. With Quotes
   Copy Create statement: CREATE TABLE "withQuotes" ( "withQuotes" INTEGER )
3. With Single Quotes
   Copy Create statement: CREATE TABLE 'withSingleQuotes' ( 'withSingleQuotes' INTEGER )
4. With Grave Quotes
   Copy Create statement: CREATE TABLE `withGraveQuotes` ( `withGraveQuotes` INTEGER )

Note: This is how it should be. No change of user code.

Export manually created tables

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `noQuotes` ( `noQuotes` INTEGER );
CREATE TABLE IF NOT EXISTS `"withQuotes"` ( `"withQuotes"` INTEGER );
CREATE TABLE IF NOT EXISTS `'withSingleQuotes'` ( `'withSingleQuotes'` INTEGER );
CREATE TABLE IF NOT EXISTS ```withGraveQuotes``` ( ```withGraveQuotes``` INTEGER );
COMMIT;

Note: Same as previous export.

Modify manually created tables with Modify Table

1. No Quotes
   Copy Create statement: CREATE TABLE "modifiedNoQuotes" ( `modifiedNoQuotes` INTEGER )
2. With Quotes
   Copy Create statement: CREATE TABLE "modifiedWithQuotes" ( `modifiedWithQuotes` INTEGER )
3. With Single Quotes
   Copy Create statement: CREATE TABLE "modifiedWithSingleQuotes" ( `modifiedWithSingleQuotes` INTEGER )
4. With Grave Quotes
   Copy Create statement: CREATE TABLE "modifiedWithGraveQuotes" ( `modifiedWithGraveQuotes` INTEGER )

Note: All quotes removed. Every table identifier is surrounded with double quotes, while columns are surrounded with grave quotes.

Export modified manually created tables

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `noQuotes` ( `noQuotes` INTEGER );
CREATE TABLE IF NOT EXISTS `"withQuotes"` ( `"withQuotes"` INTEGER );
CREATE TABLE IF NOT EXISTS `'withSingleQuotes'` ( `'withSingleQuotes'` INTEGER );
CREATE TABLE IF NOT EXISTS ```withGraveQuotes``` ( ```withGraveQuotes``` INTEGER );
COMMIT;

Note: Same as previous export

Useful extra information

I'm opening this issue because:

• DB4S is crashing
• DB4S has a bug
• DB4S needs a feature
• DB4S has another problem

I'm using DB4S on:

• Windows: ( version: 10 )
• Linux: ( distro: ___ )
• Mac OS: ( version: ___ )
• Other: ___

I'm using DB4S version:

• 3.10.1
• 3.10.0
• 3.9.1
• Other: 3.10.99 (Dec 29 2017)

I have also:

• Tried out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• Searched for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20