<commit_before>###########################################################
#
# Copyright (c) 2005, Southpaw Technology
#                     All Rights Reserved
#
# PROPRIETARY INFORMATION.  This software is proprietary to
# Southpaw Technology, and is not to be reproduced, transmitted,
# or disclosed in any way without written permission.
#
#
#

__all__ = ['DatabaseImpl', 'PostgresImpl', 'OracleImpl', 'SqliteImpl', 'MySQLImpl', 'SQLServerImpl', 'TacticImpl']

import os, sys, types, re
import subprocess
import datetime

from pyasm.common import Environment, SetupException, Config, Container, TacticException

class DatabaseImplException(TacticException):
    pass



class DatabaseImplInterface(object):


    def get_columns(cls, db_resource, table):
        pass

    def get_column_info(cls, db_resource, table, use_cache=True):
        pass

    def is_column_sortable(my, db_resource, table, column):
        pass

    def get_id_col(db_resource, search_type):
        pass

    def get_code_col(db_resource, search_type):
        pass


    def get_page(my, limit=None, offset=0):
        pass

    def get_table_info(my, db_resource):
        pass

    def table_exists(my, db_resource, table):
        pass

    def execute_query(my, sql, select):
        pass

    def execute_update(my, sql, update):
        pass




class DatabaseImpl(DatabaseImplInterface):
    '''Provides an abstraction layer for the various databases'''

    def get_database_type(my):
        return None


    def get_version(my):
        return (0,0,0)


    def get(vendor=None):
        '''Get the current database implementation'''
        from sql import Sql
        if not vendor:
            vendor = Sql.get_default_database_type()
            return DatabaseImpl.get(vendor)

        if vendor == "PostgreSQL":
            return PostgresImpl()
        elif vendor == "Sqlite":
            return SqliteImpl()
        elif vendor == "MySQL":
            return MySQLImpl()
        elif vendor == "SQLServer":
            return SQLServerImpl()
        elif vendor == "Oracle":
            return OracleImpl()

        # TEST
        elif vendor == "MongoDb":
            from mongodb import MongoDbImpl
            return MongoDbImpl()
        elif vendor == "TACTIC":
            return TacticImpl()
        raise DatabaseImplException("Vendor [%s] not supported" % vendor)

    get = staticmethod(get)


    def preprocess_sql(my, data, unquoted_cols):
        pass

    def postprocess_sql(my, statement):
        return statement

    def process_value(my, name, value, column_type="varchar"):
        '''process a database value based on column type.

        @params:
            value: current input value
            column_type: the database column type

        @return
            dict:
                value: the new value
                quoted: True|False - determines whether the value is quoted or not
        '''
        return None




    def get_id_col(my, db_resource, search_type):
        from pyasm.search import SearchType
        search_type = SearchType.get(search_type)
        id_col = search_type.get_id_col()
        return id_col


    def get_code_col(my, db_resource, search_type):
        from pyasm.search import SearchType
        search_type = SearchType.get(search_type)
        code_col = search_type.get_code_col()
        return code_col




    #
    # Schema functions
    #
    def get_schema_dir(my):
        # get the schema directory for the appropriate database type
        install_dir = Environment.get_install_dir()
        schema_dir = "%s/src/pyasm/search/upgrade/%s" % (install_dir, my.get_database_type().lower() )
        if not os.path.exists(schema_dir):
            raise DatabaseImplException("Schema '%s' does not exist" % schema_dir)

        return schema_dir


    def import_sql_file(my, db_resource, path):

        from pyasm.search import DbResource, DbContainer
        if isinstance(db_resource, basestring):
            database = db_resource
        else:
            database = db_resource.get_database()

        sql = DbContainer.get(db_resource)

        f = open(path, 'r')
        data = f.read()
        f.close()

        print "Importing sql to database [%s]..." % (database)
        print "   using path [%s]" % (path)

        cmds = data.split(";")
        for cmd in cmds:
            cmd = cmd.strip()
            sql.do_update(cmd)

        my.clear_table_cache()
        sql = DbContainer.get(db_resource)



    def import_schema(my, db_resource, type):
        '''import the schema of certain type to the given database'''

        # import the necessary schema
        types = ['config', type]
        
        # sthpw schema is composed of 2 files
        if db_resource == 'sthpw':
            types.insert(0, 'bootstrap')
        for schema_type in types:
            schema_dir = my.get_schema_dir()
            schema_path = "%s/%s_schema.sql" % (schema_dir, schema_type)
            if not os.path.exists(schema_path):
                Environment.add_warning("Schema does not exist", "Schema '%s' does not exist" % schema_path)
                continue

            my.import_sql_file(db_resource, schema_path)




    def import_default_data(my, db_resource, type):
        '''import the data of certain type to the given database'''

        # import the necessary schema
        schema_dir = my.get_schema_dir()
        data_path = "%s/%s_data.sql" % (schema_dir, type)
        data_path = os.path.normpath(data_path)
        if not os.path.exists(data_path):
            #Environment.add_warning("Default data does not exist", "Data '%s' does not exist" % data_path)
            return

        my.import_sql_file(db_resource, data_path)



    #
    # Database methods for base database implementation
    #
    def has_sequences(my):
        raise DatabaseImplException("TACTIC database implementation for current database vendor does not have method has_sequences() defined.")

    def get_table_info(my, database):
        raise DatabaseImplException("Must override 'get_table_info' for [%s]" % my.vendor)


    def database_exists(my, database, host=None, port=None):
        '''@param: 
            database - if string, it's just a database name (old)
                       if DbResource, it could contain the host already
            host - can be localhost or a different server
            port - port number that the database is listen on'''
        try:
            db_resource = database
            #from pyasm.search import DbContainer, DbResource
            from sql import DbContainer, DbResource
            if isinstance(database, basestring):
                if host == None:
                    vendor = Config.get_value("database", "vendor")
                    host = Config.get_value("database", "server")
                    port = Config.get_value("database", "port")
                else:
                    vendor = my.get_database_type()
                db_resource = DbResource(database=database, host=host, vendor=vendor, port=port)
            
            cached = Container.get("Sql:database_exists:%s"%db_resource.get_key()) 
            if cached != None:
                return cached

            sql = DbContainer.get(db_resource, connect=True)
            if sql:
                if not sql.get_connection():
                    sql.connect()
            else:
                return False
            # cache it for repeated use
            Container.put("Sql:database_exists:%s"%db_resource.get_key(), True) 
        except Exception, e:
            #print "Error: ", str(e)
            return False
        else:
            return True


    def clear_table_cache(cls):
        # this relies on the __str__ method of db_resource
        key = "DatabaseImpl:table_exists"
        Container.remove(key)
        key = "DatabaseImpl:table_info"
        Container.remove(key)
        key = "DatabaseImpl:column_info"
        Container.remove(key)
    clear_table_cache = classmethod(clear_table_cache)


    def table_exists(my, db_resource, table):

        key = "DatabaseImpl:table_exists"

        cached_dict = Container.get(key)
        if cached_dict == None:
            cached_dict = {}
            Container.put(key, cached_dict)

        # this relies on the __str__ method of db_resource
        key2 = "%s:%s" % (db_resource, table)
        cached = cached_dict.get(key2)
        if cached != None:
            return cached

        table_info = my.get_table_info(db_resource)
        if table_info.has_key(table):
            exists = True
        else:
            exists = False

        cached_dict[key2] = exists

        return exists


    def get_column_types(my, db_resource, table):
        return {}



    #
    # Save point methods
    #
    def has_savepoint(my):
        return True

    def set_savepoint(my, name='save_pt'):
        '''set a savepoint'''
        if not my.has_savepoint():
            return None
        return "SAVEPOINT %s" %name

    def rollback_savepoint(my, name='save_pt', release=False):
        if not my.has_savepoint():
            return None
        stmt = "ROLLBACK TO SAVEPOINT %s"%name
        return stmt

    def release_savepoint(my, name='save_pt'):
        if not my.has_savepoint():
            return None
        stmt = "RELEASE SAVEPOINT %s"%name
        return stmt






    def get_constraints(my, database, table):
        return []




    def handle_pagination(my, statement, limit, offset):
        return statement

    def get_id_override_statement(my, table, override=False):
        return ''

    def get_constraint(my, mode, name='', columns=[], table=None):
        if not name and table:
            if mode == 'PRIMARY KEY':
                name = '%s_pkey' %table
        return 'CONSTRAINT %s %s (%s)' %(name, mode, ','.join(columns))



    def get_regex_filter(my, column, regex, op='EQI'):
        return None


    def get_text_search_filter(cls, column, keywords, column_type, table=None):
        '''default impl works with Postgres'''
        if isinstance(keywords, basestring):
            if keywords.find("|") != -1 or keywords.find("&") != -1:
                # prevent syntax error from multiple | or &
                keywords = re.sub( r'\|+', r'|', keywords)
                keywords = re.sub( r'\&+', r'&', keywords)
                value = keywords
            else:
                keywords = keywords.strip()
                keywords = keywords.replace("  ", "")
                parts = keywords.split(" ")
                value = ' | '.join(parts)

        elif type(keywords) == types.ListType:
            # remove empty strings from the list
            keywords = filter(None, keywords)
            value = ' & '.join(keywords)
        else:
            value = str(keywords)

        # explicitly set the config in case there is an index available
        # TODO: this should be configurable
        config = 'english'

        # for multiple columns
        #coalesce(title,'') || ' ' || coalesce(body,'')
        
        # avoid syntax error
        value = value.replace("'", "''")

       

        if table:
            column = '"%s"."%s"' % (table, column)
        else:
            column = '"%s"' % column

        if column_type in ['integer','serial']:
            column = "CAST(%s AS varchar(10))" %column
        else:
            # prefix matching
            value = '%s:*'%value
        
        wheres = []
        wheres.append('''to_tsvector('%s', %s)''' % (config, column) )
        wheres.append("@@")
        wheres.append("to_tsquery('%s', '%s')" % (config, value) )

        where = " ".join(wheres)
        return where

    get_text_search_filter = classmethod(get_text_search_filter)

    def get_columns(cls, db_resource, table):
        '''get ordered column names'''
         # do a dummy select to get the ordered columns
        from sql import Select, DbContainer
        sql = DbContainer.get(db_resource)

        select = Select()
        select.set_database(db_resource)
        select.add_table(table)
        select.set_limit(0)
        statement = select.get_statement()
        sql.do_query(statement)

        columns = []
        for description in sql.description:
            # convert to unicode
            value = unicode(description[0], 'utf-8')
            columns.append(value)

        return columns

    get_columns = classmethod(get_columns)




    def can_join(db_resource1, db_resource2):

        # if the hosts are difference, joins cannot happen
        host1 = db_resource1.get_host()
        host2 = db_resource2.get_host()
        if host1 != host2:
            return False

        # if the database types are differenct, joins cannot happen
        database_type1 = db_resource1.get_database_type()
        database_type2 = db_resource2.get_database_type()
        if database_type1 != database_type2:
            return False

        # if the host is the same and the database is the same, then joins
        # can happen
        database1 = db_resource1.get_database()
        database2 = db_resource2.get_database()
        if database1 == database2:
            return True

        # multi database joins are not support in Postgres or Sqlite
        if database_type1 in ["PostgreSQL", "Sqlite"]:
            return False
        if database_type2 in ["PostgreSQL", "Sqlite"]:
            return False

        # otherwise joins can happen between the two resources
        return True

    can_join = staticmethod(can_join)


    def can_search_types_join(search_type1, search_type2):
        from search import SearchType
        db_resource = SearchType.get_db_resource_by_search_type(search_type1)
        db_resource2 = SearchType.get_db_resource_by_search_type(search_type2)
        can_join = DatabaseImpl.can_join(db_resource, db_resource2)
        return can_join
    can_search_types_join = staticmethod(can_search_types_join)



    # Defines temporary column name to be used.  Only SQLServerImpl implements
    # this
    def get_temp_column_name(my):
        return ""



