allora,
creare un dtabase dalla command line di SQLite e’ chiarmente imporponibile ma…
se lanciamo la shell e digitiamo .help come la stessa ci suggerisce…
bc@hal9000:~/1966bc$ sqlite3
SQLite version 3.7.13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help
vediamo i cosi’ detti dot commands, tra questi poniamo la nostra attenzione sul comando
.read
la shell ci dice che si aspetta un file, FILENAME, e che serve a Execute SQL in FILENAME
quindi proviamo adesso questo comando.
Apriamo il nostro editor preferito, qua si usa gedit, e scriviamo
CREATE TABLE 'types' (
type_id INTEGER PRIMARY KEY,
type TEXT,
enable INTEGER DEFAULT 1
);
CREATE TABLE ‘categories’ (
category_id INTEGER PRIMARY KEY,
type_id INTEGER,
category TEXT,
description TEXT,
enable INTEGER DEFAULT 1
);
INSERT INTO ‘types’ VALUES(Null,’Sample’,1);
INSERT INTO ‘types’ VALUES(Null,’Units’,1);
INSERT INTO ‘types’ VALUES(Null,’Methods’,1);
INSERT INTO ‘categories’ VALUES(Null,1,’S’,’Serum’,1);
INSERT INTO ‘categories’ VALUES(Null,1,’P’,’Plasma’,1);
INSERT INTO ‘categories’ VALUES(Null,1,’U’,’Urine’,1);
INSERT INTO ‘categories’ VALUES(Null,1,’B’,’Blood’,1);
INSERT INTO ‘categories’ VALUES(Null,2,’mg/dl’,’milligrammi/decilitro’,1);
INSERT INTO ‘categories’ VALUES(Null,2,’mg/L’,’milligrammi/litro’,1);
INSERT INTO ‘categories’ VALUES(Null,2,’mmol/L’,’millimoli/Litro’,1);
INSERT INTO ‘categories’ VALUES(Null,3,’SFM’,’Spettrofotometrico’,1);
INSERT INTO ‘categories’ VALUES(Null,3,’CHE’,’Chemiluminescenza’,1);
INSERT INTO ‘categories’ VALUES(Null,3,’TUR’,’Turbidimetria’,1);
INSERT INTO ‘categories’ VALUES(Null,3,’PI’,’Potenzionetria Indiretta’,1);
SELECT
types.type,
categories.category,
categories.description
FROM categories
INNER JOIN types
ON categories.type_id = types.type_id
LIMIT 3;
salviamo il file come db_schema.sql
quindi dalla shell di linux lanciamo sqlite con
bc@hal9000:~/1966bc$ sqlite3 -init setconsole lims.db
questo per creare il database lims.db e da dentro eseguiamo
.read db_schema.sql
bc@hal9000:~/1966bc$ sqlite3 -init setconsole lims.db
-- Loading resources from setconsole
SQLite version 3.7.13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read db_schema.sql
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.004000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
CPU Time: user 0.000000 sys 0.000000
type category description
---------- ---------- -----------
Sample S Serum
Sample P Plasma
Sample U Urine
CPU Time: user 0.000000 sys 0.000000
sqlite>
in pratica abbiamo creato due tabelle, types e categories,
le abbiamo popolate con alcuni dati ed infine abbiamo eseguito
una query tra le due tabelle.
Per ulteriore conferma eseguiamo altri due comandi
.tables
.schema
sqlite> .tables
categories types
sqlite> .schema categories
CREATE TABLE 'categories' (
category_id INTEGER PRIMARY KEY,
type_id INTEGER,
category TEXT,
description TEXT,
enable INTEGER DEFAULT 1
);
sqlite> .schema types
CREATE TABLE 'types' (
type_id INTEGER PRIMARY KEY,
type TEST,
enable INTEGER DEFAULT 1
);
sqlite>
tempo speso per fare questa cosa, 5 minuti…
alla prossima
1966bc 