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
|
#*************************************************************************
#
# $RCSfile: ddl.py,v $
#
# $Revision: 1.1.2.5 $
#
# last change: $Author: jbu $ $Date: 2007/01/07 13:50:38 $
#
# The Contents of this file are made available subject to the terms of
# either of the following licenses
#
# - GNU Lesser General Public License Version 2.1
# - Sun Industry Standards Source License Version 1.1
#
# Sun Microsystems Inc., October, 2000
#
# GNU Lesser General Public License Version 2.1
# =============================================
# Copyright 2000 by Sun Microsystems, Inc.
# 901 San Antonio Road, Palo Alto, CA 94303, USA
#
# This library is free software; you can redistribute it and/or
# modify it under the terms of the GNU Lesser General Public
# License version 2.1, as published by the Free Software Foundation.
#
# This library is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with this library; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston,
# MA 02111-1307 USA
#
#
# Sun Industry Standards Source License Version 1.1
# =================================================
# The contents of this file are subject to the Sun Industry Standards
# Source License Version 1.1 (the "License"); You may not use this file
# except in compliance with the License. You may obtain a copy of the
# License at http://www.openoffice.org/license.html.
#
# Software provided under this License is provided on an "AS IS" basis,
# WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING,
# WITHOUT LIMITATION, WARRANTIES THAT THE SOFTWARE IS FREE OF DEFECTS,
# MERCHANTABLE, FIT FOR A PARTICULAR PURPOSE, OR NON-INFRINGING.
# See the License for the specific provisions governing your rights and
# obligations concerning the Software.
#
# The Initial Developer of the Original Code is: Ralph Thomas
#
# Copyright: 2000 by Sun Microsystems, Inc.
#
# All Rights Reserved.
#
# Contributor(s): Ralph Thomas, Joerg Budischewski
#
#*************************************************************************
from com.sun.star.sdbc import SQLException
import sys
def dumpResultSet( rs ):
meta = rs.getMetaData()
for i in range(1, meta.getColumnCount()+1):
sys.stdout.write(meta.getColumnName( i ) + "\t")
sys.stdout.write( "\n" )
while rs.next():
for i in range( 1, meta.getColumnCount()+1):
sys.stdout.write( rs.getString( i ) + "\t" )
sys.stdout.write( "\n" )
rs.beforeFirst()
def executeIgnoringException( stmt, sql ):
try:
stmt.executeUpdate(sql)
except SQLException:
pass
def cleanGroupsAndUsers( stmt ):
rs = stmt.executeQuery("SELECT groname FROM pg_group WHERE groname LIKE 'pqsdbc_%'" )
stmt2 = stmt.getConnection().createStatement()
while rs.next():
stmt2.executeUpdate("DROP GROUP " + rs.getString(1) )
rs.close()
rs = stmt.executeQuery( "SELECT usename FROM pg_user WHERE usename LIKE 'pqsdbc_%'" )
while rs.next():
stmt2.executeUpdate( "DROP USER " + rs.getString(1) )
def executeDDLs( connection ):
stmt = connection.createStatement()
executeIgnoringException( stmt, "DROP VIEW customer2" )
executeIgnoringException( stmt, "DROP TABLE orderpos" )
executeIgnoringException( stmt, "DROP TABLE ordertab" )
executeIgnoringException( stmt, "DROP TABLE product" )
executeIgnoringException( stmt, "DROP TABLE customer" )
executeIgnoringException( stmt, "DROP TABLE blub" )
executeIgnoringException( stmt, "DROP TABLE foo" )
executeIgnoringException( stmt, "DROP TABLE nooid" )
executeIgnoringException( stmt, "DROP TABLE nooid2" )
cleanGroupsAndUsers( stmt )
executeIgnoringException( stmt, "DROP DOMAIN pqsdbc_short" )
executeIgnoringException( stmt, "DROP DOMAIN pqsdbc_amount" )
executeIgnoringException( stmt, "DROP SCHEMA pqsdbc_test" )
ddls = (
"BEGIN",
"CREATE DOMAIN pqsdbc_short AS int2",
"CREATE DOMAIN pqsdbc_amount AS integer",
"CREATE USER pqsdbc_joe",
"CREATE USER pqsdbc_susy",
"CREATE USER pqsdbc_boss",
"CREATE USER pqsdbc_customer", # technical user (e.g. a webfrontend)
"CREATE GROUP pqsdbc_employees WITH USER pqsdbc_joe,pqsdbc_susy",
"CREATE GROUP pqsdbc_admin WITH USER pqsdbc_susy,pqsdbc_boss",
"CREATE SCHEMA pqsdbc_test",
"CREATE TABLE customer ( "+
"id char(8) UNIQUE PRIMARY KEY, "+
"name text, " +
"dummySerial serial UNIQUE) WITH OIDS",
"COMMENT ON TABLE customer IS 'contains customer attributes'",
"COMMENT ON COLUMN customer.id IS 'unique id'",
"CREATE TABLE product ("+
"id char(8) UNIQUE PRIMARY KEY,"+
"name text,"+
"price numeric(10,2),"+
"image bytea) WITH OIDS",
"CREATE TABLE ordertab ( "+
"id char(8) UNIQUE PRIMARY KEY,"+
"customerid char(8) CONSTRAINT cust REFERENCES customer(id) ON DELETE CASCADE ON UPDATE RESTRICT,"+
"orderdate char(8),"+
"delivered boolean ) WITH OIDS",
"CREATE TABLE orderpos ( "+
"orderid char(8) REFERENCES ordertab(id),"+
"id char(3),"+
"productid char(8) REFERENCES product(id),"+
"amount pqsdbc_amount,"+
"shortamount pqsdbc_short,"+
"PRIMARY KEY (orderid,id)) WITH OIDS",
"CREATE TABLE nooid ("+
"id char(8) UNIQUE PRIMARY KEY,"+
"name text) "+
"WITHOUT OIDS",
"CREATE TABLE nooid2 ("+
"id serial UNIQUE PRIMARY KEY,"+
"name text) "+
"WITHOUT OIDS",
"CREATE VIEW customer2 AS SELECT id,name FROM customer",
"GRANT SELECT ON TABLE customer,product,orderpos,ordertab TO pqsdbc_customer",
"GRANT SELECT ON TABLE product TO GROUP pqsdbc_employees",
"GRANT SELECT,UPDATE, INSERT ON TABLE customer TO GROUP pqsdbc_employees",
"GRANT ALL ON TABLE orderpos,ordertab TO GROUP pqsdbc_employees, GROUP pqsdbc_admin",
"GRANT ALL ON TABLE customer TO GROUP pqsdbc_admin", # the admin is allowed to delete customers
"GRANT ALL ON TABLE product TO pqsdbc_boss", # only the boss may change the product table
"INSERT INTO public.customer VALUES ('C1','John Doe')",
"INSERT INTO \"public\" . \"customer\" VALUES ('C2','Bruce Springsteen')",
"INSERT INTO \"public\".product VALUES ('PZZ2','Pizza Mista',6.95,'\\003foo\\005')",
"INSERT INTO product VALUES ('PZZ5','Pizza Funghi',5.95,'\\001foo\\005')",
"INSERT INTO product VALUES ('PAS1','Lasagne',5.49,NULL)",
"INSERT INTO ordertab VALUES ( '1', 'C2', '20030403','true')",
"INSERT INTO ordertab VALUES ( '2', 'C1', '20030402','false')",
"INSERT INTO orderpos VALUES ( '1','001', 'PZZ2',2,0)",
"INSERT INTO orderpos VALUES ( '1','002', 'PZZ5',3,-1)",
"INSERT INTO orderpos VALUES ( '2','001', 'PAS1',5,1)",
"INSERT INTO orderpos VALUES ( '2','002', 'PZZ2',3,2)",
"COMMIT" )
for i in ddls:
stmt.executeUpdate(i)
connection.getTables() # force refresh of metadata
stmt.close()
|
