diff options
Diffstat (limited to 'src/Postgresql.cs')
| -rw-r--r-- | src/Postgresql.cs | 597 |
1 files changed, 597 insertions, 0 deletions
diff --git a/src/Postgresql.cs b/src/Postgresql.cs new file mode 100644 index 0000000..48ab0b2 --- /dev/null +++ b/src/Postgresql.cs @@ -0,0 +1,597 @@ +/* + * FsGateway - navigate a database structure as directory tree + * Copyright (C) 2009-2010 Torello Querci <torello@torosoft.com> + * + * This program is free software; you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation; either version 2 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License along + * with this program; if not, write to the Free Software Foundation, Inc., + * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + */ + +using System; +using System.Data; +using System.Collections.Generic; +using Npgsql; +using Mono.Fuse; +using System.Text.RegularExpressions; + +namespace FsGateway +{ + + + public class Postgresql : IFsDb + { + private IDbConnection dbcon=null; + private string connectionString=null; + private bool _isConnected=false; + private int version_number_release=0; // e.s. 8.x.x + private int version_number_major=0; // e.s. x.3.0 + private int version_number_minor=0; // e.s. x.x.0 + + public Postgresql() + { + } + + public Postgresql(string host, string database, string user, string password, string port) + { + + string connectionString = "Server="+host+";" + + "Database="+database+";" + + "User ID="+user+";" + + "Password="+password+";"+ + "Port="+port+";"; + Connect(connectionString); + } + + public List<string> getTypeOfObjects() { + List<string> names=new List<string>(); + + names.Add ("/tables"); + names.Add ("/views"); + names.Add ("/indexes"); + names.Add ("/sequences"); + + return names; + } + + public void Dispose() { + Unconnect(); + _isConnected=false; + } + + public string storageType { + get { + return "postgresql"; + } + } + + public string Usage { + get { + return "Specify the connection parameter like this one: \"Server=localhost; Database=mydb; User ID=username;Password=password;Port=5432;"; + } + } + + public bool isConnect { + get { + return _isConnected; + } + } + + public bool Connect() { + return Connect(this.connectionString); + } + + public bool Connect(string connectionString) { + bool res=false; + + if (dbcon!=null && dbcon.State==System.Data.ConnectionState.Open) { + Unconnect(); + } + + try { + dbcon = new NpgsqlConnection(connectionString); + dbcon.Open(); + this.connectionString=connectionString; + res=true; + _isConnected=true; + + readVersionNumber(); + + } catch (Exception ex) { + System.Console.Out.WriteLine("Exception during database connection opening. Error message: "+ex.Message); + dbcon=null; + } + return res; + } + + private string readVersionNumber() { + string sql="select version()"; + string version=""; + + IDbCommand dbcmd = dbcon.CreateCommand(); + + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + if (!reader.Read()) { + reader.Close(); + return null; + } + + // Get the version number + version=reader.GetString(0); + + // Parse the version number string to get the single part + Regex fileMultipleRegex = null; + + try { + fileMultipleRegex = new Regex(@"(?<postgres>[^ ]+) ((?<numberVersion>[0-9]+)\.(?<numberMajor>[0-9]+)\.(?<numberMinor>[0-9]+))", RegexOptions.Compiled | RegexOptions.IgnoreCase); + } catch (Exception ex) { + System.Console.WriteLine("Exception during the Regex creation: "+ex.Message); + } + + // Check for namefile with number + int index=0; + if (fileMultipleRegex!=null) { + + Match match=null; + try { + match = fileMultipleRegex.Match(version); + } catch (Exception ex) { + System.Console.WriteLine("Exception during match routine. Message: "+ex.Message); + } + + try { + if (match != null && match.Success) { + version_number_release =System.Int32.Parse(match.Groups["numberVersion"].Value); + version_number_major =System.Int32.Parse(match.Groups["numberMajor"].Value); + version_number_minor =System.Int32.Parse(match.Groups["numberMinor"].Value); + } + } catch (Exception ex) { + Console.WriteLine("Exception: "+ex.Message); + } + } + + reader.Close(); + return version; + } + + public void Unconnect() { + bool res=false; + + if (dbcon!=null && dbcon.State==System.Data.ConnectionState.Open) { + dbcon.Close(); + dbcon=null; + res=false; + } + + _isConnected=true; + return; + } + + public SortedList<string,Table> getTables() { + + SortedList<string,Table> tableList=null; + string sql; + IDataReader reader=null; + + // Check for DB Connection + if (dbcon!=null) { + + tableList=new SortedList<string,Table>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + if (version_number_release == 8 && version_number_major >= 2) { + sql = "SELECT c.tableoid " + + " , c.oid " + + " , relname " + + " , relacl " + + " , relkind " + + " , relowner as rolname " + + " , relchecks " +// + " , reltriggers " + + " , relhasindex " + + " , relhasrules " + + " , relhasoids " + + " , d.refobjid as owning_tab " + + " , d.refobjsubid as owning_col " + + " , t.spcname as reltablespace " + + " , n.nspname as namespace " + + " , array_to_string(c.reloptions, ', ') as reloptions " + + "from pg_class c " + + "left join pg_depend d on (c.relkind = 'S' and d.classid = c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid = c.tableoid and d.deptype = 'a') " + + "left join pg_tablespace t on t.oid = c.reltablespace " + + "left join pg_namespace n on n.oid = c.relnamespace " + + "where relkind = 'r' " + // + " AND pg_catalog.pg_table_is_visible(c.oid) " + + " and n.nspname not in ('pg_catalog', 'information_schema') " + + "order by n.nspname, relname"; + } else if (version_number_release == 8) { + sql = "SELECT c.tableoid " + + " , c.oid " + + " , relname " + + " , relacl " + + " , relkind " + + " , relnamespace " + + " , relowner as rolname " + + " , relchecks " + + " , reltriggers " + + " , relhasindex " + + " , relhasrules " + + " , relhasoids " + + " , d.refobjid as owning_tab " + + " , d.refobjsubid as owning_col " + + " , (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace " + + " , n.nspname as namespace " + + " , NULL as reloptions " + + "from pg_class c " + + "left join pg_depend d " + + " on (c.relkind = 'S' and d.classid = c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid = c.tableoid and d.deptype = 'i') " + + "left join pg_namespace n on n.oid = c.relnamespace " + + "where relkind = 'r' " + + " and n.nspname not in ('pg_catalog', 'information_schema') " + + "order by c.oid " + ; + } else if (version_number_release == 7 && version_number_major >= 3) { + sql = "SELECT c.tableoid " + + " , c.oid " + + " , relname " + + " , relacl " + + " , relkind " + + " , relnamespace " + + " , relowner as rolname " + + " , relchecks " + + " , reltriggers " + + " , relhasindex " + + " , relhasrules " + + " , relhasoids " + + " , d.refobjid as owning_tab " + + " , d.refobjsubid as owning_col " + + " , NULL AS reltablespace " + + " , n.nspname as namespace " + + " , NULL as reloptions " + + "from pg_class c " + + "left join pg_depend d " + + " on (c.relkind = 'S' and d.classid = c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid = c.tableoid and d.deptype = 'i') " + + "left join pg_namespace n on n.oid = c.relnamespace " + + "where relkind = 'r' " + + " and n.nspname not in ('pg_catalog', 'information_schema') " + + "order by c.oid " + ; + } else if (version_number_release == 7 && version_number_major >= 2) { + sql = "SELECT c.tableoid " + + " , c.oid " + + " , relname " + + " , relacl " + + " , relkind " + + " , relnamespace " + + " , relowner as rolname " + + " , relchecks " + + " , reltriggers " + + " , relhasindex " + + " , relhasrules " + + " , relhasoids " + + " , NULL::oid as owning_tab " + + " , NULL::int4 as owning_col " + + " , NULL AS reltablespace " + + " , n.nspname as namespace " + + " , NULL as reloptions " + + "from pg_class c " + + "left join pg_namespace n on n.oid = c.relnamespace " + + "where relkind = 'r' " + + " and n.nspname not in ('pg_catalog', 'information_schema') " + + "order by c.oid " + ; + } else if (version_number_release == 7 && version_number_major >= 1) { + sql = "SELECT c.tableoid " + + " , c.oid " + + " , relname " + + " , relacl " + + " , relkind " + + " , 0::oid as relnamespace " + + " , relowner as rolname " + + " , relchecks " + + " , reltriggers " + + " , relhasindex " + + " , relhasrules " + + " , relhasoids " + + " , NULL::oid as owning_tab " + + " , NULL::int4 as owning_col " + + " , NULL AS reltablespace " + + " , n.nspname as namespace " + + " , NULL as reloptions " + + "from pg_class c " + + "left join pg_namespace n on n.oid = c.relnamespace " + + "where relkind = 'r' " + + " and n.nspname not in ('pg_catalog', 'information_schema') " + + "order by c.oid " + ; + } else { + sql = "SELECT (SELECT oid FROM pg_class WHERE relname = 'pg_class') AS tableoid " + + " , oid " + + " , relname " + + " , relacl " + + " , CASE WHEN relhasrules and relkind = 'r' and EXISTS(SELECT rulename FROM pg_rewrite r WHERE r.ev_class = c.oid AND r.ev_type = '1') " + + " THEN '%c'::\"char\" " + + " ELSE relkind END AS relkind " + + " , 0::oid as relnamespace " + + " , (relowner) as rolname " + + " , relchecks " + + " , reltriggers " + + " , relhasindex " + + " , relhasrules " + + " , 't'::bool as relhasoids " + + " , NULL::oid as owning_tab " + + " , NULL::int4 as owning_col " + + " , NULL as reltablespace " + + " , n.nspname as namespace " + + " , NULL as reloptions " + + "from pg_class c " + + "left join pg_namespace n on n.oid = c.relnamespace " + + "where relkind = 'r' " + + " and n.nspname not in ('pg_catalog', 'information_schema') " + + "order by oid" + ; + } + + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + Table table=new Table(reader.GetInt64(reader.GetOrdinal("oid")),reader.GetString(reader.GetOrdinal("namespace")),reader.GetString(reader.GetOrdinal("relname"))); + tableList.Add(table.ToString(), table); + } + } catch (Exception ex) { + Console.WriteLine("Exception reading of the tables list : "+ex.Message); + Console.WriteLine("Postgresql Version: "+version_number_release+"."+version_number_major+"."+version_number_minor); + Console.WriteLine("List tables: SQL=" + sql); + } + reader.Close(); + + // ReRead data for fetching detail + foreach (Table table in tableList.Values) { + + sql = "SELECT a.attnum " + + " , a.attrelid " + + " , a.attname " + + " , a.atttypmod " + + " , a.attstattarget " + + " , a.attstorage " + + " , t.typstorage " + + " , a.attnotnull " + + " , a.atthasdef " + + " , a.attisdropped " + + " , a.attislocal " + + " , pg_catalog.format_type(t.oid,a.atttypmod) as atttypname " + + "from pg_catalog.pg_attribute a " + + "left join pg_catalog.pg_type t on a.atttypid = t.oid " + + "where a.attnum > 0::pg_catalog.int2 " + + " and a.attrelid = "+table.Id+ " " + + "order by a.attnum "; + string listFilesStr=""; + try { + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + listFilesStr=""; + while (reader.Read()) { + if (!reader.IsDBNull(reader.GetOrdinal("atttypname"))) { + listFilesStr += "Row number: "+reader.GetValue(0).ToString()+"\n"; + listFilesStr += "Row name: "+reader.GetValue(reader.GetOrdinal("attname")).ToString(); + Field field=new Field(reader.GetString(reader.GetOrdinal("attname")),reader.GetString(reader.GetOrdinal("atttypname"))); + table.Fields.Add(field.Name,field); + } + } + + reader.Close(); + + table.Script = "CREATE TABLE "+table.ToString()+"\n" + + "(\t"; + string separator=""; + foreach (Field field in table.Fields.Values) { + table.Script += separator+field.Name+"\t"+field.Type; + separator=",\n\t"; + } + table.Script += "\n);\n"; + + } catch (Exception ex) { + Console.WriteLine("Exception reading the tables fields detail for table: " + table.ToString()+ " message : "+ex.Message); + Console.WriteLine("SQL used: "+sql); + Console.WriteLine("FIELDS: "+listFilesStr); + } + + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + } + + return tableList; + } + + public SortedList<string,View> getViews() { + + System.Collections.Generic.SortedList<string,View> viewList=null; + View view; + + // Check for DB Connection + if (dbcon!=null) { + + viewList=new SortedList<string,View>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + string sql = "SELECT * " + + "FROM pg_catalog.pg_views " + + "WHERE schemaname!='pg_catalog' " + + " AND schemaname!='information_schema' " + + "ORDER BY schemaname, viewname "; + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + view=new View(reader.GetString(reader.GetOrdinal("schemaname")), + reader.GetString(reader.GetOrdinal("viewname")), + reader.GetString(reader.GetOrdinal("definition"))+"\n"); + viewList.Add(view.ToString(),view); + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + } + + return viewList; + } + + public SortedList<string,Index> getIndexes() { + + System.Collections.Generic.SortedList<string,Index> indexList=null; + Index index; + + // Check for DB Connection + if (dbcon!=null) { + + indexList=new SortedList<string,Index>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + string sql = "SELECT * " + + "FROM pg_catalog.pg_indexes " + + "WHERE schemaname!='pg_catalog' " + + "ORDER BY schemaname, indexname "; + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + index=new Index(reader.GetString(reader.GetOrdinal("schemaname")), + reader.GetString(reader.GetOrdinal("tablename")), + reader.GetString(reader.GetOrdinal("indexname")), + reader.GetString(reader.GetOrdinal("indexdef"))+";\n"); + indexList.Add(index.ToString(),index); + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + } + + return indexList; + } + + public SortedList<string,Sequence> getSequences() { + + SortedList<string,Sequence> sequencesList=null; + + // Check for DB Connection + if (dbcon!=null) { + + sequencesList=new SortedList<string,Sequence>(); + + IDbCommand dbcmd = dbcon.CreateCommand(); + string sql = "SELECT n.nspname as SCHEMA " + + " , c.relname as NAME " + + " , u.usename as OWNER " + + "FROM pg_catalog.pg_class c " + + "LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner " + + "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " + + "WHERE c.relkind='S' " + + " AND n.nspname NOT IN ('pg_catalog', 'pg_toast') " + + " AND pg_catalog.pg_table_is_visible(c.oid) " + + "ORDER BY 1,2"; + dbcmd.CommandText = sql; + IDataReader reader = dbcmd.ExecuteReader(); + while(reader.Read()) { + Sequence sequence=new Sequence(reader.GetString(reader.GetOrdinal("SCHEMA")),reader.GetString(reader.GetOrdinal("NAME"))); + sequencesList.Add(sequence.ToString(),sequence); + + } + reader.Close(); + + // ReRead data for fetching detail + foreach (Sequence sequence in sequencesList.Values) { + + sql = "SELECT sequence_name " + + " , last_value " + + " , increment_by " + + " , max_value " + + " , min_value " + + " , cache_value " + + " , is_cycled " + + " , is_called from "+sequence.ToString(); + dbcmd.CommandText = sql; + reader = dbcmd.ExecuteReader(); + if (reader.Read()) { + sequence.LastValue=reader.GetInt64(reader.GetOrdinal("last_value")); + sequence.IncrementBy=reader.GetInt64(reader.GetOrdinal("increment_by")); + sequence.MaxValue=reader.GetInt64(reader.GetOrdinal("max_value")); + sequence.MinValue=reader.GetInt64(reader.GetOrdinal("min_value")); + sequence.CacheValue=reader.GetInt64(reader.GetOrdinal("cache_value")); + sequence.IsCycled=reader.GetBoolean(reader.GetOrdinal("is_cycled")); + sequence.IsCalled=reader.GetBoolean(reader.GetOrdinal("is_called")); + + sequence.Script="CREATE SEQUENCE "+sequence.ToString()+"\n" + + (sequence.IsCalled ? " START WITH "+sequence.LastValue + "\n" : "") + + " INCREMENT BY "+sequence.IncrementBy+"\n" + + (sequence.MaxValue!=0x7FFFFFFFFFFFFFFF ? " MAXVALUE "+sequence.MaxValue : " NO MAXVALUE") + "\n" + + (sequence.MinValue!=-0x7FFFFFFFFFFFFFFF ? " MINVALUE "+sequence.MinValue : " NO MINVALUE") + "\n" + + " CACHE "+sequence.CacheValue + (sequence.IsCycled ? "\n CYCLE" : "") + "\n" + + ";\n"; + } + reader.Close(); + } + + // clean up + reader.Close(); + reader = null; + dbcmd.Dispose(); + dbcmd = null; + } + + return sequencesList; + } + + /* + * Just for class testing only + */ + public static void Main(string[] args) + { + Postgresql pg=new Postgresql("localhost", "affissioni", "affissioni", "", "5432"); + + System.Console.Out.WriteLine("Tables:"); + SortedList<string,Table> tables=pg.getTables(); + foreach (Table table in tables.Values) { + System.Console.Out.WriteLine(table.ToString()); + } + + System.Console.Out.WriteLine("Views:"); + SortedList<string,View> views=pg.getViews(); + foreach (View view in views.Values) { + System.Console.Out.WriteLine(view.ToString()); + } + + System.Console.Out.WriteLine("Indexes:"); + SortedList<string,Index> indexes=pg.getIndexes(); + foreach (Index index in indexes.Values) { + System.Console.Out.WriteLine(index.ToString()); + } + + System.Console.Out.WriteLine("Sequences:"); + SortedList<string,Sequence> sequences=pg.getSequences(); + foreach (Sequence sequence in sequences.Values) { + System.Console.Out.WriteLine(sequence.ToString()); + } + + } + } +} |