LEARNITIVE SPX PREPROCESSOR ================================== SPX Preprocessor Version: 1.0.0 Import Package Format: learnitive-learn-import/v1 PURPOSE ------- This tool converts the historical SPX production LMS database into a validated, portable Learnitive Learn import package. SPX SQL / spX_db.zip -> tools/spx-import/spx-preprocess.mjs -> isolated temporary MySQL database -> SPX relational reader -> Editor.js normalization -> legacy runtime and [input] conversion -> media extraction/checksum deduplication -> quiz conversion -> validation/checksums -> spx-learn-import-YYYYMMDD-XXXXXXXX.zip -> /admin/learn/import The preprocessor does NOT directly modify Learnitive production Learn tables, does NOT merge legacy SQL into learn_* tables, does NOT require the Learnitive web application to be running, and does NOT require the production Learn database password. DO NOT import the SPX SQL dump directly into the Learnitive production database. REQUIREMENTS ------------ * The Node.js version supported by the matching Learnitive release. * pnpm 11.6.0 when invoking the package script, or Node.js directly. * MySQL 8.0 or newer and the `mysql` command-line client. * A MySQL administrator account that can create/drop one temporary database and one temporary local user. The temporary user receives privileges only on the temporary SPX database. * `zip` and `unzip` command-line tools. * Temporary disk space substantially larger than the compressed SPX archive. A historical dump can be hundreds of megabytes and extracted media/package working files require additional space. MySQL administrator connection variables (all optional when local root socket access is already configured): SPX_MYSQL_SOCKET=/var/run/mysqld/mysqld.sock SPX_MYSQL_HOST=127.0.0.1 SPX_MYSQL_PORT=3306 SPX_MYSQL_ADMIN_USER=root SPX_MYSQL_ADMIN_PASSWORD=... If SPX_MYSQL_SOCKET is set it is preferred over host/port. Passwords are passed to child `mysql` processes through their environment and are never written into the generated import package. BASIC USAGE ----------- From the Learnitive source directory: pnpm spx:preprocess \ --input /path/to/spX_db.zip \ --output ./spx-output Equivalent direct invocation: node tools/spx-import/spx-preprocess.mjs \ --input /path/to/spX_db.zip \ --output ./spx-output The input may be a .zip containing exactly one .sql file or a direct .sql file. The source SHA-256 is calculated before any database work begins. ANALYZE ONLY ------------ pnpm spx:preprocess \ --input /path/to/spX_db.zip \ --output ./spx-analysis \ --analyze-only Analyze-only still imports the SQL into the isolated temporary MySQL database, because MySQL escaping and JSON content must be interpreted by MySQL rather than by an ad-hoc SQL text parser. It writes spx-analysis.json and does not build a Learn import ZIP. TEST ONE COURSE FIRST --------------------- pnpm spx:preprocess \ --input /path/to/spX_db.zip \ --course 2038 \ --output ./spx-test-course Recommended validation sequence: 1. Preprocess one representative course. 2. Inspect paragraph/list/table conversion. 3. Process a code-heavy course. 4. Verify historical [input] conversion. 5. Process an image-heavy course. 6. Verify media extraction and checksums. 7. Verify section/curriculum ordering. 8. Import the package using /admin/learn/import. 9. Inspect learner rendering. 10. Only then preprocess/import the complete historical dataset. A source-ID range can also be selected: --course-range 2038:2045 The range is inclusive and refers to SPX webinar/course IDs, not display order. FULL DATASET ------------ pnpm spx:preprocess \ --input /path/to/spX_db.zip \ --output ./spx-full A successful run produces a package named similar to: spx-learn-import-20260908-01234567.zip The package contains: manifest.json report.json courses.ndjson sections.ndjson lessons.ndjson quizzes.ndjson questions.ndjson provenance.ndjson media/ media-manifest.ndjson warnings.ndjson checksums.sha256 NDJSON is one JSON object per line. This lets the Learn importer stream large sets instead of loading thousands of lessons into memory at once. SUPPORTED OPTIONS ----------------- --input SPX .zip or .sql source. Required. --output Output directory. Required. --course Process one SPX course ID. May be repeated. --course-range Inclusive SPX course-ID range. --dry-run Convert and validate into the work/package directory but do not create the final ZIP. --analyze-only Import and analyze the source, writing spx-analysis.json, but do not run the package conversion. --keep-temp Preserve the temporary MySQL database/user after a successful run for debugging. Without this switch normal successful/handled-failure cleanup drops the temporary database and restricted user. An abruptly killed process can leave temporary resources behind; see Troubleshooting. --verbose Print additional diagnostic/progress information. --strict Treat any conversion warning as a failed validation and do not create the final package. --continue-on-warning Allow package creation with warnings. This is the default behavior and is provided explicitly for automation/readability. --external-media retain Preserve supported external references with warnings (default). download Attempt a bounded HTTPS download into package media. skip Do not internalize external media; preserve an import warning. --default-course-status Set the normalized package course status. Default is draft. The Learn administrator import flow can apply its own explicit policy. --resume Resume/reuse a compatible preprocessing work directory. Source SHA-256 and conversion options must match. --version Print tool and package-format versions. --help Print command-line help. TEMPORARY MYSQL SAFETY ---------------------- The tool creates a random database named like: learnitive_spx_import_ and a random local MySQL user named like: is_spx_ That user receives privileges only on the temporary database. The historical SQL dump is piped into this isolated database. The tool does not ask MySQL to load the dump into the Learnitive application database. On a normal run the temporary database/user are removed at completion. They are also removed on handled failures unless --keep-temp was requested. An abrupt SIGKILL/power failure may prevent cleanup. In that situation, identify only the learnitive_spx_import_* database and matching is_spx_* user recorded in the work state before manually dropping them. Never drop production databases. RESUME SUPPORT -------------- A normal run creates a working directory under the output directory similar to: .spx-work-/ state.json package/ state.json is permission-restricted and keyed to both the source SHA-256 and the conversion option hash. If an interrupted process left its isolated temporary database available, --resume can reuse it. If the database was cleaned up, run the source again rather than modifying state.json manually. Example: pnpm spx:preprocess \ --input /path/to/spX_db.zip \ --resume /path/to/spx-output/.spx-work-0123456789ab \ --output /path/to/spx-output A different source checksum or incompatible options cause resume to fail closed. RUNTIME CONVERSION ------------------ Historical runtime/Judge0-style identifiers are converted to current Learnitive project modes. The explicit mapping is maintained in: tools/spx-import/mappings/runtimes.json Examples: python_91_text/x-python -> python cpp_54_text/x-c++src -> cpp Linux_10005_text/x-sh -> shell bash_46_text/x-sh -> shell c_50_text/x-csrc -> c java_62_text/x-java -> java Unknown identifiers are reported. A conservative recognizable-name heuristic may produce a warning-labelled mapping, but unknown data is never silently thrown away and old numeric Judge0 IDs are not used as current runtime IDs. LEGACY [input] CONVERSION ------------------------- Historical code blocks may contain: program source [input] stdin data The preprocessor converts this to separate normalized fields: code: program source stdin: stdin data The Learn-side importer does not need to understand the legacy [input] marker. MEDIA ----- Embedded base64 Editor.js image data is: decoded -> MIME validated -> size bounded -> dimensions inspected where supported -> SHA-256 hashed -> deduplicated -> written to package media/ -> referenced by a package mediaImportRef The preprocessor does not create production learn_media IDs. The Learn importer creates authorized managed media records when the administrator commits the package. External media is handled according to --external-media. Downloads, when explicitly enabled, require HTTPS, have time/byte limits, validate image MIME, and are stored as package media only after validation. Unavailable or unsupported external content is reported rather than silently lost. UNKNOWN / INVALID EDITOR.JS BLOCKS ---------------------------------- Unknown Editor.js blocks are preserved as explicit `legacyImport` blocks with sanitized original block data and a reason. Invalid Editor.js documents are also preserved as import-warning content. They are never silently discarded. Review affected lessons using warnings.ndjson and report.json after package creation, then again in /admin/learn/import before committing. VALIDATION STATES ----------------- PASS No conversion warnings were generated. PASS WITH WARNINGS Package is valid but administrator review is recommended. FAIL A hard validation error occurred, or --strict converted warnings into a failure. Warnings never authorize silently dropping unknown historical content. CHECKSUMS AND PACKAGE INTEGRITY ------------------------------- The original SPX source SHA-256 is stored in manifest.json. Every generated package file/media object is recorded in checksums.sha256. Media records also carry their individual SHA-256 values. /admin/learn/import must verify the format version, safe ZIP paths, unpacked-size limits, required files, checksums, NDJSON limits and cross references before any Learn database write. Do not edit checksums.sha256 to bypass validation. IMPORTING INTO LEARNITIVE -------------------------------- After preprocessing, use: /admin/learn/import Expected workflow: Upload package -> Verify -> Analyze -> Preview -> Select courses -> Choose import behavior -> Import Import modes: Skip unchanged Update changed Create copy The Learn importer owns current-schema writes, transactions, media registration, access defaults and administrator authorization. The preprocessing CLI never owns the production Learn schema. IDEMPOTENCY ----------- Each normalized source entity has deterministic SPX provenance and a source hash. The Learn importer records source system/entity/ID/hash and target IDs. same source ID + same hash -> unchanged same source ID + changed hash -> update available Create copy explicitly selected -> intentional new copy Administrators should not have to manually determine which of thousands of lessons have already been imported. TROUBLESHOOTING --------------- mysql_client_not_found Install the MySQL 8 client and make `mysql` available in PATH. spx_requires_mysql_8_or_newer Use MySQL 8.0 or newer. The source dump contains MySQL 8 collations/features. MySQL connection/authentication failure Verify SPX_MYSQL_* administrator settings. Do not substitute production app DB credentials merely to make the tool connect. Insufficient disk space Remove incomplete output after verifying it is not needed; provide more temporary space and rerun/resume. spx_zip_requires_one_sql / corrupt ZIP Provide the original SPX archive containing exactly one SQL dump. unknown_runtime / runtime_heuristic_mapping Review mappings/runtimes.json and the affected lesson before bulk import. invalid_editorjs_json / unsupported block Review warnings.ndjson; the source is preserved as legacyImport content. invalid/corrupt base64 media Review the affected lesson. Do not disable media validation. external_media_unavailable Retry with network access if appropriate, or retain the URL/warning for administrator repair. interrupted processing Use --resume with the exact work directory and unchanged source/options. checksum failure during Learn import Recreate the package from the original SPX source. Do not bypass checksum validation. unsupported package version Use a compatible Learnitive release/preprocessor or a documented package upgrade path. SECURITY WARNINGS ----------------- * Protect SPX database archives; historical dumps can contain private data. * Never expose production database credentials to generated import packages. * Never execute untrusted SQL against the production Learnitive DB. * Never bypass checksum, path or media validation. * Never add arbitrary host-path extraction to the ZIP workflow. * Do not place API keys, passwords or other application secrets in import data. * Inspect warnings before a bulk import. VERSION INFORMATION ------------------- Run: pnpm spx:preprocess --version or: node tools/spx-import/spx-preprocess.mjs --version Package format v1 is deliberately independent of Learn database migration numbers. Future Learn schemas should continue to translate supported package versions through the trusted Learn importer rather than having the offline CLI write production SQL.