Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

db.schema property and sqlite-utils schema command #268

Closed
simonw opened this issue Jun 11, 2021 · 4 comments
Closed

db.schema property and sqlite-utils schema command #268

simonw opened this issue Jun 11, 2021 · 4 comments
Labels

Comments

@simonw
Copy link
Owner

simonw commented Jun 11, 2021

table.schema returns the schema for a table. db.schema should return the schema for the whole databes.

Can do this using select sql from sqlite_master where sql is not null:

https://latest.datasette.io/fixtures?sql=select+sql+from+sqlite_master+where+sql+is+not+null

@simonw simonw added enhancement New feature or request python-library labels Jun 11, 2021
@simonw simonw changed the title db.schema property db.schema property and sqlite-utils schema command Jun 11, 2021
@simonw
Copy link
Owner Author

simonw commented Jun 11, 2021

sqlite-utils schema data.db could output the same thing to the console.

@simonw
Copy link
Owner Author

simonw commented Jun 11, 2021

Out of interest, here are the rows from that table where sql is null: https://latest.datasette.io/fixtures?sql=select%0D%0A++*%0D%0Afrom%0D%0A++sqlite_master%0D%0Awhere%0D%0A++sql+is+null

type,name,tbl_name,rootpage,sql
index,sqlite_autoindex_simple_primary_key_1,simple_primary_key,3,
index,sqlite_autoindex_primary_key_multiple_columns_1,primary_key_multiple_columns,5,
index,sqlite_autoindex_primary_key_multiple_columns_explicit_label_1,primary_key_multiple_columns_explicit_label,7,
index,sqlite_autoindex_compound_primary_key_1,compound_primary_key,9,
index,sqlite_autoindex_compound_three_primary_keys_1,compound_three_primary_keys,11,
index,sqlite_autoindex_foreign_key_references_1,foreign_key_references,14,
index,sqlite_autoindex_sortable_1,sortable,16,
index,sqlite_autoindex_Table With Space In Name_1,Table With Space In Name,20,
index,sqlite_autoindex_table/with/slashes.csv_1,table/with/slashes.csv,22,
index,sqlite_autoindex_complex_foreign_keys_1,complex_foreign_keys,24,
index,sqlite_autoindex_custom_foreign_key_label_1,custom_foreign_key_label,26,
index,sqlite_autoindex_tags_1,tags,31,
index,sqlite_autoindex_searchable_tags_1,searchable_tags,34,
index,sqlite_autoindex_searchable_fts_segdir_1,searchable_fts_segdir,37,

@simonw
Copy link
Owner Author

simonw commented Jun 11, 2021

You can currently see the sql on the CLI using:

% sqlite-utils rows fixtures.db sqlite_master -c name -c sql
name                                                            sql
--------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------
simple_primary_key                                              CREATE TABLE simple_primary_key (
                                                                  id varchar(30) primary key,
                                                                  content text
                                                                )
sqlite_autoindex_simple_primary_key_1
primary_key_multiple_columns                                    CREATE TABLE primary_key_multiple_columns (
                                                                  id varchar(30) primary key,
                                                                  content text,
                                                                  content2 text
                                                                )
sqlite_autoindex_primary_key_multiple_columns_1
primary_key_multiple_columns_explicit_label                     CREATE TABLE primary_key_multiple_columns_explicit_label (
                                                                  id varchar(30) primary key,
                                                                  content text,
                                                                  content2 text
                                                                )
sqlite_autoindex_primary_key_multiple_columns_explicit_label_1
compound_primary_key                                            CREATE TABLE compound_primary_key (
                                                                  pk1 varchar(30),
                                                                  pk2 varchar(30),
                                                                  content text,
                                                                  PRIMARY KEY (pk1, pk2)
                                                                )
sqlite_autoindex_compound_primary_key_1
compound_three_primary_keys                                     CREATE TABLE compound_three_primary_keys (
                                                                  pk1 varchar(30),
                                                                  pk2 varchar(30),
                                                                  pk3 varchar(30),
                                                                  content text,
                                                                  PRIMARY KEY (pk1, pk2, pk3)
                                                                )
sqlite_autoindex_compound_three_primary_keys_1
foreign_key_references                                          CREATE TABLE foreign_key_references (
                                                                  pk varchar(30) primary key,
                                                                  foreign_key_with_label varchar(30),
                                                                  foreign_key_with_no_label varchar(30),
                                                                  FOREIGN KEY (foreign_key_with_label) REFERENCES simple_primary_key(id),
                                                                  FOREIGN KEY (foreign_key_with_no_label) REFERENCES primary_key_multiple_columns(id)
                                                                )
sqlite_autoindex_foreign_key_references_1
sortable                                                        CREATE TABLE sortable (
                                                                  pk1 varchar(30),
                                                                  pk2 varchar(30),
                                                                  content text,
                                                                  sortable integer,
                                                                  sortable_with_nulls real,
                                                                  sortable_with_nulls_2 real,
                                                                  text text,
                                                                  PRIMARY KEY (pk1, pk2)
                                                                )
