· 4 years ago · Feb 05, 2021, 04:48 AM
1const config = require("./../config.json"); //contains an object {} that has .debugging[boolean] and .group[string] | points to folder with db
2const sqlite3 = require('sqlite3').verbose();
3const fs = require('fs')
4
5class SQL {
6 constructor() {
7 console.log(`SQL.hub.constructor`);
8 this._table = "NONE";
9 }
10 connectDB(){
11 console.log(`Attempting to connect to DB`);
12 return new Promise((cb,rj)=>{
13 this.db = new sqlite3.Database(config.group + "sql_database.db", sqlite3.OPEN_READWRITE, (err) => {
14 if (err) rj(err.message);
15 else if (config.debugging) console.log(`Connected to the \x1b[31m$ROOT\x1b[0m database.`);
16 cb();
17 });
18 })
19 }
20 get table() {
21 return this._table;
22 }
23
24 get type() {
25 return "sql_access";
26 }
27
28 /**
29 *
30 * @param {string} property
31 * @param {string} match
32 * @param {boolean} [single=true] single
33 * @return {Promise<unknown>}
34 */
35 getByProperty(property, match, single = true) {
36 return new Promise((cb, rj) => {
37 let sql = `SELECT match ${property} FROM ${this.table} WHERE match = ?`
38 if (single) {
39 this.db.get(sql, [match], (err, row) => {
40 if (err) rj(err.message);
41 else cb(row);
42 })
43 } else {
44 this.db.all(sql, [match], (err, rows) => {
45 if (err) rj(err.message);
46 else cb(rows);
47 })
48 }
49 });
50 }
51
52 dir() {
53 let rowCount = 0;
54 this.db.each(`SELECT * FROM ${this.table}`, [], (err, row) => {
55 if (err) {
56 return console.error(err.message);
57 }
58 for (let i in row) {
59 console.log(`${this.table}[${"0000".substring(0, 4 - rowCount.toString().length)}${rowCount}]->${i} = ${row[i]}`);
60 }
61 rowCount++;
62 console.log("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-");
63 });
64 }
65
66 /**
67 * Saves an item.
68 * @param {object} data
69 */
70 _saveItem(data) {
71 let indexes = [];
72 let values = [];
73 for (let i in data) {
74 if(i.startsWith("_actual"))continue;
75 if(data[i] == null||(data[i] instanceof Array && data[i].length===0)) continue;
76 let index = i;
77 if(i.startsWith("_")) index = index.substring(1,index.length)
78 let toSave = data[i];
79 if(toSave instanceof Array) toSave = toSave.join(",");
80 indexes.push(index.toString().toLowerCase());
81 if(typeof toSave === "string") toSave = `'${toSave}'`;
82 values.push(toSave);
83 }
84 let sql = `INSERT INTO ${this.table}(${indexes.join(",")}) VALUES(${values.join(",")})`;
85 this.db.run(sql, [], (err) => {
86 if (err) {
87 return console.error(err.message);
88 }
89 })
90 }
91
92 /**
93 * Updates an item.
94 * @param {object} store Details about where to store the data.
95 * @param {string} [store.where="name"] store.where Where in the database to store the data.
96 * @param {string} store.equals What the where should equal.
97 * @param {object} data
98 */
99 _updateItem(store, data) {
100 if (data && typeof data === "object" && !data.where) data.where = "name";
101 if (typeof store !== "object" || (store && (!store.where || !store.equals))) return;
102 for (let i in data) {
103 if(i.startsWith("_"))continue;
104 this.db.run(`UPDATE ${this.table} SET ${i} = ${data[i]} WHERE ${store.where.toString().toLowerCase()} = ${store.equals}`, [], (err) => {
105 if (err) {
106 return console.error(err.message);
107 }
108 })
109 }
110 }
111
112 _createTable(data, cb) {
113 console.log(`SQL.hub._createTable`)
114 let columns = "";
115 console.log(`data.length = ${data.length}`)
116 for (let i =0;i<data.length;i++) {
117 if(columns.length) columns+=",\n";
118 let type = data[i].type;
119 if(!type) data[i].type = "text";
120 if(type === "string") type = "text";
121 if(type === "number") type = "integer";
122 columns+= `${data[i].name.toLowerCase()} ${type.toUpperCase()}`;
123 if(data[i].data) columns+= ` ${data[i].data.toUpperCase()}`;
124 if(data[i].default) columns += `DEFAULT ${data[i].default}`;
125 }
126 let sql = `CREATE TABLE IF NOT EXISTS ${this.table} (\n${columns},\nPRIMARY KEY (${data[0].name.toLowerCase()})\n) WITHOUT ROWID`
127 console.log('Running SQL:\n',sql);
128 this.db.run(sql, function (val) {
129 console.log('db,cb',val);
130 cb();
131 });
132 try {
133 } catch (e) { cb(console.log("e:", e, e.stack)); }
134 }
135}
136
137const MeasurementData = [
138 {
139 name: "name",
140 type: "string",
141 data: "unique"
142 },
143 {
144 name:"aliases",
145 type: "string",
146 },
147 {
148 name: "amount",
149 type: "number",
150 data: "not null",
151 default: 0,
152 },
153 {
154 name:"conversions",
155 type:"string",
156 }
157
158]
159
160class measurements extends SQL {
161 constructor() {
162 console.log(`SQL.measurements.constructor`)
163 super();
164 this.connectDB().then( () => {
165 console.log(`Connected DB promise.`)
166 this._table = "measurements";
167 this._createTable(MeasurementData, (err) => {
168 console.log("here in measure")
169 if (err) console.error(err.message);
170 else if (config.debugging) console.log(`Connected to the \x1b[32m${this.table}\x1b[0m table.`);
171 });
172 })
173 }
174
175 /**
176 * Saves an item.
177 * @param {Measurement} measurement
178 */
179 saveItem(measurement) {
180 this.itemExists(measurement.name).then((exists)=>{
181 if (exists) {
182 this._updateItem({db: this._name, equals: measurement.name.toLowerCase()}, measurement);
183 } else {
184 this._saveItem(measurement);
185 }
186 }).catch(console.error)
187 }
188
189 getItem(name) {
190 return new Promise((cb,rj)=> {
191 this.itemExists(name).then(function (exists) {
192 if (exists) {
193 this.db.get(`SELECT * FROM ${this.table} WHERE Name = ${name}`, [], (err, row) => {
194 if (err) rj(err.message);
195 cb(row);
196 });
197 }
198 else cb(false);
199 }).catch(console.error);
200 });
201 }
202
203 itemExists(name) {
204 return new Promise((cb,rj)=> {
205 this.db.get(`SELECT Name ${name} FROM ${this.table} WHERE Name = ${name}`, [], (err, row) => {
206 if (err) rj(err.message);
207 cb(row && row[name]&& row[name] === name);
208 });
209 })
210 }
211
212}
213
214module.exports = {
215 measurements,
216};
217