1 #*************************************************************************
7 # last change: $Author: jbu $ $Date: 2007/01/07 13:50:38 $
9 # The Contents of this file are made available subject to the terms of
10 # either of the following licenses
12 # - GNU Lesser General Public License Version 2.1
13 # - Sun Industry Standards Source License Version 1.1
15 # Sun Microsystems Inc., October, 2000
17 # GNU Lesser General Public License Version 2.1
18 # =============================================
19 # Copyright 2000 by Sun Microsystems, Inc.
20 # 901 San Antonio Road, Palo Alto, CA 94303, USA
22 # This library is free software; you can redistribute it and/or
23 # modify it under the terms of the GNU Lesser General Public
24 # License version 2.1, as published by the Free Software Foundation.
26 # This library is distributed in the hope that it will be useful,
27 # but WITHOUT ANY WARRANTY; without even the implied warranty of
28 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
29 # Lesser General Public License for more details.
31 # You should have received a copy of the GNU Lesser General Public
32 # License along with this library; if not, write to the Free Software
33 # Foundation, Inc., 59 Temple Place, Suite 330, Boston,
37 # Sun Industry Standards Source License Version 1.1
38 # =================================================
39 # The contents of this file are subject to the Sun Industry Standards
40 # Source License Version 1.1 (the "License"); You may not use this file
41 # except in compliance with the License. You may obtain a copy of the
42 # License at http://www.openoffice.org/license.html.
44 # Software provided under this License is provided on an "AS IS" basis,
45 # WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING,
46 # WITHOUT LIMITATION, WARRANTIES THAT THE SOFTWARE IS FREE OF DEFECTS,
47 # MERCHANTABLE, FIT FOR A PARTICULAR PURPOSE, OR NON-INFRINGING.
48 # See the License for the specific provisions governing your rights and
49 # obligations concerning the Software.
51 # The Initial Developer of the Original Code is: Ralph Thomas
53 # Copyright: 2000 by Sun Microsystems, Inc.
55 # All Rights Reserved.
57 # Contributor(s): Ralph Thomas, Joerg Budischewski
59 #*************************************************************************
60 from com
.sun
.star
.sdbc
import SQLException
63 def dumpResultSet( rs
):
64 meta
= rs
.getMetaData()
65 for i
in range(1, meta
.getColumnCount()+1):
66 sys
.stdout
.write(meta
.getColumnName( i
) + "\t")
67 sys
.stdout
.write( "\n" )
69 for i
in range( 1, meta
.getColumnCount()+1):
70 sys
.stdout
.write( rs
.getString( i
) + "\t" )
71 sys
.stdout
.write( "\n" )
76 def executeIgnoringException( stmt
, sql
):
78 stmt
.executeUpdate(sql
)
82 def cleanGroupsAndUsers( stmt
):
83 rs
= stmt
.executeQuery("SELECT groname FROM pg_group WHERE groname LIKE 'pqsdbc_%'" )
84 stmt2
= stmt
.getConnection().createStatement()
86 stmt2
.executeUpdate("DROP GROUP " + rs
.getString(1) )
89 rs
= stmt
.executeQuery( "SELECT usename FROM pg_user WHERE usename LIKE 'pqsdbc_%'" )
91 stmt2
.executeUpdate( "DROP USER " + rs
.getString(1) )
96 def executeDDLs( connection
):
98 stmt
= connection
.createStatement()
101 executeIgnoringException( stmt
, "DROP VIEW customer2" )
102 executeIgnoringException( stmt
, "DROP TABLE orderpos" )
103 executeIgnoringException( stmt
, "DROP TABLE ordertab" )
104 executeIgnoringException( stmt
, "DROP TABLE product" )
105 executeIgnoringException( stmt
, "DROP TABLE customer" )
106 executeIgnoringException( stmt
, "DROP TABLE blub" )
107 executeIgnoringException( stmt
, "DROP TABLE foo" )
108 executeIgnoringException( stmt
, "DROP TABLE nooid" )
109 executeIgnoringException( stmt
, "DROP TABLE nooid2" )
110 cleanGroupsAndUsers( stmt
)
111 executeIgnoringException( stmt
, "DROP DOMAIN pqsdbc_short" )
112 executeIgnoringException( stmt
, "DROP DOMAIN pqsdbc_amount" )
113 executeIgnoringException( stmt
, "DROP SCHEMA pqsdbc_test" )
117 "CREATE DOMAIN pqsdbc_short AS int2",
118 "CREATE DOMAIN pqsdbc_amount AS integer",
119 "CREATE USER pqsdbc_joe",
120 "CREATE USER pqsdbc_susy",
121 "CREATE USER pqsdbc_boss",
122 "CREATE USER pqsdbc_customer", # technical user (e.g. a webfrontend)
123 "CREATE GROUP pqsdbc_employees WITH USER pqsdbc_joe,pqsdbc_susy",
124 "CREATE GROUP pqsdbc_admin WITH USER pqsdbc_susy,pqsdbc_boss",
125 "CREATE SCHEMA pqsdbc_test",
126 "CREATE TABLE customer ( "+
127 "id char(8) UNIQUE PRIMARY KEY, "+
129 "dummySerial serial UNIQUE) WITH OIDS",
130 "COMMENT ON TABLE customer IS 'contains customer attributes'",
131 "COMMENT ON COLUMN customer.id IS 'unique id'",
132 "CREATE TABLE product ("+
133 "id char(8) UNIQUE PRIMARY KEY,"+
135 "price numeric(10,2),"+
136 "image bytea) WITH OIDS",
138 "CREATE TABLE ordertab ( "+
139 "id char(8) UNIQUE PRIMARY KEY,"+
140 "customerid char(8) CONSTRAINT cust REFERENCES customer(id) ON DELETE CASCADE ON UPDATE RESTRICT,"+
141 "orderdate char(8),"+
142 "delivered boolean ) WITH OIDS",
143 "CREATE TABLE orderpos ( "+
144 "orderid char(8) REFERENCES ordertab(id),"+
146 "productid char(8) REFERENCES product(id),"+
147 "amount pqsdbc_amount,"+
148 "shortamount pqsdbc_short,"+
149 "PRIMARY KEY (orderid,id)) WITH OIDS",
150 "CREATE TABLE nooid ("+
151 "id char(8) UNIQUE PRIMARY KEY,"+
154 "CREATE TABLE nooid2 ("+
155 "id serial UNIQUE PRIMARY KEY,"+
158 "CREATE VIEW customer2 AS SELECT id,name FROM customer",
159 "GRANT SELECT ON TABLE customer,product,orderpos,ordertab TO pqsdbc_customer",
160 "GRANT SELECT ON TABLE product TO GROUP pqsdbc_employees",
161 "GRANT SELECT,UPDATE, INSERT ON TABLE customer TO GROUP pqsdbc_employees",
162 "GRANT ALL ON TABLE orderpos,ordertab TO GROUP pqsdbc_employees, GROUP pqsdbc_admin",
163 "GRANT ALL ON TABLE customer TO GROUP pqsdbc_admin", # the admin is allowed to delete customers
164 "GRANT ALL ON TABLE product TO pqsdbc_boss", # only the boss may change the product table
165 "INSERT INTO public.customer VALUES ('C1','John Doe')",
166 "INSERT INTO \"public\" . \"customer\" VALUES ('C2','Bruce Springsteen')",
168 "INSERT INTO \"public\".product VALUES ('PZZ2','Pizza Mista',6.95,'\\003foo\\005')",
169 "INSERT INTO product VALUES ('PZZ5','Pizza Funghi',5.95,'\\001foo\\005')",
170 "INSERT INTO product VALUES ('PAS1','Lasagne',5.49,NULL)",
172 "INSERT INTO ordertab VALUES ( '1', 'C2', '20030403','true')",
173 "INSERT INTO ordertab VALUES ( '2', 'C1', '20030402','false')",
175 "INSERT INTO orderpos VALUES ( '1','001', 'PZZ2',2,0)",
176 "INSERT INTO orderpos VALUES ( '1','002', 'PZZ5',3,-1)",
177 "INSERT INTO orderpos VALUES ( '2','001', 'PAS1',5,1)",
178 "INSERT INTO orderpos VALUES ( '2','002', 'PZZ2',3,2)",
181 stmt
.executeUpdate(i
)
183 connection
.getTables() # force refresh of metadata