webissues-server 0.8.5-3 / usr / share / webissues-server / setup / schema-firebird.sql

-- Database Schema for Firebird

-- 
-- Table structure for attr_types
-- 

CREATE TABLE {attr_types} (
  attr_id integer NOT NULL,
  type_id integer default '0' NOT NULL,
  attr_name varchar(40) default '' NOT NULL,
  attr_def varchar(2048) NOT NULL,
  PRIMARY KEY (attr_id)
);
----
CREATE INDEX {attr_types}_type_idx ON {attr_types}(type_id);
----

-- 
-- Table structure for attr_values
-- 

CREATE TABLE {attr_values} (
  issue_id integer default '0' NOT NULL,
  attr_id integer default '0' NOT NULL,
  attr_value varchar(80) default '' NOT NULL,
  PRIMARY KEY (issue_id, attr_id)
);
----

-- 
-- Table structure for changes
-- 

CREATE TABLE {changes} (
  change_id integer default '0' NOT NULL,
  issue_id integer default '0' NOT NULL,
  attr_id integer default '0' NOT NULL,
  value_old varchar(80) default '' NOT NULL,
  value_new varchar(80) default '' NOT NULL,
  PRIMARY KEY (change_id)
);
----
CREATE INDEX {changes}_issue_idx ON {changes}(issue_id);
----

-- 
-- Table structure for comments
-- 

CREATE TABLE {comments} (
  comment_id integer default '0' NOT NULL,
  issue_id integer default '0' NOT NULL,
  comment_text blob sub_type text NOT NULL,
  PRIMARY KEY (comment_id)
);
----
CREATE INDEX {comments}_issue_idx ON {comments}(issue_id);
----

-- 
-- Table structure for files
-- 

CREATE TABLE {files} (
  file_id integer default '0' NOT NULL,
  issue_id integer default '0' NOT NULL,
  file_name varchar(40) default '' NOT NULL,
  file_size integer default '0' NOT NULL,
  file_data blob sub_type binary,
  file_descr varchar(80) default '' NOT NULL,
  file_storage smallint default '0' NOT NULL,
  PRIMARY KEY (file_id)
);
----
CREATE INDEX {files}_issue_idx ON {files}(issue_id);
----

-- 
-- Table structure for folders
-- 

CREATE TABLE folders (
  folder_id integer NOT NULL,
  project_id integer default '0' NOT NULL,
  type_id integer default '0' NOT NULL,
  folder_name varchar(40) default '' NOT NULL,
  stamp_id integer default '0' NOT NULL,
  PRIMARY KEY (folder_id)
);
----
CREATE INDEX {folders}_project_idx ON {folders}(project_id);
----

-- 
-- Table structure for issue_types
-- 

CREATE TABLE {issue_types} (
  type_id integer NOT NULL,
  type_name varchar(40) default '' NOT NULL,
  PRIMARY KEY (type_id)
);
----

-- 
-- Table structure for issues
-- 

CREATE TABLE {issues} (
  issue_id integer default '0' NOT NULL,
  folder_id integer default '0' NOT NULL,
  issue_name varchar(80) default '' NOT NULL,
  stamp_id integer default '0' NOT NULL,
  PRIMARY KEY (issue_id)
);
----
CREATE INDEX {issues}_folder_idx ON {issues}(folder_id);
----

-- 
-- Table structure for projects
-- 

CREATE TABLE projects (
  project_id integer NOT NULL,
  project_name varchar(40) default '' NOT NULL,
  PRIMARY KEY (project_id)
);
----

-- 
-- Table structure for rights
-- 

CREATE TABLE {rights} (
  project_id integer default '0' NOT NULL,
  user_id integer default '0' NOT NULL,
  project_access smallint default '0' NOT NULL,
  PRIMARY KEY (project_id, user_id)
);
----
CREATE INDEX {rights}_user_idx ON {rights}(user_id);
----

-- 
-- Table structure for server
-- 

