iddlZddlZddlZddlmZddlmZmZmZm Z ddl m Z m Z m Z ddl Z ddlZddlZddlZddlTddlmZddlZddlZddlZddlZddlZddlZddlZddlmZejZgdZ gd Z!gd Z"e"gd z Z"e"gd z Z"e"d gz Z"e"gdz Z"gdZ#gdZ$e%e e!ze"ze$ze#zZ"gdZ&gdZ'e%e e!ze$zZ(gdZ)ddZ*ddZ+Gddej j,j-Z-dS)N) namedtuple)ListSetDictOptional) dataclassfieldfields)*)SQLAlchemyDataDriver) BeautifulSoup)иввонечтооннаяссокакатовсеонатакегонодатыкужевызабыпо толькоеемнебыловототменяещенетоизему теперь когдадажену вдруглиеслиужеилинибытьбылнегодовас нибудь опятьужвамведьтам потомсебя ничегоей можетонитутгдеестьнадонейдлямытебяихчембыласамчтоббез будточегоразтожесебепод будетж тогдактоэтоттого потому этого какой совсемним здесьэтомодин почтимойтем чтобынее сейчасбыликуда зачемвсехникогда можнопринаконецuдваоб другойхоть посленадu большетот черезэтинаспро всегоних какаяu много развеuтриэтумоявпрочемu хорошосвоюэтой перед иногда лучшечутьтомu нельзя такойим более всегдаконечновсю между)imemymyselfweourours ourselvesyouyouryoursyourself yourselveshehimhishimselfsheherhersherselfititsitselftheythemtheirtheirs themselveswhatwhichwhowhomthisthatthesethoseamisarewaswerebebeenbeinghavehashadhavingdodoesdiddoingaantheandbutiforbecauseasuntilwhileofatbyforwithaboutagainstbetweenintothroughduringbeforeafterabovebelowtofromupdowninoutonoffoverunderagainfurtherthenonceheretherewhenwherewhyhowallanybotheachfewmoremostothersomesuchnonornotonlyownsamesothantooverystcanwilljustdonshouldnow)u сделаuделuсделuделаэтrrrrrrrrrrrruвсёrrrrr r!r"r#r$r%r&r'r(r)rrr?r-r.ruещёr1r2r3rr5r6r7r8r9r:r;r<r=r>r?r?rrBr%rDu нибытьrErFr%rHrIrJrKrLruмочьrOrPrQrRrSrrUrVr!rOrYr?r[r\r]r^rr`rarKrcr?rerfrgrhrrjэтоrlrmrOrorrqrrrsrtrurrwr?ryrzвесьr|r}r~rr2rrrrrrrhrVrr rOrlrrhrsruсвойrhrrrrrrOrrrr r).u такоеrirrrrWrMrZrrdrr+r4rnrAr0rrrkrbr{rrGrrNrCr*rrrprxr/rrTrrr,rrXrDrrr_r@rrv)u нужноuхотяuзатоu именноu вообщеuтудаuтакиu делатьuпомнитьu менееuоноu кромеuтвойu откудаuсчитатьвнеuтойu каждыйu никтоuкоторыйu затемu вопросuобаuдлит)rlruещuнибuмочrrвеr"uсвоr"ruнужнuхотзатu вообщuтудruменruкромuтвоr!ruкаждuниктu которr#r) rrlu почемуryrgrQr6u можешьrY)docwwwhttphttpsmailto)u авторuбизнес-процессuвладелецuдатuисполнителuконтрагентuлогическu отделuпостановщикuприоритетu проектuпроцессu спискuстандартнu статусuтип)approvbaseclosedefaultdocumentopentasku бизнесuдокументu задачu задачаu закрытu обычнu системu созданuчерновик)textml_textnametagscomments addon_fields key_phrasesc,ddlm}m}ddlm}||}|D]/\}}||jvr||||}0t| | ddiS)Nr)r0 bindparam) postgresql)value literal_bindsT)dialectcompile_kwargs) sqlalchemy.sqlr0r8sqlalchemy.dialectsr9items _bindparams bindparamsstrcompiler<)sqlparamsr0r8r9stmtkvs ./cmf/models/cmf_full_search.pysql_debug_not_workrK?s.............. 499D||~~44!D$$$  __YYq222 3 3 t||J$6$6$8$8Z^H_|`` a aac(|}|D]z\}}t|ttfrt jd|d|d|}?|t jd|dd|}\t jd|dd|d|}{|S)Nz(:z([^1-9a-zA-Z_]))z\2zNULL\2"z"\2)r@ isinstanceintfloatresub)rErFresrHrIs rJ sql_debugrUJs C  F F1 a#u & & F&2q222IIIsCCCC Y&2q222JDDCC&2q222KKKKEECC JrLc~eZdZdZdZgdZdZgdZedZ edZ edZ ed Z ed Z ed5d Zed6dZed7dZedZedZe d8dZedZedZedZedejjfdZed9dZeedd:dZedZeedddd !d"Z ed#Z!ed$Z"e d;d%Z#e dd*Z%ed+Z&ed?d0Z'ed@d3Z(eed4Z)dS)A CmfFullSearchuC Сервис полнотекстового поиска. russian)r2 text_renderr0 text_draftr3 parent_idtree_parent_id project_idcmf_created_atcmf_modified_atcode cmf_deleted cmf_archivedloginemailphonephone_internal phone_mobilephone_2phone_assistant ip_addressemail_2birthdayT)fulltext_searchrun_force_reindex index_statsct|dddS)Nz html.parser ignore)errors)r get_textencodedecode)clstext_with_htmls rJ strip_htmlzCmfFullSearch.strip_htmls>^];;DDTJJQQYaQbbiikkkrLcddtkrgSfdtdtD}|S)Nidc0g|]}||zzSr}).0roverlapr0 text_len_maxs rJ z6CmfFullSearch._split_size_to_parts..s,YYY!tAa nW,,-YYYrLr)lenrange)rwr0rTrrs ` @@rJ_split_size_to_partsz"CmfFullSearch._split_size_to_partss_  t99| # #6MYYYYYYuQD <7X7XYYY rLcfddtjd|DS)N c<g|]}|t|dk|S)2)rr~ws rJrz+CmfFullSearch._strip_50..s(OOOqQO3q66B;;;;;rLz\sjoinrRsplitrwr0s rJ _strip_50zCmfFullSearch._strip_50s/xxOOBHUD$9$9OOOPPPrLcfddtjd|DS)NrcHg|]}|tv| Sr})lowerALL_STOP_WORDSrs rJrz1CmfFullSearch._strip_not_word..s+nnnqaggii_mNmNmNmNmNmrLz[ \s<>|&\/%@!`+=;.]rrs rJ_strip_not_wordzCmfFullSearch._strip_not_words2xxnnBH-CT$J$JnnnooorLcddl}d}d|d|}|jj|dd|i}|S)NrzCmfFullSearchLock:_zKSELECT pg_try_advisory_xact_lock(('x' || md5(:lock_key))::bit(64)::bigint);lock_key) sqlalchemydp_ddSessionexecuter0scalar)rwobj_idsapart_norrTs rJ_fullsearch_obj_lock_get_pgz)CmfFullSearch._fullsearch_obj_lock_get_pgs:::::fj  ""**2774A,B,B D   688  rLFctj}td|d|z}|stdddl}|jj}||}|j}| } | |} | s td|ddSd} | |j j g|j j|k|j j| k} | | } | r| d}||j j |kd|j}| |ntd ||}|||d|j| | }| ||r%dd lm}|t8jjd |id tj|z dkr3tdtj|z dSdS)Nzcmf_full_search: mark_dirty z DEBUG_MSG: empty obj_idr)cmf_full_search: _text_search_sql_insert  lockedT)is_dirtydirty_atz#cmf_full_search: mark_dirty insert )idrrrrobj_name)schedule_deferred_jobr)kwargs countdowng?zPROF mark_dirty() got )timegdebug ValueErrorrr data_driver dp_model_cls __table__rrselectcrrrrwith_for_updaterfirstupdatevaluesfuncrgen_idinsert cmf.includermodels CmfPersoncelery_full_search_index)rwr fast_index debug_msgprof_strddsa_modeltablerrTrget_stmtget_resid_ update_stmt insert_stmtrs rJmark_dirty_deferzCmfFullSearch.mark_dirty_defers)++ 7v77888!I-  ._-- - V ??3''" JJLL--f55  GGOOOO P P P F IIuwzl # #uw~/00uw'122 ""  ))H%%++--  #!*C U57:,--V!W[[]]  IIk " " " " GGB&BB C C C**,,C V!!W[[]]#&   IIk " " "  u 9 9 9 9 9 9 ! !&"2"KU]_eTfrs t t t t 9;; 4 ' ' GGDTY[[7-BDD E E E E E ( 'rLc|r|||Stj|}|r|ddkrd}||dtj|<t tjdkrt ddSdS)N)rrTrri'uDEV: WARNING! Большое количество блокировок может падать на некоторых инсталяциях)rrdeferred_fullsearch_dirty_listgetr cmf_alert)rwrrforcerprevs rJ mark_dirtyzCmfFullSearch.mark_dirtys  G'':'FF F/33F;;  D&,,JBL[d3e3e(0 q/ 0 0E 9 9 f g g g g g : 9rLc|rit_dStjsdStj}t|dkrtdt|t |D]H}||}|||d|dIit_dS)NruaDEV: CmfFullSearch помечено грязными слишком много объектов: rrr)rrrrsortedkeysrr)rw only_clean dirty_listrrs rJapply_deferred_dirtyz"CmfFullSearch.apply_deferred_dirtys  /1A , F/  F5 z??R   Lz}I{J{JLL M M MZ__..// q qF'F  FJJ|4L4LX^XbXbcnXoXo p p p p+-(((rLc|stdddl}|jj}||}|j}|}||jj g |jj |k |jj dk }||}|r|d} | |jj | kdd|j} || |jdS)NrrF)ris_dirty_patchr)rrrrrrrrrrrrrrrrrrrrcommit rwrrrrrrrrrrs rJ mark_cleanzCmfFullSearch.mark_clean sK ._-- - V ??3''" JJLL IIuwzl # #uw~/00uw!+,, ""  ))H%%++--  #!*C U57:,--V"#(W[[]]  IIk " " "  rLc:|stdddl}|jj}||}|j}|}||jj g |jj |k |jj dk }||}|r|d} | |jj | k|jt)jdz} || |jdS)Nrr)hours)delay_error_reindex)rrrrrrrrrrrrrrrrrrrrdatetime timedeltarrs rJmark_delay_error_reindexz&CmfFullSearch.mark_delay_error_reindex)sY ._-- - V ??3''" JJLL IIuwzl # #uw~/00uw!+,, ""  ))H%%++--  #!*C U57:,--V(* 8JQR8S8S8S(S  IIk " " "  rLN 0_full_indexc& |dkr0|||jdS||dd}|}&d}'|r|'|z }'| r|'d| zz }'| r|'d| zz }'|!r|'d|!zz }'|&d|!zz }&||'}(||(}(|dkrk|rI||})||)}*|*d})||)}+n#|dd})|)g}*||)}+|r-|}||},||,},nd}d},| r5|| } | dd} || }-nd} d}-| r5|| }.|.dd}.||.}/nd}.d}/|rBt |}0||0dd}0||0}1nd}0d}1|!r|!}!|!}2n&d}!d}2n!|d krdg}*d}+d}d},d} d}-d}.d}/d}0d}1d}!d}2d})d}3|r|}3|!r|!dddz|3z}3d}4|rI|4dd t| Ddd z }4|r|4d |ddzz }4|4d| zz }4||3}3||4}4||3}5||4}6|5 d}7|6 d}8|7|8z}9|9d krn>|9d z }:|7d kr|8dkr_d|5 dd}5d|6 dd}6nd|5 dd|8z }5|6}6n|7dkr5|5}5d|6 dd|7z }6n^d|5 dd}5d|6 dd}6|dz|3zdz|4z};|;dd};|; };||;}<|j d|>})||)}+|j dz-CmfFullSearch.index_object..s"*Z*Z1*Z*Z*Z*Z*Z*ZrLiu Тэги i>ii*i:rr obj_model obj_parent_idobj_tree_parent_idobj_project_idobj_company_idobj_created_atobj_modified_atobj_code obj_deleted obj_archivedobj_owner_nameobj_author_nameobj_modified_by_nameobj_responsible_namesobj_hrefobj_logic_type_codeobj_activity_codeobj_status_typeobj_text text_for_vecr name_for_vecobj_tags tags_for_vecobj_result_textresult_text_for_vec obj_ml_textml_text_for_vec obj_commentscomments_for_vecobj_addon_fieldsobj_addon_fields_for_vecobj_user_ratingobj_key_phraseskey_phrases_for_vecobj_breadcrumbsobj_related_person_loginsobj_user_portal_topobj_client_portal_topr)delete_from_partnor})'_text_search_sql_delete_obj_with_childsrrrrrrCrsetrcountrr_text_search_sql_insertr enumerate_text_search_sql_delete_partno)?rw model_namerrr2r0 text_prefix text_suffixfull_search_type company_name project_namemodel_verbose_namer comments_textrrrrrrrrrrrrrrrrrobj_addon_fields_dictrrrrrrr smart_namerr obj_text_listrrrobj_comments_textcomments_text_for_vecr rrobj_ml_text_mainobj_ml_text_suffixml_text_main_for_vecml_text_suffix_for_vecmain_token_lensuffix_token_lentotal_token_len oversize_lenr r r obj_text_parts? rJ index_objectzCmfFullSearch.index_objectFs T { * *  7 7 ? ? ? FMMOOO FmmK00$7    $ J  - # , ,J  - # , ,J  4 #/ /J  O3 3H**:66 }}\22 ~ - - ===.. # 8 8 B B ,A.0"228<< )%4%02!) "228<<  $#"228<< "}}\:: #  +"%--"@"@"1'6'":&)&9&9/&J&J##"&&*# -$'MM-$@$@!$5gvg$>!(+(;(;+>?O+P+P((#' +/( +"1&5###'&*##  ) )!FMLHL"O"&  $ $( !# '+ $"O"& H  $#   O.uu5;>NN   b #((*Z*Zc:O:V:V:X:X6Y6Y*Z*Z*Z"Z"Z[`\`[`"a a   A ,%4%"@ @ cM11==)9:: ]]+=>>"223CDD!$!4!45G!H!H-33C88177<<(+;; V # # +V3L&&#u,,+.884H4N4N4P4PQXRXQX4Y+Z+Z(-0XX6L6R6R6T6TU[V[U[6\-]-]**+.884H4N4N4P4PQqR`aqRqQq4r+s+s(-C**&((';$),2H2N2N2P2PQoR`aoRoQo2p)q)q&&(+xx0D0J0J0L0LWfW0U'V'V$),2H2N2N2P2PQWRWQW2X)Y)Y&"C'*::S@CUU !'6'* "'')) --k:: $#   AA ff  '-  ;J? X ,7; FR\ cqbp ,O  CWBV  #8"7  CK(  !4 3  HYGX  kzjy  X -9L X -9L X -9L ,O BUAT $  6E_ +* >S=R .- IaH` ,O ,O! BUAT! ",O# $'@&?% &!4 3' (#8"7)  , }   ! !*3M!""4Eq*I*I  &,-"228<< ++GGFFV`V`"/-DVDVguguGUGU#1>CR?&X4?;NZ\kyjx%4O K_J^  +@*?  KS(  )<(;  PaO`  sBsB &X5AL!%7;d&X59D"T15%)D?Cd"&9=&6%5QiPh%4O %)D! ?Cd!"%4O#$/H.G%&)<(;'(+@*?), **6c-FXFX*YYY  rLc`|stdddl}|jj}||}|j}|}|||j j |k|j j |kz}| |dSNrr) rrrrrrrdeleterrrrr)rwrrrrrrdel_stmts rJrz5CmfFullSearch._text_search_sql_delete_obj_with_childsGs ._-- - V ??3''" JJLL99U##)) W "f , W^v % '   (rLc|stdddl}|jj}||}|j}|}|||j j |k|j j |k}| |dSr5) rrrrrrrr6rrrrr) rwrrrrrrrr7s rJrz,CmfFullSearch._text_search_sql_delete_partno[s ._-- -V ??3''" JJLL IIe   U57>V+ , , U57?&88 9 9  (rLc*  td||stdddl}*|jj}+|+|},|,j}-|+}.| |}/|/s td|ddS|* |-j j g |-j j|k |-j j|k}0d}1|r|dd}1t%|.|0}2|2rMt)|2dkrt+d|d|d d |2dd}3|- |-j j |3kjdV+ , , U57?g- . . _       ;,22377: qyy**++ n #7||aO6OOGOOOW[\\\\!*Q-C uwzS())///"6/#U / $)5 / (i /&X/&X/!, /&X/%4O/"./&6%5/#0-/&6%5/ (:'9!/"$2>#/$$2>%/&$2>'/(%4O)/*#%&+"9"9#/<"X"X"X+/,#%&+"9"9#/<"X"X"X-/.&(V[%<%$2>?/@%4OA/B*>)=C/D+@*?E/F&XG/H)<(;I/J'8&7K/L%4OM/N%4OO/P%4OQ/R*,)@)@Re)f)f)fS/T%4OU/V/H.GW/X8:v{7N7Ns`y7z7z7zY/Z)<(;[/\+@*?]/ d IIk " " " " GGOvOO P P P**,,C 000s0#U0$)5 0 $G 0 "6 0(i0&X0&X0!, 0&X0%4O0".0&6%50#0-0 &6%5!0"(:'9#0$$2>%0&$2>'0($2>)0*%4O+0,#%&+"9"9#/<"X"X"X-0.#%&+"9"9#/<"X"X"X/00&(V[%<%".?0@$2>A0B%4OC0D*>)=E0F+@*?G0H&XI0J)<(;K0L'8&7M0N%4OO0P%4OQ0R%4OS0T*,)@)@Re)f)f)fU0V%4OW0X/H.GY0Z8:v{7N7Ns`y7z7z7z[0\)<(;]0^+@*?_0 f IIk " " " rLobjcl|jsdS|jD]"}|dkr ||jvr||jrdS#dS)Nr_T) full_searchrequired_fieldsr is_changed)rwrJ field_names rJis_obj_need_reindexz!CmfFullSearch.is_obj_need_reindexs_  F-  J...SZ''C O,F'tt   rLr{c tjjD]$}|js |j}|r||vrd}t |}|j}|rdg} tj } | ||||zgdgdd} | sn| D]D} |r,tj | j jd0| E|t!| z }|jt&d|d|d tj | z d d &dS) NrrTr^)r sliceorder_byinclude_archivedinclude_deleted)rzCmfFullSearch.reindex_models: r:z, z0.3z sec)cmfr CmfEntityiter_subclassesrL class_namecmfutilget_model_by_namefull_search_preload_fieldsrrGrWrrr:full_search_indexrrrrr) rw models_list commit_everylazy model_clsroffsetmodel field_listtsobj_listrJs rJreindex_modelszCmfFullSearch.reindex_modelss-==?? l lI( "-J z<<F--j99E9J $"V  lY[[ ::%!6,#67./%)$( & #00C0,77 D7QQQQ--////#h--' jjjfjjPTPYP[P[^`P`jjjjkkk' l+ l lrL) only_oncecN|r|stddtdtjtj}|jj}| |}|j }| }d}|rd}td|dd d  d |d |d }| |} t| } |t!| rtdd d ntdSt)NuПоиск после обновления должен работать сразу, с приемлемым качеством без переиндексацииTr;zRun run_force_reindexrrzrun_force_reindex Process z=true: r)endflushz WITH cte AS ( SELECT id as id FROM cmf_full_search WHERE z=false AND part_no=0 ORDER BY dirty_at DESC LIMIT 1000 ) UPDATE cmf_full_search s SET zt = true FROM cte WHERE cte.id = s.id RETURNING s.id; .)rrrr CmfAccessListcheck_admin_moderWrrrrrprintrrGrr) from_patchi_do_manual_migrationrwrrrr dirty_fieldrErTs rJrnzCmfFullSearch.run_force_reindex>sf  J3 J }EI J J J J ()))--///" V ??3''" JJLL!  +*K ?;???RtTTTT  $ !C ))C..Cs))C HHJJJ3xx cr.....1 rLctj|}|d}||dS)NT)r)total dirty_count)rrmrnr)rwrtrus rJrozCmfFullSearch.index_stats~sN--/// iii.. &   rLz @minutely)rh system_jobschedulepriorityc  tdtjrtddSt jdt j}d}g} tj dggddgd gd gdd d tj tj d z ggdgddd tj ggdggdgddg}|sn:i}|D]J}t |j}||vrg||<|||jK|D]\}}td|dt#|t%||D]}||jdd|gd} |dz }| stj|gd} d| jd| jd| jd | jd!| j } t| t2j"tj|td#| j | #t:$r} td$|d| d%| t2j"tt=j||| gtAtj!|tEYd} ~ d} ~ wwxYwt j|z d&kr|tj #j$} | dkr"| d krt j|z d'krnbntd(|d)nBtEtd*|d+t jd|r[td,t2j"|D]$\}} t|d-| %|ddtd.t j|z dS)/NzStart cron_index_dirtyuJНе индексируем поскольку запущен импортrTr)r=rOR)rr|T)rr|Tr<)minutes)rr|Nr)rr|Nz -dirty_at)r filterrSrRzcron_index_dirty process rrr|)r rrUr)rrrrr)rr ucron_index_dirty: Не индексируем поскольку объект не найден или удален в бд fullsearch id:z obj name:z obj id: z dirty_at:z is_dirty:)levelzobj_id=u#ERROR! При индексации u" произошла ошибка: -uacron_index_dirty превышен лимит времени 20 секунд обработано u объектовu&cron_index_dirty обработано u объектов, sleep 1u2При индексации были ошибки:z: zEnd cron_index_dirty at )%rrrZenable_import_modeimport_is_runningrsleeprrWslistrrrget_model_by_idrappendr@rrorr\rrrrloggingERRORrr] Exception traceback format_exc cmf_rollbackr cmf_commit astimezonehour)stn exceptions obj_id_listobj_ids_by_modelrrcid_listrrJ search_rawerr_strers rJcron_index_dirtyzCmfFullSearch.cron_index_dirtys ()))  % 7 7 9 9  GG` a a a F 1 Y[[  F  .44XJ*=*=*=+/1H1H1HJgJgJg*h+/-7h>O>S>S>U>UX`XjstXuXuXu>u,v,C,C,C+, *.-BCIZI^I^I`I`,a,N,N,N)*.("1<}-.G'555K( ! % > >// >> 000.0$U+ '..v}===="2"8"8":": % %wJEJJCLLJJKKKg"%%C))5+KUY[^`cTdvz){{CFA !%+%9%=%=SRFRFRF%=&G&G #B1;#B#BJTJ]#B#Bhrhy#B#BFPFY#B#B,6,?#B#Bw}===,TTUXYYY GG.cf..///%--////$%%% vc v vC v vst v vFLMMM 4 6 6777"))3(333$,EEcJJJ" %!%0y{{R"$$(,,..99;;@199y{{R'",,-GGUABUUUVVV LLL GGZQZZZ [ [ [ JqMMMMF P  # GGIQXQ^G _ _ _$ ' 'Q3 ! &&&&Q-" " ;49;;r>;;<<<<|5pdd?| jpdd@| jpddA| jdB|.d||$|%|||||| |!|"|#|||dC|(|*}7t8jjj !|6|7"}8tG|8S)EN6Недопустимое значение field_name: Tr;obj_r2r1r _tsvectorc*g}g}g}d}|dgkrd}ttdr tjstt jdd|D]}||d|d|d|fStjd|}tjd |}tt jdd|D]}|tjvs|tj|vr||:tj|||kr||ltj|||kr||||d|d|d|fS) NdiCmfTasktiFTS_STAT_WORDS[()!|&']r | FST_FRQ_MID FST_FRQ_LOW) hasattrAPPrrrRrSrrr) tsqueryr query_high query_mid query_lowselectortokenfrq_midfrq_lows rJtsquery2idfqueryz.tsquery2idfquerysJIIH ++3 011 *9K *  3!@!@!F!F!H!HII,,E$$U++++zz*--JJy))JJy))** (7AG(7AGRVJsG<<BBDDEE - - 222hcFXY^F_6_6_$$U++++'.x87BB$$U++++'.x87BB$$U++++%%e,,,,::j))JJy))JJy))* *rL0z&ts_rank(setweight(name_tsvector,'C')||z, :query_high)zts_rank(setweight(z,'C'), :query_high)z&ts_rank(setweight(name_tsvector,'B')||z , :query_mid)z,'B'), :query_mid)z&ts_rank(setweight(name_tsvector,'A')||z , :query_low)z,'A'), :query_low)rrr z\ or (obj_ml_text LIKE '%' || :query_like || '%' or obj_text LIKE '%' || :query_like || '%') z or z LIKE '%' || :query_like || '%' r[rrrrrrrrr AND obj_tree_parent_id IN ('{}')','related_person_loginrr$obj_modified_at > now() - interval ' days' ANDr0@BrruD SET gin_fuzzy_search_limit=100000; SET default_text_search_config = 'russian'; SELECT subs.*, subs.obj_name as title, ts_headline( 'russian', substring(COALESCE(subs.headline_raw, subs.obj_name) FROM 0 FOR 100000), plainto_tsquery(:param_search_query), 'MaxFragments=1,MaxWords=25,MinWords=24' ) AS headline FROM ( ( SELECT obj_id, obj_code, {headline_field} as headline_raw, obj_model, --obj_name || ' - ' || rank as title, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then 'R' else '' end) as label, --- Множители сбросили на 1 тк выше уже есть умножение через A B C D но можно убрать A B C D Сделать только С а множители расставить уже здесь {txt_tsrank_high_string} + {txt_tsrank_mid_string} * 1 + {txt_tsrank_low_string} * 1 as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, {tsvector_field} as result_tsvector, {search_field} as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_main' as mark FROM cmf_full_search WHERE -- obj_code='DOC-006092' and {age_days_subquery} ({search_field} != '' and {search_field} is not null) and ( ( {tsvector_field} @@ :txt_tsquery_str_where_OR --ниже спорная строка часть данных может не найтись сложная проблема релевантности текста и имени and ts_rank({tsvector_field}, :txt_tsquery_str_rank_OR) > 0.0001 ) OR (:query_like is not null and {search_field} LIKE '%' || :query_like || '%') ) -- Это делаем, чтобы исключить отсюда результаты из top1 -- но это может привести к тому, что по top1 ранк низкий а здесь высокий, но мы его исключили -- and not(name_tsvector @@ :tsquery_str_where_OR) and ((obj_model IN :model_name_in)) and (:query_qstop_words_str is null or not(text_tsvector @@ :query_qstop_words_str)) and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) and 1=1 ORDER BY rank desc LIMIT :slice_for OFFSET :slice_from ) UNION ALL ( SELECT obj_id, obj_code, COALESCE({headline_field}, obj_name) as headline_raw, obj_model, --obj_name || ' - ' || rank as title, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then 'R' else '' end) as label, --- Множители сбросили на 1 тк выше уже есть умножение через A B C D но можно убрать A B C D Сделать только С а множители расставить уже здесь {txt_tsrank_high_string} + {txt_tsrank_mid_string} * 1 + {txt_tsrank_low_string} * 1 as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, ml_text_tsvector as result_tsvector, obj_text as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_attach' as mark FROM cmf_full_search WHERE {age_days_subquery} (obj_ml_text != '' and obj_ml_text is not null) and (obj_parent_model != 'CmfPerson') and ( ( ml_text_tsvector @@ :txt_tsquery_str_where_OR --ниже спорная строка часть данных может не найтись сложная проблема релевантности текста и имени and ts_rank(ml_text_tsvector, :txt_tsquery_str_rank_OR) > 0.0001 ) OR (:query_like is not null and obj_text LIKE '%' || :query_like || '%') OR (:query_like is not null and obj_name LIKE '%' || :query_like || '%') OR (:query_like is not null and obj_ml_text LIKE '%' || :query_like || '%') ) and ('{tsvector_field}' = 'ml_text_tsvector') -- Это делаем, чтобы исключить отсюда результаты из top1 -- но это может привести к тому, что по top1 ранк низкий а здесь высокий, но мы его исключили -- and not(name_tsvector @@ :tsquery_str_where_OR) and (obj_parent_model IN :model_name_in and obj_model = 'CmfAttachment') and (:query_qstop_words_str is null or not(ml_text_tsvector @@ :query_qstop_words_str)) and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) and 1=1 ORDER BY rank desc LIMIT 20 OFFSET :slice_from/:slice_for*4 ) UNION ALL ( SELECT obj_id, obj_code, ts_rank(setweight(name_tsvector,'C') || {tsvector_field}, :tsquery_str_rank_OR)|| obj_name ||' '|| {headline_field} as headline_raw, obj_model, --obj_name as title, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then 'R' else '' end) as label, -- (ts_rank(setweight(name_tsvector,'C') || ml_text_tsvector, :tsquery_str_rank_OR, {ts_rank_normalize})) -- * (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 3*365 then -- ((3*365-EXTRACT(days from (now()-obj_modified_at)))/30/100+1) -- else 1 end) as rank, --- Множители сбросили на 1 тк выше уже есть умножение через A B C D но можно убрать A B C D Сделать только С а множители расставить уже здесь {tsrank_high_string} + {tsrank_mid_string} * 1 + {tsrank_low_string} * 1 as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, COALESCE({tsvector_field}, name_tsvector) as result_tsvector, {search_field} as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_name' as mark FROM cmf_full_search WHERE {age_days_subquery} ({search_field} != '' and {search_field} is not null) and ( ( name_tsvector @@ :tsquery_str_where_OR and ts_rank(setweight(name_tsvector,'C') || {tsvector_field}, :tsquery_str_rank_OR) > 0.0001 ) OR (:query_like is not null and {search_field} LIKE '%' || :query_like || '%') -- <% медленная и на ml_text запускать не вариант 10сек -- experimental OR (:search_query_wo_qstop <<% obj_name) ) and (obj_model IN :model_name_in) and (:query_qstop_words_str is null or not(text_tsvector @@ :query_qstop_words_str)) and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) and 1=1 ORDER BY rank desc LIMIT :slice_for OFFSET :slice_from ) UNION ALL /* Дополнительно проверяем Полное совпадение CODE или ID или ilike выдаем с высоким рангом 100 в теории можно объединить с выше через CASE LIKE или с once_top */ ( SELECT obj_id, obj_code, obj_name ||' '|| obj_code || ' ' || obj_id || ' ' || {headline_field} as headline_raw, obj_model, --obj_name, obj_breadcrumbs as breadcrumbs, obj_project_id as obj_project_id, obj_parent_id as obj_parent_id, obj_related_person_logins as obj_related_person_logins, :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then 'R' else '' end) as label, 1000 as rank, -- 100 -- * (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then -- (365-EXTRACT(days from (now()-obj_modified_at)))/30+1 else 1 end) as rank, EXTRACT(days from (now()-obj_modified_at)) as age_days, {tsvector_field} as result_tsvector, {search_field} as result_text, name_tsvector, obj_name, obj_modified_at, obj_user_portal_top, obj_client_portal_top, 'flow_idcodelike' as mark FROM cmf_full_search WHERE ({search_field} != '' and {search_field} is not null) and /* Дополнительно проверяем полное совпадение CODE ID ML_TEXT >2WORDS по ilike с высоким рангом */ ( (:query_obj_code is not null and ( -- не дает бонуса возможно даже хуже obj_code = :query_obj_code or obj_code LIKE '%' || :query_obj_code ) ) or (:query_obj_id is not null and obj_id = :query_obj_id) {idcodelike_like_string} ) and (:query_qstop_words_str is null or not({tsvector_field} @@ :query_qstop_words_str)) and obj_model IN :model_name_in and part_no <= :max_partno and ( :parent_id is null or obj_project_id = :parent_id ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' ) {tags_filter} {rpl_filter} {tree_parent_filter} and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code ) and ( :status_type is null or obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) and ( :archived is null or obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and 1=1 /*ORDER BY obj_id desc -- не будем делать, т.к. тормозить будет*/ -- хак 50 здесь предполагает что запросы делаются по 50 и будем давать больше лайкответов на след слайсах LIMIT 2+:slice_from/25 ) ) as subs )rrrrrrrrtsrank_high_stringtsrank_mid_stringtsrank_low_stringtxt_tsrank_high_stringtxt_tsrank_mid_stringtxt_tsrank_low_stringidcodelike_like_stringparam_search_query param_querysearch_query_wo_qstoptsquery_str_rank_ORtsquery_str_where_ORtxt_tsquery_str_rank_ORtxt_tsquery_str_where_ORrrrrquery_qstop_words_strr query_obj_idrr)rrrrrrrrrrrrrrr})$rOrrrrrrRrrrrrrrrrrrPrrRrSEVA_OR_QUERY_STOP_WORDS_NORMr search_queryrrrrrrWrrrrfetchallrG)9rwrqueryrrrrforce_related_person_loginrrrOrrrrrrrrrrrrrrrRr[rrrrrrrrrrrrrrrrrrrrquery_token_listrrrrrrrErFrs9 rJsearch_once_top_bm25z"CmfFullSearch.search_once_top_bm25s&:): ^++ [z[[cg h h h h*j** , ,,   :#:#:'N&111 * * *D,<+;E=+Q+Q( Iy !$ # #  b!lN!l!l!l %aN%a%a%a "  ` j> j j j $_>$_$_$_ !  ` j> j j j $_>$_$_$_ !"$   cV%6%<%> !.223EFF!.223EtLL -112CTJJ)--mTBB #0445IJJ"/334GHH?.)--m<< (,,\4@@ )--mZHH .; #6j|E?FFuzzRdGeGefffCE# #9&/#J#J [ % %(%L%LMg%h%h "J  $ $%; < < %(%L%LVM`MdMde{M|M|%}%} "J JJ1X !HuQx'   [(mm8ZhZZZ     Jrvje<<BBDDEE %zz*:SA]=^=^*^__$zz*:SA]=^=^*^__"5#7   $  # Q$)JJv/G/M/M/O/O$P$P !HH P QX X R#5(,!2,&!2$"4!2!2&<%:%:&<  QX X t! &(;! 5! #f&B!  "#6!  #$8 ! &'> ! '(@ !  *!  !  U=11!  U!  $%:%Bd!  &+3t!  V08D!  f3!  *! !! "%& 0 0.&"4!2 &$""=! ! ! >?! @A! F),8@@BBJJ3PVWW``bb JrL related_user str | Noner c,|p|j}|jd} |jd} |jd} |jd} |jd} |jd}|jd}|jd}|jd d}|jd |}|p|}|j}|r(d d |nd }||j\}}|r||\}}nO|jdr1||jd\}}nd }i}d}|rtd|x}} d}|tvrtd|dd|}|j sd}d}n(|j d}|j d|j dz }d }|rt|}d|d}|dkrdnd}d|d|d|d |d!|d"|d"|d"|d#}tjjj|id$|d%|d&t'|d'|d(|d| d |d |d| d| d| d| d|d|d)|jd*|jd|||}t-|} | S)+Nr[rrrrrrrrrrrrrz AND ( :responsible_name IS NULL OR obj_responsible_names ILIKE '%' || :responsible_name || '%' ) AND ( :owner_name IS NULL OR obj_owner_name = :owner_name ) z:Param related_user is deprecated. Use related_person_loginz AND ( :responsible_name IS NULL OR obj_responsible_names ILIKE '%' || :responsible_name || '%' OR obj_owner_name = :owner_name ) rTr;rrr{rrrr0ra SELECT obj_id, obj_code, obj_model, obj_name as title, obj_breadcrumbs as breadcrumbs, :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label, 100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank, EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days,  as , substring(` FROM 0 FOR 240) as headline FROM cmf_full_search WHERE a] obj_model IN :model_name_in AND part_no <= :max_partno AND ( :parent_id IS NULL OR obj_parent_id = :parent_id ) AND ( :author_name IS NULL OR obj_author_name = :author_name ) AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name) z a AND ( :logic_type_code IS NULL OR obj_logic_type_code = :logic_type_code ) AND ( :status_type IS NULL OR obj_status_type = :status_type ) AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before ) AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after ) AND ( :archived IS NULL OR obj_archived = :archived ) AND ( :deleted IS NULL OR obj_deleted = :deleted ) and ( :user_rating is null or obj_user_rating >= :user_rating) AND obj_modified_at is not null ORDER BY obj_modified_at DESC LIMIT :slice_for OFFSET :slice_from; rrrrrrr)rOrrrrrrrrrrfullsearch_slicerPrrWrrrrrrrrG)!rwrrr rrrrrOr[rrrrrrrrrrrrrrowner_and_responsible_filterrrrrrrErall_objss! rJ filter_oncezCmfFullSearch.filter_onces&:): '++K88 !.223EFF!.223EFF -112CDD)--m<< #0445IJJ"/334GHH)--m<< (,,\4@@ )--mZHH .; #6j|E?FFuzzRdGeGefffCE# #9&/#J#J [ % %(%L%LMg%h%h "J  $ $%; < < %(%L%LVM`MdMde{M|M|%}%} "J JJ( $  R S S S,8 8J), (^++ [z[[cg h h h h*j** & PJII03J/2V5LQ5OOI  \8}}H [x [ [ [  *f 4 4WW! " " " $0" " (" " #" " (.)" " *+" " ,-" " .$/" " " H),8@@BBJJ3Q *Q Q  U=11Q  U Q * Q  Q  *Q  ;Q   0Q   0Q  Q  ;Q  !"4Q  !2Q  Q v~!Q " ;#Q $%Q &'Q   * ##rLc>ttjj}||\}}d}d}|rt |}d|d}d}d}d} d} dgd |d |d |d |d |d|d |d |d |d|d|d |d |d |d|d} tjj j | | | |d| } t| S)NrrrEMTr rra (SELECT obj_id, obj_code, obj_model, obj_name as title, obj_breadcrumbs as breadcrumbs, :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label, 100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank, EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days, rrrzW obj_model = 'CmfTask' AND part_no = 0 u AND obj_modified_at is not null /* Временный хак пока не поправят багу TEM-1625047513 */ ORDER BY obj_modified_at DESC LIMIT :slice_for OFFSET :slice_from) UNION (SELECT obj_id, obj_code, obj_model, obj_name as title, obj_breadcrumbs as breadcrumbs, :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label, 100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank, EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days, z[ obj_model = 'CmfDocument' AND part_no = 0 z obj_model != 'CmfDocument' AND obj_model != 'CmfTask' AND part_no = 0 u AND obj_modified_at is not null /* Временный хак пока не поправят багу TEM-1625047513 */ ORDER BY obj_modified_at DESC LIMIT :slice_for OFFSET :slice_from); )rrr)rCr current_userrcrrPrrrWrrrrr rG) rwrrrrrrrrrrrErs rJfilter_empty_topzCmfFullSearch.filter_empty_tops !566!$!H!HI]!^!^ J  \8}}H [x [ [ [ $   @@@@@@@@@@$0@@@@(@@@@#@@@@"#@@@@>?@@@@>$0?@@@@@(A@@@@F#G@@@@LM@@@@hi@@@@h$0i@@@@j(k@@@@p#q@@@@xy@@@@@@D),8@@BBJJ3$"Q Q  Q   8:: JrL tags_names list[str]returntuple[str, dict[str, str]]cfi}d}|r(d}ddd|Dd|d<||fS)Nrz AND obj_tags ~* :tags_re(^| |,)(|c3>K|]}tj|VdSrrRescape)r~rs rJrz3CmfFullSearch._build_tags_filter..s-8h8hQY89L9L8h8h8h8h8h8hrL)( |,|$)tags_re)r)rrrs rJrz CmfFullSearch._build_tags_filtersX   s6K%r8h8h]g8h8h8h0h0h%r%r%rK "K''rL user_loginrCcTi}d}|rd}tj|}d|d|d<||fS)Nrz; AND obj_related_person_logins ~* :related_person_logins_rer"r'related_person_logins_rer%)r)rrs rJrz1CmfFullSearch._build_related_person_logins_filtersL   UVJ:..J5T 5T5T5TJ1 2:%%rLc|t|dkrdSg}|ddD]}|dds|dds9i}|d|d<|ddr|dd|d<nd|d<||t}||_d |_||_tj j |_ d |_ t5|ddddS#1swxYwYdS) Nrrrz CmfDocument:zCmfTask:rparentr]rF)r startswithrrr CmfSearchStatrfactionrrrr person_id aggregatedrZ disable_aclsave)robj_dictst_dictrecst_recstats rJ_do_calc_statisticsz!CmfFullSearch._do_calc_statisticss |  q FCRC= # #CI((88 CIr[s """""",,,,,,,,,,,,0000000000 >>>>>>!!!!####   !! @@@AAA W W W!\!\!\\!c!c!cc ,!G!G!GGKKK:::"s#58J#JMi#il{#{U$U V V M M M"x"x"x'*<<NOO___bbbb     {{{{{CJ.<{{{{{rL