Tables, triggers and views – v. 1.x

Tables:

admin:
 CREATE TABLE admin(a INTEGER NOT NULL DEFAULT 3,d TEXT NOT NULL DEFAULT 2,i INTEGER,n INTEGER NOT NULL DEFAULT -3,r INTEGER NOT NULL DEFAULT -2,t INTEGER NOT NULL DEFAULT (strftime('%s','now')),u INTEGER NOT NULL DEFAULT (uid()))

alias:
 CREATE TABLE alias(d TEXT UNIQUE NOT NULL DEFAULT '-',i INTEGER NOT NULL DEFAULT 0,t INTEGER NOT NULL DEFAULT (strftime('%s','now')),u INTEGER NOT NULL DEFAULT (uid()))

data:
 CREATE TABLE data(d TEXT NOT NULL DEFAULT 'unid',i INTEGER,t INTEGER NOT NULL DEFAULT (strftime('%s','now')),u INTEGER NOT NULL DEFAULT (uid()),PRIMARY KEY(i ASC))

node:
 CREATE TABLE node(a INTEGER NOT NULL DEFAULT 1,d INTEGER NOT NULL DEFAULT 0,i INTEGER,n INTEGER NOT NULL DEFAULT 1,t INTEGER NOT NULL DEFAULT (strftime('%s','now')),u INTEGER NOT NULL DEFAULT (uid()),PRIMARY KEY(i DESC))

stock:
 CREATE TABLE stock(i INTEGER DEFAULT 0,t INTEGER NOT NULL DEFAULT (strftime('%s','now')),u INTEGER NOT NULL DEFAULT (uid()),PRIMARY KEY(i ASC))

5 sql tables.

Views:

aliases: 
 CREATE TEMP VIEW IF NOT EXISTS aliases AS SELECT alias.i, alias.d as als, alias.t, alias.u FROM alias ORDER BY alias.d 

blanks:  
 CREATE TEMP VIEW IF NOT EXISTS blanks AS SELECT node.i as blk, node.a, node.d, node.n, node.t, node.u FROM node WHERE (node.a = -1 AND node.d < 0 AND node.n <= 0) 

flyers:  
 CREATE TEMP VIEW IF NOT EXISTS flyers AS SELECT * FROM symbols WHERE i NOT IN (SELECT a FROM links) 

formats:  
 CREATE TEMP VIEW IF NOT EXISTS formats AS SELECT node.i, data.d AS for, node.a, node.t, node.u FROM (node LEFT JOIN data ON (node.a = data.i)) WHERE (ABS(node.a) > 0 AND node.n = 0 AND node.d = 1) 

fullforms: 
 CREATE TEMP VIEW IF NOT EXISTS fullforms AS SELECT  DISTINCT si, d1d AS for, sa, st, su FROM  (SELECT DISTINCT  symbols.i AS si, symbols.a AS sa, symbols.d AS sd,symbols.n AS sn,symbols.t AS st,symbols.u AS su, data10.d AS d1d FROM (symbols,data AS data10, titles AS title1) WHERE ((symbols.a = data10.i AND symbols.n = 0) OR (symbols.a = title1.i AND symbols.n = 0 AND data10.i = title1.a))) 

junks:  
 CREATE TEMP VIEW IF NOT EXISTS junks AS SELECT data.i, data.d as jnk, data.t, data.u FROM data WHERE data.i NOT IN (SELECT a FROM symbols) AND data.i NOT IN (SELECT n FROM symbols) AND data.i NOT IN (SELECT a FROM records) 

links: 
 CREATE TEMP VIEW IF NOT EXISTS links AS SELECT node.i AS lnk, node.a, node.d, node.n, node.t, node.u FROM node WHERE (node.a < 0 AND node.d < 0 AND node.n <= 0) 

properties: 
 CREATE TEMP VIEW IF NOT EXISTS properties AS SELECT DISTINCT data.i, data.d AS pro, data.t, data.u FROM (node LEFT JOIN data ON (data.i = node.n)) WHERE (ABS(node.a) > 0 AND ABS(node.n) > 0 AND node.d IN (0,1)) ORDER BY data.d,data.i 

records: 
 CREATE TEMP VIEW IF NOT EXISTS records AS SELECT node.i, data.d AS rec, node.a, node.n, node.t, node.u FROM node,data WHERE (node.i = node.d AND node.a = data.i) 

subdbs: 
 CREATE TEMP VIEW IF NOT EXISTS subdbs AS SELECT node.i, data.d as sdb, node.n, node.a, node.t, node.u FROM node,data WHERE (node.i = node.d AND node.a = data.i AND data.d LIKE '%') 

