+j} tdZddlmZddlmZddlmZddlmZmZddl Z ddl m Z m Z ddl mZdd lmZdd lmZmZdd lmZmZmZmZmZdd lmZmZmZmZmZm Z m!Z!dd l"m#Z#m$Z$dZ%de&de'de(fdZ)de&de*e(fdZ+dede&dedzfdZ,dede&de&defdZ-dede&dedzfdZ.dede&de&de'fdZ/dede&de0fdZ1dede&de0fdZ2 djdede&d!e&de&de&dzd"e'dzd#e&dzdedzfd$Z3 djdede&d!e&de&de&dzd"e'dzd#e&dzde4ee0ffd%Z5 dkded&ed#e&dzddfd'Z6 dldede&d)e'd*e'd+e&dzd,ee&dzd-ee&dzd.e(dzd/e&dzd0e&dzd1e7dzd2e&dzde4e*ee(e&e*e&fe'ffd3Z8 dmdede&de&de4eee*e&fdzfd4Z9 dmdede&de&de4eefdzfd5Z: dndede&d7edzd8e0ddf d9Z;dede&d!e&ddfd:Z< dkdede&d7edzddfd;Z=ded dkdede&d>e(dzddfd?Z? dkdede&d@e(dzddfdAZ@dede&de&de0fdBZAdede&de&de0fdCZB dkdede&dDe&dzddfdEZCGdFdGeZDdede&deefdHZEdede&de*e&fdIZF dmdede&de&d!e&d"e'de&de4e0e0ffdJZGdedKe*e&de'fdLZHdedMe*e&de'fdNZIdede*e&fdOZJdedPe*e&de'fdQZKdRdSdedTe*e&dUe0de*eDfdVZLdedWe*e&dXe0de'fdYZMdedWe*e&dXe0de'fdZZNdede&dXe0de'fd[ZOdedWe*e&de'fd\ZPdede&de0fd]ZQGd^d_eZR dodedTe*e&dae'd)e'de*eRf dbZSdedWe*e&dce'de'fddZTdedee*e(ddfdfZUdedge(e&e&fdeVe&fdhZWdedWe*e&deVe&fdiZXdS)pzQuery functions for the unified AssetReference table. This module replaces the separate asset_info.py and cache_state.py query modules, providing a unified interface for the merged asset_references table. ) defaultdict)datetime)Decimal) NamedTupleSequenceN)deleteselect)sqlite)IntegrityError)Sessionnoload)AssetAssetReferenceAssetReferenceMetaAssetReferenceTagTag)MAX_BIND_PARAMSapply_metadata_filterapply_tag_filtersbuild_prefix_like_conditionsbuild_visible_owner_clausecalculate_rows_per_statement iter_chunks)escape_sql_like_string get_utc_nowc|dSt|trdSt|tttt frdSdS)NTF isinstanceboolintfloatrstr)vs K/home/wildlama/comfy/ComfyUI/app/assets/database/queries/asset_reference.py_check_is_scalarr%%sHyt!Tt!c5'3/00t 5keyordinalreturncJt|tr||t|dSt|tttfr9t|tr|nt t |}|||dSt|t r|||dS|||dS)z1Convert a scalar value to a typed projection row.)r'r(val_bool)r'r(val_num)r'r(val_strr'r(val_jsonr)r'r(valuenums r$_scalar_to_rowr2/s%IwDKKHHH%#ug.//@!%11Jeews5zz7J7Jw3???%Bw5AAA7 > >>r&c$|gSt|rtd|gSt|trOt d|Drfdt |DSfdt |DSd|dgS)z5Turn a metadata key/value into typed projection rows.Nrc34K|]}t|VdSN)r%).0xs r$ z+convert_metadata_to_rows..Ds+22q""222222r&c<g|]\}}|t||Sr5)r2r6ir7r's r$ z,convert_metadata_to_rows..Es*\\\$!QamN31--mmmr&c&g|] \}}|||dS)Nr.r:s r$r<z,convert_metadata_to_rows..Fs-hhhdaZ[Zgq99ZgZgZgr&r.)r%r2rlistall enumerate)r'r0s` r$convert_metadata_to_rowsrB;s } /sAu--..%i 22E222 2 2 ]\\\\)E:J:J\\\ \hhhhiPUFVFVhhhhA5 9 9 ::r&session reference_idc8|t|Sr5)getr)rCrDs r$get_reference_by_idrGMs ;;~| 4 44r&owner_idct||}|r|jtd|d|jr|j|krt d|S)zFetch a reference and verify ownership. Raises: ValueError: if reference not found or soft-deleted PermissionError: if owner_id doesn't match )rDNAssetReference not foundz not owner)rG deleted_at ValueErrorrHPermissionError)rCrDrHrefs r$get_reference_with_owner_checkrPTsi gL A A AC E#.,C<CCCDDD |+ 00k*** Jr& file_pathc|tttj|kdS)z!Get a reference by its file path.)executer rwhererQlimitscalarsfirst)rCrQs r$get_reference_by_file_pathrYgs^  > " " ( ()AY)N O O U UVW X X     r&asset_idexclude_reference_idc|ttj|ktj|ktjdS)zKCount active (non-deleted) references to an asset, excluding one reference.N)queryrfilterrZidrLis_count)rCrZr[s r$count_active_siblingsrbus]  n%%   #x /  !5 5  % ) )$ / /  r&cttjdttj|ktjd d}| | duS)NTrS) r saliteral select_fromrrUrZrLr`rVrTrX)rCrZqs r$reference_exists_for_asset_idrhs rz$  ^ $ $ ~&(2 3 3 ~(,,T22 3 3 q  ??1   # # % %T 11r&cttjdttj|ktjd d}| | duS)zGReturn True if a reference with the given ID exists (not soft-deleted).TNrS) r rdrerfrrUr_rLr`rVrTrX)rCrDrgs r$reference_existsrjs rz$  ^ $ $ ~ L0 1 1 ~(,,T22 3 3 q  ??1   # # % %T 11r&namemtime_ns preview_idc $t} |5t||||||||| }||||cdddS#1swxYwYdS#t $rYdSwxYw)zHInsert a new AssetReference. Returns None if unique constraint violated.) rZrlrHrQrmrn created_at updated_atlast_access_timeN)r begin_nestedraddflushr ) rCrZrlrHrQrmrnnowrOs r$insert_referencerws --C  ! ! # #   !!#!%!$   C KK    MMOOO                   tts5BAA4' B4A88B;A8<B BBc t|||||||}|r|dfS|rt||}n|tttj|ktj|ktj|ktj d d }|std|dfS)aGet existing or create new AssetReference. For filesystem references (file_path is set), uniqueness is by file_path. For API references (file_path is None), we look for matching asset_id + owner_id + name. Returns (reference, created). )rZrlrHrQrmrnTNrSz4Failed to find AssetReference after insert conflict.F)rwrYrTr rrUrZrlrHrQr`rVuniquescalar_one_or_none RuntimeError) rCrZrlrHrQrmrnrOexistings r$get_or_create_referencer}s "     C Dy -gyAA OO~&&"+x7"'4/"+x7",0066  q  VXX   ! !  SQRRR U?r& referencecXt}|r|j|kr||_||_dS)zGUpdate timestamps and optionally preview_id on existing AssetReference.N)rrnrq)rCr~rnrvs r$update_reference_timestampsrs8 --C*i*j88) Ir&drVoffset name_contains include_tags exclude_tagsmetadata_filtersortorderafter_cursor_valueafter_cursor_idc : ttttjtjkt|tjdktj d ttj } |rJt|\} }| tjd| d|} t#| ||} t%| |} |pd}| pd} tjtjtjtjtjd}||tj}| dk}| | |r?t3j|| kt3j|| ktj| k}n>t3j|| kt3j|| ktj| k}| |} |rtjntj}|r|n|}| |||} | | |} tt2j!"#tttjtjkt|tjdktj d}|rJt|\} }|tjd| d|}t#|||}t%||}tI|%|&pd}|%| '()}d |D}tUtV}|r|%ttXj-t\jt\t\jtXj/ktXj-0|tXj/}|)D] \}}||1|!tW|||fS) u,List references with pagination, filtering, and sorting. When ``after_cursor_value``/``after_cursor_id`` are supplied the query uses keyset pagination — ``offset`` is ignored and a WHERE clause selects rows strictly after the given ``(sort_col, id)`` position in the active sort direction. The cursor value must already be typed for the column (datetime for time sorts, int for size, str for name); the caller decodes the opaque cursor string and resolves to the typed value. Returns (references, tag_map, total_count). FN%)escaperpdesc)rlrprqrrsizercg|] }|j Sr>)r_)r6rs r$r<z(list_references_page..bs---1!$---r&)2r rjoinrr_rZrUr is_missingrLr`optionsr tagsrrlilikerrlowerrprqrr size_bytesrFrdor_and_rascorder_byrVrfuncrarfr rT scalar_oneryrWr@rr?rasset_reference_idrtag_namein_append)rCrHrVrrrrrrrrrbaseescapedescsort_mapsort_col descendingkeysetid_expsort_exp count_stmttotalrefsid_listtag_maprowsref_idrs r$list_references_pagers4 ~ eUX!88 9 9 )(33 4 4 ~(E1 2 2 ~(,,T22 3 3 +,, - -  Q-m<< zz.-33NNNN33OOPP T< > >D  7 7D  L ' ' ) )D _f # # % %E#$/$/*; H||D.";< >E ??4 ' ' ) ) 1 1 3 3 7 7 9 9D-----G$/$5$5G- $7 B B T#sx#4#== > > U$7;;GDD E E X'04466 7 7    !%  - - FH FO " "8 , , , , ::w %%r&cttttjttjtjkttj tjkdttjtj kd tj|ktj dt|t!tjtj}||}|sdS|d\}}}g}t-} |D]6\} } } | r.| | vr*| | || 7|||fS)NT)isouterr)r rrrrlrr_rZrrrrUrLr`rrr rrrrTr@setrtr) rCrDrHstmtr first_ref first_asset_rseen_ref_assetrs r$fetch_reference_asset_and_tagsrqs ~uch// eUX!88 9 9    0N4E E   c380994 H H    -  % ) )$ / / &x 0 0  +,, - - #(,,.. ! ! $ ??4 $ $ & &D t $QI{ADUUD"&""fh  ",, HHX    KK ! ! ! k4 ''r&c"tttttjtjktj|ktjdt| d ttj }||}|sdS|d|dfS)NrSr)r rrrr_rZrUrLr`rrVrr rrTrX)rCrDrHrpairs r$fetch_reference_and_assetrs ~u%% eUX!88 9 9    -  % ) )$ / / &x 0 0  q +,, - -  ??4 & & ( (D t 7DG r&Tts only_if_newerc|p t}tjttj|k}|rS|tjtjdtj|k}| | |dS)N)rr) rrdupdaterrUr_rrrr`rTvalues)rCrDrrrs r$update_reference_access_timers {}}B 9^ $ $ * *>+< +L M MD zz F/33D99/"4      OODKKK4455555r&ct}|tjttj|k||dS)z%Update the name of an AssetReference.)rlrqNrrTrdrrrUr_r)rCrDrlrvs r$update_reference_namers` --C OO .!! ~ L0 1 1 Tc * *r&c|p t}|tjttj|k|dS)z5Update the updated_at timestamp of an AssetReference.)rqNr)rCrDrs r$update_reference_updated_atrsc {}}B OO .!! ~ L0 1 1 2  r&rOc|tttj|jk|i|jpi|jpi}|sdSg}| D]\}}t||D]}| t|j|dt|d| d| d| d| d|r+|||dSdS) zDelete and rebuild AssetReferenceMeta rows from merged system+user metadata. The merged dict is ``{**system_metadata, **user_metadata}`` so user keys override system keys of the same name. Nr'r(r-r,r+r/)rr'r(r-r,r+r/)rTrrrUrr_rusystem_metadata user_metadataitemsrBrr rFadd_all)rCrOmergedrkr#rs r$rebuild_metadata_projectionrsw  OO!""((  1SV ;    MMOOO I$* I0A0GR IF %'D   1)!Q//  A KK"'*v%) --EE),,EE),,UU:..UU:..       r&rc|t|}|std|d|pi|_t |_|t||dS)NrJrK)rFrrMrrrqrur)rCrDrrOs r$set_reference_metadatarsu ++nl 3 3C EC<CCCDDD%+C ]]CN MMOOO-----r&rc|t|}|std|d|pi|_t |_|t||dS)zESet system_metadata on a reference and rebuild the merged projection.rJrKN)rFrrMrrrqrur)rCrDrrOs r$set_reference_system_metadatarsu ++nl 3 3C EC<CCCDDD)/RC ]]CN MMOOO-----r&ctjttj|kt |}t ||jpddkS)Nr) rdrrrUr_rr rTrowcount)rCrDrHrs r$delete_reference_by_idrsb 9^ $ $ * *\)"8,,  D wt$$-2 3 3a 77r&clt}tjttj|ktjdt| |}t| |j pddkS)zMark a reference as soft-deleted by setting deleted_at timestamp. Returns True if the reference was found and marked deleted. N)rLr) rrdrrrUr_rLr`rrr rTr)rCrDrHrvrs r$soft_delete_reference_by_idr+s --C .!!    -  % ) )$ / / &x 0 0  3    wt$$-2 3 3a 77r&preview_reference_idc2|t|}|std|d|d|_n5|t|std|d||_t |_|dS)zCSet or clear preview_id and bump updated_at. Raises on unknown IDs.rJrKNzPreview AssetReference )rFrrMrnrrqru)rCrDrrOs r$set_reference_previewrAs ++nl 3 3C EC<CCCDDD#{{>+?@@ YW7KWWWXX X- ]]CN MMOOOOOr&cleZdZUdZeed<eed<edzed<eed<eed<edzed<edzed <dS) CacheStateRowz/Row from reference query with cache state data.rDrQNrm needs_verifyrZ asset_hashr)__name__ __module__ __qualname____doc__r"__annotations__r rr>r&r$rrVsr99NNNDjMMMd d r&rc|tttj|ktjdktjdtj  S)NF) rTr rrUrZrrLr`rr_rrWr@rCrZs r$list_references_by_asset_idrbs  > " " U>*h6 7 7 U>,5 6 6 U>,0066 7 7 Xn'++-- . .      r&c ^t|ttjtj|ktjd S)zReturn every file_path for an asset, including soft-deleted/missing refs. Used for orphan cleanup where all on-disk files must be removed. N) r?rTr rrQrUrZisnotrWr@rs r$list_all_file_paths_by_asset_idrss|  >+ , , U>*h6 7 7 U>+11$77 8 8     r&c t}||||t|d|||d }tjtjd i|tjg}||} t| j pddk} | rdStj t tj|k tj tj|ktjdtjt|ktjdktjd|t|dd|} || } t| j pddk} d| fS) zUpsert a reference by file_path. Returns (created, updated). Also restores references that were previously marked as missing. F) rZrQrlrHrmrrprqrrindex_elementsr)TFNT)rZrmrrLrqr>)rr r insertrron_conflict_do_nothingrQrTrrdrrUrrZrmr`rrLr)rCrZrQrlrmrHrvvalsinsrescreatedupdres2updateds r$upsert_referencers --CMM  D  n%%       0H/I  J J //#  C#,#!$$q(G{ .!! ~'94 5 5  F'83'++D11'3x==8)T1)//55      H %   " ??3  D$-$1%%)G '>r&valid_prefixesc|sdSt|}tj|}|tjt t jdt j d|t j dk d}|j S)zMark references as missing when file_path doesn't match any valid prefix. Returns number of references marked as missing. rNFTr)rrdrrTrrrUrQrrLr`rrr)rCrcondsmatches_valid_prefixresults r$(mark_references_missing_outside_prefixesrs q ( 8 8E65> __ .!! ~'--d33 4 4 ~(,,T22 3 3 $$ % % ~(E1 2 2 4 F ?r& file_pathsc|sdSd}t|tD]}|tjt t j|t j dkt j d d}||j z }|S)zkRestore references that were previously marked as missing. Returns number of references restored. rTNFr)rrrTrdrrrUrQrrrLr`rr)rCrrchunkrs r$restore_references_by_pathsrs q EZ99!! In % % U>+//66 7 7 U>,4 5 5 U>,0066 7 7 VuV % %      Lr&ctjtjdtjt jktjdktj d t  }tjt jt j d|}d||DS)zGet IDs of unhashed assets (hash=None) with no active references. An asset is considered unreferenced if it has no references, or all its references are marked as missing. Returns list of asset IDs that are unreferenced. rSFNcg|] }|d S)rr>r6rows r$r<z7get_unreferenced_unhashed_asset_ids..s G G GsCF G G Gr&)rdr rerUrrZrr_rrLr` correlateexistshashrTr@)rCactive_ref_existsunreferenced_subqs r$#get_unreferenced_unhashed_asset_idsrs "*Q--  ~&%(2 3 3 ~(E1 2 2 ~(,,T22 3 3 5     %(++11 t00 H Ggoo.?@@DDFF G G GGr& asset_idsc|sdSd}t|tD]}|tjt t j||tjttj |}||j z }|S)zUDelete assets and their references by ID. Returns number of assets deleted. r) rrrTrdrrrUrZrrr_r)rCrrrrs r$delete_assets_by_idsrs q EY88!! In % % + +N,C,G,G,N,N O O   5!1!1!7!7 U8K8K!L!LMM   Lr&F)include_missingprefixesrc |sgSt|}tjtjtjtjtjtjtj tj  ttjtjk tjd tjd tj|}|s#| tjdk}||tjtj}d|DS)aOGet all references with file paths matching any of the given prefixes. Args: session: Database session prefixes: List of absolute directory prefixes to match include_missing: If False (default), exclude references marked as missing Returns: List of cache state rows with joined asset data NFcg|]Y}t|d|d|d|d|d|d|dt|dndZS) rrSN)rDrQrmrrZrr)rr r s r$r<z/get_references_for_prefixes..?s}     Q!fVQV1v&)!f&8s3q6{{{d      r&)rrdr rr_rQrmrrZrrrrrUrrLr`rrrTrrr@)rCrrrr]rs r$get_references_for_prefixesrs\  ( 2 2E    $  #  '  # J     eUX!88 9 9 ~'--d33 4 4 ~(,,T22 3 3 rvu~   @ N5>?? ?? ~.2244n6G6K6K6M6MNN   cee       r& reference_idsr0c6|sdSd}t|tD]|}|tjt t j| |}||j z }}|S)zXSet needs_verify flag for multiple references. Returns: Number of rows updated r)r rrrTrdrrrUr_rrrrCrr0rrrs r$bulk_update_needs_verifyr#Ms q E]O<<!! In % % U>$((// 0 0 VV ' '     Lr&c6|sdSd}t|tD]|}|tjt t j| |}||j z }}|S)zVSet is_missing flag for multiple references. Returns: Number of rows updated rrr!r"s r$bulk_update_is_missingr%as q E]O<<!! In % % U>$((// 0 0 VuV % %     Lr&c2|tjttj|ktjd|}|j S)zgSet is_missing flag for ALL references belonging to an asset. Returns: Number of rows updated Nr) rTrdrrrUrZrLr`rr)rCrZr0rs r$update_is_missing_by_asset_idr'usp__ .!! ~&(2 3 3 ~(,,T22 3 3 5 ! ! F ?r&c|sdSd}t|tD]h}|tjt t j|}||j z }i|S)zIDelete references by their IDs. Returns: Number of rows deleted r) rrrTrdrrrUr_rr)rCrrrrs r$delete_references_by_idsr)s q E]O<<!! In % % + +N,=,A,A%,H,H I I     Lr&c|t|}|sdS|jdS|t jt t j|k||dS)zDelete a seed asset (hash is None) and its references. Returns: True if asset was deleted, False if not found or has a hash FNT) rFrrrTrdrrrUrZ)rCrZassets r$delete_orphaned_seed_assetr,s KKx ( (E u zu OO .!!''(?8(KLL NN5 4r&c<eZdZUdZeed<eed<eed<eed<dS)UnenrichedReferenceRowz&Row for references needing enrichment.rDrZrQenrichment_levelN)rrrrr"rr r>r&r$r.r.sB00MMMNNNr&r. max_levelc|sgSt|}tjtjtjtjtjtj dtj dtj |tj dktj|ktj|}||}d|DS)a~Get references that need enrichment (enrichment_level <= max_level). Args: session: Database session prefixes: List of absolute directory prefixes to scan max_level: Maximum enrichment level to include (0=stubs, 1=metadata done) limit: Maximum number of rows to return Returns: List of unenriched reference rows with file paths NFc dg|]-}t|d|d|d|d.S)rrSrr)rDrZrQr/)r.r s r$r<z-get_unenriched_references..sU     QV!f V       r&)rrdr rr_rZrQr/rUrrLr`rrrrrVrTr@)rCrr1rVrr]rs r$get_unenriched_referencesr4s0"  ( 2 2E    #  $  +   ~'--d33 4 4 ~(,,T22 3 3 rvu~   ~(E1 2 2 ~.); < < .#'')) * * u ??5 ! ! % % ' 'D     r&levelc|sdS|tjttj||}|jS)zZUpdate enrichment level for multiple references. Returns: Number of rows updated r)r/) rTrdrrrUr_rrr)rCrr5rs r$bulk_update_enrichment_levelr7sh q __ .!! ~ $$]33 4 4  ' 'F ?r&rc|sdSd|D}tjttjg}t |t dD]}|||dS)zBulk insert reference rows with ON CONFLICT DO NOTHING on file_path. Each dict should have: id, asset_id, file_path, name, owner_id, mtime_ns, etc. The is_missing field is automatically set to False for new inserts. Ncg|] }i|ddi S)rFr>r s r$r<z;bulk_insert_references_ignore_conflicts..s(BBBc11\511BBBr&r)r rrrrQrrrT)rCr enriched_rowsrrs r$'bulk_insert_references_ignore_conflictsr<s BBTBBBM - ' ' > >&01 ?  C],H,L,LMM$$U####$$r& path_to_assetc"|stSt|}t}t|tdzD]}t jtjtj  |}| ttj |}|||S)zQuery references to find paths where our asset_id won the insert. Args: path_to_asset: Mapping of file_path -> asset_id we tried to insert Returns: Set of file_paths where our asset_id is present r)rr?rrrrdtuple_rrQrZrrTr rUrrWr@)rCr=pairswinnersrpairwisers r$%get_references_by_paths_and_asset_idsrCs uu $$&& ' 'EGUOq$899//9^5~7NOOSS    >+ , , 2 28 < <   v~~''++--.... Nr&c|stSt}t|tD]}|t t jt j|}| | |S)z8Query to find which reference IDs exist in the database.) rrrrTr rr_rUrrrWr@)rCrfoundrrs r$get_reference_ids_by_idsrF&s uu eeE]O<<-- >$ % % + +N,=,A,A%,H,H I I    V^^%%))++,,,, Lr&)rkNNNr5) rkrrNNNNNNNN)rk)NT)rr0)Yr collectionsrrdecimalrtypingrr sqlalchemyrdrr sqlalchemy.dialectsr sqlalchemy.excr sqlalchemy.ormr r app.assets.database.modelsrrrrr"app.assets.database.queries.commonrrrrrrrapp.assets.helpersrrr%r"r dictr2r?rBrGrPrYrbrrhrjrwtupler}robjectrrrrrrrrrrrrrrrrrrrrrr#r%r'r)r,r.r4r7r<rrCrFr>r&r$rTs  $#####''''''''%%%%%%%%&&&&&&))))))********CBBBBBBB ? ?c ?T ? ? ? ? ;# ;d ; ; ; ;$5 55d5555  &    d      " 2  2 2  2 2 2 2 2  2 2  2 2 2 2& !    Tz  Dj d dF !11 11 1 1 Tz 1 Dj 1d 1 >4  1111n"     d       $)-)-#'(,"&l&l& l&l& l&  l& : l& 3-$& l&3-$&l&D[l& *l& :l& l&4Zl& 4 c49n!5s :;l&l&l&l&d"("( "("("( >5$s) +,t3 "("("("(P  >5 !D( 2 66 66 46 6  6666$          "      4     !!~!$!!!!N"& . .  . .$; . . . . .&$(.. ..D[. ...." 8  8 8 8 8 8 8 88 888 88882(, * *     J    n"  #Y2 66 666  6  6  6 4:6666r I 0d3iC*HHT#YHHHH,'d3iC*" 777 73i7 7  - 7777t %)#Y7;( %)#Y7;(    # ,0      g d3i C     34"Z // /3i//  /  ! ////d 9  &$ $ t*$ $$$$& S> X> 9 Xr&