--
-- Table: punctuated_column_name
--
CREATE TABLE punctuated_column_name (
id INTEGER PRIMARY KEY NOT NULL,
"foo ' bar" INTEGER,
'bar/baz' INTEGER,
'baz;quux' INTEGER
);
INSERT INTO punctuated_column_name ("foo ' bar", 'bar/baz', 'baz;quux') VALUES (1,2,3);
INSERT INTO punctuated_column_name ("foo ' bar", 'bar/baz', 'baz;quux') VALUES (4,5,6);
--
-- Table: fourkeys
--
CREATE TABLE fourkeys (
foo INTEGER NOT NULL,
bar INTEGER NOT NULL,
hello INTEGER NOT NULL,
goodbye INTEGER NOT NULL,
sensors CHARACTER(10) NOT NULL,
read_count int,
PRIMARY KEY (foo, bar, hello, goodbye)
);
INSERT INTO fourkeys (foo, bar, hello, goodbye, sensors) VALUES (1, 2, 3, 4, 'xxx');
INSERT INTO fourkeys (foo, bar, hello, goodbye, sensors) VALUES (5, 4, 3, 6, 'yyy');
--
-- Table: twokeys
--
CREATE TABLE twokeys (
artist INTEGER NOT NULL,
cd INTEGER NOT NULL,
PRIMARY KEY (artist, cd)
);
INSERT INTO twokeys (artist, cd) VALUES (1, 1);
INSERT INTO twokeys (artist, cd) VALUES (1, 2);
INSERT INTO twokeys (artist, cd) VALUES (2, 1);
--
-- Table: fourkeys_to_twokeys
--
CREATE TABLE fourkeys_to_twokeys (
f_foo INTEGER NOT NULL REFERENCES fourkeys(foo),
f_bar INTEGER NOT NULL REFERENCES fourkeys(bar),
f_hello INTEGER NOT NULL REFERENCES fourkeys(hello),
f_goodbye INTEGER NOT NULL REFERENCES fourkeys(goodbye),
t_artist INTEGER NOT NULL REFERENCES twokeys(artist),
t_cd INTEGER NOT NULL REFERENCES twokeys(cd),
autopilot CHARACTER NOT NULL,
pilot_sequence INTEGER,
PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
);
INSERT INTO fourkeys_to_twokeys (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd, autopilot) VALUES (1, 2, 3, 4, 1, 2, 'x');
INSERT INTO fourkeys_to_twokeys (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd, autopilot) VALUES (5, 4, 3, 6, 2, 1, 'y');
--
-- Table: dynamic_default
--
CREATE TABLE dynamic_default (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
on_create VARCHAR(100),
on_create_and_update VARCHAR(100)
);
INSERT INTO dynamic_default (name) VALUES ('Charlie');
INSERT INTO dynamic_default (name) VALUES ('Bobby');
--
-- Table: link
--
CREATE TABLE link (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url VARCHAR(100),
title VARCHAR(100)
);
INSERT INTO link (url,title) VALUES ('http://www.perl.org/','Perl');
INSERT INTO link (url,title) VALUES ('http://www.google.com/','The Chocolate Factory');
INSERT INTO link (url,title) VALUES ('http://www.amazon.com/','Amazon');
--
-- Table: bookmark
--
CREATE TABLE bookmark (
id INTEGER PRIMARY KEY AUTOINCREMENT,
link INTEGER REFERENCES link(id)
);
INSERT INTO bookmark (link) VALUES (1);
--
-- Table: bookmark_with_link_proxy
--
CREATE TABLE bookmark_with_link_proxy (
id INTEGER PRIMARY KEY AUTOINCREMENT,
link INTEGER REFERENCES link(id)
);
INSERT INTO bookmark_with_link_proxy (link) VALUES (1);
--
-- Table: unicode_test
--
CREATE TABLE unicode_test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text CHARACTER
);
INSERT INTO unicode_test (text) VALUES ('ééé');
INSERT INTO unicode_test (text) VALUES ('®ç');
--
-- Table: noprimarykey
--
CREATE TABLE noprimarykey (
foo integer NOT NULL,
bar integer NOT NULL,
baz integer NOT NULL
);
INSERT INTO noprimarykey (foo,bar,baz) VALUES (1,2,3);
INSERT INTO noprimarykey (foo,bar,baz) VALUES (4,5,6);
INSERT INTO noprimarykey (foo,bar,baz) VALUES (2,3,4);
--
-- Table: self_ref
--
CREATE TABLE self_ref (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
self_ref_id INTEGER REFERENCES self_ref(id)
);
INSERT INTO self_ref (name) VALUES ('harry');
INSERT INTO self_ref (name, self_ref_id) VALUES ('bob', 2);
INSERT INTO self_ref (name, self_ref_id) VALUES ('jim', 1);
--
-- Table: self_ref_alias
--
CREATE TABLE self_ref_alias (
self_ref INTEGER NOT NULL REFERENCES self_ref(id),
alias INTEGER NOT NULL REFERENCES self_ref(id),
PRIMARY KEY (self_ref, alias)
);
INSERT INTO self_ref_alias (self_ref,alias) VALUES (1,2);
INSERT INTO self_ref_alias (self_ref,alias) VALUES (2,1);
--
-- Table: artist
--
CREATE TABLE artist (
id INTEGER PRIMARY KEY AUTOINCREMENT,
forename VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
pseudonym VARCHAR(255),
born DATE
);
INSERT INTO artist (forename,surname,pseudonym,born) VALUES ('Mike', 'Smith', 'Alpha Artist', '1970-02-28');
INSERT INTO artist (forename,surname,pseudonym,born) VALUES ('David', 'Brown', 'Band Beta', '1992-05-30');
INSERT INTO artist (forename,surname,pseudonym,born) VALUES ('Adam', 'Smith', 'Gamma Group', '1981-05-10');
--
-- Table: artist_undirected_map
--
CREATE TABLE artist_undirected_map (
id1 INTEGER NOT NULL REFERENCES artist(id),
id2 INTEGER NOT NULL REFERENCES artist(id),
PRIMARY KEY (id1, id2)
);
INSERT INTO artist_undirected_map (id1,id2) VALUES (1,2);
INSERT INTO artist_undirected_map (id1,id2) VALUES (2,3);
--
-- View: aritsts_called_mike
--
CREATE VIEW artists_called_mike AS
SELECT * FROM artist WHERE forename = 'Mike';
--
-- Reference Tables test, from rt.cpan.org #64455
--
CREATE TABLE reference (
id INTEGER PRIMARY KEY AUTOINCREMENT
);
INSERT INTO reference (id) VALUES (1);
INSERT INTO reference (id) VALUES (2);
CREATE TABLE ref_a (
id INTEGER PRIMARY KEY AUTOINCREMENT,
reference INTEGER REFERENCES reference(id)
);
INSERT INTO ref_a (reference) VALUES (2);
CREATE TABLE ref_b (
id INTEGER PRIMARY KEY AUTOINCREMENT,
ref_a INTEGER REFERENCES ref_a(id),
reference INTEGER REFERENCES reference(id)
);
INSERT INTO ref_b (ref_a, reference) VALUES (1,1);
INSERT INTO ref_b (ref_a, reference) VALUES (1,2);