Mj0 d dl Z d dlZd dlZd dlZd dlmZmZ d dlmZmZm Z m
Z
m Z m Z m
Z
mZmZmZ d dlmZ d dlmZ d dlmZ d dlmZ d dlmZ d Zed
z Z d Z e Z ej d Z G d
de Z!d!dZ"d Z#d Z$d Z%d Z&d Z'd Z(d Z)d Z*d Z+d"dZ,d Z-d Z.d Z/ej0 d efd Z1dS )# N)datetime timedelta)
ColumnBooleanDateTimeIndexIntegerString
create_engineeventfunctext)declarative_base) Inspector)Session)close_all_sessions)
DatabaseErrorz/var/lve/ssa.dbz.old cleanup_old_datac e Zd ZdZdZ eddd fZ eed Z ee
d Z ee
d Z eed Z
eed Z eed Z eed Z eed Z eed Z eed Z e ed
ej d Z e ed
ej ej Zd
S )
RequestResultay
Describes processed request stored in database file.
E.g.
{
"timestamp": "1650008727",
"url": "http://mydomain.com/index.php",
"duration": 162077,
"hitting_limits": false,
"throttled_time": 0,
"io_throttled_time": 0,
"wordpress": true
}
Note: created_at, updated_at is saved in local TZ format
scrape_result ix_scrape_result_domain_pathdomainpathT) primary_keyF)nullable)timezone)server_defaultindex)onupdater N)__name__
__module__ __qualname____doc__
__tablename__r __table_args__r r idr
r r timestampdurationr is_slow_requesthitting_limitsthrottled_timeio_throttled_time wordpressr r
now
created_at
updated_at =/opt/cloudlinux/venv/lib64/python3.11/site-packages/ssa/db.pyr r sg " $M e :HfMMON T * * *B
VFU
+
+
+F 6&5 ) ) )Dw///Ivg...H fWu555OVGe444NVGe444N w777w///I $///
RVWWWJ$///($(**U]UYU]U_U_```JJJr4 r ' c t j t t z
}d} t | 5 }| t
d ||d }|j }||z
}|dk r
ddd n ddd n # 1 swxY w Y nt d| t | dS )a
Removes outdated records from database, saving disk space.
Deletions are batched to avoid loading the entire database into
the cgroup's page cache, which causes OOM on large databases.
Runs VACUUM after deletion to reclaim disk space.
)daysr TzrDELETE FROM scrape_result WHERE rowid IN (SELECT rowid FROM scrape_result WHERE created_at < :cutoff LIMIT :batch))cutoffbatchNz Cleanup deleted %d old records) r todayr RETENTION_TIME_DAYS
session_scopeexecuter rowcountloggerinfovacuum_database)engine
batch_size
n_days_ago
total_deletedsessionresultdeleteds r5 r r S s6 !!I3F$G$G$GGJM
6
"
" g __` &
;; F oG W $M!|| KK0-@@@Fs :BBBc j t j t j t }t j |t j r6| d| d t d| dS t
d|| dS )u
Point SQLite's temp store at the database directory (on disk) for the
given operation. SQLite spills large sorts / temp B-trees and VACUUM
copies to the system temp directory, which can be tmpfs (RAM-backed).
When it is, the spill is charged against the MemoryMax=1G cgroup and
OOM-kills the agent on large databases — the same hazard for VACUUM
and for index builds / ORDER BY sorts (CLPRO-2819, CLPRO-3077).
PRAGMA temp_store_directory = ''z&Set SQLite temp_store_directory to: %szJDirectory %s not writable. %s will fall back to the default temp location.N) osr dirnameabspathSSA_DBaccessW_OKr> r@ rA warning)cursor operationdb_dirs r5 _redirect_temp_store_to_diskrW m s W__RW__V 4 4
5
5F y!! xBBBBCCC close Exceptionerrorstr)rC raw_connoriginal_isolation_levelrT es r5 rB rB ~ s! $$&&H ' 7 #' ""$VX666 2333x 4555 1 1 1 /s1vv//000000001 $< $< s0 BB& %D &
C$0*C D C$$D D!c 4 | } | }t |d t d | d | t d | n?# t $ r2}t dt | Y d}~nd}~ww xY w| dS # | w xY w)u
Create the composite (domain, path) index on existing databases.
The schema is only materialised by ``create_all()`` in
``create_db_if_not_exist()``, which runs solely when the DB file does
not yet exist — there are no migrations. So a model-level index reaches
new installs only, while the multi-GB databases that actually OOM never
get it. This explicit ``CREATE INDEX IF NOT EXISTS`` closes that gap; it
is idempotent and a cheap no-op once the index exists (CLPRO-3077).
Building the index on a large table sorts it, so we first redirect the
temp store to disk to avoid the very tmpfs spill we are trying to fix.
z CREATE INDEXz/Ensuring ix_scrape_result_domain_path exists...zVCREATE INDEX IF NOT EXISTS ix_scrape_result_domain_path ON scrape_result(domain, path)z.Index ix_scrape_result_domain_path is present.z Failed to ensure indexes: %sN) rZ rT rW r@ rA r> commitr\ r] r^ r_ )rC r` rT rb s r5 ensure_indexesre s $$&&H ""$V^<<< EFFFoppp DEEE = = = 3SVV<<<<<<<<= s0 BB, +D ,
C(6(C# D #C((D Dc f t | s!t j | d S d S N)
is_db_presentBasemetadata
create_all)rC s r5 create_db_if_not_existrl s8 )
((((() )r4 c t j t sdS t j | }d | D }t | dk S )NFc g | ]}|S r3 r3 ).0tables r5
z!is_db_present.. s
G
G
Ge
G
G
Gr4 r )rM r isfilerP r from_engineget_table_nameslen)rC database_inspectiontabless r5 rh rh s^
7>>& ! ! u#/77
G
G!4!D!D!F!F
G
G
GF v;;?r4 c 0 | d d S )NzPRAGMA journal_mode = WAL)r> ) dbapi_con
con_records r5 setup_wal_moder{ s
122222r4 c p t j t j t }t j |t j sdS | d| d dS # t j $ r'}t d|| Y d}~dS d}~ww xY w)uZ
Keep SQLite's temp spills (sort / GROUP BY temp B-trees) on disk rather
than the default temp dir, which may be tmpfs (RAM-backed) and thus
charged against the MemoryMax=1G cgroup. ``temp_store_directory`` is a
process-wide setting, but applying it on every connect is cheap and
guarantees it is in force before the daily routine runs (CLPRO-3077).
Complements the (domain, path) index: the index removes the per-domain
ORDER BY sort entirely, this catches whatever spill remains (e.g. the
GROUP BY on the computed hour). Best-effort — never block a connection.
NrK rL z,Could not set temp_store_directory to %s: %s) rM r rN rO rP rQ rR r> sqlite3Errorr@ rS )ry rz rV rb s r5 setup_temp_store_dirr s W__RW__V 4 4
5
5F
9VRW % % REFEEEFFFFF = R R REvqQQQQQQQQQRs $A? ?B5 B00B5c t t dz t dz fD ]6} t j | rt j | d 7dS )a
Restrict ssa.db and its WAL/SHM sidecars to owner-only (0600).
The DB holds every tenant's domains and request paths; sqlite3.connect
creates the file under the agent's umask (0022 -> world-readable 0644)
in the world-searchable /var/lve, leaking data across the shared-hosting
trust boundary. chmod 0600 is idempotent, so this is a safe no-op for an
already-tightened file; each path is guarded on existence.
-wal-shmi N)rP rM r existschmod)r s r5 _secure_db_filesr sU & &6/: " "
7>>$ " HT5 ! ! !" "r4 c dt | r dfd}t d|d }t j |dt t j |dt
t
| t |S )Nzfile:z?mode=roc 0 t j d S )NT)uri)r} connect)connection_strings r5 creatorz _setup_database..creator s 0d;;;;r4 sqlite:////F)r echor )rP r r listenr{ r rl r )readonlyr rC r s @r5 _setup_databaser s ((( ; 0 : : :< < < < <
F
LN333 L$89996""" Mr4 Fc t | S rg )r )r s r5 setup_databaser s 8 $ $$r4 c t j t r-t t j t t t }| t dt } t | 5 }t | 5 }| t
t || ddd n # 1 swxY w Y ddd n # 1 swxY w Y n# t $ r Y nw xY wt t dz t dz fD ]5}t j | rt j | 6dS )z
Restore database by establish connections to old and new databases,
merge data to new one if possible and delete old one.
r Nr r )rM r r rP r replace
OLD_SSA_DBr disposer r= queryr first!merge_unharmed_data_from_databaser remove)rC
new_engine
old_engine session_old session_newr s r5 restore_databaser s
w~~f '
6:&&& !!J
NN 9Z 9 9::J
:
&
& H+}Z7P7P HT_ m , , 2 2 4 4 4 -k; G G G H H H H H H H H H H H H H H H H H H H H H H H H H H H H H H
Vf_fvo>
7>>$ IdOOO sZ D D +=C4( D 4C8 8D ;C8 <D ? D DD DD
D$#D$c d}d} | t | | } | }|sdS |D ]}| | nv# t $ ri t | D ]V} | ||z }|s n&| | G# t $ r Y Sw xY wY nw xY w| ||z
})z_
Scrape all unharmed records from malformed database and merge them into new database.
r
TN)
r r offsetlimitallmerger ranger rd ) r r r rD r records_to_saverecord pos_in_batchrecord_to_saves r5 r r sN FJ !!-0077??EEjQQ #iikkO"
)
*
*!!&))))
* %j 1 1
%*\\&<2G%H%H%N%N%P%PN) %%n5555$ D * % sH A: A: :C-,CC-CC-
C'$C-&C''C-,C-c t j t r=t j t rt j t t
| 5 }| t d }| }|d d dk cddd S # 1 swxY w Y dS # t $ r Y dS w xY w)z
Try integrity check of database file to see if it is malformed.
If database unable to execute it, will also count as malformed.
zPRAGMA integrity_checkr okNT) rM r r r rP r r= r> r fetchallr )rC dbrH errorss r5 is_malformed_databaser : s
w~~j!! bgnnV&<&<
*
6
"
" (bZZ%= > >??F__&&F!9Q<4' ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( ( tts7 #C 2AC: C C C C C
C"!C"returnc # K t | } |V | n# | xY w | dS # | w xY w)zF
Provide a transactional scope around a series of operations.
)bindN)r rd rollbackr\ )rC rG s r5 r= r= J s
6"""G
s - A AA A4)r6 )F)2rM
contextlibr} loggingr r
sqlalchemyr r r r r r
r r r
r sqlalchemy.ext.declarativer sqlalchemy.engine.reflectionr sqlalchemy.ormr sqlalchemy.orm.sessionr sqlalchemy.excr rP r r< ri getLoggerr@ r r rW rB re rl rh r{ r r r r r r r contextmanagerr= r3 r4 r5 r sg
( ( ( ( ( ( ( ( j j j j j j j j j j j j j j j j j j j j j j j j 7 7 7 7 7 7 2 2 2 2 2 2 " " " " " " 5 5 5 5 5 5 ( ( ( ( ( (
f_
- . .0a 0a 0a 0a 0aD 0a 0a 0af 4x x x" 6 :) ) )
3 3 3R R R* " " " (% % % % 8 6
W r4