| 1 | import type { createClient } from '@libsql/client' |
| 2 | |
| 3 | type LibSqlClient = ReturnType<typeof createClient> |
| 4 | |
| 5 | /** |
| 6 | * Patch: add version and style_case columns to styles table. |
| 7 | */ |
| 8 | export const patchStylesColumns = async (client: LibSqlClient): Promise<void> => { |
| 9 | const cols = await client.execute("PRAGMA table_info('styles')") |
| 10 | const columnNames = new Set(cols.rows.map((r) => r.name as string)) |
| 11 | |
| 12 | if (!columnNames.has('version')) { |
| 13 | await client.execute("ALTER TABLE styles ADD COLUMN version TEXT NOT NULL DEFAULT '1.0.0'") |
| 14 | } else { |
| 15 | await migrateStyleVersionToText(client, cols.rows as Array<Record<string, unknown>>) |
| 16 | } |
| 17 | const nextColumnNames = await getTableColumnNames(client, 'styles') |
| 18 | if (!nextColumnNames.has('style_case')) { |
| 19 | await client.execute("ALTER TABLE styles ADD COLUMN style_case TEXT NOT NULL DEFAULT ''") |
| 20 | } |
| 21 | if (!nextColumnNames.has('style_name_zh')) { |
| 22 | await client.execute("ALTER TABLE styles ADD COLUMN style_name_zh TEXT NOT NULL DEFAULT ''") |
| 23 | await client.execute("UPDATE styles SET style_name_zh = style_name WHERE style_name_zh = ''") |
| 24 | } |
| 25 | if (!nextColumnNames.has('style_name_en')) { |
| 26 | await client.execute("ALTER TABLE styles ADD COLUMN style_name_en TEXT NOT NULL DEFAULT ''") |
| 27 | } |
| 28 | if (!nextColumnNames.has('package_dir')) { |
| 29 | await client.execute("ALTER TABLE styles ADD COLUMN package_dir TEXT NOT NULL DEFAULT ''") |
| 30 | } |
| 31 | if (!nextColumnNames.has('active')) { |
| 32 | await client.execute('ALTER TABLE styles ADD COLUMN active INTEGER NOT NULL DEFAULT 1') |
| 33 | } |
| 34 | if (!nextColumnNames.has('favorite_at')) { |
| 35 | await client.execute('ALTER TABLE styles ADD COLUMN favorite_at INTEGER') |
| 36 | } |
| 37 | await client.execute(` |
| 38 | CREATE TABLE IF NOT EXISTS session_style_snapshots ( |
| 39 | id TEXT PRIMARY KEY, |
| 40 | session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE, |
| 41 | style_id TEXT NOT NULL, |
| 42 | style_key TEXT NOT NULL, |
| 43 | style_name TEXT NOT NULL, |
| 44 | style_name_zh TEXT NOT NULL DEFAULT '', |
| 45 | style_name_en TEXT NOT NULL DEFAULT '', |
| 46 | description TEXT NOT NULL DEFAULT '', |
| 47 | category TEXT NOT NULL DEFAULT '', |
| 48 | aliases TEXT NOT NULL DEFAULT '[]', |
| 49 | source TEXT NOT NULL, |
| 50 | version TEXT NOT NULL DEFAULT '1.0.0', |
| 51 | style_case TEXT NOT NULL DEFAULT '', |
| 52 | package_dir TEXT NOT NULL DEFAULT '', |
| 53 | style_skill TEXT NOT NULL DEFAULT '', |
| 54 | created_at INTEGER NOT NULL |
| 55 | ) |
| 56 | `) |
| 57 | await ensureSessionSnapshotColumn(client, 'style_name_zh', "TEXT NOT NULL DEFAULT ''") |
| 58 | await ensureSessionSnapshotColumn(client, 'style_name_en', "TEXT NOT NULL DEFAULT ''") |
| 59 | await ensureSessionSnapshotColumn(client, 'package_dir', "TEXT NOT NULL DEFAULT ''") |
| 60 | await client.execute( |
| 61 | 'CREATE UNIQUE INDEX IF NOT EXISTS session_style_snapshots_session_id_unique ON session_style_snapshots(session_id)' |
| 62 | ) |
| 63 | await backfillSessionStyleSnapshots(client) |
| 64 | } |
| 65 | |
| 66 | const getTableColumnNames = async (client: LibSqlClient, tableName: string): Promise<Set<string>> => { |
| 67 | const cols = await client.execute(`PRAGMA table_info('${tableName}')`) |
| 68 | return new Set(cols.rows.map((row) => row.name as string)) |
| 69 | } |
| 70 | |
| 71 | const normalizeVersion = (value: unknown): string => { |
| 72 | const raw = String(value ?? '').trim().replace(/^v/i, '') |
| 73 | if (!raw) return '1.0.0' |
| 74 | const parts = raw |
| 75 | .split(/[.-]/) |
| 76 | .slice(0, 3) |
| 77 | .map((part) => { |
| 78 | const parsed = Number.parseInt(part, 10) |
| 79 | return Number.isFinite(parsed) && parsed >= 0 ? parsed : 0 |
| 80 | }) |
| 81 | while (parts.length < 3) parts.push(0) |
| 82 | if (parts.every((part) => part === 0) && !/^0+(?:[.-]0+){0,2}$/.test(raw)) return '1.0.0' |
| 83 | return parts.join('.') |
| 84 | } |
| 85 | |
| 86 | const migrateStyleVersionToText = async ( |
| 87 | client: LibSqlClient, |
| 88 | rows: Array<Record<string, unknown>> |
| 89 | ): Promise<void> => { |
| 90 | const versionColumn = rows.find((row) => row.name === 'version') |
| 91 | const type = String(versionColumn?.type || '').toUpperCase() |
| 92 | if (type.includes('TEXT')) { |
| 93 | const existing = await client.execute('SELECT id, version FROM styles') |
| 94 | for (const row of existing.rows) { |
| 95 | const record = row as Record<string, unknown> |
| 96 | const id = String(record.id || '') |
| 97 | if (!id) continue |
| 98 | const normalized = normalizeVersion(record.version) |
| 99 | if (normalized !== String(record.version || '')) { |
| 100 | await client.execute({ |
| 101 | sql: 'UPDATE styles SET version = ? WHERE id = ?', |
| 102 | args: [normalized, id] |
| 103 | }) |
| 104 | } |
| 105 | } |
| 106 | return |
| 107 | } |
| 108 | |
| 109 | const legacyColumnNames = new Set(rows.map((row) => row.name as string)) |
| 110 | const legacyColumn = (name: string, fallback: string): string => |
| 111 | legacyColumnNames.has(name) ? `COALESCE(${name}, ${fallback})` : fallback |
| 112 | await client.execute('DROP INDEX IF EXISTS idx_styles_style') |
| 113 | await client.execute('ALTER TABLE styles RENAME TO styles_legacy_version') |
| 114 | await client.execute(` |
| 115 | CREATE TABLE styles ( |
| 116 | id TEXT PRIMARY KEY, |
| 117 | style TEXT UNIQUE NOT NULL, |
| 118 | style_name TEXT NOT NULL, |
| 119 | style_name_zh TEXT NOT NULL DEFAULT '', |
| 120 | style_name_en TEXT NOT NULL DEFAULT '', |
| 121 | description TEXT NOT NULL DEFAULT '', |
| 122 | category TEXT NOT NULL DEFAULT '', |
| 123 | aliases TEXT NOT NULL DEFAULT '[]', |
| 124 | source TEXT NOT NULL DEFAULT 'custom', |
| 125 | style_skill TEXT NOT NULL DEFAULT '', |
| 126 | version TEXT NOT NULL DEFAULT '1.0.0', |
| 127 | style_case TEXT NOT NULL DEFAULT '', |
| 128 | package_dir TEXT NOT NULL DEFAULT '', |
| 129 | active INTEGER NOT NULL DEFAULT 1, |
| 130 | favorite_at INTEGER, |
| 131 | created_at INTEGER NOT NULL, |
| 132 | updated_at INTEGER NOT NULL |
| 133 | ) |
| 134 | `) |
| 135 | await client.execute(` |
| 136 | INSERT INTO styles ( |
| 137 | id, style, style_name, description, category, aliases, source, style_skill, |
| 138 | version, style_case, style_name_zh, style_name_en, package_dir, active, favorite_at, created_at, updated_at |
| 139 | ) |
| 140 | SELECT |
| 141 | id, style, style_name, |
| 142 | COALESCE(description, ''), |
| 143 | COALESCE(category, ''), |
| 144 | COALESCE(aliases, '[]'), |
| 145 | COALESCE(source, 'custom'), |
| 146 | COALESCE(style_skill, ''), |
| 147 | '1.0.0', |
| 148 | ${legacyColumn('style_case', "''")}, |
| 149 | style_name, |
| 150 | '', |
| 151 | '', |
| 152 | ${legacyColumn('active', '1')}, |
| 153 | ${legacyColumn('favorite_at', 'NULL')}, |
| 154 | created_at, |
| 155 | updated_at |
| 156 | FROM styles_legacy_version |
| 157 | `) |
| 158 | const existing = await client.execute('SELECT id, version FROM styles_legacy_version') |
| 159 | for (const row of existing.rows) { |
| 160 | const record = row as Record<string, unknown> |
| 161 | const id = String(record.id || '') |
| 162 | if (!id) continue |
| 163 | await client.execute({ |
| 164 | sql: 'UPDATE styles SET version = ? WHERE id = ?', |
| 165 | args: [normalizeVersion(record.version), id] |
| 166 | }) |
| 167 | } |
| 168 | await client.execute('DROP TABLE styles_legacy_version') |
| 169 | await client.execute('CREATE UNIQUE INDEX IF NOT EXISTS idx_styles_style ON styles(style)') |
| 170 | } |
| 171 | |
| 172 | const backfillSessionStyleSnapshots = async (client: LibSqlClient): Promise<void> => { |
| 173 | await client.execute(` |
| 174 | INSERT OR IGNORE INTO session_style_snapshots ( |
| 175 | id, |
| 176 | session_id, |
| 177 | style_id, |
| 178 | style_key, |
| 179 | style_name, |
| 180 | style_name_zh, |
| 181 | style_name_en, |
| 182 | description, |
| 183 | category, |
| 184 | aliases, |
| 185 | source, |
| 186 | version, |
| 187 | style_case, |
| 188 | package_dir, |
| 189 | style_skill, |
| 190 | created_at |
| 191 | ) |
| 192 | SELECT |
| 193 | lower(hex(randomblob(16))), |
| 194 | sessions.id, |
| 195 | chosen.id, |
| 196 | chosen.style, |
| 197 | chosen.style_name, |
| 198 | COALESCE(chosen.style_name_zh, chosen.style_name), |
| 199 | COALESCE(chosen.style_name_en, ''), |
| 200 | COALESCE(chosen.description, ''), |
| 201 | COALESCE(chosen.category, ''), |
| 202 | COALESCE(chosen.aliases, '[]'), |
| 203 | COALESCE(chosen.source, 'custom'), |
| 204 | COALESCE(chosen.version, '1.0.0'), |
| 205 | COALESCE(chosen.style_case, ''), |
| 206 | COALESCE(chosen.package_dir, ''), |
| 207 | COALESCE(chosen.style_skill, ''), |
| 208 | strftime('%s', 'now') |
| 209 | FROM sessions |
| 210 | LEFT JOIN styles AS by_id ON by_id.id = sessions.style_id |
| 211 | LEFT JOIN styles AS by_style ON by_style.style = sessions.style_id |
| 212 | LEFT JOIN styles AS minimal ON minimal.style = 'minimal-white' |
| 213 | JOIN styles AS chosen ON chosen.id = COALESCE(by_id.id, by_style.id, minimal.id) |
| 214 | WHERE NOT EXISTS ( |
| 215 | SELECT 1 |
| 216 | FROM session_style_snapshots |
| 217 | WHERE session_style_snapshots.session_id = sessions.id |
| 218 | ) |
| 219 | `) |
| 220 | await client.execute(` |
| 221 | UPDATE sessions |
| 222 | SET style_id = ( |
| 223 | SELECT session_style_snapshots.style_id |
| 224 | FROM session_style_snapshots |
| 225 | WHERE session_style_snapshots.session_id = sessions.id |
| 226 | ) |
| 227 | WHERE EXISTS ( |
| 228 | SELECT 1 |
| 229 | FROM session_style_snapshots |
| 230 | WHERE session_style_snapshots.session_id = sessions.id |
| 231 | ) |
| 232 | AND COALESCE(sessions.style_id, '') != ( |
| 233 | SELECT session_style_snapshots.style_id |
| 234 | FROM session_style_snapshots |
| 235 | WHERE session_style_snapshots.session_id = sessions.id |
| 236 | ) |
| 237 | `) |
| 238 | } |
| 239 | |
| 240 | const ensureSessionSnapshotColumn = async ( |
| 241 | client: LibSqlClient, |
| 242 | columnName: string, |
| 243 | definition: string |
| 244 | ): Promise<void> => { |
| 245 | const cols = await client.execute("PRAGMA table_info('session_style_snapshots')") |
| 246 | const columnNames = new Set(cols.rows.map((row) => row.name as string)) |
| 247 | if (!columnNames.has(columnName)) { |
| 248 | await client.execute(`ALTER TABLE session_style_snapshots ADD COLUMN ${columnName} ${definition}`) |
| 249 | } |
| 250 | } |
| 251 |