Skip to content

Commit 0b9fc52

Browse files
GioeeGioee
committed
added sqlite directly supported alter table and tested it
1 parent 8edf387 commit 0b9fc52

4 files changed

Lines changed: 183 additions & 67 deletions

File tree

‎src/manager.ts‎

Lines changed: 99 additions & 66 deletions
Original file line numberDiff line numberDiff line change
@@ -7,12 +7,14 @@ import {
77
SQLiteManagerDefault,
88
SQLiteManagerCollate,
99
SQLiteManagerForeignKeyOptions,
10-
SQLiteManagerForeignKeyOn
10+
SQLiteManagerForeignKeyOn,
11+
AT
1112
} from './types'
1213

1314
export class SQLiteManager {
1415
private table: SQLiteManagerTable
15-
private create: boolean
16+
private create = false
17+
private query = ''
1618

1719
constructor(table?: SQLiteManagerTable) {
1820
if (typeof table === 'undefined') {
@@ -21,8 +23,9 @@ export class SQLiteManager {
2123
} else {
2224
if (table.name) {
2325
this.create = true
24-
} else {
25-
this.create = false
26+
if (table.columns) {
27+
this.create = false
28+
}
2629
}
2730
this.table = table
2831
}
@@ -32,84 +35,114 @@ export class SQLiteManager {
3235
this.table = {} as SQLiteManagerTable
3336
}
3437

38+
/** If changing name in altertable you need to manually call the queryBuilder() */
3539
set name(name: string) {
36-
this.table.name = name
40+
if (this.create) {
41+
this.table.name = name
42+
} else {
43+
this.table.name = name
44+
this.queryBuilder(AT.RENAME_TABLE, { name: name } as SQLiteManagerColumn)
45+
}
3746
}
3847

39-
queryBuilder(): string {
48+
queryBuilder(op?: AT, column?: SQLiteManagerColumn, newColumn?: string): string {
4049
let query = ''
4150

42-
if (this.create && this.table.columns) {
43-
query += 'CREATE TABLE "' + this.table.name + '" ('
51+
if (this.table.columns) {
52+
if (this.create) {
53+
query += 'CREATE TABLE "' + this.table.name + '" ('
54+
55+
for (let j = 0; j < this.table.columns.length; j++) {
56+
query += this.queryBuilderColumn(this.table.columns[j])
57+
if (j < this.table.columns.length - 1) {
58+
query += ', '
59+
}
60+
}
61+
62+
query += ');'
63+
} else {
64+
if (typeof op != 'undefined' && column) {
65+
switch (op) {
66+
case AT.RENAME_TABLE:
67+
this.query += 'ALTER TABLE "' + this.table.name + '" RENAME TO "' + column.name + '";\n'
68+
break
69+
case AT.ADD_COLUMN:
70+
this.query += 'ALTER TABLE "' + this.table.name + '" ADD COLUMN ' + this.queryBuilderColumn(column) + ';\n'
71+
break
72+
case AT.DROP_COLUMN:
73+
this.query += 'ALTER TABLE "' + this.table.name + '" DROP COLUMN "' + column.name + '";\n'
74+
break
75+
case AT.RENAME_COLUMN:
76+
if (newColumn) this.query += 'ALTER TABLE "' + this.table.name + '" RENAME COLUMN "' + column.name + '" TO "' + newColumn + '";\n'
77+
break
78+
}
79+
}
4480

45-
for (let j = 0; j < this.table.columns.length; j++) {
46-
const column: SQLiteManagerColumn = this.table.columns[j]
81+
query = this.query
82+
}
83+
}
4784

48-
query += '"' + column.name + '" ' + SQLiteManagerType[column.type]
85+
return query
86+
}
87+
88+
private queryBuilderColumn(column: SQLiteManagerColumn): string {
89+
let query = ''
90+
query += '"' + column.name + '" ' + SQLiteManagerType[column.type]
4991

92+
if (column.constraints) {
93+
const constraints: string[] = Object.keys(column.constraints).filter(key => {
5094
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-
}
95+
return column.constraints[key as keyof SQLiteManagerConstraints]
96+
}
97+
})
98+
99+
constraints.forEach(constraint => {
100+
query += ' ' + constraint.replace('_', ' ')
101+
})
102+
103+
if (column.constraints.Check) {
104+
query += ' CHECK (' + column.constraints.Check + ')'
105+
}
106+
107+
if (column.constraints.Default) {
108+
query += ' DEFAULT '
109+
if (typeof column.constraints.Default === 'string') {
110+
query += column.constraints.Default
111+
} else {
112+
query += SQLiteManagerDefault[column.constraints.Default]
113+
}
114+
}
115+
116+
if (column.constraints.Collate) {
117+
query += ' COLLATE '
118+
if (typeof column.constraints.Collate === 'string') {
119+
query += column.constraints.Collate
120+
} else {
121+
query += SQLiteManagerCollate[column.constraints.Collate]
122+
}
123+
}
64124

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-
}
125+
if (column.constraints.ForeignKey) {
126+
if (column.constraints.ForeignKey.enabled) {
127+
query += ' REFERENCES ' + column.constraints.ForeignKey.table + '(' + column.constraints.ForeignKey.column + ')'
128+
if (column.constraints.ForeignKey.options) {
129+
query += ' ' + SQLiteManagerForeignKeyOptions[column.constraints.ForeignKey.options]
72130
}
73131

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-
}
132+
if (column.constraints.ForeignKey.onDelete) {
133+
query += ' ON DELETE ' + SQLiteManagerForeignKeyOn[column.constraints.ForeignKey.onDelete]
81134
}
82135

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-
if (column.constraints.ForeignKey.match) {
99-
query += ' MATCH ' + column.constraints.ForeignKey.match
100-
}
101-
}
136+
if (column.constraints.ForeignKey.onUpdate) {
137+
query += ' ON UPDATE ' + SQLiteManagerForeignKeyOn[column.constraints.ForeignKey.onUpdate]
102138
}
103-
}
104139

105-
if (j < this.table.columns.length - 1) {
106-
query += ', '
140+
if (column.constraints.ForeignKey.match) {
141+
query += ' MATCH ' + column.constraints.ForeignKey.match
142+
}
107143
}
108144
}
109-
110-
query += ');'
111145
}
112-
113146
return query
114147
}
115148

@@ -120,7 +153,7 @@ export class SQLiteManager {
120153
this.table.columns = [column]
121154
}
122155

123-
return this.queryBuilder()
156+
return this.queryBuilder(AT.ADD_COLUMN, column)
124157
}
125158

