Re: [sqlite] SQLite .dump
Simon Slavin wrote: > I'm sorry, but I don't see a question in your post. As intended. It was just a statement based on observation, with regard to SQLite .dump, my GUI dump, and other database dump outputs. MySQL also uses a short version without specifying column names, but does quotes identifiers. Warren Young wrote: > and using the database's > identifier quoting character. > It does that at need already: > sqlite> create table "x y" ("a b" INTEGER); > sqlite> .dump > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER); > COMMIT; Seems only when the initial dll specified. sqlite> create table x (a INTEGER); sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x (a INTEGER); COMMIT; There is no intention to indicate a bug, or other aspect about a deficiency in SQLite .dump. The reason my tool provides columns is because the dump is made to allow users to selectively save data from only the specified columns. The reason I always quote identifiers, is because people do stuff like this for names, "keY_cOlumn2". If that was not quoted in dml then it might be interpreted as KEY_COLUMN2 for example in some databases, and then throw an error. NO SUCH COLUMN. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite .dump
On Jul 13, 2018, at 10:15 AM, dmp wrote: > > Seems .dump uses a short output of skipping the column names. To call that a problem requires that you justify why you’d need the column names to be specified in the INSERT statements. If you take the .dump file as-given and just run it, the INSERT statements are fine as-is because the .dump file has a CREATE TABLE immediately above. There can be no mismatch if you do not change the .dump file. If you’re intending to edit the CREATE TABLE statements out of the .dump file and run those INSERT statements on a SQLite DB with a different schema, then yes, you’d need the column names to be explicitly provided, but that seems like quite a special case. And of course, you could just edit the INSERT statements at the same time. > Always specifying the column names list That’s good practice whenever there can be significant drift between the code that does the CREATE TABLE call and the code that does the INSERT. For example, if your application’s DB is initialized with a script and then potentially years of software updates do ALTER TABLE calls on it on upgrades, then yes, it’s important to qualify the column names in your INSERT statements. > and using the database's > identifier quoting character. It does that at need already: sqlite> create table "x y" ("a b" INTEGER); sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER); COMMIT; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite .dump
I'm sorry, but I don't see a question in your post. Are you suggesting that the SQLite command-line tool has a bug ? Are you suggesting that Ajqvue has a bug ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order
On 17 Apr 2014, at 3:42pm, Igor Tandetnikwrote: > If there's a bug anywhere in this, I'd say it's the fact that SQLite allowed > "DROP VIEW v2" statement to proceed. Agreed. Is there a similar bug if you try to drop a table that a FOREIGN KEY depends on ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order
On 4/17/2014 12:43 AM, Tyumentsev Alexander wrote: sqlite in some cases dumps views in wrong order. Interesting situation. My take on it would be that dump is a simple little tool designed to help move a database file from one place to another. In tricky situations, some editing of its output may be needed. Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order
On 4/17/2014 3:43 AM, Tyumentsev Alexander wrote: CREATE VIEW v2 as select id1 from test where id1; CREATE VIEW v1 as select t1.id2 from test as t1 LEFT JOIN v2 ON t1.id2=v2.id1; DROP VIEW v2; Is it the user responsibility to follow all dependencies and recreate "VIEW" tree ? I'd say it's the user's responsibility to not drop a view that is being referred to elsewhere. If there's a bug anywhere in this, I'd say it's the fact that SQLite allowed "DROP VIEW v2" statement to proceed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite dump makes wrong "CREATE VIEW" order
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/04/14 00:43, Tyumentsev Alexander wrote: > Is it the user responsibility to follow all dependencies and recreate > "VIEW" tree ? The dumping is happening in the order that the views were created. This approach generally works, but fails in your example where you created another view of the same name. (There are some other edge cases where you could create circular links between views.) I can't see any way of resolving your issue since there is no general SQL parser which is what would have to be run to work out dependency order. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlNP58cACgkQmOOfHg372QT0RwCg5gXqYYmQ0Ws1LqJaFBhdpvwJ dLAAn2+8hvf+nl9jTR45Z2TYQsT6mAlB =APvL -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite .dump does not save PRAGMA user_version
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Schubert wrote: > When creating a dump with sqlite .dump, it will not save the user_version. The problem is that the usage of the user_version is not known. It could be harmless to dump or it could cause problems on a restore. > PS: please CC me on replies since I'm not subscribed to the mailing list http://catb.org/~esr/faqs/smart-questions.html#noprivate Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkrXVkYACgkQmOOfHg372QQ/+gCfd62kflIPGGwmZQ55sOWs4Eig OR8AnjE+E7MgpTWabKTpQmC29xrtXHf6 =d+CQ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users