SQLite: Code Snippets

import sqlite3
import os.path

db_filename = 'comparison.db'

if os.path.isfile(db_filename):
   os.remove(db_filename)

db = sqlite3.connect(db_filename)

cur = db.cursor()

cur.execute('create table t (a, b)')

cur.execute("insert into t values (    1 ,    1 )")
cur.execute("insert into t values (    1 ,    2 )")
cur.execute("insert into t values ( null ,    3 )")
cur.execute("insert into t values (    4 , null )")
cur.execute("insert into t values ( null , null )")

print ("a = b")
for r in cur.execute('select * from t where a = b'):
    print ("  %d = %d" % (r[0], r[1]))
#   1 = 1


print ("nullif(a, b)")
for r in cur.execute('select nullif(a, b) from t'):
    print (r[0])
# None
# 1
# None
# 4
# None

Github repository about-sqlite, path: /nulls/comparison.py

create virtual table tq84_tab
using fts4 (
  col_1 text,
  col_2 text,
  col_3 text
);

select
  name
from
  sqlite_master
where
  type = 'table'          and
  name like '%tq84_tab%'  and
  name !=    'tq84_tab';
--
--tq84_tab_content
--tq84_tab_segments
--tq84_tab_segdir
--tq84_tab_docsize
--tq84_tab_stat

.schema tq84_tab_content
--
-- CREATE TABLE 'tq84_tab_content'(docid INTEGER PRIMARY KEY, 'c0col_1', 'c1col_2', 'c2col_3');

.schema tq84_tab_segments
--
-- CREATE TABLE 'tq84_tab_segments'(blockid INTEGER PRIMARY KEY, block BLOB);

.schema tq84_tab_segdir
--
-- CREATE TABLE 'tq84_tab_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)); 

.schema tq84_tab_docsize
--
-- CREATE TABLE 'tq84_tab_docsize'(docid INTEGER PRIMARY KEY, size BLOB);

.schema tq84_tab_stat
--
-- CREATE TABLE 'tq84_tab_stat'(id INTEGER PRIMARY KEY, value BLOB);


drop table tq84_tab;

Github repository about-sqlite, path: /tables/full-text-index/shadow-tables.sql

import sqlite3
import os.path

db_file = 'points.db'

if os.path.isfile(db_file):
   os.remove(db_file)

db = sqlite3.connect(db_file)
db.text_factory = str
cur = db.cursor()

cur.execute('create table points      (id, x ,     y     )')
cur.execute('create table points_rtree(id, x1, x2, y1, y2)')

cur.execute('insert into points values (?,?,?)', (1,  0.5    ,  0.5    ))
cur.execute('insert into points values (?,?,?)', (2,  9.99999,  8.88888))
cur.execute('insert into points values (?,?,?)', (3, 42.42   ,  0      ))
cur.execute('insert into points values (?,?,?)', (4, 42.42   ,  8.888  ))


cur.execute('insert into points_rtree select id, x, x, y, y from points')

db.commit()

for r in cur.execute('select id from points_rtree where x1=42.42'):
    print "{:5d}".format(*r)

for r in cur.execute('select id from points_rtree where y2 between 8.8888 and 8.888888'):
    print "{:5d}".format(*r)

Github repository about-sqlite, path: /tables/rtree/points.py

--
-- sqlite does not have a union operator, it has
-- an except operator instead.
--

create table tq84_except_1 (col_1, col_2, col_3);
create table tq84_except_2 (col_1, col_2, col_3);

insert into tq84_except_1 values ('foo', 'bar', 'baz'  );
insert into tq84_except_1 values ('one', 'two', 'three');
insert into tq84_except_1 values ('bla', 'bla', 'bla'  );

insert into tq84_except_2 values ('foo', 'bar', 'baz'  );
insert into tq84_except_2 values ('abc', 'def', 'ghi'  );

