#!/bin/sh
# -*-Fundamental-*-
set -e

##### Config

# for chinacat & peggyo:

#. config.sh

echo "##### Database"

mysql <<HERE;

DROP DATABASE IF EXISTS p4dbm;

CREATE DATABASE p4dbm;

USE p4dbm;

### We still need to verify and optimize all of the types!!! ###
### In particualr, all VARCHAR(32) and (255)'s!              ###

## secondary keys?
## time & date formats?
## name entity lengths?

SHOW TABLES;

#$ROOT/xp4load $ROOT/test1_data

CREATE TABLE _boddate(
	_key		VARCHAR(32),
	_attr		INTEGER UNSIGNED NOT NULL,
	_date		DATETIME
);
CREATE TABLE _bodtext(
	_key		VARCHAR(32),
	_attr		INTEGER UNSIGNED NOT NULL,
	_text		BLOB
);


CREATE TABLE _change(
	_change		INTEGER UNSIGNED,
	_desckey	INTEGER UNSIGNED,
	_client		VARCHAR(32),
	_user		VARCHAR(32),
	_date		DATETIME,
	_status		VARCHAR(32),
	_description	VARCHAR(31)
);	
CREATE TABLE _changex(
	_change		INTEGER UNSIGNED,
	_desckey	INTEGER UNSIGNED,
	_client		VARCHAR(32),
	_user		VARCHAR(32),
	_date		DATETIME,
	_status		VARCHAR(32),
	_description	VARCHAR(31)
);	


CREATE TABLE _counters(
	_name		VARCHAR(32),
	_value		INTEGER UNSIGNED NOT NULL
);


CREATE TABLE _depot(
	_name		VARCHAR(32),
	_type		INTEGER UNSIGNED,
	_address	VARCHAR(128),
	_map		VARCHAR(128)
);


CREATE TABLE _desc(
	_descKey	INTEGER UNSIGNED,
	_description	BLOB
);


CREATE TABLE _domain(
	_name		VARCHAR(32),
	_type		CHAR(1),
	_host		VARCHAR(128),
	_mount		VARCHAR(128),
	_mount2		VARCHAR(128),
	_mount3		VARCHAR(128),
	_owner		VARCHAR(32),
	_updateDate	DATETIME,
	_accessDate	DATETIME,
	_options	SMALLINT UNSIGNED,
	_mapstate	TINYINT UNSIGNED,
	_descripton	BLOB
);


CREATE TABLE _fix(
	_job		VARCHAR(32),
	_change		INTEGER UNSIGNED,
	_date		DATETIME,
	_status		VARCHAR(32),
	_effect		VARCHAR(32),
	_presence	VARCHAR(32),
	_client		VARCHAR(32),
	_user		VARCHAR(32)
);
CREATE TABLE _fixrev(
	_job		VARCHAR(32),
	_change		INTEGER UNSIGNED,
	_date		DATETIME,
	_status		VARCHAR(32),
	_client		VARCHAR(32),
	_user		VARCHAR(32)
);


CREATE TABLE _group(
	_user		VARCHAR(32),
	_group		VARCHAR(32),
	_isSubGroup	TINYINT UNSIGNED,
	_maxResults	INTEGER UNSIGNED,
	_maxScanRows	INTEGER UNSIGNED
);


CREATE TABLE _have(
	_clientFile	VARCHAR(128),
	_depotFile	VARCHAR(128),
	_haveRev	INTEGER UNSIGNED,
	_type		SMALLINT UNSIGNED
);


CREATE TABLE _integ(
	_toFile		VARCHAR(128),
	_fromFile	VARCHAR(128),
	_startFromRev	INTEGER UNSIGNED,
	_endFromRev	INTEGER UNSIGNED,
	_toRev		INTEGER UNSIGNED,
	_how		TINYINT UNSIGNED,
	_committed	TINYINT UNSIGNED,
	_resolved	TINYINT UNSIGNED,
	_change		INTEGER UNSIGNED
);
CREATE TABLE _resolve(
	_toFile		VARCHAR(128),
	_fromFile	VARCHAR(128),
	_startFromRev	INTEGER UNSIGNED,
	_endFromRev	INTEGER UNSIGNED,
	_toRev		INTEGER UNSIGNED,
	_how		TINYINT UNSIGNED,
	_committed	TINYINT UNSIGNED,
	_resolved	TINYINT UNSIGNED,
	_change		INTEGER UNSIGNED
);


