Django

Code

Changeset 4064

Show
Ignore:
Timestamp:
11/10/06 11:16:45 (2 years ago)
Author:
bouldersprinters
Message:

boulder-oracle-sprint: Changed Oracle CLOB to NCLOB for i18n.
Fixed Oracle backend's get_date_trunc_sql() function.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • django/branches/boulder-oracle-sprint/django/core/management.py

    r4019 r4064  
    204204        final_output.append(sequence_statement) 
    205205        trigger_statement = '' + \ 
    206             'CREATE OR REPLACE trigger %s\n'    % truncate_name('%s_tr' % opts.db_table, backend.get_max_name_length()) + \ 
    207             '  before insert on %s\n'           % backend.quote_name(opts.db_table) + \ 
    208             '    for each row\n'  + \ 
    209             '      when (new.id is NULL)\n' + \ 
    210             '        begin\n' + \ 
    211             '         select %s.NEXTVAL into :new.id from DUAL;\n' % sequence_name + \ 
    212             '      end;\n' 
     206            'CREATE OR REPLACE TRIGGER %s\n'    % truncate_name('%s_tr' % opts.db_table, backend.get_max_name_length()) + \ 
     207            '  BEFORE INSERT ON %s\n'           % backend.quote_name(opts.db_table) + \ 
     208            '  FOR EACH ROW\n'  + \ 
     209            '  WHEN (new.id IS NULL)\n' + \ 
     210            '    BEGIN\n' + \ 
     211            '      SELECT %s.nextval INTO :new.id FROM dual;\n' % sequence_name + \ 
     212            '    END;\n' 
    213213        final_output.append(trigger_statement) 
    214  
    215214 
    216215    return final_output, pending_references 
    …… 
    284283                final_output.append(sequence_statement) 
    285284                trigger_statement = '' + \ 
    286                 'CREATE OR REPLACE trigger %s\n'    % truncate_name('%s_tr' % m_table, backend.get_max_name_length()) + \ 
    287                 '  before insert on %s\n'           % backend.quote_name(m_table) + \ 
    288                 '    for each row\n'  + \ 
    289                 '      when (new.id is NULL)\n' + \ 
    290                 '        begin\n' + \ 
    291                 '         select %s.NEXTVAL into :new.id from DUAL;\n' % sequence_name + \ 
    292                 '      end;\n' 
     285                    'CREATE OR REPLACE TRIGGER %s\n'    % truncate_name('%s_tr' % m_table, backend.get_max_name_length()) + \ 
     286                    '  BEFORE INSERT ON %s\n'           % backend.quote_name(m_table) + \ 
     287                    '  FOR EACH ROW\n'  + \ 
     288                    '  WHEN (new.id IS NULL)\n' + \ 
     289                    '    BEGIN\n' + \ 
     290                    '      SELECT %s.nextval INTO :new.id FROM dual;\n' % sequence_name + \ 
     291                    '    END;\n' 
    293292                final_output.append(trigger_statement) 
    294293    return final_output 

  • django/branches/boulder-oracle-sprint/django/db/backends/mysql/base.py

    r3986 r4064  
    181181    return "DEFAULT" 
    182182 
    183 def get_max_name_length(): 
    184     return 64 
    185  
    186183OPERATOR_MAPPING = { 
    187184    'exact': '= %s', 

  • django/branches/boulder-oracle-sprint/django/db/backends/oracle/base.py

    r3981 r4064  
    4141                self.connection = Database.connect(conn_string) 
    4242        # set oracle date to ansi date format 
    43         cursor = self.connection.cursor() 
    44         cursor.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'") 
     43        cursor = self.connection.cursor() 
     44        cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'") 
    4545        cursor.close() 
    4646        return FormatStylePlaceholderCursor(self.connection) 
    …… 
    6969    This fixes it -- but note that if you want to use a literal "%s" in a query, 
    7070    you'll need to use "%%s". 
    71     """ 
    72     def execute(self, query, params=None): 
     71    """     
     72    def _rewrite_args(self, query, params=None): 
    7373        if params is None:  
    7474            params = [] 
    75         args = [(':arg%s' % i) for i in range(len(params))] 
     75        args = [(':arg%d' % i) for i in range(len(params))] 
    7676        query = query % tuple(args) 
    77          
    78         # cx can not execute the query with the closing ';'  
     77        # cx_Oracle cannot execute a query with the closing ';'  
    7978        if query.endswith(';'): 
    8079            query = query[:-1] 
     80        return query, params 
     81     
     82    def execute(self, query, params=None): 
     83        query, params = self._rewrite_args(query, params) 
     84        self.arraysize = 200 
    8185        return Database.Cursor.execute(self, query, params) 
    8286 
    8387    def executemany(self, query, params=None): 
    84         if params is None: params = [] 
    85         query = self.convert_arguments(query, len(params[0])) 
    86         # cx can not execute the query with the closing ';'  
    87         if query.endswith(';') : 
    88             query = query[0:len(query)-1] 
     88        query, params = self._rewrite_args(query, params) 
     89        self.arraysize = 200 
    8990        return Database.Cursor.executemany(self, query, params) 
    9091 
     92     
    9193def quote_name(name): 
    92     if name.startswith('"') and name.endswith('"'): 
    93         return name # Quoting once is enough. 
    94          
    95     # Oracle requires that quoted names be uppercase. 
    96     return '"%s"' % name.upper() 
     94    # Oracle requires that quoted names be uppercase.     
     95    name = name.upper() 
     96    if not name.startswith('"') and not name.endswith('"'): 
     97        name = '"%s"' % util.truncate_name(name.upper(), get_max_name_length()) 
     98    return name 
    9799 
    98100dictfetchone = util.dictfetchone 
    …… 
    101103 
    102104def get_last_insert_id(cursor, table_name, pk_name): 
    103     query = "SELECT %s_sq.currval from dual" % table_name 
    104     cursor.execute(query) 
     105    cursor.execute('SELECT %s_sq.currval FROM dual' % table_name) 
    105106    return cursor.fetchone()[0] 
    106107 
    107108def get_date_extract_sql(lookup_type, table_name): 
    108109    # lookup_type is 'year', 'month', 'day' 
    109     # http://www.psoug.org/reference/date_func.html 
     110    # http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#1017163 
    110111    return "EXTRACT(%s FROM %s)" % (lookup_type, table_name) 
    111112 
    112113def get_date_trunc_sql(lookup_type, field_name): 
    113     return "EXTRACT(%s FROM TRUNC(%s))" % (lookup_type, field_name) 
     114    # lookup_type is 'year', 'month', 'day' 
     115    # Oracle uses TRUNC() for both dates and numbers. 
     116    # http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions155a.htm#SQLRF06151 
     117    if lookup_type == 'day': 
     118        sql = 'TRUNC(%s)' % (field_name,) 
     119    else: 
     120        sql = "TRUNC(%s, '%s')" % (field_name, lookup_type) 
     121    return sql 
    114122 
    115123def get_limit_offset_sql(limit, offset=None): 
    116124    # Limits and offset are too complicated to be handled here. 
    117     # Instead, they are handled in django/db/query.py. 
    118     pass 
     125    # Instead, they are handled in django/db/backends/oracle/query.py. 
     126    raise NotImplementedError 
    119127 
    120128def get_random_function_sql(): 

  • django/branches/boulder-oracle-sprint/django/db/backends/oracle/creation.py

    r4014 r4064  
    1 import sys 
     1import sys, time 
    22 
     3# This dictionary maps Field objects to their associated Oracle column 
     4# types, as strings. Column-type strings can contain format strings; they'll 
     5# be interpolated against the values of Field.__dict__ before being output. 
     6# If a column type is set to None, it won't be included in the output. 
    37DATA_TYPES = { 
    4     'AutoField':                    'number(11)', 
    5     'BooleanField':                 'number(1) CHECK (%(column)s IN (0,1))', 
    6     'CharField':                    'varchar2(%(maxlength)s)', 
    7     'CommaSeparatedIntegerField':   'varchar2(%(maxlength)s)', 
    8     'DateField':                    'date', 
    9     'DateTimeField':                'timestamp with time zone', 
    10     'FileField':                    'varchar2(100)', 
    11     'FilePathField':                'varchar2(100)', 
    12     'FloatField':                   'number(%(max_digits)s, %(decimal_places)s)', 
    13     'ImageField':                   'varchar2(100)', 
    14     'IntegerField':                 'number(11)', 
    15     'IPAddressField':               'char(15)', 
     8    'AutoField':                    'NUMBER(11)', 
     9    'BooleanField':                 'NUMBER(1) CHECK (%(column)s IN (0,1))', 
     10    'CharField':                    'VARCHAR2(%(maxlength)s)', 
     11    'CommaSeparatedIntegerField':   'VARCHAR2(%(maxlength)s)', 
     12    'DateField':                    'DATE', 
     13    'DateTimeField':                'TIMESTAMP WITH TIME ZONE', 
     14    'FileField':                    'VARCHAR2(100)', 
     15    'FilePathField':                'VARCHAR2(100)', 
     16    'FloatField':                   'NUMBER(%(max_digits)s, %(decimal_places)s)', 
     17    'ImageField':                   'VARCHAR2(100)', 
     18    'IntegerField':                 'NUMBER(11)', 
     19    'IPAddressField':               'CHAR(15)', 
    1620    'ManyToManyField':              None, 
    17     'NullBooleanField':             'number(1) CHECK ((%(column)s IN (0,1)) OR (%(column)s IS NULL))', 
    18     'OneToOneField':                'number(11)', 
    19     'PhoneNumberField':             'varchar2(20)', 
    20     'PositiveIntegerField':         'number(11) CHECK (%(column)s >= 1)', 
    21     'PositiveSmallIntegerField':    'number(11) CHECK (%(column)s >= 1)', 
    22     'SlugField':                    'varchar2(50)', 
    23     'SmallIntegerField':            'number(11)', 
    24     'TextField':                    'clob', 
    25     'TimeField':                    'timestamp', 
    26     'URLField':                     'varchar2(200)', 
    27     'USStateField':                 'char(2)', 
     21    'NullBooleanField':             'NUMBER(1) CHECK ((%(column)s IN (0,1)) OR (%(column)s IS NULL))', 
     22    'OneToOneField':                'NUMBER(11)', 
     23    'PhoneNumberField':             'VARCHAR2(20)', 
     24    'PositiveIntegerField':         'NUMBER(11) CHECK (%(column)s >= 1)', 
     25    'PositiveSmallIntegerField':    'NUMBER(11) CHECK (%(column)s >= 1)', 
     26    'SlugField':                    'VARCHAR2(50)', 
     27    'SmallIntegerField':            'NUMBER(11)', 
     28    'TextField':                    'NCLOB', 
     29    'TimeField':                    'TIMESTAMP', 
     30    'URLField':                     'VARCHAR2(200)', 
     31    'USStateField':                 'CHAR(2)', 
    2832} 
    2933 
    3034TEST_DATABASE_PREFIX = 'test_' 
    3135PASSWORD = 'Im_a_lumberjack' 
     36OLD_DATABASE_USER = None 
     37OLD_DATABASE_PASSWORD = None 
    3238 
    3339def create_test_db(settings, connection, backend, verbosity=1, autoclobber=False): 
    …… 
    4753            try: 
    4854                if verbosity >= 1: 
    49                     print "Destroying old test database..."                 
     55                    print "Destroying old test database..." 
    5056                _destroy_test_db(cursor, TEST_DATABASE_NAME, verbosity) 
    5157                if verbosity >= 1: 
    …… 
    6773    cursor = connection.cursor() 
    6874         
    69 def destroy_test_db(settings, connection, old_database_name, verbosity=1): 
     75def destroy_test_db(settings, connection, backend, old_database_name, verbosity=1): 
    7076    if verbosity >= 1: 
    7177        print "Destroying test database..." 
    …… 
    7480    TEST_DATABASE_NAME = _test_database_name(settings) 
    7581    settings.DATABASE_NAME = old_database_name 
     82    #settings.DATABASE_USER = 'old_user' 
     83    #settings.DATABASE_PASSWORD = 'old_password' 
    7684 
    7785    cursor = connection.cursor() 
    …… 
    8492        print "_create_test_db(): dbname = %s" % dbname 
    8593    statements = [ 
    86         """create tablespace %(user)s 
    87            datafile '%(user)s.dbf' size 10M autoextend on next 10M  maxsize 20M 
     94        """CREATE TABLESPACE %(user)s 
     95           DATAFILE '%(user)s.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 20M 
    8896        """, 
    89         """create temporary tablespace %(user)s_temp 
    90            tempfile '%(user)s_temp.dbf' size 10M autoextend on next 10M  maxsize 20M 
     97        """CREATE TEMPORARY TABLESPACE %(user)s_temp 
     98           TEMPFILE '%(user)s_temp.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 20M 
    9199        """, 
    92         """create user %(user)s 
    93            identified by %(password)s 
    94            default tablespace %(user)s 
    95            temporary tablespace %(user)s_temp 
     100        """CREATE USER %(user)s 
     101           IDENTIFIED BY %(password)s 
     102           DEFAULT TABLESPACE %(user)s 
     103           TEMPORARY TABLESPACE %(user)s_temp 
    96104        """, 
    97         """grant resource to %(user)s""", 
    98         """grant connect to %(user)s""", 
     105        """GRANT CONNECT, RESOURCE TO %(user)s""", 
    99106    ] 
    100107    _execute_statements(cursor, statements, dbname, verbosity) 
    …… 
    104111        print "_destroy_test_db(): dbname=%s" % dbname 
    105112    statements = [ 
    106         """drop user %(user)s cascade""", 
    107         """drop tablespace %(user)s including contents and datafiles cascade constraints""", 
    108         """drop tablespace %(user)s_temp including contents and datafiles cascade constraints""", 
     113        'DROP USER %(user)s CASCADE', 
     114        'DROP TABLESPACE %(user)s INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS', 
     115        'DROP TABLESPACE %(user)s_TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS', 
    109116        ] 
    110117    _execute_statements(cursor, statements, dbname, verbosity) 
    …… 
    120127        except Exception, err: 
    121128            sys.stderr.write("Failed (%s)\n" % (err)) 
    122             if required: 
    123                 raise 
     129            raise 
    124130 
    125131def _test_database_name(settings): 
    …… 
    129135        name = TEST_DATABASE_PREFIX + settings.DATABASE_NAME 
    130136    return name 
    131      

  • django/branches/boulder-oracle-sprint/django/db/backends/oracle/introspection.py

    r4023 r4064  
    2929SELECT ta.column_id - 1, tb.table_name, tb.column_id - 1 
    3030FROM   user_constraints, USER_CONS_COLUMNS ca, USER_CONS_COLUMNS cb, 
    31 user_tab_cols ta, user_tab_cols tb 
     31       user_tab_cols ta, user_tab_cols tb 
    3232WHERE  user_constraints.table_name = %s AND 
    33       ta.table_name = %s AND 
    34       ta.column_name = ca.column_name AND 
    35       ca.table_name = %s AND 
    36       user_constraints.constraint_name = ca.constraint_name AND 
    37       user_constraints.r_constraint_name = cb.constraint_name AND 
    38       cb.table_name = tb.table_name AND 
    39       cb.column_name = tb.column_name AND 
    40       ca.position = cb.position""", [table_name, table_name, table_name]) 
     33      ta.table_name = %s AND 
     34      ta.column_name = ca.column_name AND 
     35      ca.table_name = %s AND 
     36      user_constraints.constraint_name = ca.constraint_name AND 
     37      user_constraints.r_constraint_name = cb.constraint_name AND 
     38      cb.table_name = tb.table_name AND 
     39      cb.column_name = tb.column_name AND 
     40      ca.position = cb.position""", [table_name, table_name, table_name]) 
    4141 
    4242    relations = {} 

  • django/branches/boulder-oracle-sprint/django/db/backends/oracle/query.py

    r4022 r4064  
    6868            # so here's the logic; 
    6969            # 1. retrieve each row in turn 
    70             # 2. convert CLOBs 
     70            # 2. convert NCLOBs 
    7171 
    7272            def resolve_lobs(row): 

  • django/branches/boulder-oracle-sprint/django/db/models/query.py

    r4022 r4064  
    172172         
    173173        full_query = None 
    174         select, sql, params = self._get_sql_clause()  
     174        select, sql, params, full_query = self._get_sql_clause()  
    175175        cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)  
    176176 
    …… 
    197197        counter._limit = None 
    198198        counter._select_related = False 
    199         select, sql, params = counter._get_sql_clause()[:3] 
     199        select, sql, params, full_query = counter._get_sql_clause() 
    200200        cursor = connection.cursor() 
    201201        if self._distinct: 
    …… 
    536536 
    537537        cursor = connection.cursor() 
    538         select, sql, params = self._get_sql_clause()[:3] 
     538        select, sql, params, full_query = self._get_sql_clause() 
    539539        select = ['%s.%s' % (backend.quote_name(self.model._meta.db_table), backend.quote_name(c)) for c in columns] 
    540540        cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params) 
    …… 
    558558            self._where.append('%s.%s IS NOT NULL' % \ 
    559559                (backend.quote_name(self.model._meta.db_table), backend.quote_name(self._field.column))) 
    560         select, sql, params = self._get_sql_clause() 
    561         sql = 'SELECT %s %s GROUP BY 1 ORDER BY 1 %s' % \ 
    562             (backend.get_date_trunc_sql(self._kind, '%s.%s' % (backend.quote_name(self.model._meta.db_table), 
    563             backend.quote_name(self._field.column))), sql, self._order) 
    564         cursor = connection.cursor() 
    565         cursor.execute(sql, params) 
    566         # We have to manually run typecast_timestamp(str()) on the results, because 
    567         # MySQL doesn't automatically cast the result of date functions as datetime 
    568         # objects -- MySQL returns the values as strings, instead. 
    569         return [typecast_timestamp(str(row[0])) for row in cursor.fetchall()] 
     560        select, sql, params, full_query = self._get_sql_clause() 
     561        table_name = backend.quote_name(self.model._meta.db_table) 
     562        field_name = backend.quote_name(self._field.column) 
     563        date_trunc_sql = backend.get_date_trunc_sql(self._kind, 
     564                                                    '%s.%s' % (table_name, field_name)) 
     565        fmt = 'SELECT %s %s GROUP BY %s ORDER BY 1 %s' 
     566 
     567        if settings.DATABASE_ENGINE == 'oracle': 
     568            sql = fmt % (date_trunc_sql, sql, date_trunc_sql, self._order) 
     569            cursor = connection.cursor() 
     570            cursor.execute(sql, params) 
     571            return [row[0] for row in cursor.fetchall()]   
     572        else: 
     573            sql = fmt % (date_trunc_sql, sql, 1, self._order_by) 
     574            cursor = connection.cursor() 
     575            cursor.execute(sql, params) 
     576            # We have to manually run typecast_timestamp(str()) on the results, because 
     577            # MySQL doesn't automatically cast the result of date functions as datetime 
     578            # objects -- MySQL returns the values as strings, instead. 
     579            return [typecast_timestamp(str(row[0])) for row in cursor.fetchall()] 
    570580 
    571581    def _clone(self, klass=None, **kwargs):