-- ================================================================================
--   sybase SQL DDL Script File
-- ================================================================================


-- ================================================================================
-- 
--   Generated by:      tedia2sql -- v1.2.9
--                      See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information
-- 
--   Target Database:   sybase
--   Generated at:      Tue Aug 12 11:43:57 2003
--   Input File:        TestERD.dia
-- 
-- ================================================================================



-- Generated SQL Constraints Drop statements
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia

drop index imageInfo.idx_iimd5
go
drop index imageInfo.idx_iiid
go
drop index subImageInfo.idx_siiid
go
drop index subImageInfo.idx_siips
go
drop index imageCategoryList.idx_iclidnm
go
drop index userInfo.idx_uinm
go
drop index userInfo.idx_uiid
go
drop index userAttribute.idx_uauiid
go
drop index userImageRating.idx_uiruid
go
drop index attributeCategory.idx_acid
go
drop index userSession.idx_usmd5
go
alter table subImageInfo drop constraint fk_iisii 
go
alter table imageCategoryList drop constraint fk_iiicl 
go
alter table imageAttribute drop constraint fk_iiia 
go
alter table userImageRating drop constraint fk_uiuir 
go
alter table userAttribute drop constraint fk_uiua 
go
alter table userSession drop constraint fk_uius 
go
alter table imageAttribute drop constraint fk_iaac 
go
alter table userAttribute drop constraint fk_acua 
go


-- Generated Permissions Drops
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia

revoke all on imageInfo from fmorg 
go
revoke select on imageInfo from public 
go
revoke all on subImageInfo from fmorg 
go
revoke all on imageCategoryList from fmorg 
go
revoke select on categoryNames from public 
go
revoke all on categoryNames from fmorg 
go
revoke all on imageAttribute from fmorg 
go
revoke all on userInfo from fmorg 
go
revoke all on userAttribute from fmorg 
go
revoke all on userImageRating from fmorg 
go
revoke all on attributeCategory from fmorg 
go
revoke all on userSession from fmorg 
go
revoke select on extremes from public 
go
revoke all on extremes from fmorg 
go



-- Generated SQL View Drop Statements
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia


-- drop ratings_view
if exists (select 1 from sysobjects where type = 'V' and name = 'ratings_view')
begin
  drop view ratings_view
end 
go

-- drop whorated_view
if exists (select 1 from sysobjects where type = 'V' and name = 'whorated_view')
begin
  drop view whorated_view
end 
go

-- drop users_view
if exists (select 1 from sysobjects where type = 'V' and name = 'users_view')
begin
  drop view users_view
end 
go


-- Generated SQL Schema Drop statements
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia


-- drop imageInfo
if exists (select 1 from sysobjects where type = 'U' and name = 'imageInfo')
begin
  drop table imageInfo
end 
go

-- drop subImageInfo
if exists (select 1 from sysobjects where type = 'U' and name = 'subImageInfo')
begin
  drop table subImageInfo
end 
go

-- drop imageCategoryList
if exists (select 1 from sysobjects where type = 'U' and name = 'imageCategoryList')
begin
  drop table imageCategoryList
end 
go

-- drop categoryNames
if exists (select 1 from sysobjects where type = 'U' and name = 'categoryNames')
begin
  drop table categoryNames
end 
go

-- drop imageAttribute
if exists (select 1 from sysobjects where type = 'U' and name = 'imageAttribute')
begin
  drop table imageAttribute
end 
go

-- drop userInfo
if exists (select 1 from sysobjects where type = 'U' and name = 'userInfo')
begin
  drop table userInfo
end 
go

-- drop userAttribute
if exists (select 1 from sysobjects where type = 'U' and name = 'userAttribute')
begin
  drop table userAttribute
end 
go

-- drop userImageRating
if exists (select 1 from sysobjects where type = 'U' and name = 'userImageRating')
begin
  drop table userImageRating
end 
go

