NAME
oEdtk::DBAdmin - oEdtk database administration, schema upkeep and bulk CSV loading
SYNOPSIS
use oEdtk::DBAdmin qw(db_connect schema_Create csv_import);
my $dbh = db_connect( $cfg, 'EDTK_DBI_DSN' );
schema_Create($dbh); # build the whole schema
csv_import( $dbh, $table, $file, ref_col => 'id' );DESCRIPTION
oEdtk::DBAdmin creates, detects, upgrades and maintains the oEdtk database and loads CSV data into it, across PostgreSQL, MySQL / MariaDB, Oracle and SQLite. The hardening work of the DBAdmin chantier (see docs/dbadmin/) added the tuning keys and the schema-detection / staleness helpers documented below. The public entry points are grouped by area; the private helpers are not listed.
CONNECTION
db_connect( $cfg, $key )
Connect to the database named by the configuration key $key (for example EDTK_DBI_DSN), applying the per-driver normalisation the module expects.
db_check_privileges( $dbh, %opt )
Read-only probe of the privileges the account holds (CREATE, DROP, INDEX, ALTER, SEQUENCE); never blocking. Returns a hash with the granted set, the missing one and an optional error. Option table targets the ownership checks (PostgreSQL).
db_invalidate_meta_cache
Drop the column-metadata cache used by csv_import. Call it after a schema change (schema_Create / schema_Upgrade) so the next import re-reads the columns instead of reusing a stale description.
SCHEMA
schema_Create( $dbh )
Create the whole T schema: the base tables, the configuration-driven tables and the tracking table.
schema_Upgrade( $dbh, %opt )
Bring an existing base up to the current schema (delegates to the historisation path, see schema_Historize).
schema_Detect( $dbh, %opt )
Inspect a live base and report its detected level against the T definitions. Option index_table names the index table (defaults to EDTK_DBI_OUTMNGR). READ-ONLY: every statement is a SELECT / PRAGMA; nothing is written.
schema_Report( $dbh, %opt )
The schema_Detect result as printable text.
schema_Manifest( ... )
The ordered, de-duplicated list of tables the migration covers: the fixed base tables, the configuration-driven ones, the tracking table and any extension table.
schema_MigrationPlan( $dbh, %opt )
Read-only plan of an update by historisation (--check-only / --dry-run): the detected level, the per-table column sets and the copy SQL. Writes nothing.
schema_Historize( $dbh, %opt )
Perform the update by historisation. Options: stamp, manifest, checkpoint (a code ref called after each table's copy; a die aborts and rolls back), keep_old (default 1, the stamped tables are kept) and force. An already-T base is a no-op (result field noop). Returns a result hash.
schema_Revert( $dbh, %opt )
Invert a historisation before resuming the service: put the stamped tables back under their original names, dropping the current target. Option stamp (required) is the one the migration used.
schema_AdditivePlan( $dbh, %opt )
Read-only preview of the in-place additive update: what schema_Additive would add, widen, create or change. Option with_constraints (0|1). Writes nothing (every statement is a SELECT / PRAGMA). Returns a plan hash: ok, level, driver, detect, create, add, widen, key, add_sql, widen_sql, key_sql, violations and report. When with_constraints asks for a key change on a driver that cannot perform it in place (SQLite, NC), the plan reports it as a violation instead of rendering DDL that the update would refuse.
schema_Additive( $dbh, %opt )
Perform the in-place additive update. Options: manifest, with_constraints (0|1), checkpoint and force. A base that already conforms to T with nothing to add or widen is a no-op (result field noop). Returns a result hash: created, added, widened, key_changed, violations and error.
schema_StaleWarning( $dbh, %opt )
Report a stale schema ONCE, READ-ONLY, never fatal. Options: cfg, index_table, tracking_table (all default to the EDTK_DB configuration). Returns the probe when a warning was emitted, undef otherwise. It never writes.
schema_History_add( $dbh, %named )
Append one history row: date, from, to, mode, tool and counts (a hash ref, stored as a compact "k=v;k=v" string).
historicize_table( $dbh, %opt )
Historise one table (used by the migration).
create_SCHEMA( $dbh, $table, $maxkeys )
Legacy entry point kept for the fork: it creates the whole schema (the canonical schema_Create) plus the TRACKING table the fork also created.
create_lot_sequence( $dbh )
Create the lot-sequence counter table (needed by SQLite, which has no SEQUENCE).
create_table_TRACKING / OUTMNGR / DISTRIB / ACQUIT / ACQMGENTECH / ... ( $dbh, ... )
Create one table of the schema: TRACKING, OUTMNGR, DISTRIB, ACQUIT, ACQMGENTECH, SCHEMA_HISTORY, ADMIN, AGREGE, DATAGROUPS, FILIERES, ID, INDEX, LOTS, PARA, REFIDDOC, SUPPORTS. create_table_INDEX is a fork alias of create_table_OUTMNGR; create_table_ACQMGENTECH targets the optional oEUser extension and is a silent no-op when it is not installed.
LOADING DATA
csv_import( $dbh, $table, $file, %opt )
Import a CSV file into a table (the historic row-by-row API, unchanged). The keys and parameters that tune the PostgreSQL bulk path are below.
insert_tData( $dbh, %opt )
Insert one row (used by the csv_import row-by-row path).
BULK LOAD TUNING (PostgreSQL)
EDTK_DB_BURST_MO
Above this size, in MiB, a PostgreSQL insert switches to the ELT staging path: the file is copied raw into an unconstrained TEMP table and moved with a single INSERT ... SELECT, skipping the per-row Perl parse. 0 disables the path; the shipped default is 50 MiB. The caller parameter burst_mo overrides it, pg_copy_staging (0 or 1) forces either path regardless of size, and force_copy (true) forces the parsed COPY path ahead of every other setting (the packaged tracker exposes it as --forceCopy).
The ELT path additionally requires an explicit source encoding (pg_copy_encoding or EDTK_DB_COPY_ENCODING). Without it the load logs _load_csv_pg_copy: ELT unavailable (ELT needs an explicit source encoding) and falls back to the parsed COPY path.
EDTK_DB_COPY_ENCODING
Source encoding of the CSV bytes, declared to the PostgreSQL COPY. It is required to enable the ELT path: an explicit value (the caller parameter pg_copy_encoding, else this key) makes the ELT declare ENCODING '<name'>. When unset, the parsed COPY path keeps its historical per-field heuristic and the ELT is unavailable (it falls back to parsed COPY as described above). The name must match the file's real encoding; an unknown value is rejected with a message instead of being passed silently.
EDTK_DB_DEFER_INDEX_RATIO, EDTK_DB_DEFER_INDEX_MIN_ROWS
A PostgreSQL bulk insert drops the secondary indexes up-front and rebuilds them afterwards only when the incoming row count is at least EDTK_DB_DEFER_INDEX_RATIO times the table's estimated row count (default 0.1) and at least EDTK_DB_DEFER_INDEX_MIN_ROWS rows (default 10000). The caller parameters pg_defer_index_ratio and pg_defer_index_min_rows take precedence.
MAINTENANCE
acheck_db_admin( %opt )
The daily "first process of the day" maintenance, driven by the edtk_admin day row.
acheck_db_admin_on_connect( $dbh, $cfg )
Claim the day lock on connect and run the maintenance once per day.
admin_optimize_db( $dbh, %opt )
Dispatch a per-engine REPAIR / OPTIMIZE (VACUUM ANALYZE on PostgreSQL) over the maintenance tables, on a single connection.
agrege_idJob( %opt )
Aggregate one job's distribution rows.
copy_table / move_table / db_drop_table ( $dbh, $table, $target, %opt )
Copy a table, move it (copy then drop) or drop it.
get_columns_names( $dbh, $table )
Read the driver-neutral column catalogue; the names keep the catalogue's case.
BACKUP BUFFER
db_backup_available( $dbh ) / db_on_backup( $dbh )
Whether a distinct backup database is configured, and whether this handle points at the backup buffer.
db_backup_pending( $dbh )
Count the pending rows per buffer table (read-only).
db_backup_prepare( $dbh )
Guarantee the backup buffer can hold the event tables.
db_backup_agent( %opt )
Archive the event tables into the buffer, then purge them (the daily backup job). It is not deprecated: it archives three tables and then purges.
db_reconcile_backup( %opt )
Copy every pending row of the backup buffer back into the main database. Never dies; returns a report hashref. Options: CFG, tables, dry_run, keep_backup.
EXPORTED VARIABLES
@INDEX_COLS / @TRACKER_COLS
The ordered [ name, ddl-type ] lists of the index table's and the tracking table's columns.
SEE ALSO
docs/dbadmin/, dbadmin_optim.md, PLAN-DBAdmin-optimisation-durcissement.md.
AUTHOR
The oEdtk team.