class BaseSQLDatabaseImpl(DatabaseImpl):
    pass



class SQLServerImpl(BaseSQLDatabaseImpl):
    '''Implementation for Microsoft SQL Server's SQL'''

    def get_database_type(my):
        return "SQLServer"

    def __init__(my):

        # FIXME: this will not work in mixed db cases because it assumes a global
        # single database
        my.server   = Config.get_value("database", "server")
        my.port     = Config.get_value("database", "port")
        my.user     = Config.get_value("database", "user")
        my.password = Config.get_value("database", "password")




    def get_version(my):
        from sql import DbContainer
        sql = DbContainer.get("sthpw")
        result = sql.do_query("select @@version")

        # result is [(u'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) \n\tApr  2 2010 15:48:46 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)\n', )]
        version_str = result[0][0]

        parts = version_str.split(" ")
        version_parts = parts[7].split(".")
        version_parts = [int(x) for x in version_parts]
        return version_parts


    def get_create_sequence(my, sequence_name):
        #return 'CREATE SEQUENCE "%s" START WITH 1 INCREMENT BY 1 NO MAXVALUE CACHE 1' % name
        # SQL Server specific implementation.
        #postfix_len = '_id_seq'.__len__()
        #table_name_len = sequence_name.__len__() - postfix_len
        #table_name = sequence_name[0:table_name_len] 
        #print '  get_create_sequence: table_name = ', table_name
        #return 'ALTER TABLE %s ADD %s INT IDENTITY(100, 5) ' % (table_name, sequence_name)
        #return 'ALTER COLUMN %s ADD %s INT IDENTITY(100, 5) ' % (table_name, sequence_name)
        return

    def get_sequence_name(my, table, database=None):
        # SQL Server specific implementation: use the ID column as the sequence.
        # OLD return "%s_id_seq" % table
        from pyasm.search import SearchType
        if isinstance(table, SearchType):
            search_type = table
            table = search_type.get_table()
        return table



    def get_page(my, limit=None, offset=0, table_input=0, already_in_where_clause=0):
        '''get the pagination sql based on limit and offset'''
        #
        # SQL Server implementation
        #
        return None

    def handle_pagination(my, statement, limit, offset):
        '''specific method to handle MS SQL Server's pagination'''

        if limit == None:
            return statement

        # SQL Server implementation
        # 
        # Example:
        # SELECT * from (SELECT TOP 100 *, 
        #        ROW_NUMBER() over (ORDER BY id) as _tmp_spt_rownum FROM
        # [tblCatalogCrossReference]  WHERE code='abc'
        #   ) tmp_spt_table 
        # WHERE tmp_spt_table._tmp_spt_rownum between (5) and (10)
        start = offset + 1
        end = start + int(limit) - 1
        # one can set the limit to be 0 to return 0 row back
        if end < start and limit > 0:
            raise DatabaseImplException('start [%s] must be larger than end [%s] for limit' %(start, end))
        #order_by = ''
        #if order_bys:
        #    order_by = "ORDER BY %s" % ", ".join( order_bys )
        tmp_col = my.get_temp_column_name()
        page = "tmp_spt_table.%s BETWEEN (%s) AND (%s)" % (tmp_col, start, end)

        statement = "SELECT * FROM ( \
                     %s ) tmp_spt_table \
                     WHERE %s" % (statement, page)
        return statement

    def get_id_override_statement(my, table, override=True):
        '''SQL Server needs to manually turn on an off the auto id generation feature'''
        if override:
            return "SET IDENTITY_INSERT %s ON" % table
        else:
            return "SET IDENTITY_INSERT %s OFF" % table




    #
    # Column methods
    #
    def get_boolean(my, not_null=False):
        parts = []
        parts.append("bit")
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_serial(my, not_null=False):
        parts = []
        # For SQL Server, replaced "serial" type with "identity".
        # create table "hi" ("colA" int, "id" int identity(1,1) primary key("id") );
        # 
        parts.append("int identity(1,1)")
        return " ".join(parts)


    def get_int(my, length=4, not_null=False):
        """
        http://technet.microsoft.com/en-us/library/cc917573.aspx

        If the integer is from 1 through 255, use tinyint.
        If the integer is from -32768 through 32767, use smallint.
        If the integer is from -2,147,483,648 through 2,147,483,647 use int.
        If you require a number with decimal places, use decimal. Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server decimal do not round).
        If you are not sure, use decimal; it most closely resembles Oracle NUMBER data type.

        """
        parts = []
        parts.append("int")
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_text(my, not_null=False):
        parts = []
        parts.append("varchar(max)")
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)


    def get_varchar(my, length=256, not_null=False):
        assert length
        if length == -1:
            length = 'max'
        parts = []
        parts.append("varchar(%s)" % length)
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_nvarchar(my, length=256, not_null=False):
        assert length
        if length == -1:
            length = 'max'
        parts = []
        parts.append("nvarchar(%s)" % length)
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_timestamp(my, default="now", not_null=False, timezone=False):
        # SQL Server implementation.
        parts = []
        if timezone:
            parts.append("datetimeoffset(6)")
        else:
            parts.append("datetime2(6)")

        if default:
            if default == "now":
                parts.append("DEFAULT(%s)" % my.get_timestamp_now())
            else:
                parts.append("DEFAULT(%S)" % default)

        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)


    def get_timestamp_now(my, offset=None, type=None, op='+'):
        # SQL Server implementation.

        if not type:
            type = 'day'
        
        if offset:
            # Postgres: parts.append("'%s %s'::interval" % (offset, type) )
            # Postgres: eg. now() + '10 hour'::interval

            # SQL Server: DATEADD(hour, +10, CURRENT_TIMESTAMP)
            part = "DATEADD(%s, %s%s, GETDATE())" % (type, op, offset) 
        else:
            part = "GETDATE()"
        return part


    
    #
    # Sequence methods for SQL Server
    #
    def has_sequences(my):
        return True

    def get_nextval(my, sequence):
        return '"%s".nextval' % sequence

    def get_currval(my, sequence):
        # IDENT_CURRENT returns the last identity value
        # generated for a specified table[
        return 'SELECT IDENT_CURRENT(\'' + sequence + '\')'

    def get_currval_select(my, sequence):
        # IDENT_CURRENT returns the last identity value
        # generated for a specified table[
        return 'SELECT IDENT_CURRENT(\'' + sequence + '\')'

    def get_nextval_select(my, sequence):
        # In Postgres, when a table is created, currval is undefined and nextval is 1.
        # SQL Server doesn't have a concept of nextval.
        # When the table is created, the currval *is already 1*, 
        # and so nextval becomes 2.  This poses a problem.
        # The solution is to check if
        # ident_current('table_name') = 1 then just return 1.

        cmd  = "declare @cur_id int;"
        cmd += "select @cur_id = ident_current('" + sequence + "');"
        cmd += "if @cur_id = 1"
        cmd += "  select @cur_id;"
        cmd += "else"
        cmd += "  select @cur_id + 1;"
        return cmd

    def get_setval_select(my, sequence, num):
        # Set the current identity value for the specified table.
        cmd = "DBCC CHECKIDENT ('" + sequence + "', RESEED, " + str(num) + ");"
        return cmd

    # Method to build and return an SQL statement that can be run to reset the ID sequence for a table to a number
    # that is one greater than the highest index found in the given table.  NOTE: this ASSUMES that there are rows
    # in the table to provide a MAX id value from.  TODO: provide handling for a table with no data rows.
    def get_reset_table_sequence_statement(my, table, database=None):

        from sql import DbContainer
        sql = DbContainer.get(database)

        query = "SELECT MAX(id) + 1 FROM %s ;" % table
        result = sql.do_query(query)
        max_id = result[0][0]

        reset_seq_sql = "ALTER SEQUENCE %s_id_seq RESTART WITH %d ;" % (table, max_id)
        return reset_seq_sql


    #
    # Regular Expressions
    # 
    def get_regex_filter(my, column, regex, op='EQI'):
        if op == 'EQI':
            op = 'LIKE'
            column = 'lower(CAST("%s" AS varchar(max)))' %column
            regex = "lower('%%%s%%')"%regex
        elif op == 'EQ':
            op = 'LIKE'
            regex = "'%%%s%%'" %regex
        elif op == 'NEQI':
            op = 'NOT LIKE'
            regex = "lower('%%%s%%')"%regex
        elif op == 'NEQ':
            op = 'NOT LIKE'
            regex = "'%%%s%%'" %regex
        else:
            raise SetupException('Invalid op [%s]. Try EQ, EQI, NEQ, or NEQI' %op)
            
        return "%s %s %s" %(column, op, regex)


    def get_text_search_filter(cls, column, keywords, column_type, table=None):
        '''When Full Text Index is created in the db for the table, it works with SQLServer 2008 and above'''
        if isinstance(keywords, basestring):
            value = keywords
        elif type(keywords) == types.ListType:
            # remove empty strings from the list
            keywords = filter(None, keywords)
            value = " ".join(keywords)
        else:
            value = str(keywords)

        
        # avoid syntax error
        value = value.replace("'", "''")

       

        if table:
            column = '"%s"."%s"' % (table, column)
        else:
            column = '"%s"' % column

        """
        if column_type in ['integer','serial']:
            column = "CAST(%s AS varchar(10))" %column
       
        """
        wheres = []
        # use FREETEXT() or CONTAINS(), CONTAINS() takes OR AND operator
        wheres.append("FREETEXT(%s, '%s')" % (column, value) )

        where = " ".join(wheres)
        return where

    get_text_search_filter = classmethod(get_text_search_filter)


    #
    # Type process methods
    #
    def process_value(my, name, value, column_type="varchar"):

        if column_type in ['timestamp','datetime']:
            quoted = False
            lower_value = value.lower()
            if value == "NOW":
                value = "getdate()"
                #return {"value": value, "quoted": quoted}
            # FIXME: this is implemented this way because set_value 
            # can be called twice.  This method should called from commit
            # and not set_value
            elif isinstance(value, datetime.datetime):
                pass
            elif not value:
                pass
            elif not lower_value.startswith("convert") and not lower_value.startswith("getdate") and not lower_value.startswith("dateadd") :
                if value == 'NULL':
                    pass
                else:
                    value = "convert(datetime2, '%s', 0)" % value
                
            return {"value": value, "quoted": quoted}
          


        elif column_type in ['uniqueidentifier'] and value == "NEWID":
            value = "newid()"
            quoted = False
            return {"value": value, "quoted": quoted}




    #
    # Database methods
    # 
    def _get_db_info(my, db_resource):
        ''' get the database info from the config file'''
        if isinstance(db_resource, DbResource):
            host = db_resource.get_host()
            user = db_resource.get_user()
            password = db_resource.get_password()
            port = db_resource.get_port()
        else:
            host = Config.get_value("database", "server")
            user = Config.get_value("database", "user")
            password = Config.get_value("database", "password")
            port = Config.get_value("database", "port")


        parts = []
        host_str ="-S %s" % host 
        if port:
            host_str = '%s,%s'%(host_str, port)
        parts.append(host_str)
        parts.append("-U %s" % user)
        parts.append("-P %s" % password)

        
        return " ".join(parts)


    def create_database(my, database):
        '''create a database.  This is done by a system command'''
        
        # if the database already exists, do nothing
        if my.database_exists(database):
            return

        # TODO: Retrieve server, username, password from TACTIC config file.
        # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -Q "create database test1"
        # note: The database we are connecting to must be 'sthpw'
        create_SQL_arg = '"CREATE DATABASE ' + database + '"'
        create = 'sqlcmd -S %s,%s -U %s -P %s -Q %s' % \
                 (my.server, my.port, my.user, my.password, create_SQL_arg)
        cmd = os.popen(create)
        result = cmd.readlines()
        if not result:
            print "No output from sql command to create db [%s], assumed success" % database
            cmd.close()
            return
            #raise Exception("Error creating database '%s'" % database)
        cmd.close()

        if result[0].find("already exists") != -1:
            print "already exists"
            print "  Try deleting C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\<database_name>.mdf"
        else:
            print "no returned result from database creation (sqlcmd)"


    def drop_database(my, database):
        '''remove a database in SQL Server. Note this is a very dangerous
        operation.  Use with care.'''
        # if the database does not exist, do nothing
        #if not database_exists(database):
        #    return

  
        # TODO: Retrieve server, username, password from TACTIC config file.
        # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -Q "dropdatabase test1"
        # note: The database we are connecting to must be 'sthpw'
        drop_SQL_arg = '"DROP DATABASE %s"' %database
        create = 'sqlcmd -S %s,%s -U %s -P %s -Q %s' % \
                 (my.server, my.port, my.user, my.password, drop_SQL_arg)
        cmd = os.popen(create)
        result = cmd.readlines()
        if not result:
            print "No output from sql command to drop db [%s], assumed success" % database
            cmd.close()
            return
        else:
            print result
        cmd.close()


    def get_modify_column(my, table, column, type, not_null=None):
        ''' get the statement for setting the column type '''
        # this may not return the type exacty like before like varchar is in place of
        # varchar(256) due to the column type returned from the sql impl
        statements = []
        statements.append('ALTER TABLE "%s" ALTER COLUMN "%s" %s' \
            % (table,column,type))
        if not_null == None:
            return statements
        if not_null:
            # In order to set the column to disallow NULL values,
            # we must first set any current NULL values to the empty string ''.
            statements.append('UPDATE "%s" SET "%s"=\'\' WHERE %s IS NULL' \
            % (table,column, column))

            # Now that any existing NULL values for that column are set to the empty string,
            # proceed to alter the column so that it disallows NULL values.
            statements.append('ALTER TABLE "%s" ALTER COLUMN "%s" %s NOT NULL' \
            % (table,column,type))
        else:
            statements.append('ALTER TABLE "%s" ALTER COLUMN "%s" %s' \
                % (table, column, type))
        return statements



    def import_schema(my, db_resource, type):
        '''import the schema of certain type to the given database'''


        # DEPRECATED: this shouldn't be necessary anymore as the base class
        # should be general enough

        from pyasm.search import DbResource, DbContainer
        if isinstance(db_resource, basestring):
            database = db_resource
        else:
            database = db_resource.get_database()

        sql = DbContainer.get(db_resource)


        types = ['config', type]
        for schema_type in types:
            schema_dir = my.get_schema_dir()
            schema_path = "%s/%s_schema.sql" % (schema_dir, schema_type)
            schema_path = os.path.normpath(schema_path)
            if not os.path.exists(schema_path):
                Environment.add_warning("Schema does not exist", "Schema '%s' does not exist" % schema_path)
                return
                #raise Exception("Schema '%s' does not exist" % schema_path)

            # cmd = 'psql -q %s %s < "%s"' % (my._get_db_info(database), database, schema_path)
            # TODO: Retrieve server, username, password from TACTIC config file.
            # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -i c:/schema.py
            cmd = 'sqlcmd -S %s,%s -U %s -P %s -d %s -i "%s"' % \
                  (my.server, my.port, my.user, my.password, database, schema_path)

           
            print "Importing schema ..."
            print cmd
            os.system(cmd)
            #program = subprocess.call(cmd, shell=True)
            #print "FINSIHED importing schema"


    def import_default_data(my, db_resource, type):
        '''import the data of certain type to the given database'''

        from sql import DbResource, DbContainer
        if isinstance(db_resource, DbResource):
            database = db_resource.get_database()
        else:
            database = db_resource

        # import the necessary schema
        schema_dir = my.get_schema_dir()
        data_path = "%s/%s_data.sql" % (schema_dir, type)
        data_path = os.path.normpath(data_path)
        if not os.path.exists(data_path):
            #Environment.add_warning("Default data does not exist", "Data '%s' does not exist" % data_path)
            return

        #cmd = 'psql -q %s %s < "%s"' % (my._get_db_info(database), database, data_path)
        # TODO: Retrieve server, username, password from TACTIC config file.
        # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -i c:/schema.py
        cmd = 'sqlcmd -S %s,%s -U %s -P %s -d %s -i "%s"' % \
              (my.server, my.port, my.user, my.password, database, data_path)

        print "Importing data ..."
        print cmd
        os.system(cmd)


    def get_table_info(my, db_resource):

        key = "DatabaseImpl:table_info"
        cache_dict = Container.get(key)
        if cache_dict == None:
            cache_dict = {}
            Container.put(key, cache_dict)

        from sql import DbContainer, Sql
        if isinstance(db_resource, Sql):
            key2 = "%s" % (db_resource.get_db_resource())
        else:
            key2 = "%s" % (db_resource)
        cache = cache_dict.get(key2)
        if cache != None:
            return cache


        from sql import Select, DbContainer
        sql = DbContainer.get(db_resource)

        statement = 'SELECT * from sys.Tables'
        results = sql.do_query(statement)
        info = {}
        for result in results:
            table = result[0]
            info[table] = table

        Container.put(key2, info)

        return info


    def get_column_info(cls, db_resource, table, use_cache=True):
        '''SQLServer: get column info like data types, is_nullable in a dict'''

        from sql import DbContainer, Sql
        if isinstance(db_resource, Sql):
            prefix = "%s" % db_resource.get_db_resource()
        else:
            prefix = "%s" % db_resource
            
        if use_cache:
            # use global cache
            if prefix.endswith(':sthpw'):
                from pyasm.biz import CacheContainer
                cache = CacheContainer.get("sthpw_column_info")
                if cache:
                    dict = cache.get_value_by_key("data", table)
                    if dict != None:
                        return dict

        key2 = "%s:%s" % (prefix, table)
      
        key = "DatabaseImpl:column_info"
        cache_dict = Container.get(key)
        if cache_dict == None:
            cache_dict = {}
            Container.put(key, cache_dict)
        
        if use_cache:    
            cache = cache_dict.get(key2)
            if cache != None:
                return cache

        cache = {}
        cache_dict[key2] = cache


        # get directly from the database
        from sql import DbContainer
        # get directly from the database
        if isinstance(db_resource, Sql):
            sql = db_resource
        else:
            sql = DbContainer.get(db_resource)
        query = "select column_name, data_type, \
            is_nullable, character_maximum_length from \
            information_schema.columns where table_name = '%s' \
            " % table

            #order by ordinal_position" % table
        result = sql.do_query(query)

        # convert to the proper data structure
        if len(result) > 0:
            for k in range(len(result)):
                name = result[k][0]
                data_type = result[k][1]
                is_nullable = result[k][2] == 'YES'
                size = result[k][3]
                if data_type in ['character varying', 'varchar','nvarchar']:
                    data_type = 'varchar'
                elif data_type in ['integer', 'smallint', 'int']:
                    data_type = 'integer'
                elif data_type in ['text','ntext']:
                    data_type = "text"
                elif data_type == 'boolean':
                    data_type = "boolean"
                elif data_type == 'bit':
                    data_type = "boolean"

                elif data_type == 'uniqueidentifier':
                    data_type = "uniqueidentifier"
                elif data_type.startswith('int identity'):
                    data_type = "serial"

                elif data_type.startswith("datetime"):
                    data_type = "timestamp"
                # for time with/wihtout time zone
                elif data_type.startswith("time "):
                    data_type = "time"

                info_dict = {'data_type': data_type, 'nullable': is_nullable, 'size': size}
                cache[name] = info_dict


        return cache

    get_column_info = classmethod(get_column_info)

  

    def is_column_sortable(my, db_resource, table, column):
        sql = DbContainer.get(my.db_resource)
        columns = sql.get_columns(table)
        if column in columns:
            return True
        else:
            return False
 

    def get_column_types(my, database, table):
        ''' get column data types. Note: can potentially get 
            character_maximum_length, numeric_precision, and udt_name '''
    
        info = my.get_column_info(database, table) 
        column_dict = {}
        for key, value in info.items():
            column_dict[key] = value.get('data_type')
        return column_dict

    def get_column_nullables(my, database, table):
        ''' get column data nullables '''
    
        info = my.get_column_info(database, table) 
        column_dict = {}
        for key, value in info.items():
            column_dict[key] = value.get('nullable')
        return column_dict 

    def get_columns(cls, db_resource, table):
        '''SQLServer get ordered column names'''
        
        from sql import DbContainer
        sql = DbContainer.get(db_resource)
