예제 #1
0
        public override ITableDesc GetTableByName(string[] names, bool useCache)
        {
            if (useCache)
            {
                var t = Get(names);
                if (t != null)
                {
                    return(t);
                }
            }
            string tn;
            string sh;

            if (names.Length == 2)
            {
                sh = names[0];
                tn = names[1];
            }
            else
            if (names.Length == 1)
            {
                sh = DefaultSchema();
                tn = names[0];
            }
            else
            {
                throw new Exception("Incorrect table name");
            }
            using (var con = GetConnection())
            {
                con.Open();
                string    sql = @"SELECT EXISTS (
   SELECT 1 
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = @schema
   AND    c.relname = @name
   );";
                DbCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
                ParserDbUtils.AddParam(cmd, "@schema", sh);
                ParserDbUtils.AddParam(cmd, "@name", tn);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new Exception("Table " + string.Join(".", names) + " is not found");
                    }
                }
            }

            using (var con = GetConnection())
            {
                con.Open();
                string    sql = @"SELECT 
      attname            AS column_name
     , atttypid::regtype  AS data_type
       -- more attributes?
FROM   pg_attribute 
WHERE  attrelid = @schemaTable::regclass  -- table name, optionally schema-qualified
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum";
                DbCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
                string st = "";
                if (!string.IsNullOrEmpty(sh))
                {
                    st += "\"" + sh + "\"";
                }
                if (!string.IsNullOrEmpty(tn))
                {
                    if (st != "")
                    {
                        st += ".";
                    }
                    st += "\"" + tn + "\"";
                }

                ParserDbUtils.AddParam(cmd, "@schemaTable", st);
                TableDesc td = new TableDesc();

                td.PhysicalTableName = tn;
                td.LogicalTableName  = tn;
                td.PhysicalSchema    = sh;
                td.LogicalSchema     = sh;
                //td.DbDriver = Driver;
                td.TableColumns = new List <Column>();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Column ci = new Column(
                            reader.GetString(reader.GetOrdinal("column_name")),
                            ColumnSqlTypeToSimpleType(reader.GetString(reader.GetOrdinal("data_type")))
                            );
                        td.TableColumns.Add(ci);
                    }
                }
                Set(names, td);
                return(td);
            }
        }
예제 #2
0
        public override ITableDesc GetTableByName(string[] names, bool useCache)
        {
            if (useCache)
            {
                var t = Get(names);
                if (t != null)
                {
                    return(t);
                }
            }
            string tn;
            string sh;

            if (names.Length == 2)
            {
                sh = names[0];
                tn = names[1];
            }
            else
            if (names.Length == 1)
            {
                sh = DefaultSchema();
                tn = names[0];
            }
            else
            {
                throw new Exception("Incorrect table name");
            }
            using (var con = GetConnection())
            {
                con.Open();
                string    sql = "select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @schema and TABLE_NAME = @name";
                DbCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
                ParserDbUtils.AddParam(cmd, "@schema", sh);
                ParserDbUtils.AddParam(cmd, "@name", tn);
                using (var reader = cmd.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new Exception("Table " + string.Join(".", names) + " is not found");
                    }
                }
            }

            using (var con = GetConnection())
            {
                con.Open();
                string    sql = @"select
                    COLUMN_NAME,
                    DATA_TYPE
                  from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @schema and TABLE_NAME = @name";
                DbCommand cmd = con.CreateCommand();
                cmd.CommandText = sql;
                ParserDbUtils.AddParam(cmd, "@schema", sh);
                ParserDbUtils.AddParam(cmd, "@name", tn);
                TableDesc td = new TableDesc();

                td.PhysicalTableName = tn;
                td.LogicalTableName  = tn;
                td.PhysicalSchema    = sh;
                td.LogicalSchema     = sh;
                td.TableColumns      = new List <Column>();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Column ci = new Column(
                            reader.GetString(reader.GetOrdinal("COLUMN_NAME")),
                            ColumnSqlTypeToSimpleType(reader.GetString(reader.GetOrdinal("DATA_TYPE"))));
                        td.TableColumns.Add(ci);
                    }
                }
                Set(names, td);
                return(td);
            }
        }