# Copyright (C) 2004  Joshua Hoblitt
#
# $Id: database_setup.txt,v 1.6 2005-11-05 02:46:32 jhoblitt Exp $

### Database Setup

### MySQL 4.1

DROP DATABASE IF EXISTS nebulous;
CREATE DATABASE nebulous;
GRANT ALL PRIVILEGES ON *.* TO 'nebulous'@'localhost'
    IDENTIFIED BY 'pixel' WITH GRANT OPTION;
FLUSH PRIVILEGES;
USE nebulous;

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,
    PRIMARY KEY(so_id),
    KEY(ext_id(64)),
) ENGINE=innodb;

DROP TABLE IF EXISTS storage_attr ;
CREATE TABLE storage_attr (
    so_id BIGINT NOT NULL AUTO_INCREMENT,
    class_id TINYINT NOT NULL,
    comment VARCHAR(255),
    read_lock TINYINT DEFAULT 0 NOT NULL,
    write_lock ENUM( 'write' ),
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    mtime TIMESTAMP,
    PRIMARY KEY(so_id),
    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,
    sha1sum CHAR(40) ASCII,
    assigned_location BOOL,
    epoch TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    mtime TIMESTAMP,
    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;

DROP TABLE IF EXISTS log;
CREATE TABLE log (
    timestamp TIMESTAMP,
    hostname VARCHAR(255),
    level VARCHAR(255),
    sub VARCHAR(255),    
    message VARCHAR(2048) NOT NULL,
    PRIMARY KEY(timestamp)
) ENGINE=innodb;


### Initialization

# localhost

$ mkdir /tmp/po01 /tmp/po02
INSERT INTO volume VALUES (1,'po01','file:/tmp/po01');
INSERT INTO volume VALUES (1,'po02','file:/tmp/po02');

# NFS

INSERT INTO volume VALUES (1,'po01','file:/netdisks/po01');
INSERT INTO volume VALUES (2,'po02','file:/netdisks/po02');
INSERT INTO volume VALUES (3,'po03','file:/netdisks/po03');
INSERT INTO volume VALUES (4,'po04','file:/netdisks/po04');
INSERT INTO volume VALUES (5,'po05','file:/netdisks/po05');
INSERT INTO volume VALUES (6,'po06','file:/netdisks/po06');
INSERT INTO volume VALUES (7,'po07','file:/netdisks/po07');
INSERT INTO volume VALUES (8,'po08','file:/netdisks/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