CREATE TABLE _integed(
	_toFile		VARCHAR(128),
	_fromFile	VARCHAR(128),
	_startFromRev	INTEGER UNSIGNED,
	_endFromRev	INTEGER UNSIGNED,
	_startToRev	INTEGER UNSIGNED,
	_endToRev	INTEGER UNSIGNED,
	_how		TINYINT UNSIGNED,
	_change		INTEGER UNSIGNED
);


CREATE TABLE _ixdate(
	_date		DATETIME,
	_attr		INTEGER UNSIGNED,
	_value		VARCHAR(255)
);
CREATE TABLE _ixtext(
	_word		BLOB,
	_attr		INTEGER UNSIGNED,
	_value		VARCHAR(255)
);


CREATE TABLE _job(
	_job		VARCHAR(32),
	_xuser		VARCHAR(32),
	_xdate		DATETIME,
	_xstatus	INTEGER UNSIGNED,
	_description	VARCHAR(31)
);


# OBS 98.2
#
CREATE TABLE _jobpend(
	_job		VARCHAR(32),
	_xuser		VARCHAR(32),
	_xdate		DATETIME,
	_xstatus	INTEGER UNSIGNED,
	_description	VARCHAR(31)
);


# OBS 98.2
#
CREATE TABLE _jobdesc(
	_xjob		VARCHAR(32),
	_xdescription	BLOB
);


CREATE TABLE _label(
	_name		VARCHAR(32),
	_depotFile	VARCHAR(128),
	_rev		INTEGER UNSIGNED
);


CREATE TABLE _locks(
	_depotFile	VARCHAR(128),
	_client		VARCHAR(32),
	_user		VARCHAR(32),
	_action		TINYINT UNSIGNED,
	_isLocked	TINYINT UNSIGNED
);
	

CREATE TABLE _logger(
	_seq		INTEGER UNSIGNED,
	_key		VARCHAR(8),
	_attr		VARCHAR(32)
);


CREATE TABLE _message(
	_language	VARCHAR(32),
	_id		INTEGER UNSIGNED,
	_message	VARCHAR(255)
);


CREATE TABLE _protect(
	_seq		INTEGER UNSIGNED,
	_isGroup	TINYINT UNSIGNED,
	_user		VARCHAR(32),
	_host		VARCHAR(32),
	_perm		TINYINT UNSIGNED,
	_mapFlag	TINYINT UNSIGNED,
	_depotFile	VARCHAR(128)
);


CREATE TABLE _rev(
	_depotFile	VARCHAR(128),
	_depotRev	INTEGER UNSIGNED,
	_type		SMALLINT UNSIGNED,
	_action		TINYINT UNSIGNED,
	_change		INTEGER UNSIGNED,
	_date		DATETIME,
	_modTime	DATETIME,
	_digest		VARCHAR(32),
	_lbrFile	VARCHAR(128),
	_lbrRev		INTEGER UNSIGNED,
	_lbrType	SMALLINT UNSIGNED
);


CREATE TABLE _revcx(
	_change		INTEGER,
	_depotFile	VARCHAR(128),
	_depotRev	INTEGER UNSIGNED,
	_action		TINYINT UNSIGNED
);


CREATE TABLE _review(
	_user		VARCHAR(32),
	_seq		INTEGER,
	_mapFlag	TINYINT UNSIGNED,
	_depotFile	VARCHAR(128),
	_reviewer	TINYINT UNSIGNED
);


CREATE TABLE _trigger(
	_seq		INTEGER UNSIGNED,
	_trigger	BLOB,
	_mapFlag	TINYINT UNSIGNED,
	_depotFile	VARCHAR(128),
	_action		BLOB
);


CREATE TABLE _user(
	_user		VARCHAR(32),
	_email		VARCHAR(255),
	_jobView	BLOB,
	_updateDate	DATETIME,
	_accessDate	DATETIME,
	_fullName	VARCHAR(255),
	_password	VARCHAR(32)
);


CREATE TABLE _view(
	_domain		VARCHAR(32),
	_seq		INTEGER UNSIGNED,
	_mapFlag	TINYINT UNSIGNED,
	_viewFile	VARCHAR(128),
	_depotFile	VARCHAR(128)
);


CREATE TABLE _working(
	_clientFile	VARCHAR(128),
	_depotFile	VARCHAR(128),
	_client		VARCHAR(32),
	_user		VARCHAR(32),
	_haveRev	INTEGER UNSIGNED,
	_workRev	INTEGER UNSIGNED,
	_isVirtual	TINYINT UNSIGNED,
	_type		SMALLINT UNSIGNED,
	_action		TINYINT UNSIGNED,
	_change		INTEGER UNSIGNED,
	_date		DATETIME,
	_isLocked	TINYINT UNSIGNED
);

HERE
##### END DATABASE