
    $j                     2    d Z ddlmZ  G d de          ZdS )zPostgreSQL SQL dialect implementation.

Provides PostgreSQL-specific SQL fragments for parameter binding, JSON operators,
vector distance (pgvector), full-text search (VectorChord BM25 / tsvector),
and other non-portable patterns.
   )
SQLDialectc                      e Zd ZdZdedefdZdededefdZdededefd	Zdededefd
Z	dededefdZ
dededefdZdededefdZdddedededz  defdZdddedededz  defdZdededefdZdedee         dee         dee         def
dZdeeeef                  defdZdededefdZdee         defd Zdededefd!Zdedefd"Zdedefd#Zdededefd$Zdefd%Zd&edefd'Zdefd(Zd)edefd*Zdefd+Zd,edefd-Zd.d.d.d/ded0ed1ed2ed3ed4ed5ed6ed7edefd8Z d.d.d9d:d.d;ded0ed1ed3eded<ed5ed6ed=ed>ed7edefd?Z!d:d@dAee         dBed>edefdCZ"dS )DPostgreSQLDialectz%SQL dialect for PostgreSQL (asyncpg).nreturnc                     d| S )N$ )selfr   s     k/home/rurouni/.hermes/hermes-agent/venv/lib/python3.11/site-packages/hindsight_api/engine/sql/postgresql.pyparamzPostgreSQLDialect.param   s    1ww    r   	type_namec                     | d| S )N::r
   )r   r   r   s      r   castzPostgreSQLDialect.cast   s    &&9&&&r   colc                     | d| dS )N <=> z::vectorr
   r   r   r   s      r   vector_distancez!PostgreSQLDialect.vector_distance   s    ++E++++r   c                     d| d| dS )Nz1 - (r   z	::vector)r
   r   s      r   vector_similarityz#PostgreSQLDialect.vector_similarity   s    1s111111r   keyc                     | d| dS )Nz ->> ''r
   )r   r   r   s      r   json_extract_textz#PostgreSQLDialect.json_extract_text"   s    ##S####r   c                     | d| dS )N @> ::jsonbr
   r   s      r   json_containszPostgreSQLDialect.json_contains%       ))5))))r   c                     | d| dS )Nz || r    r
   r   s      r   
json_mergezPostgreSQLDialect.json_merge(   r"   r   N)
index_namequery_paramr%   c                0    |rd| d| d| dS d| d| dS )N-( <@> to_bm25query(, 'z'))ts_rank_cd(, to_tsquery())r
   r   r   r&   r%   s       r   text_search_scorez#PostgreSQLDialect.text_search_score-   sE     	ONNN{NNzNNNN>S>>{>>>>r   c                .    |r| d| d| dS d| d| dS )Nr)   r*   z') ASCr+   r,   z)) DESCr
   r.   s       r   text_search_orderz#PostgreSQLDialect.text_search_order4   sC     	POO[OOZOOOOCSCC{CCCCr   c                     d| d| dS )Nzsimilarity(, )r
   r   s      r   
similarityzPostgreSQLDialect.similarity<   s    ,S,,E,,,,r   tablecolumnsconflict_columnsupdate_columnsc           
      J   d                     |          }d                     d t          t          |                    D                       }d                     |          }|sd| d| d| d| d	S d                     d |D                       }d| d| d| d| d	| 
S )
Nr3   c              3   &   K   | ]}d |dz    V  dS )r	   r   Nr
   ).0is     r   	<genexpr>z+PostgreSQLDialect.upsert.<locals>.<genexpr>I   s,       J JQU J J J J J Jr   zINSERT INTO z (z
) VALUES (z) ON CONFLICT (z) DO NOTHINGc              3   $   K   | ]}| d | V  dS )z = EXCLUDED.Nr
   )r<   cs     r   r>   z+PostgreSQLDialect.upsert.<locals>.<genexpr>O   s0      JJaq11a11JJJJJJr   z) DO UPDATE SET )joinrangelen)	r   r6   r7   r8   r9   col_listplaceholdersconflictupdatess	            r   upsertzPostgreSQLDialect.upsertA   s     99W%%yy J JeCLL6I6I J J JJJ99-.. 	sr%rr8rr|rr\drrrr))JJ>JJJJJx5xxHxxxxU]xxovxx	
r   param_typesc                 L    d                     d |D                       }d| dS )Nr3   c              3   *   K   | ]\  }}| d | V  dS )r   Nr
   )r<   pts      r   r>   z0PostgreSQLDialect.bulk_unnest.<locals>.<genexpr>W   s0      ==AA======r   zunnest(r4   rA   )r   rI   argss      r   bulk_unnestzPostgreSQLDialect.bulk_unnestV   s3    yy=======     r   limit_paramoffset_paramc                     d| d| S )NzLIMIT z OFFSET r
   )r   rQ   rR   s      r   limit_offsetzPostgreSQLDialect.limit_offset\   s    ;;;\;;;r   c                 2    dd                     |           S )Nz
