Exemple #1
0
        override internal void LoadForTable()
        {
            try
            {
                //				NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);
                //
                //				NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("select * from public.\"MasterTypes\"", cn);
                //
                //				DataTable metaData = new DataTable();
                //
                //				adapter.Fill(metaData);
                //
                //				NpgsqlCommandBuilder builder = new NpgsqlCommandBuilder(adapter);
                //
                //				NpgsqlCommand cmd = builder.GetInsertCommand(metaData.Rows[0]);
                //
                //				cn.Close();
                //
                //
                //
                //				foreach(NpgsqlParameter p in cmd.Parameters)
                //				{
                //					Console.WriteLine("<Type From=\"" + p.SourceColumn + "\" To=\"" + p.DbType.ToString() + "\" />");
                //				}
                //
                //
                //				int i = 9;

                // real code below here

                string query = _query +
                               "AND nc.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' " + "ORDER BY attnum";

                /*=================================
                *
                *  string query =
                *       "SELECT CAST(current_database() AS character varying) AS table_catalog, CAST(nc.nspname AS character varying) AS table_schema, CAST(c.relname AS character varying) AS table_name, CAST(a.attname AS character varying) AS column_name, CAST(a.attnum AS integer) AS ordinal_position, CAST(CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END AS character varying) AS column_default, CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END AS character varying) AS is_nullable, CAST(CASE WHEN t.typtype = 'd' THEN CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null) ELSE 'USER-DEFINED' END ELSE CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null) ELSE 'USER-DEFINED' END END AS character varying) AS TYPE_NAMECOMPLETE, CAST( CASE WHEN t.typtype = 'd' THEN CASE WHEN t.typbasetype IN (1042, 1043) AND t.typtypmod <> -1 THEN t.typtypmod - 4 WHEN t.typbasetype IN (1560, 1562) AND t.typtypmod <> -1 THEN t.typtypmod ELSE null END ELSE CASE WHEN a.atttypid IN (1042, 1043) AND a.atttypmod <> -1 THEN a.atttypmod - 4 WHEN a.atttypid IN (1560, 1562) AND a.atttypmod <> -1 THEN a.atttypmod ELSE null END END AS integer) AS character_maximum_length, CAST( CASE WHEN t.typtype = 'd' THEN CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END ELSE CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END END AS integer) AS character_octet_length, CAST( CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END) WHEN 21 THEN 16 WHEN 23 THEN 32 WHEN 20 THEN 64 WHEN 1700 THEN " +
                *       "((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535 WHEN 700 THEN 24 WHEN 701 THEN 53 ELSE null END AS integer) AS numeric_precision, CAST( CASE WHEN t.typtype = 'd' THEN CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2 WHEN t.typbasetype IN (1700) THEN 10 ELSE null END ELSE CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2 WHEN a.atttypid IN (1700) THEN 10 ELSE null END END AS integer) AS numeric_precision_radix, CAST( CASE WHEN t.typtype = 'd' THEN CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535 ELSE null END ELSE CASE WHEN a.atttypid IN (21, 23, 20) THEN 0 WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535 ELSE null END END AS integer) AS numeric_scale, CAST( CASE WHEN t.typtype = 'd' THEN CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266) THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END) WHEN t.typbasetype IN (1186) THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END) ELSE null END ELSE CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266) THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END) WHEN a.atttypid IN (1186) THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END) ELSE null END END AS integer) AS datetime_precision, CAST(null AS character varying) AS interval_type, CAST(null AS character varying) AS interval_precision, CAST(null AS character varying) AS character_set_catalog, CAST(null AS character varying) AS character_set_schema, CAST(null AS character varying) AS character_set_name, CAST(null AS character varying) AS collation_catalog, CAST(null AS character varying) " +
                *       "AS collation_schema, CAST(null AS character varying) AS collation_name, CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END AS character varying) AS domain_catalog, CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END AS character varying) AS domain_schema, CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END AS character varying) AS domain_name, CAST(current_database() AS character varying) AS udt_catalog, CAST(coalesce(nbt.nspname, nt.nspname) AS character varying) AS udt_schema, CAST(coalesce(bt.typname, t.typname) AS character varying) AS TYPE_NAME, CAST(null AS character varying) AS scope_catalog, CAST(null AS character varying) AS scope_schema, CAST(null AS character varying) AS scope_name, CAST(null AS integer) AS maximum_cardinality, CAST(a.attnum AS character varying) AS dtd_identifier, CAST('NO' AS character varying) AS is_self_referencing FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a, pg_class c, pg_namespace nc, pg_user u, (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd' AND t.typbasetype = bt.oid) WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND u.usesysid = c.relowner AND nc.oid = c.relnamespace AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') AND " +
                *       "nc.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' AND	(u.usename = current_user OR has_table_privilege(c.oid, 'SELECT') OR has_table_privilege(c.oid, 'INSERT') OR has_table_privilege(c.oid, 'UPDATE') OR has_table_privilege(c.oid, 'REFERENCES') ) ORDER BY a.attnum;";
                *
                *  /*===============================*/

                //				string query =
                //					"SELECT  a.attname as COLUMN_NAME, a.attnum as ORDINAL_POSITION, format_type(a.atttypid, a.atttypmod) as TYPE_NAMECOMPLETE, " +
                //					"attnotnull as IS_NULLABLE, atthasdef as COLUMN_HASDEFAULT, atttypmod as CHARACTER_MAXIMUM_LENGTH, " +
                //					"d.adsrc as COLUMN_DEFAULT,  typname as TYPE_NAME, description AS DESCRIPTION, " +
                //					"FROM pg_catalog.pg_class c INNER JOIN " +
                //					"pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN " +
                //					"pg_catalog.pg_attribute a ON (a.attrelid = c.oid) LEFT JOIN " +
                //					"pg_catalog.pg_attrdef d ON (d.adrelid = c.oid and d.adnum = a.attnum) INNER JOIN " +
                //					"pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT OUTER JOIN " +
                //					"pg_description des ON des.objsubid = a.attnum " +
                //					"WHERE n.nspname = '" + this.Table.Schema + "' AND c.relname = '" + this.Table.Name + "' AND a.attisdropped = false AND a.attnum > 0 " +
                //					"ORDER BY attnum";

                NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);

                DataTable         metaData = new DataTable();
                NpgsqlDataAdapter adapter  = new NpgsqlDataAdapter(query, cn);

                adapter.Fill(metaData);
                cn.Close();

                PopulateArray(metaData);

                if (metaData.Columns.Contains("TYPE_NAME"))
                {
                    f_TypeName = metaData.Columns["TYPE_NAME"];
                }
                if (metaData.Columns.Contains("TYPE_NAMECOMPLETE"))
                {
                    f_TypeNameComplete = metaData.Columns["TYPE_NAMECOMPLETE"];
                }
            }
            catch {}
        }
