L7`j '\SrSSKJr SSKrSSKrSSKrSSKJrJrJr SSK J r SSK J r Sr SSjrSS jrSS jrSS jrSS .SS jjrS SjrS!SjrS"SjrSS.S#SjjrSr\S.S$SjjrSS.S%SjjrSSjrS&S'Sjjrg)(u@SQLite-хранилище: схема, upsert, бэкап, runs.) annotationsN)datetime timedeltatimezone)Path)Iterablea PRAGMA foreign_keys = ON; CREATE TABLE IF NOT EXISTS channels ( username TEXT PRIMARY KEY, tg_id INTEGER, title TEXT, subscribers INTEGER, last_pulled_at TEXT ); CREATE TABLE IF NOT EXISTS posts ( channel TEXT NOT NULL REFERENCES channels(username), post_id INTEGER NOT NULL, date TEXT NOT NULL, text TEXT NOT NULL DEFAULT '', link TEXT, media_type TEXT, views INTEGER, forwards INTEGER, reposts INTEGER, comments INTEGER, reactions INTEGER, er REAL, err REAL, views_growth TEXT, is_deleted INTEGER NOT NULL DEFAULT 0, first_seen_at TEXT NOT NULL, last_updated_at TEXT NOT NULL, stats_updated_at TEXT, PRIMARY KEY (channel, post_id) ); CREATE INDEX IF NOT EXISTS idx_posts_date ON posts(channel, date); CREATE INDEX IF NOT EXISTS idx_posts_stats_updated ON posts(stats_updated_at); CREATE TABLE IF NOT EXISTS runs ( started_at TEXT PRIMARY KEY, finished_at TEXT, status TEXT, succeeded_channels TEXT, failed_channels TEXT, error_summary TEXT ); CREATE TABLE IF NOT EXISTS channel_snapshots ( channel TEXT NOT NULL REFERENCES channels(username), date TEXT NOT NULL, subscribers INTEGER, captured_at TEXT NOT NULL, PRIMARY KEY (channel, date) ); cd[R"[RS9R S5$)Ntz%Y-%m-%dT%H:%M:%SZ)rnowrutcstrftime/home/rasp/tgstat-puller/db.pynow_isorBs! <<8<< ( 1 12F GGrc~[R"U5nURS5 [RUlU$)NzPRAGMA foreign_keys = ON)sqlite3connectexecuteRow row_factorydb_pathconns rrrFs. ??7 #DLL+,{{D KrcURRSSS9 [U5nUR[5 UR S5 UR 5 SSS5 g!,(df  g=f)NT)parentsexist_okzPRAGMA user_version = 1)parentmkdirr executescriptSCHEMArcommitrs rinit_dbr%MsS NN5  T 6" ./    s 7A&& A4c [U5nURSXX4[545 UR5 SSS5 g!,(df  g=f)Nal INSERT INTO channels (username, tg_id, title, subscribers, last_pulled_at) VALUES (?, ?, ?, ?, ?) ON CONFLICT(username) DO UPDATE SET tg_id = excluded.tg_id, title = excluded.title, subscribers = excluded.subscribers, last_pulled_at = excluded.last_pulled_at rrrr$)rusernametg_idtitle subscribersrs rupsert_channelr,UsD  T  e') <     s .A A)on_datec[5nU=(d USSn[U5nURSXX$45 UR5 SSS5 g!,(df  g=f)uЗаписать срез числа подписчиков. on_date='YYYY-MM-DD' — для исторического бэкфилла; None = сегодня (UTC). Один срез на канал на день; повтор в ту же дату обновляет строку.N a INSERT INTO channel_snapshots (channel, date, subscribers, captured_at) VALUES (?, ?, ?, ?) ON CONFLICT(channel, date) DO UPDATE SET subscribers = excluded.subscribers, captured_at = excluded.captured_at )rrrr$)rchannelr+r-r dayrs rinsert_snapshotr2msW )C  S"XC  T  ; ,     s %A A ct[5n[U5nURSUUSUSURSS5URS5URS5URS5URS 5URS 5URS 5URS 5URS 5URS5URS5(a[R "USSS9OSUU45nUR 5 URsSSS5 $!,(df  g=f)uВставить пост, если такого ещё нет. Возвращает 1, если вставлено, иначе 0.ac INSERT OR IGNORE INTO posts ( channel, post_id, date, text, link, media_type, views, forwards, reposts, comments, reactions, er, err, views_growth, is_deleted, first_seen_at, last_updated_at, stats_updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, NULL) post_iddatetextlink media_typeviewsforwardsrepostscomments reactionsererr views_growthF ensure_asciiN)rrrgetjsondumpsr$rowcount)rr0postr rcurs r insert_postrJs )C  Tll YV $ &!$#$%HLQ_H`H` 4/eDfj!  6 ||;   s D D)) D7c[5nURS5(a[R"USSS9OSn[ U5nUR SURS5URS5URS5URS5URS 5URS 5URS 5UUUUU4 5 UR 5 SSS5 g!,(df  g=f) NrAFrBa UPDATE posts SET views = ?, forwards = ?, reposts = ?, comments = ?, reactions = ?, er = ?, err = ?, views_growth = ?, last_updated_at = ?, stats_updated_at = ? WHERE channel = ? AND post_id = ? r:r;r<r=r>r?r@)rrDrErFrrr$)rr0r4statsr growth_jsonrs rupdate_post_statsrNs )CKP99UcKdKd$**U>2GjnK  T   '" *% )$ *% +& $ %   . 1   s BC'' C5c[U5nURS[5X45 UR5 SSS5 g!,(df  g=f)NzVUPDATE posts SET is_deleted = 1, last_updated_at = ? WHERE channel = ? AND post_id = ?r')rr0r4rs r mark_deletedrPs<  T d Y )    s -A A) now_iso_tsc4[R"U=(d [5RSS55nU[ US9- R S5n[ U5nURSX45R5sSSS5 $!,(df  g=f)upПосты канала за последние `days` дней, не помеченные удалёнными.Zz+00:00)daysr z SELECT * FROM posts WHERE channel = ? AND is_deleted = 0 AND date >= ? ORDER BY date DESC N) r fromisoformatrreplacerrrrfetchall)rr0rTrQnow_dtcutoffrs rposts_in_windowrZs{ # #Z%<79$E$Ec8$T UFyd++ 5 56J KF  T||      (*   s !B  B)stale_after_hourscl[R"[RS9nUR S5nU[ US9- R S5n[ U5nURS[5U45 URSU45 UR5 SSS5 U$!,(df  U$=f)uZНачать новый run. Заодно закрывает зависшие 'running'-строки старше `stale_after_hours` как failed — иначе жёсткое падение процесса оставляет их без finished_at навсегда, и они не попадают в recent_runs() (алерт молчит).r z%Y-%m-%dT%H:%M:%S.%fZ)hoursu: UPDATE runs SET finished_at = ?, status = 'failed', error_summary = 'stale run: процесс не завершился штатно (crash/kill), помечено при следующем старте' WHERE status = 'running' AND started_at < ? z;INSERT INTO runs (started_at, status) VALUES (?, 'running')N) rr rrrrrrrr$)rr\r started_atrYrs r start_runr`s ,,(,, 'C56JI$566 @ @AX YF  T   Y   I M       s AB$$ B3r7) error_summaryc :[U5nURS[5U[R"USS9[R"USS9UU45nUR 5 UR S:Xa[SU<35eSSS5 g!,(df  g=f)Nz UPDATE runs SET finished_at = ?, status = ?, succeeded_channels = ?, failed_channels = ?, error_summary = ? WHERE started_at = ? FrBrz(finish_run: no run found for started_at=)rrrrErFr$rG RuntimeError)rr_status succeededfailedrarrIs r finish_runrgs  Tll   959 66    <<1 !I*XY Y %   s A6B  BcUR5(dg[R"XRURS-55 g)Nz.bak)existsshutilcopy2 with_suffixsuffix)rs r backup_dbrns2 >>   LL--gnnv.EFGrc[U5nURSU45R5sSSS5 $!,(df  g=f)NzQSELECT * FROM runs WHERE finished_at IS NOT NULL ORDER BY started_at DESC LIMIT ?)rrrW)rlimitrs r recent_runsrq$s5  T|| _ H  (*   s !7 A)returnstr)rrrrzsqlite3.Connection)rrrrNone) rrr(rsr) int | Noner* str | Noner+rurrrt) rrr0rsr+rur-rvrrrt)rrr0rsrHdictrrint) rrr0rsr4rxrLrwrrrt)rrr0rsr4rxrrrt) rrr0rsrTrxrQrvrrlist[sqlite3.Row])rrr\floatrrrs)rrr_rsrdrsre list[str]rfr{rarsrrrt))rrrprxrrry)__doc__ __future__rrErjrrrrpathlibrtypingrr#rrr%r,r2rJrNrPrZSTALE_RUN_HOURSr`rgrnrqrrrrsiF" 224 nH        :       6 F<IM *-;E"H r