1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/visualweb.dataconnectivity/release/startup/samples/create-schema-vir-derby.sql Thu Apr 19 01:21:06 2007 +0000
1.3 @@ -0,0 +1,8 @@
1.4 +-- Vehicle Incident Report: create user and schema
1.5 +-- CREATE USER vir PASSWORD vir
1.6 +CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
1.7 + 'derby.user.vir', 'vir');
1.8 +-- new DROP SCHEMA will fail on a new install - That's OK for a new install.
1.9 +DROP SCHEMA vir RESTRICT;
1.10 +CREATE SCHEMA vir;
1.11 +
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
2.2 +++ b/visualweb.dataconnectivity/release/startup/samples/create-vir-derby.sql Thu Apr 19 01:21:06 2007 +0000
2.3 @@ -0,0 +1,159 @@
2.4 +-- Vehicle Incident Report (assumes you already have a vir schema)
2.5 +-- create tables
2.6 +
2.7 +-- Create validation table
2.8 +CREATE TABLE vir.validation (
2.9 + id CHAR(1) NOT NULL,
2.10 + CONSTRAINT vir_validation_pk PRIMARY KEY ( id )
2.11 +);
2.12 +
2.13 +-- Create employee table
2.14 +CREATE TABLE vir.employee (
2.15 + id INTEGER NOT NULL,
2.16 + firstname VARCHAR(40) NOT NULL,
2.17 + lastname VARCHAR(40) NOT NULL,
2.18 + email VARCHAR(40) NOT NULL,
2.19 + CONSTRAINT vir_employee_pk PRIMARY KEY ( id )
2.20 +);
2.21 +
2.22 +-- Create password table
2.23 +CREATE TABLE vir.password (
2.24 + id INTEGER NOT NULL,
2.25 + password VARCHAR(10) NOT NULL,
2.26 + CONSTRAINT vir_password_pk PRIMARY KEY ( id ),
2.27 + CONSTRAINT vir_password_id_fk FOREIGN KEY ( id )
2.28 + REFERENCES vir.employee ( id )
2.29 +);
2.30 +
2.31 +-- Create state table
2.32 +CREATE TABLE vir.state (
2.33 + stateid CHAR(2) NOT NULL,
2.34 + statename VARCHAR(40) NOT NULL,
2.35 + CONSTRAINT vir_state_pk PRIMARY KEY ( stateid )
2.36 +);
2.37 +
2.38 +-- Create vehicle table
2.39 +CREATE TABLE vir.vehicle (
2.40 + stateid CHAR(2) NOT NULL,
2.41 + licenseplate VARCHAR(10) NOT NULL,
2.42 + make VARCHAR(40) NOT NULL,
2.43 + model VARCHAR(40) NOT NULL,
2.44 + color VARCHAR(40) NOT NULL,
2.45 + CONSTRAINT vir_vehicle_pk PRIMARY KEY ( stateid, licenseplate ),
2.46 + CONSTRAINT vir_vehicle_stateid_fk_vehicle FOREIGN KEY ( stateid )
2.47 + REFERENCES vir.state ( stateid )
2.48 +);
2.49 +
2.50 +-- Create owner table
2.51 +CREATE TABLE vir.owner (
2.52 + stateid CHAR(2) NOT NULL,
2.53 + licenseplate VARCHAR(10) NOT NULL,
2.54 + employeeid INTEGER NOT NULL,
2.55 + CONSTRAINT vir_vehicle_stateid_fk_owner FOREIGN KEY ( stateid )
2.56 + REFERENCES vir.state ( stateid ),
2.57 + CONSTRAINT vir_vehicle_employeeid_fk FOREIGN KEY ( employeeid )
2.58 + REFERENCES vir.employee ( id )
2.59 +);
2.60 +
2.61 +-- insert data into tables
2.62 +
2.63 +-- Populate employee data
2.64 +INSERT INTO vir.employee VALUES (1 , 'John' , 'Doe' , 'John.Doe@johndoe.net' );
2.65 +INSERT INTO vir.employee VALUES (2 , 'Jane' , 'Doe' , 'Jane.Doe@janedoe.net' );
2.66 +INSERT INTO vir.employee VALUES (3 , 'Jack' , 'Doe' , 'Jack.Doe@jackdoe.net' );
2.67 +INSERT INTO vir.employee VALUES (4 , 'Jill' , 'Doe' , 'Jill.Doe@jilldoe.net' );
2.68 +INSERT INTO vir.employee VALUES (5 , 'Sally' , 'Able' , 'Sally.Able@sallyable.biz' );
2.69 +INSERT INTO vir.employee VALUES (6 , 'Zoe' , 'Zack' , 'Zoe.Zack@zoezack.net' );
2.70 +INSERT INTO vir.employee VALUES (7 , 'Sue' , 'Jacobs' , 'Sue.Jacobs@suejacobs.net' );
2.71 +
2.72 +-- Populate password data
2.73 +INSERT INTO vir.password VALUES (1 , 'johndoe' );
2.74 +INSERT INTO vir.password VALUES (2 , 'janedoe' );
2.75 +INSERT INTO vir.password VALUES (3 , 'jackdoe' );
2.76 +INSERT INTO vir.password VALUES (4 , 'jilldoe' );
2.77 +INSERT INTO vir.password VALUES (5 , 'sallyable' );
2.78 +INSERT INTO vir.password VALUES (6 , 'zoezack' );
2.79 +INSERT INTO vir.password VALUES (7 , 'suejacobs' );
2.80 +
2.81 +-- Populate state data
2.82 +INSERT INTO vir.state VALUES ('xx', 'Any');
2.83 +INSERT INTO vir.state VALUES ('AL', 'Alabama');
2.84 +INSERT INTO vir.state VALUES ('AK', 'Alaska');
2.85 +INSERT INTO vir.state VALUES ('AS', 'American Samoa');
2.86 +INSERT INTO vir.state VALUES ('AZ', 'Arizona ');
2.87 +INSERT INTO vir.state VALUES ('AR', 'Arkansas');
2.88 +INSERT INTO vir.state VALUES ('CA', 'California ');
2.89 +INSERT INTO vir.state VALUES ('CO', 'Colorado');
2.90 +INSERT INTO vir.state VALUES ('CT', 'Connecticut');
2.91 +INSERT INTO vir.state VALUES ('DE', 'Delaware');
2.92 +INSERT INTO vir.state VALUES ('DC', 'District of Columbia');
2.93 +INSERT INTO vir.state VALUES ('FM', 'Federated States of Micronesia');
2.94 +INSERT INTO vir.state VALUES ('FL', 'Florida');
2.95 +INSERT INTO vir.state VALUES ('GA', 'Georgia');
2.96 +INSERT INTO vir.state VALUES ('GU', 'Guam ');
2.97 +INSERT INTO vir.state VALUES ('HI', 'Hawaii');
2.98 +INSERT INTO vir.state VALUES ('ID', 'Idaho');
2.99 +INSERT INTO vir.state VALUES ('IL', 'Illinois');
2.100 +INSERT INTO vir.state VALUES ('IN', 'Indiana');
2.101 +INSERT INTO vir.state VALUES ('IA', 'Iowa');
2.102 +INSERT INTO vir.state VALUES ('KS', 'Kansas');
2.103 +INSERT INTO vir.state VALUES ('KY', 'Kentucky');
2.104 +INSERT INTO vir.state VALUES ('LA', 'Louisiana');
2.105 +INSERT INTO vir.state VALUES ('ME', 'Maine');
2.106 +INSERT INTO vir.state VALUES ('MH', 'Marshall Islands');
2.107 +INSERT INTO vir.state VALUES ('MD', 'Maryland');
2.108 +INSERT INTO vir.state VALUES ('MA', 'Massachusetts');
2.109 +INSERT INTO vir.state VALUES ('MI', 'Michigan');
2.110 +INSERT INTO vir.state VALUES ('MN', 'Minnesota');
2.111 +INSERT INTO vir.state VALUES ('MS', 'Mississippi');
2.112 +INSERT INTO vir.state VALUES ('MO', 'Missouri');
2.113 +INSERT INTO vir.state VALUES ('MT', 'Montana');
2.114 +INSERT INTO vir.state VALUES ('NE', 'Nebraska');
2.115 +INSERT INTO vir.state VALUES ('NV', 'Nevada');
2.116 +INSERT INTO vir.state VALUES ('NH', 'New Hampshire');
2.117 +INSERT INTO vir.state VALUES ('NJ', 'New Jersey');
2.118 +INSERT INTO vir.state VALUES ('NM', 'New Mexico');
2.119 +INSERT INTO vir.state VALUES ('NY', 'New York');
2.120 +INSERT INTO vir.state VALUES ('NC', 'North Carolina');
2.121 +INSERT INTO vir.state VALUES ('ND', 'North Dakota');
2.122 +INSERT INTO vir.state VALUES ('MP', 'Northern Mariana Islands');
2.123 +INSERT INTO vir.state VALUES ('OH', 'Ohio');
2.124 +INSERT INTO vir.state VALUES ('OK', 'Oklahoma');
2.125 +INSERT INTO vir.state VALUES ('OR', 'Oregon');
2.126 +INSERT INTO vir.state VALUES ('PW', 'Palau');
2.127 +INSERT INTO vir.state VALUES ('PA', 'Pennsylvania');
2.128 +INSERT INTO vir.state VALUES ('PR', 'Puerto Rico');
2.129 +INSERT INTO vir.state VALUES ('RI', 'Rhode Island');
2.130 +INSERT INTO vir.state VALUES ('SC', 'South Carolina');
2.131 +INSERT INTO vir.state VALUES ('SD', 'South Dakota');
2.132 +INSERT INTO vir.state VALUES ('TN', 'Tennessee');
2.133 +INSERT INTO vir.state VALUES ('TX', 'Texas');
2.134 +INSERT INTO vir.state VALUES ('UT', 'Utah');
2.135 +INSERT INTO vir.state VALUES ('VT', 'Vermont');
2.136 +INSERT INTO vir.state VALUES ('VI', 'Virgin Islands');
2.137 +INSERT INTO vir.state VALUES ('VA', 'Virginia ');
2.138 +INSERT INTO vir.state VALUES ('WA', 'Washington');
2.139 +INSERT INTO vir.state VALUES ('WV', 'West Virginia');
2.140 +INSERT INTO vir.state VALUES ('WI', 'Wisconsin');
2.141 +INSERT INTO vir.state VALUES ('WY', 'Wyoming');
2.142 +
2.143 +-- Populate vehicle data
2.144 +INSERT INTO vir.vehicle VALUES ('CA' , 'Sun' , 'Porsche' , 'Boxster' , 'Red');
2.145 +INSERT INTO vir.vehicle VALUES ('HI' , 'Aloha' , 'Ferrari' , 'GT' , 'Orange');
2.146 +INSERT INTO vir.vehicle VALUES ('CA' , 'Surf' , 'Lexus' , '300' , 'Black');
2.147 +INSERT INTO vir.vehicle VALUES ('CA' , 'Sands' , 'Jaguar' , 'XJ8' , 'Yellow');
2.148 +INSERT INTO vir.vehicle VALUES ('AZ' , 'Beamer' , 'BMW' , '325i' , 'Silver');
2.149 +INSERT INTO vir.vehicle VALUES ('NV' , 'Surf' , 'Lexus' , '300' , 'White');
2.150 +INSERT INTO vir.vehicle VALUES ('NV' , 'Mine' , 'Honda' , 'Accord' , 'Black');
2.151 +INSERT INTO vir.vehicle VALUES ('TX' , 'Popn' , 'Volkswagen' , 'Jetta' , 'Green');
2.152 +
2.153 +-- Populate owner data
2.154 +INSERT INTO vir.owner VALUES ('CA' , 'Sun' , 1);
2.155 +INSERT INTO vir.owner VALUES ('HI' , 'Aloha' , 1);
2.156 +INSERT INTO vir.owner VALUES ('CA' , 'Surf' , 2);
2.157 +INSERT INTO vir.owner VALUES ('CA' , 'Sands' , 3);
2.158 +INSERT INTO vir.owner VALUES ('AZ' , 'Beamer' , 4);
2.159 +INSERT INTO vir.owner VALUES ('NV' , 'Surf' , 5);
2.160 +INSERT INTO vir.owner VALUES ('NV' , 'Mine' , 6);
2.161 +INSERT INTO vir.owner VALUES ('TX' , 'Popn' , 7);
2.162 +