sqlite_autoindex_sortable_1
no_primary_key                                                  CREATE TABLE no_primary_key (
                                                                  content text,
                                                                  a text,
                                                                  b text,
                                                                  c text
                                                                )
123_starts_with_digits                                          CREATE TABLE [123_starts_with_digits] (
                                                                  content text
                                                                )
paginated_view                                                  CREATE VIEW paginated_view AS
                                                                    SELECT
                                                                        content,
                                                                        '- ' || content || ' -' AS content_extra
                                                                    FROM no_primary_key
Table With Space In Name                                        CREATE TABLE "Table With Space In Name" (
                                                                  pk varchar(30) primary key,
                                                                  content text
                                                                )
sqlite_autoindex_Table With Space In Name_1
table/with/slashes.csv                                          CREATE TABLE "table/with/slashes.csv" (
                                                                  pk varchar(30) primary key,
                                                                  content text
                                                                )
sqlite_autoindex_table/with/slashes.csv_1
complex_foreign_keys                                            CREATE TABLE "complex_foreign_keys" (
                                                                  pk varchar(30) primary key,
                                                                  f1 text,
                                                                  f2 text,
                                                                  f3 text,
                                                                  FOREIGN KEY ("f1") REFERENCES [simple_primary_key](id),
                                                                  FOREIGN KEY ("f2") REFERENCES [simple_primary_key](id),
                                                                  FOREIGN KEY ("f3") REFERENCES [simple_primary_key](id)
                                                                )
sqlite_autoindex_complex_foreign_keys_1
custom_foreign_key_label                                        CREATE TABLE "custom_foreign_key_label" (
                                                                  pk varchar(30) primary key,
                                                                  foreign_key_with_custom_label text,
                                                                  FOREIGN KEY ("foreign_key_with_custom_label") REFERENCES [primary_key_multiple_columns_explicit_label](id)
                                                                )
sqlite_autoindex_custom_foreign_key_label_1
units                                                           CREATE TABLE units (
                                                                  pk integer primary key,
                                                                  distance int,
                                                                  frequency int
                                                                )
searchable                                                      CREATE TABLE searchable (
                                                                  pk integer primary key,
                                                                  text1 text,
                                                                  text2 text,
                                                                  [name with . and spaces] text
                                                                )
searchable_fts                                                  CREATE VIRTUAL TABLE "searchable_fts"
                                                                    USING FTS3 (text1, text2, [name with . and spaces], content="searchable")
searchable_fts_content                                          CREATE TABLE 'searchable_fts_content'(docid INTEGER PRIMARY KEY, 'c0text1', 'c1text2', 'c2name with . and spaces', 'c3content')
searchable_fts_segments                                         CREATE TABLE 'searchable_fts_segments'(blockid INTEGER PRIMARY KEY, block BLOB)
searchable_fts_segdir                                           CREATE TABLE 'searchable_fts_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx))
sqlite_autoindex_searchable_fts_segdir_1
select                                                          CREATE TABLE [select] (
                                                                  [group] text,
                                                                  [having] text,
                                                                  [and] text
                                                                )
facet_cities                                                    CREATE TABLE facet_cities (
                                                                    id integer primary key,
                                                                    name text
                                                                )
simple_view                                                     CREATE VIEW simple_view AS
                                                                    SELECT content, upper(content) AS upper_content FROM simple_primary_key

@simonw
Copy link
Owner Author

simonw commented Jun 11, 2021

From the prototype:

% sqlite-utils schema 24ways.db
CREATE TABLE [articles] (
               [title] TEXT  ,
   [contents] TEXT  ,
   [year] TEXT  ,
   [author] TEXT  ,
   [author_slug] TEXT  ,
   [published] TEXT  ,
   [url] TEXT  ,
   [topic] TEXT  
        );
CREATE VIRTUAL TABLE "articles_fts" USING FTS5 (
                title, author, contents,
                content="articles"
            );
CREATE TABLE 'articles_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'articles_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'articles_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'articles_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
% sqlite-utils schema 24ways.db | sqlite3 /tmp/boo.db
Error: near line 15: table 'articles_fts_data' already exists
Error: near line 16: table 'articles_fts_idx' already exists
Error: near line 17: table 'articles_fts_docsize' already exists
Error: near line 18: table 'articles_fts_config' already exists

The problem here is that the CREATE VIRTUAL TABLE "articles_fts"... line causes those next four tables to be created - but that means that piping the output of this command into sqlite3 in order to re-create those tables throws errors.

I don't think this matters. I see this tool as more for introspection than for recreating table structures.

@simonw simonw closed this as completed in 0d2e4f4 Jun 11, 2021
simonw added a commit that referenced this issue Jun 12, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

1 participant