mirror of
https://github.com/tiennm99/thptqg.git
synced 2026-09-07 06:18:01 +00:00
main
6
Commits
| Author | SHA1 | Message | Date | |
|---|---|---|---|---|
 tiennm99
|
4cb0a4f340 |
feat: download the database instead of reading it over HTTP
Reading the file where it lay never worked well enough. Two costs were structural rather than bugs: reads are serial, because the worker uses synchronous XHR, so a name search touching 390 pages waited 17 seconds to move 608 KB — roughly one request per result row, which no page size removes — and the first visitor after each deploy waited ~26 seconds for the CDN to fill its cache with a 288 MB object. The browser now downloads the whole database once and queries it in memory with sql.js. A dataset page is gated behind that: the gate states what it will cost, in transfer and in memory, and offers only the download, because there is nothing to show without it. Dropping the structures that existed to make range-request queries index-driven halved the file. name_word carried one row per word of every name, about 3.5 million of them, and with the partial score indexes it was more than half of what every visitor would now download. Measured on rebuilt databases: 2016 went 288.6 -> 142.5 MB (31 MB gzipped on the wire), 2017 237.7 -> 119.3 MB, both with row counts and audits unchanged. Queries on the result: an exam number is immediate, a name scans all 877,460 rows in about 240 ms. Alternatives were measured before choosing this. sqlite-wasm-http sizes files from a HEAD Content-Length with no override, so on a host that gzips it silently uses the compressed size. DuckDB-WASM ships 32-37 MB of WebAssembly before its Parquet extension, more than this whole download. Static pre-generated shards are the most robust option but cannot answer arbitrary SQL, and cannot stop early the way LIMIT does. The published name loses its chunk index, the byte budgets and the SQL consent modal go with the range reads that made them necessary, and the docs no longer describe a design the site does not use. |
||
|
tiennm99
|
dbc23c25c5 |
feat: read the databases over HTTP range requests
The browser downloaded 45 MB of gzipped SQLite before it could answer anything. Now sql.js-httpvfs asks for the pages a query touches and the databases ship uncompressed as <id>.sqlite3 — a byte range of a gzip stream is not a byte range of a database. That only works if every query the site issues is index-driven, and measured against the real 2016 file, most were not: so_bao_danh = ? SEARCH via PK ~20 KB ho_ten_ascii LIKE '%x%' SCAN 127 MB ho_ten_ascii LIKE 'x%' SCAN 127 MB COUNT(*) covering index scan 20 MB ORDER BY toan DESC LIMIT 10 SCAN + temp b-tree 127 MB Prefix LIKE scans because SQLite's LIKE optimisation needs a NOCASE index; a range comparison does use the index. So the schema changed to suit the access pattern rather than the search changing to suit the schema. name_word holds one row per word of each name, WITHOUT ROWID so the table is the index, carrying ho_ten_ascii so a multi-word query is resolved inside a single b-tree. name_word_freq says which word of a query is rarest — the vocabulary is 4,397 words across 2.87M entries, so "buu loc" seeks on 287 entries rather than walking the 300,000 that "thi" would. Searching by any word of a name survives, at a few hundred KB a query. idx_ho_ten and idx_ho_ten_ascii are gone: no plan could use either. Partial indexes on toan, khtn and khxh cost 12 MB and keep the SQL presets off a full scan. The footer's candidate count now comes from datasets.json instead of COUNT(*). 2016 grows 223.5 MB to 288.6 MB, 2017 162.7 MB to 237.7 MB, and the site is 528 MB against the 1 GB GitHub Pages limit. Row counts are unchanged. The SQL tab is the one place a user can still write a query that reads the whole table, so it asks before it opens, runs under a byte budget that stops a runaway query, and shows what each query actually fetched. Verified: row counts through the assembler guards, every app query index-driven under EXPLAIN QUERY PLAN, and GitHub Pages returning 206 with a correct Content-Range. Not verified in a browser — this machine has none — and the library refuses to open a file the host compresses, so the deployed response headers need a look. |
||
|
tiennm99
|
219c7c6a69 |
fix(parser): read the two 2016 layouts that were column-shifted
Four of the 119 files in data/2016 publish one score column per subject instead of a DIEM_THI sentence, and none of them was being read correctly. The ĐH Công nghiệp Thực phẩm file puts a three-row ministry title block above its header, so no header was recognised and the positional fallback shifted every column by one: the serial number became so_bao_danh, the exam number became ho_ten, the name became ngay_sinh, and the national ID became the score cell. All 7,833 rows were unusable. The three ĐH Cần Thơ files name an SBD column but no DIEM_THI, so they fell to the same fallback: surname into ngay_sinh, given name into ten_cum_thi, birth date into the score cell, and 12,152 candidates with no scores at all. Both are now read by FormatSubjectColumns, which resolves identity and one column per subject from the header. The header is searched for in the first five rows, so a title block no longer hides it. The Cần Thơ score columns are numbered rather than named. They follow the order the exam was sat — each morning an essay paper, each afternoon a multiple-choice one — which is what identifies them: columns 1/3/5/7 quantise to 0.25 and 2/4/6/8 do not, and each column's mean lands within 0.5 of the same subject's mean across the rest of the dataset. The foreign language is filed under the subject its N1..N6 code names. Gender now accepts the 0/1 encoding those files use: of the rows marked 1, 53% carry "Thị" in the name against 1% of those marked 0. Birth dates in the compact ddmmyy form are expanded so the column holds one format. A score of 0 is stored rather than dropped, recovering 302 real scores that a JavaScript falsy check had been turning into NULL. Row count falls by one, to 877,460: the removed row is the title line "ĐƠN VỊ: / TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP THỰC PHẨM TP. HỒ CHÍ MINH", which had been stored as a student. The dataset has no duplicate exam numbers; the three rows previously described as collapsing were that same file's title and header lines being counted and then rejected. Also drops behaviour that existed only to match the parser this one replaced: the inert "SINH " header token, the untrimmed diem_thi cell, an unreachable blank-row branch, and a cross-check test against a database that can no longer exist. None of them changes output. Verified by rebuilding both datasets: 877,460 and 861,068 rows, both artifacts through the assembler's row and size guards, and the reader fidelity suite unchanged across all 182 files. |
||
|
tiennm99
|
933adf70c9 |
refactor: comments state current behavior, not project history
Comments across the tree justified the code by pointing at a Rust implementation that is no longer in the repository, citing files and line numbers (config.rs:132, schema.rs:26-54, reader.rs:42) that cannot be opened, plus crates and datasets that are equally gone. A reader could not check any of it. Every invariant those comments carried is kept and restated so it stands on its own: the bytewise sort that decides which row survives a duplicate exam number, the literal U+0300..U+036F range that must match the site's toAscii, the trailing space in "SINH ", the BIFF and shared-string corrections, the VACUUM-after-COMMIT rule, the deploy-from-main guard. The reader's contract is now anchored to the frozen oracle in parser/testdata, which still exists and is still checked, rather than to the tool that originally produced it. TestDDLMatchesRust becomes TestDDLIsFrozen: it compares against a copy of the DDL inside the test and never read schema.rs, so both the name and the failure message were misleading. ToAscii no longer claims the d-replacement must precede lowercasing. Both cases map to 'd' and ToLower runs last, so the order has no effect. |
||
|
tiennm99
|
a0420fdc37 |
refactor: remove the last JS script and the dead weight three audits found
The pipeline is now Go outside web/. differential-parity.mjs becomes
assembler/internal/verify, reachable as `assemble verify A B`. The port fixed a
real weakness: the JavaScript hashed each row's fields joined bare, so a value
shifted across a column boundary produced the same digest. A test now pins that.
The hub still rendered "Phiên bản cũ của trang 2017" above a permanently empty
list — it split datasets on id.includes("old"), and both such datasets are gone.
The heading and the filter are removed. index.html titled every page "THPT QG
2017", including 2016 and the hub, because one file is copied to every route;
the static title is now neutral and the app sets the dataset's own.
Dead code removed: the isOld2/containsOld branches in the stats block, which
only 2017-old2 could ever reach; SUBJECT_LABELS, DATASET_IDS and the unread
`short` subject field; an unused vite.svg and a favicon link to a file that
never existed; two unused CSS rules and --shadow-sm; site.Paths.Root.
Corrected comments that were confidently wrong rather than merely stale: the
reader claimed to be row-streaming when both implementations decode the whole
workbook into memory first, and the fidelity oracle still spoke of 299 input
files when it covers 182. Candidate counts in the hub now derive from
datasets.json instead of being written a second time as prose.
plans/ is emptied. The parity report it held was cited by docs/data-pipeline.md,
so the evidence that the recovered foreign-language scores are real — not the
citation, the four arguments themselves — is now inline there.
Verified: 2017 rebuilt after the writer change hashes identically to the build
before it.
|
||
|
tiennm99
|
c359a0b444 |
refactor: one directory per pipeline stage, and an assembler to drive them
The repository now reads as the pipeline it is: crawler fetches, parser converts, assembler verifies and publishes, with data/ and web/ as the stores they hand work through. go-parser is renamed parser now that there is no other. The assembler replaces build-db.js and assemble-site.js. It compiles the parser, builds and verifies each database, compresses it, runs the Vite build and assembles _site — one command, and the only place that knows the order. It also closes a real hole: nothing previously asserted that a database reached the site. An empty staging directory assembled happily, so every page rendered, every query 404d and CI stayed green. The row-count and size guards could not catch that, since they only run when a database was built at all. Removing Node from the root forced the dataset list out of web/src/datasets.js, which the assembler cannot import. datasets.json is now the registry both sides read — JSON because Go and the browser both parse it without a dependency — while presentation stays in the web app, keyed by id and cross-checked against the registry so a half-added dataset fails instead of half-working. Guards verified by making each one fail: a missing database, and an expected row count one higher than the truth. |