Skip to content

Commit 3c575de

Browse files
GioeeGioee
committed
first query builder commit: added types, sqlitemanager with create table and tested everything
1 parent f07abc1 commit 3c575de

7 files changed

Lines changed: 405 additions & 4 deletions

File tree

‎jest.config.js‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
module.exports = {
22
preset: 'ts-jest',
33
testEnvironment: 'node',
4-
testMatch: ['**/test/**/*.test.ts'],
4+
testMatch: ['**/test/**/manager.test.ts'],
55
maxWorkers: 12,
6-
collectCoverageFrom: ['<rootDir>/src/**/*.ts', '!<rootDir>/src/index.ts', '!<rootDir>/src/types/**/*.ts'],
6+
collectCoverageFrom: ['<rootDir>/src/**/types.ts', '<rootDir>/src/**/manager.ts'],
77
transform: {
88
'^.+\\.(ts|tsx)$': [
99
'ts-jest',

‎package-lock.json‎

Lines changed: 2 additions & 2 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎src/index.ts‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,3 +10,15 @@ export { SQLiteCloudRowset, SQLiteCloudRow } from './rowset'
1010
export { SQLiteCloudConnection } from './connection'
1111

1212
export { escapeSqlParameter, prepareSql } from './utilities'
13+
14+
export { SQLiteManager } from './manager'
15+
export {
16+
SQLiteManagerType,
17+
SQLiteManagerColumn,
18+
SQLiteManagerTable,
19+
SQLiteManagerConstraints,
20+
SQLiteManagerForeignKeyOn,
21+
SQLiteManagerForeignKeyOptions,
22+
SQLiteManagerCollate,
23+
SQLiteManagerDefault
24+
} from './types'

‎src/manager.ts‎

Lines changed: 169 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,169 @@
1+
/* eslint-disable prettier/prettier */
2+
import {
3+
SQLiteManagerType,
4+
SQLiteManagerColumn,
5+
SQLiteManagerTable,
6+
SQLiteManagerConstraints,
7+
SQLiteManagerDefault,
8+
SQLiteManagerCollate,
9+
SQLiteManagerForeignKeyOptions,
10+
SQLiteManagerForeignKeyOn
11+
} from './types'
12+
13+
export class SQLiteManager {
14+
private table: SQLiteManagerTable
15+
private create: boolean
16+
17+
constructor(table?: SQLiteManagerTable) {
18+
if (typeof table === 'undefined') {
19+
this.create = true
20+
this.table = {} as SQLiteManagerTable
21+
} else {
22+
if (table.name) {
23+
this.create = true
24+
} else {
25+
this.create = false
26+
}
27+
this.table = table
28+
}
29+
}
30+
31+
reset(): void {
32+
this.table = {} as SQLiteManagerTable
33+
}
34+
35+
set name(name: string) {
36+
this.table.name = name
37+
}
38+
39+
queryBuilder(): string {
40+
let query = ''
41+
42+
if (this.create && this.table.columns) {
43+
query += 'CREATE TABLE "' + this.table.name + '" ('
44+
45+
for (let j = 0; j < this.table.columns.length; j++) {
46+
const column: SQLiteManagerColumn = this.table.columns[j]
47+
48+
query += '"' + column.name + '" ' + SQLiteManagerType[column.type]
49+
50+
if (column.constraints) {
51+
const constraints: string[] = Object.keys(column.constraints).filter(key => {
52+
if (column.constraints) {
53+
return column.constraints[key as keyof SQLiteManagerConstraints]
54+
}
55+
})
56+
57+
constraints.forEach(constraint => {
58+
query += ' ' + constraint.replace('_', ' ')
59+
})
60+
61+
if (column.constraints.Check) {
62+
query += ' CHECK (' + column.constraints.Check + ')'
63+
}
64+
65+
if (column.constraints.Default) {
66+
query += ' DEFAULT '
67+
if (typeof column.constraints.Default === 'string') {
68+
query += column.constraints.Default
69+
} else {
70+
query += SQLiteManagerDefault[column.constraints.Default]
71+
}
72+
}
73+
74+
if (column.constraints.Collate) {
75+
query += ' COLLATE '
76+
if (typeof column.constraints.Collate === 'string') {
77+
query += column.constraints.Collate
78+
} else {
79+
query += SQLiteManagerCollate[column.constraints.Collate]
80+
}
81+
}
82+
83+
if (column.constraints.ForeignKey) {
84+
if (column.constraints.ForeignKey.enabled) {
85+
query += ' REFERENCES ' + column.constraints.ForeignKey.table + '(' + column.constraints.ForeignKey.column + ')'
86+
if (column.constraints.ForeignKey.options) {
87+
query += ' ' + SQLiteManagerForeignKeyOptions[column.constraints.ForeignKey.options]
88+
}
89+
90+
if (column.constraints.ForeignKey.onDelete) {
91+
query += ' ON DELETE ' + SQLiteManagerForeignKeyOn[column.constraints.ForeignKey.onDelete]
92+
}
93+
94+
if (column.constraints.ForeignKey.onUpdate) {
95+
query += ' ON UPDATE ' + SQLiteManagerForeignKeyOn[column.constraints.ForeignKey.onUpdate]
96+
}
97+
}
98+
}
99+
}
100+
101+
if (j < this.table.columns.length - 1) {
102+
query += ', '
103+
}
104+
}
105+
106+
query += ');'
107+
}
108+
109+
return query
110+
}
111+
112+
mixTables(tables: SQLiteManagerTable, newTables: Partial<SQLiteManagerTable>): SQLiteManagerTable {
113+
return { ...tables, ...newTables }
114+
}
115+
116+
addColumn(column: SQLiteManagerColumn): string {
117+
this.table = this.mixTables(this.table, { name: this.table.name, columns: [Object.create(column)] })
118+
return this.queryBuilder()
119+
}
120+
121+
deleteColumn(columnName: string): string {
122+
if (this.table.columns) {
123+
const i = this.table.columns.findIndex(column => column.name === columnName)
124+
if (i > -1) {
125+
this.table.columns.splice(i, 1)
126+
}
127+
}
128+
//this.tables[index].columns = this.tables[index].columns.filter(column => column.name !== columnName) it's slower
129+
130+
return this.queryBuilder()
131+
}
132+
133+
renameColumn(oldColumnName: string, newColumnName: string): string {
134+
if (this.table.columns) {
135+
const i = this.table.columns.findIndex(column => column.name === oldColumnName)
136+
if (i > -1) {
137+
this.table.columns[i].name = newColumnName
138+
}
139+
}
140+
141+
return this.queryBuilder()
142+
}
143+
144+
changeColumnType(columnName: string, type: SQLiteManagerType): string {
145+
if (this.table.columns) {
146+
const i = this.table.columns.findIndex(column => column.name === columnName)
147+
if (i > -1) {
148+
this.table.columns[i].type = type
149+
}
150+
}
151+
152+
return this.queryBuilder()
153+
}
154+
155+
changeColumnConstraints(name: string, type: SQLiteManagerType, constraints: SQLiteManagerConstraints): string {
156+
/* if (typeof this.table.columns != 'undefined') {
157+
const i = this.table.columns.findIndex(column => column.name === columnName)
158+
if (i > -1) {
159+
this.table.columns[i].constraints = constraints
160+
}
161+
} */
162+
163+
this.table = this.mixTables(this.table, {
164+
columns: [{ name: name, type: type, constraints: constraints }]
165+
})
166+
167+
return this.queryBuilder()
168+
}
169+
}

‎src/types.ts‎

Lines changed: 144 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -129,3 +129,147 @@ export enum SQLiteCloudArrayType {
129129

130130
ARRAY_TYPE_SQLITE_STATUS = 50 // used in sqlite_status
131131
}
132+
133+
/** SQLite column types*/
134+
export enum SQLiteManagerType {
135+
TEXT,
136+
INTEGER,
137+
REAL,
138+
BLOB,
139+
VARCHAR,
140+
SMALLINT,
141+
FLOAT,
142+
DOUBLE,
143+
BOOLEAN,
144+
CURRENCY,
145+
DATE,
146+
TIME,
147+
TIMESTAMP,
148+
BINARY
149+
}
150+
151+
/** SQLite column defaults */
152+
export enum SQLiteManagerDefault {
153+
NULL,
154+
CURRENT_TIME,
155+
CURRENT_DATE,
156+
CURRENT_TIMESTAMP
157+
}
158+
159+
/** SQLite column collates */
160+
export enum SQLiteManagerCollate {
161+
BINARY,
162+
NOCASE,
163+
RTRIM
164+
}
165+
166+
/** SQLite column foreign key options */
167+
export enum SQLiteManagerForeignKeyOptions {
168+
NONE,
169+
DEFERRABLE,
170+
DEFERRABLE_INITIALLY_DEFERRED,
171+
DEFERRABLE_INITIALLY_IMMEDIATE,
172+
NOT_DEFERRABLE,
173+
NOT_DEFERRABLE_INITIALLY_DEFERRED,
174+
NOT_DEFERRABLE_INITIALLY_IMMEDIATE
175+
}
176+
177+
/** SQLite column foreign key on delete or on update cases */
178+
export enum SQLiteManagerForeignKeyOn {
179+
NO_ACTION,
180+
RESTRICT,
181+
SET_NULL,
182+
SET_DEFAULT,
183+
CASCADE
184+
}
185+
186+
/** SQLite foreign key */
187+
class SQLiteManagerForeignKey {
188+
enabled = false
189+
table = ''
190+
column = ''
191+
options: SQLiteManagerForeignKeyOptions = SQLiteManagerForeignKeyOptions.NONE
192+
onDelete: SQLiteManagerForeignKeyOn = SQLiteManagerForeignKeyOn.NO_ACTION
193+
onUpdate: SQLiteManagerForeignKeyOn = SQLiteManagerForeignKeyOn.NO_ACTION
194+
195+
constructor(
196+
enabled: boolean,
197+
table: string,
198+
column: string,
199+
options?: SQLiteManagerForeignKeyOptions,
200+
onDelete?: SQLiteManagerForeignKeyOn,
201+
onUpdate?: SQLiteManagerForeignKeyOn
202+
) {
203+
if (enabled) {
204+
this.enable(table, column, options, onDelete, onUpdate)
205+
} else {
206+
this.disable()
207+
}
208+
}
209+
210+
/** By disabling foreign key you delete references */
211+
disable() {
212+
this.enabled = false
213+
this.table = ''
214+
this.column = ''
215+
this.options = SQLiteManagerForeignKeyOptions.NONE
216+
this.onDelete = SQLiteManagerForeignKeyOn.NO_ACTION
217+
this.onUpdate = SQLiteManagerForeignKeyOn.NO_ACTION
218+
}
219+
220+
enable(table: string, column: string, options?: SQLiteManagerForeignKeyOptions, onDelete?: SQLiteManagerForeignKeyOn, onUpdate?: SQLiteManagerForeignKeyOn) {
221+
this.enabled = true
222+
this.table = table
223+
this.column = column
224+
225+
if (options) {
226+
this.options = options
227+
}
228+
229+
if (onDelete) {
230+
this.onDelete = onDelete
231+
}
232+
233+
if (onUpdate) {
234+
this.onUpdate = onUpdate
235+
}
236+
}
237+
}
238+
239+
/** SQLite column constraints */
240+
export interface SQLiteManagerConstraints {
241+
PRIMARY_KEY?: boolean
242+
AUTOINCREMENT?: boolean
243+
NOT_NULL?: boolean
244+
UNIQUE?: boolean
245+
Check?: string
246+
247+
/** You should pass: SQLiteManagerDefault.NULL, SQLiteManagerDefault.CURRENT_TIME, etc. */
248+
Default?: SQLiteManagerDefault | string
249+
250+
/** You should pass: SQLiteManagerCollate.BINARY, SQLiteManagerCollate.NOCASE, etc. */
251+
Collate?: SQLiteManagerCollate | string
252+
253+
ForeignKey?: SQLiteManagerForeignKey
254+
}
255+
256+
/** SQLite column interface */
257+
export interface SQLiteManagerColumn {
258+
/** Column name */
259+
name: string
260+
261+
/** Data type */
262+
type: SQLiteManagerType
263+
264+
/** Constraints */
265+
constraints?: SQLiteManagerConstraints
266+
}
267+
268+
/** SQLite table interface */
269+
export interface SQLiteManagerTable {
270+
/** Name of the table */
271+
name: string
272+
273+
/** Columns */
274+
columns?: SQLiteManagerColumn[]
275+
}

0 commit comments

Comments
 (0)