<commit_msg>BRANCH 4.1:    moved is_column_sortable method to the BaseSQLDatabaseImpl instead of SQLServerImpl    fixed a bug in its reference to my.db_resource<commit_after>###########################################################
#
# Copyright (c) 2005, Southpaw Technology
#                     All Rights Reserved
#
# PROPRIETARY INFORMATION.  This software is proprietary to
# Southpaw Technology, and is not to be reproduced, transmitted,
# or disclosed in any way without written permission.
#
#
#

__all__ = ['DatabaseImpl', 'PostgresImpl', 'OracleImpl', 'SqliteImpl', 'MySQLImpl', 'SQLServerImpl', 'TacticImpl']

import os, sys, types, re
import subprocess
import datetime

from pyasm.common import Environment, SetupException, Config, Container, TacticException

class DatabaseImplException(TacticException):
    pass



class DatabaseImplInterface(object):


    def get_columns(cls, db_resource, table):
        pass

    def get_column_info(cls, db_resource, table, use_cache=True):
        pass

    def is_column_sortable(my, db_resource, table, column):
        pass

    def get_id_col(db_resource, search_type):
        pass

    def get_code_col(db_resource, search_type):
        pass


    def get_page(my, limit=None, offset=0):
        pass

    def get_table_info(my, db_resource):
        pass

    def table_exists(my, db_resource, table):
        pass

    def execute_query(my, sql, select):
        pass

    def execute_update(my, sql, update):
        pass




