Example #1
0
        public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
        {
            ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();

            IPooledDbConnection conn    = connectionPool.Request();
            IDbCommand          command = conn.CreateCommand("select name, xtype from sysobjects where xtype in ('F','PK','CK')");     //TODO: unique

            try {
                using (command) {
                    using (IDataReader r = command.ExecuteReader()) {
                        while (r.Read())
                        {
                            ConstraintSchema constraint = null;
                            switch (r.GetString(1))
                            {
                            case "F":                                     //foreign key
                                constraint = new ForeignKeyConstraintSchema(this);
                                break;

                            case "PK":                                     //primary key
                                constraint = new PrimaryKeyConstraintSchema(this);
                                break;

                            case "CK":                                     //check constraint
                                constraint = new CheckConstraintSchema(this);
                                break;

                            default:
                                break;
                            }

                            if (constraint != null)
                            {
                                constraint.Name = r.GetString(0);
                                constraints.Add(constraint);
                            }
                        }
                        r.Close();
                    }
                }
            } catch (Exception e) {
                QueryService.RaiseException(e);
            }
            conn.Release();

            return(constraints);
        }
        public override ConstraintSchemaCollection GetColumnConstraints(TableSchema table, ColumnSchema column)
        {
            ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();

            IPooledDbConnection conn    = connectionPool.Request();
            IDbCommand          command = conn.CreateCommand(String.Format("DESCRIBE {0}", table.Name));

            try {
                using (command) {
                    using (IDataReader r = command.ExecuteReader()) {
                        while (r.Read())
                        {
                            if (r.IsDBNull(3) || String.Compare(r.GetString(0), column.Name, true) != 0)
                            {
                                continue;
                            }

                            string key = r.GetString(3).ToUpper();

                            ConstraintSchema constraint = null;
                            if (key.Contains("PRI"))
                            {
                                constraint = GetNewPrimaryKeyConstraintSchema("pk_" + column.Name);
                            }
                            else if (key.Contains("UNI"))
                            {
                                constraint = GetNewUniqueConstraintSchema("uni_" + column.Name);
                            }
                            else
                            {
                                continue;
                            }
                            constraint.IsColumnConstraint = true;
                            constraint.OwnerName          = r.GetString(0);

                            constraints.Add(constraint);
                        }
                        r.Close();
                    };
                }
            } catch (Exception e) {
                QueryService.RaiseException(e);
            }
            conn.Release();

            return(constraints);
        }
        public virtual ConstraintSchemaCollection GetTableConstraints(TableSchema table)
        {
            ConstraintSchemaCollection collection = new ConstraintSchemaCollection();

            IPooledDbConnection conn = connectionPool.Request();

            try {
                //restrictions: database, schema, table, name
                DataTable dt = conn.GetSchema(foreignKeysCollectionString, null, connectionPool.ConnectionContext.ConnectionSettings.Database);
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow row = dt.Rows[r];
                    collection.Add(GetTableConstraint(row, table));
                }
            } catch (Exception e) {
                QueryService.RaiseException(e);
            }
            conn.Release();

            return(collection);
        }
        public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
        {
            ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();

            IPooledDbConnection conn    = connectionPool.Request();
            IDbCommand          command = conn.CreateCommand("SHOW TABLE STATUS FROM `" + table.SchemaName + "`;");

            try {
                using (command) {
                    using (IDataReader r = command.ExecuteReader()) {
                        while (r.Read())
                        {
                            string[] chunks = ((string)r["Comment"]).Split(';');

                            //the values we are looking for are in the format (`table`) REFER `database\table2` (`table2`)
                            foreach (string chunk in chunks)
                            {
                                if (constraintRegex.IsMatch(chunk))
                                {
                                    MatchCollection matches = constraintRegex.Matches(chunk);

                                    ForeignKeyConstraintSchema constraint = new ForeignKeyConstraintSchema(this);
                                    constraint.ReferenceTableName = matches[1].Groups[1].ToString();
                                    constraint.Name = matches[0].Groups[1].ToString();

                                    constraints.Add(constraint);
                                }
                            }
                        }
                        r.Close();
                    }
                }
            } catch (Exception e) {
                QueryService.RaiseException(e);
            }
            conn.Release();

            return(constraints);
        }
