63 lines
2.0 KiB
TypeScript
63 lines
2.0 KiB
TypeScript
/** Offline, explicit import into an EMPTY migrated PostgreSQL database. */
|
|
import { DatabaseSync } from 'node:sqlite'
|
|
import { createDatabase } from '@trumpsignal/database'
|
|
import columns from '../migration/database-columns.json'
|
|
const source = process.argv[2],
|
|
url = process.env.DATABASE_URL
|
|
if (!source || !url)
|
|
throw new Error('Usage: DATABASE_URL=... tsx scripts/import-sqlite.ts /absolute/backup.sqlite')
|
|
const sqlite = new DatabaseSync(source, { readOnly: true }),
|
|
{ pool, lockPool } = createDatabase(url),
|
|
client = await pool.connect()
|
|
const booleanNames = new Set([
|
|
'relevant',
|
|
'active',
|
|
'paper_mode',
|
|
'auto_trade',
|
|
'trump_enabled',
|
|
'macro_enabled',
|
|
'grow_mode',
|
|
'talks_vs_trades_flag',
|
|
'alerts_enabled',
|
|
'alert_trump',
|
|
'alert_btc_bottom',
|
|
'alert_funding',
|
|
'alert_kol_divergence',
|
|
'digest_enabled',
|
|
])
|
|
try {
|
|
await client.query('BEGIN')
|
|
for (const table of Object.keys(columns)) {
|
|
const count = await client.query<{ n: string }>(`SELECT count(*) AS n FROM "${table}"`)
|
|
if (Number(count.rows[0].n) > 0) throw new Error('Destination must be empty: ' + table)
|
|
}
|
|
for (const [table, names] of Object.entries(columns)) {
|
|
const rows = sqlite
|
|
.prepare(`SELECT ${names.map((n) => '"' + n + '"').join(',')} FROM "${table}"`)
|
|
.all()
|
|
for (const row of rows) {
|
|
const values = names.map((n) =>
|
|
booleanNames.has(n) && row[n] != null ? !!row[n] : (row[n] ?? null),
|
|
)
|
|
await client.query(
|
|
`INSERT INTO "${table}" (${names.map((n) => '"' + n + '"').join(',')}) VALUES (${names.map((_, i) => '$' + (i + 1)).join(',')})`,
|
|
values,
|
|
)
|
|
}
|
|
await client.query(
|
|
`SELECT setval(pg_get_serial_sequence($1,'id'),coalesce(max(id),1),max(id) IS NOT NULL) FROM "${table}"`,
|
|
[table],
|
|
)
|
|
}
|
|
await client.query('COMMIT')
|
|
process.stdout.write('Import completed. Source opened read-only.\n')
|
|
} catch (e) {
|
|
await client.query('ROLLBACK')
|
|
throw e
|
|
} finally {
|
|
sqlite.close()
|
|
client.release()
|
|
await pool.end()
|
|
await lockPool.end()
|
|
}
|