1/** 2 _____ ____ _ _ _ _ 3 / ____|/ __ \| | (_) | (_) 4 | (___ | | | | | _| |_ ___ _ ___ __ ___ _____ ___ ___ _ __ ___ ___ 5 \___ \| | | | | | | __/ _ \ | / __| / _` \ \ /\ / / _ \/ __|/ _ \| '_ ` _ \ / _ \ 6 ____) | |__| | |____| | || __/ | \__ \ | (_| |\ V V / __/\__ \ (_) | | | | | | __/ 7 |_____/ \___\_\______|_|\__\___| |_|___/ \__,_| \_/\_/ \___||___/\___/|_| |_| |_|\___| 8*/ 9 10let PAGE_SIZE = 100; 11 12 13function getEntriesSelectColumns() { 14 columns = ""; 15 16 columns += "l.id,"; // 0 17 columns += "l.link,"; // 1 18 columns += "l.title,"; // 2 19 columns += "l.description,"; // 3 20 columns += "l.date_published,"; // 4 21 columns += "l.thumbnail,"; // 5 22 columns += "l.author,"; // 6 23 columns += "l.album,"; // 7 24 columns += "l.language,"; // 8 25 columns += "l.permanent,"; // 9 26 columns += "l.bookmarked,"; // 10 27 columns += "l.age,"; // 11 28 columns += "l.status_code,"; // 12 29 columns += "l.manual_status_code,"; // 13 30 columns += "l.page_rating,"; // 14 31 columns += "l.page_rating_votes,"; // 15 32 columns += "l.page_rating_contents,"; // 16 33 34 columns += "t.tag,"; // 17 35 36 columns += "socialdata.thumbs_up AS thumbs_up,"; // 18 37 columns += "socialdata.thumbs_down AS thumbs_down,"; // 19 38 columns += "socialdata.view_count AS view_count,"; // 20 39 columns += "socialdata.followers_count AS followers_count,"; // 21 40 columns += "socialdata.stars AS stars,"; // 22 41 columns += "socialdata.upvote_ratio AS upvote_ratio,"; // 23 42 columns += "socialdata.upvote_diff AS upvote_diff,"; // 24 43 columns += "socialdata.upvote_view_ratio AS upvote_view_ratio"; // 25 44 45 return columns; 46} 47 48 49function unpackEntries(res) { 50 let results = []; 51 52 if (res.length > 0) { 53 const rows = res[0].values; 54 55 rows.forEach(row => { 56 let tags = []; 57 if (row[17] != null) { 58 tags = row[17].split(',').filter(tag => tag.trim() !== ''); 59 } 60 61 const data = { 62 id: row[0], 63 link: row[1], 64 title: row[2], 65 description: row[3], 66 date_published: row[4], 67 thumbnail: row[5], 68 author: row[6], 69 album: row[7], 70 language: row[8], 71 permanent: row[9], 72 bookmarked: row[10], 73 age: row[11], 74 status_code: row[12], 75 manual_status_code: row[13], 76 page_rating: row[14], 77 page_rating_votes: row[15], 78 page_rating_contents: row[16], 79 tags: tags, 80 thumbs_up: row[18], 81 thumbs_down: row[19], 82 view_count: row[20], 83 followers_count: row[21], 84 stars: row[22], 85 upvote_ratio: row[23], 86 upvote_diff: row[24], 87 upvote_view_ratio: row[25], 88 }; 89 90 results.push(data); 91 }); 92 } 93 94 return results; 95} 96 97 98`we need to join tags, because when we search for something we want to filter by tags` 99function getEntriesSelectFromStmt() { 100 return ` FROM linkdatamodel AS l 101 LEFT JOIN entrycompactedtags AS t ON l.id = t.entry_id 102 LEFT JOIN socialdata AS socialdata ON l.id = socialdata.entry_id`; 103} 104 105 106function selectEntryTags(entry_id) { 107 let text = `SELECT tag FROM usertags WHERE entry_id = ${entry_id}`; 108 109 let result = new Set(); 110 111 console.log(text); 112 113 const res = db.exec(text); 114 115 if (res.length > 0) { 116 const rows = res[0].values; 117 118 rows.forEach(row => { 119 result.add(row[0]); 120 }); 121 } 122 123 return Array.from(result); 124} 125 126 127function selectEntrySocialStmt(entry_id) { 128 let text = `SELECT 129 id, 130 entry_id, 131 thumbs_up, 132 thumbs_down, 133 view_count, 134 followers_count, 135 stars, 136 upvote_ratio, 137 upvote_diff, 138 upvote_view_ratio 139 FROM socialdata WHERE entry_id = ${entry_id}`; 140 141 return text; 142} 143 144 145function selectEntrySocial(entry_id) { 146 let text = selectEntrySocialStmt(entry_id); 147 148 let result = new Set(); 149 150 console.log(text); 151 152 const res = db.exec(text); 153 154 let social_data = unpackSocialData(res); 155 return social_data; 156} 157 158 159function unpackSocialData(res) { 160 let results = []; 161 162 if (res.length > 0) { 163 const rows = res[0].values; 164 165 rows.forEach(row => { 166 const data = { 167 id: row[0], 168 entry_id: row[1], 169 thumbs_up: row[2], 170 thumbs_down: row[3], 171 view_count: row[4], 172 followers_count: row[5], 173 stars: row[6], 174 upvote_ratio: row[7], 175 upvote_diff: row[8], 176 upvote_view_ratio: row[9], 177 }; 178 179 results.push(data); 180 }); 181 } 182 183 return results; 184} 185 186 187function getOrderStmt() { 188 let sort_method = sort_function; 189 let order_method = "ASC"; 190 191 if (sort_function.startsWith("-")) { 192 sort_method = sort_function.slice(1); 193 order_method = "DESC"; 194 } 195 196 return `ORDER BY ${sort_method} ${order_method}`; 197} 198 199 200function getSelectEntry(entry_id) { 201 let text = "SELECT " + getEntriesSelectColumns(); 202 text += getEntriesSelectFromStmt(); 203 text += ` WHERE l.id = ${entry_id}`; 204 205 return text; 206 207} 208 209 210function getEntriesSelectDefault(userInput, page_size) { 211 let text = "SELECT " + getEntriesSelectColumns(); 212 text += getEntriesSelectFromStmt(); 213 214 let page = getQueryParam("page") || 1; 215 const offset = (page - 1) * page_size; 216 217 let order_by = getOrderStmt(); 218 219 let page_size_query = page_size; 220 221 text = text + ` ${order_by} LIMIT ${page_size_query} OFFSET ${offset}`; 222 return text; 223} 224 225 226function getEntriesSelectDefaultUserInput(userInput, page_size) { 227 let text = "SELECT " + getEntriesSelectColumns(); 228 text += getEntriesSelectFromStmt(); 229 230 text += ` WHERE UPPER(l.title) LIKE UPPER('%${userInput}%') 231 OR UPPER(l.link) LIKE UPPER('%${userInput}%') 232 OR UPPER(l.description) LIKE UPPER('%${userInput}%') 233 OR UPPER(t.tag) LIKE UPPER('%${userInput}%')`; 234 235 let page = getQueryParam("page") || 1; 236 const offset = (page - 1) * page_size; 237 238 let order_by = getOrderStmt(); 239 240 let page_size_query = page_size; 241 242 text = text + ` ${order_by} LIMIT ${page_size_query} OFFSET ${offset}`; 243 return text; 244} 245 246 247function getEntriesSelectCustomSQL(userInput, page_size) { 248 249 let text = "SELECT " + getEntriesSelectColumns(); 250 text += getEntriesSelectFromStmt(); 251 252 text += ` WHERE ${userInput}`; 253 254 let page = getQueryParam("page") || 1; 255 const offset = (page - 1) * page_size; 256 257 let order_by = getOrderStmt(); 258 let page_size_query = page_size; 259 260 text = text + ` ${order_by} LIMIT ${page_size_query} OFFSET ${offset}`; 261 return text; 262} 263 264 265function execQuery(text) { 266 console.log(text); 267 try { 268 const res = db.exec(text); 269 object_list_data.entries = unpackEntries(res); 270 } catch (error) { 271 console.error("Error executing query:", error); 272 } 273} 274 275 276async function getQueryTotalRows(text) { 277 const fromIndex = text.toUpperCase().indexOf('FROM'); 278 279 if (fromIndex === -1) { 280 return 0; 281 } 282 283 const offsetIndex = text.toUpperCase().indexOf('OFFSET'); 284 285 if (offsetIndex !== -1) { 286 text = text.slice(0, offsetIndex); 287 } 288 289 text = "SELECT COUNT(*) " + text.slice(fromIndex); 290 291 console.log(text); 292 293 try { 294 const res = db.exec(text); 295 296 if (res.length > 0) { 297 const rows = res[0].values; 298 299 if (rows.length > 0) { 300 let size = rows[0][0]; 301 return size; 302 } 303 } 304 } catch (error) { 305 console.error("Error executing query:", error); 306 } 307 308 return 0; 309} 310 311 312function getQueryText(page_size=PAGE_SIZE) { 313 let userInput = $("#searchInput").val(); 314 315 let text = getEntriesSelectDefault(userInput, page_size); 316 317 let entry_id = getQueryParam("entry_id"); 318 if (entry_id) 319 {
320 text = getSelectEntry(entry_id); 321 } 322 323 if (userInput && userInput != "") { 324 if (userInput.indexOf("LIKE") !== -1) { 325 text = getEntriesSelectCustomSQL(userInput, page_size); 326 } 327 else { 328 text = getEntriesSelectDefaultUserInput(userInput, page_size); 329 } 330 } 331 332 return text; 333} 334 335 336async function createDatabaseData(dataArray) { 337 if (db) { 338 return false; 339 } 340 341 try { 342 const config = { 343 locateFile: filename => `https://cdn.jsdelivr.net/npm/[email protected]/dist/${filename}` 344 }; 345 346 // Initialize SQL.js with the correct .wasm path 347 const SQL = await initSqlJs(config); 348 349 // Load the database 350 db = new SQL.Database(dataArray); 351 352 return true; 353 354 } catch (error) { 355 console.error('Error loading SQLite database or executing query:', error); 356 progressBarElement.textContent = 'Error loading SQLite database or executing query:'+ error; 357 return false; 358 } 359}
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.