------------------------------------------------------------------------------------------------------
--
-- FILENAME : pantera_sql_create_script.txt
-- CODER    : Simon Roses Femerling
-- DATE     : 11/28/05 
-- ABSTRACT : Pantera sql scheme for Mysql 5.0
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- Install:
-- 1. create a database called 'panteradb'
-- 2. (optional) create a user to manage the database
-- 3. Import the schema: mysql -u <username> -p panteradb < pantera_sql_create__script.txt
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
-- DROP TABLES;
------------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS project_t;

DROP TABLE IF EXISTS links_t;

DROP TABLE IF EXISTS not_save_links_t;

DROP TABLE IF EXISTS person_t;

DROP TABLE IF EXISTS page_info_t;

DROP TABLE IF EXISTS project_info_t;

DROP TABLE IF EXISTS snitch_t;

-- DROP TABLE result_t;

------------------------------------------------------------------------------------------------------
-- CREATE TABLES
------------------------------------------------------------------------------------------------------

--
-- Project Table
--
CREATE TABLE project_t (
	project_id INTEGER NOT NULL AUTO_INCREMENT,
    	project_name CHAR(60) NOT NULL,
	start_date DATE,
	start_time TIME,	
	last_date DATE,
	last_time TIME,
	notes MEDIUMTEXT,	
	PRIMARY KEY (project_id)
);

--
-- Save links (links pantera works on)
--
CREATE TABLE links_t (
	link_id INTEGER NOT NULL AUTO_INCREMENT,
	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
	link_file TEXT NOT NULL, 	
	link_url TEXT,
	PRIMARY KEY (link_id)	
);

--
-- Links that we want to save but not work on (maybe images, etc.)
--
CREATE TABLE not_save_links_t (
	not_save_link_id INTEGER NOT NULL AUTO_INCREMENT,
	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
	not_save_link_file TEXT NOT NULL,
	not_save_link_url TEXT NOT NULL,
	not_save_link_date DATE,
	not_save_link_time TIME,
	method CHAR(10), 
	page_hash CHAR(50),
	return_code CHAR(5),
	return_msg TEXT,
	version CHAR(10),
	extension TEXT,
	host TEXT,
	PRIMARY KEY (not_save_link_id)	
);

--
-- Testers info table
--
CREATE TABLE person_t (
	person_id INTEGER NOT NULL AUTO_INCREMENT,
	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
	person_name CHAR(255) NOT NULL,
	person_email TEXT NOT NULL, 		
	PRIMARY KEY (person_id)		
);

--
-- Link info table
--
CREATE TABLE page_info_t (
	page_info_id INTEGER NOT NULL AUTO_INCREMENT,
	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
	link_id INTEGER NOT NULL REFERENCES links_t(link_id),
	page_date DATE,
	page_time TIME,
	have_ssl TINYINT(1),
	have_email TINYINT(1),
	have_script TINYINT(1),
	have_form TINYINT(1),
	have_auth_form TINYINT(1),
	have_cookie TINYINT(1),
	have_session_id TINYINT(1),
	have_external_link TINYINT(1),
	have_comment TINYINT(1),
	have_vuln TINYINT(1),
	have_hidden TINYINT(1),
	have_object TINYINT(1),
	have_postauth TINYINT(1),
	have_querystr TINYINT(1),
	have_auth TINYINT(1),
	auth_data TEXT,	
	vuln_data LONGTEXT,
	notes LONGTEXT,
	method CHAR(10), 
	page_hash CHAR(50),
	return_code CHAR(5),
	return_msg TEXT,
	version CHAR(10),
	extension TEXT,
	host TEXT,
	PRIMARY KEY (page_info_id)	
);

--
-- Save general info about project. (type: server, target, id_session, mail, auth_basic, auth_ntlm, cgi_dir)
--
CREATE TABLE project_info_t (
	project_info_id INTEGER NOT NULL AUTO_INCREMENT,
	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
	type TEXT,
	value TEXT,
	domain TEXT,
	project_info_date DATE,
	project_info_time TIME,
	PRIMARY KEY (project_info_id)	
);

CREATE TABLE snitch_t (
	snitch_id INTEGER NOT NULL AUTO_INCREMENT,
	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
	link_id INTEGER NOT NULL REFERENCES links_t(link_id),
	data MEDIUMBLOB,
	PRIMARY KEY (snitch_id)	
);

--CREATE TABLE result_t (
--	result_id INTEGER NOT NULL AUTO_INCREMENT,
--	project_id INTEGER NOT NULL REFERENCES project_t(project_id),
--	
--	PRIMARY KEY (result_id)	
--);

------------------------------------------------------------------------------------------------------
-- VARIABLES
------------------------------------------------------------------------------------------------------

--SET @current_project_id = 0;

------------------------------------------------------------------------------------------------------
-- TRIGGERS
------------------------------------------------------------------------------------------------------

-- This really sucks... not multiple events 

CREATE TRIGGER project_update_1_trigger
	AFTER INSERT ON links_t
	FOR EACH ROW 
		UPDATE project_t SET last_time = CURTIME(), last_date = CURDATE() WHERE project_id = @current_project_id;

CREATE TRIGGER project_update_2_trigger
	AFTER DELETE ON links_t
	FOR EACH ROW 
		UPDATE project_t SET last_time = CURTIME(), last_date = CURDATE() WHERE project_id = @current_project_id;

CREATE TRIGGER project_update_3_trigger
	AFTER UPDATE ON links_t
	FOR EACH ROW 
		UPDATE project_t SET last_time = CURTIME(), last_date = CURDATE() WHERE project_id = @current_project_id;

-- for page_info_t

--CREATE TRIGGER page_update_1_trigger
--	AFTER INSERT ON page_info_t
--	FOR EACH ROW 
--		UPDATE page_info_t SET page_time = CURTIME(), page_date = CURDATE() WHERE project_id = @current_project_id;

--CREATE TRIGGER page_update_2_trigger
--	AFTER DELETE ON page_info_t
--	FOR EACH ROW 
--		UPDATE page_info_t SET page_time = CURTIME(), page_date = CURDATE() WHERE project_id = @current_project_id;

--CREATE TRIGGER page_update_3_trigger
--	AFTER UPDATE ON page_info_t
--	FOR EACH ROW 
--		UPDATE page_info_t SET page_time = CURTIME(), page_date = CURDATE() WHERE project_id = @current_project_id;


------------------------------------------------------------------------------------------------------
-- PERMISSIONS
------------------------------------------------------------------------------------------------------

--GRANT INSERT,SELECT,UPDATE,DELETE ON project_t TO "panteradb";

--GRANT INSERT,SELECT,UPDATE,DELETE ON links_t TO "panteradb";

--GRANT INSERT,SELECT,UPDATE,DELETE ON person_t TO "panteradb";

--GRANT INSERT,SELECT,UPDATE,DELETE ON extensions_t TO "panteradb";

--GRANT INSERT,SELECT,UPDATE,DELETE ON id_sessions_t TO "panteradb";

--GRANT INSERT,SELECT,UPDATE,DELETE ON page_info_t TO "panteradb";

------------------------------------------------------------------------------------------------------
-- RL+I EOF
------------------------------------------------------------------------------------------------------