-- drop attributeCategory
if exists (select 1 from sysobjects where type = 'U' and name = 'attributeCategory')
begin
  drop table attributeCategory
end 
go

-- drop userSession
if exists (select 1 from sysobjects where type = 'U' and name = 'userSession')
begin
  drop table userSession
end 
go

-- drop extremes
if exists (select 1 from sysobjects where type = 'U' and name = 'extremes')
begin
  drop table extremes
end 
go


-- Generated SQL Schema
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia


-- imageInfo
create table imageInfo (
  id                        numeric (18) not null,
  insertionDate             datetime default now() not null,
  md5sum                    char (32) not null,
  binaryType                varchar (16) default 'jpg' null,
  name                      varchar (64) not null,
  locationList              varchar (128) default '//imgserver.org',
  description               varchar (128) null,
  constraint pk_imageInfo primary key (id)
) 
go

-- subImageInfo
create table subImageInfo (
  imageInfo_id              numeric (18) not null,
  pixSize                   integer not null,
  constraint pk_subImageInfo primary key (imageInfo_id,pixSize)
) 
go

-- imageCategoryList
create table imageCategoryList (
  imageInfo_id              numeric (18) not null,
  name                      varchar (32) not null,
  constraint pk_imageCategoryList primary key (imageInfo_id,name)
) 
go

-- categoryNames
create table categoryNames (
  name                      varchar (32) not null,
  constraint pk_categoryNames primary key (name)
) 
go

-- imageAttribute
create table imageAttribute (
  imageInfo_id              numeric (18) not null,
  attributeCategory_id      numeric (18) not null,
  numValue                  numeric (8),
  category                  numeric (4),
  constraint pk_imageAttribute primary key (imageInfo_id,attributeCategory_id)
) 
go

-- userInfo
create table userInfo (
  id                        numeric (18) not null,
  insertionDate             datetime,
  md5sum                    char (32),
  birthDate                 datetime,
  gender                    char (1),
  name                      varchar (32),
  email                     varchar (96),
  currentCategory           varchar (32),
  lastDebitDate             datetime,
  acctBalance               numeric (10,2),
  active                    integer,
  constraint pk_userInfo primary key (id)
) 
go

-- userAttribute
create table userAttribute (
  userInfo_id               numeric (18) not null,
  attributeCategory_id      numeric (18) not null,
  numValue                  numeric (5,4),
  constraint pk_userAttribute primary key (userInfo_id,attributeCategory_id)
) 
go

-- userImageRating
create table userImageRating (
  userInfo_id               numeric (18) not null,
  imageInfo_id              numeric (15) not null,
  rating                    integer,
  constraint pk_userImageRating primary key (userInfo_id,imageInfo_id)
) 
go

-- attributeCategory
create table attributeCategory (
  id                        numeric (18) not null,
  attributeDesc             varchar (128),
  constraint pk_attributeCategory primary key (id)
) 
go

-- userSession
create table userSession (
  userInfo_id               numeric (18) not null,
  md5sum                    char (32) not null,
  insertionDate             datetime,
  expireDate                datetime,
  ipAddress                 varchar (24),
  constraint pk_userSession primary key (userInfo_id,md5sum)
) 
go

-- extremes
create table extremes (
  name                      varchar (32) not null,
  colName                   varchar (64),
  minVal                    numeric (15),
  maxVal                    numeric (15),
  constraint pk_extremes primary key (name)
) 
go


-- Generated SQL Views
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia


-- ratings_view
create view ratings_view as
  select b.name, c.md5sum, a.rating
  from userImageRating a,
    userImageRating z,
    userInfo b,
    imageInfo c
  where (((a.userInfo_id = b.id)
    and (a.imageInfo_id = c.id)
    and (a.userInfo_id = z.userInfo_id))
    and (a.userInfo_id <> z.userInfo_id))
  order by c.md5sum,b.name,a.rating

go