Example #5
0
        //http://www.sqlite.org/pragma.html
        public virtual ConstraintSchemaCollection GetConstraints(TableSchema table, ColumnSchema column)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table");
            }
            string columnName = column == null ? null : column.Name;

            ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();

            IPooledDbConnection conn = connectionPool.Request();

            //fk and unique
            IDbCommand command = conn.CreateCommand("SELECT name, tbl_name FROM sqlite_master WHERE sql IS NULL AND type = 'index'");

            try {
                using (command) {
                    using (IDataReader r = command.ExecuteReader()) {
                        while (r.Read())
                        {
                            ConstraintSchema constraint = null;

                            if (r.IsDBNull(1) || r.GetString(1) == null)
                            {
                                constraint = new UniqueConstraintSchema(this);
                            }
                            else
                            {
                                ForeignKeyConstraintSchema fkc = new ForeignKeyConstraintSchema(this);
                                fkc.ReferenceTableName = r.GetString(1);

                                constraint = fkc;
                            }
                            constraint.Name = r.GetString(0);

                            constraints.Add(constraint);
                        }
                        r.Close();
                    }
                }

                //pk, column
                if (columnName != null)
                {
                    command = conn.CreateCommand(
                        "PRAGMA table_info('" + table.Name + "')"
                        );
                    using (command) {
                        using (IDataReader r = command.ExecuteReader()) {
                            while (r.Read())
                            {
                                if (r.GetInt32(5) == 1 && r.GetString(1) == columnName)
                                {
                                    PrimaryKeyConstraintSchema constraint = new PrimaryKeyConstraintSchema(this);

                                    ColumnSchema priColumn = new ColumnSchema(this, table);
                                    priColumn.Name = r.GetString(1);

                                    constraint.Columns.Add(priColumn);
                                    constraint.IsColumnConstraint = true;
                                    constraint.Name = "pk_" + table.Name + "_" + priColumn.Name;

                                    constraints.Add(constraint);
                                }
                            }
                            r.Close();
                        }
                    }
                }
            } catch (Exception e) {
                QueryService.RaiseException(e);
            }

            conn.Release();

            return(constraints);
        }
        public override ConstraintSchemaCollection GetTableConstraints(TableSchema table)
        {
            ConstraintSchemaCollection constraints = new ConstraintSchemaCollection();

            IPooledDbConnection conn    = connectionPool.Request();
            IDbCommand          command = conn.CreateCommand(String.Format(
                                                                 "SELECT "
                                                                 + "pc.conname, "
                                                                 + "pg_catalog.pg_get_constraintdef(pc.oid, true) AS consrc, "
                                                                 + "pc.contype, "
                                                                 + "CASE WHEN pc.contype='u' OR pc.contype='p' THEN ( "
                                                                 + "	SELECT "
                                                                 + "		indisclustered "
                                                                 + "	FROM "
                                                                 + "		pg_catalog.pg_depend pd, "
                                                                 + "		pg_catalog.pg_class pl, "
                                                                 + "		pg_catalog.pg_index pi "
                                                                 + "	WHERE "
                                                                 + "		pd.refclassid=pc.tableoid "
                                                                 + "		AND pd.refobjid=pc.oid "
                                                                 + "		AND pd.objid=pl.oid "
                                                                 + "		AND pl.oid=pi.indexrelid "
                                                                 + ") ELSE "
                                                                 + "	NULL "
                                                                 + "END AS indisclustered "
                                                                 + "FROM "
                                                                 + "pg_catalog.pg_constraint pc "
                                                                 + "WHERE "
                                                                 + "pc.conrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{0}' "
                                                                 + "	AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace "
                                                                 + "	WHERE nspname='{1}')) "
                                                                 + "ORDER BY "
                                                                 + "1;", table.Name, table.SchemaName
                                                                 ));

            try {
                using (command) {
                    using (IDataReader r = command.ExecuteReader()) {
                        while (r.Read())
                        {
                            ConstraintSchema constraint = null;

                            //TODO: Add support for Check constraints.
                            switch (r.GetString(2))
                            {
                            case "f":
                                string match = @".*REFERENCES (.+)\(.*\).*";
                                constraint = new ForeignKeyConstraintSchema(this);
                                if (Regex.IsMatch(r.GetString(1), match))
                                {
                                    (constraint as ForeignKeyConstraintSchema).ReferenceTableName
                                        = Regex.Match(r.GetString(1), match).Groups[0].Captures[0].Value;
                                }
                                break;

                            case "u":
                                constraint = new UniqueConstraintSchema(this);
                                break;

                            case "p":
                            default:
                                constraint = new PrimaryKeyConstraintSchema(this);
                                break;
                            }

                            constraint.Name       = r.GetString(0);
                            constraint.Definition = r.GetString(1);

                            int parenOpen = constraint.Definition.IndexOf('(');
                            if (parenOpen > 0)
                            {
                                int    parenClose = constraint.Definition.IndexOf(')');
                                string colstr     = constraint.Definition.Substring(parenOpen + 1, parenClose - parenOpen - 1);
                                foreach (string col in colstr.Split(','))
                                {
                                    ColumnSchema column = new ColumnSchema(this, table);
                                    column.Name = col.Trim();
                                    constraint.Columns.Add(column);
                                }
                            }

                            constraints.Add(constraint);
                        }
                        r.Close();
                    }
                }
            } catch (Exception e) {
                QueryService.RaiseException(e);
            }
            conn.Release();

            return(constraints);
        }