Index: /trunk/import/pg-to-oci_keytables.pl
===================================================================
--- /trunk/import/pg-to-oci_keytables.pl	(revision 241)
+++ /trunk/import/pg-to-oci_keytables.pl	(revision 241)
@@ -0,0 +1,121 @@
+#!/usr/bin/perl
+
+#############################################################################
+#    pg-to-oci_keytables.pl - ALKIS-PostgreSQL-Schema fÃŒr Oracle vorbereiten
+#    ---------------------
+#    begin                : 2013-01-18
+#    copyright            : (C) 2013 by Juergen Fischer, norBIT GmbH
+#    email                : jef at norbit dot de
+#############################################################################
+#                                                                         
+# Permission is hereby granted, free of charge, to any person obtaining a
+# copy of this software and associated documentation files (the "Software"),
+# to deal in the Software without restriction, including without limitation
+# the rights to use, copy, modify, merge, publish, distribute, sublicense,
+# and/or sell copies of the Software, and to permit persons to whom the
+# Software is furnished to do so, subject to the following conditions:
+#
+# The above copyright notice and this permission notice shall be included
+# in all copies or substantial portions of the Software.
+#
+# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
+# OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
+# THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
+# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
+# DEALINGS IN THE SOFTWARE.
+#                                                                         
+#############################################################################
+
+open I, "alkis_PostNAS_keytables.sql";
+open O, ">alkis_PostNAS_ORACLE_keytables.sql";
+
+$i = 0;
+print O "-- Automatisch mit pg-to-oci_keytables.pl konvertiert.\n---\n---\n\n";
+
+while(<I>) {
+	# Kommentare entfernen
+	s/,\s*--.*$/,/;
+	s/;\s*--.*$/;/;
+	s/\s*--.*$//;
+
+	s/^\s+SET client_encoding = 'UTF8';/set serveroutput on\nset autocommit on\nset feedback off\nset verify off\n/;
+	s/^\s+SET default_with_oids = false;/define alkis_epsg=\&1/;
+	s/\\set ON_ERROR_STOP/whenever sqlerror exit 1/;
+
+	# Funktionsinclude ÃŒberspringen
+	next if /\\i\s+alkis-functions/;
+	
+	# Includes umwandeln
+	s/\\i alkis-trigger.sql/@@ alkis-trigger-oracle.sql/;
+
+	# Datentypen abbilden
+	s/varchar\s+default\s+false/varchar2(5) default 'false'/i;
+	s/varchar\s+default\s+'false'/varchar2(5) default 'false'/i;
+	s/boolean\s+default\s+false/varchar2(5) default 'false'/i;
+	s/\bvarchar\[\]/varchar2(2047)/;
+	s/\binteger\[\]/varchar2(2047)/;
+	s/\bvarchar\b,/varchar2(2047),/;
+	s/\bdouble precision\[\]/varchar2(2047)/;
+	s/serial/integer/;
+	s/boolean/varchar2(5)/;
+
+	# Feldnamen auf OCI Defaults abbilden
+	s/wkb_geometry/ora_geometry/;
+	s/ogc_fid/ogr_fid/;
+
+	s/USING btree //;
+
+	s/SELECT alkis_drop\(\);/-- $1/;
+
+	# Indexnamen ersetzen
+	if( /CREATE( UNIQUE)? INDEX (\S+)/ ) {
+		s/INDEX \S+/INDEX ALKIS_KEY_$i/;
+		$i++;
+	}
+
+	# Constraintnamen ersetzen
+	if( /CONSTRAINT (\S+)/ ) {
+		s/CONSTRAINT \S+/CONSTRAINT ALKIS_KEY_$i/;
+		$i++;
+	}
+
+	# RÃ€umliche Indizes konvertieren
+	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;
+
+	# Tabelle ggf. vor dem Anlegen droppen und aus den Metadaten lÃ¶schen
+	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/;
+
+	# Anlegen der Geometriespalte anpassen und fÃŒr Metadateneintrag sorgen
+	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\)\)\);/;
+	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\)\)\);/;
+
+	# DELETE-SchlÃŒsselwort quoten
+	s/"delete"/"DELETE"/;
+	s/ALTER TABLE DELETE/ALTER TABLE "DELETE"/;
+	s/'"DELETE"'/'DELETE'/g;
+	s/COMMENT ON COLUMN delete/COMMENT ON COLUMN "DELETE"/;
+
+	# Oracle kennt keine Indexkommentare
+	s/COMMENT ON INDEX .*$/\n/;
+
+	# Ãberlange Bezeichner kÃŒrzen und in GroÃbuchstaben wandeln
+	s/([A-Z_]{30})[A-Z_]+/\U$1/gi;
+
+	# Insert Tabellennamen in uppercase
+	s/INSERT INTO (\S+)/INSERT INTO \U$1\E/;
+
+	next if /^COMMENT ON TABLE (geometry_columns|spatial_ref_sys)/;
+
+	next if /^\s*$/;
+
+	print O;
+}
+
+print O "purge recyclebin;\nQUIT;\n";
+close O;
+
+close I;
+
+# vim: set nowrap :
