source: trunk/import/pg-to-oci_keytables.pl @ 358

Revision 245, 5.5 KB checked in by astrid.emde, 8 years ago (diff)

View upper case, Comment on View, Test steht aus

  • Property svn:executable set to *
Line 
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
31open I, "alkis_PostNAS_keytables.sql";
32open O, ">alkis_PostNAS_ORACLE_keytables.sql";
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";
43
44$i = 0;
45print O "-- Automatisch mit pg-to-oci_keytables.pl konvertiert.\n---\n---\n\n";
46
47while(<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
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
125        next if /^COMMENT ON TABLE (geometry_columns|spatial_ref_sys)/;
126
127        next if /^\s*$/;
128
129        print O;
130}
131
132print O "purge recyclebin;\nQUIT;\n";
133close O;
134
135close I;
136
137# vim: set nowrap :
Note: See TracBrowser for help on using the repository browser.