Tag Archives: sql

Aviation, Software Development, Stratux, Technology

SQLite – get table schema

Leave a comment

SQLite is a functional little SQL database that is often found used in embedded systems projects (such as one of my favorites, Stratux).

As far as I know, there are three ways to look at a table’s schema (the 3rd is just and extension of the 2nd).

.schema table_name

Running .schema will show you a ‘CREATE’ statement that can be used to build the database table. The issues I have with using this are that the command only shows the original CREATE, not any subsequent changes such as indexes, are worse yet, new fields! Regardless, here is an example:

.schema traffic
CREATE TABLE traffic (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Icao_addr INTEGER, Reg TEXT, Tail TEXT, Emitter_category INTEGER, OnGround INTEGER, Addr_type INTEGER, TargetType INTEGER, SignalLevel REAL, Squawk INTEGER, Position_valid INTEGER, Lat REAL, Lng REAL, Alt INTEGER, GnssDiffFromBaroAlt INTEGER, AltIsGNSS INTEGER, NIC INTEGER, NACp INTEGER, Track INTEGER, Speed INTEGER, Speed_valid INTEGER, Vvel INTEGER, Timestamp STRING, PriorityStatus INTEGER, Age REAL, AgeLastAlt REAL, Last_seen STRING, Last_alt STRING, Last_GnssDiff STRING, Last_GnssDiffAlt INTEGER, Last_speed STRING, Last_source INTEGER, ExtrapolatedPosition INTEGER, Bearing REAL, Distance REAL, timestamp_id INTEGER);

PRAGMA table_info

This is my favored by far. Format can actually be taken and dropped int a Wiki page, or JIRA case or any other similar document system.. but it’s still not the most ideal, as you can see:


PRAGMA table_info(traffic);

0|id|INTEGER|1||1
1|Icao_addr|INTEGER|0||0
2|Reg|TEXT|0||0
3|Tail|TEXT|0||0
4|Emitter_category|INTEGER|0||0
5|OnGround|INTEGER|0||0
6|Addr_type|INTEGER|0||0
7|TargetType|INTEGER|0||0
8|SignalLevel|REAL|0||0
9|Squawk|INTEGER|0||0
10|Position_valid|INTEGER|0||0
11|Lat|REAL|0||0
12|Lng|REAL|0||0
13|Alt|INTEGER|0||0
14|GnssDiffFromBaroAlt|INTEGER|0||0
15|AltIsGNSS|INTEGER|0||0
16|NIC|INTEGER|0||0
17|NACp|INTEGER|0||0
18|Track|INTEGER|0||0
19|Speed|INTEGER|0||0
20|Speed_valid|INTEGER|0||0
21|Vvel|INTEGER|0||0
22|Timestamp|STRING|0||0
23|PriorityStatus|INTEGER|0||0
24|Age|REAL|0||0
25|AgeLastAlt|REAL|0||0
26|Last_seen|STRING|0||0
27|Last_alt|STRING|0||0
28|Last_GnssDiff|STRING|0||0
29|Last_GnssDiffAlt|INTEGER|0||0
30|Last_speed|STRING|0||0
31|Last_source|INTEGER|0||0
32|ExtrapolatedPosition|INTEGER|0||0
33|Bearing|REAL|0||0
34|Distance|REAL|0||0
35|timestamp_id|INTEGER|0||0

Now.. for my favorite modifier:

PRAGMA table_info with headers!

By turning headers ‘on’ you will now get a tabular list of what each of these fields mean. Note that the first operator command does not use a terminating semi-colon.

.headers on
PRAGMA table_info(traffic);

cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|1||1
1|Icao_addr|INTEGER|0||0
2|Reg|TEXT|0||0
3|Tail|TEXT|0||0
[... you get the idea ...]

35|timestamp_id|INTEGER|0||0

There you go.. three (OK two) ways to list a table schema in SQLite.

Software Development, Technology

Fun with ‘sed’ taking a list of table names and creating a list of T-SQL TRUNCATE commands

Leave a comment

Woo.. I love *NIX, especially ‘sed’ (and awk too for that matter). Wonderful things you can do to automated your programming, speed up development and really REALLY cut down on cut-paste errors.

Today’s task, was to take this list of tables… and generate a single bit fat T-SQL command to truncate them all.. *however* these tables might not exist in a given environment so each Truncate is wrapped in a test to make sure the table is there. If it’s not there, nothing to do, not to mention avoid a potential fatal error trying to truncate a non-existent table.

Here is a snippet of the tables in question:

…
FCT_CRIMINOGENIC_NEED_TOP_DOMAINS
FCT_DETENTION_RISK_RESPONSE_DETAILS
FCT_DETENTION_RISK_RESPONSE_FULL
…

This is the desired T-SQL for these three tables

— Truncate table FCT_CRIMINOGENIC_NEED_TOP_DOMAINS
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ‘FCT_CRIMINOGENIC_NEED_TOP_DOMAINS’)
BEGIN
TRUNCATE TABLE FCT_CRIMINOGENIC_NEED_TOP_DOMAINS
END

— Truncate table FCT_DETENTION_RISK_RESPONSE_DETAILS
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ‘FCT_DETENTION_RISK_RESPONSE_DETAILS’)
BEGIN
TRUNCATE TABLE FCT_DETENTION_RISK_RESPONSE_DETAILS
END

— Truncate table FCT_DETENTION_RISK_RESPONSE_FULL
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ‘FCT_DETENTION_RISK_RESPONSE_FULL’)
BEGIN
TRUNCATE TABLE FCT_DETENTION_RISK_RESPONSE_FULL
END

The Sed Command

sed ‘s#^\(.*\)#– Truncate table \1 \’$’\nIF EXISTS (SELECT * FROM sys.objects WHERE NAME = \’\\1\’) \\’$’\n BEGIN \\’$’\n TRUNCATE TABLE \\1 \\’$’\nEND \\’$’\n#g’ MY_INPUT_FILE

Secret Sauce Ingredients

Setting up the string match. In this case it was simple.. I wanted the entire line which is represented with .*

\(.*\)

This loads the matching string into an internal register in ‘sed’ that can be references as ‘1’ within the output replacement expression. This is what it looks like in the command:

\1

Adding a newline in the output.. this was the big trick, and requires dropping into the shell to generate the desired output. The string in the command looks like this:

\’$’\n

You may notice after the first drop into the shell, the escaping has to be doubled for the substitution and the newline…

\\1 \\’$’\n

I cannot speak to the reason for this, but that is what I encountered and why the substitutions change in the latter part of the string. You may find that is not necessary, or might cause an issue in your environment. Adjust as necessary.