connect 'jdbc:derby:fortunes;create=true';

-- =========================================================================
-- Drop tables, if they exist
-- =========================================================================
drop table fortunes;
drop table fortune_meta;
drop table offensive;

-- =========================================================================
-- Create the metadata table that stores information about the sets of
-- fortunes.
-- =========================================================================

-- The 'name' column automatically gets a backing index because it is the PK.
create table fortune_meta
(name        varchar(24) not null constraint name_pk primary key,
 package     varchar(32) not null,
 source      varchar(64) not null,
 license     varchar(8)  not null,
 url         varchar(64)
);

--
-- << fortune-mod >>
--
insert into fortune_meta (name, package, source, license) 
values ('ascii-art',   'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('art',         'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('computers',   'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('cookie',      'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('definitions', 'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('drugs',       'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('education',   'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('ethnic',      'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('food',        'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('fortunes',    'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('goedel',      'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('humorists',   'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('kids',        'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('law',         'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('linuxcookie', 'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('literature',  'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('love',        'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('magic',       'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('medicine',    'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('men-women',   'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('miscellaneous','fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('news',        'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('people',      'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('pets',        'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('platitudes',  'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('politics',    'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('riddles',     'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('science',     'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('songs-poems', 'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('sports',      'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('startrek',    'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('translate-me','fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('wisdom',      'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('work',       'fortune-mod', 'www.gentoo.org', 'BSD');

insert into fortune_meta (name, package, source, license) 
values ('zippy',       'fortune-mod', 'www.gentoo.org', 'BSD');

--
-- << fortune-mod-kernelcookies >>
--

insert into fortune_meta values
('kernelcookies', 
 'fortune-mod-kernelcookies',
 'www.gentoo.org', 
 'GPL-2',
 'http://www.schwarzvogel.de/software-misc.shtml'
);

--
-- << fortune-mod-osfortune >>
--
insert into fortune_meta (name, package, source, license) values
('osfortune', 
 'fortune-mod-osfortune',
 'www.gentoo.org', 
 'GPL-1'
);

--
-- << fortune-mod-starwars >>
--
insert into fortune_meta values
('starwars', 
 'fortune-mod-starwars',
 'www.gentoo.org', 
 'GPL-2',
 'http://www.splitbrain.org/Fortunes/starwars/'
);

--
-- << fortune-mod-tao >>
--
insert into fortune_meta values
('tao', 
 'fortune-mod-tao',
 'www.gentoo.org', 
 'GPL-1',
 'http://aboleo.net/software/'
);

-- =========================================================================
-- Create the offensive words table
-- =========================================================================
create table offensive 
(word     varchar(20) not null constraint word_pk primary key, 
alternate varchar(20),
level     smallint not null constraint level_ck check (level > 0)
);

insert into offensive values ('[Dd][Aa][Mm][Nn]'    , 'D===', 1);
insert into offensive values ('\b[Aa][Ss][Ss]\b'    , 'A==', 2);
insert into offensive values ('\b[Cc][Oo][Cc][Kk]\b', 'C===', 3);
insert into offensive values ('[Ss][Hh][Ii][Tt]'    , 'S===', 4);
insert into offensive values ('[Ff][Uu][Cc][Kk]'    , 'F===', 5);


-- =========================================================================
-- Create the fortunes table
-- =========================================================================

-- The 'id' (PK) and 'src' (FK) columns automatically get a backing index.
create table fortunes
(id          int         not null constraint id_pk primary key,
 src         varchar(24) not null,
 offense     smallint    default 0
                         constraint off_ck check (offense >= 0),
 fortune     varchar(3000),
 foreign key(src) references fortune_meta(name)
);


-- =========================================================================
-- Create the SQL Functions
-- =========================================================================

--
-- First install the jar file (remove it first if it is there).
--
CALL SQLJ.remove_jar('APP.TutorialJar', 0);

CALL SQLJ.install_jar('fortuneServerSql.jar', 'APP.TutorialJar', 0);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.classpath', 'APP.TutorialJar');

--
-- Create the functions
--
create function tutRand(minInt int, maxInt int)
returns int
language java
parameter style java
no sql
external name 'examples.tutorial.derby.DerbyRand.randIntJakarta';

create function tutMatch(source varchar(3000), re varchar(128))
returns int
language java
parameter style java
no sql
external name 'examples.tutorial.derby.DerbyRegex.tutMatchRegexp';

create function tutReplace(source varchar(3000), re varchar(128),
change varchar(128))
returns varchar(3000)
language java
parameter style java
no sql
external name 'examples.tutorial.derby.DerbyRegex.tutReplaceRegexp';

disconnect;
