Some cleanups and code dedup of Top and FetchFirst limit dialects

[dbsrgits/DBIx-Class.git] / t / lib / sqlite.sql

-- 
-- Created by SQL::Translator::Producer::SQLite
-- Created on Fri Mar  2 18:22:33 2012
-- 

--
-- Table: artist
--
CREATE TABLE artist (
  artistid INTEGER PRIMARY KEY NOT NULL,
  name varchar(100),
  rank integer NOT NULL DEFAULT 13,
  charfield char(10)
);

CREATE INDEX artist_name_hookidx ON artist (name);

CREATE UNIQUE INDEX artist_name ON artist (name);

CREATE UNIQUE INDEX u_nullable ON artist (charfield, rank);

--
-- Table: bindtype_test
--
CREATE TABLE bindtype_test (
  id INTEGER PRIMARY KEY NOT NULL,
  bytea blob,
  blob blob,
  clob clob,
  a_memo memo
);

--
-- Table: collection
--
CREATE TABLE collection (
  collectionid INTEGER PRIMARY KEY NOT NULL,
  name varchar(100) NOT NULL
);

--
-- Table: encoded
--
CREATE TABLE encoded (
  id INTEGER PRIMARY KEY NOT NULL,
  encoded varchar(100)
);

--
-- Table: event
--
CREATE TABLE event (
  id INTEGER PRIMARY KEY NOT NULL,
  starts_at date NOT NULL,
  created_on timestamp NOT NULL,
  varchar_date varchar(20),
  varchar_datetime varchar(20),
  skip_inflation datetime,
  ts_without_tz datetime
);

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

--
-- Table: genre
--
CREATE TABLE genre (
  genreid INTEGER PRIMARY KEY NOT NULL,
  name varchar(100) NOT NULL
);

CREATE UNIQUE INDEX genre_name ON genre (name);

--
-- Table: link
--
CREATE TABLE link (
  id INTEGER PRIMARY KEY NOT NULL,
  url varchar(100),
  title varchar(100)
);

--
-- Table: money_test
--
CREATE TABLE money_test (
  id INTEGER PRIMARY KEY NOT NULL,
  amount money
);

--
-- Table: noprimarykey
--
CREATE TABLE noprimarykey (
  foo integer NOT NULL,
  bar integer NOT NULL,
  baz integer NOT NULL
);

CREATE UNIQUE INDEX foo_bar ON noprimarykey (foo, bar);

--
-- Table: onekey
--
CREATE TABLE onekey (
  id INTEGER PRIMARY KEY NOT NULL,
  artist integer NOT NULL,
  cd integer NOT NULL
);

--
-- Table: owners
--
CREATE TABLE owners (
  id INTEGER PRIMARY KEY NOT NULL,
  name varchar(100) NOT NULL
);

CREATE UNIQUE INDEX owners_name ON owners (name);

--
-- Table: producer
--
CREATE TABLE producer (
  producerid INTEGER PRIMARY KEY NOT NULL,
  name varchar(100) NOT NULL
);

CREATE UNIQUE INDEX prod_name ON producer (name);

--
-- Table: self_ref
--
CREATE TABLE self_ref (
  id INTEGER PRIMARY KEY NOT NULL,
  name varchar(100) NOT NULL
);

--
-- Table: sequence_test
--
CREATE TABLE sequence_test (
  pkid1 integer NOT NULL,
  pkid2 integer NOT NULL,
  nonpkid integer NOT NULL,
  name varchar(100),
  PRIMARY KEY (pkid1, pkid2)
);

--
-- Table: serialized
--
CREATE TABLE serialized (
  id INTEGER PRIMARY KEY NOT NULL,
  serialized text NOT NULL
);

--
-- Table: timestamp_primary_key_test
--
CREATE TABLE timestamp_primary_key_test (
  id timestamp NOT NULL DEFAULT current_timestamp,
  PRIMARY KEY (id)
);

--
-- Table: treelike
--
CREATE TABLE treelike (
  id INTEGER PRIMARY KEY NOT NULL,
  parent integer,
  name varchar(100) NOT NULL
);

