sql stuff

--Initial scripts to run

--recreate thesis with pre-filled values
drop table app.thesis;
CREATE TABLE app.THESIS (thesisid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), USERNAME VARCHAR(128) NOT NULL, COURSENUM INTEGER NOT NULL,
LIVELINK VARCHAR(256),KEYWORDS VARCHAR(256), ABSTRACT VARCHAR(1024), ZIPFILE VARCHAR(256), SCREENCAST VARCHAR(256), COMMITTEE VARCHAR(1024), SEMESTER VARCHAR(128),
DATEUPLOADED VARCHAR(10), views integer default 0, downloads integer default 0, CONSTRAINT thesisid PRIMARY KEY (thesisid));
INSERT INTO APP.THESIS (USERNAME, COURSENUM, LIVELINK, KEYWORDS, ABSTRACT, ZIPFILE, SCREENCAST, COMMITTEE, SEMESTER, DATEUPLOADED)
	VALUES ('Austin Savage', 353, 'https://google.com/', 'test, example, demo, thesis', 'This is an example post of a thesis. This is where the abstract will show up.', '/1/file.zip', 'https://youtube.com/', 'Austin Savage, Andrew Smith', 'Spring 2015', '04-22-2015');
INSERT INTO APP.THESIS (USERNAME, COURSENUM, LIVELINK, KEYWORDS, ABSTRACT, ZIPFILE, SCREENCAST, COMMITTEE, SEMESTER, DATEUPLOADED)
	VALUES ('Andrew Smith', 353, 'https://reddit.com/', 'test, upload, hello,', 'This is an example post of a thesis. This is where the abstract will show up.', '/2/file.zip', 'https://illinoisstate.edu/', 'Andrew Smith, Austin Savage', 'Spring 2015', '04-23-2015');
INSERT INTO APP.THESIS (USERNAME, COURSENUM, LIVELINK, KEYWORDS, ABSTRACT, ZIPFILE, SCREENCAST, COMMITTEE, SEMESTER, DATEUPLOADED)
	VALUES ('Austin Savage', 367, 'https://google.com/', 'test, example, app, georgia', 'This is an example post of a thesis. This is where the abstract will show up.', '/3/file.zip', 'https://youtube.com/', 'Austin Savage, Andrew Smith, Dr. Lim', 'Spring 2015', '04-27-2015');
	select * from app.thesis;
--create users table with pre-filled values
drop table app.users;
CREATE TABLE app.USERS (userid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FIRSTNAME VARCHAR(25) NOT NULL,
LASTNAME VARCHAR(25) NOT NULL, PASSWORD VARCHAR(25) NOT NULL, EMAIL VARCHAR(25) NOT NULL, SECURITYQ VARCHAR(128) NOT NULL, SECURITYA VARCHAR(128) NOT NULL,
REASON VARCHAR(128) NOT NULL, USERNAME VARCHAR(25) NOT NULL, APPROVED BOOLEAN DEFAULT false , ISADMIN BOOLEAN default false, loginCounter integer default 0,
 uploadCounter integer default 0, CONSTRAINT userid PRIMARY KEY (userid));
insert into app.users (firstname,lastname,password,email,securityq,securitya,reason,username,approved,isadmin)
	values('The','Admin','password','ajsmit2@ilstu.edu','What is your favorite color?','Red','Bc admin.','admin',true,true);
select * from app.users;
--to increment view counters
update app.thesis set views = views + 1 where thesisid = ?
update app.thesis set downloads = downloads + 1 where thesisid = ?
update app.users set  loginCounter = loginCounter + 1 where userid = ?
update app.users set uploadCounter = uploadCounter + 1 where userid = ?