idZddlZddlZddlZddlZddlZddlZddlmZddl m Z ddl m Z ddl mZddlmZddlmZdd lmZmZdd lmZmZmZGd d Zy) z Database checks module NPath)Logger)SchemaAnalyzer) DockerUtils)ModelSchemaAnalyzer)MigrationChecker)INDEX_DEFINITIONSTRIGGER_DEFINITIONS)DictListOptionalceZdZdZdedefdZdefdZdde de fdZ de fd Z de de fd Z de fd Z d ede fd Zdde de fdZdde de fdZdede fdZdede fdZde fdZde fdZde fdZdde de fdZde fdZy)DatabaseChecksz$Check database schema and connectionlogger project_dircx||_||_tjj |d|_t ||_t||_ t||_ t||_ |j tjvr+tjjd|j yy)Nbackendr)rrospathjoin backend_dirrschema_analyzerr docker_utilsrmodel_schema_analyzerr migration_checkersysinsert)selfrrs    388 + HHOOAt// 0 ,returnct}t|jdz dz }|jshdS |j dD]r}|j dk(r|j d}tjd|}|j|tjd |}|j|t |S#t$r-}|jjd d |hdcYd }~Sd }~wwxYw) z6Dynamically extract table names from SQLAlchemy modelsappmodels tagsusersstreamschapterscommentssessions stream_tags activity_log twitch_oauth chat_messages site_settings watch_historyprocessing_jobs*.pyz __init__.pyutf-8encodingz'__tablename__\s*=\s*["\']([^"\']+)["\']z\b(\w+)\s*=\s*Table\s*\(DATABASEz#Failed to scan models dynamically: N) setrrexistsglobname read_textrefindallupdate Exceptionrwarning)rexpected_tables models_dir model_filecontenttablename_matches table_matcheses r _get_expected_tables_from_modelsz/DatabaseChecks._get_expected_tables_from_models$s%$**+e3h>   "   (oof5 6 ??m3$...@$&JJ/Y[b$c!&&'89!# +F P &&}5 6,  KK   .QRSQT,U V  sBC C: "C5/C:5C:auto_fixc|jjd|jsy|j|sy|j |sy|j |sy|j sy|jsy|jsy|j|sy|jsy|jjddy)z1Run all database checks and optionally fix issueszDatabase ChecksFr9zAll database checks passedT) r subheader_check_database_connection_compare_schema_with_models_check_indexes_check_triggers_check_initial_datacheck_migration_system_check_model_schema_coverage_check_migration_consistency_check_all_model_tables_existsuccess)rrLs r run_all_checkszDatabaseChecks.run_all_checksJs /0..0//9""8,##H-'')**,00200:113 J(DEr"c|jjdd ddl}|jgdddd}|jdk(r|jj dd y|jj dd |jy #j$r|jj dd Yy t$r)}|jj dd |Yd}~y d}~wwxYw)z4Check if database connection works using docker execr9zChecking database connection...rN)dockerexecstreama2026-postgres pg_isready-Ustreama-d streama2026T capture_outputtexttimeoutzDatabase connection successfulzDatabase connection failed: Fz#Database connection check timed outz"Database connection check failed: ) rinfo subprocessrun returncoderXerrorstderrTimeoutExpiredrB)rriresultrJs r rOz)DatabaseChecks._check_database_connectionus %FG  #Z^^n# F  A% ##J0PQ !!*0LV]]O.\]((  KK  j*O P  KK  j,Nqc*R S s$AB&)B+C.=C.C))C.c |jjdd ddl}|jgdddd}|jdk7r*|jj dd |j y t}|jjd D]w}|js|jd r&d |vs+|jd }t|dk\sK|dj}|sa|dk7sg|j|y|j}d} ||z } | r|jj dddj| d } |rf|jjdd|j!| r|jj#ddn|jj dd||z } | rQ| dhk(r|jjddn.|jj%dddj| |D]I}||vr!|jj#dd|d(|jj dd|dd } K|r|j&j)} |j&j+} | dr | dj-D]6\}}|jj dd|ddj|8|r|jjdd|j/rU|jj#dd|j&j1| | rV|jj#dd n9|jj dd!n|jjdd"d } | d#rO|jj dd$dj| d#|jjdd"d } | S#j2$r|jj dd%Yy t4$r)}|jj dd&|Yd}~y d}~wwxYw)'z>Compare database tables with expected tables using docker execr9zComparing tables with models...rN r[r\r]psqlr_r`rarb-cz\dtTrcrdFailed to get table list: F |publicr=zMissing tables: , z#Attempting to fix missing tables...zMissing tables fixedzFailed to fix missing tablesalembic_versionz%MM*5  6 @@BLF*I5N !!*0@>AZ@[.\]KK$$Z1VW//? ++J8NO ))*6TU%|3L$5#66KK$$Z3oqKK'' 6J499UaKbJc4de+ # *KK'' fZL4QRKK%%jF:,i2PQ"F  #%)%9%9%I%I%K""22AAC %&78/ABS/T/Z/Z/\C+ G ))*zlJlmqmvmvw~mmA7BCC  ((5gh@@B KK// PP#P9P?LP+Q$3Q$;QQ$c N ddlm}|jjd}dddddd d d d |g }|jj |\}}|s |j j d d|y|j jd dgd}|jj |\}}|s |j j d d|y|j jd dy#t$r)}|j j d d|Yd}~yd}~wwxYw)z0Automatically create and apply Alembic migrationrdatetime %Y%m%d_%H%Mr[composer\ralembicrevisionz--autogenerate-mauto_migration_r9Failed to create migration: FMigration created successfully)r[rr\rrupgradeheadFailed to apply migration: Migration applied successfullyTzAuto-migration failed: N) rnowstrftimer _run_commandrrlrXrB) rr timestamp cmd_createsuccess_createoutput cmd_upgradesuccess_upgraderJs r rz/DatabaseChecks._auto_create_and_apply_migrations7  ) // >I#Ivy)ZYikotCDMCNrOPJ%)%6%6%C%CJ%O "NF! !!*0LVH.UV KK   ,L MaK&*&7&7&D&D[&Q #OV" !!*0KF8.TU KK   ,L M  KK  j,CA3*G H s%A2C25AC2C22 D$;DD$r~c|jjddtjj |j d}tjj |s|jjddy t|d5}|j}dddd|vrd vr|jjdd d }d |vr|jd |d z}n||z }t|d 5}|j|ddd|jjddyy#1swYxYw#1swY3xYw#t$r)}|jjdd|Yd}~yd}~wwxYw)z5Attempt to fix missing tables by updating init-db.sqlr9z*Checking init-db.sql for missing tables... init-db.sqlinit-db.sql not foundFrNr2z(CREATE TABLE IF NOT EXISTS site_settingsz,Adding site_settings table to init-db.sql...ad -- Site settings table for configuration CREATE TABLE IF NOT EXISTS site_settings ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), key VARCHAR(100) UNIQUE NOT NULL, value TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); -- Index for site_settings CREATE INDEX IF NOT EXISTS idx_site_settings_key ON site_settings(key); -- Trigger for updated_at on site_settings CREATE TRIGGER update_site_settings_updated_at BEFORE UPDATE ON site_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); z-CREATE TRIGGER update_twitch_oauth_updated_atwz(site_settings table added to init-db.sqlTzFailed to update init-db.sql: )rrhrrrrr;rlopenreadreplacewriterXrB)rr~ init_db_pathfrGsite_settings_sqlrJs r rz"DatabaseChecks._fix_missing_tablessh %QRww||D$4$4mD ww~~l+ KK  j*A B- lC( #A&&( #.05_gn5n   -[\%!&CgM%ooG),[[G 00G,,%GGG$% ##J0Z[Q # #D%%  KK  j,J1#*N O sI E D2!AE :D> $E 2D;7E >EE E<E77E<c|jjdd ddl}|jgdddd}|jdk7r*|jj dd |j yt}|jjd D]i}|jsd |vsd |vs|jd }t|dk\s=|dj}|sS|dk7sY|j|khd}d} |D]G}||vr!|jjdd|d(|jjdd|dIt|t|z } | r|jjdddj| d|rT| rR|jjdd|jt!| s|jj ddy#j"$r|jj ddYyt$$r)} |jj dd| Yd} ~ yd} ~ wwxYw)z1Check if required indexes exist using docker execr9zChecking indexes...rN) r[r\r]rrr_r`rarbrsz\diTrcrdzFailed to get index list: ruidx_ix_rvrxryr=>ix_streams_statusix_comments_user_idix_sessions_user_idix_site_settings_keyix_chapters_stream_idix_comments_parent_idix_comments_stream_idix_streams_created_byix_sessions_expires_atix_activity_log_user_idix_twitch_oauth_user_idix_watch_history_user_idix_processing_jobs_statusix_activity_log_created_atix_chat_messages_stream_idix_watch_history_stream_idix_processing_jobs_stream_idix_chapters_game_display_name ix_watch_history_last_watched_atzIndex r|: missing (may be in migration)zMissing indexes: rzz (may be in migration)z&NN:6  7  ,F. g +KK'' fZL4QRKK$$Z6*Ed1ef  g ""23c*oEO   /@?A[@\\r-stKK$$Z1op<Ih1ij"F$++L9  : ##J0HIM !!*.EF 0KK$$Z1no::;KL ++J8lm!%M ))*6`aM  KK  j,Fqc*J K sJA2G7G7#G75G7:G7?G7BG7,A,G7G77 H)H$$H)rc R ddl}ddlm}|jjd}ddddd d d d d |g }|j|ddd}|j dk7r*|j jdd|jygd}|j|ddd}|j dk7s|jjs|j jddy|jjjdd} d| } |j jdd| ddddd d| g} |j| ddd} | j dk7r*|j jdd| jy| j} d}|D]9}|dk(r|dz }|d k(r|d!z }|d"k(r|d#z }$|d$k(r|d%z }/|d&k(s5|d'z };d}|D]9}|dk(r|d(z }|d k(r|d)z }|d"k(r|d*z }$|d$k(r|d+z }/|d&k(s5|d,z };| jd-}t|d.kDr|d.jd/}d0|d1}t|d.kDr |d2|d3z }n|d4z }|d|z}|jd z d z d5z | z } |j|d67|j j!dd8t|d9gd;}|j|ddd}|j dk7r*|j jdd<|jy|j j!dd=y#t"$r)}|j jdd:|Yd}~yd}~wwxYw#t"$r)}|j jdd>|Yd}~yd}~wwxYw)?zOAutomatically create and apply migration for missing triggers using docker execrNrrr[rr\-Trrrradd_missing_triggers_Trdr9rFr[rr\rrshrsz6ls -t /app/alembic/versions/*.py 2>/dev/null | head -1rcNo migration file found//app/alembic/versions/Migration file: catFailed to read migration file: ra  op.execute(""" DROP TRIGGER IF EXISTS update_site_settings_updated_at ON site_settings; CREATE TRIGGER update_site_settings_updated_at BEFORE UPDATE ON site_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); """) rz op.execute(""" DROP TRIGGER IF EXISTS update_users_updated_at ON users; CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); """) rz op.execute(""" DROP TRIGGER IF EXISTS update_streams_updated_at ON streams; CREATE TRIGGER update_streams_updated_at BEFORE UPDATE ON streams FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); """) rz op.execute(""" DROP TRIGGER IF EXISTS update_comments_updated_at ON comments; CREATE TRIGGER update_comments_updated_at BEFORE UPDATE ON comments FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); """) ra  op.execute(""" DROP TRIGGER IF EXISTS update_twitch_oauth_updated_at ON twitch_oauth; CREATE TRIGGER update_twitch_oauth_updated_at BEFORE UPDATE ON twitch_oauth FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); """) zm op.execute(""" DROP TRIGGER IF EXISTS update_site_settings_updated_at ON site_settings; """) z] op.execute(""" DROP TRIGGER IF EXISTS update_users_updated_at ON users; """) za op.execute(""" DROP TRIGGER IF EXISTS update_streams_updated_at ON streams; """) zc op.execute(""" DROP TRIGGER IF EXISTS update_comments_updated_at ON comments; """) zk op.execute(""" DROP TRIGGER IF EXISTS update_twitch_oauth_updated_at ON twitch_oauth; """) def upgrade():rydef downgrade():def upgrade():  def downgrade(): ru def downgrade(): pass versionsr6r7Migration updated with z trigger statementsz(Failed to write migration file locally: r[rr\rrrrrrrzAuto-trigger migration failed: )rirrrrjrkrrlrmrrrrhrr write_textrXrB)rrrirrr result_createcmd_find result_findlatest_migrationmigration_pathcmd_read result_readmigration_contenttrigger_statementstriggerdowngrade_statementsupgrade_section after_upgrade new_upgrade new_contentlocal_migration_pathrJrresult_upgrades r rz-DatabaseChecks._auto_create_trigger_migrationsc   ) // >I#IvtY S]_cg|~G}HfIJJ*JNN:dQU_abM''1, !!*0L]MaMaLb.cdLH(*..$T[]^K%%*+2D2D2J2J2L !!*.GH*11779??DRH 56F5GHN KK  Z+; (,'7'7)'Ci'OR\'\_o'o$!(33K'3RKK'' 6McRbNcMddw4xy gK+Z^^KSWacdN((A- !!*0KNLaLaKb.cd KK   ,L M!!KK%%j4\]^\_2`a !  KK  j,KA3*O P skBM4AM4(B'M4AM4?M4A=M4 z << 'EOF' z EOF)inputrerfrgz Failed to write migration file: rz index statementsrrrzAuto-index migration failed: )rirrrrjrkrrlrmrrrrhr rrXrB)rrrirrrrrrrrrrrindex_definitionsindex_statementsrtablecolumnunique unique_strr r r r r  cmd_write result_writerrrJs r rz+DatabaseChecks._auto_create_index_migrationFs\Y   ) // >I#IvtY S]_cg{}F|GfHIJ*JNN:dQU_abM''1, !!*0L]MaMaLb.cdLH(*..$T[]^K%%*+2D2D2J2J2L !!*.GH*11779??DRH 56F5GHN KK  Z+; &y&$ 4QUY_`n_ooz|G{HHMXNO -z~~i{[_fjtvw **a/KK%%j4TUaUhUhTi2jk  ##J2I#oJ^I__p0qrgK+Z^^KSWacdN((A- !!*0KNLaLaKb.cd KK   ,L M  KK  j,I!*M N sFBMAM(B'MM..MC M'A;M#M M2 M--M2c |jjdd ddl}|jgddd}|jdk(r|j j jd}t|d k\r t|d j }|dk(r|jjdd y |d k\r!|jjdd|dy|jjdd|dy |jjdd|jy y#t$r|jjddYy wxYw#t$r)}|jjdd|Yd}~y d}~wwxYw)z.Check if initial data exists using docker execr9zChecking initial data...rN) r[r\r]rrr_r`rarbrsz#SELECT COUNT(*) FROM site_settings;Trrurxz3Site settings table is empty - missing initial dataFzSite settings: u rows ✓zSite settings: only z rows (expected at least 5)z#Failed to parse site settings countzFailed to check initial data: )rrhrirjrkrrrrintrlrXrC ValueErrorrmrB)rrirorsite_settings_countrJs r rSz"DatabaseChecks._check_initial_datas %?@#  $Z^^V#F   A% ++-33D9u:?%.1%(..2B.C+.!3 KK--j:op#(0A5 KK// oNaMbbkRSfRghC=DE#(  !!*0Nv}}o.^_%#&% ))*6[\$%  KK  j,J1#*N O sHA!E=D0>%D0$ D0)E0%EEEE F $FF c  |jjdd ddl}ddl}ddl}ddlm}||j}|dz }|dz dz d z }|js|jjdd y t|d 5}|j} ddd|d z } | jsD|jjddg}  jd} | D cgc]$} | jjdr#| &}} dj|}d}|j |||j"|j$z|j&zD]:}|j)dj}|s%|| vs*| j+|<d}|j |||j"D]:}|j)dj}|s%|| vs*| j+|<nt| d 5}|j}dddd}| }|}t-|}t-|}||z }||z }|s|s|jj/ddy |jjddt1|dt1|dt3|} | r|jjddt1| d|jryt5|j7dd }t9d|ddD}|s)|jjdd|jjdd|j:gdd d t=| }|j>dk(r|jj/dd!t5|j7dd }|r|d} |jjdd"| j@t| d 5}|j}!ddd!jd#}"t1|"d$kDr.|"d$jd%}#d&}$| D] }|$d'|d(z }$ d)|$d*}%t1|#d$kDr |%d+|#d$z }%n|%d,z }%t| d-5}|jC|"d|%zddd|jj/dd.t1| d/|d z } t| d-5}|jC| ddd|jj/dd0nF|jjEdd1|jFn|jj/dd2|jj/dd3y #1swYxYwcc} w#1swYxYw#1swYxYw#1swY xYw#1swYxYw#tH$r)}&|jjEdd4|&Yd}&~&y d}&~&wwxYw)5zMCheck if Alembic migration system is set up and check for init-db.sql changesr9zChecking migration system...rNrrrrrrTrzinit-db.reference.sqlzEReference file init-db.reference.sql not found - using regex fallbackruz--zdINSERT INTO\s+\w+\s*\([^)]+\)\s*VALUES\s*\([\s\S]+?\)(?:\s*ON CONFLICT\s*\([^)]+\)\s*DO\s+NOTHING)?;zALTER TABLE\s+\w+\s+[^;]+;cg}tj|}|D]U}t|}tj|dd}|j s7|j |j W|S)z,Parse SQL content into individual statementsTF)strip_commentsreindent)sqlparseparsestrformatrr) sql_content statementsparsedstmtstmt_str clean_stmts r parse_sql_statementszCDatabaseChecks.check_migration_system..parse_sql_statementssm!#J%^^K8F &B#&t9%-__Xd]b%c %++-&--j.>.>.@A B&%r"z"No changes detected in init-db.sqlzFound z added, z removed statementsz statements from diffr5)reversec3FK|]tfddDyw)c3rK|].}|jjjv0ywN)lowerr=).0keywordrs r zBDatabaseChecks.check_migration_system...,s&k'GMMOqvv||~=ks47)initialdataschemaN)anyr5rs @r r7z8DatabaseChecks.check_migration_system..+s%1kMjkk1s!rz7init-db.sql has changes but no matching migration foundz-Creating migration for init-db.sql changes...)r[rr\rrrrsync_init_db_changes)rerfcwdrrrryrz # From init-db.sql z op.execute(""" z """) rrrz def downgrade(): pass rrz statements from init-db.sqlz+Reference file updated to match init-db.sqlru,Migration for init-db.sql changes exists ✓zMigration system check passedz"Failed to check migration system: )%rrhrirr?pathlibrrr;rCrrrrrrfinditer IGNORECASE MULTILINEDOTALLgrouprr:rXrrsortedr<r;rjr'rkr=rrlrmrB)'rrirr?r project_rootr versions_dirrrGreference_pathdata_statementsrr clean_lines clean_contentinsert_patternmatchr, alter_patternreference_contentr/current_statementsreference_statements current_set reference_setadded_statementsremoved_statementsrecent_migrationshas_matching_migrationronew_migrationsrrr r statements_blockr rJs' r rTz%DatabaseChecks.check_migration_systems+ %CD_     $  0 01L'-7L')3i?*LL&&( ##J0GHlC( #A&&( #*,CCN!((* ##J0wx"$ d+05[TZZ\=T=TUY=Zt[ [ $ + 6 "I(R[[ XZXdXdHdgigpgpHpq5E ;;q>//1DO ;'..t45 !> (R[[ r}}U5E ;;q>//1DO ;'..t45 .#.1!()%1 &&:'%B"'; &3[%@"'0BKK'' 4XY   vc:J6K5LHUXYkUlTmnA.BC#''7"8   vc/6J5KK`-ab  &&((.|/@/@/HRV(W%-01!22A!61.* 2 ++J8qr ((5de"0y+/!% #L 1 "",,1 KK// <\].4L4E4Ef4MW[-\N-3A!3D 0 $ 0 0?OP`PePeOf=g h&**:C%@!AA89$5!A3D2I2IJZ2[#&#7!#;4CA4F4L4LM_4`M8T$40?%n(8>Z[_Z``l)D%R()0B[0P(Q%R%)KK$7$7 F]^abq^r]stPEQ%R6BD[5[N)-nc)B%9a()(8%9%)KK$7$7 Dq$r KK--jDatabaseChecks._check_model_schema_coverage..~s#oFCK#ozMissing from schemas: z fieldsz z: rzc3&K|] }|d yw)r=Nr<s r r7z>DatabaseChecks._check_model_schema_coverage..sMh\]aPViMhsextra_in_schemasc32K|]}t|ywr3rarbs r r7z>DatabaseChecks._check_model_schema_coverage..s!i&#f+!irdzExtra in schemas: z fields (may be intentional)type_mismatchesc32K|]}t|ywr3ra)r5 mismatchess r r7z>DatabaseChecks._check_model_schema_coverage..s)xj#j/)xrdrzType mismatches found: Tz'Failed to check model-schema coverage: N) rrhrcompare_model_schema_coveragesumvaluesrCrrgetrBrl) rcoverage_report missing_count model_namerc extra_counttype_mismatch_countrrkrJs r rUz+DatabaseChecks._check_model_schema_coveragersa %HI "88VVXO KK  Z+B?ShCiBjjk)l m KK  Z+?Pd@e?f)g h KK  Z+;OL\<];^)_ `56 ##ooNd>e>l>l>n#o o  ##J2HW^0_`*9:P*Q*W*W*Yl&JKK$$Z2j\DIIMhagMhDhCi1jkl12!!iOL^<_%MM*5  6L F)I5N !!*0LTYYWeMfLg.hiM ##J0TUM((  KK  j*A B  KK  j,J1#*N O sOAE/38E/,E/>E/E/#E/9E/?AE/E//+G G $GG N)T)F)__name__ __module__ __qualname____doc__rr'r!r:rKboolrYrOrPrrrQrRrrrrSrTrUrVrWrfr"r rrs. 1v 1C 1$#$L)t)t)VD6fDfTfP$@7#7$7rMtMM^===~eteeN[D[T[z'T'RddL"d"HTd84t4r"r)rrrridifflibr?r%r?rtests.utils.loggerrtests.utils.schema_analyzerrtests.utils.docker_utilsr!tests.utils.model_schema_analyzerrtests.utils.migration_checkerr tests.utils.db_constantsr r typingr r rrrfr"r rsE  %60A:K''RRr"