TiledImageMySQLDatabaseSchema < JBrowse

JBrowse > TiledImageMySQLDatabaseSchema

TWiki webs: Main | TWiki | Sandbox Log In or Register

This is the MySQL schema (written by IanHolmes and ChrisMungall) for the gdtile database used by TiledImage.

GRANT ALL ON gdtile.* TO ''@'localhost';
CREATE DATABASE gdtile;
USE gdtile;

CREATE TABLE tiledimage (
        tiledimage_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        width INT UNSIGNED,
        height INT UNSIGNED,
        PRIMARY KEY (tiledimage_id)
);

CREATE TABLE primitive (
        tiledimage_id INT UNSIGNED,
        command_order INT UNSIGNED NOT NULL,
        command TEXT,
        x0 INT UNSIGNED NOT NULL,
        y0 INT UNSIGNED NOT NULL,
        x1 INT UNSIGNED NOT NULL,
        y1 INT UNSIGNED NOT NULL,
        PRIMARY KEY (tiledimage_id, command_order)
);
CREATE INDEX primitive_track_bbox ON primitive (tiledimage_id,x0,x1,y0,y1);

CREATE TABLE global_primitive (
        tiledimage_id INT UNSIGNED,
        command_order INT UNSIGNED NOT NULL,
        command TEXT,
        PRIMARY KEY (tiledimage_id, command_order)
);
CREATE INDEX global_primitive_track ON global_primitive (tiledimage_id);

CREATE TABLE image (
        image_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        image_data BLOB,
        PRIMARY KEY (image_id)
);

SHOW tables;
DESCRIBE tiledimage;
DESCRIBE primitive;
DESCRIBE global_primitive;
DESCRIBE image;

-- AndrewUzilov - 31 Jan 2006

You can load the schema by piping it to mysql, as in:

$ cat gdtile.sql | mysql --user=root

If something goes haywire, flush the tables:

$ mysql --user=root

> USE gdtile;

> FLUSH TABLES;

and try again.

-- AndrewUzilov - 07 Feb 2006