[241] | 1 | #!/usr/bin/perl |
---|
| 2 | |
---|
| 3 | ############################################################################# |
---|
| 4 | # pg-to-oci_keytables.pl - ALKIS-PostgreSQL-Schema fÃŒr Oracle vorbereiten |
---|
| 5 | # --------------------- |
---|
| 6 | # begin : 2013-01-18 |
---|
| 7 | # copyright : (C) 2013 by Juergen Fischer, norBIT GmbH |
---|
| 8 | # email : jef at norbit dot de |
---|
| 9 | ############################################################################# |
---|
| 10 | # |
---|
| 11 | # Permission is hereby granted, free of charge, to any person obtaining a |
---|
| 12 | # copy of this software and associated documentation files (the "Software"), |
---|
| 13 | # to deal in the Software without restriction, including without limitation |
---|
| 14 | # the rights to use, copy, modify, merge, publish, distribute, sublicense, |
---|
| 15 | # and/or sell copies of the Software, and to permit persons to whom the |
---|
| 16 | # Software is furnished to do so, subject to the following conditions: |
---|
| 17 | # |
---|
| 18 | # The above copyright notice and this permission notice shall be included |
---|
| 19 | # in all copies or substantial portions of the Software. |
---|
| 20 | # |
---|
| 21 | # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS |
---|
| 22 | # OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, |
---|
| 23 | # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL |
---|
| 24 | # THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER |
---|
| 25 | # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING |
---|
| 26 | # FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER |
---|
| 27 | # DEALINGS IN THE SOFTWARE. |
---|
| 28 | # |
---|
| 29 | ############################################################################# |
---|
| 30 | |
---|
| 31 | open I, "alkis_PostNAS_keytables.sql"; |
---|
| 32 | open O, ">alkis_PostNAS_ORACLE_keytables.sql"; |
---|
[245] | 33 | #open I, "nutzungsart_definition.sql"; |
---|
| 34 | #open O, ">nutzungsart_definition_ORACLE.sql"; |
---|
| 35 | #open I, "nutzungsart_laden.sql"; |
---|
| 36 | #open O, ">nutzungsart_laden_ORACLE.sql"; |
---|
| 37 | #open I, "pp_definition.sql"; |
---|
| 38 | #open O, ">pp_definition_ORACLE.sql"; |
---|
| 39 | #open I, "pp_laden.sql"; |
---|
| 40 | #open O, ">pp_laden_ORACLE.sql"; |
---|
| 41 | #open I, "sichten.sql"; |
---|
| 42 | #open O, ">sichten_ORACLE.sql"; |
---|
[241] | 43 | |
---|
| 44 | $i = 0; |
---|
| 45 | print O "-- Automatisch mit pg-to-oci_keytables.pl konvertiert.\n---\n---\n\n"; |
---|
| 46 | |
---|
| 47 | while(<I>) { |
---|
| 48 | # Kommentare entfernen |
---|
| 49 | s/,\s*--.*$/,/; |
---|
| 50 | s/;\s*--.*$/;/; |
---|
| 51 | s/\s*--.*$//; |
---|
| 52 | |
---|
| 53 | s/^\s+SET client_encoding = 'UTF8';/set serveroutput on\nset autocommit on\nset feedback off\nset verify off\n/; |
---|
| 54 | s/^\s+SET default_with_oids = false;/define alkis_epsg=\&1/; |
---|
| 55 | s/\\set ON_ERROR_STOP/whenever sqlerror exit 1/; |
---|
| 56 | |
---|
| 57 | # Funktionsinclude ÃŒberspringen |
---|
| 58 | next if /\\i\s+alkis-functions/; |
---|
| 59 | |
---|
| 60 | # Includes umwandeln |
---|
| 61 | s/\\i alkis-trigger.sql/@@ alkis-trigger-oracle.sql/; |
---|
| 62 | |
---|
| 63 | # Datentypen abbilden |
---|
| 64 | s/varchar\s+default\s+false/varchar2(5) default 'false'/i; |
---|
| 65 | s/varchar\s+default\s+'false'/varchar2(5) default 'false'/i; |
---|
| 66 | s/boolean\s+default\s+false/varchar2(5) default 'false'/i; |
---|
| 67 | s/\bvarchar\[\]/varchar2(2047)/; |
---|
| 68 | s/\binteger\[\]/varchar2(2047)/; |
---|
| 69 | s/\bvarchar\b,/varchar2(2047),/; |
---|
| 70 | s/\bdouble precision\[\]/varchar2(2047)/; |
---|
| 71 | s/serial/integer/; |
---|
| 72 | s/boolean/varchar2(5)/; |
---|
| 73 | |
---|
| 74 | # Feldnamen auf OCI Defaults abbilden |
---|
| 75 | s/wkb_geometry/ora_geometry/; |
---|
| 76 | s/ogc_fid/ogr_fid/; |
---|
| 77 | |
---|
| 78 | s/USING btree //; |
---|
| 79 | |
---|
| 80 | s/SELECT alkis_drop\(\);/-- $1/; |
---|
| 81 | |
---|
| 82 | # Indexnamen ersetzen |
---|
| 83 | if( /CREATE( UNIQUE)? INDEX (\S+)/ ) { |
---|
| 84 | s/INDEX \S+/INDEX ALKIS_KEY_$i/; |
---|
| 85 | $i++; |
---|
| 86 | } |
---|
| 87 | |
---|
| 88 | # Constraintnamen ersetzen |
---|
| 89 | if( /CONSTRAINT (\S+)/ ) { |
---|
| 90 | s/CONSTRAINT \S+/CONSTRAINT ALKIS_KEY_$i/; |
---|
| 91 | $i++; |
---|
| 92 | } |
---|
| 93 | |
---|
| 94 | # RÀumliche Indizes konvertieren |
---|
| 95 | s/CREATE\s+INDEX\s+(\S+)\s+ON\s+(\S+)\s+USING\s+gist\s*\((\S+)\);/CREATE INDEX \U$1\E ON \U$2\E(\U$3\E) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARALLEL;/i; |
---|
| 96 | |
---|
| 97 | # Tabelle ggf. vor dem Anlegen droppen und aus den Metadaten löschen |
---|
| 98 | s/CREATE TABLE (\S+)/\nDELETE FROM user_sdo_geom_metadata WHERE upper(table_name)='\U$1\E';\nBEGIN EXECUTE IMMEDIATE 'DROP TABLE \U$1\E CASCADE CONSTRAINTS'; EXCEPTION WHEN OTHERS THEN NULL; END;\n\/\nCREATE TABLE \U$1/; |
---|
| 99 | |
---|
| 100 | # Anlegen der Geometriespalte anpassen und fÃŒr Metadateneintrag sorgen |
---|
| 101 | s/SELECT AddGeometryColumn\('([^']+)','([^']+)',:alkis_epsg,'[^']+',2\);.*$/ALTER TABLE \U$1\E ADD \U$2\E MDSYS.SDO_GEOMETRY;\nINSERT INTO user_sdo_geom_metadata\(table_name,column_name,srid,diminfo\) VALUES \('\U$1\E','\U$2\E',&&alkis_epsg,mdsys.sdo_dim_array\(mdsys.sdo_dim_element\('X',200000,800000,0.001\),mdsys.sdo_dim_element\('Y',5200000,6100000,0.001\)\)\);/; |
---|
| 102 | s/SELECT AddGeometryColumn\('([^']+)','([^']+)',:alkis_epsg,'[^']+',3\);.*$/ALTER TABLE \U$1\E ADD \U$2\E MDSYS.SDO_GEOMETRY;\nINSERT INTO user_sdo_geom_metadata\(table_name,column_name,srid,diminfo\) VALUES \('\U$1\E','\U$2\E',&&alkis_epsg,mdsys.sdo_dim_array\(mdsys.sdo_dim_element\('X',200000,800000,0.001\),mdsys.sdo_dim_element\('Y',5200000,6100000,0.001\),mdsys.sdo_dim_element\('Z',-50,3000,0.001\)\)\);/; |
---|
| 103 | |
---|
| 104 | # DELETE-SchlÃŒsselwort quoten |
---|
| 105 | s/"delete"/"DELETE"/; |
---|
| 106 | s/ALTER TABLE DELETE/ALTER TABLE "DELETE"/; |
---|
| 107 | s/'"DELETE"'/'DELETE'/g; |
---|
| 108 | s/COMMENT ON COLUMN delete/COMMENT ON COLUMN "DELETE"/; |
---|
| 109 | |
---|
| 110 | # Oracle kennt keine Indexkommentare |
---|
| 111 | s/COMMENT ON INDEX .*$/\n/; |
---|
| 112 | |
---|
| 113 | # Ãberlange Bezeichner kÃŒrzen und in GroÃbuchstaben wandeln |
---|
| 114 | s/([A-Z_]{30})[A-Z_]+/\U$1/gi; |
---|
| 115 | |
---|
| 116 | # Insert Tabellennamen in uppercase |
---|
| 117 | s/INSERT INTO (\S+)/INSERT INTO \U$1\E/; |
---|
| 118 | |
---|
[245] | 119 | # View Tabellennamen in uppercase |
---|
| 120 | s/CREATE OR REPLACE VIEW (\S+)/CREATE OR REPLACE VIEW \U$1\E/; |
---|
| 121 | |
---|
| 122 | # View Comment in uppercase |
---|
| 123 | s/COMMENT ON VIEW (\S+)/COMMENT ON VIEW \U$1\E/; |
---|
| 124 | |
---|
[241] | 125 | next if /^COMMENT ON TABLE (geometry_columns|spatial_ref_sys)/; |
---|
| 126 | |
---|
| 127 | next if /^\s*$/; |
---|
| 128 | |
---|
| 129 | print O; |
---|
| 130 | } |
---|
| 131 | |
---|
| 132 | print O "purge recyclebin;\nQUIT;\n"; |
---|
| 133 | close O; |
---|
| 134 | |
---|
| 135 | close I; |
---|
| 136 | |
---|
| 137 | # vim: set nowrap : |
---|