CREATE TABLE {server} (
  server_name varchar(40) default '' NOT NULL,
  server_uuid char(36) default '' NOT NULL,
  db_version varchar(20) default '' NOT NULL,
  last_cron integer
);
----

-- 
-- Table structure for stamps
-- 

CREATE TABLE {stamps} (
  stamp_id integer NOT NULL,
  user_id integer default '0' NOT NULL,
  stamp_time integer default '0' NOT NULL,
  PRIMARY KEY (stamp_id)
);
----

-- 
-- Table structure for users
-- 

CREATE TABLE users (
  user_id integer NOT NULL,
  user_login varchar(40) default '' NOT NULL,
  user_name varchar(40) default '' NOT NULL,
  user_passwd char(32) default '' NOT NULL,
  user_access smallint default '0' NOT NULL,
  PRIMARY KEY (user_id),
  UNIQUE (user_login)
);
----

--
-- Generator and triggers for auto-incrementing primary keys
--

CREATE GENERATOR {gen_webissues};
----

SET GENERATOR {gen_webissues} TO 0;
----

CREATE TRIGGER {attr_types}_bi FOR {attr_types}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.attr_id IS NULL) THEN
  BEGIN
    new.attr_id = gen_id({gen_webissues}, 1);
    rdb$set_context('USER_SESSION', 'ATTR_TYPES.ATTR_ID', new.attr_id);
  END
END
----

CREATE TRIGGER {folders}_bi FOR {folders}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.folder_id IS NULL) THEN
  BEGIN
    new.folder_id = gen_id({gen_webissues}, 1);
    rdb$set_context('USER_SESSION', 'FOLDERS.FOLDER_ID', new.folder_id);
  END
END
----

CREATE TRIGGER {issue_types}_bi FOR {issue_types}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.type_id IS NULL) THEN
  BEGIN
    new.type_id = gen_id({gen_webissues}, 1);
    rdb$set_context('USER_SESSION', 'ISSUE_TYPES.TYPE_ID', new.type_id);
  END
END
----

CREATE TRIGGER {projects}_bi FOR {projects}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.project_id IS NULL) THEN
  BEGIN
    new.project_id = gen_id({gen_webissues}, 1);
    rdb$set_context('USER_SESSION', 'PROJECTS.PROJECT_ID', new.project_id);
  END
END
----

CREATE TRIGGER {stamps}_bi FOR {stamps}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.stamp_id IS NULL) THEN
  BEGIN
    new.stamp_id = gen_id({gen_webissues}, 1);
    rdb$set_context('USER_SESSION', 'STAMPS.STAMP_ID', new.stamp_id);
  END
END
----

CREATE TRIGGER {users}_bi FOR {users}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (new.user_id IS NULL) THEN
  BEGIN
    new.user_id = gen_id({gen_webissues}, 1);
    rdb$set_context('USER_SESSION', 'USERS.USER_ID', new.user_id);
  END
END
----

COMMIT;
----

-- [0.8.4-beta1]

--
-- Table structure for notify_folders
--

CREATE TABLE {notify_folders} (
  user_id integer default '0' NOT NULL,
  folder_id integer default '0' NOT NULL,
  stamp_id integer default '0' NOT NULL,
  PRIMARY KEY (user_id, folder_id)
);
----

--
-- Table structure for notify_isues
--

CREATE TABLE {notify_issues} (
  user_id integer default '0' NOT NULL,
  issue_id integer default '0' NOT NULL,
  stamp_id integer default '0' NOT NULL,
  PRIMARY KEY (user_id, issue_id)
);
----

--
-- Table structure for notify_users
--

CREATE TABLE {notify_users} (
  user_id integer default '0' NOT NULL,
  last_sent integer default '0' NOT NULL,
  last_status integer default '0' NOT NULL,
  PRIMARY KEY (user_id)
);
----

--
-- Table structure for preferences
--

CREATE TABLE {preferences} (
  user_id integer default '0' NOT NULL,
  pref_key varchar(40) default '' NOT NULL,
  pref_value varchar(2048) NOT NULL,
  PRIMARY KEY (user_id, pref_key)
);
----

COMMIT;
----