Exemple #2
0
        private void _LoadAll(string query1, string query2)
        {
            NpgsqlConnection cn = null;

            try
            {
                cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Table.Database.Name);

                DataTable metaData1 = new DataTable();
                DataTable metaData2 = new DataTable();

                NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query1, cn);
                adapter.Fill(metaData1);

                adapter = new NpgsqlDataAdapter(query2, cn);
                adapter.Fill(metaData2);

                DataRowCollection rows = metaData2.Rows;
                int count = rows.Count;
                for (int i = 0; i < count; i++)
                {
                    metaData1.ImportRow(rows[i]);
                }

                PopulateArrayNoHookup(metaData1);

                if (metaData1.Rows.Count > 0)
                {
                    string   catalog = this.Table.Database.Name;
                    string   schema;
                    string   table;
                    string[] cols = null;
                    string   q;

                    string query =
                        "SELECT a.attname as COLUMN from pg_attribute a, pg_class c, pg_namespace n " +
                        "WHERE a.attrelid = c.oid AND c.relnamespace = n.oid " +
                        "AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind = 'r' ";

                    foreach (ForeignKey key in this)
                    {
                        //------------------------------------------------
                        // Primary
                        //------------------------------------------------
                        cols = ParseColumns(key._row["PK_COLS"] as string);

                        schema = key._row["PK_TABLE_SCHEMA"] as string;
                        table  = key._row["PK_TABLE_NAME"] as string;

                        q  = query;
                        q += "AND n.nspname = '" + schema + "' AND c.relname = '" + table + "' AND attnum IN(";

                        for (int i = 0; i < cols.GetLength(0); i++)
                        {
                            if (i > 0)
                            {
                                q += ',';
                            }
                            q += cols[i].ToString();
                        }

                        q += ") ORDER BY attnum;";

                        DataTable metaData = new DataTable();
                        adapter = new NpgsqlDataAdapter(q, cn);

                        adapter.Fill(metaData);

                        for (int i = 0; i < cols.GetLength(0); i++)
                        {
                            key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, true);
                        }

                        //------------------------------------------------
                        // Foreign
                        //------------------------------------------------
                        cols = ParseColumns(key._row["FK_COLS"] as string);

                        schema = key._row["FK_TABLE_SCHEMA"] as string;
                        table  = key._row["FK_TABLE_NAME"] as string;

                        q  = query;
                        q += "AND n.nspname = '" + schema + "' AND c.relname = '" + table + "' AND attnum IN(";

                        for (int i = 0; i < cols.GetLength(0); i++)
                        {
                            if (i > 0)
                            {
                                q += ',';
                            }
                            q += cols[i].ToString();
                        }

                        q += ") ORDER BY attnum;";

                        metaData = new DataTable();
                        adapter  = new NpgsqlDataAdapter(q, cn);

                        adapter.Fill(metaData);

                        for (int i = 0; i < cols.GetLength(0); i++)
                        {
                            key.AddForeignColumn(catalog, "", table, metaData.Rows[i]["COLUMN"] as string, false);
                        }
                    }
                }
            }
            catch {}

            cn.Close();
        }