class DatabaseImpl(DatabaseImplInterface):
    '''Provides an abstraction layer for the various databases'''

    def get_database_type(my):
        return None


    def get_version(my):
        return (0,0,0)


    def get(vendor=None):
        '''Get the current database implementation'''
        from sql import Sql
        if not vendor:
            vendor = Sql.get_default_database_type()
            return DatabaseImpl.get(vendor)

        if vendor == "PostgreSQL":
            return PostgresImpl()
        elif vendor == "Sqlite":
            return SqliteImpl()
        elif vendor == "MySQL":
            return MySQLImpl()
        elif vendor == "SQLServer":
            return SQLServerImpl()
        elif vendor == "Oracle":
            return OracleImpl()

        # TEST
        elif vendor == "MongoDb":
            from mongodb import MongoDbImpl
            return MongoDbImpl()
        elif vendor == "TACTIC":
            return TacticImpl()
        raise DatabaseImplException("Vendor [%s] not supported" % vendor)

    get = staticmethod(get)


    def preprocess_sql(my, data, unquoted_cols):
        pass

    def postprocess_sql(my, statement):
        return statement

    def process_value(my, name, value, column_type="varchar"):
        '''process a database value based on column type.

        @params:
            value: current input value
            column_type: the database column type

        @return
            dict:
                value: the new value
                quoted: True|False - determines whether the value is quoted or not
        '''
        return None




    def get_id_col(my, db_resource, search_type):
        from pyasm.search import SearchType
        search_type = SearchType.get(search_type)
        id_col = search_type.get_id_col()
        return id_col


    def get_code_col(my, db_resource, search_type):
        from pyasm.search import SearchType
        search_type = SearchType.get(search_type)
        code_col = search_type.get_code_col()
        return code_col




    #
    # Schema functions
    #
    def get_schema_dir(my):
        # get the schema directory for the appropriate database type
        install_dir = Environment.get_install_dir()
        schema_dir = "%s/src/pyasm/search/upgrade/%s" % (install_dir, my.get_database_type().lower() )
        if not os.path.exists(schema_dir):
            raise DatabaseImplException("Schema '%s' does not exist" % schema_dir)

        return schema_dir


    def import_sql_file(my, db_resource, path):

        from pyasm.search import DbResource, DbContainer
        if isinstance(db_resource, basestring):
            database = db_resource
        else:
            database = db_resource.get_database()

        sql = DbContainer.get(db_resource)

        f = open(path, 'r')
        data = f.read()
        f.close()

        print "Importing sql to database [%s]..." % (database)
        print "   using path [%s]" % (path)

        cmds = data.split(";")
        for cmd in cmds:
            cmd = cmd.strip()
            sql.do_update(cmd)

        my.clear_table_cache()
        sql = DbContainer.get(db_resource)



    def import_schema(my, db_resource, type):
        '''import the schema of certain type to the given database'''

        # import the necessary schema
        types = ['config', type]
        
        # sthpw schema is composed of 2 files
        if db_resource == 'sthpw':
            types.insert(0, 'bootstrap')
        for schema_type in types:
            schema_dir = my.get_schema_dir()
            schema_path = "%s/%s_schema.sql" % (schema_dir, schema_type)
            if not os.path.exists(schema_path):
                Environment.add_warning("Schema does not exist", "Schema '%s' does not exist" % schema_path)
                continue

            my.import_sql_file(db_resource, schema_path)




    def import_default_data(my, db_resource, type):
        '''import the data of certain type to the given database'''

        # import the necessary schema
        schema_dir = my.get_schema_dir()
        data_path = "%s/%s_data.sql" % (schema_dir, type)
        data_path = os.path.normpath(data_path)
        if not os.path.exists(data_path):
            #Environment.add_warning("Default data does not exist", "Data '%s' does not exist" % data_path)
            return

        my.import_sql_file(db_resource, data_path)



    #
    # Database methods for base database implementation
    #
    def has_sequences(my):
        raise DatabaseImplException("TACTIC database implementation for current database vendor does not have method has_sequences() defined.")

    def get_table_info(my, database):
        raise DatabaseImplException("Must override 'get_table_info' for [%s]" % my.vendor)


    def database_exists(my, database, host=None, port=None):
        '''@param: 
            database - if string, it's just a database name (old)
                       if DbResource, it could contain the host already
            host - can be localhost or a different server
            port - port number that the database is listen on'''
        try:
            db_resource = database
            #from pyasm.search import DbContainer, DbResource
            from sql import DbContainer, DbResource
            if isinstance(database, basestring):
                if host == None:
                    vendor = Config.get_value("database", "vendor")
                    host = Config.get_value("database", "server")
                    port = Config.get_value("database", "port")
                else:
                    vendor = my.get_database_type()
                db_resource = DbResource(database=database, host=host, vendor=vendor, port=port)
            
            cached = Container.get("Sql:database_exists:%s"%db_resource.get_key()) 
            if cached != None:
                return cached

            sql = DbContainer.get(db_resource, connect=True)
            if sql:
                if not sql.get_connection():
                    sql.connect()
            else:
                return False
            # cache it for repeated use
            Container.put("Sql:database_exists:%s"%db_resource.get_key(), True) 
        except Exception, e:
            #print "Error: ", str(e)
            return False
        else:
            return True


    def clear_table_cache(cls):
        # this relies on the __str__ method of db_resource
        key = "DatabaseImpl:table_exists"
        Container.remove(key)
        key = "DatabaseImpl:table_info"
        Container.remove(key)
        key = "DatabaseImpl:column_info"
        Container.remove(key)
    clear_table_cache = classmethod(clear_table_cache)


    def table_exists(my, db_resource, table):

        key = "DatabaseImpl:table_exists"

        cached_dict = Container.get(key)
        if cached_dict == None:
            cached_dict = {}
            Container.put(key, cached_dict)

        # this relies on the __str__ method of db_resource
        key2 = "%s:%s" % (db_resource, table)
        cached = cached_dict.get(key2)
        if cached != None:
            return cached

        table_info = my.get_table_info(db_resource)
        if table_info.has_key(table):
            exists = True
        else:
            exists = False

        cached_dict[key2] = exists

        return exists


    def get_column_types(my, db_resource, table):
        return {}



    #
    # Save point methods
    #
    def has_savepoint(my):
        return True

    def set_savepoint(my, name='save_pt'):
        '''set a savepoint'''
        if not my.has_savepoint():
            return None
        return "SAVEPOINT %s" %name

    def rollback_savepoint(my, name='save_pt', release=False):
        if not my.has_savepoint():
            return None
        stmt = "ROLLBACK TO SAVEPOINT %s"%name
        return stmt

    def release_savepoint(my, name='save_pt'):
        if not my.has_savepoint():
            return None
        stmt = "RELEASE SAVEPOINT %s"%name
        return stmt






    def get_constraints(my, database, table):
        return []




    def handle_pagination(my, statement, limit, offset):
        return statement

    def get_id_override_statement(my, table, override=False):
        return ''

    def get_constraint(my, mode, name='', columns=[], table=None):
        if not name and table:
            if mode == 'PRIMARY KEY':
                name = '%s_pkey' %table
        return 'CONSTRAINT %s %s (%s)' %(name, mode, ','.join(columns))



    def get_regex_filter(my, column, regex, op='EQI'):
        return None


    def get_text_search_filter(cls, column, keywords, column_type, table=None):
        '''default impl works with Postgres'''
        if isinstance(keywords, basestring):
            if keywords.find("|") != -1 or keywords.find("&") != -1:
                # prevent syntax error from multiple | or &
                keywords = re.sub( r'\|+', r'|', keywords)
                keywords = re.sub( r'\&+', r'&', keywords)
                value = keywords
            else:
                keywords = keywords.strip()
                keywords = keywords.replace("  ", "")
                parts = keywords.split(" ")
                value = ' | '.join(parts)

        elif type(keywords) == types.ListType:
            # remove empty strings from the list
            keywords = filter(None, keywords)
            value = ' & '.join(keywords)
        else:
            value = str(keywords)

        # explicitly set the config in case there is an index available
        # TODO: this should be configurable
        config = 'english'

        # for multiple columns
        #coalesce(title,'') || ' ' || coalesce(body,'')
        
        # avoid syntax error
        value = value.replace("'", "''")

       

        if table:
            column = '"%s"."%s"' % (table, column)
        else:
            column = '"%s"' % column

        if column_type in ['integer','serial']:
            column = "CAST(%s AS varchar(10))" %column
        else:
            # prefix matching
            value = '%s:*'%value
        
        wheres = []
        wheres.append('''to_tsvector('%s', %s)''' % (config, column) )
        wheres.append("@@")
        wheres.append("to_tsquery('%s', '%s')" % (config, value) )

        where = " ".join(wheres)
        return where

    get_text_search_filter = classmethod(get_text_search_filter)

    def get_columns(cls, db_resource, table):
        '''get ordered column names'''
         # do a dummy select to get the ordered columns
        from sql import Select, DbContainer
        sql = DbContainer.get(db_resource)

        select = Select()
        select.set_database(db_resource)
        select.add_table(table)
        select.set_limit(0)
        statement = select.get_statement()
        sql.do_query(statement)

        columns = []
        for description in sql.description:
            # convert to unicode
            value = unicode(description[0], 'utf-8')
            columns.append(value)

        return columns

    get_columns = classmethod(get_columns)




    def can_join(db_resource1, db_resource2):

        # if the hosts are difference, joins cannot happen
        host1 = db_resource1.get_host()
        host2 = db_resource2.get_host()
        if host1 != host2:
            return False

        # if the database types are differenct, joins cannot happen
        database_type1 = db_resource1.get_database_type()
        database_type2 = db_resource2.get_database_type()
        if database_type1 != database_type2:
            return False

        # if the host is the same and the database is the same, then joins
        # can happen
        database1 = db_resource1.get_database()
        database2 = db_resource2.get_database()
        if database1 == database2:
            return True

        # multi database joins are not support in Postgres or Sqlite
        if database_type1 in ["PostgreSQL", "Sqlite"]:
            return False
        if database_type2 in ["PostgreSQL", "Sqlite"]:
            return False

        # otherwise joins can happen between the two resources
        return True

    can_join = staticmethod(can_join)


    def can_search_types_join(search_type1, search_type2):
        from search import SearchType
        db_resource = SearchType.get_db_resource_by_search_type(search_type1)
        db_resource2 = SearchType.get_db_resource_by_search_type(search_type2)
        can_join = DatabaseImpl.can_join(db_resource, db_resource2)
        return can_join
    can_search_types_join = staticmethod(can_search_types_join)



    # Defines temporary column name to be used.  Only SQLServerImpl implements
    # this
    def get_temp_column_name(my):
        return ""



