[Spidertree-cvs] spidertree/db spidertree.PGModel,1.1.1.1,1.2 spidertree_create.sql,1.1.1.1,1.2
Status: Pre-Alpha
Brought to you by:
spiderr
|
From: <sp...@us...> - 2004-02-07 21:08:32
|
Update of /cvsroot/spidertree/spidertree/db In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv7635/db Modified Files: spidertree.PGModel spidertree_create.sql Log Message: working on upload Index: spidertree.PGModel =================================================================== RCS file: /cvsroot/spidertree/spidertree/db/spidertree.PGModel,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -u -d -r1.1.1.1 -r1.2 --- spidertree.PGModel 7 Feb 2004 16:21:28 -0000 1.1.1.1 +++ spidertree.PGModel 7 Feb 2004 21:05:35 -0000 1.2 @@ -1,4 +1,4 @@ -<PGModel Align="68" DisplayFlags="1" LineColor="#000000" FillStyle="none" LineWidth="1" FontUnderline="0" FillColor="#ffffff" LineStyle="Solid" FontColor="#000000" FontPointSize="9" DatabaseType="PostgreSQL" FontBold="0" DatabaseName="spidertree" FontItalic="0" Author="Christian Fowler" TextFlag="2048" FontStrikeOut="0" Name="Spidertree" Version="1.0" NextOID="2" FontFamily="Helvetica [Urw]" > +<PGModel Align="68" DisplayFlags="1" LineColor="#000000" FillStyle="none" LineWidth="1" FontUnderline="0" FillColor="#ffffff" LineStyle="Solid" FontColor="#000000" FontPointSize="9" DatabaseType="PostgreSQL" FontBold="0" DatabaseName="spidertree" FontItalic="0" Author="Christian Fowler" TextFlag="2048" FontStrikeOut="0" Name="Spidertree" Version="1.0" NextOID="8" FontFamily="Helvetica [Urw]" > <Validation> <Rule Name="Reference name missing" Value="Warning" /> <Rule Name="Reference name duplicate" Value="Warning" /> @@ -47,9 +47,14 @@ <Rule Name="Inherit derived table missing" Value="Error" /> <Rule Name="Inherit base table missing" Value="Error" /> </Validation> - <PGTable width="185" x="57" TextField="2" Align="68" y="50" LineColor="#000000" FillStyle="Solid" LineWidth="1" FontUnderline="0" FillColor="#ffffff" LineStyle="Solid" FontColor="#000000" FontPointSize="9" OID="1" height="112" FontBold="0" FontItalic="0" ScriptCreate="1" TextFlag="2048" FontStrikeOut="0" Name="album" id="0x83fbe70" NextOID="8" ScriptCreateDrop="1" FontFamily="Luxi Sans" > + <PGSequence OID="4" ScriptCreate="1" Name="album_id_seq" NextOID="1" ScriptCreateDrop="1" /> + <PGSequence OID="7" ScriptCreate="1" Name="image_id_seq" NextOID="1" ScriptCreateDrop="0" /> + <PGTable width="185" x="31" TextField="2" Align="68" y="41" LineColor="#000000" FillStyle="Solid" LineWidth="1" FontUnderline="0" FillColor="#ffffff" LineStyle="Solid" FontColor="#000000" FontPointSize="9" OID="1" height="168" FontBold="0" FontItalic="0" ScriptCreate="1" TextFlag="2048" FontStrikeOut="0" Name="album" id="0x83fbe70" NextOID="11" ScriptCreateDrop="1" FontFamily="Sans" > + <sink x="0.924324" y="0.119048" OID="8" id="0x860cc00" /> <PGColumn PrimaryKey="1" OID="1" DataType="integer" Name="album_id" NextOID="1" Mandatory="1" /> <PGColumn ForeignKey="1" OID="6" DataType="integer" Name="user_id" NextOID="1" Mandatory="1" /> + <PGColumn OID="9" DataType="timestamp" Name="create_date" Default="'now'" NextOID="1" Mandatory="1" /> + <PGColumn OID="10" DataType="timestamp" Name="modify_date" Default="'now'" NextOID="1" Mandatory="1" /> <PGColumn OID="4" DataType="ltree" Name="path" NextOID="1" Mandatory="1" /> <PGColumn OID="2" Length="256" DataType="varchar" Name="title" NextOID="1" Mandatory="1" /> <PGColumn OID="3" DataType="bit" Name="is_private" NextOID="1" /> @@ -60,4 +65,37 @@ <PGIndexColumn OID="1" Order="" Name="user_id" NextOID="1" /> </PGIndex> </PGTable> + <PGTable width="182" x="505" TextField="2" Align="68" y="41" LineColor="#000000" FillStyle="Solid" LineWidth="1" FontUnderline="0" FillColor="#ffffff" LineStyle="Solid" FontColor="#000000" FontPointSize="9" OID="5" height="251" FontBold="0" FontItalic="0" ScriptCreate="1" TextFlag="2048" FontStrikeOut="0" Name="image" id="0x8483020" NextOID="8" ScriptCreateDrop="0" FontFamily="Sans" > + <sink x="0.0879121" y="0.0836653" OID="7" id="0x84bbeb0" /> + <PGColumn PrimaryKey="1" OID="2" DataType="integer" Name="image_id" NextOID="1" Mandatory="1" /> + <PGColumn OID="3" Length="256" DataType="varchar" Name="file_name" NextOID="1" Mandatory="1" /> + <PGColumn OID="4" Length="256" DataType="varchar" Name="title" NextOID="1" Mandatory="1" /> + <PGColumn OID="5" DataType="text" Name="descr" NextOID="1" /> + <PGIndex Unique="1" OID="6" Name="image_image_id_idx" NextOID="2" > + <PGIndexColumn OID="1" Order="" Name="image_id" NextOID="1" /> + </PGIndex> + </PGTable> + <PGTable width="136" x="290" TextField="2" Align="68" y="88" LineColor="#000000" FillStyle="Solid" LineWidth="1" FontUnderline="0" FillColor="#ffffff" LineStyle="Solid" FontColor="#000000" FontPointSize="9" OID="6" height="59" FontBold="0" FontItalic="0" ScriptCreate="1" TextFlag="2048" FontStrikeOut="0" Name="album_map" id="0x8624950" NextOID="6" ScriptCreateDrop="0" FontFamily="Sans" > + <sink x="0.419118" y="0.305085" OID="1" id="0x854c2e8" /> + <sink x="0.610294" y="0.745763" OID="2" id="0x861ac98" /> + <PGColumn ForeignKey="1" OID="3" DataType="integer" Name="album_id" NextOID="1" Mandatory="1" /> + <PGColumn ForeignKey="1" OID="4" DataType="integer" Name="image_id" NextOID="1" Mandatory="1" /> + <PGIndex OID="5" Name="album_map_album_id_idx" NextOID="2" > + <PGIndexColumn OID="1" Order="" Name="album_id" NextOID="1" /> + </PGIndex> + </PGTable> + <PGReference TextField="3" Align="68" LineColor="#000000" FillStyle="none" LineWidth="1" EndArrowDirection="520550350" EndArrowType="2" FontUnderline="0" FillColor="#000000" LineStyle="Solid" FontColor="#000000" FontPointSize="9" OID="2" BeginArrowDirection="-872351970" BeginArrowType="0" FontBold="0" Child="album_map" FontItalic="0" ScriptCreate="1" Parent="image" EndTruncate="1" TextFlag="2048" FontStrikeOut="0" Name="album_map_image_ref" BeginTruncate="1" id="0x8625e98" NextOID="6" ScriptCreateDrop="0" FontFamily="Sans" > + <point x="373" y="132" sinkobjectid="0x8624950" OID="1" id="0x8549f48" sinkid="0x861ac98" /> + <point x="373" y="61" OID="2" id="0x8548520" /> + <point x="521" y="62" sinkobjectid="0x8483020" OID="3" id="0x85ec498" sinkid="0x84bbeb0" /> + <texthandle x="0.0447761" y="0.243243" OID="4" /> + <ColumnRef OID="5" Child="album_id" Parent="image_id" NextOID="1" /> + </PGReference> + <PGReference TextField="3" Align="68" LineColor="#000000" FillStyle="none" LineWidth="1" EndArrowDirection="41" EndArrowType="2" FontUnderline="0" FillColor="#000000" LineStyle="Solid" FontColor="#000000" FontPointSize="9" OID="3" BeginArrowDirection="0" BeginArrowType="0" FontBold="0" Child="album_map" FontItalic="0" ScriptCreate="1" Parent="album" EndTruncate="1" TextFlag="2048" FontStrikeOut="0" Name="album_map_album_ref" BeginTruncate="1" id="0x8555d28" NextOID="6" ScriptCreateDrop="0" FontFamily="Sans" > + <point x="347" y="106" sinkobjectid="0x8624950" OID="1" id="0x8615d58" sinkid="0x854c2e8" /> + <point x="347" y="61" OID="2" id="0x8624f68" /> + <point x="202" y="61" sinkobjectid="0x83fbe70" OID="3" id="0x84f7cb8" sinkid="0x860cc00" /> + <texthandle x="0.171233" y="0.326087" OID="4" /> + <ColumnRef OID="5" Child="album_id" Parent="album_id" NextOID="1" /> + </PGReference> </PGModel> Index: spidertree_create.sql =================================================================== RCS file: /cvsroot/spidertree/spidertree/db/spidertree_create.sql,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -u -d -r1.1.1.1 -r1.2 --- spidertree_create.sql 7 Feb 2004 16:21:28 -0000 1.1.1.1 +++ spidertree_create.sql 7 Feb 2004 21:05:35 -0000 1.2 @@ -9,6 +9,17 @@ -- +--------------------------------------------------------- -- | DROP -- +--------------------------------------------------------- +DROP SEQUENCE image_id_seq; +CREATE TABLE temp AS SELECT * FROM album_map; +DROP TABLE album_map; +CREATE TABLE album_map AS SELECT * FROM temp; +DROP TABLE temp; +CREATE TABLE temp AS SELECT * FROM album_map; +DROP TABLE album_map; +CREATE TABLE album_map AS SELECT * FROM temp; +DROP TABLE temp; +DROP TABLE album_map; +DROP TABLE image; DROP SEQUENCE album_id_seq; DROP TABLE album; -- +--------------------------------------------------------- @@ -18,6 +29,8 @@ ( album_id integer NOT NULL, user_id integer NOT NULL, + create_date timestamp NOT NULL DEFAULT 'now', + modify_date timestamp NOT NULL DEFAULT 'now', path ltree NOT NULL, title varchar(256) NOT NULL, is_private bit, @@ -34,3 +47,35 @@ ); CREATE SEQUENCE album_id_seq; +CREATE TABLE image +( + image_id integer NOT NULL, + file_name varchar(256) NOT NULL, + title varchar(256) NOT NULL, + descr text, + PRIMARY KEY (image_id) +); +CREATE UNIQUE INDEX image_image_id_idx ON image +( + image_id +); + +CREATE TABLE album_map +( + album_id integer NOT NULL, + image_id integer NOT NULL +); +CREATE INDEX album_map_album_id_idx ON album_map +( + album_id +); + +ALTER TABLE album_map ADD CONSTRAINT album_map_image_ref + FOREIGN KEY ( album_id ) + REFERENCES image ( image_id ) + NOT DEFERRABLE; +ALTER TABLE album_map ADD CONSTRAINT album_map_album_ref + FOREIGN KEY ( album_id ) + REFERENCES album ( album_id ) + NOT DEFERRABLE; +CREATE SEQUENCE image_id_seq; |