126159
deleteColumn(name: string): string {
@@ -130,7 +163,7 @@ export class SQLiteManager {
130163
this.table.columns.splice(i, 1)
131164
}
132165

133-
return this.queryBuilder()
166+
return this.queryBuilder(AT.DROP_COLUMN, { name: name } as SQLiteManagerColumn)
134167
}
135168

136169
renameColumn(oldColumnName: string, newColumnName: string): string {
@@ -140,7 +173,7 @@ export class SQLiteManager {
140173
this.table.columns[i].name = newColumnName
141174
}
142175

143-
return this.queryBuilder()
176+
return this.queryBuilder(AT.RENAME_COLUMN, { name: oldColumnName } as SQLiteManagerColumn, newColumnName)
144177
}
145178

146179
changeColumnType(name: string, type: SQLiteManagerType): string {

‎src/types.ts‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,3 +278,11 @@ export interface SQLiteManagerTable {
278278
/** Columns */
279279
columns?: SQLiteManagerColumn[]
280280
}
281+
282+
/** SQLite Alter Table */
283+
export enum AT {
284+
ADD_COLUMN,
285+
DROP_COLUMN,
286+
RENAME_COLUMN,
287+
RENAME_TABLE
288+
}

‎test/assets/manager-test-tables.ts‎

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,3 +41,44 @@ export const testTable = {
4141
}
4242
]
4343
}
44+
45+
export const testTable2 = {
46+
name: 'myTable',
47+
columns: [
48+
{
49+
name: 'test1',
50+
type: types.SQLiteManagerType.REAL,
51+
constraints: {
52+
PRIMARY_KEY: true,
53+
AUTOINCREMENT: true,
54+
NOT_NULL: true,
55+
UNIQUE: true
56+
}
57+
},
58+
{
59+
name: 'test2',
60+
type: types.SQLiteManagerType.INTEGER,
61+
constraints: {
62+
NOT_NULL: true,
63+
UNIQUE: true
64+
}
65+
},
66+
{
67+
name: 'test3',
68+
type: types.SQLiteManagerType.TEXT,
69+
constraints: {
70+
NOT_NULL: false,
71+
UNIQUE: true
72+
}
73+
},
74+
{
75+
name: 'test4',
76+
type: types.SQLiteManagerType.BLOB,
77+
constraints: {
78+
NOT_NULL: true,
79+
UNIQUE: true,
80+
CHECK: 'test4 > 0'
81+
}
82+
}
83+
]
84+
}

