-
Notifications
You must be signed in to change notification settings - Fork 1
/
retrievePrograms.jsx
183 lines (174 loc) · 5.67 KB
/
retrievePrograms.jsx
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
import { Database } from "bun:sqlite"; // Import bun:sqlite
// Retrieve and return the program by id
function getProgram(id) {
// Load the database as readonly
var db = new Database('database.sqlite', { readonly: true });
// Create Queries for getting a program by id and getting multiple by index
const retrieveById = db.query("SELECT * FROM programs WHERE id = $id");
const output = formatOutput(retrieveById.get({ $id: id }));
db.close();
return output;
}
// Get programs without stringifying the output
function getProgramsNoString($limit = 50, $offset = 0) {
// Load the database as readonly
var db = new Database('database.sqlite', { readonly: true });
// Create Queries for getting a program by id and getting multiple by index
const retrievePrograms = db.query("SELECT * FROM programs ORDER BY archive__added DESC LIMIT $limit OFFSET $offset");
if ($limit > 1000) $limit = 1000; // Make sure limit is under 1000
$limit = Number($limit); $offset = Number($offset); // Make sure the limit and offset are numbers if they aren't return 400
if ($limit === NaN || $offset === NaN) return 400;
const archiveData = retrievePrograms.all({ $limit, $offset }); // Get data from archive
for (let i in archiveData) { archiveData[i] = formatOutput(archiveData[i]); } // Structure the JSON
return archiveData // Stringify and return the data
}
// Get programs stringified
function getPrograms() {
return JSON.stringify(getProgramsNoString(...arguments))
}
// Query programs database (readonly)
function queryPrograms(params) {
let data;
try {
// Load the database as readonly
var db = new Database('database.sqlite', { readonly: true });
// Make sure there is at least one query parameter, excluding the one to differentiate between normal and raw search
if (Array.from(params.values()).slice(0,-1).join('').length < 1) return data = [{ noQuery: true } ];
// Defualt limit to 50 and max it at 1000
let limit = params.get('limit') || 50;
if (limit > 1000) limit = 1000;
function parseDate(d) {
return typeof d === "number" ? d : Date.parse(d);
}
data = db.query(`SELECT * FROM programs
WHERE ($id = "null" OR id LIKE $id)
AND (title LIKE $title)
AND (author__nick LIKE $author OR author__name LIKE $author)
AND (votes BETWEEN $votes_min AND $votes_max)
AND (spinoffs BETWEEN $spinoffs_min AND $spinoffs_max)
AND (created BETWEEN $created_min AND $created_max)
AND (updated BETWEEN $updated_min AND $updated_max)
AND (archive__added BETWEEN $archive_added_min AND $archive_added_max)
AND (archive__updated BETWEEN $archive_updated_min AND $archive_updated_max)
LIMIT $limit`).all({
$id : params.get('id') || "null",
$title : "%"+params.get('title').split("").join("%")+"%",
$author : "%"+params.get('author').split("").join("%")+"%",
$votes_min : params.get('votes_min') || 0,
$votes_max : params.get('votes_max') || Infinity,
$spinoffs_min : params.get('spinoffs_min') || 0,
$spinoffs_max : params.get('spinoffs_max') || Infinity,
$created_min : parseDate(params.get('created_min')) || 0,
$created_max : parseDate(params.get('created_max')) || Infinity,
$updated_min : parseDate(params.get('updated_min')) || 0,
$updated_max : parseDate(params.get('updated_max')) || Infinity,
$archive_added_min : parseDate(params.get('archive_added_min')) || 0,
$archive_added_max : parseDate(params.get('archive_added_max')) || Infinity,
$archive_updated_min : parseDate(params.get('archive_updated_min')) || 0,
$archive_updated_max : parseDate(params.get('archive_updated_max')) || Infinity,
$limit : limit
});
// Close the database when done
db.close();
// Format the JSON
for (var i in data) data[i] = formatOutput(data[i]);
} catch (e) {
// Handle any errors
const errMsg = 'Error while querying database (queryPrograms): ' + e;
console.error(errMsg);
return { status: 500, message: errMsg };
}
return data;
}
// Take in the output from the sqlite database and retern a formatted JSON version
function formatOutput(sqliteOut) {
// Make sure there's an sqlite output
if (!sqliteOut) {
return {
status: 500,
severe: true,
message: "Error: Recieved no data from the database"
}
}
// Get variables from sqlite data
let {
archive__added,
archive__updated,
id,
created,
updated,
title,
code,
folds,
thumbnail,
fork,
key,
votes,
spinoffs,
type,
width,
height,
user_flagged,
origin_scratchpad,
hidden_from_hotlist,
restricted_posting,
by_child,
author__nick,
author__name,
author__id,
author__profile_access
} = sqliteOut;
// Convert the origin sratchpad data back to an object
if (origin_scratchpad) {
const origin = origin_scratchpad.split('\n');
origin_scratchpad = {
id: origin[0],
title: origin[1],
deleted: origin[2],
official: origin[3]
}
// console.log('spinoff of: ' + JSON.stringify(origin_scratchpad))
}
// Format and return JSON data
return {
status: 200,
message: 'Sucessfully formatted the database output',
archive: {
added: archive__added,
updated: archive__updated
},
id,
created,
updated,
title,
code,
folds,
thumbnail,
fork: fork === 1,
key,
votes,
spinoffs,
type,
width,
height,
userFlagged: user_flagged === 1,
originScratchpad: origin_scratchpad,
hiddenFromHotlist: hidden_from_hotlist === 1,
restrictedPosting: restricted_posting === 1,
byChild: by_child === 1,
author: {
nick: author__nick,
name: author__name,
id: author__id,
profileAccess: author__profile_access
}
};
}
export {
getProgram,
getPrograms,
getProgramsNoString,
queryPrograms,
formatOutput as formatProgramFromDatabase
};
export default getProgram;