Dumpfile02.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "table01" ( id, column01, column02 );
INSERT INTO table01 VALUES(NULL,'value01','value02');
INSERT INTO table01 VALUES(NULL,'value01','value02');
INSERT INTO table01 VALUES(NULL,'value01','contain'' commna');
INSERT INTO table01 VALUES(NULL,'value01',replace('改行\n する','\n',char(10)));
INSERT INTO table01 VALUES(NULL,'value01','value12');
INSERT INTO table01 VALUES(NULL,'value02','value12');
INSERT INTO table01 VALUES(NULL,'value03','contain'' commna');
INSERT INTO table01 VALUES(NULL,'value04',replace('改行\n する','\n',char(10)));
CREATE TABLE product(id integer, name text default 'no name',
price integer default 0);
INSERT INTO product VALUES(3,'no name',5000);
CREATE TABLE users (
id integer primary key
,name text not null
,score integer default 10 check (score >=0)
,memo text default 'user profile'
,email text unique
);
INSERT INTO users VALUES(1,'kaki%',40,'user profile','aaa@bbcbs');
INSERT INTO users VALUES(2,'ishida',80,'user profile',NULL);
INSERT INTO users VALUES(3,'yamada',90,'user profile',NULL);
INSERT INTO users VALUES(4,'koga',100,'user profile',NULL);
INSERT INTO users VALUES(5,'tagawa',110,'user profile',NULL);
INSERT INTO users VALUES(6,'ogawa',120,'user profile',NULL);
CREATE TABLE messages (message);
INSERT INTO messages VALUES('name:tagawa 80->110');
INSERT INTO messages VALUES('name:ogawa 90->120');
CREATE TABLE posts (
id integer primary key
,title text
,body text
);
INSERT INTO posts VALUES(1,'t1','b1');
INSERT INTO posts VALUES(2,'t2','b2');
CREATE TABLE comments (
id integer primary key
,post_id integer
,comment text
);
INSERT INTO comments VALUES(1,1,'c1');
INSERT INTO comments VALUES(2,1,'c2');
CREATE TABLE table02 (
id integer primary key autoincrement
,column_int integer
,column_text text
,column_real real
,column_blb blb
,column_null
);
INSERT INTO table02 VALUES(3,111,NULL,NULL,NULL,NULL);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('table02',3);
CREATE TRIGGER new_winner update of score on users when new.score > 100
begin
insert into messages (message) values (
'name:' || new.name ||
' ' || old.score ||
'->' || new.score
);
end;
CREATE INDEX score_index on users(score);
COMMIT;