OwlCyberSecurity - MANAGER
Edit File: sql_util.pyc
� �t�ac @ s� d d l m Z m Z m Z m Z m Z m Z m Z m Z m Z m Z d d l m Z d d l m Z d d l m Z m Z m Z d d l m Z d d l m Z d d l m Z m Z m Z m Z d d l Z d d l m Z e � Z d e f d � � YZ! d e f d � � YZ" d � Z# e d � � Z$ d � Z% d � Z& e d � � Z' d � Z( e d � � Z) d e f d � � YZ* e d � � Z+ e d � � Z, d S( i����( t Maxt Selectt Parametert Deletet Insertt Updatet ColumnSyntaxt TableSyntaxt Uppert utcNowSQL( t classproperty( t Logger( t succeedt inlineCallbackst returnValue( t normalizeUUIDOrNot( t schema( t SyncTokenValidExceptiont ENOTIFICATIONTYPEt ECALENDARTYPEt EADDRESSBOOKTYPEN( t UUIDt _EmptyCacherc B s& e Z d � Z e d � Z d � Z RS( c C s t t � S( N( R t True( t selft keyt value( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt set( s c C s t d � S( N( R t None( R R t withIdentifier( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt get+ s c C s t t � S( N( R R ( R R ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt delete. s ( t __name__t __module__R t FalseR R ( ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR & s t _SharedSyncLogicc B s� e Z d Z e d � � Z e d � � Z d � Z e d � � Z e d � � Z e e d � � � Z d � Z e e d � � Z d � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e d � � Z e e d � � Z d � Z d � Z d � Z e d � � Z e d � � Z! e d � � Z" e d � � Z# e d � � Z$ e d � � Z% e d � � Z& d � Z' RS(! sn Logic for maintaining sync-token shared between notification collections and shared collections. c C s: | j } t t | j � g d | d | j t d � k �S( ss DAL query for retrieving the sync token of a L{CommonHomeChild} based on its resource ID. t Fromt Wheret resourceID( t _revisionsSchemaR R t REVISIONt RESOURCE_IDR ( t clst rev( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _childSyncTokenQuery8 s c C sy | j } t | j t | j � g d | d | j j t d t | � � � j | j d k j | j t k � � d | j �S( NR$ R% t resourceIDst GroupBy( R' R R) R R( t InR t lent Andt RESOURCE_NAMER t Ort DELETEDR" ( R* R- R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _revisionsForResourceIDsB s !$c C sX | d k r d St | t � s. t | t � rP | j d d � \ } } t | � S| Sd S( Ni t _i ( R t isinstancet strt unicodet splitt int( R t tokent _ignore_uuidt revision( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt revisionFromTokenM s c c s@ | j d k r" | j � V| _ n t d | j | j f � d S( Ns %s_%s( t _syncTokenRevisionR t syncTokenRevisionR t _resourceID( R ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt syncTokenV s c c s] | j j | j d | j �Vd d } | d k rO t | j j d � V� } n t | � d S( NR& i s MIN-VALID-REVISION( R, t ont _txnRB R R; t calendarserverValueR ( R R> ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyRA \ s ' c c s� | j | � j | j d | �V} t | � } t | � t | j � � } | r� t | j j d � V� } x | D] } | | | <qp Wn t | � d S( NR- s MIN-VALID-REVISION( R5 RD RE t dictR t keysR; RF R ( R* t homet childResourceIDst rowst revisionst missingIDst min_revisionR& ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt childSyncTokenRevisionsc s " c C s t � � d S( N( t NotImplementedError( R R< ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt objectResourcesSinceTokenq s c C sy | j } | j t d � k j | j t d � k � } | sW | j | j t k � } n t | j | j g d | d | �S( s8 DAL query for (resource, deleted-flag) R> R& R$ R% ( R' R( R R1 R) R4 R" R R2 ( R* t deletedR+ t where( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _objectNamesSinceRevisionQueryt s -c C s | j | j | � � S( s" Return the changed and deleted resources since a particular sync-token. This simply extracts the revision from from the token then calls L{resourceNamesSinceRevision}. @param revision: the revision to determine changes since @type revision: C{int} ( t resourceNamesSinceRevisionR? ( R R< ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt resourceNamesSinceToken� s c c s g } g } g } | r� | j j d � V} | t | � k rF t � n g | j � j | j d | d | j �VD]$ \ } } | r� | n d | f ^ qo } | j d d � � xP | D]8 \ } } | r� | r� | j | � q� | j | � q� q� Wn | j � V} t | | | f � d S( s� Return the changed and deleted resources since a particular revision. @param revision: the revision to determine changes since @type revision: C{int} s MIN-VALID-REVISIONR> R& t R c S s | d S( Ni ( ( t x( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt <lambda>� RW N( RE RF R; R RT RD RB t sortt appendt listObjectResourcesR ( R R> t changedRR t invalidt minValidRevisiont namet removedt resultst wasdeleted( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyRU � s$ D c C sC | j } t d | d | j t d � k j | j t d � k � � S( NR$ R% t homeIDt collectionName( R' R t HOME_RESOURCE_IDR R1 t COLLECTION_NAME( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _removeDeletedRevision� s c C sc | j } t i t d � | j 6t d � | j 6t d � | j 6d | j 6t | j 6d | j g �S( NRd R& Re t Return( R' R R Rf R) Rg R R2 R" R4 R( ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _addNewRevision� s c c s| | j j | j d | j j d | j �V| j j | j d | j j d | j d | j �Vd d | _ | j j | � d S( NRd Re R& i ( Rh RD RE t _homeRB t _nameRj R@ t bumpRevisionForObject( R ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _initSyncToken� s c C sj | j } t i t j | j 6t d � | j 6t | j 6d | j t d � k j | j d k � d | j �S( sk DAL query to change sync token for a rename (increment and adjust resource name). R` R% R& Ri N( R' R R t REVISION_SEQR( R Rg R t MODIFIEDR) R1 R2 R ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _renameSyncTokenQuery� s c c sa | j j | j d | j d | j �V} | rR | d d | _ | j j | � n | j � Vd S( NR` R& i ( Rq RD RE Rl RB R@ Rm Rn ( R RK ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _renameSyncToken� s c C sQ | j } t i t j | j 6t | j 6d | j t d � k j | j d k � �S( s� DAL query to change collection sync token. Note this can impact multiple rows if the collection is shared. R% R& N( R' R R Ro R( R Rp R) R R1 R2 R ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _bumpSyncTokenQuery� s c c sO | j j | � sK | j j | � | j j | j d | j �Vd | _ n d S( NR& ( RE t isRevisionBumpedAlreadyRm Rs RD RB R R@ ( R ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _bumpSyncToken� s c C sU | j } t d | d | j t d � k j | j t d � k � j | j d k � � S( s� DAL query to remove all child revision information. The revision for the collection itself is not touched. R$ R% Rd R& N( R' R Rf R R1 R) Rg R ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _deleteSyncTokenQuery s c C s} | j } t i d | j 6t j | j 6t | j 6t | j 6d | j t d � k j | j t d � k � j | j d k � �S( sM DAL query to indicate a shared collection has been deleted. R% Rd R& N( R' R R R) R Ro R( R R4 R Rp Rf R R1 R2 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _sharedRemovalQuery s c C se | j } t i d | j 6t j | j 6t | j 6t | j 6d | j t d � k j | j d k � �S( sM DAL query to indicate an owned collection has been deleted. R% R& N( R' R R R) R Ro R( R R4 R Rp R R1 R2 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _unsharedRemovalQuery* s c c s� | j j | j d | j j d | j �V| r[ | j j | j d | j j d | j �Vn | j j | j d | j �Vd | _ d S( s� When a collection is deleted we remove all the revision information for its child resources. We update the collection's sync token to indicate it has been deleted - that way a sync on the home collection can report the deletion of the collection. @param sharedRemoval: indicates whether the collection being removed is shared @type sharedRemoval: L{bool} Rd R& N( Rv RD RE Rk RB Rw Rx R R@ ( R t sharedRemoval( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _deletedSyncToken; s c C s | j d | � S( Nt insert( t _changeRevision( R R` ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _insertRevisionW s c C s | j d | � S( Nt update( R| ( R R` ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _updateRevisionZ s c C s | j d | � S( NR ( R| ( R R` ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _deleteRevision] s c C sj | j } t i t j | j 6t | j 6t | j 6d | j t d � k j | j t d � k � d | j �S( NR% R& R` Ri ( R' R R Ro R( R R4 R Rp R) R R1 R2 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _deleteBumpTokenQuery` s c C s` | j } t i t j | j 6t | j 6d | j t d � k j | j t d � k � d | j �S( NR% R& R` Ri ( R' R R Ro R( R Rp R) R R1 R2 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _updateBumpTokenQueryn s c C sL | j } t | j g d | d | j t d � k j | j t d � k � �S( NR$ R% R& R` ( R' R R) R R1 R2 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _insertFindPreviouslyNamedQuery{ s c C sj | j } t i t j | j 6t | j 6t | j 6d | j t d � k j | j t d � k � d | j �S( NR% R& R` Ri ( R' R R Ro R( R" R4 R Rp R) R R1 R2 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _updatePreviouslyNamedQuery� s c C sc | j } t i t d � | j 6t d � | j 6t d � | j 6t j | j 6t | j 6d | j �S( NRd R& R` Ri ( R' R R Rf R) R2 R Ro R( R" R4 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _completelyNewRevisionQuery� s c C sc | j } t i t d � | j 6t d � | j 6t d � | j 6t j | j 6t | j 6d | j �S( NRd R& R` Ri ( R' R R Rf R) R2 R Ro R( R R4 ( R* R+ ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt" _completelyNewDeletedRevisionQuery� s c c s� | d k r� | j j | j d | j d | �V} | rK | d d | _ q�| j j | j d | j � j d | j d | �Vd d | _ n?| d k r| j j | j d | j d | �V} | r� | d d | _ q�| j j | j d | j � j d | j d | �Vd d | _ n� | d k r�t | j j | j d | j d | �V� } | r�| j j | j d | j d | �Vd d | _ q�| j j | j d | j � j d | j d | �Vd d | _ n | j � Vt | j � d S( NR R& R` i Rd R~ R{ ( R� RD RE RB R@ R� t ownerHomeR� R� t boolR� R� t _maybeNotifyR ( R t actionR` RK t found( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR| � s@ c C s t d � S( sP Maybe notify changed. (Overridden in NotificationCollection.) N( R R ( R ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR� � s (( R R! t __doc__R R, t classmethodR5 R? R RC RA RO RQ R RT RV RU Rh Rj Rn Rq Rr Rs Ru Rv Rw Rx R" Rz R} R R� R� R� R� R� R� R� R| R� ( ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR# 2 sH # 7c C sR | t k rd t t t j j � g d t j j t j d t j j t j j k �d t j j | k �Si d t 6d t 6| } t t | d � } t t | d � } t t | � } t t | d � } t t | j � g d | j | d | j | j k �j | d | j | j k �j | d | j | j k �d | j d k j | j | k � �S( s� Construct a query to determine the modification time of the newest object in a given home. @param uid: the UID of the home to scan. @type uid: C{str} @param homeType: The type of home to scan; C{ECALENDARTYPE}, C{ENOTIFICATIONTYPE}, or C{EADDRESSBOOKTYPE}. @type homeType: C{int} @return: A select query that will return a single row containing a single column which is the maximum value. @rtype: L{Select} R$ RD R% t CALENDARt ADDRESSBOOKt _HOMEt _BINDt _OBJECTi ( R R R R t NOTIFICATIONRp t NOTIFICATION_HOMEt joinR) t NOTIFICATION_HOME_RESOURCE_IDt OWNER_UIDR R t getattrRf t PARENT_RESOURCE_IDt BIND_MODER1 ( t uidt homeTypet homeTypeNameRI t bindt childt obj( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt determineNewest� s( !c c s� d d l m } d d l m } i | t 6| t 6t t 6| } i t j t 6t j t 6t j t 6| } g } | j | t | j � | � j | � Vg � | j | t | j � | � j | � Vg � | j d d � � | d d } | d d } | | | d t �Vt | j � � } t | j � � } t | | | j � d | � V| j � | k ryt | | | j � | � Vn t | � Vd S( s# Merge two homes together. This determines which of C{one} or C{two} is newer - that is, has been modified more recently - and pulls all the data from the older into the newer home. Then, it changes the UID of the old home to its UID, normalized and prefixed with "old.", and then re-names the new home to its name, normalized. Because the UIDs of both homes have changed, B{both one and two will be invalid to all other callers from the start of the invocation of this function}. @param sqlTxn: the transaction to use @type sqlTxn: A L{CommonTransaction} @param one: A calendar home. @type one: L{ICalendarHome} @param two: Another, different calendar home. @type two: L{ICalendarHome} @param homeType: The type of home to scan; L{ECALENDARTYPE} or L{EADDRESSBOOKTYPE}. @type homeType: C{int} @return: a L{Deferred} which fires with with the newer of C{one} or C{two}, into which the data from the other home has been merged, when the merge is complete. i����( t migrateHomeR c S s | d S( Ni ( ( RX ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyRY @ RW i i t merges old.N( t txdav.caldav.datastore.utilR� t txdav.carddav.datastore.utilR R t _dontBotherWithNotificationsR R t ADDRESSBOOK_HOMEt CALENDAR_HOMER� R[ R� R� RD RZ R R t _renameHomeR ( t sqlTxnt onet otherR� t migrateCalendarHomet migrateABHomeR� t homeTablet botht oldert newert newNormalizedt oldNormalized( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt mergeHomes s. # # c C s, t i | | j 6d | j | k �j | � S( s Rename a calendar, addressbook, or notification home. Note that this function is only safe in transactions that have had caching disabled, and more specifically should only ever be used during upgrades. Running this in a normal transaction will have unpredictable consequences, especially with respect to memcache. @param txn: an SQL transaction to use for this update @type txn: L{twext.enterprise.ienterprise.IAsyncTransaction} @param table: the storage table of the desired home type @type table: L{TableSyntax} @param oldUID: the old UID, the existing home's UID @type oldUID: L{str} @param newUID: the new UID, to change the UID to @type newUID: L{str} @return: a L{Deferred} which fires when the home is renamed. R% ( R R� RD ( t txnt tablet oldUIDt newUID( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR� O s c C s d S( s� Notifications are more transient and can be easily worked around; don't bother to migrate all of them when there is a UUID case mismatch. N( ( R� R� R� ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR� i s c c s� d d l m } i t j t 6t j t 6t j t 6| } | j j j d � d } t | j g d | d | j �j | � V} t | � } g } xt | � D]�\ } \ } t j � } | r� d t | � t | � | | } n d } t j d d | d | t | � d d | d | �d } t | | | � V} | t k rO| | � V} n d } d } | d k r}t j d d | �n y t | � } Wn t k r�n� Xt | � j � } | | k rIt j d d | d | d | �t | | | � V} | d k rt | | | | � VqI| t k r0| | � V} n t | | | | � V} n t j � } | | } | j | � t j d d | d | d | d | �q� Wt d � d S( s� Normalize the UUIDs in the given L{txdav.common.datastore.CommonStore}. This changes the case of the UUIDs in the calendar home. @param t: the transaction to normalize all the UUIDs in. @type t: L{CommonStoreTransaction} @param homeType: The type of home to scan, L{ECALENDARTYPE}, L{EADDRESSBOOKTYPE}, or L{ENOTIFICATIONTYPE}. @type homeType: C{int} @return: a L{Deferred} which fires with C{None} when the UUID normalization is complete. i����( t fixOneCalendarHomeR6 i R$ t OrderBys %0.3dt unknownsN Scanning UID {uid} [{homeType}] ({pct:0.2d}%, {estimate} seconds remaining)...R� t pctid t estimateR� s0 {uid!r} appears to be missing, already processeds2 Detected case variance: {uid} {newuid}[{homeType}]t newuidsh Scanned UID {uid}; {elapsed} seconds elapsed, {fixes} properties fixed ({duplicate} fixes in duplicate).t elapsedt fixest duplicateN( R� R� R R� R R� R R� R t modelR` R: R R� RD R0 t enumeratet timet sumt logt infot floatR t _getHomeR t ValueErrorR8 t upperR� R� R[ R ( t tR� R� R� R� t allUIDst totalt allElapsedt nt UIDt startR� R� t thist fixedThisHomet fixedOtherHomet uuidobjt newnamet endR� ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _normalizeHomeUUIDsInq sh c C s- | t k r | j | � S| j | | � Sd S( s� Like L{CommonHome.homeWithUID} but also honoring ENOTIFICATIONTYPE which isn't I{really} a type of home. @param txn: the transaction to retrieve the home from @type txn: L{CommonStoreTransaction} @param homeType: L{ENOTIFICATIONTYPE}, L{ECALENDARTYPE}, or L{EADDRESSBOOKTYPE}. @param uid: the UID of the home to retrieve. @type uid: L{str} @return: a L{Deferred} that fires with the L{CommonHome} or L{NotificationHome} when it has been retrieved. N( R t notificationsWithUIDt homeWithUID( R� R� R� ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR� � s c c s� | j j } g | j D] } t | � ^ q } x� t | g | d t | � �j | � VD]� } | d } | d } t | � } | | k rX t } x2 t | | � D]! \ } } | j | | k � } q� Wt i | | 6d | �j | � VqX qX Wd S( s` Upper-case the UUIDs in the given SQL DAL column. @param txn: The transaction. @type txn: L{CommonStoreTransaction} @param column: the column, which may contain UIDs, to normalize. @type column: L{ColumnSyntax} @return: A L{Deferred} that will fire when the UUID normalization of the given column has completed. R$ i i R% N( R� R� t primaryKeyR R R RD R t _AndNothingt zipR1 R ( R� t columnt tableModelt columnModelt pkeyt rowt beforet pkeypartst afterRS t pkeycolt pkeypart( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _normalizeColumnUUIDs� s R� c B s e Z d Z e d � � Z RS( s� Simple placeholder for iteratively generating a 'Where' clause; the 'And' just returns its argument, so it can be used at the start of the loop. c C s | S( s& Return the argument. ( ( R ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR1 s ( R R! R� t staticmethodR1 ( ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR� � s c c s� x� t j t j t j g D]� } t | j g d | d | j t | j � k �} | j | � V} | r x3 | D]( \ } t | � | k rl t t � ql ql Wq q Wt t � d S( s- Determine whether a given store requires a UUID normalization data upgrade. @param txn: the transaction to use @type txn: L{CommonStoreTransaction} @return: a L{Deferred} that fires with C{True} or C{False} depending on whether we need the normalization upgrade or not. R$ R% N( R R� R� R� R R� R RD R R R R" ( R� RX t slctRK R� ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt _needsNormalizationUpgrade s c c s� | j d d d t � } t | � VsJ t j d � | j � Vt d � n yV t | t � Vt | t � Vt | t � Vt | t j j � Vt | t j j � VWn t j d � | j � Vn X| j � Vd S( s� Fix all UUIDs in the given SQL store to be in a canonical form; 00000000-0000-0000-0000-000000000000 format and upper-case. t labelt fixUUIDNormalizationt disableCachesK No potentially denormalized UUIDs detected, skipping normalization upgrade.s Unable to normalize UUIDsN( t newTransactionR R� R� R� t abortR R R� R R R R� R t RESOURCE_PROPERTYt VIEWER_UIDt APN_SUBSCRIPTIONSt SUBSCRIBER_GUIDt failuret commit( t storeR� ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyR� # s (- t twext.enterprise.dal.syntaxR R R R R R R R R R t twext.python.clspropR t twext.python.logR t twisted.internet.deferR R R t txdav.base.datastore.utilR t! txdav.common.datastore.sql_tablesR t txdav.common.icommondatastoreR R R R R� t uuidR R� t objectR R# R� R� R� R� R� R� R� R� R� R� ( ( ( s2 /opt/cpanel-ccs/txdav/common/datastore/sql_util.pyt <module> s, F " � � (: V !