Re: [sqlite] SQLite shell dot commands
Please, I don't mean this to be offensive. I'm not. It was suggested that the syntax [Ben's table] is cumbersome. What is really cumbersome, in my opinion, is the table name itself. The table name includes an white space (space) and a delimiting character (apostrophe.) The simple table name has lots of junk in it that will throw a left hook to many language parsers. To simplify everything, just name the table BensTable. Camel case the words, drop the spaces. Everything is still very readable and makes sense. dvn On Thu, Feb 23, 2012 at 4:17 AM, Benoit Mortgat mort...@gmail.com wrote: I have a database with a table name containing a quote and a space. Let's say it's called “Ben's table” I have created it with: CREATE TABLE Ben's table ([column_spec]); I tried the following in the SQLite shell: .import 'file_name.txt' Ben's table But that does not work. So I had to dig a bit into shell.c to understand, and I found the following: * inside the do_meta_command function, the arguments to dot-commands are tokenized with the following rules: * The tokenizer skips all whitespace character when finding the start for the next token. * When ' or is found where a token should start, the token will be the portion of text between that delimiter and its next occurrence. There is no possibility to escape the delimiter. The two delimiters are discarded. * If any other character is found where a token should start, the token ends at whitespace. * Backslashes get special processing except for tokens that were delimited by the single quote. * inside the same do_meta_command method, when processing the import command, the name of the table is appended to some queries this way: sqlite3_snprintf(nByte+20, zSql, INSERT INTO %s VALUES(?, zTable); So, my .import command was tokenized that way: Token 1: file_name.txt Token 2: Ben's table --- note: double quotes gone. And the SQL query that was forged was incorrect: INSERT INTO Ben's table VALUES(?... The only way I found in order to have my import succeed was: .import 'file_name.txt' [Ben's table] which I find cumbersome. Are there any plans to improve the tokenizer to enable escaping the delimiter (doubling the single quote or backslash-escaping the double quote), and to escape the table name in the dot-commands? Potential problem: how to use .backup or .restore with a file name that contains both ' and space? Thanks. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite shell dot commands
On Fri, Feb 24, 2012 at 13:28, Don V Nielsen donvniel...@gmail.com wrote: Please, I don't mean this to be offensive. I'm not. Thanks for the answer, I did not feel offended. It was suggested that the syntax [Ben's table] is cumbersome. What is really cumbersome, in my opinion, is the table name itself. The table name includes an white space (space) and a delimiting character (apostrophe.) The simple table name has lots of junk in it that will throw a left hook to many language parsers. To simplify everything, just name the table BensTable. Camel case the words, drop the spaces. Everything is still very readable and makes sense. I fully agree that it's not really advisable to name a table like this. Still, since SQLite supports non-\w+ table names, I felt that the SQLite shell should also support them in meta commands. If you re-read the original question, there is still the problem when it's file names that contain spaces, apostrophes or other challenging characters (commands affected could be: .import, .backup, .restore, .load, .log, .output). Those cases are not that uncommon. If it's not planned to rewrite the argument tokenizer for meta-commands could it be possible at least to specify in the output of .help how those arguments are tokenized? (Note: another workaround to import into Ben's table is using the octal digits escaping with backslashes:) .import my_file \042Ben's\040table\042 -- Benoit Mortgat 20, avenue Marcel Paul 69200 Vénissieux, France +33 6 17 15 41 58 +33 4 27 11 61 23 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite shell dot commands
On 24 Feb 2012, at 1:29pm, Benoit Mortgat mort...@gmail.com wrote: I fully agree that it's not really advisable to name a table like this. Still, since SQLite supports non-\w+ table names, Is this documented somewhere ? I can't find any documentation about what SQLite considers to be an acceptable table name. I felt that the SQLite shell should also support them in meta commands. If you re-read the original question, there is still the problem when it's file names that contain spaces, apostrophes or other challenging characters (commands affected could be: .import, .backup, .restore, .load, .log, .output). Those cases are not that uncommon. If it's not planned to rewrite the argument tokenizer for meta-commands could it be possible at least to specify in the output of .help how those arguments are tokenized? It is faster, simpler, and would introduce far fewer ambiguities and opportunities for bugs, simply to remove the ability to create tables with whacky names. There are no real restrictions on table names in the SQL specs. You can even theoretically create a table name with a '' or a ']' character in, if you can make the parser accepting one. So every implementation of SQL has its own peculiarities. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite shell dot commands
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 24/02/12 07:22, Simon Slavin wrote: I can't find any documentation about what SQLite considers to be an acceptable table name. Providing you use quotation, anything is acceptable as a table name including zero length strings. create table create table select(create)( [select select]); A table named create table select(create) with a zero length column name of type select select. Full unicode works too. About the only thing you can't do is use a name that requires multiple kinds of quoting (double quotes and []) simultaneously. The issue that OP has is that the shell strips off one level of quoting under some circumstances (dot commands), so there are situations where you effectively have to quote names twice. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk9H7F8ACgkQmOOfHg372QSGNACgjYTKtmGwmhaRD+oZvvkabLaf 4C4Anj9ArJa8x+RPMi4aXUeUKeSEEpyo =kjFY -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite shell dot commands
On Fri, Feb 24, 2012 at 16:22, Simon Slavin slav...@bigfraud.org wrote: It is faster, simpler, and would introduce far fewer ambiguities and opportunities for bugs, simply to remove the ability to create tables with whacky names. There are no real restrictions on table names in the SQL specs. You can even theoretically create a table name with a '' or a ']' character in, if you can make the parser accepting one. So every implementation of SQL has its own peculiarities. Sorry but the question is not about what an acceptable table name is. It is about how dot-commands in the SQLite shell could accept arguments containing special characters (I recall that those arguments can be table names, file names or various others: boolean, fixed hardcoded values: `.help` in the SQLite shell lists them, as well as reading shell.c.) Please do not convert my thread to a debate about what characters table names should include. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite shell dot commands
I have a database with a table name containing a quote and a space. Let's say it's called “Ben's table” I have created it with: CREATE TABLE Ben's table ([column_spec]); I tried the following in the SQLite shell: .import 'file_name.txt' Ben's table But that does not work. So I had to dig a bit into shell.c to understand, and I found the following: * inside the do_meta_command function, the arguments to dot-commands are tokenized with the following rules: * The tokenizer skips all whitespace character when finding the start for the next token. * When ' or is found where a token should start, the token will be the portion of text between that delimiter and its next occurrence. There is no possibility to escape the delimiter. The two delimiters are discarded. * If any other character is found where a token should start, the token ends at whitespace. * Backslashes get special processing except for tokens that were delimited by the single quote. * inside the same do_meta_command method, when processing the import command, the name of the table is appended to some queries this way: sqlite3_snprintf(nByte+20, zSql, INSERT INTO %s VALUES(?, zTable); So, my .import command was tokenized that way: Token 1: file_name.txt Token 2: Ben's table --- note: double quotes gone. And the SQL query that was forged was incorrect: INSERT INTO Ben's table VALUES(?... The only way I found in order to have my import succeed was: .import 'file_name.txt' [Ben's table] which I find cumbersome. Are there any plans to improve the tokenizer to enable escaping the delimiter (doubling the single quote or backslash-escaping the double quote), and to escape the table name in the dot-commands? Potential problem: how to use .backup or .restore with a file name that contains both ' and space? Thanks. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
