o 3al@sddlZddlZddlmZddlmZddlmZmZddl m Z ddl m Z m Z ddlmZmZmZddlmZdd lmZdd lmZdd lmZmZdd lmZdd lmZddlm Z ddl!m"Z"m#Z#m$Z$Gddde Z%dS)N) lru_cache)settings) DatabaseErrorNotSupportedError)BaseDatabaseOperations) strip_quotes truncate_name) AutoFieldExistsExpressionWrapper)RawSQL) WhereNode)timezone) force_bytes force_str)cached_property)_lazy_re_compile)Database)BulkInsertMapper InsertVarOracle_datetimec s2eZdZdddddddddd ZiejddiZd Zd Zd d d edZddZ ddZ dddZ e dZ ddZddZddZddZdd Zd!d"Zdd#d$Zfd%d&Zd'd(Zd)d*Zd+d,Zd-d.Zd/d0Zd1d2Zd3d4Zed5d6Zed7d8Zd9d:Z d;d<Z!d=d>Z"d?d@Z#dAdBZ$dCdDZ%dEdFZ&ddGdHZ'dIdJZ(dKdLZ)dMdNZ*dOdPZ+dQdRZ,dSdTZ-dUdVZ.dWdXZ/dYdZZ0e1d[d\Z2d]d]d^d_d`Z3dadbZ4dcddZ5dedfZ6ddgdhZ7didjZ8dkdlZ9dmdnZ:ddodpZ;fdqdrZdwdxZ?fdydzZ@d{d|ZAd}d~ZBZCS)DatabaseOperations)lg]g])l 9V 9V)rr)rr)iayi) SmallIntegerField IntegerFieldBigIntegerFieldPositiveBigIntegerFieldPositiveSmallIntegerFieldPositiveIntegerFieldSmallAutoFieldr BigAutoField differenceMINUSa DECLARE table_value integer; seq_value integer; seq_name user_tab_identity_cols.sequence_name%%TYPE; BEGIN BEGIN SELECT sequence_name INTO seq_name FROM user_tab_identity_cols WHERE table_name = '%(table_name)s' AND column_name = '%(column_name)s'; EXCEPTION WHEN NO_DATA_FOUND THEN seq_name := '%(no_autofield_sequence_name)s'; END; SELECT NVL(MAX(%(column)s), 0) INTO table_value FROM %(table)s; SELECT NVL(last_number - cache_size, 0) INTO seq_value FROM user_sequences WHERE sequence_name = seq_name; WHILE table_value > seq_value LOOP EXECUTE IMMEDIATE 'SELECT "'||seq_name||'".nextval FROM DUAL' INTO seq_value; END LOOP; END; /zNVARCHAR2(2000)z NUMBER(11)z NUMBER(19)z NUMBER(5))r r"r! TextFieldcCdS)NzSSELECT cache_key FROM %s ORDER BY cache_key OFFSET %%s ROWS FETCH FIRST 1 ROWS ONLYselfr'r'F/usr/lib/python3/dist-packages/django/db/backends/oracle/operations.pycache_key_culling_sqlHz(DatabaseOperations.cache_key_culling_sqlcCs`|dkrd|S|dkrd|S|dkrd|S|dkr d|S|d kr(d |Sd ||fS) Nweek_dayzTO_CHAR(%s, 'D') iso_week_dayzTO_CHAR(%s - 1, 'D')weekzTO_CHAR(%s, 'IW')quarterzTO_CHAR(%s, 'Q')iso_yearzTO_CHAR(%s, 'IYYY')zEXTRACT(%s FROM %s)upper)r) lookup_type field_namer'r'r*date_extract_sqlKsz#DatabaseOperations.date_extract_sqlNcCsL|||}|dvrd||fS|dkrd|S|dkr"d|Sd|S)NyearmonthTRUNC(%s, '%s')r0TRUNC(%s, 'Q')r/TRUNC(%s, 'IW') TRUNC(%s)_convert_field_to_tzr3r)r4r5tznamer'r'r*date_trunc_sql\s z!DatabaseOperations.date_trunc_sqlz ^[\w/:+-]+$cCs8d|vr ||ddSd|vr||ddS|S)N+-)find)r)rAr'r'r*_prepare_tzname_deltans z(DatabaseOperations._prepare_tzname_deltacCsNtjr|s|S|j|std||jj|kr%d||jj||fS|S)NzInvalid time zone name: %sz8CAST((FROM_TZ(%s, '%s') AT TIME ZONE '%s') AS TIMESTAMP))rUSE_TZ _tzname_rematch ValueError connection timezone_namerFr)r5rAr'r'r*r?us    z'DatabaseOperations._convert_field_to_tzcCs|||}d|S)Nr=r?rMr'r'r*datetime_cast_date_sqls z)DatabaseOperations.datetime_cast_date_sqlcCs |||SNrNrMr'r'r*datetime_cast_time_sqls z)DatabaseOperations.datetime_cast_time_sqlcCs|||}|||SrP)r?r6r@r'r'r*datetime_extract_sqls  z'DatabaseOperations.datetime_extract_sqlcCs|||}|dvrd||f}|S|dkrd|}|S|dkr(d|}|S|dkr2d|}|S|d krr)r4r5rAsqlr'r'r*datetime_trunc_sqls*    z%DatabaseOperations.datetime_trunc_sqlcCsH|||}|dkrd|}|S|dkrd|}|S|dkr"d|}|S)NrTrUrVrWsecondrXrNrYr'r'r*time_trunc_sqls z!DatabaseOperations.time_trunc_sqlcst|}|j}|dvr||jnD|dkr!||jn9|dvr,||jn.|dkr:tj r9||j n |dkrE||j n|dkrP||j n |dkrZ||j |jjrk||dkrg|jn|j|S)N) JSONFieldr% BinaryField) BooleanFieldNullBooleanField DateTimeField DateField TimeField UUIDField)superget_db_converters output_fieldget_internal_typeappendconvert_textfield_valueconvert_binaryfield_valueconvert_booleanfield_valuerrGconvert_datetimefield_valueconvert_datefield_valueconvert_timefield_valueconvert_uuidfield_valuefieldempty_strings_allowedconvert_empty_bytesconvert_empty_string)r) expression converters internal_type __class__r'r*rgs2    z$DatabaseOperations.get_db_converterscCt|tjr |}|SrP) isinstancerLOBreadr)valuervrKr'r'r*rk z*DatabaseOperations.convert_textfield_valuecCst|tjr t|}|SrP)r|rr}rr~rr'r'r*rls  z,DatabaseOperations.convert_binaryfield_valuecCs|dvrt|}|S)N)rr)boolrr'r'r*rmsz-DatabaseOperations.convert_booleanfield_valuecCs|dur t||jj}|SrP)r make_awarerKrr'r'r*rnsz.DatabaseOperations.convert_datetimefield_valuecCr{rP)r|r Timestampdaterr'r'r*rorz*DatabaseOperations.convert_datefield_valuecCr{rP)r|rrtimerr'r'r*rprz*DatabaseOperations.convert_timefield_valuecCs|dur t|}|SrP)uuidUUIDrr'r'r*rqs z*DatabaseOperations.convert_uuidfield_valuecC|durdS|SNr'rrvrKr'r'r*ruz'DatabaseOperations.convert_empty_stringcCr)Nr'rr'r'r*rtrz&DatabaseOperations.convert_empty_bytescCr&)Nz DEFERRABLE INITIALLY DEFERREDr'r(r'r'r*deferrable_sqlr,z!DatabaseOperations.deferrable_sqlcCsRg}|D] }|}|dus|gkrtd|t|tr!|dn|qt|S)NzThe database did not return a new row id. Probably "ORA-1403: no data found" was raised internally but was hidden by the Oracle OCI library (see https://code.djangoproject.com/ticket/28859).r) get_valuerrjr|listtuple)r)cursorreturning_paramscolumnsparamrr'r'r*fetch_returned_insert_columnssz0DatabaseOperations.fetch_returned_insert_columnscCs|r |dr |dkr dSdS)Nr}r^DBMS_LOB.SUBSTR(%s)%s)endswith)r)db_typerxr'r'r*field_cast_sqlsz!DatabaseOperations.field_cast_sqlcCsdSrPr'r(r'r'r*no_limit_valuer,z!DatabaseOperations.no_limit_valuecCs@|||\}}ddd|rd|nd|rd|ndfDS)N css|]}|r|VqdSrPr').0rZr'r'r* sz6DatabaseOperations.limit_offset_sql..zOFFSET %d ROWSzFETCH FIRST %d ROWS ONLY)_get_limit_offset_paramsjoin)r)low_mark high_markfetchoffsetr'r'r*limit_offset_sqls   z#DatabaseOperations.limit_offset_sqlcCsz|j}t|ttfr!t|D]\}}|d|t|dd}q|St|tr;|D]\}}|d|t|dd}q*|S)Nz:arg%dreplace)errorsz:%s) statementr|rr enumeraterrdictitems)r)rrZparamsrirkeyr'r'r*last_executed_query!s z&DatabaseOperations.last_executed_querycCs,||t||}|d||dS)Nz "%s".currvalr)_get_sequence_namerexecutefetchone)r)r table_namepk_namesq_namer'r'r*last_insert_id0s z!DatabaseOperations.last_insert_idcCs$|dvrdS|dkr|dkrdSdS)N)iexact icontains istartswith iendswithz UPPER(%s)r^exactrrr')r)r4rxr'r'r* lookup_cast5s zDatabaseOperations.lookup_castcCr&)Nir'r(r'r'r*max_in_list_size<r,z#DatabaseOperations.max_in_list_sizecCr&)Nr'r(r'r'r*max_name_length?r,z"DatabaseOperations.max_name_lengthcCr&)NNULLr'r(r'r'r*pk_default_valueBr,z#DatabaseOperations.pk_default_valuecC|SrPr')r)xr'r'r*prep_for_iexact_queryEr,z(DatabaseOperations.prep_for_iexact_querycCs|durdS|Sr)r~r)rr'r'r* process_clobHszDatabaseOperations.process_clobcCs>|ds|dsdt||}|dd}|S)N"z"%s"%%%) startswithrrr3rr)r)namer'r'r* quote_nameMs zDatabaseOperations.quote_namecCs |dkr d}d|Sd}d|S)Nregexz'c'z'i'zREGEXP_LIKE(%%s, %%s, %s)r')r)r4 match_optionr'r'r* regex_lookupZs zDatabaseOperations.regex_lookupcCsx|sdSg}g}|D]}|d||jjj||jf|t|q dd|ddgt|ft |fS)N)rr'z%s.%szRETURNING %s INTO %s, r) rjrmodel_metadb_tablecolumnrrlenr)r)fields field_namesrrrr'r'r*return_insert_columnsas  z(DatabaseOperations.return_insert_columnscCsZ|j}|r|d||fn|d|f|WdS1s&wYdS)Na SELECT user_tables.table_name, rcons.constraint_name FROM user_tables JOIN user_constraints cons ON (user_tables.table_name = cons.table_name AND cons.constraint_type = ANY('P', 'U')) LEFT JOIN user_constraints rcons ON (user_tables.table_name = rcons.table_name AND rcons.constraint_type = 'R') START WITH user_tables.table_name = UPPER(%s) CONNECT BY NOCYCLE PRIOR cons.constraint_name = rcons.r_constraint_name GROUP BY user_tables.table_name, rcons.constraint_name HAVING user_tables.table_name != UPPER(%s) ORDER BY MAX(level) DESC a6 SELECT cons.table_name, cons.constraint_name FROM user_constraints cons WHERE cons.constraint_type = 'R' AND cons.table_name = UPPER(%s) )rKrrfetchall)r)r recursiverr'r'r*__foreign_key_constraintsqs  $z,DatabaseOperations.__foreign_key_constraintscCstdd|jS)Ni)maxsize)r,_DatabaseOperations__foreign_key_constraintsr(r'r'r*_foreign_key_constraintssz+DatabaseOperations._foreign_key_constraintsF)reset_sequences allow_cascadec s|sgSdd|Dt}|D]}j||dD]\}}|r$||||fqqfdd|DfddDfdd|D} |rcfddjjD} | | | S) NcSsh|]}|qSr'r2rtabler'r'r* sz/DatabaseOperations.sql_flush..)rc sdg|].\}}ddd|dd|ddfqS)z%s %s %s %s %s %s %s %s;ALTERTABLEDISABLE CONSTRAINTKEEPINDEX SQL_KEYWORD SQL_FIELDrrr constraintr)styler'r* s z0DatabaseOperations.sql_flush..c s2g|]}ddd|fqS)z %s %s %s;TRUNCATErrrrr'r*rsc sTg|]&\}}ddd|dd|fqS)z%s %s %s %s %s %s;rrENABLErrrrr'r*rscs g|] }|dvr|qS)rr2)rsequence)truncated_tablesr'r*rs )setraddrK introspection sequence_listextendsequence_reset_by_name_sql) r)rtablesrr constraintsr foreign_tablerrZ sequencesr')r)rrr* sql_flushs6       zDatabaseOperations.sql_flushc Cshg}|D]-}||d}||d}||dpd}|j|||t|t|d}||q|S)Nrridno_autofield_sequence_namerrr column_name)_get_no_autofield_sequence_namer_sequence_reset_sqlrrj) r)rrrZ sequence_inforrrqueryr'r'r*rs z-DatabaseOperations.sequence_reset_by_name_sqlc Cs~g}|j}|D]5}|jjD].}t|tr;||jj}||jj}||j} | |||| t |t | dnq q|S)Nr) rr local_fieldsr|r rrrrrjr) r)r model_listoutputr rfrrrr'r'r*sequence_reset_sqls&     z%DatabaseOperations.sequence_reset_sqlcCr&rr'r(r'r'r*start_transaction_sqlr,z(DatabaseOperations.start_transaction_sqlcCs |r d||Sd||S)NzUSING INDEX TABLESPACE %sz TABLESPACE %s)r)r) tablespaceinliner'r'r*tablespace_sqlsz!DatabaseOperations.tablespace_sqlcCr)z Transform a date value to an object compatible with what is expected by the backend driver for date columns. The default implementation transforms the date to text, but that is not necessary for Oracle. r'rr'r'r*adapt_datefield_valuesz(DatabaseOperations.adapt_datefield_valuecCsN|durdSt|dr |St|r"tjrt||jj}ntdt |S)aE Transform a datetime value to an object compatible with what is expected by the backend driver for datetime columns. If naive datetime is passed assumes that is in UTC. Normally Django models.DateTimeField makes sure that if USE_TZ is True passed datetime is timezone aware. Nresolve_expressionzNOracle backend does not support timezone-aware datetimes when USE_TZ is False.) hasattrris_awarerrG make_naiverKrJr from_datetimerr'r'r*adapt_datetimefield_values    z,DatabaseOperations.adapt_datetimefield_valuecCs`|durdSt|dr |St|trtj|dSt|r"tdtddd|j |j |j |j S)Nrz%H:%M:%Sz5Oracle backend does not support timezone-aware times.ilr) rr|strdatetimestrptimerrrJrrTrVr\ microsecondrr'r'r*adapt_timefield_values   z(DatabaseOperations.adapt_timefield_valuecCrrPr')r)r max_digitsdecimal_placesr'r'r*adapt_decimalfield_value0r,z+DatabaseOperations.adapt_decimalfield_valuecs|\}}|dkrdd|S|dkrdd|S|dkr%d||dS|d kr0d ||dS|d kr;d ||dS|d krFdd|S|dkrNtdt||S)NrzMOD(%s),&z BITAND(%s)|z"BITAND(-%(lhs)s-1,%(rhs)s)+%(lhs)s)lhsrhsz<>z"FLOOR(%(lhs)s / POWER(2, %(rhs)s))^z POWER(%s)#z'Bitwise XOR is not supported in Oracle.)rrrfcombine_expression)r) connectorsub_expressionsr&r'ryr'r*r*3s z%DatabaseOperations.combine_expressioncCs"|d}dtt||S)z Manually created sequence name to keep backward compatibility for AutoFields that aren't Oracle identity columns. z%s_SQ)rrrr3)r)r name_lengthr'r'r*rEs z2DatabaseOperations._get_no_autofield_sequence_namecCs2|d||g|}|dur||S|dS)Nz SELECT sequence_name FROM user_tab_identity_cols WHERE table_name = UPPER(%s) AND column_name = UPPER(%s)r)rrr)r)rrrrowr'r'r*rMs z%DatabaseOperations._get_sequence_namec Csg}|D]=}g}t|D]*\}}||r)t||d||}tj|d|}|s1d||f}||q |dd|qdd|S)N target_fieldrz %s col_%szSELECT %s FROM DUALrzSELECT * FROM (%s)z UNION ALL )rgetattrrirtypesgetrjr) r)rplaceholder_rowsr r/selectr placeholderrxr'r'r*bulk_insert_sqlVs  z"DatabaseOperations.bulk_insert_sqlc sF|dkr|\}}|\}}g||R}d||f|fSt|||S)Nrcz*NUMTODSINTERVAL(TO_NUMBER(%s - %s), 'DAY'))rfsubtract_temporals) r)rxr&r'lhs_sql lhs_paramsrhs_sql rhs_paramsrryr'r*r8ks z%DatabaseOperations.subtract_temporalscCs|r |jjjt|St|S)z5Oracle restricts the number of parameters in a query.)rKfeaturesmax_query_paramsr)r)robjsr'r'r*bulk_batch_sizessz"DatabaseOperations.bulk_batch_sizecCsFt|ttfr dSt|tr|jr||jSt|tr!|jr!dSdS)z} Oracle supports only EXISTS(...) or filters in the WHERE clause, others must be compared with True. TF)r|r r r conditional0conditional_expression_supported_in_where_clauservr )r)rvr'r'r*rBys zCDatabaseOperations.conditional_expression_supported_in_where_clauserP)F)NN)D__name__ __module__ __qualname__integer_field_rangesr set_operatorsr"cast_char_field_without_max_lengthcast_data_typesr+r6rBrrHrFr?rOrQrRr[r]rgrkrlrmrnrorprq staticmethodrurtrrrrrrrrrrrrrrrrrrrrrrrrrrrr"r*rrr7r8r@rB __classcell__r'r'ryr*rs         ! 5     r)&rr functoolsr django.confr django.dbrr"django.db.backends.base.operationsrdjango.db.backends.utilsrrdjango.db.modelsr r r django.db.models.expressionsr django.db.models.sql.wherer django.utilsrdjango.utils.encodingrrdjango.utils.functionalrdjango.utils.regex_helperrbaserutilsrrrrr'r'r'r*s"