protected virtual string GetConstraintString(ConstraintSchema constraint, bool isTableConstraint)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("CONSTRAINT ");
            sb.Append(constraint.Name);
            sb.Append(' ');

            switch (constraint.ConstraintType)
            {
            case ConstraintType.PrimaryKey:
                sb.Append("PRIMARY KEY ");
                if (isTableConstraint)
                {
                    sb.Append(GetColumnsString(constraint.Columns, true));
                }
                break;

            case ConstraintType.Unique:
                sb.Append("UNIQUE ");
                if (isTableConstraint)
                {
                    sb.Append(GetColumnsString(constraint.Columns, true));
                }
                break;

            case ConstraintType.ForeignKey:
                sb.Append("FOREIGN KEY ");
                sb.Append(GetColumnsString(constraint.Columns, true));
                sb.Append(" REFERENCES ");

                ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema;
                sb.Append(fk.ReferenceTable);
                sb.Append(' ');
                if (fk.ReferenceColumns != null)
                {
                    sb.Append(GetColumnsString(fk.ReferenceColumns, true));
                }
                break;

            case ConstraintType.Check:
                sb.Append("CHECK (");
                sb.Append((constraint as CheckConstraintSchema).Source);
                sb.Append(")");
                break;

            default:
                throw new NotImplementedException();
            }

            return(sb.ToString());
        }
Beispiel #2
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);
        }
Beispiel #4
0
        public override ICollection <ConstraintSchema> GetTableConstraints(TableSchema table)
        {
            CheckConnectionState();
            List <ConstraintSchema> constraints = new List <ConstraintSchema> ();

            IDbCommand command = connectionProvider.CreateCommand(
                "SELECT k.owner, k.table_name, k.constraint_name, " +
                "       k.constraint_type, k.status, k.validated " +
                "FROM all_constraints k " +
                "WHERE k.owner = '" + table.OwnerName + "' " +
                "AND k.table_name = '" + table.Name + "' " +
                "and k.constraint_type = 'P'"
                );

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

                        switch (r.GetString(4))
                        {
                        case "P":
                        default:
                            constraint = new PrimaryKeyConstraintSchema(this);
                            break;
                        }

                        constraint.Name       = r.GetString(3);
                        constraint.Definition = "";

                        constraints.Add(constraint);
                    }
                    r.Close();
                }
                connectionProvider.Close(command.Connection);
            }

            return(constraints);
        }
Beispiel #5
0
        protected virtual string GetConstraintString(ConstraintSchema constraint)
        {
            //PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT]
            //UNIQUE [ conflict-clause ]
            //CHECK ( expr )
            //COLLATE collation-name

            StringBuilder sb = new StringBuilder();

            sb.Append("CONSTRAINT ");
            sb.Append(constraint.Name);
            sb.Append(' ');

            switch (constraint.ConstraintType)
            {
            case ConstraintType.PrimaryKey:
                //PrimaryKeyConstraintSchema pk = constraint as PrimaryKeyConstraintSchema;
                sb.Append("PRIMARY KEY");                  //TODO: auto inc + sort
                break;

            case ConstraintType.Unique:
                //UniqueConstraintSchema u = constraint as UniqueConstraintSchema;
                sb.Append("UNIQUE");
                break;

            case ConstraintType.Check:
                CheckConstraintSchema chk = constraint as CheckConstraintSchema;
                sb.Append("CHECK (");
                sb.Append(chk.Source);
                sb.Append(")");
                break;

            default:
                throw new NotImplementedException();
            }

            return(sb.ToString());
        }
Beispiel #6
0
 protected ConstraintSchema(ConstraintSchema constraint)
     : base(constraint)
 {
     isColumnConstraint = constraint.isColumnConstraint;
     constraintType     = constraint.constraintType;
 }
Beispiel #7
0
		protected ConstraintSchema (ConstraintSchema constraint)
			: base (constraint)
		{
			isColumnConstraint = constraint.isColumnConstraint;
			constraintType = constraint.constraintType;
		}
Beispiel #8
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);
        }
		protected virtual string GetConstraintString (ConstraintSchema constraint)
		{
			//PRIMARY KEY [sort-order] [ conflict-clause ] [AUTOINCREMENT]
			//UNIQUE [ conflict-clause ]
			//CHECK ( expr )
			//COLLATE collation-name
			
			StringBuilder sb = new StringBuilder ();
			sb.Append ("CONSTRAINT ");
			sb.Append (constraint.Name);
			sb.Append (' ');
			
			switch (constraint.ConstraintType) {
			case ConstraintType.PrimaryKey:
				//PrimaryKeyConstraintSchema pk = constraint as PrimaryKeyConstraintSchema;
				sb.Append ("PRIMARY KEY"); //TODO: auto inc + sort
				break;
			case ConstraintType.Unique:
				//UniqueConstraintSchema u = constraint as UniqueConstraintSchema;
				sb.Append ("UNIQUE");
				break;
			case ConstraintType.Check:
				CheckConstraintSchema chk = constraint as CheckConstraintSchema;
				sb.Append ("CHECK (");
				sb.Append (chk.Source);
				sb.Append (")");
				break;
			default:
				throw new NotImplementedException ();
			}
			
			return sb.ToString ();
		}
					
		protected virtual string GetConstraintString (ConstraintSchema constraint, bool isTableConstraint)
		{
			StringBuilder sb = new StringBuilder ();
			sb.Append ("CONSTRAINT ");
			sb.Append (constraint.Name);
			sb.Append (' ');
			
			switch (constraint.ConstraintType) {
			case ConstraintType.PrimaryKey:
				sb.Append ("PRIMARY KEY ");
				if (isTableConstraint)
					sb.Append (GetColumnsString (constraint.Columns, true));
				break;
			case ConstraintType.Unique:
				sb.Append ("UNIQUE ");
				if (isTableConstraint)
					sb.Append (GetColumnsString (constraint.Columns, true));
				break;
			case ConstraintType.ForeignKey:
				sb.Append ("FOREIGN KEY ");
				sb.Append (GetColumnsString (constraint.Columns, true));
				sb.Append (" REFERENCES ");
				
				ForeignKeyConstraintSchema fk = constraint as ForeignKeyConstraintSchema;
				sb.Append (fk.ReferenceTable);
				sb.Append (' ');
				if (fk.ReferenceColumns != null)
					sb.Append (GetColumnsString (fk.ReferenceColumns, true));
				break;
			case ConstraintType.Check:
				sb.Append ("CHECK (");
				sb.Append ((constraint as CheckConstraintSchema).Source);
				sb.Append (")");
				break;
			default:
				throw new NotImplementedException ();
			}
			
			return sb.ToString ();