$j56dZddlmZmZGddeZdS)zAbstract base class for SQL dialect modules. Each method encapsulates a SQL pattern that differs between database platforms. Business logic calls these methods instead of embedding raw SQL fragments. )ABCabstractmethodceZdZdZededefdZedededefdZedededefd Z edededefd Z eded edefd Z edededefd Z edededefdZ edddedededzdefdZedddedededzdefdZedededefdZededeedeedeedef dZedeeeefdefdZedededefdZedeedefd Zedededefd!Zededefd"Zededefd#Zedededefd$Zedefd%Zed&edefd'Zedefd(Zed)edefd*Zedefd+Zed,edefd-Z ed.d.d.d/ded0ed1ed2ed3ed4ed5ed6ed7edefd8Z!ed.d.d9d:d.d;ded0ed1ed3ededed7edefd?Z"ed:d@dAeedBed>edefdCZ#dS)D SQLDialecta SQL dialect interface for portable query construction. Implementors provide database-specific SQL fragments for operations that are not standard across PostgreSQL and Oracle (parameter binding, JSON operators, vector distance, full-text search, etc.). nreturncdS)zReturn the nth positional parameter placeholder. Args: n: 1-based parameter index. Returns: "$1" for PostgreSQL, ":1" for Oracle. N)selfrs e/home/rurouni/.hermes/hermes-agent/venv/lib/python3.11/site-packages/hindsight_api/engine/sql/base.pyparamzSQLDialect.param  r type_namecdS)a?Cast a parameter or expression to the given type. Args: param: The expression to cast (e.g. "$1" or a column name). type_name: Target type (e.g. "jsonb", "uuid[]", "vector"). Returns: Cast expression (e.g. "$1::jsonb" for PG, "CAST(:1 AS ...)" for Oracle). Nr )r r rs r castzSQLDialect.cast"  rcolcdS)amCosine distance expression between a column and a parameter. Args: col: Column name containing the vector. param: Parameter placeholder for the query vector. Returns: Distance expression (lower = more similar). PG: "col <=> $1::vector" Oracle: "VECTOR_DISTANCE(col, :1, COSINE)" Nr r rr s r vector_distancezSQLDialect.vector_distance1s  rcdS)zCosine similarity expression (1 - distance). Args: col: Column name. param: Parameter placeholder. Returns: Similarity expression (higher = more similar). Nr rs r vector_similarityzSQLDialect.vector_similarity@rrkeycdS)zExtract a text value from a JSON/JSONB column. Args: col: Column name. key: JSON key to extract. Returns: PG: "col ->> 'key'" Oracle: "JSON_VALUE(col, '$.key')" Nr )r rrs r json_extract_textzSQLDialect.json_extract_textO  rcdS)aTest whether a JSON column contains the given JSON object. Args: col: Column name. param: Parameter placeholder for the JSON object to test. Returns: PG: "col @> $1::jsonb" Oracle: "JSON_EXISTS(col, ...)" Nr rs r json_containszSQLDialect.json_contains]rrcdS)aMerge (concatenate) a JSON object into a JSON column. Args: col: Column name. param: Parameter placeholder for the JSON to merge. Returns: PG: "col || $1::jsonb" Oracle: "JSON_MERGEPATCH(col, :1)" Nr rs r json_mergezSQLDialect.json_mergekrrN) index_name query_paramr"cdS)aYRelevance score expression for full-text search. Args: col: Column name (text or tsvector/bm25vector). query_param: Parameter placeholder for the search query. index_name: Optional index name (needed by some backends). Returns: Score expression (higher = more relevant). Nr r rr#r"s r text_search_scorezSQLDialect.text_search_score{rrcdS)a2ORDER BY expression for full-text search (ascending = best first). Args: col: Column name. query_param: Parameter placeholder for the search query. index_name: Optional index name. Returns: Expression suitable for ORDER BY ... ASC. Nr r%s r text_search_orderzSQLDialect.text_search_orderrrcdS)a!Fuzzy string similarity score between a column and a parameter. Args: col: Column name. param: Parameter placeholder. Returns: PG: "similarity(col, $1)" Oracle: "UTL_MATCH.EDIT_DISTANCE_SIMILARITY(col, :1) / 100.0" Nr rs r similarityzSQLDialect.similarityrrtablecolumnsconflict_columnsupdate_columnscdS)aGenerate an upsert statement. Args: table: Fully-qualified table name. columns: All columns in the INSERT. conflict_columns: Columns that form the unique constraint. update_columns: Columns to update on conflict. Returns: Complete INSERT ... ON CONFLICT DO UPDATE (PG) or MERGE INTO ... (Oracle) statement. Nr )r r+r,r-r.s r upsertzSQLDialect.upserts ( r param_typescdS)aoGenerate a bulk unnest/table-value expression. Converts parallel arrays into rows. Args: param_types: List of (param_placeholder, sql_type) pairs e.g. [("$1", "text[]"), ("$2", "uuid[]")] Returns: PG: "unnest($1::text[], $2::uuid[])" Oracle: JSON_TABLE-based equivalent. Nr )r r1s r bulk_unnestzSQLDialect.bulk_unnests  r limit_param offset_paramcdS)a(Generate LIMIT/OFFSET clause. Args: limit_param: Parameter placeholder for row limit. offset_param: Parameter placeholder for row offset. Returns: PG: "LIMIT $1 OFFSET $2" Oracle: "OFFSET :2 ROWS FETCH FIRST :1 ROWS ONLY" Nr )r r4r5s r limit_offsetzSQLDialect.limit_offsetrrcdS)zGenerate a RETURNING clause. Args: columns: Column names to return. Returns: PG: "RETURNING col1, col2" Oracle: "RETURNING col1, col2 INTO :out1, :out2" (handled by backend). Nr )r r,s r returningzSQLDialect.returningrrcdS)zCase-insensitive LIKE expression. Args: col: Column name. param: Parameter placeholder for the pattern. Returns: PG: "col ILIKE $1" Oracle: "UPPER(col) LIKE UPPER(:1)" Nr rs r ilikezSQLDialect.ilikerrcdS)zIN-array membership expression. Args: param: Parameter placeholder for the array. Returns: PG: "= ANY($1)" Oracle: "IN (SELECT ... FROM JSON_TABLE(...))" Nr r r s r array_anyzSQLDialect.array_anyrrcdS)zNOT-IN-array expression (not equal to all elements). Args: param: Parameter placeholder for the array. Returns: PG: "!= ALL($1)" Nr r=s r array_allzSQLDialect.array_allrrcdS)zTest whether an array column contains all elements in the parameter. Args: col: Array column name. param: Parameter placeholder for the array to test. Returns: PG: "col @> $1::varchar[]" Nr rs r array_containszSQLDialect.array_containsrrcdS)z;FOR UPDATE SKIP LOCKED clause (same on both PG and Oracle).Nr r s r for_update_skip_lockedz!SQLDialect.for_update_skip_locked*  rid_paramcdS)zAdvisory lock expression. Args: id_param: Parameter placeholder for the lock ID. Returns: PG: "pg_try_advisory_lock($1)" Oracle: "SELECT ... FOR UPDATE NOWAIT" equivalent. Nr )r rGs r advisory_lockzSQLDialect.advisory_lock/rrcdS)zSQL expression to generate a random UUID. Returns: PG: "gen_random_uuid()" Oracle: "SYS_GUID()" Nr rDs r generate_uuidzSQLDialect.generate_uuid>  rargscdS)z-GREATEST() function (same on both platforms).Nr )r rMs r greatestzSQLDialect.greatestJrFrcdS)zsCurrent timestamp expression. Returns: PG: "now()" Oracle: "SYSTIMESTAMP" Nr rDs r current_timestampzSQLDialect.current_timestampOrLrexprcdS)zAggregate values into an array. Args: expr: Expression to aggregate. Returns: PG: "array_agg(expr)" Oracle: "CAST(COLLECT(expr) AS ...)" or JSON_ARRAYAGG. Nr )r rRs r array_aggzSQLDialect.array_aggYrr) tags_clause groups_clause extra_wherecols fact_typeembedding_param bank_id_param fetch_limitrVrWrXc dS)aABuild a semantic (vector similarity) search subquery arm. Returns a complete subquery suitable for UNION ALL that selects matching rows ordered by cosine similarity. Args: table: Fully-qualified table name. cols: Column list expression. fact_type: Fact type literal (inlined, not parameterized). embedding_param: Parameter placeholder for query embedding. bank_id_param: Parameter placeholder for bank_id. fetch_limit: Max rows to fetch (over-fetched for HNSW approximation). tags_clause: Optional WHERE clause fragment for tag filtering. groups_clause: Optional WHERE clause fragment for tag group filtering. extra_where: Optional additional WHERE clause fragment (e.g. time range filter). Nr ) r r+rYrZr[r\r]rVrWrXs r build_semantic_armzSQLDialect.build_semantic_armls < rrnative)rVrW arm_indextext_search_extensionrX text_paramrarbc dS)a?Build a BM25/full-text search subquery arm. Returns a complete subquery suitable for UNION ALL that selects matching rows ordered by text relevance score. Args: table: Fully-qualified table name. cols: Column list expression. fact_type: Fact type literal (inlined, not parameterized). bank_id_param: Parameter placeholder for bank_id. limit_param: Parameter placeholder for result limit. text_param: Parameter placeholder for the search text. tags_clause: Optional WHERE clause fragment for tag filtering. groups_clause: Optional WHERE clause fragment for tag group filtering. arm_index: Index of this arm in the UNION ALL (used by Oracle for unique SCORE labels). text_search_extension: Full-text search backend ("native", "vchord", "pg_textsearch"). Only relevant for PostgreSQL. extra_where: Optional additional WHERE clause fragment (e.g. time range filter). Nr ) r r+rYrZr\r4rcrVrWrarbrXs r build_bm25_armzSQLDialect.build_bm25_arms H r)rbtokens query_textcdS)aPrepare the text parameter value for BM25 search. Transforms tokens/query text into the format expected by the backend's full-text search engine. Args: tokens: Tokenized query words. query_text: Original query text. text_search_extension: Full-text search backend variant. Returns: Prepared text string to bind as the BM25 text parameter. Nr )r rfrgrbs r prepare_bm25_textzSQLDialect.prepare_bm25_texts * r)$__name__ __module__ __qualname____doc__rintstrr rrrrrr!r&r(r*listr0tupler3r7r9r;r>r@rBrErIrKrOrQrTr_rerir rr rr s  s  s    ^    #  #  #    ^    3  s  s    ^    S        ^    S  s  s    ^      S  S    ^    c  #  #    ^  X\    S  s  3QU:  ad    ^  X\    S  s  3QU:  ad    ^    c  #  #    ^    c s)  S     ^ .  tE#s(O'<      ^  "    3  3    ^    c  s    ^      S  S    ^    s  s    ^    s  s    ^    #  c  c    ^      ^   c  c    ^   s   ^  c c   ^  3   ^   c  c    ^  $                   ^ >%-# # # #  #  #  # # # # # #  ## #  # # # ^# J &.    S   #     ^   rrN)rmabcrrrr rr rssg $#######} } } } } } } } } } r