CREATE INDEX treelike_idx_parent ON treelike (parent);

--
-- Table: twokeytreelike
--
CREATE TABLE twokeytreelike (
  id1 integer NOT NULL,
  id2 integer NOT NULL,
  parent1 integer NOT NULL,
  parent2 integer NOT NULL,
  name varchar(100) NOT NULL,
  PRIMARY KEY (id1, id2)
);

CREATE INDEX twokeytreelike_idx_parent1_parent2 ON twokeytreelike (parent1, parent2);

CREATE UNIQUE INDEX tktlnameunique ON twokeytreelike (name);

--
-- Table: typed_object
--
CREATE TABLE typed_object (
  objectid INTEGER PRIMARY KEY NOT NULL,
  type varchar(100) NOT NULL,
  value varchar(100) NOT NULL
);

--
-- Table: artist_undirected_map
--
CREATE TABLE artist_undirected_map (
  id1 integer NOT NULL,
  id2 integer NOT NULL,
  PRIMARY KEY (id1, id2)
);

CREATE INDEX artist_undirected_map_idx_id1 ON artist_undirected_map (id1);

CREATE INDEX artist_undirected_map_idx_id2 ON artist_undirected_map (id2);

--
-- Table: bookmark
--
CREATE TABLE bookmark (
  id INTEGER PRIMARY KEY NOT NULL,
  link integer
);

CREATE INDEX bookmark_idx_link ON bookmark (link);

--
-- Table: books
--
CREATE TABLE books (
  id INTEGER PRIMARY KEY NOT NULL,
  source varchar(100) NOT NULL,
  owner integer NOT NULL,
  title varchar(100) NOT NULL,
  price integer
);

CREATE INDEX books_idx_owner ON books (owner);

CREATE UNIQUE INDEX books_title ON books (title);

--
-- Table: employee
--
CREATE TABLE employee (
  employee_id INTEGER PRIMARY KEY NOT NULL,
  position integer NOT NULL,
  group_id integer,
  group_id_2 integer,
  group_id_3 integer,
  name varchar(100),
  encoded integer
);

CREATE INDEX employee_idx_encoded ON employee (encoded);

--
-- Table: forceforeign
--
CREATE TABLE forceforeign (
  artist INTEGER PRIMARY KEY NOT NULL,
  cd integer NOT NULL
);

--
-- Table: self_ref_alias
--
CREATE TABLE self_ref_alias (
  self_ref integer NOT NULL,
  alias integer NOT NULL,
  PRIMARY KEY (self_ref, alias)
);

CREATE INDEX self_ref_alias_idx_alias ON self_ref_alias (alias);

CREATE INDEX self_ref_alias_idx_self_ref ON self_ref_alias (self_ref);

--
-- Table: track
--
CREATE TABLE track (
  trackid INTEGER PRIMARY KEY NOT NULL,
  cd integer NOT NULL,
  position int NOT NULL,
  title varchar(100) NOT NULL,
  last_updated_on datetime,
  last_updated_at datetime
);

CREATE INDEX track_idx_cd ON track (cd);

CREATE UNIQUE INDEX track_cd_position ON track (cd, position);

CREATE UNIQUE INDEX track_cd_title ON track (cd, title);

--
-- Table: cd
--
CREATE TABLE cd (
  cdid INTEGER PRIMARY KEY NOT NULL,
  artist integer NOT NULL,
  title varchar(100) NOT NULL,
  year varchar(100) NOT NULL,
  genreid integer,
  single_track integer
);

CREATE INDEX cd_idx_artist ON cd (artist);

CREATE INDEX cd_idx_genreid ON cd (genreid);

CREATE INDEX cd_idx_single_track ON cd (single_track);

CREATE UNIQUE INDEX cd_artist_title ON cd (artist, title);

--
-- Table: collection_object
--
CREATE TABLE collection_object (
  collection integer NOT NULL,
  object integer NOT NULL,
  PRIMARY KEY (collection, object)
);

CREATE INDEX collection_object_idx_collection ON collection_object (collection);

