107 lines
2.7 KiB
TypeScript
107 lines
2.7 KiB
TypeScript
import createConnectionPool, { ConnectionPool, sql } from '@databases/pg'
|
|
import sqlite3 from 'better-sqlite3'
|
|
import S from 'jsonschema-definer'
|
|
import { PythonShell } from 'python-shell'
|
|
|
|
export async function main(db: ConnectionPool) {
|
|
const s3 = sqlite3('./assets/edict.db')
|
|
|
|
const rows = s3
|
|
.prepare(
|
|
/* sql */ `
|
|
SELECT "id", "entry", "reading", "english" FROM "edict"
|
|
`
|
|
)
|
|
.all()
|
|
|
|
const fPrev = new Set<string>(
|
|
await db
|
|
.query(
|
|
sql`
|
|
SELECT "entry" FROM "frequency"
|
|
`
|
|
)
|
|
.then((rs) => rs.map((r) => r.entry))
|
|
)
|
|
|
|
const py = new PythonShell('./scripts/freq.py', {
|
|
mode: 'text',
|
|
pythonOptions: ['-u'],
|
|
})
|
|
|
|
const fMap = new Map<string, number>()
|
|
py.on('message', (msg: string) => {
|
|
const [w = '', f] = msg.split('=')
|
|
fMap.set(w, Number(f))
|
|
})
|
|
;[...new Set(rows.flatMap((r) => JSON.parse(r.entry)))]
|
|
.filter((v) => !fPrev.has(v))
|
|
.map((v) => py.send(v))
|
|
|
|
await new Promise<void>((resolve, reject) => {
|
|
py.end((err) => (err ? reject(err) : resolve()))
|
|
})
|
|
|
|
const batchSize = 5000
|
|
|
|
if (fMap.size) {
|
|
const fRow = [...fMap]
|
|
for (let i = 0; i < fRow.length; i += batchSize) {
|
|
console.log(i)
|
|
|
|
await db.query(sql`
|
|
INSERT INTO "frequency" ("entry", "frequency")
|
|
VALUES ${sql.join(
|
|
fRow.slice(i, i + batchSize).map(([k, v]) => sql`(${k}, ${v})`),
|
|
','
|
|
)}
|
|
ON CONFLICT ("entry")
|
|
DO UPDATE SET "frequency" = EXCLUDED."frequency";
|
|
`)
|
|
}
|
|
}
|
|
|
|
for (let i = 0; i < rows.length; i += batchSize) {
|
|
console.log(i)
|
|
const sublot = rows.slice(i, i + batchSize)
|
|
|
|
await db.query(sql`
|
|
INSERT INTO "entry" ("userId", "type", "entry", "key", "reading", "translation")
|
|
VALUES ${sql.join(
|
|
sublot.map((r) => {
|
|
const reading = JSON.parse(r.reading)
|
|
const english = JSON.parse(r.english)
|
|
|
|
return sql`(uuid_nil(), 'vocabulary', ${JSON.parse(
|
|
r.entry
|
|
)}, ${`edict:${r.id}`}, ${{
|
|
_: reading.length ? reading : undefined,
|
|
}}, ${{
|
|
en: english.length ? english : undefined,
|
|
}})`
|
|
}),
|
|
','
|
|
)}
|
|
ON CONFLICT DO NOTHING;
|
|
`)
|
|
}
|
|
}
|
|
|
|
export async function constraint(db: ConnectionPool) {
|
|
const sReading = S.object().additionalProperties(S.list(S.string()))
|
|
|
|
const colName = sql`reading`
|
|
const constraintName = sql`c_entry_${colName}`
|
|
|
|
await db.query(sql`
|
|
ALTER TABLE "entry" DROP CONSTRAINT IF EXISTS ${constraintName};
|
|
ALTER TABLE "entry" ADD CONSTRAINT ${constraintName} CHECK (validate_json_schema('${sql.__dangerous__rawValue(
|
|
JSON.stringify(sReading.valueOf())
|
|
)}', ${colName}))
|
|
`)
|
|
}
|
|
|
|
if (require.main === module) {
|
|
constraint(createConnectionPool({ bigIntMode: 'number' }))
|
|
}
|