1 /******************************************************************************
2 *
3 * Copyright (C) 2009, The Gentee Group. All rights reserved.
4 * This file is part of the Gentee open source project - http://www.gentee.com.
5 *
6 * THIS FILE IS PROVIDED UNDER THE TERMS OF THE GENTEE LICENSE ("AGREEMENT").
7 * ANY USE, REPRODUCTION OR DISTRIBUTION OF THIS FILE CONSTITUTES RECIPIENTS
8 * ACCEPTANCE OF THE AGREEMENT.
9 *
10 * Author: Alexander Antypenko ( santy ) v. 1.00
11 *
12 ******************************************************************************/
13
14 define
15 {
16 SQLITE_OK = 0 /* Successful result */
17 SQLITE_ERROR = 1 /* SQL error or missing database */
18 SQLITE_INTERNAL = 2 /* An internal logic error in SQLite */
19 SQLITE_PERM = 3 /* Access permission denied */
20 SQLITE_ABORT = 4 /* Callback routine requested an abort */
21 SQLITE_BUSY = 5 /* The database file is locked */
22 SQLITE_LOCKED = 6 /* A table in the database is locked */
23 SQLITE_NOMEM = 7 /* A malloc() failed */
24 SQLITE_READONLY = 8 /* Attempt to write a readonly database */
25 SQLITE_INTERRUPT = 9 /* Operation terminated by sqlite_interrupt() */
26 SQLITE_IOERR = 10 /* Some kind of disk I/O error occurred */
27 SQLITE_CORRUPT = 11 /* The database disk image is malformed */
28 SQLITE_NOTFOUND = 12 /* (Internal Only) Table or record not found */
29 SQLITE_FULL = 13 /* Insertion failed because database is full */
30 SQLITE_CANTOPEN = 14 /* Unable to open the database file */
31 SQLITE_PROTOCOL = 15 /* Database lock protocol error */
32 SQLITE_EMPTY = 16 /* (Internal Only) Database table is empty */
33 SQLITE_SCHEMA = 17 /* The database schema changed */
34 SQLITE_TOOBIG = 18 /* Too much data for one row of a table */
35 SQLITE_CONSTRAINT = 19 /* Abort due to contraint violation */
36 SQLITE_MISMATCH = 20 /* Data type mismatch */
37 SQLITE_MISUSE = 21 /* Library used incorrectly */
38 SQLITE_NOLFS = 22 /* Uses OS features not supported on host */
39 SQLITE_AUTH = 23 /* Authorization denied */
40 SQLITE_ROW = 100 /* sqlite_step() has another row ready */
41 SQLITE_DONE = 101 /* sqlite_step() has finished executing */
42 SQLITE_ANY = 5
43 }
44
45 define
46 {
47 SQLITE_INTEGER = 1
48 SQLITE_FLOAT = 2
49 SQLITE_TEXT = 3
50 SQLITE_BLOB = 4
51 SQLITE_NULL = 5
52 }
53
54 import "sqlite3.dll"
55 {
56 uint sqlite3_open(uint,uint) // ”гЄжЁп ®вЄалвЁп Ў §л ¤ ле
57 // Џ а: 1- §ў ЁҐ Ў §л; 2- Ё¤ҐвЁдЁЄ в®а ®вЄалв®© Ў §л
58 uint sqlite3_close(uint) // ”гЄжЁп § ЄалвЁп Ў §л ¤ ле
59 // Џ а: 1- Ё¤ҐвЁдЁЄ в®а ®вЄалв®© Ў §л
60 uint sqlite3_get_table(uint,uint,uint,uint,uint,uint) // ”гЄжЁп «®ЈЁз sqlite3_exec, ® Ґбвм ¤агЈЁҐ Ї а ¬Ґвал //ў®§ўа в
61 // Џ а.:
62 // 1. €¤Ґв. Ў §л
63 // 2. ‘ва®Є ®ЇҐа в®а®ў SQL
64 // 3. Њ ббЁў бва®Є Єг¤ § ЇЁбЁў овбп ¤ лҐ
65 // 4. Љ®«ЁзҐбвў® бва®Є
66 // 5. Љ®«ЁзҐбвў® Ї®«Ґ©
67 // 6. ‘ва®Є Єг¤ § ЇЁбЁў Ґвбп ®ЇЁб ЁҐ ®иЁЎЄЁ ( ¤аҐб)
68
69 uint sqlite3_exec(uint,uint,uint,uint,uint)// ”гЄжЁп § Їгбг SQL ®ЇҐа в®а®ў
70 // Џ а.:
71 // 1. €¤Ґв. Ў §л
72 // 2. ‘ва®Є ®ЇҐа в®а®ў SQL
73 // 3. ”гЄжЁп ў®§ўа в callBack (ў 襬 дгЄжЁЁ Ґвг -0)
74 // 4. ЏҐаўЁ© аЈг¬Ґв ў дгЄжЁо ў®§ўа в (0)
75 // 5. ‘ва®Є Єг¤ § ЇЁбЁў Ґвбп ®ЇЁб ЁҐ ®иЁЎЄЁ ( ¤аҐб)
76 uint sqlite3_errmsg(uint)
77 uint sqlite3_errcode(uint)
78
79 sqlite3_free(uint)
80 sqlite3_free_table(uint)
81
82 uint sqlite3_prepare(
83 uint, /* Database handle */
84 uint, /* SQL statement, UTF-8 encoded */
85 uint , /* Length of zSql in bytes. */
86 uint, /* OUT: Statement handle */
87 uint /* OUT: Pointer to unused portion of zSql */
88 )
89 uint sqlite3_prepare_v2(
90 uint, /* Database handle */
91 uint, /* SQL statement, UTF-8 encoded */
92 uint , /* Length of zSql in bytes. */
93 uint, /* OUT: Statement handle */
94 uint /* OUT: Pointer to unused portion of zSql */
95 )
96 uint sqlite3_column_count(uint)
97 uint sqlite3_data_count(uint)
98 uint sqlite3_step(uint)
99 uint sqlite3_column_name(uint,int)
100 uint sqlite3_column_type(uint, int)
101 uint sqlite3_column_blob(uint, int)
102 int sqlite3_column_bytes(uint, int)
103 double sqlite3_column_double(uint,int)
104 uint sqlite3_column_int(uint,int)
105 uint sqlite3_column_text(uint,int)
106 uint sqlite3_finalize(uint)
107 uint sqlite3_last_insert_rowid(uint)
108 uint sqlite3_changes(uint)
109 uint sqlite3_busy_timeout(uint,int)
110 uint sqlite3_create_function( uint, uint, uint, uint, uint, uint, uint, uint )
111 uint sqlite3_value_text( uint )
112 sqlite3_result_text( uint, uint, uint, uint )
113 }
114
115 type arr_str
116 {
117 arrstr values;
118 }
119
120 operator arr_str =(arr_str aLeft,arrstr aRight)
121 {
122 aLeft.values=aRight
123 return aLeft;
124 }
125
126
127 type sqlite3
128 {
129 uint db // database handle
130 uint db_ptr // ptr to database handle
131 str error_message
132 arrstr col_names // array of column headers
133 arr col_types of uint // array of column types
134 arr col_val of arr_str // array of column values
135 uint compiled_sql_ptr // ptr to compiled sql
136 uint rc // result data
137 byte transStarted
138 }
139 extern
140 {
141 method int sqlite3.sql_execute(str sql_code)
142 method sqlite3.getColumnsValue(uint num_columns,uint uLich)
143 method sqlite3.getColumnsType(uint num_columns)
144 method sqlite3.getColumnNames(uint num_columns)
145 }
146
147
148 method sqlite3.init()
149 {
150
151 }
152 /*-----------------------------------------------------------------------------
153 * @syntax [ sql3.open(db_name) ]
154 *
155 * @param [db_name] The name of the database.
156 *
157 * @return A database handle or error in error_message field
158 *
159 * Opens or creates a database. If the database does exist it gets opened,
160 * else a new database with the name given is created.
161 -----------------------------------------------------------------------------*/
162 func LOWER(uint ctx, int narg, uint args )
163 {
164 //if (narg)
165 {
166 uint p = sqlite3_value_text( args->uint )
167 str s
168 s.copy( p )
169 /*uint i
170 fornum i, 10
171 {
172 print( "\((p+i)->ubyte) " )
173 }
174 */
175 s.lower()
176 //print( "lLL \(narg) \(s)\n" )
177 //s = "tttt"
178 sqlite3_result_text(ctx,s.ptr(), *s, -1 )
179 }
180 }
181 func UPPER(uint ctx, int narg, uint args )
182 {
183 print( "up1\n" )
184 //if (narg)
185 {
186 uint p = sqlite3_value_text( args->uint )
187 str s
188 s.copy( p )
189 /* uint i
190 fornum i, 10
191 {
192 print( "\((p+i)->ubyte) " )
193 }*/
194
195 s.upper()
196 //print( "lLL \(narg) \(s)\n" )
197 //s = "tttt"
198 sqlite3_result_text(ctx, s.ptr(), *s, -1 )
199 }
200 print( "up2\n" )
201 }
202 global { uint cbupper, cblower }
203 func cb<entry>()
204 {
205 cbupper = callback(&UPPER,12)
206 cblower = callback(&LOWER,12)
207 }
208
209 method int sqlite3.open(str db_name)
210 {
211 int iRetData = 1
212 if (!this.db)
213 {
214 ustr us = db_name
215 str s
216 us.toutf8( s )
217 this.rc = sqlite3_open(s.ptr(), &this.db)
218 if( this.rc != $SQLITE_OK )
219 {
220 this.error_message.copy(sqlite3_errmsg(this.db))
221 sqlite3_close(this.db)
222 iRetData = 0
223 }
224 //print( "upper \(cbupper)\n" )
225 //sqlite3_create_function(this.db,"LOWER".ptr(),1,1,0,cblower,0,0)
226 //sqlite3_create_function(this.db,"zzz".ptr(),1,1,0,cbupper,0,0)
227 //this.error_message.copy(sqlite3_errmsg(this.db))
228 //print( this.error_message )
229 }
230 else : this.error_message= "A database is already open"
231 return iRetData;
232 }
233
234 /*-----------------------------------------------------------------------------
235 * @syntax [ sql3.close() ]
236 *
237 * Closes the currently open database.
238 -----------------------------------------------------------------------------*/
239 method sqlite3.close()
240 {
241 if (this.db)
242 {
243 sqlite3_close(this.db)
244 this.db = 0
245 }
246 }
247
248 /*-----------------------------------------------------------------------------
249 * @syntax [ sql3.sql_execute(str sql_code) ]
250 *
251 * @param <sql_code> The SQL statement.
252 *
253 * Executes the SQL statement in <sql_code>. For 'select' statements an array
254 * of the result set is returned (col_val)
255 -----------------------------------------------------------------------------*/
256 method int sqlite3.sql_execute(str sql_code)
257 {
258 uint tail_ptr,num_cols=0,ich=0
259 int done=0,result1 = 0,retCode=0
260
261 int iErr = sqlite3_prepare_v2(this.db,sql_code.ptr(),*sql_code,&this.compiled_sql_ptr,0) //&tail_ptr
262 if (iErr == $SQLITE_OK)
263 {
264 this.col_val.clear()
265 num_cols = sqlite3_column_count(this.compiled_sql_ptr)
266 this.getColumnNames(num_cols)
267 while (!done)
268 {
269 /*uint j
270 fornum j=0, 2 * 30 * 4453
271 {
272 //fornum i=0,5//num_cols
273 {
274 //sqlite3_column_type(this.compiled_sql_ptr, 0)
275 sqlite3_column_count(this.compiled_sql_ptr)
276 }
277 print( "j = \(j)\n" )
278 }
279 print( "eee" )
280 getch()*/
281
282 result1 = sqlite3_step(this.compiled_sql_ptr)
283 uint i
284 /*print( "z1\n" )
285 fornum i=0, 1000 :sqlite3_column_type(this.compiled_sql_ptr,0)
286 print( "z2\n" )
287 getch()*/
288 if (result1 == $SQLITE_ROW) {
289
290 this.getColumnsType(num_cols)
291 //this.getColumnsType(num_cols)
292 this.getColumnsValue(num_cols,ich)
293
294 //if ftest && (ftest - 370 <= ich) : getch()
295
296 }
297 else : done = 1
298 ich+=1
299 //getch()*/
300 }
301 if(result1 == $SQLITE_DONE )
302 {
303 retCode=1
304 }
305 sqlite3_finalize(this.compiled_sql_ptr)
306
307 }
308 return retCode
309 }
310 global { uint x }
311 method sqlite3.getColumnsValue(uint num_columns,uint uLich)
312 {
313 uint i
314 this.col_val.expand(1)
315 this.col_val[*this.col_val-1].values.expand(num_columns)
316 fornum i=0, num_columns
317 {
318 str sqarray// = "sss"
319
320 switch (this.col_types[i])
321 {
322 case $SQLITE_INTEGER
323 {
324 // sqarray = "\(*this.col_val-1)"
325 uint int_ptr = sqlite3_column_text(this.compiled_sql_ptr,i)
326 if (int_ptr): sqarray.copy(int_ptr)
327 }
328 /* case $SQLITE_FLOAT
329 {
330 uint float_ptr = sqlite3_column_text(this.compiled_sql_ptr,i)
331 if (float_ptr) : sqarray.copy(float_ptr)
332 }*/
333 case $SQLITE_TEXT
334 {
335 uint text_ptr = sqlite3_column_text(this.compiled_sql_ptr,i)
336 uint err
337 if (text_ptr) : sqarray.copy(text_ptr)
338 /*if (err = sqlite3_errcode(this.db)) && err !=100
339 {
340 print("error \(sqarray) \(err)\n")
341 //getch()
342 } */
343 /*fornum i = 0 , 100000
344 {
345 text_ptr = sqlite3_column_text(this.compiled_sql_ptr,i)
346 uint err
347 if err = sqlite3_errcode(this.db)
348 {
349 print("error \(i) \(err)")
350 getch()
351 }
352 }
353 //text_ptr = sqlite3_column_text(this.compiled_sql_ptr,i)
354 x++
355 if ( x > 4818 )
356 {
357 getch()
358 print( "\(x)\n" )
359 }*/
360
361 }
362 /*case $SQLITE_BLOB
363 {
364 uint blob_ptr = sqlite3_column_blob(this.compiled_sql_ptr,i)
365 uint blob_len = sqlite3_column_bytes(this.compiled_sql_ptr,i)
366 if (blob_ptr) : sqarray.load(blob_ptr,blob_len)
367 }*/
368 case $SQLITE_NULL : sqarray=""
369 }
370 //print( "add \(*this.col_val-1) \(i) = \(sqarray)\n" )
371 this.col_val[*this.col_val-1].values[i] = sqarray
372 }
373 }
374
375 method sqlite3.getColumnNames(uint num_columns)
376 {
377 uint i
378 str sColName
379
380 this.col_names.clear()
381 for i=0,i < num_columns,i++
382 {
383 sColName.copy(sqlite3_column_name(this.compiled_sql_ptr, i))
384 this.col_names += sColName
385 sColName.clear()
386 }
387 }
388
389 method sqlite3.getColumnsType(uint num_columns)
390 {
391 uint i
392 // print( "ct1 \(*this.col_types) \(this.col_types.use)\n" )
393 this.col_types.clear()
394 //print( "ct2 \(*this.col_types) \(this.col_types.use)\n" )
395 if (num_columns > 0) {
396 this.col_types.expand( num_columns )
397 for i=0,i < num_columns,i++
398 {
399 //uint itypeCol = sqlite3_column_type(this.compiled_sql_ptr, i)
400 //this.col_types += str(itypeCol)
401 this.col_types[i] = $SQLITE_TEXT//itypeCol
402 }
403 }
404 }
405
406 /*-----------------------------------------------------------------------------
407 * @syntax [ sql3.rowid() ]
408 *
409 * @return The last row id from last 'insert'.
410 -----------------------------------------------------------------------------*/
411 method uint sqlite3.rowid()
412 {
413 if this.db : return sqlite3_last_insert_rowid(this.db)
414 else : return 0
415 }
416
417 /*-----------------------------------------------------------------------------
418 * @syntax [ sql3.tables(arrstr arrTables) ]
419 *
420 * @return A array of tables names in the database.
421 -----------------------------------------------------------------------------*/
422 method uint sqlite3.tables(arrstr arrTables)
423 {
424 uint uretData = 0
425 this.col_val.clear()
426 if this.db
427 {
428 uretData=this.sql_execute("select tbl_name from sqlite_master;")
429 uint i
430 fornum i=0,*this.col_val : arrTables+=this.col_val[i].values[0]
431 }
432 return uretData
433 }
434
435 /*-----------------------------------------------------------------------------
436 * @syntax [ sql3.columns(str aTableName,arrstr asColumns) ]
437 *
438 * @return A arrstr of column names for a table.
439 -----------------------------------------------------------------------------*/
440 method uint sqlite3.columns(str aTableName, arrstr asColumns)
441 {
442 str sql_text = "select * from "+aTableName+" where 0;"
443 uint uretData = 0
444 if this.db {
445 uretData=this.sql_execute(sql_text)
446 asColumns = this.col_names
447 }
448 return uretData
449 }
450
451 /*-----------------------------------------------------------------------------
452 * @syntax [ sql3.change() ]
453 *
454 * @return The Number of rows changed/affected by the last SQL statement.
455 -----------------------------------------------------------------------------*/
456 method uint sqlite3.changes()
457 {
458 if this.db : return sqlite3_changes(this.db)
459 else : return 0
460 }
461
462 /*-----------------------------------------------------------------------------
463 * @syntax [ sql3.rollbackTrans() ]
464 *
465 * @return RollBack changed data
466 -----------------------------------------------------------------------------*/
467 method int sqlite3.rollbackTrans()
468 {
469 uint uretData = 0
470 if this.db {
471 this.transStarted = 0
472 uretData=this.sql_execute("ROLLBACK")
473 }
474 return uretData
475 }
476
477 /*-----------------------------------------------------------------------------
478 * @syntax [ sql3.beginTrans() ]
479 *
480 * @return Start transaction for current data
481 -----------------------------------------------------------------------------*/
482 method int sqlite3.beginTrans()
483 {
484 uint uretData = 0
485 if this.db {
486 this.transStarted = 1
487 uretData=this.sql_execute("BEGIN")
488 }
489 return uretData
490 }
491
492 /*-----------------------------------------------------------------------------
493 * @syntax [ sql3.commitTrans() ]
494 *
495 * @return set changed data in the current database
496 -----------------------------------------------------------------------------*/
497 method int sqlite3.commitTrans()
498 {
499 uint uretData = 0
500 if this.db {
501 this.transStarted = 0
502 uretData=this.sql_execute("COMMIT")
503 }
504 return uretData
505 }
506
507 /*-----------------------------------------------------------------------------
508 * @syntax [ sql3.vacuumData() ]
509 *
510 * @return Pack database space after drop (delete) data from current database
511 -----------------------------------------------------------------------------*/
512 method int sqlite3.vacuumData()
513 {
514 uint uretData = 0
515 if this.db {
516 uretData=this.sql_execute("VACUUM")
517 }
518 return uretData
519 }
520
521 /*-----------------------------------------------------------------------------
522 * @syntax [ sql3.columnsCount() ]
523 *
524 * @return Return count of columns after sql query for current database
525 -----------------------------------------------------------------------------*/
526 method int sqlite3.columnsCount() : return *this.col_names
527
528 /*-----------------------------------------------------------------------------
529 * @syntax [ sql3.recordsCount() ]
530 *
531 * @return Return count of records after sql query for current database
532 -----------------------------------------------------------------------------*/
533 method int sqlite3.recordsCount() : return *this.col_val
534
535 /*-----------------------------------------------------------------------------
536 * @syntax [ sql3.allIndexes(arrstr arrIndexes)]
537 *
538 * @return A array of indexes names in the database.
539 -----------------------------------------------------------------------------*/
540 method uint sqlite3.allIndexes(arrstr arrIndexes)
541 {
542 uint uretData = 0
543 this.col_val.clear()
544 if this.db
545 {
546 uretData=this.sql_execute("select name from sqlite_master where type = 'index';")
547 uint i
548 fornum i=0,*this.col_val : arrIndexes+=this.col_val[i].values[0]
549 }
550 return uretData
551 }
552
553 /*-----------------------------------------------------------------------------
554 * @syntax [ sql3.indexesOfTable(str sTable,arrstr arrIndOfTable)]
555 *
556 * @return A array of indexes names in the given table.
557 -----------------------------------------------------------------------------*/
558 method uint sqlite3.indexesOfTable(str sTable, arrstr arrIndOfTable)
559 {
560 uint uretData = 0
561 this.col_val.clear()
562 if this.db
563 {
564 uretData=this.sql_execute("select name from sqlite_master where type = 'index' and tbl_name='+sTable+';")
565 uint i
566 fornum i=0,*this.col_val : arrIndOfTable+=this.col_val[i].values[0]
567 }
568 return uretData
569 }
570
571 /*-----------------------------------------------------------------------------
572 * @syntax [ sql3.allViews(arrstr arrViews)]
573 *
574 * @return A array of indexes names in the database.
575 -----------------------------------------------------------------------------*/
576 method uint sqlite3.allViews(arrstr arrViews)
577 {
578 uint uretData = 0
579 this.col_val.clear()
580 if this.db
581 {
582 uretData=this.sql_execute("select name from sqlite_master where type = 'view';")
583 uint i
584 fornum i=0,*this.col_val : arrViews+=this.col_val[i].values[0]
585 }
586 return uretData
587 }
588
589 /*-----------------------------------------------------------------------------
590 * @syntax [ sql3.columnName(int iColumnIndex) ]
591 *
592 * @return A array of indexes names in the database.
593 -----------------------------------------------------------------------------*/
594 method str sqlite3.columnName(int iColumnIndex) : return this.col_names[iColumnIndex]
595
596