‎test/manager.test.ts‎

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
/* eslint-disable prettier/prettier */
22
import { SQLiteManager } from '../src/manager'
33
import { SQLiteManagerType } from '../src/types'
4-
import { testTable } from './assets/manager-test-tables'
4+
import { testTable, testTable2 } from './assets/manager-test-tables'
55

66
describe('Create a table', () => {
77
let manager: SQLiteManager
@@ -30,4 +30,38 @@ describe('Create a table', () => {
3030

3131
expect(risCnstr).toContain('NOT NULL UNIQUE')
3232
})
33+
34+
it('tests alter table', () => {
35+
manager = new SQLiteManager(testTable)
36+
37+
const addColumn: string = manager.addColumn(testTable2.columns[0])
38+
39+
expect(addColumn).toContain('ALTER TABLE')
40+
expect(addColumn).toContain(testTable.name)
41+
expect(addColumn).toContain('ADD COLUMN')
42+
expect(addColumn).toContain(testTable2.columns[0].name)
43+
expect(addColumn).toContain(SQLiteManagerType[testTable2.columns[0].type])
44+
45+
manager.addColumn(JSON.parse(JSON.stringify(testTable2.columns[1])))
46+
expect(manager.deleteColumn(testTable2.columns[0].name)).toContain('ALTER TABLE "' + testTable.name + '" DROP COLUMN "' + testTable2.columns[0].name + '";')
47+
48+
const risRen: string = manager.renameColumn(testTable.columns[1].name, testTable2.columns[2].name)
49+
50+
expect(risRen).toContain(testTable.columns[1].name)
51+
expect(risRen).toContain(testTable2.columns[2].name)
52+
53+
manager.name = testTable2.name
54+
const renTable: string = manager.queryBuilder()
55+
56+
expect(renTable).toContain('ALTER TABLE "' + testTable.name + '" RENAME TO "' + testTable2.name + '";')
57+
58+
/* const risCh: string = manager.changeColumnType(testTable.columns[2].name, SQLiteManagerType.TEXT)
59+
60+
expect(risCh).toContain(SQLiteManagerType[SQLiteManagerType.TEXT])
61+
expect(risCh).not.toContain(SQLiteManagerType[SQLiteManagerType.INTEGER])
62+
63+
const risCnstr: string = manager.changeColumnConstraints(testTable.columns[2].name, testTable.columns[2].constraints)
64+
65+
expect(risCnstr).toContain('NOT NULL UNIQUE') */
66+
})
3367
})

0 commit comments

Comments
 (0)