public void CanFormatObjectNameWithoutSchema()
        {
            var table = new SchemaQualifiedObjectName {Name = "Moo"};
            string sql = string.Format(formatter, "select * from {0:NAME}", table);

            Assert.AreEqual(sql, "select * from <Moo>");
        }
        public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
        {
            string sql = FormatSql(
                "select count(*) from rdb$relation_constraints " +
                "where rdb$relation_name = '{0}' and rdb$constraint_name = '{1}'", table.Name, name);

            int cnt = Convert.ToInt32(ExecuteScalar(sql));
            return cnt > 0;
        }
        public override void ChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
        {
            if (newDefaultValue != null)
            {
                base.ChangeDefaultValue(table, column, null);
            }

            base.ChangeDefaultValue(table, column, newDefaultValue);
        }
        public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
        {
            string sql = FormatSql(
                "SELECT {0:NAME} FROM {1:NAME} " +
                "WHERE {0:NAME} = '{2}' AND {3:NAME} = '{4}'",
                "CONSTRAINT_NAME", "TABLE_CONSTRAINTS".WithSchema("INFORMATION_SCHEMA"),
                name, "TABLE_NAME", table.Name);

            using (IDataReader reader = ExecuteReader(sql))
            {
                return reader.Read();
            }
        }
        public override void AddForeignKey(string name, SchemaQualifiedObjectName primaryTable, string[] primaryColumns, SchemaQualifiedObjectName refTable, string[] refColumns, ForeignKeyConstraint onDeleteConstraint = ForeignKeyConstraint.NoAction, ForeignKeyConstraint onUpdateConstraint = ForeignKeyConstraint.NoAction)
        {
            if (onUpdateConstraint != ForeignKeyConstraint.NoAction)
            {
                throw new NotSupportedException("Oracle не поддерживает действий при обновлении внешнего ключа");
            }

            if (onDeleteConstraint == ForeignKeyConstraint.SetDefault)
            {
                throw new NotSupportedException("Oracle не поддерживает SET DEFAULT при удалении записи, на которую ссылается внешний ключ");
            }

            base.AddForeignKey(name, primaryTable, primaryColumns, refTable, refColumns, onDeleteConstraint, onUpdateConstraint);
        }
        public override void ChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
        {
            string defaultConstraintName = GetDefaultConstraintName(table, column);

            if (!string.IsNullOrWhiteSpace(defaultConstraintName))
            {
                RemoveConstraint(table, defaultConstraintName);
            }

            if (newDefaultValue != null)
            {
                string sql = GetSqlChangeDefaultValue(table, column, newDefaultValue);
                ExecuteNonQuery(sql);
            }
        }
        public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
        {
            string nspname = table.SchemaIsEmpty ? "current_schema()" : string.Format("'{0}'", table.Schema);
            string tablename = NeedQuotesForNames ? table.Name : table.Name.ToLower();
            string colname = NeedQuotesForNames ? column : column.ToLower();

            string sql = FormatSql(
                "SELECT {0:NAME} FROM {1:NAME}.{2:NAME} WHERE {3:NAME} = {4} AND {5:NAME} = '{6}' AND {7:NAME} = '{8}'",
                "column_name", "information_schema", "columns", "table_schema",
                nspname, "table_name", tablename, "column_name", colname);

            using (IDataReader reader = ExecuteReader(sql))
            {
                return reader.Read();
            }
        }
        public virtual string GetDefaultConstraintName(SchemaQualifiedObjectName table, string column)
        {
            var sqlBuilder = new StringBuilder();

            sqlBuilder.Append(FormatSql("SELECT {0:NAME}.{1:NAME} AS {2:NAME} ", "dobj", "name", "CONSTRAINT_NAME"));
            sqlBuilder.Append(FormatSql("FROM {0:NAME} {1:NAME} ", "columns".WithSchema("sys"), "col"));
            sqlBuilder.Append(FormatSql("INNER JOIN {0:NAME} {1:NAME} ", "objects".WithSchema("sys"), "dobj"));
            sqlBuilder.Append(FormatSql("ON {0:NAME}.{1:NAME} = {2:NAME}.{3:NAME} AND {0:NAME}.{4:NAME} = 'D' ",
                "dobj", "object_id", "col", "default_object_id", "type"));
            sqlBuilder.Append(FormatSql("WHERE {0:NAME}.{1:NAME} = object_id(N'{2}') AND {0:NAME}.{3:NAME} = '{4}'",
                "col", "object_id", table, "name", column));

            using (var reader = ExecuteReader(sqlBuilder.ToString()))
            {
                if (reader.Read())
                {
                    return reader.GetString(0);
                }

                return null;
            }
        }
        public override bool ConstraintExists(SchemaQualifiedObjectName table, string name)
        {
            string constraintsTableName = table.SchemaIsEmpty ? "USER_CONSTRAINTS" : "ALL_CONSTRAINTS";

            string sql = FormatSql(
                    "SELECT COUNT(*) FROM {0:NAME} WHERE {1:NAME} = '{2}' AND {3:NAME} = '{4}'",
                        constraintsTableName, "CONSTRAINT_NAME", name, "TABLE_NAME", table.Name);

            if (!table.SchemaIsEmpty)
            {
                sql += FormatSql(" and {0:NAME} = '{1}'", "OWNER", table.Schema);
            }

            object scalar = ExecuteScalar(sql);
            return Convert.ToInt32(scalar) > 0;
        }
 protected override string GetSqlRemoveTable(SchemaQualifiedObjectName table)
 {
     return FormatSql("DROP TABLE {0:NAME} CASCADE", table);
 }
        protected override string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
        {
            // если изменение типа колонки и признака NOT NULL происходит одним запросом,
            // то изменяем параметр sqlChangeColumnType и возвращаем NULL
            // иначе возвращаем запрос, меняющий признак NOT NULL

            string sqlNotNull = notNull ? "SET NOT NULL" : "DROP NOT NULL";

            return FormatSql("ALTER TABLE {0:NAME} ALTER COLUMN {1:NAME} {2}", table, column, sqlNotNull);
        }
        protected override string GetSqlChangeColumnType(SchemaQualifiedObjectName table, string column, ColumnType columnType)
        {
            string columnTypeSql = typeMap.Get(columnType);

            return FormatSql("ALTER TABLE {0:NAME} MODIFY {1:NAME} {2}", table, column, columnTypeSql);
        }
 protected override string GetSqlAddColumn(SchemaQualifiedObjectName table, string columnSql)
 {
     return FormatSql("ALTER TABLE {0:NAME} ADD ({1})", table, columnSql);
 }
        public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
        {
            string sql = FormatSql("SHOW COLUMNS FROM {0:NAME} WHERE Field='{1}'", table, column);

            using (IDataReader reader = ExecuteReader(sql))
            {
                return reader.Read();
            }
        }
 public override void AddCheckConstraint(string name, SchemaQualifiedObjectName table, string checkSql)
 {
     throw new NotSupportedException("SqlServerCe doesn't support check constraints");
 }
 protected override string FindConstraints(SchemaQualifiedObjectName table, string column)
 {
     return
         FormatSql("SELECT {0:NAME} FROM {1:NAME} WHERE {2:NAME}='{3}' AND {4:NAME} = '{5}'",
             "CONSTRAINT_NAME", "KEY_COLUMN_USAGE".WithSchema("INFORMATION_SCHEMA"),
             "TABLE_NAME", table.Name, "COLUMN_NAME", column);
 }
        public override bool TableExists(SchemaQualifiedObjectName table)
        {
            string sql = FormatSql(
                "SELECT * FROM {0:NAME} WHERE {1:NAME}='{2}'",
                 "TABLES".WithSchema("INFORMATION_SCHEMA"), "TABLE_NAME", table.Name);

            using (IDataReader reader = ExecuteReader(sql))
            {
                return reader.Read();
            }
        }
 public override void RenameColumn(SchemaQualifiedObjectName tableName, string oldColumnName, string newColumnName)
 {
     throw new NotSupportedException("SqlServerCe doesn't support column renaming");
 }
        public override bool TableExists(SchemaQualifiedObjectName table)
        {
            string nspname = table.SchemaIsEmpty ? "SCHEMA_NAME()" : string.Format("'{0}'", table.Schema);

            string sql = FormatSql(
                "SELECT * FROM {0:NAME} WHERE {1:NAME}='{2}' AND {3:NAME} = {4}",
                "TABLES".WithSchema("INFORMATION_SCHEMA"), "TABLE_NAME", table.Name, "TABLE_SCHEMA", nspname);

            using (IDataReader reader = ExecuteReader(sql))
            {
                return reader.Read();
            }
        }
        public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
        {
            string indexesTableName = tableName.SchemaIsEmpty ? "USER_INDEXES" : "ALL_INDEXES";

            string sql = FormatSql(
                "select count(*) from {0:NAME} where {1:NAME} = '{2}' and {3:NAME} = '{4}'",
                indexesTableName, "TABLE_NAME", tableName.Name, "INDEX_NAME", indexName);

            if (!tableName.SchemaIsEmpty)
            {
                sql += FormatSql(" and {0:NAME} = '{1}' and {2:NAME} = '{1}'", "TABLE_OWNER", tableName.Schema, "OWNER");
            }

            int count = Convert.ToInt32(ExecuteScalar(sql));
            return count > 0;
        }
        public override bool TableExists(SchemaQualifiedObjectName table)
        {
            string schemaName = table.SchemaIsEmpty ? "user" : string.Format("'{0}'", table.Schema);

            string sql = FormatSql("SELECT COUNT(*) from {0:NAME} where {1:NAME} = '{2}' and {3:NAME} = {4}",
                "ALL_TABLES", "TABLE_NAME", table.Name, "OWNER", schemaName);

            object count = ExecuteScalar(sql);
            return Convert.ToInt32(count) > 0;
        }
        protected override string GetSqlChangeNotNullConstraint(SchemaQualifiedObjectName table, string column, bool notNull, ref string sqlChangeColumnType)
        {
            string colsTable = table.SchemaIsEmpty ? "USER_TAB_COLUMNS" : "ALL_TAB_COLUMNS";

            string sqlCheckNotNull = FormatSql(
                "select {0:NAME} from {1:NAME} where {2:NAME} = '{3}' and {4:NAME} = '{5}'",
                  "NULLABLE", colsTable, "TABLE_NAME", table.Name, "COLUMN_NAME", column);

            if (!table.SchemaIsEmpty)
            {
                sqlCheckNotNull += FormatSql(" and {0:NAME} = '{1}'", "OWNER", table.Schema);
            }

            using (var reader = ExecuteReader(sqlCheckNotNull))
            {
                if (reader.Read())
                {
                    bool columnAlreadyNotNull = reader[0].ToString().Equals("n", StringComparison.CurrentCultureIgnoreCase);
                    if (notNull == columnAlreadyNotNull)
                    {
                        return null;
                    }
                }
            }

            return base.GetSqlChangeNotNullConstraint(table, column, notNull, ref sqlChangeColumnType);
        }
        protected override string GetSqlAddIndex(string name, bool unique, SchemaQualifiedObjectName table, params string[] columns)
        {
            Require.That(columns.Length > 0, "Not specified columns of the table to create an index");

            string uniqueString = unique ? "UNIQUE" : string.Empty;
            string sql = FormatSql("CREATE {0} INDEX {1:NAME} ON {2:NAME} ({3:COLS})",
                uniqueString, name.WithSchema(table.Schema), table, columns);

            return sql;
        }
        public override void AddForeignKey(string name, SchemaQualifiedObjectName primaryTable, string[] primaryColumns, SchemaQualifiedObjectName refTable, string[] refColumns, ForeignKeyConstraint onDeleteConstraint = ForeignKeyConstraint.NoAction, ForeignKeyConstraint onUpdateConstraint = ForeignKeyConstraint.NoAction)
        {
            if (onDeleteConstraint == ForeignKeyConstraint.SetDefault ||
                onUpdateConstraint == ForeignKeyConstraint.SetDefault)
            {
                throw new NotSupportedException("MySQL не поддерживает SET DEFAULT для внешних ключей");
            }

            base.AddForeignKey(name, primaryTable, primaryColumns, refTable, refColumns, onDeleteConstraint, onUpdateConstraint);
        }
 protected override string GetSqlChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
 {
     return FormatSql("ALTER TABLE {0:NAME} MODIFY {1:NAME} {2}", table, column, GetSqlDefaultValue(newDefaultValue));
 }
 protected override string GetSqlChangeDefaultValue(SchemaQualifiedObjectName table, string column, object newDefaultValue)
 {
     string dfConstraintName = string.Format("DF_{0}", Guid.NewGuid().ToString("N"));
     string sqlDefaultValue = GetSqlDefaultValue(newDefaultValue);
     return FormatSql("ALTER TABLE {0:NAME} ADD CONSTRAINT {1:NAME} {2} FOR {3:NAME}", table, dfConstraintName, sqlDefaultValue, column);
 }
 protected override string GetSqlRemoveIndex(string indexName, SchemaQualifiedObjectName tableName)
 {
     return FormatSql("DROP INDEX {0:NAME}", indexName.WithSchema(tableName.Schema));
 }
        public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
        {
            string sql = FormatSql(
                "SELECT COUNT(*) FROM {0:NAME} " +
                "WHERE {1:NAME} = '{2}' and {3:NAME} = '{4}'",
                "INDEXES".WithSchema("INFORMATION_SCHEMA"),
                "TABLE_NAME", tableName.Name,
                "INDEX_NAME", indexName);

            int count = Convert.ToInt32(ExecuteScalar(sql));
            return count > 0;
        }
        public override bool ColumnExists(SchemaQualifiedObjectName table, string column)
        {
            string columnsTableName = table.SchemaIsEmpty ? "USER_TAB_COLUMNS" : "ALL_TAB_COLUMNS";

            string sql = FormatSql(
                "SELECT COUNT(*) from {0:NAME} where {1:NAME} = '{2}' and {3:NAME} = '{4}'",
                    columnsTableName, "TABLE_NAME", table.Name, "COLUMN_NAME", column);

            if (!table.SchemaIsEmpty)
            {
                sql += FormatSql(" and {0:NAME} = '{1}'", "OWNER", table.Schema);
            }

            object scalar = ExecuteScalar(sql);
            return Convert.ToInt32(scalar) > 0;
        }
        public override bool IndexExists(string indexName, SchemaQualifiedObjectName tableName)
        {
            string nspname = tableName.SchemaIsEmpty ? "current_schema()" : string.Format("'{0}'", tableName.Schema);
            string tablename = NeedQuotesForNames ? tableName.Name : tableName.Name.ToLower();
            string indname = NeedQuotesForNames ? indexName : indexName.ToLower();

            var builder = new StringBuilder();

            builder.Append("SELECT count(*) FROM pg_class c ");
            builder.Append("JOIN pg_index i ON i.indexrelid = c.oid ");
            builder.Append("JOIN pg_class c2 ON i.indrelid = c2.oid ");
            builder.Append("LEFT JOIN pg_user u ON u.usesysid = c.relowner ");
            builder.Append("LEFT JOIN pg_namespace n ON n.oid = c.relnamespace ");
            builder.Append("WHERE c.relkind = 'i' ");
            builder.AppendFormat("AND n.nspname = {0} ", nspname);
            builder.AppendFormat("AND c2.relname = '{0}' ", tablename);
            builder.AppendFormat("AND c.relname = '{0}' ", indname);

            int count = Convert.ToInt32(ExecuteScalar(builder.ToString()));
            return count > 0;
        }