.mode column
.width 5 5
select col_1, col_2 from tq84_except_1  EXCEPT
select col_1, col_2 from tq84_except_2;
-- bla    bla
-- one    two

drop table tq84_except_1;
drop table tq84_except_2;

Github repository about-sqlite, path: /sql/select/set_operators/except.sql

import sqlite3
import os.path


db_file = 'aggregate_function_without_group_by.db'
if os.path.isfile(db_file):
   os.remove(db_file)

db = sqlite3.connect(db_file)
db.text_factory = str
cur = db.cursor()

cur.execute('create table t (a text, b integer)')

cur.execute('insert into t values (?, ?)', ('foo',  7))
cur.execute('insert into t values (?, ?)', ('foo',  2))
cur.execute('insert into t values (?, ?)', ('foo',  5))

cur.execute('insert into t values (?, ?)', ('bar',  3))
cur.execute('insert into t values (?, ?)', ('bar',  8))
cur.execute('insert into t values (?, ?)', ('bar',  5))
cur.execute('insert into t values (?, ?)', ('bar',  4))

cur.execute('insert into t values (?, ?)', ('baz',  4))
cur.execute('insert into t values (?, ?)', ('baz',  6))

for r in cur.execute('select a, min(b), max(b) from t'):
    print "{:4s} {:2d} {:2d}".format(*r)
    # baz   2  8

Github repository about-sqlite, path: /sql/select/group_by/aggregate_function_without_group_by.py

create table tq84_table (
  id integer primary key
);

with num_generator(n) as (
     select 1 n union all
     select 1+n from num_generator
     limit  1000
)
insert into tq84_table
select n from num_generator;

analyze tq84_table;

select
  idx,
  stat
from
  sqlite_stat1
where
  tbl = 'tq84_table';


--
--   Table sqlite_stat3 only available if
--   compiled with SQLITE_ENABLE_STAT3 and
--   without SQLITE_ENABLE_STAT4
--
select * from sqlite_stat3;

--
--   Table sqlite_stat4 only available if
--   compiled with SQLITE_ENABLE_STAT4 and
select * from sqlite_stat4;

drop table tq84_table;

Github repository about-sqlite, path: /sql/analyze/sqlite_statx.sql

--
--  $ rm -f vacuum.db ; sqlite3 vacuum.db < vacuum.sql

create table foo (
   t text 
);

insert into foo
with recursive rec (t_) as
  (
      select '*'
    union all
      select
        t_ || t_
      from
        rec
      limit 22
  )
select
  t_
from
  rec;


-- select length(t) from foo;

.shell ls -sh vacuum.db
--
-- 4.1M vacuum.db


drop table foo;

.shell ls -sh vacuum.db
--
-- 4.1M vacuum.db

vacuum;

.shell ls -sh vacuum.db
--
-- 4.0K vacuum.db

Github repository about-sqlite, path: /sql/vacuum.sql

create table tq84_eqp_A (
  id     integer primary key,
  col1   text,
  col2   text
);

create table tq84_eqp_B (
  id     integer primary key,
  col1   text,
  col2   text
);

create table tq84_eqp_A2B (
  id_A   integer references tq84_eqp_A,
  id_B   integer references tq84_eqp_B,
  col1   text,
  col2   text
);


create index tq84_eqp_A_ix_col2   on tq84_eqp_A  (col2);
create index tq84_eqp_A2B_ix_col1 on tq84_eqp_A2B(col1);

.headers on
.mode column
.width 8 5 4 80
explain query plan
  select
    A.col1,
    A.col2,
    B.col2
  from
    tq84_eqp_A      A                      join
    tq84_eqp_A2B    A2B on A.id = A2B.id_A join
    tq84_eqp_B        B on B.id = A2B.id_B
  where
    A.col2 > 'foo'  and
    B.col1 = 'bar';


drop table tq84_eqp_A2B;
drop table tq84_eqp_A;
drop table tq84_eqp_B;

