#
# --------------------------------------------------------
#

CREATE DATABASE image_database;
USE image_database;
 

# --------------------------------------------------------

#
# Table structure for table `annotation`
#

CREATE TABLE `annotation` (
  `annotation_id` int(11) NOT NULL auto_increment,
  `annotate_image` int(10) NOT NULL default '0',
  `annotate_label` varchar(50) NOT NULL default '',
  `x1` int(4) NOT NULL default '0',
  `y1` int(4) NOT NULL default '0',
  `x2` int(4) NOT NULL default '0',
  `y2` int(4) NOT NULL default '0',
  PRIMARY KEY  (`annotation_id`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `copyright`
#

CREATE TABLE `copyright` (
  `copyright_id` int(5) NOT NULL auto_increment,
  `copyright_owner` varchar(100) default NULL,
  `allowuse` char(1) NOT NULL default 'y',
  PRIMARY KEY  (`copyright_id`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `host`
#

CREATE TABLE `host` (
  `host_id` int(10) NOT NULL auto_increment,
  `host_name` varchar(255) default NULL,
  PRIMARY KEY  (`host_id`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `image`
#

CREATE TABLE `image` (
  `id` int(10) NOT NULL auto_increment,
  `filename` tinytext NOT NULL,
  `original_filename` tinytext NOT NULL,
  `notes` text,
  `date_taken` date default NULL,
  `date_entered` date NOT NULL default '0000-00-00',
  `image_type` int(2) default NULL,
  `width` int(7) default NULL,
  `height` int(7) default NULL,
  `magnification` int(8) default NULL,
  `taxon` int(10) default NULL,
  `user` int(4) NOT NULL default '0',
  `host` int(10) default NULL,
  `copyright` int(5) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `image_keyword`
#

CREATE TABLE `image_keyword` (
  `keyword` int(10) NOT NULL default '0',
  `image` int(10) NOT NULL default '0',
  PRIMARY KEY  (`keyword`,`image`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `image_type`
#

CREATE TABLE `image_type` (
  `image_type_id` int(2) NOT NULL auto_increment,
  `description` varchar(50) default NULL,
  PRIMARY KEY  (`image_type_id`)
) TYPE=MyISAM;

INSERT INTO `image_type` (`image_type_id`,`description`)
VALUES ('','Scanning Electron Micrograph');
INSERT INTO `image_type` (`image_type_id`,`description`)
VALUES ('','Light Microscope');
INSERT INTO `image_type` (`image_type_id`,`description`)
VALUES ('','Diagram');

# --------------------------------------------------------

#
# Table structure for table `keywords`
#

CREATE TABLE `keywords` (
  `keyword_id` int(10) NOT NULL auto_increment,
  `keyword` varchar(255) default NULL,
  PRIMARY KEY  (`keyword_id`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `taxon`
#

CREATE TABLE `taxon` (
  `taxon_id` int(10) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `user_created` int(4) NOT NULL default '0',
  `is_group` char(2) NOT NULL default '',
  `taxon_type_image` int(10) default NULL,
  `path` varchar(255) default NULL,
  PRIMARY KEY  (`taxon_id`),
  UNIQUE KEY `name` (`name`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `user`
#

CREATE TABLE `user` (
  `user_id` int(4) NOT NULL auto_increment,
  `password` varchar(16) NOT NULL default '',
  `username` varchar(20) NOT NULL default '',
  `e_mail` varchar(50) NOT NULL default '',
  `forename` varchar(20) NOT NULL default '',
  `surname` varchar(20) NOT NULL default '',
  `institution` varchar(60) default NULL,
  `address` varchar(250) default NULL,
  `access` varchar(16) default NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `username` (`username`)
) TYPE=MyISAM;