SQLite Forum

Suggestion a new dot command (.map)
Login

Suggestion a new dot command (.map)

(1) By anonymous on 2021-01-29 15:38:37 [link] [source]

Suggestion for a new CLI dot command (may be .map) to produce the table below:

1.to promote at-a-glance familiarisation with any given database database.

2.to assist in the enumeration of column names in SQL statements

3.(may be even an aid to manual version control over time)

Column Name Description
AtDate Current timestamp
dbname Fully qualified database name
tid Ordinal position of table
tbl_name Table name
tbl_type Table type [table or view]
cid Ordinal position of column in table
col_name Column name
col_type Colum type
nullable Column acceps null?
dflt_value Column default value
pk Primary key?

`

AtDate dbname tid tbl_name tbl_type cid col_name col_type nullable dflt_value pk
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 12 Table1 table 1 Field1 varchar(255) 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 12 Table1 table 2 Field2 varchar(255) 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 12 Table1 table 0 ID integer 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 7 USStateAb table 0 Abbreviation nvarchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 7 USStateAb table 2 Classification nvarchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 7 USStateAb table 1 State nvarchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 4 address varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 2 au_fname varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 0 au_id varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 1 au_lname varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 5 city varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 8 contract bit 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 3 phone char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 6 state char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 9 authors table 7 zip char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 0 discounts table 4 discount decimal 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 0 discounts table 0 discounttype varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 0 discounts table 3 highqty smallint 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 0 discounts table 2 lowqty smallint 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 0 discounts table 1 stor_id char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 0 emp_id char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 1 fname varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 7 hire_date datetime 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 4 job_id smallint 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 5 job_lvl tinyint 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 3 lname varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 2 minit char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 11 employee table 6 pub_id char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 1 jobs table 1 job_desc varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 1 jobs table 0 job_id smallint 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 1 jobs table 3 max_lvl tinyint 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 1 jobs table 2 min_lvl tinyint 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 2 publishers table 2 city varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 2 publishers table 4 country varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 2 publishers table 0 pub_id char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 2 publishers table 1 pub_name varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 2 publishers table 3 state char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 3 roysched table 2 hirange int 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 3 roysched table 1 lorange int 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 3 roysched table 3 royalty int 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 3 roysched table 0 title_id varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 10 sales table 2 ord_date datetime 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 10 sales table 1 ord_num varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 10 sales table 4 payterms varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 10 sales table 3 qty smallint 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 10 sales table 0 stor_id char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 10 sales table 5 title_id varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 4 stores table 3 city varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 4 stores table 4 state char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 4 stores table 2 stor_address varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 4 stores table 0 stor_id char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 4 stores table 1 stor_name varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 4 stores table 5 zip char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 5 titleauthor table 0 au_id varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 5 titleauthor table 2 au_ord tinyint 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 5 titleauthor table 3 royaltyper int 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 5 titleauthor table 1 title_id varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 5 advance money 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 8 notes varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 4 price money 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 3 pub_id char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 9 pubdate datetime 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 6 royalty int 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 1 title varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 0 title_id varchar 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 2 type char 1 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 6 titles table 7 ytd_sales int 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 8 titleview view 2 au_lname varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 8 titleview view 1 au_ord tinyint 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 8 titleview view 3 price money 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 8 titleview view 5 pub_id char 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 8 titleview view 0 title varchar 0 0
2021-01-29 15:16:31 d:\sqlite32\db\pubs.db 8 titleview view 4 ytd_sales int 0 0

(2) By Simon Slavin (slavin) on 2021-01-29 19:08:55 in reply to 1 [link] [source]

Some of this information is not stored by SQLite (AtDate, column descriptions). Other parts of this information can be obtained using these commands

.databases PRAGMA schema.table_xinfo(table_name)

Do you have some specific reason to want the information in that format ?

(3) By anonymous on 2021-01-29 21:39:01 in reply to 2 [link] [source]

Do you have some specific reason to want the information in that format ?

ALL the information is generated by SQLite3 table/view by table/view; there is no wizardry here.

I am of a generation who wants to see what exists rather than of the generation who find out when the need arises, especially when chasing unexpected software behaviour or when inheriting an SQLite3 application

The suggested dot command would be a time-saving convenience both for debugging and documentation, nothing more.

(4) By Simon Slavin (slavin) on 2021-01-30 02:08:48 in reply to 3 [link] [source]

Pardon me. I didn't understand that the first table you included was a description of the second table, which was the subject of your comment. However, apart from having to iterate through the tables yourself, all that information is provided by the PRAGMA I listed. Perhaps I'm of the wrong generation to appreciate the advantage of having it listed all in one big table.

(5) By Mark Lawrence (mark) on 2021-01-31 13:23:21 in reply to 3 [source]

I find the table you have created valuable. Would you mind sharing the SQL you generate it with?

(6) By anonymous on 2021-01-31 15:08:07 in reply to 5 [link] [source]

Alas, I have no SQL script, at least, no SQL script runnable from the SQLite3 CLI.

I built the table using C# calling SQLite3.DLL directly.

I can describe the process:

1. Get a list of tables:

SELECT tbl_name
FROM sqlite_master
WHERE type IN (
		'table',
		'view'
		)
	AND tbl_name NOT LIKE 'sqlite%';

with CHINOOK.DB, you will get something like this:

tbl_name
--------------
albums
artists
customers
employees
genres
invoices
invoice_items
media_types
playlists
playlist_track
tracks

2. Iterate the list with this SQL:

select datetime('now') as AtDate, 'chinook.db' as Database,* from pragma_table_xinfo('albums');
AtDate               Database    cid  name      type           notnull  dflt_value  pk  hidden
-------------------  ----------  ---  --------  -------------  -------  ----------  --  ------
2021-01-31 14:53:49  chinook.db  0    AlbumId   INTEGER        1                    1   0
2021-01-31 14:53:49  chinook.db  1    Title     NVARCHAR(160)  1                    0   0
2021-01-31 14:53:49  chinook.db  2    ArtistId  INTEGER        1                    0   0

You need to save the results in each iteration into a table.

EITHER create a table with the first iteration with:

create table dbMap as select datetime('now') as AtDate, 'chinook.db' as Database,* from pragma_table_xinfo('albums');

and append to it with subsequent iterations.

OR

Create a table in each iteration

create table tblAlbums as select datetime('now') as AtDate, 'chinook.db' as Database,* from pragma_table_xinfo('albums');

If you choose the latter method, you need to collate the individual tables:

select * from tblAlbuns
union all
....
select * from tbltracks
select * from tbl tracks

You can then redirect the output to a text file, more or less giving you what I posted here.

For the iteration, you'll need to use a programming language OR use clever recursive CTEs within the SQLiite3 CLI.