English  

   ..

   read.me

   sqlite3.def

   sqlite3.g

source\lib\sqlite3\sqlite3.g
  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