class BaseSQLDatabaseImpl(DatabaseImpl):
    
    def is_column_sortable(my, db_resource, table, column):
        from sql import DbContainer
        sql = DbContainer.get(db_resource)
        columns = sql.get_columns(table)
        if column in columns:
            return True
        else:
            return False



class SQLServerImpl(BaseSQLDatabaseImpl):
    '''Implementation for Microsoft SQL Server's SQL'''

    def get_database_type(my):
        return "SQLServer"

    def __init__(my):

        # FIXME: this will not work in mixed db cases because it assumes a global
        # single database
        my.server   = Config.get_value("database", "server")
        my.port     = Config.get_value("database", "port")
        my.user     = Config.get_value("database", "user")
        my.password = Config.get_value("database", "password")




    def get_version(my):
        from sql import DbContainer
        sql = DbContainer.get("sthpw")
        result = sql.do_query("select @@version")

        # result is [(u'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) \n\tApr  2 2010 15:48:46 \n\tCopyright (c) Microsoft Corporation\n\tExpress Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)\n', )]
        version_str = result[0][0]

        parts = version_str.split(" ")
        version_parts = parts[7].split(".")
        version_parts = [int(x) for x in version_parts]
        return version_parts


    def get_create_sequence(my, sequence_name):
        #return 'CREATE SEQUENCE "%s" START WITH 1 INCREMENT BY 1 NO MAXVALUE CACHE 1' % name
        # SQL Server specific implementation.
        #postfix_len = '_id_seq'.__len__()
        #table_name_len = sequence_name.__len__() - postfix_len
        #table_name = sequence_name[0:table_name_len] 
        #print '  get_create_sequence: table_name = ', table_name
        #return 'ALTER TABLE %s ADD %s INT IDENTITY(100, 5) ' % (table_name, sequence_name)
        #return 'ALTER COLUMN %s ADD %s INT IDENTITY(100, 5) ' % (table_name, sequence_name)
        return

    def get_sequence_name(my, table, database=None):
        # SQL Server specific implementation: use the ID column as the sequence.
        # OLD return "%s_id_seq" % table
        from pyasm.search import SearchType
        if isinstance(table, SearchType):
            search_type = table
            table = search_type.get_table()
        return table



    def get_page(my, limit=None, offset=0, table_input=0, already_in_where_clause=0):
        '''get the pagination sql based on limit and offset'''
        #
        # SQL Server implementation
        #
        return None

    def handle_pagination(my, statement, limit, offset):
        '''specific method to handle MS SQL Server's pagination'''

        if limit == None:
            return statement

        # SQL Server implementation
        # 
        # Example:
        # SELECT * from (SELECT TOP 100 *, 
        #        ROW_NUMBER() over (ORDER BY id) as _tmp_spt_rownum FROM
        # [tblCatalogCrossReference]  WHERE code='abc'
        #   ) tmp_spt_table 
        # WHERE tmp_spt_table._tmp_spt_rownum between (5) and (10)
        start = offset + 1
        end = start + int(limit) - 1
        # one can set the limit to be 0 to return 0 row back
        if end < start and limit > 0:
            raise DatabaseImplException('start [%s] must be larger than end [%s] for limit' %(start, end))
        #order_by = ''
        #if order_bys:
        #    order_by = "ORDER BY %s" % ", ".join( order_bys )
        tmp_col = my.get_temp_column_name()
        page = "tmp_spt_table.%s BETWEEN (%s) AND (%s)" % (tmp_col, start, end)

        statement = "SELECT * FROM ( \
                     %s ) tmp_spt_table \
                     WHERE %s" % (statement, page)
        return statement

    def get_id_override_statement(my, table, override=True):
        '''SQL Server needs to manually turn on an off the auto id generation feature'''
        if override:
            return "SET IDENTITY_INSERT %s ON" % table
        else:
            return "SET IDENTITY_INSERT %s OFF" % table




    #
    # Column methods
    #
    def get_boolean(my, not_null=False):
        parts = []
        parts.append("bit")
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_serial(my, not_null=False):
        parts = []
        # For SQL Server, replaced "serial" type with "identity".
        # create table "hi" ("colA" int, "id" int identity(1,1) primary key("id") );
        # 
        parts.append("int identity(1,1)")
        return " ".join(parts)


    def get_int(my, length=4, not_null=False):
        """
        http://technet.microsoft.com/en-us/library/cc917573.aspx

        If the integer is from 1 through 255, use tinyint.
        If the integer is from -32768 through 32767, use smallint.
        If the integer is from -2,147,483,648 through 2,147,483,647 use int.
        If you require a number with decimal places, use decimal. Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server decimal do not round).
        If you are not sure, use decimal; it most closely resembles Oracle NUMBER data type.

        """
        parts = []
        parts.append("int")
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_text(my, not_null=False):
        parts = []
        parts.append("varchar(max)")
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)


    def get_varchar(my, length=256, not_null=False):
        assert length
        if length == -1:
            length = 'max'
        parts = []
        parts.append("varchar(%s)" % length)
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_nvarchar(my, length=256, not_null=False):
        assert length
        if length == -1:
            length = 'max'
        parts = []
        parts.append("nvarchar(%s)" % length)
        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)

    def get_timestamp(my, default="now", not_null=False, timezone=False):
        # SQL Server implementation.
        parts = []
        if timezone:
            parts.append("datetimeoffset(6)")
        else:
            parts.append("datetime2(6)")

        if default:
            if default == "now":
                parts.append("DEFAULT(%s)" % my.get_timestamp_now())
            else:
                parts.append("DEFAULT(%S)" % default)

        if not_null:
            parts.append("NOT NULL")
        return " ".join(parts)


    def get_timestamp_now(my, offset=None, type=None, op='+'):
        # SQL Server implementation.

        if not type:
            type = 'day'
        
        if offset:
            # Postgres: parts.append("'%s %s'::interval" % (offset, type) )
            # Postgres: eg. now() + '10 hour'::interval

            # SQL Server: DATEADD(hour, +10, CURRENT_TIMESTAMP)
            part = "DATEADD(%s, %s%s, GETDATE())" % (type, op, offset) 
        else:
            part = "GETDATE()"
        return part


    
    #
    # Sequence methods for SQL Server
    #
    def has_sequences(my):
        return True

    def get_nextval(my, sequence):
        return '"%s".nextval' % sequence

    def get_currval(my, sequence):
        # IDENT_CURRENT returns the last identity value
        # generated for a specified table[
        return 'SELECT IDENT_CURRENT(\'' + sequence + '\')'

    def get_currval_select(my, sequence):
        # IDENT_CURRENT returns the last identity value
        # generated for a specified table[
        return 'SELECT IDENT_CURRENT(\'' + sequence + '\')'

    def get_nextval_select(my, sequence):
        # In Postgres, when a table is created, currval is undefined and nextval is 1.
        # SQL Server doesn't have a concept of nextval.
        # When the table is created, the currval *is already 1*, 
        # and so nextval becomes 2.  This poses a problem.
        # The solution is to check if
        # ident_current('table_name') = 1 then just return 1.

        cmd  = "declare @cur_id int;"
        cmd += "select @cur_id = ident_current('" + sequence + "');"
        cmd += "if @cur_id = 1"
        cmd += "  select @cur_id;"
        cmd += "else"
        cmd += "  select @cur_id + 1;"
        return cmd

    def get_setval_select(my, sequence, num):
        # Set the current identity value for the specified table.
        cmd = "DBCC CHECKIDENT ('" + sequence + "', RESEED, " + str(num) + ");"
        return cmd

    # Method to build and return an SQL statement that can be run to reset the ID sequence for a table to a number
    # that is one greater than the highest index found in the given table.  NOTE: this ASSUMES that there are rows
    # in the table to provide a MAX id value from.  TODO: provide handling for a table with no data rows.
    def get_reset_table_sequence_statement(my, table, database=None):

        from sql import DbContainer
        sql = DbContainer.get(database)

        query = "SELECT MAX(id) + 1 FROM %s ;" % table
        result = sql.do_query(query)
        max_id = result[0][0]

        reset_seq_sql = "ALTER SEQUENCE %s_id_seq RESTART WITH %d ;" % (table, max_id)
        return reset_seq_sql


    #
    # Regular Expressions
    # 
    def get_regex_filter(my, column, regex, op='EQI'):
        if op == 'EQI':
            op = 'LIKE'
            column = 'lower(CAST("%s" AS varchar(max)))' %column
            regex = "lower('%%%s%%')"%regex
        elif op == 'EQ':
            op = 'LIKE'
            regex = "'%%%s%%'" %regex
        elif op == 'NEQI':
            op = 'NOT LIKE'
            regex = "lower('%%%s%%')"%regex
        elif op == 'NEQ':
            op = 'NOT LIKE'
            regex = "'%%%s%%'" %regex
        else:
            raise SetupException('Invalid op [%s]. Try EQ, EQI, NEQ, or NEQI' %op)
            
        return "%s %s %s" %(column, op, regex)


    def get_text_search_filter(cls, column, keywords, column_type, table=None):
        '''When Full Text Index is created in the db for the table, it works with SQLServer 2008 and above'''
        if isinstance(keywords, basestring):
            value = keywords
        elif type(keywords) == types.ListType:
            # remove empty strings from the list
            keywords = filter(None, keywords)
            value = " ".join(keywords)
        else:
            value = str(keywords)

        
        # avoid syntax error
        value = value.replace("'", "''")

       

        if table:
            column = '"%s"."%s"' % (table, column)
        else:
            column = '"%s"' % column

        """
        if column_type in ['integer','serial']:
            column = "CAST(%s AS varchar(10))" %column
       
        """
        wheres = []
        # use FREETEXT() or CONTAINS(), CONTAINS() takes OR AND operator
        wheres.append("FREETEXT(%s, '%s')" % (column, value) )

        where = " ".join(wheres)
        return where

    get_text_search_filter = classmethod(get_text_search_filter)


    #
    # Type process methods
    #
    def process_value(my, name, value, column_type="varchar"):

        if column_type in ['timestamp','datetime']:
            quoted = False
            lower_value = value.lower()
            if value == "NOW":
                value = "getdate()"
                #return {"value": value, "quoted": quoted}
            # FIXME: this is implemented this way because set_value 
            # can be called twice.  This method should called from commit
            # and not set_value
            elif isinstance(value, datetime.datetime):
                pass
            elif not value:
                pass
            elif not lower_value.startswith("convert") and not lower_value.startswith("getdate") and not lower_value.startswith("dateadd") :
                if value == 'NULL':
                    pass
                else:
                    value = "convert(datetime2, '%s', 0)" % value
                
            return {"value": value, "quoted": quoted}
          


        elif column_type in ['uniqueidentifier'] and value == "NEWID":
            value = "newid()"
            quoted = False
            return {"value": value, "quoted": quoted}




    #
    # Database methods
    # 
    def _get_db_info(my, db_resource):
        ''' get the database info from the config file'''
        if isinstance(db_resource, DbResource):
            host = db_resource.get_host()
            user = db_resource.get_user()
            password = db_resource.get_password()
            port = db_resource.get_port()
        else:
            host = Config.get_value("database", "server")
            user = Config.get_value("database", "user")
            password = Config.get_value("database", "password")
            port = Config.get_value("database", "port")


        parts = []
        host_str ="-S %s" % host 
        if port:
            host_str = '%s,%s'%(host_str, port)
        parts.append(host_str)
        parts.append("-U %s" % user)
        parts.append("-P %s" % password)

        
        return " ".join(parts)


    def create_database(my, database):
        '''create a database.  This is done by a system command'''
        
        # if the database already exists, do nothing
        if my.database_exists(database):
            return

        # TODO: Retrieve server, username, password from TACTIC config file.
        # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -Q "create database test1"
        # note: The database we are connecting to must be 'sthpw'
        create_SQL_arg = '"CREATE DATABASE ' + database + '"'
        create = 'sqlcmd -S %s,%s -U %s -P %s -Q %s' % \
                 (my.server, my.port, my.user, my.password, create_SQL_arg)
        cmd = os.popen(create)
        result = cmd.readlines()
        if not result:
            print "No output from sql command to create db [%s], assumed success" % database
            cmd.close()
            return
            #raise Exception("Error creating database '%s'" % database)
        cmd.close()

        if result[0].find("already exists") != -1:
            print "already exists"
            print "  Try deleting C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\<database_name>.mdf"
        else:
            print "no returned result from database creation (sqlcmd)"


    def drop_database(my, database):
        '''remove a database in SQL Server. Note this is a very dangerous
        operation.  Use with care.'''
        # if the database does not exist, do nothing
        #if not database_exists(database):
        #    return

  
        # TODO: Retrieve server, username, password from TACTIC config file.
        # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -Q "dropdatabase test1"
        # note: The database we are connecting to must be 'sthpw'
        drop_SQL_arg = '"DROP DATABASE %s"' %database
        create = 'sqlcmd -S %s,%s -U %s -P %s -Q %s' % \
                 (my.server, my.port, my.user, my.password, drop_SQL_arg)
        cmd = os.popen(create)
        result = cmd.readlines()
        if not result:
            print "No output from sql command to drop db [%s], assumed success" % database
            cmd.close()
            return
        else:
            print result
        cmd.close()


    def get_modify_column(my, table, column, type, not_null=None):
        ''' get the statement for setting the column type '''
        # this may not return the type exacty like before like varchar is in place of
        # varchar(256) due to the column type returned from the sql impl
        statements = []
        statements.append('ALTER TABLE "%s" ALTER COLUMN "%s" %s' \
            % (table,column,type))
        if not_null == None:
            return statements
        if not_null:
            # In order to set the column to disallow NULL values,
            # we must first set any current NULL values to the empty string ''.
            statements.append('UPDATE "%s" SET "%s"=\'\' WHERE %s IS NULL' \
            % (table,column, column))

            # Now that any existing NULL values for that column are set to the empty string,
            # proceed to alter the column so that it disallows NULL values.
            statements.append('ALTER TABLE "%s" ALTER COLUMN "%s" %s NOT NULL' \
            % (table,column,type))
        else:
            statements.append('ALTER TABLE "%s" ALTER COLUMN "%s" %s' \
                % (table, column, type))
        return statements



    def import_schema(my, db_resource, type):
        '''import the schema of certain type to the given database'''


        # DEPRECATED: this shouldn't be necessary anymore as the base class
        # should be general enough

        from pyasm.search import DbResource, DbContainer
        if isinstance(db_resource, basestring):
            database = db_resource
        else:
            database = db_resource.get_database()

        sql = DbContainer.get(db_resource)


        types = ['config', type]
        for schema_type in types:
            schema_dir = my.get_schema_dir()
            schema_path = "%s/%s_schema.sql" % (schema_dir, schema_type)
            schema_path = os.path.normpath(schema_path)
            if not os.path.exists(schema_path):
                Environment.add_warning("Schema does not exist", "Schema '%s' does not exist" % schema_path)
                return
                #raise Exception("Schema '%s' does not exist" % schema_path)

            # cmd = 'psql -q %s %s < "%s"' % (my._get_db_info(database), database, schema_path)
            # TODO: Retrieve server, username, password from TACTIC config file.
            # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -i c:/schema.py
            cmd = 'sqlcmd -S %s,%s -U %s -P %s -d %s -i "%s"' % \
                  (my.server, my.port, my.user, my.password, database, schema_path)

           
            print "Importing schema ..."
            print cmd
            os.system(cmd)
            #program = subprocess.call(cmd, shell=True)
            #print "FINSIHED importing schema"


    def import_default_data(my, db_resource, type):
        '''import the data of certain type to the given database'''

        from sql import DbResource, DbContainer
        if isinstance(db_resource, DbResource):
            database = db_resource.get_database()
        else:
            database = db_resource

        # import the necessary schema
        schema_dir = my.get_schema_dir()
        data_path = "%s/%s_data.sql" % (schema_dir, type)
        data_path = os.path.normpath(data_path)
        if not os.path.exists(data_path):
            #Environment.add_warning("Default data does not exist", "Data '%s' does not exist" % data_path)
            return

        #cmd = 'psql -q %s %s < "%s"' % (my._get_db_info(database), database, data_path)
        # TODO: Retrieve server, username, password from TACTIC config file.
        # eg.  sqlcmd -S localhost -U tactic -P south123paw -d sthpw -i c:/schema.py
        cmd = 'sqlcmd -S %s,%s -U %s -P %s -d %s -i "%s"' % \
              (my.server, my.port, my.user, my.password, database, data_path)

        print "Importing data ..."
        print cmd
        os.system(cmd)


    def get_table_info(my, db_resource):

        key = "DatabaseImpl:table_info"
        cache_dict = Container.get(key)
        if cache_dict == None:
            cache_dict = {}
            Container.put(key, cache_dict)

        from sql import DbContainer, Sql
        if isinstance(db_resource, Sql):
            key2 = "%s" % (db_resource.get_db_resource())
        else:
            key2 = "%s" % (db_resource)
        cache = cache_dict.get(key2)
        if cache != None:
            return cache


        from sql import Select, DbContainer
        sql = DbContainer.get(db_resource)

        statement = 'SELECT * from sys.Tables'
        results = sql.do_query(statement)
        info = {}
        for result in results:
            table = result[0]
            info[table] = table

        Container.put(key2, info)

        return info


    def get_column_info(cls, db_resource, table, use_cache=True):
        '''SQLServer: get column info like data types, is_nullable in a dict'''

        from sql import DbContainer, Sql
        if isinstance(db_resource, Sql):
            prefix = "%s" % db_resource.get_db_resource()
        else:
            prefix = "%s" % db_resource
            
        if use_cache:
            # use global cache
            if prefix.endswith(':sthpw'):
                from pyasm.biz import CacheContainer
                cache = CacheContainer.get("sthpw_column_info")
                if cache:
                    dict = cache.get_value_by_key("data", table)
                    if dict != None:
                        return dict

        key2 = "%s:%s" % (prefix, table)
      
        key = "DatabaseImpl:column_info"
        cache_dict = Container.get(key)
        if cache_dict == None:
            cache_dict = {}
            Container.put(key, cache_dict)
        
        if use_cache:    
            cache = cache_dict.get(key2)
            if cache != None:
                return cache

        cache = {}
        cache_dict[key2] = cache


        # get directly from the database
        from sql import DbContainer
        # get directly from the database
        if isinstance(db_resource, Sql):
            sql = db_resource
        else:
            sql = DbContainer.get(db_resource)
        query = "select column_name, data_type, \
            is_nullable, character_maximum_length from \
            information_schema.columns where table_name = '%s' \
            " % table

            #order by ordinal_position" % table
        result = sql.do_query(query)

        # convert to the proper data structure
        if len(result) > 0:
            for k in range(len(result)):
                name = result[k][0]
                data_type = result[k][1]
                is_nullable = result[k][2] == 'YES'
                size = result[k][3]
                if data_type in ['character varying', 'varchar','nvarchar']:
                    data_type = 'varchar'
                elif data_type in ['integer', 'smallint', 'int']:
                    data_type = 'integer'
                elif data_type in ['text','ntext']:
                    data_type = "text"
                elif data_type == 'boolean':
                    data_type = "boolean"
                elif data_type == 'bit':
                    data_type = "boolean"

                elif data_type == 'uniqueidentifier':
                    data_type = "uniqueidentifier"
                elif data_type.startswith('int identity'):
                    data_type = "serial"

                elif data_type.startswith("datetime"):
                    data_type = "timestamp"
                # for time with/wihtout time zone
                elif data_type.startswith("time "):
                    data_type = "time"

                info_dict = {'data_type': data_type, 'nullable': is_nullable, 'size': size}
                cache[name] = info_dict


        return cache

    get_column_info = classmethod(get_column_info)

  

   
 

    def get_column_types(my, database, table):
        ''' get column data types. Note: can potentially get 
            character_maximum_length, numeric_precision, and udt_name '''
    
        info = my.get_column_info(database, table) 
        column_dict = {}
        for key, value in info.items():
            column_dict[key] = value.get('data_type')
        return column_dict

    def get_column_nullables(my, database, table):
        ''' get column data nullables '''
    
        info = my.get_column_info(database, table) 
        column_dict = {}
        for key, value in info.items():
            column_dict[key] = value.get('nullable')
        return column_dict 

    def get_columns(cls, db_resource, table):
        '''SQLServer get ordered column names'''
        
        from sql import DbContainer
        sql = DbContainer.get(db_resource)

{'fraction_non_alphanumeric': 0.10243750324827192, 'fraction_numerical': 0.005976820331583597, 'mean_word_length': 1.9996492322082782, 'pattern_counts': {'":': 32, '<': 13, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5375447', 'n_tokens_mistral': 22458, 'n_tokens_neox': 21248, 'n_words': 6465}