Hi. A colleage is making schema changes to a binary DB (we do not keep around the original source .sql; not my choice). To review those, I'm asking him for a before/after diff of the schema. He used the CLI to .schema the before/after and uploaded both under the same name, so our code review tool can show that diff. But we're seeing a different order of the tables in the output, thus the diff is mostly unusable. Is that expected or normal? I'm surprised, to be honest. Am I missing something?

What other options do we have, to review schema differences between two binary SQLite DB files? We'll give sqldiff --schema a try too, but anything else?

BTW, he's adding foreign key constraints, so could it be that .schema does a topological sort to show the create table DDLs in order? Should we craft a different query against sqlite_master to ignore that topological order, to be able to diff the before/after? Any advise on doing that?

Why not use the sqldiff command-line tool with the --schema option?

Unfortunately, it reports nothing...

The only differences between tables are foreign key constraints, and it does not appear sqldiff reports on those at all :(

That sounds like a feature-request-to-be for sqldiff...

Since we are talking about schema and normalcy, I would love for SQLite to display a more SQL look and feel on the .schema command. For example:

sqlite> .schema
CREATE TABLE z (id, pid, a, yyyy, c, d, idate);
CREATE TABLE zz (id, pid, a, yyyy, c, d, idate);
CREATE TABLE z0
(
   id,
   pid,
   a,
   yyyy,
   c,
   d,
   idate
);
CREATE TABLE z1
(
   id,
        pid,
               a,
                       yyyy,
                                     c,
                                           d,
                                                idate
);

But this is what I copied and pasted to the tool:

create table z (id, pid, a, yyyy, c, d, idate);
create table zz (id, pid, a, yyyy, c, d, idate);
create table z0
(
   id,
   pid,
   a,
   yyyy,

   c, 
   d,
   idate
);
create table z1
(
   id,
        pid,
               a,
                       yyyy,
                                     c, 
                                           d,
                                                idate
);

It would be nice to have these CREATE TABLE calls be standardized to a more SQL friendly table declaration and spacing. And, furthermore, assign the default types to each column name, if they are not defined. For example, all of those above will be set to TEXT because no definition was assigned. Just thinking out-loud the desires of my heart. :-) Thanks.

josé

Since we are talking about schema and normalcy, I would love for SQLite to display a more SQL look and feel on the .schema command.

That would require that the shell actually be able to parse the schema. It doesn't: it simply spits out what's in the sqlite_schema, and that table simply stores exactly what you put into it, including the whitespace and comments:

sqlite> .schema
CREATE TABLE t(
a,

b,

-- comment
c default 1);

In terms of normalization, the obvious answer is, if you want CREATE TABLE statements to be standardized, then you should do so at table creation. That way you can format and structure them exactly as you want.

A slightly more practical answer is to use pragma table_info(). For example:

sqlite> create table z1
   ...> (
   ...>    id,
   ...>         pid,
   ...>                a,
   ...>                        yyyy,
   ...>                                      c, 
   ...>                                            d,
   ...>                                                 idate
   ...> );

sqlite> pragma table_info(z1);
cid  name   type  notnull  dflt_value  pk
---  -----  ----  -------  ----------  --
0    id           0                    0 
1    pid          0                    0 
2    a            0                    0 
3    yyyy         0                    0 
4    c            0                    0 
5    d            0                    0 
6    idate        0                    0 

You can also use the new(er) Table Valued Function syntax: SELECT * FROM PRAGMA_TABLE_INFO('z1'); Just be aware you need to pass the table name as a string, not as an identifier. That gives you the full power of the SELECT statement, so you can do stuff like this: SELECT m.name, i.* FROM sqlite_master AS m JOIN pragma_table_info(m.name) AS i; to dump the whole schema.

I do kind of wish table_info had a comments column, so that comments done in a standard way would end up there. That would make it easier for GUI tools to display and show the comments in their table builders. I know that is likely a really difficult thing, however, as most times comments are thrown away quite early in the parsing process.

-j

I agree. The reason why I used that example was that I created a table using a program and the tabs and new lines logic was wrong and ended up creating something like the z1 table above. So, when I do .schema, something like that comes up. :-) But, you are right. I will stop now. :-) Thanks.

