· 5 years ago · Aug 31, 2020, 12:08 PM
1import DBValueType from "../model/dbvaluetype";
2import { SQLCompiledFilter } from "../model/sqlfilter";
3import { MySQLConnector, MySQLOptions } from "..";
4import SQLiteConnector from "./sqliteconnector";
5
6/**
7 * A type that represents every possible data
8 * type that can be read and written from and
9 * to a database.
10 */
11export type DBValue = string | number;
12
13/**
14 * Represents a row in a database. Each key is a column
15 * name and the value it the corresponding value in that
16 * column and row. See {@link DBTable} for an example.
17 *
18 */
19export type DBRow = { [column: string]: DBValue };
20
21/**
22 * Represents a column in a database. Each key is a column
23 * name and the type is the corresponding data type of that
24 * column.
25 */
26export type DBColumn = { name: string, type: DBValueType };
27
28/**
29 * Represents a path to a SQLite database.
30 */
31export type SQLiteOptions = { path: string };
32
33/**
34 * Represents a table in a database. It's a list
35 * list of {@link DBRow}s. The table gets
36 * mapped to an array of JSON objects like in this
37 * example:
38 *
39 * Table in database:
40 * ```
41 * | ID | Name |
42 * |----|------|
43 * | 0 | Tim |
44 * | 1 | Tom |
45 * ```
46 *
47 * JSON representation:
48 *
49 * ```json
50 * [
51 * {ID: 0, Name: "Tim"},
52 * {ID: 1, Name: "Tom"}
53 * ]
54 * ```
55 */
56export type DBTable = DBRow[];
57
58export type DBSelectCallback =
59 /**
60 * Callback function for SQL select operations.
61 *
62 * @param error Error value, undefined if no error occurred.
63 * @param result Selection result, can be empty or undefined when error is not undefined.
64 */
65 (error?: Error, result?: DBTable) => void;
66
67export type DBInsertCallback =
68 /**
69 * Callback function for SQL insert operations.
70 *
71 * @param error Error value, undefined if no error occurred.
72 * @param inserted Number of rows that got inserted.
73 */
74 (error?: Error, inserted?: number) => void;
75
76export type DBDeleteCallback =
77 /**
78 * Callback function for SQL delete operations.
79 *
80 * @param error Error value, undefined if no error occurred.
81 * @param deleted Number of rows that got deleted.
82 */
83 (error?: Error, deleted?: number) => void;
84
85export type DBUpdateCallback =
86 /**
87 * Callback function for SQL update operations.
88 *
89 * @param error Error value, undefined if no error occurred.
90 * @param updated Number of rows that got updated.
91 */
92 (error?: Error, updated?: number) => void;
93
94export type DBConnectCallback =
95 /**
96 * Callback function for database connections.
97 *
98 * @param error Error value, undefined if no error occurred.
99 */
100 (error?: Error) => void;
101
102export type DBCreateTableCallback =
103 /**
104 * Callback function for table creation.
105 *
106 * @param error Error value, undefined if no error occurred.
107 */
108 (error?: Error) => void;
109
110export type ToSQLiteCallback =
111 /**
112 * Callback function for MySQL export call.
113 */
114 (error: Error | undefined, connector: DBConnector | undefined) => void;
115
116export type GetTablesCallback =
117 /**
118 * Callback function for getting tables;
119 */
120 (error?: Error | undefined, tables?: Array<string> | undefined) => void;
121
122export type GetColumnsCallback =
123 /**
124 * Callback function for getting columns
125 */
126 (error?: Error | undefined, columns?: any[] | undefined) => void;
127
128/**
129 * Options passed to he select function of a database connector.
130 *
131 * - `table` _string_ - Table name from where data should be selected.
132 * - `columns` _string[]_ - Optional, specify which columns should get selected. Selectes all (*) if this is undefined or empty.
133 * - `filter` _{@link SQLCompiledFilter}_ - Optional, add a WHERE clause to the query.
134 */
135export type DBSelectOptions = { table: string, columns?: string[], filter?: SQLCompiledFilter };
136
137/**
138 * Options passed to the insert function of a database connector.
139 *
140 * - `table` _string_ - Table name to where the data should be inserted.
141 * - `data` _{@link DBTable}_ - Data to insert.
142 */
143export type DBInsertOptions = { table: string, data: DBTable };
144
145/**
146 * Options passed to the update function of a database connector.
147 *
148 * - `table` _string_ - Name of the table where rows should get updated.
149 * - `data` _{@link DBRow}_ - Updated data.
150 * - `filter` _{@link SQLCompiledFilter}_ - Optional, add a WHERE clause to the query.
151 */
152export type DBUpdateOptions = { table: string, data: DBRow, filter?: SQLCompiledFilter };
153
154/**
155 * Options passed to the delete function of a database connector.
156 *
157 * - `table` _string_ - Name of the table where data should get deleted.
158 * - `filter` _{@link SQLCompiledFilter}_ - Add a WHERE clause to the query.
159 */
160export type DBDeleteOptions = { table: string, filter: SQLCompiledFilter };
161
162/**
163 * Options passed to the create table function of a database connector.
164 *
165 * - `name` _string_ - Name of the table to create
166 * - `columns` - Array of key value pairs consisting of the column name and it's type, see {@link DBValueType}
167 * - `softFail` _boolean_ - Optional, don't error when the table already exists. Equivalent to SQL syntax `IF NOT EXISTS`.
168 */
169export type DBCreateTableOptions = { name: string, columns: DBColumn[], softFail?: boolean };
170
171/**
172 * Abstract class that represents a connection to a database
173 * of given type, for example a MySQL Database. For information on
174 * how to connect to the different types of databases, take a look
175 * at the corresponding special connectors:
176 *
177 * - MySQL: {@link MySQLConnector}
178 * - SQLite: {@link SQLiteConnector}
179 */
180export default abstract class DBConnector {
181 /**
182 * Performs a SELECT query on the database with the given parameters and
183 * returns the results using a callback.
184 *
185 * @param options Options for the select query.
186 * @param callback Callback gets executed when the query finishes.
187 */
188 abstract select(options: DBSelectOptions, callback: DBSelectCallback): void;
189
190 /**
191 * Performs an INSERT query on the database with the given parameters
192 * and returns the results using a callback.
193 *
194 * @param options Options for the insert query.
195 * @param callback Callback gets executed when the query finishes.
196 */
197 abstract insert(options: DBInsertOptions, callback: DBInsertCallback): void;
198
199 /**
200 * Performs an UPDATE query on the database with the given parameters
201 * and returns the results using a callback.
202 *
203 * @param options Options for the update query.
204 * @param callback Callback gets executed when the query finishes.
205 */
206 abstract update(options: DBUpdateOptions, callback: DBUpdateCallback): void;
207
208 /**
209 * Performs a DELETE query on the database with the given parameters
210 * and returns the results using a callback.
211 *
212 * @param options Options for the delete query.
213 * @param callback Callback gets executed when the query finishes.
214 */
215 abstract delete(options: DBDeleteOptions, callback: DBDeleteCallback): void;
216
217 /**
218 * Performs a CREATE TABLE query on the database with given parameters
219 * and returns the results using a callback.
220 *
221 * @param options Options for the create table query.
222 * @param callback Callback gets executed when the query finishes.
223 */
224 abstract createTable(options: DBCreateTableOptions, callback: DBCreateTableCallback): void;
225
226 /**
227 * Performs a DELETE TABLE query on the database with given parameters
228 * and returns the results using a callback.
229 *
230 * @param options Options for the delete table query.
231 * @param callback Callback gets executed when the query finishes.
232 */
233 //abstract deleteTable(options: DBDeleteTableOptions: callback: DBDeleteTableCallback): void;
234
235 /**
236 * Exports a MySQL database as a SQLite database
237 *
238 * @param connector Connection to the MySQL database that shall be exported.
239 * @param options Target path where SQLite the database will be created.
240 * @param callback Callback gets executed when the query finishes.
241 */
242 static toSQLite(connector: MySQLConnector, options: SQLiteOptions, callback: ToSQLiteCallback): void {
243 connector.connect((err) => {
244 if (err) {
245 callback(new Error("Could not connect to database"), undefined);
246 return;
247 }
248 SQLiteConnector.connect(options, (err, sqlite) => {
249 if (err) {
250 callback(new Error("Could not create SQLite database"), undefined);
251 return;
252 }
253 if (sqlite !== undefined) {
254 connector.getTables((err, tablenames) => {
255 if (err) {
256 callback(new Error("Could not connect to Database"), undefined);
257 return;
258 }
259
260 if (!tablenames) {
261 callback(new Error("Could not find any table names"), undefined);
262 return;
263 }
264 tablenames.forEach((tablename, tableIndex) => {
265 const tableColumns: DBColumn[] = [];
266
267 connector.getColumns(tablename, (err, columns) => {
268 if (err) {
269 callback(new Error(err.message), undefined);
270 return;
271 }
272
273 columns?.forEach(column => {
274 const name = column.field;
275 let type: DBValueType;
276
277 const dataType: string = column.Type;
278 if (dataType.startsWith("tinyint") || dataType.startsWith("smallint") || dataType.startsWith("mediumint") || dataType.startsWith("int") || dataType.startsWith("bigint")) {
279 type = DBValueType.INTEGER;
280 } else if (dataType.startsWith("decimal") || dataType.startsWith("float") || dataType.startsWith("double") || dataType.startsWith("real")) {
281 type = DBValueType.FLOAT;
282 } else if (dataType.startsWith("BLOB")) {
283 type = DBValueType.TEXT;
284 } else {
285 type = DBValueType.TEXT;
286 }
287
288 tableColumns.push({ name: name, type: type });
289 });
290
291 const createTableOptions: DBCreateTableOptions = { name: "", columns: tableColumns, softFail: true };
292 sqlite.createTable(createTableOptions, (err) => {
293 if (err) {
294 callback(new Error(err.message), undefined);
295 } else {
296 const selectOptions: DBSelectOptions = { table: tablename };
297 connector.select(selectOptions, (err, rows) => {
298 if (err) {
299 callback(new Error(err.message), undefined);
300 return;
301 } else {
302 rows?.forEach((row, rowIndex) => {
303 const insertOptions: DBInsertOptions = { table: tablename, data: rows};
304 sqlite.insert(insertOptions, (err) => {
305 if (err) {
306 callback(new Error(err.message), undefined);
307 return;
308 }
309 if (tableIndex === tablenames.length - 1 && rowIndex === rows.length - 1) {
310 sqlite.disconnect(() => {
311 callback(undefined, sqlite);
312 });
313 }
314 });
315 });
316 }
317 });
318 }
319 });
320 });
321 });
322 });
323 }
324 });
325 });
326 }
327}
328
329const options: MySQLOptions = {host: "localhost", port: 3306, user: "root", password: "", database: "db_connector"};
330const slite: SQLiteOptions = {path: "./dbdb.sqlite"};
331DBConnector.toSQLite(new MySQLConnector(options), slite, (err, connector) => {
332 console.log(err);
333 console.log(connector);
334});
335