Skip to content

Commit 2b5ace5

Browse files
LucJosinLucJosin
authored
Feat: JSON to SQL converter (#2)
1 parent 727e873 commit 2b5ace5

File tree

4 files changed

+251
-1
lines changed

4 files changed

+251
-1
lines changed

‎package.json‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,8 @@
66
"scripts": {
77
"start": "node -r dotenv/config scripts/start.js",
88
"dev": "node -r dotenv/config scripts/start.js dotenv_config_path=.env.dev --dev",
9+
"convert": "node -r dotenv/config scripts/converter/json-to-sql.js",
10+
"convert-dev": "node -r dotenv/config scripts/converter/json-to-sql.js dotenv_config_path=.env.dev",
911
"clear": "rm -rf ./dev/"
1012
},
1113
"dependencies": {

‎scripts/constants.js‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ export const TOKEN = process.env.TOKEN;
2121
/**
2222
* List of tables that require translations.
2323
*
24-
* All translations will be saved on 'data/translations/<target>_translations.json'
24+
* All translations will be saved on 'data/translations/\<target\>_translations.json'
2525
*/
2626
export const TRANSLATION_TABLES = ["episodes", "games", "locations", "seasons"];
2727

‎scripts/converter/json-to-sql.js‎

Lines changed: 214 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,214 @@
1+
import fs from "fs/promises";
2+
3+
import { DB_FOLDER, TRANSLATION_TABLES, VERSION } from "../constants.js";
4+
import { getSingular } from "../utils.js";
5+
import { log } from "console";
6+
7+
const SPECIAL_NAMES = ["character", "language"];
8+
const BASE_PATH = `./${DB_FOLDER}/${VERSION}/data`;
9+
const OUTPUT_PATH = `${BASE_PATH}/sql`;
10+
11+
/**
12+
* Main script to convert json files into sqls
13+
*/
14+
async function main() {
15+
const files = await fs.readdir(BASE_PATH, {
16+
recursive: false,
17+
});
18+
19+
// Create the folder path
20+
await fs.mkdir(OUTPUT_PATH, { recursive: true });
21+
// Clear current file (if any)
22+
await fs.writeFile(`${OUTPUT_PATH}/hawapi.sql`, "");
23+
24+
let queries = [];
25+
for (let target of files) {
26+
if (!(await fs.stat(`${BASE_PATH}/${target}`)).isDirectory()) {
27+
// Remove file extension
28+
target = target.substring(0, target.lastIndexOf("."));
29+
30+
let data = await getJsonFile(target, false);
31+
32+
for (const item of data) {
33+
let { fields, values, subQueries } = query(target, item);
34+
35+
if (TRANSLATION_TABLES.includes(target)) {
36+
let res = await translationQuery(target, item.uuid);
37+
subQueries.push(...res);
38+
}
39+
40+
let sqlQuery =
41+
`INSERT INTO ${target} (` + fields + ") VALUES (" + values + ");\n";
42+
43+
queries.push(sqlQuery, ...subQueries);
44+
}
45+
}
46+
}
47+
48+
await fs.writeFile(`${OUTPUT_PATH}/hawapi.sql`, queries);
49+
50+
log(
51+
`Converted '${BASE_PATH}' files into SQL (Output: '${OUTPUT_PATH}/hawapi.sql')`
52+
);
53+
}
54+
55+
/**
56+
* Method that query all target translation
57+
* @param {string} target The target (actors, episodes...) of the request
58+
* @param {string} uuid The item identification
59+
* @returns An SQL INSERT string with target translation
60+
* @since 1.0.0
61+
*/
62+
const translationQuery = async (target, uuid) => {
63+
let data = await getJsonFile(target, true);
64+
65+
let res = [];
66+
for (const item of data) {
67+
if (item[`${getSingular(target)}_uuid`] === uuid) {
68+
let { fields, values } = query(target, item);
69+
70+
let sqlQuery =
71+
`INSERT INTO ${target}_translations (` +
72+
fields +
73+
") VALUES (" +
74+
values +
75+
");\n";
76+
77+
res.push(sqlQuery);
78+
}
79+
}
80+
81+
return res;
82+
};
83+
84+
/**
85+
* Method that 'query' a field from main query
86+
* @param {string} target The target (actors, episodes...) of the request
87+
* @param {string} field The field name representing the 'sub query'
88+
* @param {string[]} array All values from field of main query
89+
* @returns An SQL INSERT string
90+
* @since 1.0.0
91+
*/
92+
const subQuery = (target, field, array) => {
93+
let res = "";
94+
95+
for (const item of array) {
96+
// Ignore any possible array or non dictionary/map
97+
if (typeof item !== "object" || Array.isArray(item)) return "";
98+
99+
let { fields, values } = query(target, item);
100+
101+
res +=
102+
`INSERT INTO ${target}_${field} (` +
103+
fields +
104+
") VALUES (" +
105+
values +
106+
");\n";
107+
}
108+
109+
return res;
110+
};
111+
112+
/**
113+
* Method that query and create a SQL INSERT string
114+
* @param {string} target The target (actors, episodes...) of the request
115+
* @param {string[]} array All values from file/field
116+
* @returns All fields, values and subQueries from file/field
117+
* @since 1.0.0
118+
*/
119+
const query = (target, array) => {
120+
let subQueries = [];
121+
let fields = "";
122+
let values = "";
123+
124+
for (const field in array) {
125+
let value = array[field];
126+
127+
switch (typeof value) {
128+
case "string":
129+
values += `'${escapeSingleQuote(value)}', `;
130+
break;
131+
case "number":
132+
case "boolean":
133+
values += `${value}, `;
134+
break;
135+
case "object":
136+
if (Array.isArray(value)) {
137+
const res = subQuery(target, field, value);
138+
if (res !== "") {
139+
subQueries.push(res);
140+
continue;
141+
}
142+
values += `'${objectToPostgreSQLArray(value)}', `;
143+
}
144+
break;
145+
}
146+
147+
if (SPECIAL_NAMES.includes(field)) {
148+
fields += `"${field}", `;
149+
continue;
150+
}
151+
152+
fields += `${field}, `;
153+
}
154+
155+
fields = fields.slice(0, -2);
156+
values = values.slice(0, -2);
157+
158+
return {
159+
fields,
160+
values,
161+
subQueries,
162+
};
163+
};
164+
165+
/**
166+
* Method that converts all items inside the array into a PostgreSQL array
167+
* @param {string[]} array All values from field
168+
* @returns An string representing the PostgreSQL array
169+
* @since 1.0.0
170+
*/
171+
const objectToPostgreSQLArray = (array) => {
172+
let res = "{";
173+
174+
for (let i = 0; i < array.length; i++) {
175+
const value = array[i];
176+
177+
if (typeof value === "string") {
178+
res += `"${value}", `;
179+
continue;
180+
}
181+
182+
res += `${value}, `;
183+
}
184+
185+
return res.slice(0, -2) + "}";
186+
};
187+
188+
/**
189+
* Method that read and parse the json file using default path
190+
* @param {string} target The target (actors, episodes...) of the request
191+
* @param {boolean} isTranslation Define target is from a normal or translation file
192+
* @returns An JSON with all file items
193+
* @since 1.0.0
194+
*/
195+
const getJsonFile = async (target, isTranslation) => {
196+
const res = await fs.readFile(
197+
isTranslation
198+
? `${BASE_PATH}/translations/${target}_translations.json`
199+
: `${BASE_PATH}/${target}.json`
200+
);
201+
202+
return JSON.parse(res);
203+
};
204+
205+
/**
206+
* Method that escape/fix single quotes from string (according to SQL)
207+
* @param {string} value The string to be fixed
208+
* @returns An quote fixed string
209+
*/
210+
const escapeSingleQuote = (value) => {
211+
return value.replace(/'/g, "''");
212+
};
213+
214+
main();

‎test/sql_test.json‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
[
2+
{
3+
"string": "test",
4+
"number": 0,
5+
"bool": true,
6+
"string_list": ["test", "test", "test"],
7+
"number_list": [0, 1, 2],
8+
"bool_list": [false, true],
9+
"sub": [
10+
{
11+
"string": "test",
12+
"number": 0,
13+
"bool": true
14+
}
15+
],
16+
"sub_multi": [
17+
{
18+
"string": "test",
19+
"number": 0,
20+
"bool": true
21+
},
22+
{
23+
"string": "test",
24+
"number": 0,
25+
"bool": true
26+
}
27+
],
28+
"dict": {
29+
"string": "test",
30+
"number": 0,
31+
"bool": true
32+
}
33+
}
34+
]

0 commit comments

Comments
 (0)