1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
|
-- ================================================================================
-- mysql SQL DDL Script File
-- ================================================================================
-- ===============================================================================
--
-- Generated by: tedia2sql -- v1.2.12b3
-- See http://tedia2sql.tigris.org/AUTHORS.html for tedia2sql author information
--
-- Target Database: mysql
-- Generated at: Wed Dec 1 00:30:59 2004
-- Input Files: TestERD.dia
--
-- ================================================================================
-- Generated SQL Constraints Drop statements
-- --------------------------------------------------------------------
-- Target Database: mysql
-- SQL Generator: tedia2sql -- v1.2.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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.12b3
-- Generated at: Wed Dec 1 00:30:51 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
|
