# Connection: develop
# Host: localhost
# Saved: 2004-03-03 22:56:41
# 
# Connection: develop
# Host: localhost
# Saved: 2004-03-03 22:53:22
# 
# Connection: develop
# Host: localhost
# Saved: 2004-02-25 23:21:57
# 
USE TBDEVELOP;

REVOKE ALL ON TBDEVELOP.* FROM 'tbdevelop'@'localhost';
GRANT DELETE, INSERT, LOCK TABLES, SELECT, UPDATE ON TBDEVELOP.* TO 'tbdevelop'@'localhost'
IDENTIFIED BY 'sibsonpc';
DROP  INDEX IF EXISTS FP_OBJID 
DROP  INDEX IF EXISTS FP_STRING
DROP  INDEX IF EXISTS FP_INT
DROP  INDEX IF EXISTS FP_DOUBLE
DROP  INDEX IF EXISTS  FP_DATE
DROP  INDEX IF EXISTS FP_DBOREF
DROP INDEX IF EXISTS TBO_CLASS;
DROP INDEX IF EXISTS LINK_PID_CID;

DROP TABLE IF EXISTS TBOBJECT;
DROP TABLE IF EXISTS FINDPARAM;
DROP TABLE IF EXISTS LINK;
DROP TABLE IF EXISTS SEQUENCE;

CREATE TABLE TBOBJECT
(
	ID INT NOT NULL,
	CLASS	VARCHAR(70) NOT NULL,
	STATE BLOB,
	VCOUNT INT NOT NULL,
	RCOUNT INT NOT NULL,
	PRIMARY KEY ( ID )
)
TYPE=InnoDB;

CREATE INDEX TBO_CLASS ON TBOBJECT(CLASS);

CREATE TABLE IF NOT EXISTS FINDPARAM
(
	OBJID INT NOT NULL REFERENCES TBOBJECT(ID),
	NAME VARCHAR(100)	NOT NULL,
	STRING VARCHAR(100),
	INTG INT,
	FLT FLOAT(11,4),
	DT DATETIME,
	DBOREF INT REFERENCES TBOBJECT(ID)
)
TYPE=InnoDB;

CREATE INDEX FP_OBJID ON FINDPARAM ( OBJID );
CREATE INDEX FP_STRING ON FINDPARAM ( NAME, STRING );
CREATE INDEX FP_INT ON FINDPARAM ( NAME, INTG );
CREATE INDEX FP_DOUBLE ON FINDPARAM ( NAME, FLT );
CREATE INDEX FP_DATE ON FINDPARAM ( NAME, DT );
CREATE INDEX FP_DBOREF ON FINDPARAM ( NAME, DBOREF );

CREATE TABLE IF NOT EXISTS LINK
(
	PARENTID INT NOT NULL REFERENCES TBOBJECT(ID),
	CHILDID	INT NOT NULL REFERENCES TBOBJECT(ID)
)
TYPE=InnoDB;

CREATE UNIQUE INDEX LINK_PID_CID ON LINK( PARENTID, CHILDID );

CREATE TABLE SEQUENCE
(
	NEXT_ID INT NOT NULL
)
TYPE=InnoDB;

# insert one row to start increment.
INSERT INTO SEQUENCE VALUES (1);


COMMIT;