2009-09-10

Create Sqlite Database by SQL Script.

Sqlite supports c/c++, Tcl and sql interfaces. Max wrote some sql scripts to create and initialize a single-table sqlite DB. Put the following three files in the same fold and execute createDB.sh.

1) createDB.sql (create a DB)

CREATE TABLE tb_master
(
Id integer PRIMARY KEY,
Name text NOT NULL UNIQUE,
Creator text,
Group_name varchar(20),
Time_stamp integer,
Snapshot blob,
Thumbnail blob,
Ingredient text,
Workflow text
);

2) demoData.sql (insert demo data)


INSERT INTO tb_master
VALUES
(
1,
'Applepipe',
'Max',
'Cake',
10,
'ddd',
'ddd',
'dddd',
'ssss'
);
INSERT INTO tb_master
VALUES
(
2,
'Chinaball',
'Jasmin',
'Soup',
11,
'ddd',
'ddd',
'dddd',
'ssss'
);



3) createDB.sh (main shell script)

#!/bin/sh
# This is a script to create a sqlite3 database "test.db"

#local variables
DB=test.db
CREATE_SCRIPT=createDB.sql
DATA_SCRIPT=demoData.sql

#remove database file if it exists.
if [ -f $DB ]
then
echo remove $DB
rm -rf $DB
fi


#create a database with sql script
echo create $DB
sqlite3 $DB < $CREATE_SCRIPT

#initialize with testing data
echo insert records
sqlite3 $DB < $DATA_SCRIPT

No comments:

Post a Comment