RETURNING r3   rN   )r   r7   s     r   	returningzPostgreSQLDialect.returninga   s    0DIIg..000r   c                     | d| S )Nz ILIKE r
   r   s      r   ilikezPostgreSQLDialect.ilikef   s    %%e%%%r   c                     d| dS )Nz= ANY(r4   r
   r   r   s     r   	array_anyzPostgreSQLDialect.array_anyk   s         r   c                     d| dS )Nz!= ALL(r4   r
   rZ   s     r   	array_allzPostgreSQLDialect.array_alln   s    !!!!!r   c                     | d| dS )Nr   z::varchar[]r
   r   s      r   array_containsz PostgreSQLDialect.array_containsq   s    --5----r   c                     dS )NzFOR UPDATE SKIP LOCKEDr
   r   s    r   for_update_skip_lockedz(PostgreSQLDialect.for_update_skip_lockedv   s    ''r   id_paramc                     d| dS )Nzpg_try_advisory_lock(r4   r
   )r   rc   s     r   advisory_lockzPostgreSQLDialect.advisory_locky   s    2x2222r   c                     dS )Nzgen_random_uuid()r
   ra   s    r   generate_uuidzPostgreSQLDialect.generate_uuid~   s    ""r   rO   c                 4    dd                     |           dS )Nz	GREATEST(r3   r4   rN   )r   rO   s     r   greatestzPostgreSQLDialect.greatest   s    -499T??----r   c                     dS )Nznow()r
   ra   s    r   current_timestampz#PostgreSQLDialect.current_timestamp   s    wr   exprc                     d| dS )Nz
array_agg(r4   r
   )r   rl   s     r   	array_aggzPostgreSQLDialect.array_agg   s    #D####r    )tags_clausegroups_clauseextra_wherecols	fact_typeembedding_parambank_id_paramfetch_limitrp   rq   rr   c       	         J    d| d| d| d| d| d| d| d| d|	 d	| d
| dS )N(SELECT z,        1 - (embedding <=> z\::vector) AS similarity,        NULL::float AS bm25_score,        'semantic' AS source FROM  WHERE bank_id =    AND fact_type = 'z8'   AND embedding IS NOT NULL   AND (1 - (embedding <=> z::vector)) >= 0.3      z ORDER BY embedding <=> z::vector LIMIT r4   r
   )
r   r6   rs   rt   ru   rv   rw   rp   rq   rr   s
             r   build_semantic_armz$PostgreSQLDialect.build_semantic_arm   s    %t % %*9% % 	% %
 !.% % $-% % +:% % % %  % % % % (7% % "% % %	
r       native)rp   rq   	arm_indextext_search_extensionrr   
text_paramr   r   c                    |
dk    rd| d}| d}d}n)|
dk    rd| d| d	}d
| d}d}nd| d}| d}d| d}d| d| d| d| d| d| d| d| d| d| d| dS )NvchordzJ-(search_vector <&> to_bm25query('idx_memory_units_text_search', tokenize(z, 'llmlingua2')))z DESCro   pg_textsearchr(   r)   z", 'idx_memory_units_text_search'))ztext <@> to_bm25query(z%, 'idx_memory_units_text_search') ASCz0ts_rank_cd(search_vector, to_tsquery('english', r-   z+AND search_vector @@ to_tsquery('english', r4   ry   z+,        NULL::float AS similarity,        z- AS bm25_score,        'bm25' AS source FROM rz   r{   z'   r|   z
 ORDER BY z LIMIT r
   )r   r6   rs   rt   rv   rQ   r   rp   rq   r   r   rr   bm25_score_exprbm25_order_bybm25_where_filters                  r   build_bm25_armz PostgreSQLDialect.build_bm25_arm   s^    !H,, Iku  I  I  IO.555M ""o55o:oooooOfZfffM "_Q[___O.555M [j [ [ [%t % %&% % 	% %
 !.% % $-% % $% % % %  % % % % '% % "% % %	
r   )r   tokens
query_textc                8    |dv r|S d                     |          S )N)r   r   z | rN   )r   r   r   r   s       r   prepare_bm25_textz#PostgreSQLDialect.prepare_bm25_text   s(     !$???zz&!!!r   )#__name__
__module____qualname____doc__intstrr   r   r   r   r   r!   r$   r/   r1   r5   listrH   tuplerP   rT   rV   rX   r[   r]   r_   rb   re   rg   ri   rk   rn   r}   r   r   r
   r   r   r   r      s       //s s    
'# '# '# ' ' ' '
,3 ,s ,s , , , ,2S 2 2 2 2 2 2
$S $s $s $ $ $ $* *S *S * * * **c *# *# * * * *
 Y] ? ? ?S ?s ?3QU: ?ad ? ? ? ? Y] D D DS Ds D3QU: Dad D D D D-c -# -# - - - -


 c
 s)	

 S	
 

 
 
 
*!tE#s(O'< ! ! ! ! !< <3 <3 < < < <
1c 1s 1 1 1 1
& &S &S & & & &
!s !s ! ! ! !"s "s " " " ".# .c .c . . . .
( ( ( ( (3c 3c 3 3 3 3
#s # # # #
.c .c . . . .3    $c $c $ $ $ $ 
 
 
 
 	

 
 
 
 
 
 
 
 

 
 
 
N %-+
 +
 +
 +
 	+

 +
 +
 +
 +
 +
 +
 +
  #+
 +
 
+
 +
 +
 +
d &.
" 
" 
"S	
" 
"
  #
" 

" 
" 
" 
" 
" 
"r   r   N)r   baser   r   r
   r   r   <module>r      sa          X" X" X" X" X"
 X" X" X" X" X"r   