Re: [sqlite] SQLite .dump

2018-07-14 Thread 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite .dump

2018-07-13 Thread Warren Young 
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

2018-07-13 Thread Simon Slavin 
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

[sqlite] SQLite .dump

2018-07-13 Thread 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.18864,'some text',
 X'6162630a',153051120,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', 153051120, 68715000, 1530343358000,
 1530559371079);

___
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

2014-04-17 Thread Simon Slavin 

On 17 Apr 2014, at 3:42pm, Igor Tandetnik  wrote:

> 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

2014-04-17 Thread Gerry Snyder 

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

2014-04-17 Thread Igor Tandetnik 

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

2014-04-17 Thread Roger Binns 
-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

[sqlite] sqlite dump makes wrong "CREATE VIEW" order

2014-04-17 Thread Tyumentsev Alexander 
sqlite in some cases dumps views in wrong order.

how to reproduce on linux:

1) Make the test database with 2 "view". v1 depends on v2. Recreate v2
cat <<_EOF_ | sqlite3 test.sqlite
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test ( "id1" integer(8,0), "id2" integer(8,0), "id3"
integer(8,0) );
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;
CREATE VIEW v2 as select id1 from test where id1;
COMMIT;
_EOF_

2) Dump the base
sqlite3 test.sqlite .dump > test.sqlite.dump
cat test.sqlite.dump
  =>
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test ( "id1" integer(8,0), "id2" integer(8,0), "id3"
integer(8,0) );
CREATE VIEW v1 as select t1.id2 from test as t1 LEFT JOIN v2 ON
t1.id2=v2.id1;
CREATE VIEW v2 as select id1 from test where id1;
COMMIT;

Here "CREATE VIEW v1" and "CREATE VIEW v2" placed in wrong order.

3) try to restore
cat test.sqlite.dump | sqlite3 test2.sqlite
 =>
Error: near line 4: no such table: main.v2


Is it the user responsibility to follow all dependencies and recreate
"VIEW" tree ?

Best Regards
 Tyumentsev Alexander
___
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

2009-10-15 Thread Roger Binns 
-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