import sys

import time

try:

    set

except NameError:

    # Python 2.3 compat

    from sets import Set as set

from django.conf import settings

from django.core.management import call_command

# The prefix to put on the default database name when creating

# the test database.

TEST_DATABASE_PREFIX = 'test_'

class BaseDatabaseCreation(object):

    """

    This class encapsulates all backend-specific differences that pertain to

    database *creation*, such as the column types to use for particular Django

    Fields, the SQL used to create and destroy tables, and the creation and

    destruction of test databases.

    """

    data_types = {}

    def __init__(self, connection):

        self.connection = connection

    def sql_create_model(self, model, style, known_models=set()):

        """

        Returns the SQL required to create a single model, as a tuple of:

            (list_of_sql, pending_references_dict)

        """

        from django.db import models

        opts = model._meta

        final_output = []

        table_output = []

        pending_references = {}

        qn = self.connection.ops.quote_name

        for f in opts.local_fields:

            col_type = f.db_type()

            tablespace = f.db_tablespace or opts.db_tablespace

            if col_type is None:

                # Skip ManyToManyFields, because they're not represented as

                # database columns in this table.

                continue

            # Make the definition (e.g. 'foo VARCHAR(30)') for this field.

            field_output = [style.SQL_FIELD(qn(f.column)),

                style.SQL_COLTYPE(col_type)]

            field_output.append(style.SQL_KEYWORD('%sNULL' % (not f.null and 'NOT ' or '')))

            if f.primary_key:

                field_output.append(style.SQL_KEYWORD('PRIMARY KEY'))

            elif f.unique:

                field_output.append(style.SQL_KEYWORD('UNIQUE'))

            if tablespace and f.unique:

                # We must specify the index tablespace inline, because we

                # won't be generating a CREATE INDEX statement for this field.

                field_output.append(self.connection.ops.tablespace_sql(tablespace, inline=True))

            if f.rel:

                ref_output, pending = self.sql_for_inline_foreign_key_references(f, known_models, style)

                if pending:

                    pr = pending_references.setdefault(f.rel.to, []).append((model, f))

                else:

                    field_output.extend(ref_output)

            table_output.append(' '.join(field_output))

        if opts.order_with_respect_to:

            table_output.append(style.SQL_FIELD(qn('_order')) + ' ' + \

                style.SQL_COLTYPE(models.IntegerField().db_type()) + ' ' + \

                style.SQL_KEYWORD('NULL'))

        for field_constraints in opts.unique_together:

            table_output.append(style.SQL_KEYWORD('UNIQUE') + ' (%s)' % \

                ", ".join([style.SQL_FIELD(qn(opts.get_field(f).column)) for f in field_constraints]))

        full_statement = [style.SQL_KEYWORD('CREATE TABLE') + ' ' + style.SQL_TABLE(qn(opts.db_table)) + ' (']

        for i, line in enumerate(table_output): # Combine and add commas.

            full_statement.append('    %s%s' % (line, i < len(table_output)-1 and ',' or ''))

        full_statement.append(')')

        if opts.db_tablespace:

            full_statement.append(self.connection.ops.tablespace_sql(opts.db_tablespace))

        full_statement.append(';')

        final_output.append('\n'.join(full_statement))

        if opts.has_auto_field:

            # Add any extra SQL needed to support auto-incrementing primary keys.

            auto_column = opts.auto_field.db_column or opts.auto_field.name

            autoinc_sql = self.connection.ops.autoinc_sql(opts.db_table, auto_column)

            if autoinc_sql:

                for stmt in autoinc_sql:

                    final_output.append(stmt)

        return final_output, pending_references

    def sql_for_inline_foreign_key_references(self, field, known_models, style):

        "Return the SQL snippet defining the foreign key reference for a field"

        qn = self.connection.ops.quote_name

        if field.rel.to in known_models:

            output = [style.SQL_KEYWORD('REFERENCES') + ' ' + \

                style.SQL_TABLE(qn(field.rel.to._meta.db_table)) + ' (' + \

                style.SQL_FIELD(qn(field.rel.to._meta.get_field(field.rel.field_name).column)) + ')' +

                self.connection.ops.deferrable_sql()

            ]

            pending = False

        else:

            # We haven't yet created the table to which this field

            # is related, so save it for later.

            output = []

            pending = True

        return output, pending

    def sql_for_pending_references(self, model, style, pending_references):

        "Returns any ALTER TABLE statements to add constraints after the fact."

        from django.db.backends.util import truncate_name

        qn = self.connection.ops.quote_name

        final_output = []

        opts = model._meta

        if model in pending_references:

            for rel_class, f in pending_references[model]:

                rel_opts = rel_class._meta

                r_table = rel_opts.db_table

                r_col = f.column

                table = opts.db_table

                col = opts.get_field(f.rel.field_name).column

                # For MySQL, r_name must be unique in the first 64 characters.

                # So we are careful with character usage here.

                r_name = '%s_refs_%s_%x' % (r_col, col, abs(hash((r_table, table))))

                final_output.append(style.SQL_KEYWORD('ALTER TABLE') + ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)%s;' % \

                    (qn(r_table), truncate_name(r_name, self.connection.ops.max_name_length()),

                    qn(r_col), qn(table), qn(col),

                    self.connection.ops.deferrable_sql()))

            del pending_references[model]

        return final_output

    def sql_for_many_to_many(self, model, style):

        "Return the CREATE TABLE statments for all the many-to-many tables defined on a model"

        output = []

        for f in model._meta.local_many_to_many:

            output.extend(self.sql_for_many_to_many_field(model, f, style))

        return output

    def sql_for_many_to_many_field(self, model, f, style):

        "Return the CREATE TABLE statements for a single m2m field"

        from django.db import models

        from django.db.backends.util import truncate_name

        output = []

        if f.creates_table:

            opts = model._meta

            qn = self.connection.ops.quote_name

            tablespace = f.db_tablespace or opts.db_tablespace

            if tablespace:

                sql = self.connection.ops.tablespace_sql(tablespace, inline=True)

                if sql:

                    tablespace_sql = ' ' + sql

                else:

                    tablespace_sql = ''

            else:

                tablespace_sql = ''

            table_output = [style.SQL_KEYWORD('CREATE TABLE') + ' ' + \

                style.SQL_TABLE(qn(f.m2m_db_table())) + ' (']

            table_output.append('    %s %s %s%s,' %

                (style.SQL_FIELD(qn('id')),

                style.SQL_COLTYPE(models.AutoField(primary_key=True).db_type()),

                style.SQL_KEYWORD('NOT NULL PRIMARY KEY'),

                tablespace_sql))

            deferred = []

            inline_output, deferred = self.sql_for_inline_many_to_many_references(model, f, style)

            table_output.extend(inline_output)

            table_output.append('    %s (%s, %s)%s' %

                (style.SQL_KEYWORD('UNIQUE'),

                style.SQL_FIELD(qn(f.m2m_column_name())),

                style.SQL_FIELD(qn(f.m2m_reverse_name())),

                tablespace_sql))

            table_output.append(')')

            if opts.db_tablespace:

                # f.db_tablespace is only for indices, so ignore its value here.

                table_output.append(self.connection.ops.tablespace_sql(opts.db_tablespace))

            table_output.append(';')

            output.append('\n'.join(table_output))

            for r_table, r_col, table, col in deferred:

                r_name = '%s_refs_%s_%x' % (r_col, col,

                        abs(hash((r_table, table))))

                output.append(style.SQL_KEYWORD('ALTER TABLE') + ' %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)%s;' %

                (qn(r_table),

                truncate_name(r_name, self.connection.ops.max_name_length()),

                qn(r_col), qn(table), qn(col),

                self.connection.ops.deferrable_sql()))

            # Add any extra SQL needed to support auto-incrementing PKs

            autoinc_sql = self.connection.ops.autoinc_sql(f.m2m_db_table(), 'id')

            if autoinc_sql:

                for stmt in autoinc_sql:

                    output.append(stmt)

        return output

    def sql_for_inline_many_to_many_references(self, model, field, style):

        "Create the references to other tables required by a many-to-many table"

        from django.db import models

        opts = model._meta

        qn = self.connection.ops.quote_name

        table_output = [

            '    %s %s %s %s (%s)%s,' %

                (style.SQL_FIELD(qn(field.m2m_column_name())),

                style.SQL_COLTYPE(models.ForeignKey(model).db_type()),

                style.SQL_KEYWORD('NOT NULL REFERENCES'),

                style.SQL_TABLE(qn(opts.db_table)),

                style.SQL_FIELD(qn(opts.pk.column)),

                self.connection.ops.deferrable_sql()),

            '    %s %s %s %s (%s)%s,' %

                (style.SQL_FIELD(qn(field.m2m_reverse_name())),

                style.SQL_COLTYPE(models.ForeignKey(field.rel.to).db_type()),

                style.SQL_KEYWORD('NOT NULL REFERENCES'),

                style.SQL_TABLE(qn(field.rel.to._meta.db_table)),

                style.SQL_FIELD(qn(field.rel.to._meta.pk.column)),

                self.connection.ops.deferrable_sql())

        ]

        deferred = []

        return table_output, deferred

    def sql_indexes_for_model(self, model, style):

        "Returns the CREATE INDEX SQL statements for a single model"

        output = []

        for f in model._meta.local_fields:

            output.extend(self.sql_indexes_for_field(model, f, style))

        return output

    def sql_indexes_for_field(self, model, f, style):

        "Return the CREATE INDEX SQL statements for a single model field"

        if f.db_index and not f.unique:

            qn = self.connection.ops.quote_name

            tablespace = f.db_tablespace or model._meta.db_tablespace

            if tablespace:

                sql = self.connection.ops.tablespace_sql(tablespace)

                if sql:

                    tablespace_sql = ' ' + sql

                else:

                    tablespace_sql = ''

            else:

                tablespace_sql = ''

            output = [style.SQL_KEYWORD('CREATE INDEX') + ' ' +

                style.SQL_TABLE(qn('%s_%s' % (model._meta.db_table, f.column))) + ' ' +

                style.SQL_KEYWORD('ON') + ' ' +

                style.SQL_TABLE(qn(model._meta.db_table)) + ' ' +

                "(%s)" % style.SQL_FIELD(qn(f.column)) +

                "%s;" % tablespace_sql]

        else:

            output = []

        return output

    def sql_destroy_model(self, model, references_to_delete, style):

        "Return the DROP TABLE and restraint dropping statements for a single model"

        # Drop the table now

        qn = self.connection.ops.quote_name

        output = ['%s %s;' % (style.SQL_KEYWORD('DROP TABLE'),

                              style.SQL_TABLE(qn(model._meta.db_table)))]

        if model in references_to_delete:

            output.extend(self.sql_remove_table_constraints(model, references_to_delete, style))

        if model._meta.has_auto_field:

            ds = self.connection.ops.drop_sequence_sql(model._meta.db_table)

            if ds:

                output.append(ds)

        return output

    def sql_remove_table_constraints(self, model, references_to_delete, style):

        from django.db.backends.util import truncate_name

        output = []

        qn = self.connection.ops.quote_name

        for rel_class, f in references_to_delete[model]:

            table = rel_class._meta.db_table

            col = f.column

            r_table = model._meta.db_table

            r_col = model._meta.get_field(f.rel.field_name).column

            r_name = '%s_refs_%s_%x' % (col, r_col, abs(hash((table, r_table))))

            output.append('%s %s %s %s;' % \

                (style.SQL_KEYWORD('ALTER TABLE'),

                style.SQL_TABLE(qn(table)),

                style.SQL_KEYWORD(self.connection.ops.drop_foreignkey_sql()),

                style.SQL_FIELD(truncate_name(r_name, self.connection.ops.max_name_length()))))

        del references_to_delete[model]

        return output

    def sql_destroy_many_to_many(self, model, f, style):

        "Returns the DROP TABLE statements for a single m2m field"

        qn = self.connection.ops.quote_name

        output = []

        if f.creates_table:

            output.append("%s %s;" % (style.SQL_KEYWORD('DROP TABLE'),

                style.SQL_TABLE(qn(f.m2m_db_table()))))

            ds = self.connection.ops.drop_sequence_sql("%s_%s" % (model._meta.db_table, f.column))

            if ds:

                output.append(ds)

        return output

    def create_test_db(self, verbosity=1, autoclobber=False):

        """

        Creates a test database, prompting the user for confirmation if the

        database already exists. Returns the name of the test database created.

        """

        if verbosity >= 1:

            print "Creating test database..."

        test_database_name = self._create_test_db(verbosity, autoclobber)

        self.connection.close()

        settings.DATABASE_NAME = test_database_name

        call_command('syncdb', verbosity=verbosity, interactive=False)

        if settings.CACHE_BACKEND.startswith('db://'):

            cache_name = settings.CACHE_BACKEND[len('db://'):]

            call_command('createcachetable', cache_name)

        # Get a cursor (even though we don't need one yet). This has

        # the side effect of initializing the test database.

        cursor = self.connection.cursor()

        return test_database_name

    def _create_test_db(self, verbosity, autoclobber):

        "Internal implementation - creates the test db tables."

        suffix = self.sql_table_creation_suffix()

        if settings.TEST_DATABASE_NAME:

            test_database_name = settings.TEST_DATABASE_NAME

        else:

            test_database_name = TEST_DATABASE_PREFIX + settings.DATABASE_NAME

        qn = self.connection.ops.quote_name

        # Create the test database and connect to it. We need to autocommit

        # if the database supports it because PostgreSQL doesn't allow

        # CREATE/DROP DATABASE statements within transactions.

        cursor = self.connection.cursor()

        self.set_autocommit()

        try:

            cursor.execute("CREATE DATABASE %s %s" % (qn(test_database_name), suffix))

        except Exception, e:

            sys.stderr.write("Got an error creating the test database: %s\n" % e)

            if not autoclobber:

                confirm = raw_input("Type 'yes' if you would like to try deleting the test database '%s', or 'no' to cancel: " % test_database_name)

            if autoclobber or confirm == 'yes':

                try:

                    if verbosity >= 1:

                        print "Destroying old test database..."

                    cursor.execute("DROP DATABASE %s" % qn(test_database_name))

                    if verbosity >= 1:

                        print "Creating test database..."

                    cursor.execute("CREATE DATABASE %s %s" % (qn(test_database_name), suffix))

                except Exception, e:

                    sys.stderr.write("Got an error recreating the test database: %s\n" % e)

                    sys.exit(2)

            else:

                print "Tests cancelled."

                sys.exit(1)

        return test_database_name

    def destroy_test_db(self, old_database_name, verbosity=1):

        """

        Destroy a test database, prompting the user for confirmation if the

        database already exists. Returns the name of the test database created.

        """

        if verbosity >= 1:

            print "Destroying test database..."

        self.connection.close()

        test_database_name = settings.DATABASE_NAME

        settings.DATABASE_NAME = old_database_name

        self._destroy_test_db(test_database_name, verbosity)

    def _destroy_test_db(self, test_database_name, verbosity):

        "Internal implementation - remove the test db tables."

        # Remove the test database to clean up after

        # ourselves. Connect to the previous database (not the test database)

        # to do so, because it's not allowed to delete a database while being

        # connected to it.

        cursor = self.connection.cursor()

        self.set_autocommit()

        time.sleep(1) # To avoid "database is being accessed by other users" errors.

        cursor.execute("DROP DATABASE %s" % self.connection.ops.quote_name(test_database_name))

        self.connection.close()

    def set_autocommit(self):

        "Make sure a connection is in autocommit mode."

        if hasattr(self.connection.connection, "autocommit"):

            if callable(self.connection.connection.autocommit):

                self.connection.connection.autocommit(True)

            else:

                self.connection.connection.autocommit = True

        elif hasattr(self.connection.connection, "set_isolation_level"):

            self.connection.connection.set_isolation_level(0)

    def sql_table_creation_suffix(self):

        "SQL to append to the end of the test table creation statements"

        return ''