text: /*
  script generated by aqua data studio 9.0.1 on jan-30-2011 10:13:56 pm
  database: vars_kb
  schema: dbo
  objects: table, index
  
  Contributed by: Katja Loffler
  */
  alter table usage
  	drop constraint usage_fk1
  ;
  alter table sectioninfo
  	drop constraint sectioninfo_fk1
  ;
  alter table media
  	drop constraint media_fk1
  ;
  alter table linktemplate
  	drop constraint linktemplate_fk1
  ;
  alter table linkrealization
  	drop constraint linkrealization_fk1
  ;
  alter table history
  	drop constraint history_fk1
  ;
  alter table conceptname
  	drop constraint conceptname_fk1
  ;
  alter table conceptdelegate
  	drop constraint conceptdelegate_fk1
  ;
  alter table artifact
  	drop constraint artifact_fk1
  ;
  alter table useraccount
  	drop constraint uc_useraccount_username
  ;
  drop index useraccount.idx_useraccount_username
  ;
  drop index usage.idx_usage_lut
  ;
  drop index usage.idx_usage_fk1
  ;
  drop index sectioninfo.idx_sectioninfo_fk1
  ;
  drop index prefs.idx_prefs
  ;
  drop index media.idx_media_lut
  ;
  drop index linktemplate.idx_linktemplate_lut
  ;
  drop index linktemplate.idx_linktemplate_fk1
  ;
  drop index linkrealization.idx_linkrealization_lut
  ;
  drop index linkrealization.idx_linkrealization_fk1
  ;
  drop index history.idx_history_lut
  ;
  drop index history.idx_history_fk1
  ;
  drop index selectedConcept.idx_concept_lut
  ;
  drop index selectedConcept.idx_concept_fk1
  ;
  drop index conceptname.idx_conceptname_name
  ;
  drop index conceptname.idx_conceptname_lut
  ;
  drop index conceptname.idx_conceptname_fk1
  ;
  drop index conceptdelegate.idx_conceptdelegate_lut
  ;
  drop index conceptdelegate.idx_conceptdelegate_fk1
  ;
  drop index artifact.idx_artifact_lut
  ;
  drop index artifact.idx_artifact_fk1
  ;
  drop index artifact.idx_artifact_ck
  ;
  drop table useraccount
  ;
  drop table usage
  ;
  drop table uniqueid
  ;
  drop table sectioninfo
  ;
  drop table prefs
  ;
  drop table media
  ;
  drop table linktemplate
  ;
  drop table linkrealization
  ;
  drop table history
  ;
  drop table conceptname
  ;
  drop table conceptdelegate
  ;
  drop table selectedConcept
  ;
  drop table artifact
  ;
  
  create table artifact  ( 
  	conceptdelegateid_fk	int8 not null,
  	groupid             	varchar(64) not null,
  	artifactid          	varchar(256) not null,
  	version             	varchar(64) not null,
  	classifier          	varchar(64) null,
  	description         	varchar(2048) null,
  	mimetype            	varchar(32) null,
  	caption             	varchar(1024) null,
  	reference           	varchar(1024) not null,
  	credit              	varchar(1024) null,
  	id                  	int8 not null,
  	last_updated_time   	timestamp not null,
  	creationdate        	timestamp null,
  	constraint artifact_pk primary key(id)
  )
  ;
  create table selectedConcept  (
  	id                	int8 not null,
  	parentconceptid_fk	int8 null,
  	originator        	varchar(255) null,
  	structuretype     	varchar(10) null,
  	reference         	varchar(1024) null,
  	nodccode          	varchar(20) null,
  	rankname          	varchar(20) null,
  	ranklevel         	varchar(20) null,
  	taxonomytype      	varchar(20) null,
  	last_updated_time 	timestamp null,
  	constraint concept_pk primary key(id)
  )
  ;
  create table conceptdelegate  ( 
  	id               	int8 not null,
  	conceptid_fk     	int8 not null,
  	last_updated_time	timestamp null,
  	constraint conceptdelegate_pk primary key(id)
  )
  ;
  create table conceptname  ( 
  	conceptname      	varchar(128) null,
  	conceptid_fk     	int8 null,
  	author           	varchar(255) null,
  	nametype         	varchar(10) null,
  	id               	int8 not null,
  	last_updated_time	timestamp null,
  	constraint conceptname_pk primary key(id)
  )
  ;
  create table history  ( 
  	id                  	int8 not null,
  	conceptdelegateid_fk	int8 null,
  	processeddtg        	timestamp null,
  	creationdtg         	timestamp null,
  	description         	varchar(1000) null,
  	creatorname         	varchar(50) null,
  	processorname       	varchar(50) null,
  	field               	varchar(50) null,
  	oldvalue            	varchar(2048) null,
  	newvalue            	varchar(2048) null,
  	action              	varchar(16) null,
  	comment             	varchar(2048) null,
  	approved            	smallint not null,
  	last_updated_time   	timestamp null,
  	constraint history_pk primary key(id)
  )
  ;
  create table linkrealization  ( 
  	id                  	int8 not null,
  	conceptdelegateid_fk	int8 null,
  	linkname            	varchar(50) null,
  	toconcept           	varchar(128) null,
  	linkvalue           	varchar(2048) null,
  	last_updated_time   	timestamp null,
  	constraint linkrealization_pk primary key(id)
  )
  ;
  create table linktemplate  ( 
  	id                  	int8 not null,
  	conceptdelegateid_fk	int8 null,
  	linkname            	varchar(50) null,
  	toconcept           	varchar(128) null,
  	linkvalue           	varchar(255) null,
  	last_updated_time   	timestamp null,
  	constraint linktemplate_pk primary key(id)
  )
  ;
  create table media  ( 
  	id                  	int8 not null,
  	conceptdelegateid_fk	int8 null,
  	url                 	varchar(1024) null,
  	mediatype           	char(5) null,
  	primarymedia        	bit null,
  	credit              	varchar(255) null,
  	caption             	varchar(1000) null,
  	last_updated_time   	timestamp null,
  	constraint media_pk primary key(id)
  )
  ;
  create table prefs  ( 
  	nodename 	varchar(255) not null,
  	prefkey  	varchar(50) not null,
  	prefvalue	varchar(255) not null
  	)
  ;
  create table sectioninfo  ( 
  	id                  	int8 not null,
  	conceptdelegateid_fk	int8 not null,
  	header              	varchar(30) not null,
  	label               	varchar(50) not null,
  	information         	varchar(5000) null,
  	constraint sectioninfo_pk primary key(id)
  )
  ;
  create table uniqueid  ( 
  	tablename	varchar(200) not null,
  	nextid   	int8 null,
  	constraint uniqueid_pk primary key(tablename)
  )
  ;
  create table usage  ( 
  	id                   	int8 not null,
  	conceptdelegateid_fk 	int8 null,
  	embargoexpirationdate	timestamp null,
  	specification        	varchar(1000) null,
  	last_updated_time    	timestamp null,
  	constraint usage_pk primary key(id)
  )
  ;
  create table useraccount  ( 
  	id               	int8 not null,
  	username         	varchar(50) not null,
  	password         	varchar(50) null,
  	role             	varchar(10) null,
  	last_updated_time	timestamp null,
  	affiliation      	varchar(50) null,
  	firstname        	varchar(50) null,
  	lastname         	varchar(50) null,
  	email            	varchar(50) null,
  	constraint useraccount_pk primary key(id)
  )
  ;
  create unique index idx_artifact_ck
  	on artifact(conceptdelegateid_fk, groupid, artifactid, version, classifier)
  ;
  create index idx_artifact_fk1
  	on artifact(conceptdelegateid_fk)
  ;
  create index idx_artifact_lut
  	on artifact(last_updated_time)
  ;
  create index idx_conceptdelegate_fk1
  	on conceptdelegate(conceptid_fk)
  ;
  create index idx_conceptdelegate_lut
  	on conceptdelegate(last_updated_time)
  ;
  create index idx_conceptname_fk1
  	on conceptname(conceptid_fk)
  ;
  create index idx_conceptname_lut
  	on conceptname(last_updated_time)
  ;
  create index idx_conceptname_name
  	on conceptname(conceptname)
  ;
  create index idx_concept_fk1
  	on selectedConcept(parentconceptid_fk)
  ;
  create index idx_concept_lut
  	on selectedConcept(last_updated_time)
  ;
  create index idx_history_fk1
  	on history(conceptdelegateid_fk)
  ;
  create index idx_history_lut
  	on history(last_updated_time)
  ;
  create index idx_linkrealization_fk1
  	on linkrealization(conceptdelegateid_fk)
  ;
  create index idx_linkrealization_lut
  	on linkrealization(last_updated_time)
  ;
  create index idx_linktemplate_fk1
  	on linktemplate(conceptdelegateid_fk)
  ;
  create index idx_linktemplate_lut
  	on linktemplate(last_updated_time)
  ;
  create index idx_media_lut
  	on media(last_updated_time)
  ;
  create index idx_prefs
  	on prefs(nodename, prefkey)
  ;
  create index idx_sectioninfo_fk1
  	on sectioninfo(conceptdelegateid_fk)
  ;
  create index idx_usage_fk1
  	on usage(conceptdelegateid_fk)
  ;
  create index idx_usage_lut
  	on usage(last_updated_time)
  ;
  create index idx_useraccount_username
  	on useraccount(username)
  ;
  
  alter table useraccount
  	add constraint uc_useraccount_username
  	unique (username)
  ;
  alter table artifact
  	add constraint artifact_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table conceptdelegate
  	add constraint conceptdelegate_fk1
  	foreign key(conceptid_fk)
  	references selectedConcept(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table conceptname
  	add constraint conceptname_fk1
  	foreign key(conceptid_fk)
  	references selectedConcept(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table history
  	add constraint history_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table linkrealization
  	add constraint linkrealization_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table linktemplate
  	add constraint linktemplate_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table media
  	add constraint media_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table sectioninfo
  	add constraint sectioninfo_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  alter table usage
  	add constraint usage_fk1
  	foreign key(conceptdelegateid_fk)
  	references conceptdelegate(id)
  	on delete no action 
  	on update no action 
  --	not for replication 
  ;
  
  