ChatSql.sys.mjs (8824B)
1 /* 2 This Source Code Form is subject to the terms of the Mozilla Public 3 * License, v. 2.0. If a copy of the MPL was not distributed with this 4 * file, You can obtain one at https://mozilla.org/MPL/2.0/. */ 5 6 // Every time the schema or the underlying data changes, you must bump up the 7 // schema version. 8 9 // Remember to: 10 // 1. Bump up the version number 11 // 2. Add a migration function to migrate the data to the new schema. 12 // 3. Update #createDatabaseEntities and #checkDatabaseHealth 13 // 4. Add a test to check that the migration works correctly. 14 15 // Note: migrations should be reasonably re-entry-friendly. If the user 16 // downgrades, the schema version is decreased, and upon a subsequent upgrade, 17 // the migration step is reapplied. 18 // This ensures that any necessary conversions are performed, even for entries 19 // added after the downgrade. 20 // In practice, schema changes should be additive, allowing newer versions to 21 // operate on older schemas, albeit with potentially reduced functionality. 22 23 export const ESCAPE_CHAR = "/"; 24 25 export const CONVERSATION_TABLE = ` 26 CREATE TABLE conversation ( 27 conv_id TEXT PRIMARY KEY, 28 title TEXT, 29 description TEXT, 30 page_url TEXT, 31 page_meta_jsonb BLOB, 32 created_date INTEGER NOT NULL, 33 updated_date INTEGER NOT NULL, 34 status INTEGER NOT NULL DEFAULT 0, 35 active_branch_tip_message_id TEXT -- no foreign here, as we insert messages later. 36 ) WITHOUT ROWID; 37 `; 38 39 export const CONVERSATION_UPDATED_DATE_INDEX = ` 40 CREATE INDEX conversation_updated_date_idx ON conversation(updated_date); 41 `; 42 43 export const MESSAGE_TABLE = ` 44 CREATE TABLE message ( 45 message_id TEXT PRIMARY KEY, 46 conv_id TEXT NOT NULL REFERENCES conversation(conv_id) ON DELETE CASCADE, 47 created_date INTEGER NOT NULL, 48 parent_message_id TEXT REFERENCES message(message_id) ON DELETE CASCADE, 49 revision_root_message_id TEXT REFERENCES message(message_id) ON DELETE CASCADE, 50 ordinal INTEGER NOT NULL CHECK(ordinal >= 0), 51 is_active_branch INTEGER NOT NULL, 52 role INTEGER NOT NULL, 53 model_id TEXT, 54 params_jsonb BLOB, 55 content_jsonb BLOB, 56 usage_jsonb BLOB, 57 page_url TEXT, 58 turn_index INTEGER, 59 insights_enabled BOOLEAN, 60 insights_flag_source INTEGER, 61 insights_applied_jsonb BLOB, 62 web_search_queries_jsonb BLOB 63 ) WITHOUT ROWID; 64 `; 65 66 export const MESSAGE_ORDINAL_INDEX = ` 67 CREATE INDEX message_ordinal_idx ON message(ordinal); 68 `; 69 70 // @todo Bug 2005423 71 // Maybe add hashed url column to optimize message_url_idx 72 export const MESSAGE_URL_INDEX = ` 73 CREATE INDEX message_url_idx ON message(page_url); 74 `; 75 76 export const MESSAGE_CREATED_DATE_INDEX = ` 77 CREATE INDEX message_created_date_idx ON message(created_date); 78 `; 79 80 export const MESSAGE_CONV_ID_INDEX = ` 81 CREATE INDEX IF NOT EXISTS message_conv_id_idx ON message(conv_id); 82 `; 83 84 export const CONVERSATION_INSERT = ` 85 INSERT INTO conversation ( 86 conv_id, title, description, page_url, page_meta_jsonb, 87 created_date, updated_date, status, active_branch_tip_message_id 88 ) VALUES ( 89 :conv_id, :title, :description, :page_url, jsonb(:page_meta), 90 :created_date, :updated_date, :status, :active_branch_tip_message_id 91 ) 92 ON CONFLICT(conv_id) DO UPDATE 93 SET title = :title, 94 updated_date = :updated_date, 95 status = :status, 96 active_branch_tip_message_id = :active_branch_tip_message_id; 97 `; 98 99 export const MESSAGE_INSERT = ` 100 INSERT INTO message ( 101 message_id, conv_id, created_date, parent_message_id, 102 revision_root_message_id, ordinal, is_active_branch, role, 103 model_id, params_jsonb, content_jsonb, usage_jsonb, page_url, turn_index, 104 insights_enabled, insights_flag_source, insights_applied_jsonb, 105 web_search_queries_jsonb 106 ) VALUES ( 107 :message_id, :conv_id, :created_date, :parent_message_id, 108 :revision_root_message_id, :ordinal, :is_active_branch, :role, 109 :model_id, jsonb(:params), jsonb(:content), jsonb(:usage), :page_url, :turn_index, 110 :insights_enabled, :insights_flag_source, jsonb(:insights_applied_jsonb), 111 jsonb(:web_search_queries_jsonb) 112 ) 113 ON CONFLICT(message_id) DO UPDATE SET 114 is_active_branch = :is_active_branch; 115 `; 116 117 export const CONVERSATIONS_MOST_RECENT = ` 118 SELECT conv_id, title 119 FROM conversation 120 ORDER BY updated_date DESC 121 LIMIT :limit; 122 `; 123 124 export const CONVERSATIONS_OLDEST = ` 125 SELECT conv_id, title 126 FROM conversation 127 ORDER BY updated_date ASC 128 LIMIT :limit; 129 `; 130 131 export const CONVERSATION_BY_ID = ` 132 SELECT conv_id, title, description, page_url, 133 json(page_meta_jsonb) AS page_meta, created_date, updated_date, 134 status, active_branch_tip_message_id 135 FROM conversation WHERE conv_id = :conv_id; 136 `; 137 138 export const CONVERSATIONS_BY_DATE = ` 139 SELECT conv_id, title, description, page_url, 140 json(page_meta_jsonb) AS page_meta, created_date, updated_date, 141 status, active_branch_tip_message_id 142 FROM conversation 143 WHERE updated_date >= :start_date AND updated_date <= :end_date 144 ORDER BY updated_date DESC; 145 `; 146 147 export const CONVERSATIONS_BY_URL = ` 148 SELECT c.conv_id, c.title, c.description, c.page_url, 149 json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date, 150 c.status, c.active_branch_tip_message_id 151 FROM conversation c 152 WHERE EXISTS ( 153 SELECT 1 154 FROM message m 155 WHERE m.conv_id = c.conv_id 156 AND m.page_url = :page_url 157 ) 158 ORDER BY c.updated_date DESC; 159 `; 160 161 /** 162 * Get all messages for multiple conversations 163 * 164 * @param {number} amount - The number of conversation IDs to get messages for 165 */ 166 export function getConversationMessagesSql(amount) { 167 return ` 168 SELECT 169 message_id, created_date, parent_message_id, revision_root_message_id, 170 ordinal, is_active_branch, role, model_id, conv_id, 171 json(params_jsonb) AS params, json(usage_jsonb) AS usage, 172 page_url, turn_index, insights_enabled, insights_flag_source, 173 json(insights_applied_jsonb) AS insights_applied, 174 json(web_search_queries_jsonb) AS web_search_queries, 175 json(content_jsonb) AS content 176 FROM message 177 WHERE conv_id IN(${new Array(amount).fill("?").join(",")}) 178 ORDER BY ordinal ASC; 179 `; 180 } 181 182 export const CONVERSATIONS_CONTENT_SEARCH = ` 183 SELECT c.conv_id, c.title, c.description, c.page_url, 184 json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date, 185 c.status, c.active_branch_tip_message_id 186 FROM conversation c 187 JOIN message m ON m.conv_id = c.conv_id 188 WHERE json_type(m.content_jsonb, :path) IS NOT NULL; 189 `; 190 191 export const CONVERSATIONS_CONTENT_SEARCH_BY_ROLE = ` 192 SELECT c.conv_id, c.title, c.description, c.page_url, 193 json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date, 194 c.status, c.active_branch_tip_message_id 195 FROM conversation c 196 JOIN message m ON m.conv_id = c.conv_id 197 WHERE m.role = :role 198 AND json_type(m.content_jsonb, :path) IS NOT NULL; 199 `; 200 201 export const CONVERSATIONS_HISTORY_SEARCH = ` 202 SELECT c.conv_id, c.title, c.description, c.page_url, 203 json(c.page_meta_jsonb) AS page_meta, c.created_date, c.updated_date, 204 c.status, c.active_branch_tip_message_id 205 FROM conversation c 206 JOIN message m ON m.conv_id = c.conv_id 207 WHERE m.role = 0 208 AND ( 209 CAST(json_extract(m.content_jsonb, :path) AS TEXT) LIKE :pattern ESCAPE '/' 210 OR 211 c.title LIKE :pattern ESCAPE '/' 212 ); 213 `; 214 215 export const MESSAGES_BY_DATE = ` 216 SELECT 217 message_id, created_date, parent_message_id, revision_root_message_id, 218 ordinal, is_active_branch, role, model_id, conv_id, 219 json(params_jsonb) AS params, json(usage_jsonb) AS usage, 220 page_url, turn_index, insights_enabled, insights_flag_source, 221 json(insights_applied_jsonb) AS insights_applied, 222 json(web_search_queries_jsonb) AS web_search_queries, 223 json(content_jsonb) AS content 224 FROM message 225 WHERE created_date >= :start_date AND created_date <= :end_date 226 ORDER BY created_date DESC 227 LIMIT :limit OFFSET :offset; 228 `; 229 230 export const MESSAGES_BY_DATE_AND_ROLE = ` 231 SELECT 232 message_id, created_date, parent_message_id, revision_root_message_id, 233 ordinal, is_active_branch, role, model_id, conv_id, 234 json(params_jsonb) AS params, json(usage_jsonb) AS usage, 235 page_url, turn_index, insights_enabled, insights_flag_source, 236 json(insights_applied_jsonb) AS insights_applied, 237 json(web_search_queries_jsonb) AS web_search_queries, 238 json(content_jsonb) AS content 239 FROM message 240 WHERE role = :role 241 AND created_date >= :start_date AND created_date <= :end_date 242 ORDER BY created_date DESC 243 LIMIT :limit OFFSET :offset; 244 `; 245 246 export const DELETE_CONVERSATION_BY_ID = ` 247 DELETE FROM conversation WHERE conv_id = :conv_id; 248 `; 249 250 export const CONVERSATION_HISTORY = ` 251 SELECT c.conv_id, c.title, c.created_date, c.updated_date, ( 252 SELECT group_concat(t.page_url) 253 FROM ( 254 SELECT 255 m.page_url 256 FROM message m 257 WHERE m.conv_id = c.conv_id 258 AND m.page_url IS NOT NULL 259 GROUP BY m.page_url 260 ORDER BY MAX(m.created_date) ASC 261 ) AS t 262 ) AS urls 263 FROM conversation c 264 WHERE EXISTS ( 265 SELECT 1 266 FROM message AS m 267 WHERE m.conv_id = c.conv_id 268 ) 269 ORDER BY c.updated_date {sort} 270 LIMIT :limit OFFSET :offset; 271 `;