# Copyright (C) 2004  Joshua Hoblitt
#
# $Id: database_setup.txt,v 1.1.1.1 2004-12-22 02:16:23 jhoblitt Exp $

### Database Setup

DROP DATABASE IF EXISTS pixel;
CREATE DATABASE pixel;
INSERT INTO db VALUES ('%','pixel','pixel','y','y','y','y','y','y','y','y','y','y','y','y');
INSERT INTO user
(Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_priv,Alter_priv,Show_db_priv)
VALUES ('%','pixel',password('pixel'),'y','y','y','y','y','y','y','y','y');
INSERT INTO user
(Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_priv,Alter_priv,Show_db_priv)
VALUES ('localhost','pixel',password('pixel'),'y','y','y','y','y','y','y','y','y');
FLUSH PRIVILEGES;

USE pixel;

### MySQL 4.0

DROP TABLE IF EXISTS storage_object;
CREATE TABLE storage_object (
    so_id BIGINT NOT NULL AUTO_INCREMENT,
    ext_id VARCHAR(255) BINARY NOT NULL UNIQUE,
    class_id TINYINT NOT NULL,
    comment VARCHAR(255),
    read_lock TINYINT DEFAULT 0 NOT NULL,
    write_lock ENUM( 'write' ),
    epoch TIMESTAMP NOT NULL,
    PRIMARY KEY(so_id),
    KEY(ext_id(64)),
    KEY(class_id)
) ENGINE=innodb;

DROP TABLE IF EXISTS instance;
CREATE TABLE instance ( 
    ins_id BIGINT NOT NULL AUTO_INCREMENT,
    so_id BIGINT NOT NULL,
    uri VARCHAR(255) BINARY NOT NULL UNIQUE,
    sha1sum CHAR(40),
    assigned_location BOOL,
    epoch TIMESTAMP NOT NULL,
    atime TIMESTAMP NOT NULL,
    PRIMARY KEY(ins_id),
    KEY(so_id),
    KEY(uri(64))
) ENGINE=innodb;

DROP TABLE IF EXISTS lock_record;
CREATE TABLE lock_record (
        so_id BIGINT NOT NULL,
        type ENUM( 'read', 'write' ) NOT NULL,
        epoch TIMESTAMP,
        KEY(so_ID)
    ) ENGINE=innodb;

DROP TABLE IF EXISTS volume;
CREATE TABLE volume (
        vol_id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) UNIQUE BINARY NOT NULL,
        uri VARCHAR(255) BINARY NOT NULL,
        key(vol_id)
    ) ENGINE=innodb;

DROP TABLE IF EXISTS class;
CREATE TABLE class (
        class_id TINYINT NOT NULL AUTO_INCREMENT,
        instance TINYINT NOT NULL,
        priority TINYINT,
        comment VARCHAR(255),
        key(class_id)
    ) ENGINE=innodb;

### MySQL 4.1

DROP TABLE IF EXISTS storage_object;
CREATE TABLE storage_object (
        so_id BIGINT NOT NULL AUTO_INCREMENT,
        ext_id VARCHAR(255) UNIQUE BINARY NOT NULL,
        comment VARCHAR(255),
        epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY(so_id),
        KEY(ext_id(20))
    ) ENGINE=innodb;


DROP TABLE IF EXISTS instance;
CREATE TABLE instance ( 
        ins_id BIGINT NOT NULL AUTO_INCREMENT,
        so_id BIGINT NOT NULL,
        uri VARCHAR(255) BINARY NOT NULL,
        sha1sum CHAR(40) ASCII,
        read_lock TINYINT NOT NULL,
        write_lock ENUM( 'write', 'wait' ),
        atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY(ins_id),
        KEY(so_id)
    ) ENGINE=innodb;

DROP TABLE IF EXISTS lock_record;
CREATE TABLE lock_record (
        ins_id BIGINT NOT NULL,
        type ENUM( 'read', 'write', 'wait' ) NOT NULL,
        epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=innodb;

        epoch TIMESTAMP,

### Initialization

# NFS

INSERT INTO volume VALUES (1,'po01','file:/tmp/po01');
INSERT INTO volume VALUES (2,'po02','file:/tmp/po02');
INSERT INTO volume VALUES (3,'po03','file:/tmp/po03');
INSERT INTO volume VALUES (4,'po04','file:/tmp/po04');
INSERT INTO volume VALUES (5,'po05','file:/tmp/po05');
INSERT INTO volume VALUES (6,'po06','file:/tmp/po06');
INSERT INTO volume VALUES (7,'po07','file:/tmp/po07');
INSERT INTO volume VALUES (8,'po08','file:/tmp/po08');

INSERT INTO class VALUES (1,3,0,'raw image data');

### Example queries

LOAD DATA INFILE '/home/moa/jhoblitt/tmp/storage_objects.txt' INTO TABLE storage_object; 
LOAD DATA INFILE '/home/moa/jhoblitt/tmp/instances.txt' INTO TABLE instance; 

SELECT * FROM storage_object LEFT JOIN instance ON storage_object.so_id=instance.so_id WHERE storage_object.ext_id = "foobarbaz1.fits";
SELECT * FROM storage_object LEFT JOIN instance USING (so_id) WHERE storage_object.ext_id = "foobarbaz1.fits";

SET AUTOCOMMIT = 0;
BEGIN;
UPDATE instance SET read_lock=read_lock+1 WHERE ins_id=1;
INSERT INTO lock_record values (1, 'read', NULL);
COMMIT;

SET AUTOCOMMIT = 0;
BEGIN;
INSERT INTO storage_object values (NULL, 'myfooedbar.fits', 'mr. wizard made this', NULL);
INSERT INTO instance values (NULL, LAST_INSERT_ID(), 'file://tmp/bat/man/12345a', 'da39a3ee5e6b4b0d3255bfef95601890afd80709',0, NULL, NULL);
COMMIT;

IF select write_lock from instance WHERE id=1 THEN
ELSE
ENDIF
