--
-- 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);