-
-
Notifications
You must be signed in to change notification settings - Fork 563
Expand file tree
/
Copy pathdbstructures.sqlite.pas
More file actions
435 lines (405 loc) · 17.5 KB
/
dbstructures.sqlite.pas
File metadata and controls
435 lines (405 loc) · 17.5 KB
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
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
unit dbstructures.sqlite;
interface
uses
dbstructures;
const
{ SQLite Result Codes
result code definitions
Many SQLite functions return an integer result code from the set shown
here in order to indicate success or failure.
New error codes may be added in future versions of SQLite.
See also: [extended result code definitions]
}
SQLITE_OK = 0; // Successful result
// beginning-of-error-codes
SQLITE_ERROR = 1; // Generic error
SQLITE_INTERNAL = 2; // Internal logic error in SQLite
SQLITE_PERM = 3; // Access permission denied
SQLITE_ABORT = 4; // Callback routine requested an abort
SQLITE_BUSY = 5; // The database file is locked
SQLITE_LOCKED = 6; // A table in the database is locked
SQLITE_NOMEM = 7; // A malloc() failed
SQLITE_READONLY = 8; // Attempt to write a readonly database
SQLITE_INTERRUPT = 9; // Operation terminated by sqlite3_interrupt()*/
SQLITE_IOERR = 10; // Some kind of disk I/O error occurred
SQLITE_CORRUPT = 11; // The database disk image is malformed
SQLITE_NOTFOUND = 12; // Unknown opcode in sqlite3_file_control()
SQLITE_FULL = 13; // Insertion failed because database is full
SQLITE_CANTOPEN = 14; // Unable to open the database file
SQLITE_PROTOCOL = 15; // Database lock protocol error
SQLITE_EMPTY = 16; // Internal use only
SQLITE_SCHEMA = 17; // The database schema changed
SQLITE_TOOBIG = 18; // String or BLOB exceeds size limit
SQLITE_CONSTRAINT = 19; // Abort due to constraint violation
SQLITE_MISMATCH = 20; // Data type mismatch
SQLITE_MISUSE = 21; // Library used incorrectly
SQLITE_NOLFS = 22; // Uses OS features not supported on host
SQLITE_AUTH = 23; // Authorization denied
SQLITE_FORMAT = 24; // Not used
SQLITE_RANGE = 25; // 2nd parameter to sqlite3_bind out of range
SQLITE_NOTADB = 26; // File opened that is not a database file
SQLITE_NOTICE = 27; // Notifications from sqlite3_log()
SQLITE_WARNING = 28; // Warnings from sqlite3_log()
SQLITE_ROW = 100; // sqlite3_step() has another row ready
SQLITE_DONE = 101; // sqlite3_step() has finished executing
{ SQLite Flags
These constants define various flags that can be passed into
"prepFlags" parameter of the [sqlite3_prepare_v3()] and
[sqlite3_prepare16_v3()] interfaces.
New flags may be added in future releases of SQLite.
}
SQLITE_PREPARE_PERSISTENT = $01; // prepared statement will be retained for a long time and probably reused many times
SQLITE_PREPARE_NORMALIZE = $02; // no-op
SQLITE_PREPARE_NO_VTAB = $04; // return an error (error code SQLITE_ERROR) if the statement uses any virtual tables
{ SQLite Fundamental Datatypes
Every value in SQLite has one of five fundamental datatypes:
64-bit signed integer
64-bit IEEE floating point number
string
BLOB
NULL
}
SQLITE_INTEGER = 1;
SQLITE_FLOAT = 2;
SQLITE_BLOB = 4;
SQLITE_NULL = 5;
SQLITE_TEXT = 3;
SQLITE3_TEXT = 3;
{ CAPI3REF: Database Connection Configuration Options
These constants are the available integer configuration options that
can be passed as the second argument to the [sqlite3_db_config()] interface.
}
SQLITE_DBCONFIG_MAINDBNAME = 1000; // const char*
SQLITE_DBCONFIG_LOOKASIDE = 1001; // void* int int
SQLITE_DBCONFIG_ENABLE_FKEY = 1002; // int int*
SQLITE_DBCONFIG_ENABLE_TRIGGER = 1003; // int int*
SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER = 1004; // int int*
SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION = 1005; // int int*
SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE = 1006; // int int*
SQLITE_DBCONFIG_ENABLE_QPSG = 1007; // int int*
SQLITE_DBCONFIG_TRIGGER_EQP = 1008; // int int*
SQLITE_DBCONFIG_RESET_DATABASE = 1009; // int int*
SQLITE_DBCONFIG_DEFENSIVE = 1010; // int int*
SQLITE_DBCONFIG_WRITABLE_SCHEMA = 1011; // int int*
SQLITE_DBCONFIG_LEGACY_ALTER_TABLE = 1012; // int int*
SQLITE_DBCONFIG_DQS_DML = 1013; // int int*
SQLITE_DBCONFIG_DQS_DDL = 1014; // int int*
SQLITE_DBCONFIG_ENABLE_VIEW = 1015; // int int*
SQLITE_DBCONFIG_MAX = 1015; // Largest DBCONFIG
type
Psqlite3 = Pointer;
Psqlite3_stmt = Pointer;
TSQLiteCollationNeededCallback = procedure(userData: Pointer; ppDb:Psqlite3; eTextRep: Integer; zName: PAnsiChar); cdecl;
TSQLiteCollation = function(userData: Pointer; lenA: Integer; strA: PAnsiChar; lenB: Integer; strB: PAnsiChar): Integer; cdecl;
TSQLiteLib = class(TDbLib)
sqlite3_open: function(const filename: PAnsiChar; var ppDb: Psqlite3): Integer; cdecl;
sqlite3_libversion: function(): PAnsiChar; cdecl;
sqlite3_close: function(ppDb: Psqlite3): Integer; cdecl;
sqlite3_db_config: function (ppDb: Psqlite3; op: Integer): Integer; cdecl varargs;
sqlite3_enable_load_extension: function(ppDb: Psqlite3; onoff: Integer): Integer; cdecl;
sqlite3_errmsg: function(ppDb: Psqlite3): PAnsiChar; cdecl;
sqlite3_errcode: function(ppDb: Psqlite3): Integer; cdecl;
sqlite3_prepare_v2: function(ppDb: Psqlite3; zSql: PAnsiChar; nByte: Integer; var ppStmt: Psqlite3_stmt; var pzTail: PAnsiChar): Integer; cdecl;
sqlite3_prepare_v3: function(ppDb: Psqlite3; zSql: PAnsiChar; nByte: Integer; prepFlags: Cardinal; var ppStmt: Psqlite3_stmt; var pzTail: PAnsiChar): Integer; cdecl;
sqlite3_exec: function(ppDb: Psqlite3; sql: PAnsiChar; callback: Integer; callvack_arg: Pointer; errmsg: PAnsiChar): Integer; cdecl;
sqlite3_finalize: function(pStmt: Psqlite3_stmt): Integer; cdecl;
sqlite3_step: function(pStmt: Psqlite3_stmt): Integer; cdecl;
sqlite3_reset: function(pStmt: Psqlite3_stmt): Integer; cdecl;
sqlite3_total_changes: function(ppDb: Psqlite3): Integer; cdecl;
sqlite3_column_text: function(pStmt: Psqlite3_stmt; iCol: Integer): PAnsiChar; cdecl;
sqlite3_column_count: function(pStmt: Psqlite3_stmt): Integer; cdecl;
sqlite3_column_name: function(pStmt: Psqlite3_stmt; N: Integer): PAnsiChar; cdecl;
sqlite3_column_decltype: function(pStmt: Psqlite3_stmt; N: Integer): PAnsiChar; cdecl;
sqlite3_column_database_name: function(pStmt: Psqlite3_stmt; N: Integer): PAnsiChar; cdecl;
sqlite3_column_table_name: function(pStmt: Psqlite3_stmt; N: Integer): PAnsiChar; cdecl;
sqlite3_column_origin_name: function(pStmt: Psqlite3_stmt; N: Integer): PAnsiChar; cdecl;
sqlite3_column_type: function(pStmt: Psqlite3_stmt; iCol: Integer): Integer; cdecl;
sqlite3_next_stmt: function(ppDb: Psqlite3; pStmt: Psqlite3_stmt): Psqlite3_stmt; cdecl;
sqlite3_table_column_metadata: function(ppDb: Psqlite3;
zDbName, zTableName, zColumnName: PAnsiChar;
var pzDataType, pzCollSeq: PAnsiChar; var pNotNull, pPrimaryKey, pAutoinc: Integer
): Integer; cdecl;
sqlite3_collation_needed: function(ppDb: Psqlite3; userData: Pointer; Func: TSQLiteCollationNeededCallback): Integer; cdecl;
sqlite3_create_collation: function(ppDb: Psqlite3; const zName: PAnsiChar; eTextRep: Integer; pArg: Pointer; xCompare: TSQLiteCollation): Integer; cdecl;
// Additionally, for use in Multiple Ciphers library:
sqlite3_key: function(ppDb: Psqlite3; const pKey: Pointer; nKey: Integer): Integer; cdecl;
sqlite3mc_cipher_count: function(): Integer; cdecl;
sqlite3mc_cipher_name: function(cipherIndex: Integer): PAnsiChar; cdecl;
sqlite3mc_cipher_index: function(const cipherName: PAnsiChar): Integer; cdecl;
sqlite3mc_config: function(ppDb: Psqlite3; const paramName: PAnsiChar; newValue: Integer): Integer; cdecl;
sqlite3mc_config_cipher: function(ppDb: Psqlite3; const cipherName: PAnsiChar; const paramName: PAnsiChar; newValue: Integer): Integer; cdecl;
private
FWithMultipleCipherFunctions: Boolean;
protected
procedure AssignProcedures; override;
public
constructor Create(DllFile, DefaultDll: String); override;
constructor CreateWithMultipleCipherFunctions(DllFile, DefaultDll: String);
end;
TSQLiteProvider = class(TSqlProvider)
public
function GetSql(AId: TQueryId): string; override;
end;
var
SQLiteDatatypes: Array[0..15] of TDBDatatype =
(
(
Index: dbdtUnknown;
Name: 'UNKNOWN';
Description: 'Unknown data type';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: False;
LoadPart: False;
Category: dtcOther;
),
(
Index: dbdtTinyint;
Name: 'TINYINT';
Names: 'INT2|BOOLEAN|BOOL';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: False;
LoadPart: False;
Category: dtcInteger;
),
(
Index: dbdtInt;
Name: 'INTEGER';
Names: 'INT|MEDIUMINT|INT8';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcInteger;
),
(
Index: dbdtUint;
Name: 'UINT';
Names: 'UINT';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcInteger;
),
(
Index: dbdtBigint;
Name: 'BIGINT';
Names: 'UNSIGNED BIG INT';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcInteger;
),
(
Index: dbdtChar;
Name: 'CHAR';
Names: 'CHARACTER|CHAR|NCHAR|NATIVE CHARACTER';
HasLength: True;
RequiresLength: True;
HasBinary: False;
HasDefault: True;
LoadPart: True;
DefLengthSet: '50';
Category: dtcText;
),
(
Index: dbdtVarchar;
Name: 'VARCHAR';
Names: 'VARCHAR|VARYING CHARACTER|NVARCHAR|CHARACTER|CHAR|NCHAR|NATIVE CHARACTER';
HasLength: True;
RequiresLength: True;
HasBinary: False;
HasDefault: True;
LoadPart: True;
DefLengthSet: '50';
Category: dtcText;
),
(
Index: dbdtText;
Name: 'TEXT';
Names: 'CLOB';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: True;
Category: dtcText;
),
(
Index: dbdtUniqueidentifier;
Name: 'UNIQUEIDENTIFIER';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcBinary;
),
(
Index: dbdtBlob;
Name: 'BLOB';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: False;
LoadPart: True;
Category: dtcBinary;
),
(
Index: dbdtReal;
Name: 'REAL';
Names: 'REAL|NUMERIC|DOUBLE|DOUBLE PRECISION|FLOAT|DECIMAL';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcReal;
),
(
Index: dbdtDate;
Name: 'DATE';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcTemporal;
),
(
Index: dbdtTime;
Name: 'TIME';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcTemporal;
),
(
Index: dbdtDatetime;
Name: 'DATETIME';
HasLength: False;
RequiresLength: False;
HasBinary: False;
HasDefault: True;
LoadPart: False;
Category: dtcTemporal;
),
(
Index: dbdtEnum;
Name: 'ENUM';
HasLength: True;
RequiresLength: True;
HasBinary: False;
HasDefault: True;
LoadPart: False;
DefLengthSet: '''Y'',''N''';
Category: dtcOther;
),
(
Index: dbdtSet;
Name: 'SET';
HasLength: True;
RequiresLength: True;
HasBinary: False;
HasDefault: True;
LoadPart: False;
DefLengthSet: '''Value A'',''Value B''';
Category: dtcOther;
)
);
implementation
constructor TSQLiteLib.Create(DllFile, DefaultDll: String);
begin
FWithMultipleCipherFunctions := False;
inherited;
end;
constructor TSQLiteLib.CreateWithMultipleCipherFunctions(DllFile, DefaultDll: String);
begin
FWithMultipleCipherFunctions := True;
inherited Create(DllFile, DefaultDll);
end;
procedure TSQLiteLib.AssignProcedures;
begin
AssignProc(@sqlite3_open, 'sqlite3_open');
AssignProc(@sqlite3_libversion, 'sqlite3_libversion');
AssignProc(@sqlite3_close, 'sqlite3_close');
AssignProc(@sqlite3_db_config, 'sqlite3_db_config');
AssignProc(@sqlite3_enable_load_extension, 'sqlite3_enable_load_extension');
AssignProc(@sqlite3_errmsg, 'sqlite3_errmsg');
AssignProc(@sqlite3_errcode, 'sqlite3_errcode');
AssignProc(@sqlite3_prepare_v2, 'sqlite3_prepare_v2');
AssignProc(@sqlite3_prepare_v3, 'sqlite3_prepare_v3');
AssignProc(@sqlite3_exec, 'sqlite3_exec');
AssignProc(@sqlite3_finalize, 'sqlite3_finalize');
AssignProc(@sqlite3_step, 'sqlite3_step');
AssignProc(@sqlite3_reset, 'sqlite3_reset');
AssignProc(@sqlite3_total_changes, 'sqlite3_total_changes');
AssignProc(@sqlite3_column_text, 'sqlite3_column_text');
AssignProc(@sqlite3_column_count, 'sqlite3_column_count');
AssignProc(@sqlite3_column_name, 'sqlite3_column_name');
AssignProc(@sqlite3_column_decltype, 'sqlite3_column_decltype');
AssignProc(@sqlite3_column_database_name, 'sqlite3_column_database_name');
AssignProc(@sqlite3_column_table_name, 'sqlite3_column_table_name');
AssignProc(@sqlite3_column_origin_name, 'sqlite3_column_origin_name');
AssignProc(@sqlite3_column_type, 'sqlite3_column_type');
AssignProc(@sqlite3_next_stmt, 'sqlite3_next_stmt');
AssignProc(@sqlite3_table_column_metadata, 'sqlite3_table_column_metadata');
AssignProc(@sqlite3_collation_needed, 'sqlite3_collation_needed');
AssignProc(@sqlite3_create_collation, 'sqlite3_create_collation');
if FWithMultipleCipherFunctions then begin
// Additionally, for use in Multiple Ciphers library:
AssignProc(@sqlite3_key, 'sqlite3_key', False);
AssignProc(@sqlite3mc_cipher_count, 'sqlite3mc_cipher_count');
AssignProc(@sqlite3mc_cipher_name, 'sqlite3mc_cipher_name');
AssignProc(@sqlite3mc_cipher_index, 'sqlite3mc_cipher_index');
AssignProc(@sqlite3mc_config, 'sqlite3mc_config');
AssignProc(@sqlite3mc_config_cipher, 'sqlite3mc_config_cipher');
end;
end;
{ TSQLiteProvider }
function TSQLiteProvider.GetSql(AId: TQueryId): string;
begin
case AId of
qDatabaseDrop: Result := 'DROP DATABASE %s';
qRenameTable: Result := 'ALTER TABLE %s RENAME TO %s';
qRenameView: Result := 'ALTER TABLE %s RENAME TO %s';
qCurrentUserHost: Result := ''; // unsupported
qLikeCompare: Result := '%s LIKE %s';
qAddColumn: Result := 'ADD COLUMN %s';
qChangeColumn: Result := ''; // SQLite only supports renaming
qRenameColumn: Result := 'RENAME COLUMN %s TO %s';
qSessionVariables: Result := 'SELECT null, null'; // Todo: combine "PRAGMA pragma_list" + "PRAGMA a; PRAGMY b; ..."?
qGlobalVariables: Result := 'SHOW GLOBAL VARIABLES';
qISSchemaCol: Result := '%s_SCHEMA';
qUSEQuery: Result := '';
qKillQuery: Result := 'KILL %d';
qKillProcess: Result := 'KILL %d';
qFuncLength: Result := 'LENGTH';
qFuncCeil: Result := 'CEIL';
qFuncLeft: Result := 'SUBSTR(%s, 1, %d)';
qFuncNow: Result := 'DATETIME()';
qFuncLastAutoIncNumber: Result := 'LAST_INSERT_ID()';
qLockedTables: Result := '';
qDisableForeignKeyChecks: Result := '';
qEnableForeignKeyChecks: Result := '';
qForeignKeyDrop: Result := 'DROP FOREIGN KEY %s';
qGetTableColumns: Result := 'SELECT * FROM pragma_table_xinfo(%s, %s)';
// See https://www.sqlite.org/datatype3.html#collation_sequence_examples
qGetCollations: Result := 'SELECT name AS "Collation", '''' AS "Charset", '''' AS "Id", '''' AS "Default", '''' AS "Compiled", ''1'' AS Sortlen from pragma_collation_list';
qGetCharsets: Result := 'SELECT ''UTF-8'' AS "Charset", ''UTF-8'' AS "Description" '+
'UNION SELECT ''UTF-16le'', ''UTF-16 Little Endian'' '+
'UNION SELECT ''UTF-16be'', ''UTF-16 Big Endian''';
else Result := inherited;
end;
end;
end.