christian@0: /*
christian@0:  * Postarc
christian@0:  *
christian@0:  * Author:
christian@0:  * Christian Lins <christian.lins@intevation.de>
christian@0:  *
christian@0:  * Copyright:
christian@0:  * Copyright (C) 2012 Intevation GmbH <http://www.intevation.de/>
christian@0:  *
christian@0:  * This program is free software: you can redistribute it and/or modify
christian@0:  * it under the terms of the GNU Lesser General Public License as published by
christian@0:  * the Free Software Foundation, either version 3 of the License, or
christian@0:  * (at your option) any later version.
christian@0:  *
christian@0:  * This program is distributed in the hope that it will be useful,
christian@0:  * but WITHOUT ANY WARRANTY; without even the implied warranty of
christian@0:  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
christian@0:  * GNU General Public License for more details.
christian@0:  *
christian@0:  * You should have received a copy of the GNU Lesser General Public License
christian@0:  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
christian@0: */
christian@0: 
christian@0: using System;
christian@0: using System.Collections.Generic;
christian@0: using System.Linq;
christian@0: using System.Text;
christian@0: using ESRI.ArcGIS.Geodatabase;
christian@0: using ESRI.ArcGIS.Geometry;
christian@0: using Npgsql;
christian@0: 
christian@0: namespace Postarc.Catalog
christian@0: {
christian@0:     public class FeatureClassExporter
christian@0:     {
christian@0:         protected IFeatureClass featureClass;
christian@0: 
christian@0:         public FeatureClassExporter(IFeatureClass featClass)
christian@0:         {
christian@0:             this.featureClass = featClass;
christian@0:         }
christian@0: 
christian@0:         protected string CreateTableDefinition(IFields fields, string tableName)
christian@0:         {
christian@0:             StringBuilder buf = new StringBuilder();
christian@0:             buf.Append("CREATE TABLE ");
christian@0:             buf.Append(tableName);
christian@0:             buf.Append(" ( ");
christian@0: 
christian@0:             bool hasOID = false;
christian@0:             for (int n = 0; n < fields.FieldCount; n++)
christian@0:             {
christian@0:                 IField field = fields.get_Field(n);
christian@0:                 string fieldType = GetSQLType(field.Type);
christian@0:                 if (fieldType != null)
christian@0:                 {
christian@0:                     if (field.Name.Equals(Postarc.Feature.FeatureCursor.ARCGIS_OID_COLUMN, StringComparison.InvariantCultureIgnoreCase))
christian@0:                     {
christian@0:                         hasOID = true;
christian@0:                         buf.Append(Postarc.Feature.FeatureCursor.POSTARC_OID_COLUMN);
christian@0:                     }
christian@0:                     else
christian@0:                     {
christian@0:                         buf.Append(field.Name);
christian@0:                     }
christian@0:                     buf.Append(" ");
christian@0:                     buf.Append(fieldType);
christian@0:                     buf.Append(",");
christian@0:                 }
christian@0: 
christian@0:                 if (!hasOID && n == fields.FieldCount - 1)
christian@0:                 {
christian@0:                     buf.Append(Postarc.Feature.FeatureCursor.POSTARC_OID_COLUMN);
christian@0:                     buf.Append(" BIGSERIAL NOT NULL,");
christian@0:                 }
christian@0:             }
christian@0: 
christian@0:             buf.Append("PRIMARY KEY(");
christian@0:             buf.Append(Postarc.Feature.FeatureCursor.POSTARC_OID_COLUMN);
christian@0:             buf.Append(")");
christian@0:             buf.Append(" )");
christian@0:             return buf.ToString();
christian@0:         }
christian@0: 
christian@0:         protected string CreateGeoColumnDef(string tableName)
christian@0:         {
christian@0:             StringBuilder buf = new StringBuilder();
christian@0: 
christian@0:             buf.Append("SELECT AddGeometryColumn('public', '");
christian@0:             buf.Append(tableName);
christian@0:             buf.Append("', '");
christian@0:             buf.Append(Postarc.Feature.FeatureCursor.POSTARC_SHAPE_COLUMN);
christian@0:             buf.Append("', -1, 'POINT', 2)");
christian@0: 
christian@0:             return buf.ToString();
christian@0:         }
christian@0: 
christian@0:         protected string GetSQLType(esriFieldType type)
christian@0:         {
christian@0:             switch (type)
christian@0:             {
christian@0:                 case esriFieldType.esriFieldTypeInteger:
christian@0:                     return "INT";
christian@0:         //        case esriFieldType.esriFieldTypeString:
christian@0:         //            return "TEXT";
christian@0:             }
christian@0:             return null;
christian@0:         }
christian@0: 
christian@0:         protected string CreateInsertStatement(string tableName, object[] row)
christian@0:         {
christian@0:             StringBuilder buf = new StringBuilder();
christian@0:             buf.Append("INSERT INTO ");
christian@0:             buf.Append(tableName);
christian@0:             buf.Append("(");
christian@0: 
christian@0:             List<string> fields = new List<string>();
christian@0:             for (int n = 0; n < featureClass.Fields.FieldCount; n++)
christian@0:             {
christian@0:                 IField field = featureClass.Fields.get_Field(n);
christian@0:                 string fieldType = GetSQLType(field.Type);
christian@0: 
christian@0:                 if (fieldType != null)
christian@0:                 {
christian@0:                     fields.Add(field.Name);
christian@0:                 }
christian@0:                 else if (field.Type == esriFieldType.esriFieldTypeGeometry)
christian@0:                 {
christian@0:                     fields.Add(Postarc.Feature.FeatureCursor.POSTARC_SHAPE_COLUMN);
christian@0:                 }
christian@0:             }
christian@0: 
christian@0:             for (int n = 0; n < fields.Count; n++)
christian@0:             {
christian@0:                 buf.Append(fields[n]);
christian@0:                 if (n < fields.Count - 1)
christian@0:                     buf.Append(",");
christian@0:             }
christian@0:             
christian@0:             buf.Append(") VALUES (");
christian@0: 
christian@0:             List<string> values = new List<string>();
christian@0:             for (int n = 0; n < row.Length; n++)
christian@0:             {
christian@0:                 esriFieldType fieldType = featureClass.Fields.get_Field(n).Type;
christian@0: 
christian@0:                 if (GetSQLType(fieldType) != null || fieldType == esriFieldType.esriFieldTypeGeometry)
christian@0:                 {
christian@0:                     values.Add("'" + row[n] + "'");
christian@0:                 }
christian@0:             }
christian@0: 
christian@0:             for (int n = 0; n < values.Count; n++)
christian@0:             {
christian@0:                 buf.Append(values[n]);
christian@0:                 if (n < values.Count - 1)
christian@0:                     buf.Append(",");
christian@0:             }
christian@0: 
christian@0:             buf.Append(")");
christian@0:             return buf.ToString();
christian@0:         }
christian@0: 
christian@0:         protected void CreateTable(PostGISConnection conn, string tableName)
christian@0:         {
christian@0:             NpgsqlConnection npgsqlConn = conn.Open();
christian@0:             string sql;
christian@0:             NpgsqlCommand cmd;
christian@0: 
christian@0:             sql = CreateTableDefinition(featureClass.Fields, tableName);    
christian@0:             cmd = new NpgsqlCommand(sql, npgsqlConn);
christian@0:             cmd.ExecuteNonQuery();
christian@0: 
christian@0:             sql = CreateGeoColumnDef(tableName);
christian@0:             cmd = new NpgsqlCommand(sql, npgsqlConn);
christian@0:             cmd.ExecuteNonQuery();
christian@0:         }
christian@0: 
christian@0:         protected object[] CreateRow(IFeature feature)
christian@0:         {
christian@0:             object[] row = new object[feature.Fields.FieldCount];
christian@0:             for (int n = 0; n < row.Length; n++)
christian@0:             {
christian@0:                 row[n] = feature.get_Value(n);
christian@0:                 if (row[n] is IGeometry)
christian@0:                 {
christian@0:                     Postarc.Feature.Point point = new Postarc.Feature.Point(row[n] as IPoint);
christian@0:                     row[n] = point.GetWKT();
christian@0:                 }
christian@0:                 else if (feature.Fields.get_Field(n).Type == esriFieldType.esriFieldTypeString)
christian@0:                 {
christian@0:                     row[n] = EscapeString(row[n] as string);
christian@0:                 }
christian@0:             }
christian@0:             return row;
christian@0:         }
christian@0: 
christian@0:         protected string EscapeString(string str)
christian@0:         {
christian@0:             str = str.Replace("'", "\'");
christian@0:             return str;
christian@0:         }
christian@0: 
christian@0:         protected int WriteRow(string tableName, NpgsqlConnection conn, object[] row)
christian@0:         {
christian@0:             string sql = CreateInsertStatement(tableName, row);
christian@0:             NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
christian@0:             return cmd.ExecuteNonQuery();
christian@0:         }
christian@0: 
christian@0:         public int ExportFeatureClass(IQueryFilter filter, PostGISConnection conn, string tableName)
christian@0:         {
christian@0:             // FIXME: Enclose in transaction
christian@0:             CreateTable(conn, tableName);
christian@0: 
christian@0:             NpgsqlConnection npgsqlConn = conn.Open();
christian@0:             int numExported = 0;
christian@0: 
christian@0:             IFeatureCursor cursor = this.featureClass.Search(filter, true);
christian@0:             IFeature ifeature;
christian@0:             while ((ifeature = cursor.NextFeature()) != null)
christian@0:             {
christian@0:                 object[] row = CreateRow(ifeature);
christian@0:                 WriteRow(tableName, npgsqlConn, row);
christian@0:                 numExported++;
christian@0:             }
christian@0: 
christian@0:             npgsqlConn.Close();
christian@0:             return numExported;
christian@0:         }
christian@0:     }
christian@0: }