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