josé

That would require that the shell actually be able to parse the schema.

Right, Stephan. But, I am talking deeper than the shell. Let's call it a beautifier of the code sent execution. And by the way, there is already a code beautifier available. Because if you enter,

create table z(b);

when you type .schema, you get,

CREATE TABLE z(b);

What changed create table to CREATE TABLE? That's the beautifier that I am talking about. :-) By the way, the same thing happens when I send it through the SQLite wrapper. So, it's deeper than the shell. Anyway, it's just a desire of mine. :-) I am not going to stop using SQLite because of a lack of a code beautifier. :-)

josé

SQLite records the CREATE TABLE statements verbatim for a number of reasons, including both backwards and forwards compatibility (using new database with old SQLite, or new SQLite with old database). The table statements are then re-parsed on database open, allowing the feature-set to scale with the SQLite library version.

Our team happens to take advantage of that verbatim recording, as our design spec requires that we add comments to both the table and each column describing its basic function. For example, if we input the following statement, the whole thing including comments is recorded into the database.

sqlite> CREATE TABLE test
   ...> (   -- Table to setup and run tests
   ...> 
   ...>   id  INTEGER PRIMARY KEY  NOT NULL, -- PK id
   ...>   name TEXT,                         -- name of test
   ...>   cmd TEXT                           -- command syntax to run test
   ...> );

sqlite> .schema test
CREATE TABLE test
(   -- Table to setup and run tests

  id  INTEGER PRIMARY KEY  NOT NULL, -- PK id
  name TEXT,                         -- name of test
  cmd TEXT                           -- command syntax to run test
);

Since our files are data exports, this hugely helps our customers understand and navigate the schema, as the database becomes somewhat self-documenting, and there is no need to refer to an external .sql file (which, in our case, doesn't exist since these are all programmatically generated).

-j

Not that I mind too much, but you're a bit hijacking this thread Jose :)

Maybe next time start a new thread, which references the old thread if you want. Just my $0.02.

PS: I admit my title for the thread was not great, since missing the main point of comparing schemas, rather than the CLI .schema itself.

PS: I admit my title for the thread was not great,

FYI: you can change the title by editing the top post.

Not that I mind too much, but you're a bit hijacking this thread Jose :)

You're right. I apologize for that. But, I had this in mind for a while, but, some times I feel this tool is such a great tool that I don't want to ask for anything that is, IMHO, 'an enhancement', so I kept it quiet. But when I saw your post, my eyes got really big (and I have big eyes), so imagine...

Mayhaps using the option to the .schema command which is intended to format the SQL to someone's liking might be perspicacious.

See .help schema at a CLI interface prompt near you.

See .help schema at a CLI interface prompt near you.

sqlite> .help schema
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
   Options:
      --indent             Try to pretty-print the schema
      --nosys              Omit objects whose names start with "sqlite_"
sqlite>
sqlite> create table z (id, pid, a, yyyy, c, d, idate);
sqlite> create table z0
   ...> (
   ...>    id,
   ...>    pid,
   ...>    a,
   ...>    yyyy,
   ...>
   ...>    c,
   ...>    d,
   ...>    idate
   ...> );
sqlite>
sqlite> create table z1
   ...> (
   ...>    id,
   ...>         pid,
   ...>                a,
   ...>                        yyyy,
   ...>                                      c,
   ...>                                            d,
   ...>                                                 idate
   ...> );
sqlite> .schema --indent
CREATE TABLE z(id, pid, a, yyyy, c, d, idate);
CREATE TABLE z0(id,
pid,
a,
yyyy,
c, d,
idate);
CREATE TABLE z1(id,
pid,
a,
yyyy,
c, d,
idate);
sqlite>

Not so pretty...

Pretty is in the eye of the beholder. The beholder who wrote the --indent would probably differ with your assessment.

I just make stuff pretty before sending the command for execution since the overhead of parsing the "prettified" statements is negligible (about the same as removing the comments from C source code) and does not affect the "parsing result" (executable) in any way.

In other words, if you wish to have pretty source code it would be perspicacious to write it that way in the first place.

In other words, if you wish to have pretty source code it would be perspicacious to write it that way in the first place.

Yes, you're right.