connectivity/workben/postgresql/ddl.py

   1 #*************************************************************************
   2 #
   3 #   $RCSfile: ddl.py,v $
   4 #
   5 #   $Revision: 1.1.2.5 $
   6 #
   7 #   last change: $Author: jbu $ $Date: 2007/01/07 13:50:38 $
   8 #
   9 #   The Contents of this file are made available subject to the terms of
  10 #   either of the following licenses
  11 #
  12 #          - GNU Lesser General Public License Version 2.1
  13 #          - Sun Industry Standards Source License Version 1.1
  14 #
  15 #   Sun Microsystems Inc., October, 2000
  16 #
  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
  21 #
  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.
  25 #
  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.
  30 #
  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,
  34 #   MA  02111-1307  USA
  35 #
  36 #
  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.
  43 #
  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.
  50 #
  51 #   The Initial Developer of the Original Code is: Ralph Thomas
  52 #
  53 #   Copyright: 2000 by Sun Microsystems, Inc.
  54 #
  55 #   All Rights Reserved.
  56 #
  57 #   Contributor(s): Ralph Thomas, Joerg Budischewski
  58 #
  59 #*************************************************************************
  60 from com.sun.star.sdbc import SQLException
  61 import sys
  62
  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" )
  68     while rs.next():
  69         for i in range( 1, meta.getColumnCount()+1):
  70             sys.stdout.write( rs.getString( i ) + "\t" )
  71         sys.stdout.write( "\n" )
  72     rs.beforeFirst()
  73
  74
  75
  76 def executeIgnoringException( stmt, sql ):
  77     try:
  78         stmt.executeUpdate(sql)
  79     except SQLException:
  80         pass
  81
  82 def cleanGroupsAndUsers( stmt ):
  83     rs = stmt.executeQuery("SELECT groname FROM pg_group WHERE groname LIKE 'pqsdbc_%'" )
  84     stmt2 = stmt.getConnection().createStatement()
  85     while rs.next():
  86         stmt2.executeUpdate("DROP GROUP " + rs.getString(1) )
  87
  88     rs.close()
  89     rs = stmt.executeQuery( "SELECT usename FROM pg_user WHERE usename LIKE 'pqsdbc_%'" )
  90     while rs.next():
  91         stmt2.executeUpdate( "DROP USER " + rs.getString(1) )
  92
  93
  94
  95
  96 def executeDDLs( connection ):
  97
  98     stmt = connection.createStatement()
  99
 100
 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" )
 114
 115     ddls = (
 116         "BEGIN",
 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, "+
 128               "name text, " +
 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,"+
 134              "name text,"+
 135              "price numeric(10,2),"+
 136              "image bytea) WITH OIDS",
 137
 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),"+
 145               "id char(3),"+
 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,"+
 152              "name text) "+
 153              "WITHOUT OIDS",
 154         "CREATE TABLE nooid2 ("+
 155              "id serial UNIQUE PRIMARY KEY,"+
 156              "name text) "+
 157              "WITHOUT OIDS",
 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')",
 167
 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)",
 171
 172         "INSERT INTO ordertab VALUES ( '1', 'C2', '20030403','true')",
 173         "INSERT INTO ordertab VALUES ( '2', 'C1', '20030402','false')",
 174
 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)",
 179         "COMMIT" )
 180     for i in ddls:
 181         stmt.executeUpdate(i)
 182
 183     connection.getTables() # force refresh of metadata
 184
 185     stmt.close()