CONNECT 'jdbc:derby:/Users/raju/src/svn/lzproject/trunk/derby/WEB-INF/lzprojectdb';

DROP TABLE USERS;

CREATE TABLE USERS
(
	ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	LOGIN VARCHAR(24) NOT NULL,
	REAL_NAME VARCHAR(50),
	EMAIL VARCHAR(50),
	PASS VARCHAR(50),
	LAST_LOGIN TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE (LOGIN)
);

INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('laszlo', 'E439796B953BB9FB06970325219E2940', 'Laszlo Moholy-Nagy', 'laszlo.moholy@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('david', 'E439796B953BB9FB06970325219E2940', 'David Temkin', 'dave@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('john', 'E439796B953BB9FB06970325219E2940', 'John Sundman', 'johnny@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('helena', 'E439796B953BB9FB06970325219E2940', 'Helena Kimball', 'helena@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('bret', 'E439796B953BB9FB06970325219E2940', 'Bret Simister', 'bret@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('max', 'E439796B953BB9FB06970325219E2940', 'Max Carlson', 'max@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('henry', 'E439796B953BB9FB06970325219E2940', 'Henry Minsky', 'henry@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('tucker', 'E439796B953BB9FB06970325219E2940', 'PT Withington', 'tucker@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('ben', 'E439796B953BB9FB06970325219E2940', 'Ben Shine', 'ben@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('mayme', 'E439796B953BB9FB06970325219E2940', 'Mayme Kratt', 'mayme@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('amy', 'E439796B953BB9FB06970325219E2940', 'Amy Muntz', 'amy@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('josh', 'E439796B953BB9FB06970325219E2940', 'Josh Crowley', 'josh@openlaszlo.org', CURRENT_TIMESTAMP );
INSERT INTO USERS (LOGIN, PASS, REAL_NAME, EMAIL, LAST_LOGIN)
	VALUES ('phil', 'E439796B953BB9FB06970325219E2940', 'Philip Romanik', 'phil@openlaszlo.org', CURRENT_TIMESTAMP );

DROP TABLE PROJECTS;	
CREATE TABLE PROJECTS
(
	ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	NAME VARCHAR(30) NOT NULL,
	DESCRIPTION VARCHAR(1000),
	DATE_STARTED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE (ID),
	UNIQUE (NAME)
);

INSERT INTO PROJECTS (NAME, DESCRIPTION, DATE_STARTED)
	VALUES ('OpenLaszlo 4.1 Release', 'The release of the next minor version of the OpenLaszlo Server', TIMESTAMP('2006-12-20', '17.12.30'));
INSERT INTO PROJECTS (NAME, DESCRIPTION, DATE_STARTED)
	VALUES ('Project Orbit', 'J2ME player for OpenLaszlo DHTML runtime.', TIMESTAMP('2006-10-3', '03.20.07'));
INSERT INTO PROJECTS (NAME, DESCRIPTION, DATE_STARTED)
	VALUES ('OpenLaszlo iPhone examle app', 'Development of an iPhone example app at <a href="http://barcamp.org/iPhoneDevCamp">iPhoneDevCamp</a>.', TIMESTAMP('2007-04-10', '20.29.53'));
INSERT INTO PROJECTS (NAME, DESCRIPTION, DATE_STARTED)
	VALUES ('OpenLaszlo 4.0 Release', 'Release of OpenLaszlo 4.0, the first release with multiple runtime support around Ajax World in New York', TIMESTAMP('2006-03-01', '17.12.30'));

DROP TABLE TASKS;	
CREATE TABLE TASKS
(
	ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
	PROJECT_ID INTEGER NOT NULL,
	NAME VARCHAR(150) NOT NULL,
	DESCRIPTION VARCHAR(1000),
	DATE_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	DEADLINE DATE NOT NULL DEFAULT CURRENT_DATE,
	FINISHED INTEGER DEFAULT 0,	
	CONSTRAINT "PROJECT_FK" FOREIGN KEY ("PROJECT_ID") REFERENCES "PROJECTS" ("ID")	
);

INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE)
	VALUES ('OL 4.1 Website changes', 1, 'All the changes on the OpenLaszlo.org website changes for OpenLaszlo 4.1', CURRENT_TIMESTAMP, CURRENT_DATE);
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE)
	VALUES ('DHTML Performance Optimization', 1, 'DHTML runtime performance optimization.', CURRENT_TIMESTAMP, DATE('2007-10-03'));
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE)
	VALUES ('Flash Performance Optimization', 1, 'Here can be a more complex description of the task in the future.', CURRENT_TIMESTAMP, DATE('2007-09-13'));
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE)
	VALUES ('Update Build Instructions', 2, 'Update the Wiki page with the build instruction', CURRENT_TIMESTAMP, CURRENT_DATE);
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE)
	VALUES ('Java bytecode requirements', 2, 'Discuss the technical requirements of a Java bytecode runtime.', CURRENT_TIMESTAMP, DATE('2007-06-25'));
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE)
	VALUES ('Interface prototypes', 3, 'Develop interface prototypes/dummies for the mobile Laszlo Webtop.', CURRENT_TIMESTAMP, DATE('2007-12-01'));
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE, FINISHED)
	VALUES ('Website changes for 4.0', 4, 'OpenLaszlo.org website changes for the 4.0 release on March 20th.', CURRENT_TIMESTAMP, DATE('2007-03-01'), 1);
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE, FINISHED)
	VALUES ('Community announcements', 4, 'Communtiy anouncements for Webtop and OpenLaszlo 4.0.', CURRENT_TIMESTAMP, DATE('2007-03-18'), 1);	
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE, FINISHED)
	VALUES ('Wiki Updates', 4, 'Check the Wiki for content not up-to-date', CURRENT_TIMESTAMP, DATE('2007-03-28'), 0);
INSERT INTO TASKS (NAME, PROJECT_ID, DESCRIPTION, DATE_CREATED, DEADLINE, FINISHED)
	VALUES ('Release party', 4, 'A huge OL 4.0 release party with Pizza, Beer and the OL team.', CURRENT_TIMESTAMP, DATE('2007-03-30'), 1);
