Thread: [Squirrel-sql-commits] sql12/test/scripts/tedia2sql_scripts mysql-testerd.sql, NONE, 1.1 sas-tester
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Rob M. <man...@us...> - 2008-01-20 21:38:08
|
Update of /cvsroot/squirrel-sql/sql12/test/scripts/tedia2sql_scripts In directory sc8-pr-cvs8.sourceforge.net:/tmp/cvs-serv21092/test/scripts/tedia2sql_scripts Added Files: mysql-testerd.sql sas-testerd.sql oracle-testerd.sql innodb-testerd.sql db2-testerd.sql sybase-testerd.sql ingres-testerd.sql pgsql-testerd.sql Log Message: Scripts for experimenting with refactoring implementations. --- NEW FILE: mysql-testerd.sql --- -- ================================================================================ -- mysql SQL DDL Script File -- ================================================================================ -- =============================================================================== -- -- Generated by: tedia2sql -- v1.2.13b2 -- See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information -- -- Target Database: mysql -- Generated at: Sat Dec 18 19:47:39 2004 -- Input Files: TestERD.dia -- -- ================================================================================ -- Generated SQL Constraints Drop statements -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia drop index idx_iimd5 on imageInfo; drop index idx_iiid on imageInfo; drop index idx_siiid on subImageInfo; drop index idx_siips on subImageInfo; drop index idx_iclidnm on imageCategoryList; drop index idx_uinm on userInfo; drop index idx_uiid on userInfo; drop index idx_uauiid on userAttribute; drop index idx_uiruid on userImageRating; drop index idx_acid on attributeCategory; drop index idx_usmd5 on userSession; -- drop constraint fk_iisii for mysql -- drop constraint fk_iiicl for mysql -- drop constraint fk_iiia for mysql -- drop constraint fk_uiuir for mysql -- drop constraint fk_uiua for mysql -- drop constraint fk_uius for mysql -- drop constraint fk_iaac for mysql -- drop constraint fk_acua for mysql -- Generated Permissions Drops -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia -- revoke all on imageInfo from fmorg for mysql -- revoke select on imageInfo from public for mysql -- revoke all on subImageInfo from fmorg for mysql -- revoke all on imageCategoryList from fmorg for mysql -- revoke select on categoryNames from public for mysql -- revoke all on categoryNames from fmorg for mysql -- revoke all on imageAttribute from fmorg for mysql -- revoke all on userInfo from fmorg for mysql -- revoke all on userAttribute from fmorg for mysql -- revoke all on userImageRating from fmorg for mysql -- revoke all on attributeCategory from fmorg for mysql -- revoke all on userSession from fmorg for mysql -- revoke select on extremes from public for mysql -- revoke all on extremes from fmorg for mysql -- Generated SQL View Drop Statements -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia -- MySQL doesn't support views yet -- drop view if exists ratings_view ; -- MySQL doesn't support views yet -- drop view if exists whorated_view ; -- MySQL doesn't support views yet -- drop view if exists users_view ; -- Generated SQL Schema Drop statements -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia drop table if exists imageInfo ; drop table if exists subImageInfo ; drop table if exists imageCategoryList ; drop table if exists categoryNames ; drop table if exists imageAttribute ; drop table if exists userInfo ; drop table if exists userAttribute ; drop table if exists userImageRating ; drop table if exists attributeCategory ; drop table if exists userSession ; drop table if exists extremes ; -- Generated SQL Schema -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia -- imageInfo create table imageInfo ( id numeric (18) not null, insertionDate timestamp 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) ) ; -- subImageInfo create table subImageInfo ( imageInfo_id numeric (18) not null, pixSize integer not null, constraint pk_SubImageInfo primary key (imageInfo_id,pixSize) ) ; -- imageCategoryList create table imageCategoryList ( imageInfo_id numeric (18) not null, name varchar (32) not null, constraint pk_ImageCategoryList primary key (imageInfo_id,name) ) ; -- categoryNames create table categoryNames ( name varchar (32) not null, constraint pk_CategoryNames primary key (name) ) ; -- 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) ) ; -- userInfo create table userInfo ( id numeric (18) not null, insertionDate timestamp, md5sum char (32), birthDate timestamp, gender char (1), name varchar (32), email varchar (96), currentCategory varchar (32), lastDebitDate timestamp, acctBalance numeric (10,2), active integer, constraint pk_UserInfo primary key (id) ) ; -- 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) ) ; -- 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) ) ; -- attributeCategory create table attributeCategory ( id numeric (18) not null, attributeDesc varchar (128), constraint pk_AttributeCategory primary key (id) ) ; -- userSession create table userSession ( userInfo_id numeric (18) not null, md5sum char (32) not null, insertionDate timestamp, expireDate timestamp, ipAddress varchar (24), constraint pk_UserSession primary key (userInfo_id,md5sum) ) ; -- extremes create table extremes ( name varchar (32) not null, colName varchar (64), minVal numeric (15), maxVal numeric (15), constraint pk_Extremes primary key (name) ) ; -- Generated SQL Views -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia -- ratings_view -- WARNING! tedia2sql currently believes MySQL does not support -- views, but you're trying to create a view here. Expect errors. 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 ; -- whorated_view -- WARNING! tedia2sql currently believes MySQL does not support -- views, but you're trying to create a view here. Expect errors. 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 ; -- users_view -- WARNING! tedia2sql currently believes MySQL does not support -- views, but you're trying to create a view here. Expect errors. create view users_view as select id, birthDate, name ||'<'|| email ||'>' as whoIsThis, currentCategory, acctBalance, active from userInfo order by userInfo.name ; -- Generated Permissions -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia -- grant all on imageInfo to fmorg for mysql -- grant select on imageInfo to public for mysql -- grant all on subImageInfo to fmorg for mysql -- grant all on imageCategoryList to fmorg for mysql -- grant select on categoryNames to public for mysql -- grant all on categoryNames to fmorg for mysql -- grant all on imageAttribute to fmorg for mysql -- grant all on userInfo to fmorg for mysql -- grant all on userAttribute to fmorg for mysql -- grant all on userImageRating to fmorg for mysql -- grant all on attributeCategory to fmorg for mysql -- grant all on userSession to fmorg for mysql -- grant select on extremes to public for mysql -- grant all on extremes to fmorg for mysql -- Generated SQL Insert statements -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia -- inserts for categoryNames insert into categoryNames values ( 'Buildings' ) ; insert into categoryNames values ( 'Landscapes' ) ; insert into categoryNames values ( 'Nudes' ) ; insert into categoryNames values ( 'Life Studies' ) ; insert into categoryNames values ( 'Portraits' ) ; insert into categoryNames values ( 'Abstracts' ) ; -- inserts for attributeCategory insert into attributeCategory values ( 1,'Blurriness' ) ; insert into attributeCategory values ( 2,'Contrastiness' ) ; insert into attributeCategory values ( 3,'Saturation' ) ; insert into attributeCategory values ( 4,'Size' ) ; insert into attributeCategory values ( 5,'Relevence' ) ; -- Generated SQL Constraints -- -------------------------------------------------------------------- -- Target Database: mysql -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:30 2004 -- Input Files: TestERD.dia create unique index idx_iimd5 on imageInfo (md5sum) ; create index idx_iiid on imageInfo (id) ; create index idx_siiid on subImageInfo (imageInfo_id) ; create index idx_siips on subImageInfo (pixSize) ; create index idx_iclidnm on imageCategoryList (imageInfo_id,name) ; create unique index idx_uinm on userInfo (name,md5sum) ; create index idx_uiid on userInfo (id) ; create index idx_uauiid on userAttribute (userInfo_id) ; create index idx_uiruid on userImageRating (userInfo_id) ; create index idx_acid on attributeCategory (id) ; create index idx_usmd5 on userSession (md5sum) ; -- alter table subImageInfo add constraint fk_iisii foreign key (imageInfo_id) references imageInfo (id) for mysql -- alter table imageCategoryList add constraint fk_iiicl foreign key (imageinfo_id) references imageInfo (id) for mysql -- alter table imageAttribute add constraint fk_iiia foreign key (imageInfo_id) references imageInfo (id) for mysql -- alter table userImageRating add constraint fk_uiuir foreign key (userInfo_id) references userInfo (id) for mysql -- alter table userAttribute add constraint fk_uiua foreign key (userInfo_id) references userInfo (id) for mysql -- alter table userSession add constraint fk_uius foreign key (userInfo_id) references userInfo (id) for mysql -- alter table imageAttribute add constraint fk_iaac foreign key (attributeCategory_id) references attributeCategory (id) for mysql -- alter table userAttribute add constraint fk_acua foreign key (attributeCategory_id) references attributeCategory (id) for mysql --- NEW FILE: innodb-testerd.sql --- -- ================================================================================ -- innodb SQL DDL Script File -- ================================================================================ -- =============================================================================== -- -- Generated by: tedia2sql -- v1.2.13b2 -- See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information -- -- Target Database: innodb -- Generated at: Sat Dec 18 19:47:54 2004 -- Input Files: TestERD.dia -- -- ================================================================================ set autocommit = 0 ; -- Generated SQL Constraints Drop statements -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia drop index idx_iimd5 on imageInfo; drop index idx_iiid on imageInfo; drop index idx_siiid on subImageInfo; drop index idx_siips on subImageInfo; drop index idx_iclidnm on imageCategoryList; drop index idx_uinm on userInfo; drop index idx_uiid on userInfo; drop index idx_uauiid on userAttribute; drop index idx_uiruid on userImageRating; drop index idx_acid on attributeCategory; drop index idx_usmd5 on userSession; -- alter table subImageInfo drop constraint fk_iisii-- (is implicitly done) -- alter table imageCategoryList drop constraint fk_iiicl-- (is implicitly done) -- alter table imageAttribute drop constraint fk_iiia-- (is implicitly done) -- alter table userImageRating drop constraint fk_uiuir-- (is implicitly done) -- alter table userAttribute drop constraint fk_uiua-- (is implicitly done) -- alter table userSession drop constraint fk_uius-- (is implicitly done) -- alter table imageAttribute drop constraint fk_iaac-- (is implicitly done) -- alter table userAttribute drop constraint fk_acua-- (is implicitly done) -- Generated Permissions Drops -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia revoke all on imageInfo from fmorg ; revoke select on imageInfo from public ; revoke all on subImageInfo from fmorg ; revoke all on imageCategoryList from fmorg ; revoke select on categoryNames from public ; revoke all on categoryNames from fmorg ; revoke all on imageAttribute from fmorg ; revoke all on userInfo from fmorg ; revoke all on userAttribute from fmorg ; revoke all on userImageRating from fmorg ; revoke all on attributeCategory from fmorg ; revoke all on userSession from fmorg ; revoke select on extremes from public ; revoke all on extremes from fmorg ; -- Generated SQL View Drop Statements -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia -- MySQL doesn't support views yet -- drop view if exists ratings_view ; -- MySQL doesn't support views yet -- drop view if exists whorated_view ; -- MySQL doesn't support views yet -- drop view if exists users_view ; -- Generated SQL Schema Drop statements -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia drop table if exists imageInfo ; drop table if exists subImageInfo ; drop table if exists imageCategoryList ; drop table if exists categoryNames ; drop table if exists imageAttribute ; drop table if exists userInfo ; drop table if exists userAttribute ; drop table if exists userImageRating ; drop table if exists attributeCategory ; drop table if exists userSession ; drop table if exists extremes ; -- Generated SQL Schema -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia -- imageInfo create table imageInfo ( id numeric (18) not null, insertionDate timestamp 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) ) type = InnoDB ; -- subImageInfo create table subImageInfo ( imageInfo_id numeric (18) not null, pixSize integer not null, constraint pk_SubImageInfo primary key (imageInfo_id,pixSize) ) type = InnoDB ; -- imageCategoryList create table imageCategoryList ( imageInfo_id numeric (18) not null, name varchar (32) not null, constraint pk_ImageCategoryList primary key (imageInfo_id,name) ) type = InnoDB ; -- categoryNames create table categoryNames ( name varchar (32) not null, constraint pk_CategoryNames primary key (name) ) type = InnoDB ; -- 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) ) type = InnoDB ; -- userInfo create table userInfo ( id numeric (18) not null, insertionDate timestamp, md5sum char (32), birthDate timestamp, gender char (1), name varchar (32), email varchar (96), currentCategory varchar (32), lastDebitDate timestamp, acctBalance numeric (10,2), active integer, constraint pk_UserInfo primary key (id) ) type = InnoDB ; -- 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) ) type = InnoDB ; -- 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) ) type = InnoDB ; -- attributeCategory create table attributeCategory ( id numeric (18) not null, attributeDesc varchar (128), constraint pk_AttributeCategory primary key (id) ) type = InnoDB ; -- userSession create table userSession ( userInfo_id numeric (18) not null, md5sum char (32) not null, insertionDate timestamp, expireDate timestamp, ipAddress varchar (24), constraint pk_UserSession primary key (userInfo_id,md5sum) ) type = InnoDB ; -- extremes create table extremes ( name varchar (32) not null, colName varchar (64), minVal numeric (15), maxVal numeric (15), constraint pk_Extremes primary key (name) ) type = InnoDB ; -- Generated SQL Views -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia -- ratings_view -- WARNING! tedia2sql currently believes MySQL does not support -- views, but you're trying to create a view here. Expect errors. 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 ; -- whorated_view -- WARNING! tedia2sql currently believes MySQL does not support -- views, but you're trying to create a view here. Expect errors. 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 ; -- users_view -- WARNING! tedia2sql currently believes MySQL does not support -- views, but you're trying to create a view here. Expect errors. create view users_view as select id, birthDate, name ||'<'|| email ||'>' as whoIsThis, currentCategory, acctBalance, active from userInfo order by userInfo.name ; -- Generated Permissions -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia grant all on imageInfo to fmorg ; grant select on imageInfo to public ; grant all on subImageInfo to fmorg ; grant all on imageCategoryList to fmorg ; grant select on categoryNames to public ; grant all on categoryNames to fmorg ; grant all on imageAttribute to fmorg ; grant all on userInfo to fmorg ; grant all on userAttribute to fmorg ; grant all on userImageRating to fmorg ; grant all on attributeCategory to fmorg ; grant all on userSession to fmorg ; grant select on extremes to public ; grant all on extremes to fmorg ; -- Generated SQL Insert statements -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia -- inserts for categoryNames insert into categoryNames values ( 'Buildings' ) ; insert into categoryNames values ( 'Landscapes' ) ; insert into categoryNames values ( 'Nudes' ) ; insert into categoryNames values ( 'Life Studies' ) ; insert into categoryNames values ( 'Portraits' ) ; insert into categoryNames values ( 'Abstracts' ) ; -- inserts for attributeCategory insert into attributeCategory values ( 1,'Blurriness' ) ; insert into attributeCategory values ( 2,'Contrastiness' ) ; insert into attributeCategory values ( 3,'Saturation' ) ; insert into attributeCategory values ( 4,'Size' ) ; insert into attributeCategory values ( 5,'Relevence' ) ; -- Generated SQL Constraints -- -------------------------------------------------------------------- -- Target Database: innodb -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:47:46 2004 -- Input Files: TestERD.dia create unique index idx_iimd5 on imageInfo (md5sum) ; create index idx_iiid on imageInfo (id) ; create index idx_siiid on subImageInfo (imageInfo_id) ; create index idx_siips on subImageInfo (pixSize) ; create index idx_iclidnm on imageCategoryList (imageInfo_id,name) ; create unique index idx_uinm on userInfo (name,md5sum) ; create index idx_uiid on userInfo (id) ; create index idx_uauiid on userAttribute (userInfo_id) ; create index idx_uiruid on userImageRating (userInfo_id) ; create index idx_acid on attributeCategory (id) ; create index idx_usmd5 on userSession (md5sum) ; alter table subImageInfo add constraint fk_iisii foreign key (imageInfo_id) references imageInfo (id) ; alter table imageCategoryList add constraint fk_iiicl foreign key (imageinfo_id) references imageInfo (id) ; alter table imageAttribute add constraint fk_iiia foreign key (imageInfo_id) references imageInfo (id) ; alter table userImageRating add constraint fk_uiuir foreign key (userInfo_id) references userInfo (id) on delete cascade ; alter table userAttribute add constraint fk_uiua foreign key (userInfo_id) references userInfo (id) on delete cascade ; alter table userSession add constraint fk_uius foreign key (userInfo_id) references userInfo (id) on delete cascade ; alter table imageAttribute add constraint fk_iaac foreign key (attributeCategory_id) references attributeCategory (id) ; alter table userAttribute add constraint fk_acua foreign key (attributeCategory_id) references attributeCategory (id) ; -- innodb requires a special 'quit' command commit ; --- NEW FILE: db2-testerd.sql --- -- ================================================================================ -- db2 SQL DDL Script File -- ================================================================================ -- =============================================================================== -- -- Generated by: tedia2sql -- v1.2.13b2 -- See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information -- -- Target Database: db2 -- Generated at: Sat Dec 18 19:48:25 2004 -- Input Files: TestERD.dia -- -- ================================================================================ -- Generated SQL Constraints Drop statements -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia drop index idx_iimd5; drop index idx_iiid; drop index idx_siiid; drop index idx_siips; drop index idx_iclidnm; drop index idx_uinm; drop index idx_uiid; drop index idx_uauiid; drop index idx_uiruid; drop index idx_acid; drop index idx_usmd5; alter table subImageInfo drop constraint fk_iisii ; alter table imageCategoryList drop constraint fk_iiicl ; alter table imageAttribute drop constraint fk_iiia ; alter table userImageRating drop constraint fk_uiuir ; alter table userAttribute drop constraint fk_uiua ; alter table userSession drop constraint fk_uius ; alter table imageAttribute drop constraint fk_iaac ; alter table userAttribute drop constraint fk_acua ; -- Generated Permissions Drops -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia revoke all on imageInfo from fmorg ; revoke select on imageInfo from public ; revoke all on subImageInfo from fmorg ; revoke all on imageCategoryList from fmorg ; revoke select on categoryNames from public ; revoke all on categoryNames from fmorg ; revoke all on imageAttribute from fmorg ; revoke all on userInfo from fmorg ; revoke all on userAttribute from fmorg ; revoke all on userImageRating from fmorg ; revoke all on attributeCategory from fmorg ; revoke all on userSession from fmorg ; revoke select on extremes from public ; revoke all on extremes from fmorg ; -- Special statements for oracle,postgres,db2:pre databases -- statements to do BEFORE creating -- the tables (schema) drop sequence imageInfo_id; create sequence imageInfo_id; -- Generated SQL View Drop Statements -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia drop view ratings_view ; drop view whorated_view ; drop view users_view ; -- Generated SQL Schema Drop statements -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia drop table imageInfo ; drop table subImageInfo ; drop table imageCategoryList ; drop table categoryNames ; drop table imageAttribute ; drop table userInfo ; drop table userAttribute ; drop table userImageRating ; drop table attributeCategory ; drop table userSession ; drop table extremes ; -- Generated SQL Schema -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia -- imageInfo create table imageInfo ( id numeric (18) not null, insertionDate timestamp 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) ) ; -- subImageInfo create table subImageInfo ( imageInfo_id numeric (18) not null, pixSize integer not null, constraint pk_SubImageInfo primary key (imageInfo_id,pixSize) ) ; -- imageCategoryList create table imageCategoryList ( imageInfo_id numeric (18) not null, name varchar (32) not null, constraint pk_ImgCtgryLst primary key (imageInfo_id,name) ) ; -- categoryNames create table categoryNames ( name varchar (32) not null, constraint pk_CategoryNames primary key (name) ) ; -- 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) ) ; -- userInfo create table userInfo ( id numeric (18) not null, insertionDate timestamp, md5sum char (32), birthDate timestamp, gender char (1), name varchar (32), email varchar (96), currentCategory varchar (32), lastDebitDate timestamp, acctBalance numeric (10,2), active integer, constraint pk_UserInfo primary key (id) ) ; -- 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) ) ; -- 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) ) ; -- attributeCategory create table attributeCategory ( id numeric (18) not null, attributeDesc varchar (128), constraint pk_AtrbtCtgry primary key (id) ) ; -- userSession create table userSession ( userInfo_id numeric (18) not null, md5sum char (32) not null, insertionDate timestamp, expireDate timestamp, ipAddress varchar (24), constraint pk_UserSession primary key (userInfo_id,md5sum) ) ; -- extremes create table extremes ( name varchar (32) not null, colName varchar (64), minVal numeric (15), maxVal numeric (15), constraint pk_Extremes primary key (name) ) ; -- Generated SQL Views -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: 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 ; -- 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 ; -- users_view create view users_view as select id, birthDate, name ||'<'|| email ||'>' as whoIsThis, currentCategory, acctBalance, active from userInfo order by userInfo.name ; -- Special statements for oracle,postgres,db2:post databases -- statements to do AFTER creating -- the tables (schema) --drop trigger . . . . --create trigger . . . . -- Generated Permissions -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia grant all on imageInfo to fmorg ; grant select on imageInfo to public ; grant all on subImageInfo to fmorg ; grant all on imageCategoryList to fmorg ; grant select on categoryNames to public ; grant all on categoryNames to fmorg ; grant all on imageAttribute to fmorg ; grant all on userInfo to fmorg ; grant all on userAttribute to fmorg ; grant all on userImageRating to fmorg ; grant all on attributeCategory to fmorg ; grant all on userSession to fmorg ; grant select on extremes to public ; grant all on extremes to fmorg ; -- Generated SQL Insert statements -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia -- inserts for categoryNames insert into categoryNames values ( 'Buildings' ) ; insert into categoryNames values ( 'Landscapes' ) ; insert into categoryNames values ( 'Nudes' ) ; insert into categoryNames values ( 'Life Studies' ) ; insert into categoryNames values ( 'Portraits' ) ; insert into categoryNames values ( 'Abstracts' ) ; -- inserts for attributeCategory insert into attributeCategory values ( 1,'Blurriness' ) ; insert into attributeCategory values ( 2,'Contrastiness' ) ; insert into attributeCategory values ( 3,'Saturation' ) ; insert into attributeCategory values ( 4,'Size' ) ; insert into attributeCategory values ( 5,'Relevence' ) ; -- Generated SQL Constraints -- -------------------------------------------------------------------- -- Target Database: db2 -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:17 2004 -- Input Files: TestERD.dia create unique index idx_iimd5 on imageInfo (md5sum) ; create index idx_iiid on imageInfo (id) ; create index idx_siiid on subImageInfo (imageInfo_id) ; create index idx_siips on subImageInfo (pixSize) ; create index idx_iclidnm on imageCategoryList (imageInfo_id,name) ; create unique index idx_uinm on userInfo (name,md5sum) ; create index idx_uiid on userInfo (id) ; create index idx_uauiid on userAttribute (userInfo_id) ; create index idx_uiruid on userImageRating (userInfo_id) ; create index idx_acid on attributeCategory (id) ; create index idx_usmd5 on userSession (md5sum) ; alter table subImageInfo add constraint fk_iisii foreign key (imageInfo_id) references imageInfo (id) ; alter table imageCategoryList add constraint fk_iiicl foreign key (imageinfo_id) references imageInfo (id) ; alter table imageAttribute add constraint fk_iiia foreign key (imageInfo_id) references imageInfo (id) ; alter table userImageRating add constraint fk_uiuir foreign key (userInfo_id) references userInfo (id) ; alter table userAttribute add constraint fk_uiua foreign key (userInfo_id) references userInfo (id) ; alter table userSession add constraint fk_uius foreign key (userInfo_id) references userInfo (id) ; alter table imageAttribute add constraint fk_iaac foreign key (attributeCategory_id) references attributeCategory (id) ; alter table userAttribute add constraint fk_acua foreign key (attributeCategory_id) references attributeCategory (id) ; --- NEW FILE: ingres-testerd.sql --- -- ================================================================================ -- ingres SQL DDL Script File -- ================================================================================ -- =============================================================================== -- -- Generated by: tedia2sql -- v1.2.13b2 -- See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information -- -- Target Database: ingres -- Generated at: Sat Dec 18 19:49:12 2004 -- Input Files: TestERD.dia -- -- ================================================================================ -- Generated SQL Constraints Drop statements -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia -- drop index idx_iimd5 for ingres \g -- drop index idx_iiid for ingres \g -- drop index idx_siiid for ingres \g -- drop index idx_siips for ingres \g -- drop index idx_iclidnm for ingres \g -- drop index idx_uinm for ingres \g -- drop index idx_uiid for ingres \g -- drop index idx_uauiid for ingres \g -- drop index idx_uiruid for ingres \g -- drop index idx_acid for ingres \g -- drop index idx_usmd5 for ingres \g -- alter table subImageInfo drop constraint fk_iisii-- (is implicitly done) -- alter table imageCategoryList drop constraint fk_iiicl-- (is implicitly done) -- alter table imageAttribute drop constraint fk_iiia-- (is implicitly done) -- alter table userImageRating drop constraint fk_uiuir-- (is implicitly done) -- alter table userAttribute drop constraint fk_uiua-- (is implicitly done) -- alter table userSession drop constraint fk_uius-- (is implicitly done) -- alter table imageAttribute drop constraint fk_iaac-- (is implicitly done) -- alter table userAttribute drop constraint fk_acua-- (is implicitly done) -- Generated Permissions Drops -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia -- revoke all on imageInfo from fmorg for ingres -- revoke select on imageInfo from public for ingres -- revoke all on subImageInfo from fmorg for ingres -- revoke all on imageCategoryList from fmorg for ingres -- revoke select on categoryNames from public for ingres -- revoke all on categoryNames from fmorg for ingres -- revoke all on imageAttribute from fmorg for ingres -- revoke all on userInfo from fmorg for ingres -- revoke all on userAttribute from fmorg for ingres -- revoke all on userImageRating from fmorg for ingres -- revoke all on attributeCategory from fmorg for ingres -- revoke all on userSession from fmorg for ingres -- revoke select on extremes from public for ingres -- revoke all on extremes from fmorg for ingres -- Generated SQL View Drop Statements -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia drop view ratings_view \g drop view whorated_view \g drop view users_view \g -- Generated SQL Schema Drop statements -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia drop table imageInfo \g drop table subImageInfo \g drop table imageCategoryList \g drop table categoryNames \g drop table imageAttribute \g drop table userInfo \g drop table userAttribute \g drop table userImageRating \g drop table attributeCategory \g drop table userSession \g drop table extremes \g -- Generated SQL Schema -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia -- imageInfo create table imageInfo ( id numeric (18) not null, insertionDate date 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) ) \g -- subImageInfo create table subImageInfo ( imageInfo_id numeric (18) not null, pixSize integer not null, constraint pk_SubImageInfo primary key (imageInfo_id,pixSize) ) \g -- imageCategoryList create table imageCategoryList ( imageInfo_id numeric (18) not null, name varchar (32) not null, constraint pk_ImageCategoryList primary key (imageInfo_id,name) ) \g -- categoryNames create table categoryNames ( name varchar (32) not null, constraint pk_CategoryNames primary key (name) ) \g -- 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) ) \g -- userInfo create table userInfo ( id numeric (18) not null, insertionDate date, md5sum char (32), birthDate date, gender char (1), name varchar (32), email varchar (96), currentCategory varchar (32), lastDebitDate date, acctBalance numeric (10,2), active integer, constraint pk_UserInfo primary key (id) ) \g -- 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) ) \g -- 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) ) \g -- attributeCategory create table attributeCategory ( id numeric (18) not null, attributeDesc varchar (128), constraint pk_AttributeCategory primary key (id) ) \g -- userSession create table userSession ( userInfo_id numeric (18) not null, md5sum char (32) not null, insertionDate date, expireDate date, ipAddress varchar (24), constraint pk_UserSession primary key (userInfo_id,md5sum) ) \g -- extremes create table extremes ( name varchar (32) not null, colName varchar (64), minVal numeric (15), maxVal numeric (15), constraint pk_Extremes primary key (name) ) \g -- Generated SQL Views -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: 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 \g -- 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 \g -- users_view create view users_view as select id, birthDate, name ||'<'|| email ||'>' as whoIsThis, currentCategory, acctBalance, active from userInfo order by userInfo.name \g -- Generated Permissions -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia -- grant all on imageInfo to fmorg for ingres -- grant select on imageInfo to public for ingres -- grant all on subImageInfo to fmorg for ingres -- grant all on imageCategoryList to fmorg for ingres -- grant select on categoryNames to public for ingres -- grant all on categoryNames to fmorg for ingres -- grant all on imageAttribute to fmorg for ingres -- grant all on userInfo to fmorg for ingres -- grant all on userAttribute to fmorg for ingres -- grant all on userImageRating to fmorg for ingres -- grant all on attributeCategory to fmorg for ingres -- grant all on userSession to fmorg for ingres -- grant select on extremes to public for ingres -- grant all on extremes to fmorg for ingres -- Generated SQL Insert statements -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia -- inserts for categoryNames -- insert into categoryNames values ( 'Buildings' )-- for ingres -- insert into categoryNames values ( 'Landscapes' )-- for ingres -- insert into categoryNames values ( 'Nudes' )-- for ingres -- insert into categoryNames values ( 'Life Studies' )-- for ingres -- insert into categoryNames values ( 'Portraits' )-- for ingres -- insert into categoryNames values ( 'Abstracts' )-- for ingres -- inserts for attributeCategory -- insert into attributeCategory values ( 1,'Blurriness' )-- for ingres -- insert into attributeCategory values ( 2,'Contrastiness' )-- for ingres -- insert into attributeCategory values ( 3,'Saturation' )-- for ingres -- insert into attributeCategory values ( 4,'Size' )-- for ingres -- insert into attributeCategory values ( 5,'Relevence' )-- for ingres -- Generated SQL Constraints -- -------------------------------------------------------------------- -- Target Database: ingres -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:49:04 2004 -- Input Files: TestERD.dia -- create unique index idx_iimd5 on imageInfo (md5sum) for ingres -- create index idx_iiid on imageInfo (id) for ingres -- create index idx_siiid on subImageInfo (imageInfo_id) for ingres -- create index idx_siips on subImageInfo (pixSize) for ingres -- create index idx_iclidnm on imageCategoryList (imageInfo_id,name) for ingres -- create unique index idx_uinm on userInfo (name,md5sum) for ingres -- create index idx_uiid on userInfo (id) for ingres -- create index idx_uauiid on userAttribute (userInfo_id) for ingres -- create index idx_uiruid on userImageRating (userInfo_id) for ingres -- create index idx_acid on attributeCategory (id) for ingres -- create index idx_usmd5 on userSession (md5sum) for ingres -- alter table subImageInfo add constraint fk_iisii foreign key (imageInfo_id) references imageInfo (id) for ingres -- alter table imageCategoryList add constraint fk_iiicl foreign key (imageinfo_id) references imageInfo (id) for ingres -- alter table imageAttribute add constraint fk_iiia foreign key (imageInfo_id) references imageInfo (id) for ingres -- alter table userImageRating add constraint fk_uiuir foreign key (userInfo_id) references userInfo (id) for ingres -- alter table userAttribute add constraint fk_uiua foreign key (userInfo_id) references userInfo (id) for ingres -- alter table userSession add constraint fk_uius foreign key (userInfo_id) references userInfo (id) for ingres -- alter table imageAttribute add constraint fk_iaac foreign key (attributeCategory_id) references attributeCategory (id) for ingres -- alter table userAttribute add constraint fk_acua foreign key (attributeCategory_id) references attributeCategory (id) for ingres -- ingres requires a special 'quit' command \q --- NEW FILE: oracle-testerd.sql --- -- ================================================================================ -- oracle SQL DDL Script File -- ================================================================================ -- =============================================================================== -- -- Generated by: tedia2sql -- v1.2.13b2 -- See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information -- -- Target Database: oracle -- Generated at: Sat Dec 18 19:48:40 2004 -- Input Files: TestERD.dia -- -- ================================================================================ -- Generated SQL Constraints Drop statements -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia drop index idx_iimd5; drop index idx_iiid; drop index idx_siiid; drop index idx_siips; drop index idx_iclidnm; drop index idx_uinm; drop index idx_uiid; drop index idx_uauiid; drop index idx_uiruid; drop index idx_acid; drop index idx_usmd5; alter table subImageInfo drop constraint fk_iisii ; alter table imageCategoryList drop constraint fk_iiicl ; alter table imageAttribute drop constraint fk_iiia ; alter table userImageRating drop constraint fk_uiuir ; alter table userAttribute drop constraint fk_uiua ; alter table userSession drop constraint fk_uius ; alter table imageAttribute drop constraint fk_iaac ; alter table userAttribute drop constraint fk_acua ; -- Generated Permissions Drops -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia revoke all on imageInfo from fmorg ; revoke select on imageInfo from public ; revoke all on subImageInfo from fmorg ; revoke all on imageCategoryList from fmorg ; revoke select on categoryNames from public ; revoke all on categoryNames from fmorg ; revoke all on imageAttribute from fmorg ; revoke all on userInfo from fmorg ; revoke all on userAttribute from fmorg ; revoke all on userImageRating from fmorg ; revoke all on attributeCategory from fmorg ; revoke all on userSession from fmorg ; revoke select on extremes from public ; revoke all on extremes from fmorg ; -- Special statements for oracle,postgres,db2:pre databases -- statements to do BEFORE creating -- the tables (schema) drop sequence imageInfo_id; create sequence imageInfo_id; -- Generated SQL View Drop Statements -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia drop view ratings_view cascade constraints ; drop view whorated_view cascade constraints ; drop view users_view cascade constraints ; -- Generated SQL Schema Drop statements -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia drop table imageInfo cascade constraints ; drop table subImageInfo cascade constraints ; drop table imageCategoryList cascade constraints ; drop table categoryNames cascade constraints ; drop table imageAttribute cascade constraints ; drop table userInfo cascade constraints ; drop table userAttribute cascade constraints ; drop table userImageRating cascade constraints ; drop table attributeCategory cascade constraints ; drop table userSession cascade constraints ; drop table extremes cascade constraints ; -- Generated SQL Schema -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia -- imageInfo create table imageInfo ( id numeric (18) not null, insertionDate date 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) ) ; -- subImageInfo create table subImageInfo ( imageInfo_id numeric (18) not null, pixSize integer not null, constraint pk_SubImageInfo primary key (imageInfo_id,pixSize) ) ; -- imageCategoryList create table imageCategoryList ( imageInfo_id numeric (18) not null, name varchar (32) not null, constraint pk_ImageCategoryList primary key (imageInfo_id,name) ) ; -- categoryNames create table categoryNames ( name varchar (32) not null, constraint pk_CategoryNames primary key (name) ) ; -- 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) ) ; -- userInfo create table userInfo ( id numeric (18) not null, insertionDate date, md5sum char (32), birthDate date, gender char (1), name varchar (32), email varchar (96), currentCategory varchar (32), lastDebitDate date, acctBalance numeric (10,2), active integer, constraint pk_UserInfo primary key (id) ) ; -- 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) ) ; -- 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) ) ; -- attributeCategory create table attributeCategory ( id numeric (18) not null, attributeDesc varchar (128), constraint pk_AttributeCategory primary key (id) ) ; -- userSession create table userSession ( userInfo_id numeric (18) not null, md5sum char (32) not null, insertionDate date, expireDate date, ipAddress varchar (24), constraint pk_UserSession primary key (userInfo_id,md5sum) ) ; -- extremes create table extremes ( name varchar (32) not null, colName varchar (64), minVal numeric (15), maxVal numeric (15), constraint pk_Extremes primary key (name) ) ; -- Generated SQL Views -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: 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 ; -- 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 ; -- users_view create view users_view as select id, birthDate, name ||'<'|| email ||'>' as whoIsThis, currentCategory, acctBalance, active from userInfo order by userInfo.name ; -- Special statements for oracle,postgres,db2:post databases -- statements to do AFTER creating -- the tables (schema) --drop trigger . . . . --create trigger . . . . -- Generated Permissions -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia grant all on imageInfo to fmorg ; grant select on imageInfo to public ; grant all on subImageInfo to fmorg ; grant all on imageCategoryList to fmorg ; grant select on categoryNames to public ; grant all on categoryNames to fmorg ; grant all on imageAttribute to fmorg ; grant all on userInfo to fmorg ; grant all on userAttribute to fmorg ; grant all on userImageRating to fmorg ; grant all on attributeCategory to fmorg ; grant all on userSession to fmorg ; grant select on extremes to public ; grant all on extremes to fmorg ; -- Generated SQL Insert statements -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia -- inserts for categoryNames insert into categoryNames values ( 'Buildings' ) ; insert into categoryNames values ( 'Landscapes' ) ; insert into categoryNames values ( 'Nudes' ) ; insert into categoryNames values ( 'Life Studies' ) ; insert into categoryNames values ( 'Portraits' ) ; insert into categoryNames values ( 'Abstracts' ) ; -- inserts for attributeCategory insert into attributeCategory values ( 1,'Blurriness' ) ; insert into attributeCategory values ( 2,'Contrastiness' ) ; insert into attributeCategory values ( 3,'Saturation' ) ; insert into attributeCategory values ( 4,'Size' ) ; insert into attributeCategory values ( 5,'Relevence' ) ; -- Generated SQL Constraints -- -------------------------------------------------------------------- -- Target Database: oracle -- SQL Generator: tedia2sql -- v1.2.13b2 -- Generated at: Sat Dec 18 19:48:32 2004 -- Input Files: TestERD.dia create unique index idx_iimd5 on imageInfo (md5sum) ; create index idx_iiid on imageInfo (id) ; create index idx_siiid on subImageInfo (imageInfo_id) ; creat... [truncated message content] |