diff options
Diffstat (limited to 'connectivity/workben/postgresql/ddl.py')
| -rw-r--r-- | connectivity/workben/postgresql/ddl.py | 185 |
1 files changed, 185 insertions, 0 deletions
diff --git a/connectivity/workben/postgresql/ddl.py b/connectivity/workben/postgresql/ddl.py new file mode 100644 index 000000000000..4c1c5e2c5275 --- /dev/null +++ b/connectivity/workben/postgresql/ddl.py @@ -0,0 +1,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() |