symbols: 
 CREATE TEMP VIEW IF NOT EXISTS symbols AS SELECT node.i, secondata.d AS typ, node.a, firstdata.d AS pro, node.n, node.d, node.t, node.u FROM ((node LEFT JOIN data AS firstdata ON ((node.n = firstdata.i))) LEFT JOIN data AS secondata ON ((node.a = secondata.i))) WHERE ((node.a != 0 AND node.n != 0 AND node.d IN (0,1)) OR (node.a != 0 AND node.n = 0 AND  node.d = 1)) 

symdaprodatyp: 
 CREATE TEMP VIEW IF NOT EXISTS symdaprodatyp AS SELECT node.i, data1.d AS typ, node.a, data2.d AS pro, node.n,node.d, node.t, node.u FROM ((node LEFT JOIN data data1 ON (node.a = data1.i)) LEFT JOIN data data2 ON (data2.i = node.n)) WHERE (node.a > 0 AND node.n > 0 AND node.d IN (0,1)) 

symdaproretyp: 
 CREATE TEMP VIEW IF NOT EXISTS symdaproretyp AS SELECT node.i, node.a AS typ,node.a, data.d AS pro, node.n, node.d, node.t, node.u FROM (node LEFT JOIN data ON (data.i = node.n)) WHERE (node.a 0 AND node.d IN (0,1)) 

symoffrecs: 
 CREATE TEMP VIEW IF NOT EXISTS symoffrecs AS SELECT sym.i, datt.d AS typ, sym.a, datp.d AS pro, sym.n, sym.d, sym.t, sym.u FROM data AS datt, data AS datp, symbols AS sym WHERE ( sym.n < 0 AND (datp.i IN (SELECT a FROM records AS recp WHERE (recp.i = get_myvar('var')) OR (recp.rec LIKE get_myvar('var')) AND recp.i = sym.n)) AND sym.a < 0 AND (datt.i IN (SELECT a FROM records AS rect WHERE (rect.i = get_myvar('txt'))  OR (rect.rec LIKE get_myvar('txt')) AND rect.i = sym.a ))) 

symreprodatyp: 
 CREATE TEMP VIEW IF NOT EXISTS symreprodatyp AS SELECT node.i, data.d AS typ,node.a, node.n AS pro, node.n, node.d, node.t, node.u FROM (node LEFT JOIN data ON (data.i = node.a)) WHERE (node.a > 0 AND node.n < 0 AND node.d IN (0,1)) 

symreproretyp: 
 CREATE TEMP VIEW IF NOT EXISTS symreproretyp AS SELECT node.i, node.a as typ, node.a, node.n as pro, node.n, node.d, node.t, node.u FROM node WHERE (node.a < 0 AND node.n < 0 AND node.d IN (0,1)) 

titles: 
 CREATE TEMP VIEW IF NOT EXISTS titles AS SELECT node.i, data.d as tit, node.a, node.n, node.t, node.u FROM node,data WHERE (node.i = node.d AND node.a = data.i AND data.d NOT LIKE '%') 

types: 
 CREATE TEMP VIEW IF NOT EXISTS types AS SELECT DISTINCT data.i, data.d as typ, data.t, data.u FROM (node LEFT JOIN data ON (data.i = node.a)) WHERE (ABS(node.a) > 0 AND ABS(node.n) > 0 AND node.d IN (0,1)) ORDER BY data.d,data.i 

vtest: 
 CREATE TEMP VIEW IF NOT EXISTS vtest AS SELECT sym.i, datt.d AS typ, sym.a, datp.d AS pro, sym.n, sym.d, sym.t, sym.u FROM data AS datt, data AS datp, symbols AS sym WHERE ( sym.n < 0 AND (datp.i IN (SELECT a FROM records AS recp WHERE (recp.i = get_myvar('var')) OR (recp.rec LIKE get_myvar('var')) AND recp.i = sym.n)) AND sym.a < 0 AND (datt.i IN (SELECT a FROM records AS rect WHERE (rect.i = get_myvar('txt'))  OR (rect.rec LIKE get_myvar('txt')) AND rect.i = sym.a )))

19 sql views.

Triggers:

admin_stamp:
 CREATE TEMP TRIGGER IF NOT EXISTS admin_stamp UPDATE OF d ON admin BEGIN UPDATE admin SET t = strftime('%s','now'),u = uid() WHERE i = new.i; END;

alias_stamp:
 CREATE TEMP TRIGGER IF NOT EXISTS alias_stamp UPDATE OF d ON alias BEGIN UPDATE alias SET t = strftime('%s','now'),u = uid() WHERE i = new.i; END;

bin_stamp:
 CREATE TEMP TRIGGER IF NOT EXISTS bin_stamp UPDATE OF i ON stock BEGIN UPDATE stock SET t = strftime('%s','now'),u = uid() WHERE i = new.i; END;

data_stamp:
 CREATE TEMP TRIGGER IF NOT EXISTS data_stamp UPDATE OF d ON data BEGIN UPDATE data SET t = strftime('%s','now'),u = uid() WHERE i = new.i; END;

4 sql triggers.
 

Advertisements