Github repository about-sqlite, path: /sql/explain/explain_query_plan-columns.sql

attach database 'foo.db' as foo;

--
-- Use ».database« to show attached databases:
--
.database
-- 
-- main: 
-- foo: /home/rene/github/about/sqlite/attach/foo.db
-- 

--
-- Create table foo in persistent file foo.db:
--
create table foo.tab (a, b, c);

--
-- Detach database again:
--
detach foo;

--
-- Verify it has been detached:
--
.database
--
-- main:
--

Github repository about-sqlite, path: /attach/attach.sql

attach database 'xyz.db' as xyz;

create table xyz.tab (a, b, c);

--
--  Note: in SQLite, when creating an index,
--  the schema name xyz is in front of the index name rather
--  than the table name:
--
create index xyz.ix on tab(a);

detach xyz;

Github repository about-sqlite, path: /attach/create-index.sql

attach database 'abc.db' as abc;

create table abc.tab(a);
insert into tab values ('attached table');

create table tab(a);
insert   into tab values ('main table');
select * from tab;

create temp table tab(a);
insert into tab values ('temp table');
select * from tab;

drop table tab;
select * from tab;

drop table tab;
select * from tab;

drop table tab;

Github repository about-sqlite, path: /attach/name-resolution.sql

create table tq84_not_null (
  col_int     int,
  col_int_nn  int not null
);

insert into tq84_not_null values (   1,     5);
insert into tq84_not_null values (null,    42);
insert into tq84_not_null values (   9,    '');
insert into tq84_not_null values (  11, 'abc');

-- not possible, throws
--     NOT NULL constraint failed: tq84_not_null.col_int_nn
--
-- insert into tq84_not_null values (  88,  null);

select * from tq84_not_null;

drop table tq84_not_null;

Github repository about-sqlite, path: /constraints/not-null.sql

import sqlite3
import os.path


db_file = 'foreign-key_deferrable.db'
if os.path.isfile(db_file):
   os.remove(db_file)

db = sqlite3.connect(db_file)
db.text_factory = str

# Use the following pragma to enforce foreign keys!!!
db.execute('pragma foreign_keys=on')

cur = db.cursor()

cur.execute("""

  create table parent (
    id integer primary key,
    t  text
  )

""")

cur.execute("""

  create table child (
    parent_id integer not null 
              references parent
              deferrable initially deferred,
    t         text)
    
""")

cur.execute('insert into parent values (1, "one")')
cur.execute('insert into parent values (2, "two")')

cur.execute('insert into child  values (1, "ABC")')
cur.execute('insert into child  values (3, "DEF")') # Uh oh...

try:
  db.commit()
  print "Expected IntegrityError wasn't raised"
  
except sqlite3.IntegrityError:
  pass # Error was expected

cur.execute('insert into parent values (3, "GHI")')

db.commit()

Github repository about-sqlite, path: /constraints/foreign-key_deferrable.py

import sqlite3
import os.path


db_file = 'foreign-key_pragma.db'
if os.path.isfile(db_file):
   os.remove(db_file)

db = sqlite3.connect(db_file)
db.text_factory = str

# Use the following pragma to enforce foreign keys!!!
# db.execute('pragma foreign_keys=on')

cur = db.cursor()

cur.execute('create table parent  (id integer primary key, t text)')
cur.execute('create table child_1 (parent_id integer not null references parent, t text)')
cur.execute('create table child_2 (parent_id integer not null, t text, foreign key (parent_id) references parent(id))')

cur.execute('insert into parent values (1, "one")')
cur.execute('insert into parent values (2, "two")')

cur.execute('insert into child_1 values (1, "ABC")')
cur.execute('insert into child_1 values (3, "DEF")') # Uh oh...

cur.execute('insert into child_2 values (1, "ABC")')
cur.execute('insert into child_2 values (3, "DEF")') # Uh oh...

db.commit()

Github repository about-sqlite, path: /constraints/foreign-key_pragma.py