SQLite .dump

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
dmp
Reply | Threaded
Open this post in threaded view
|

SQLite .dump

dmp
Hello,

Recently in testing my GUI tool I made a comparison from the
tool's dump and SQLite's command line .dump tool.

Seems .dump uses a short output of skipping the column names.
According to some of my research for various databases I use
one of these as options for SQL dump output:

http://ajqvue.com/docs/Manual/Ajqvue_Manual.html#Preferences_Data_Export_SQL
Preferences Data Export SQL

Always specifying the column names list and using the database's
identifier quoting character.

danap.

=======================
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE datatypes (data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
                        int_type INTEGER, real_type REAL, text_type TEXT,
                        blob_type BLOB, date_type DATE, time_type TIME,
                        datetime_type DATETIME, timestamp_type TIMESTAMP);

INSERT INTO datatypes VALUES(1,100,200.19999999999998864,'some text',
                             X'6162630a',1530511200000,68715000,1530343358000,
                             1530559371079);

=======================
GUI tool:
--
-- Dumping data for table "datatypes"
--

INSERT INTO "datatypes" ("data_type_id", "int_type", "real_type",
"text_type",
                         "blob_type", "date_type", "time_type",
"datetime_type",
                         "timestamp_type") VALUES(1, 100, 200.2, 'some text',
                         x'6162630a', 1530511200000, 68715000, 1530343358000,
                         1530559371079);

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite .dump

Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite .dump

Warren Young
In reply to this post by dmp
On Jul 13, 2018, at 10:15 AM, dmp <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: SQLite .dump

dmp
In reply to this post by dmp
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users