CREATE INDEX collection_object_idx_object ON collection_object (object);

--
-- Table: lyrics
--
CREATE TABLE lyrics (
  lyric_id INTEGER PRIMARY KEY NOT NULL,
  track_id integer NOT NULL
);

CREATE INDEX lyrics_idx_track_id ON lyrics (track_id);

--
-- Table: cd_artwork
--
CREATE TABLE cd_artwork (
  cd_id INTEGER PRIMARY KEY NOT NULL
);

--
-- Table: liner_notes
--
CREATE TABLE liner_notes (
  liner_id INTEGER PRIMARY KEY NOT NULL,
  notes varchar(100) NOT NULL
);

--
-- Table: lyric_versions
--
CREATE TABLE lyric_versions (
  id INTEGER PRIMARY KEY NOT NULL,
  lyric_id integer NOT NULL,
  text varchar(100) NOT NULL
);

CREATE INDEX lyric_versions_idx_lyric_id ON lyric_versions (lyric_id);

--
-- Table: tags
--
CREATE TABLE tags (
  tagid INTEGER PRIMARY KEY NOT NULL,
  cd integer NOT NULL,
  tag varchar(100) NOT NULL
);

CREATE INDEX tags_idx_cd ON tags (cd);

CREATE UNIQUE INDEX tagid_cd ON tags (tagid, cd);

CREATE UNIQUE INDEX tagid_cd_tag ON tags (tagid, cd, tag);

CREATE UNIQUE INDEX tags_tagid_tag ON tags (tagid, tag);

CREATE UNIQUE INDEX tags_tagid_tag_cd ON tags (tagid, tag, cd);

--
-- Table: cd_to_producer
--
CREATE TABLE cd_to_producer (
  cd integer NOT NULL,
  producer integer NOT NULL,
  attribute integer,
  PRIMARY KEY (cd, producer)
);

CREATE INDEX cd_to_producer_idx_cd ON cd_to_producer (cd);

CREATE INDEX cd_to_producer_idx_producer ON cd_to_producer (producer);

--
-- Table: images
--
CREATE TABLE images (
  id INTEGER PRIMARY KEY NOT NULL,
  artwork_id integer NOT NULL,
  name varchar(100) NOT NULL,
  data blob
);

CREATE INDEX images_idx_artwork_id ON images (artwork_id);

--
-- Table: twokeys
--
CREATE TABLE twokeys (
  artist integer NOT NULL,
  cd integer NOT NULL,
  PRIMARY KEY (artist, cd)
);

CREATE INDEX twokeys_idx_artist ON twokeys (artist);

--
-- Table: artwork_to_artist
--
CREATE TABLE artwork_to_artist (
  artwork_cd_id integer NOT NULL,
  artist_id integer NOT NULL,
  PRIMARY KEY (artwork_cd_id, artist_id)
);

CREATE INDEX artwork_to_artist_idx_artist_id ON artwork_to_artist (artist_id);

CREATE INDEX artwork_to_artist_idx_artwork_cd_id ON artwork_to_artist (artwork_cd_id);

--
-- Table: fourkeys_to_twokeys
--
CREATE TABLE fourkeys_to_twokeys (
  f_foo integer NOT NULL,
  f_bar integer NOT NULL,
  f_hello integer NOT NULL,
  f_goodbye integer NOT NULL,
  t_artist integer NOT NULL,
  t_cd integer NOT NULL,
  autopilot character NOT NULL,
  pilot_sequence integer,
  PRIMARY KEY (f_foo, f_bar, f_hello, f_goodbye, t_artist, t_cd)
);

CREATE INDEX fourkeys_to_twokeys_idx_f_foo_f_bar_f_hello_f_goodbye ON fourkeys_to_twokeys (f_foo, f_bar, f_hello, f_goodbye);

CREATE INDEX fourkeys_to_twokeys_idx_t_artist_t_cd ON fourkeys_to_twokeys (t_artist, t_cd);

--
-- View: year2000cds
--
CREATE VIEW year2000cds AS
    SELECT cdid, artist, title, year, genreid, single_track FROM cd WHERE year = "2000";