-- whorated_view
create view whorated_view as
  select a.name, count (*) as numRatings
  from userInfo a,
    userImageRating b
  where (a.id = b.userInfo_id)
  group by a.name

go

-- users_view
create view users_view as
  select id, birthDate, name ||'<'|| email ||'>' as whoIsThis, currentCategory, acctBalance, active
  from userInfo
  order by userInfo.name

go



-- Generated Permissions
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia

grant all on imageInfo to fmorg 
go
grant select on imageInfo to public 
go
grant all on subImageInfo to fmorg 
go
grant all on imageCategoryList to fmorg 
go
grant select on categoryNames to public 
go
grant all on categoryNames to fmorg 
go
grant all on imageAttribute to fmorg 
go
grant all on userInfo to fmorg 
go
grant all on userAttribute to fmorg 
go
grant all on userImageRating to fmorg 
go
grant all on attributeCategory to fmorg 
go
grant all on userSession to fmorg 
go
grant select on extremes to public 
go
grant all on extremes to fmorg 
go


-- Generated SQL Insert statements
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia


-- inserts for categoryNames
insert into categoryNames values ( 'Buildings' ) 
go
insert into categoryNames values ( 'Landscapes' ) 
go
insert into categoryNames values ( 'Nudes' ) 
go
insert into categoryNames values ( 'Life Studies' ) 
go
insert into categoryNames values ( 'Portraits' ) 
go
insert into categoryNames values ( 'Abstracts' ) 
go

-- inserts for attributeCategory
insert into attributeCategory values ( 1,'Blurriness' ) 
go
insert into attributeCategory values ( 2,'Contrastiness' ) 
go
insert into attributeCategory values ( 3,'Saturation' ) 
go
insert into attributeCategory values ( 4,'Size' ) 
go
insert into attributeCategory values ( 5,'Relevence' ) 
go


-- Generated SQL Constraints
-- --------------------------------------------------------------------
--     Target Database:   sybase
--     SQL Generator:     tedia2sql -- v1.2.9
--     Generated at:      Tue Aug 12 11:43:56 2003
--     Input File:        TestERD.dia

create unique index idx_iimd5 on imageInfo  (md5sum) 
go
create index idx_iiid on imageInfo  (id) 
go
create index idx_siiid on subImageInfo  (imageInfo_id) 
go
create index idx_siips on subImageInfo  (pixSize) 
go
create index idx_iclidnm on imageCategoryList  (imageInfo_id,name) 
go
create unique index idx_uinm on userInfo  (name,md5sum) 
go
create index idx_uiid on userInfo  (id) 
go
create index idx_uauiid on userAttribute  (userInfo_id) 
go
create index idx_uiruid on userImageRating  (userInfo_id) 
go
create index idx_acid on attributeCategory  (id) 
go
create index idx_usmd5 on userSession  (md5sum) 
go

-- constraint fk_iisii plus related triggers
alter table subImageInfo add constraint fk_iisii foreign key (imageInfo_id) references imageInfo (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_iiicl plus related triggers
alter table imageCategoryList add constraint fk_iiicl foreign key (imageinfo_id) references imageInfo (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_iiia plus related triggers
alter table imageAttribute add constraint fk_iiia foreign key (imageInfo_id) references imageInfo (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_uiuir plus related triggers
alter table userImageRating add constraint fk_uiuir foreign key (userInfo_id) references userInfo (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_uiua plus related triggers
alter table userAttribute add constraint fk_uiua foreign key (userInfo_id) references userInfo (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_uius plus related triggers
alter table userSession add constraint fk_uius foreign key (userInfo_id) references userInfo (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_iaac plus related triggers
alter table imageAttribute add constraint fk_iaac foreign key (attributeCategory_id) references attributeCategory (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go

-- constraint fk_acua plus related triggers
alter table userAttribute add constraint fk_acua foreign key (attributeCategory_id) references attributeCategory (id) 
go
-- create trigger ri_one . . . .
-- create trigger ri_two . . . .
-- 
go