-
Notifications
You must be signed in to change notification settings - Fork 0
/
NDBModel.ts
155 lines (138 loc) · 4.76 KB
/
NDBModel.ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
export type INDBModel = typeof NDBModel;
export class NDBModel {
static TableName = "";
static sqlCountAll() {
const TableName = this.TableName;
return `SELECT COUNT(*) AS counter FROM "${TableName}";`
}
static sqlCountGroup() {
const TableName = this.TableName;
const group = this.getGroupName();
return `SELECT COUNT(*) AS counter FROM "${TableName}" WHERE "${group}"=?;`
}
static sqlInsert(){
const TableName = this.TableName;
return `INSERT INTO "${TableName}" (${this.getKeysWithoutIndex().join(',')}) VALUES(${this.getKeysWithoutIndex().map(e=>'?').join(',')});`
}
static sqlUpdateByID() {
const TableName = this.TableName;
const indexKey = this.getIndexKey();
return `UPDATE "${TableName}" SET ${this.getKeysWithoutIndex().map(e=>e+"=?").join(',')} WHERE "${indexKey}"=?`;
}
static sqlSelectByID() {
const TableName = this.TableName;
const indexKey = this.getIndexKey();
return `SELECT * FROM "${TableName}" WHERE "${indexKey}"=?;`
}
static sqlDeleteByID() {
const TableName = this.TableName;
const indexKey = this.getIndexKey();
return `DELETE FROM "${TableName}" WHERE "${indexKey}"=?;`
}
static sqlSelectByText() {
const TableName = this.TableName;
const name = this.getTextSearchKey();
return `SELECT * FROM "${TableName}" WHERE "${name}" LIKE ?`
}
static sqlSelectByTextAndMatch(matches: string[]) {
const TableName = this.TableName;
const name = this.getTextSearchKey();
const sql = `SELECT * FROM "${TableName}" WHERE "${name}" LIKE ?`
if (matches.length===0) return sql;
const queries = [];
const validKeys = this.getKeysWithoutIndex();
for (const key of matches) {
if (validKeys.includes(key)) {
queries.push(`"${key}"=?`);
}
}
const matchQuery = queries.join(" AND ");
return `${sql} AND ${matchQuery}`;
}
static sqlSelectByGroupAndText() {
const sql = this.sqlSelectByText();
const group = this.getGroupName();
return `${sql} AND "${group}"=?`
}
static sqlCleanQuery(match?: {[key:string]: any}) {
if (typeof match === "undefined") {
return {keys: [], values: []}
}
const cleanQuery:string[] = [];
const cleanValues:any[] = [];
const validKeys = this.getKeysWithoutIndex();
for (const key in match) {
if (validKeys.includes(key)) {
cleanQuery.push(key);
cleanValues.push(match[key]);
}
};
return {
keys: cleanQuery,
values: cleanValues
}
}
static sqlSelectByGroupAndTextAndMatch(matches: string[]) {
const sql = this.sqlSelectByText();
const group = this.getGroupName();
const queries = [`"${group}"=?`];
const validKeys = this.getKeysWithoutIndex();
for (const key of matches) {
if (validKeys.includes(key)) {
queries.push(`"${key}"=?`);
}
}
const matchQuery = queries.join(" AND ");
return `${sql} AND ${matchQuery}`;
}
static getPaginationSort(sortBy?: string, asc?: boolean) {
const Paginate = " LIMIT ?, ?"
if (!sortBy) return Paginate;
const keys = [...this.getKeys()];
if (keys.includes(sortBy)) {
if (typeof asc !== "undefined" && asc === false ) return ` ORDER BY ${sortBy} DESC ${Paginate}`;
return ` ORDER BY ${sortBy} ASC ${Paginate}`;
}
return Paginate;
}
static sqlSelectByExactMatch() {
const TableName = this.TableName;
const name = this.getTextSearchKey();
return `SELECT * FROM "${TableName}" WHERE "${name}"=?`
}
static SqlCountSelectQuery(selectQuery: string) {
let r = selectQuery;
if (selectQuery.trim().startsWith("SELECT *")) {
r = selectQuery.replace("SELECT *", "SELECT COUNT(*) AS counter")
}
return r;
}
static getKeys() {
return [] as string [];
}
static getIndexKey() {
return "id";
}
static getTextSearchKey() {
return "";
}
static getGroupName() {
return "";
}
static getKeysWithoutIndex() {
const keys = [...this.getKeys()];
const index = keys.findIndex(k=>k===this.getIndexKey());
if (index<0) return keys;
keys.splice(index, 1);
return keys;
}
public asJson() {
const json = {};
// @ts-ignore
const keys = this.constructor.getKeys();
for (const key of keys) {
json[key] = this[key];
}
return json
}
}