RE: [sqlite] vxworks

[Mr. Tezozomoc]

We have been successfully been using sqlite in several flavors of Vxworks.

I have ported it over to PPC604, X86, and PC_SIM.

We had to do some kludging to get it to work concurrently and coherently.

1.  We modified os.c and added a semaphore for the singleton file.
2.  We wrote our own arbitration handle between processes so, if one 
data base is common across tasks, it needs arbitration and separate handles 
to it.
3.  If you plan on using in memory databases we switched them over to a ram 
drive and it the rest of the arbitration works just fine.


We have used this port successfully across several projects.

Please let me know if this helps.

Tezozomoc.


Original Message Follows
From: Martin Pfeifle <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: [sqlite] vxworks
Date: Wed, 12 Oct 2005 15:01:32 +0200 (CEST)

Hi,
I am in deep trouble. I would like to use sqlite on
vxworks. There are no fysnc, fcntl calls available
which are used in os_unix.c.
Can anybody help me? PLEASE!







___
Gesendet von Yahoo! Mail - Jetzt mit 1GB Speicher kostenlos - Hier anmelden: 
http://mail.yahoo.de

Re: [sqlite] preventing text to integer conversion of bind variables in perl

[Mr. Tezozomoc]

Please verify that they have implemented a 'natural sort' vs. a 'lexical 
sort'


Tezo.


Original Message Follows
From: Darren Duncan <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] preventing text to integer conversion of bind 
variables in perl

Date: Wed, 15 Jun 2005 18:12:26 -0700

At 12:55 AM + 6/16/05, Mr. Tezozomoc wrote:

sqlite is typeless
I have addressed this issue in the following HOWTO:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg03205.html
Please refer to it.
Tezozomoc.


I think not.


From your own HOWTO:


  Assumption... this is based on SQLITE 2.8.6... forgive the aging..

I believe that this is out of date and not applicable.

The SQLite 3.x line is not typeless and has distinct numerical and text and 
binary data types.


Therefore it should be able to take columns declared as numbers and sort 
them as such.


-- Darren Duncan

Re: [sqlite] preventing text to integer conversion of bind variables in perl

[Mr. Tezozomoc]

sqlite is typeless

I have addressed this issue in the following HOWTO:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg03205.html

Please refer to it.

Tezozomoc.


Original Message Follows
From: Darren Duncan <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org, Matt Sergeant <[EMAIL PROTECTED]>
Subject: Re: [sqlite] preventing text to integer conversion of bind 
variables in perl

Date: Wed, 15 Jun 2005 17:45:44 -0700

At 11:39 AM -0400 6/15/05, Matt Sergeant wrote:
I added it because of another bug report that was incorrectly sorting 
integer columns based on text sort order. For example if you inserted:


 ("k1", 8);
 ("k2", 9);
 ("k3", 10);
 ("k4", 11);

and then asked for: SELECT * FROM t ORDER BY Column2
you get back:

  k3, 10
  k4, 11
  k1, 8
  k2, 9

Which seems obviously incorrect.

In answer to your question though, yes you can remove that bit of code, as 
long as you're aware of the above side effect.


Well, if Column2 is explicitly defined as an integer, then it should always 
sort as an integer, and making that work is the responsibility of SQLite 
itself.


I see that preserving the input data in all situations where the declared 
column type can handle it is of the utmost importance, and DBD::SQLite 
should do this.


So I vote to remove any de-stringification code you have in DBD::SQLite.

To be honest I'm not entirely sure what the correct fix is - maybe ignore 
the above bug and tell the requestor he has to: SELECT * FROM t ORDER BY 
int(Column2)


Yes, go ahead and do that.  Alternately, tell the person to use SQLite in 
strict mode so that it only ever stores ints in int columns.  Like most 
databases do.


Matt, I would also appreciate it if a new DBD::SQLite was released asap that 
embeds and is known to work well with the 3.2 series, specifically 3.2.2.


Thank you in advance.

-- Darren Duncan

[sqlite] Assert with sql 2.6.8....

[Mr. Tezozomoc]

I am getting this assert when I call my sqlite from a c++ program...
i have if def for c extern all of the files and everything compiles...
but I get this error...
main.c
Assert (iDb>=0 && iDbnDb)failed in file ../../sqlite/src/build.c at 
line 2154

Anybody have any ideas... what my problem is.
Tezozomoc.

RE: [sqlite] Any successful VxWorks ports out there?

[Mr. Tezozomoc]

Does this mean that the in memory database can be accessed by more than one 
task without giving schema corruption?
This is the problem I was having the 2.8.x... version...

Tezo
Original Message Follows
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Any successful VxWorks ports out there?
Date: Wed, 02 Mar 2005 15:13:18 -0500
On Wed, 2005-03-02 at 19:35 +0000, Mr. Tezozomoc wrote:
> I have successfully port 2.8.15 to vxworks
> The in memory database is very problematic
>
Note that the in-memory database for version 3.x is
completely new and uses a different approach than the
in-memory database in version 2.8.x.  So whatever the
problems were that Mr. Tezozomoc had, they may well
be gone now.
--
D. Richard Hipp <[EMAIL PROTECTED]>

RE: [sqlite] Any successful VxWorks ports out there?

[Mr. Tezozomoc]

I have successfully port 2.8.15 to vxworks
I have ported over to PPC604 and PENTIUM
The main catch here is the ... that there is no file locking in vxworks
This is the major problem.
You have to write your own wrapper to prevent multiple tasks accessing the 
database...

I wrote my own busy handler that pends on the database when it is busy and 
throws 1 task delay...

On a pentium 200 MHZ I was able to achieve extremely fast responses using 
pre-compile queries.

Another big gotcha... is that sqlite uses a lot of Mallocs... this will 
cause problems in VXworks... eventually... the memory becomes fragmented and 
it cause problems... so I switched to using memory pools... this reduced 
this problem...

The pure 'c' version is very easy to compile.
The in memory database is very problematic
If you want to do an in memory database and are going to access it with more 
than one task, i recommend the following

I implemented a ramdrive and trick sqlite into thinking it is running from a 
real drive...

This will give the best of both worlds without lossing to much 
performance

Let me know if you need any more info...
Tezo.
Original Message Follows
From: "Andrew Lukasik" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: "'sqlite-users@sqlite.org'" 
Subject: [sqlite] Any successful VxWorks ports out there?
Date: Wed, 2 Mar 2005 14:19:49 -0500
I'm currently using SQLite on Linux but I'm considering a port to VxWorks.
Has anyone done this successfully?  Care to comment? I took a look thru the
archives, there wasn't a whole lot there. I'd be interested in knowing:
a) how difficult/lengthy was the port
b) pitfalls/got-chas
c) performance - does sqlite perform as well here as other known OS's?
d) eperience w/ in-memory databases on VxWorks?
e) other db alternatives you rejected, and why.
Thanks,
/Andrew
Andrew J. Lukasik
Nortel Networks
Phone:(978)-288-6861, ESN 248-6861
Email: [EMAIL PROTECTED]

RE: [sqlite] Best way to check for existence of a table?

[Mr. Tezozomoc]

Try something like .
dba_int_t  dba_check_table_exist (dba_void_t *hndArg, dba_char_t *table)
{
/**/
/* Local Variables:  
 */
/**/
   dba_handle_t*pHnd = (dba_handle_t *)hndArg;
   dba_status_tstatus = DBA_TABLE_NOT_EXIST;
   dba_result_tres, *pResult=
   dba_char_t  statement[200];
   dba_char_t  *pErrMsg = NULL;
   dba_int_t   rc = 0;
   dba_int_t   time_delays = 0;

/**/
/* Function Body:
 */
/**/

   if ( (pHnd != NULL) && (pHnd->magic == DBA_HANDLE_MAGIC) )
   {
   /* init sqlite database */
   if( ( pHnd->db != NULL ) && (statement != NULL) )
   {
   pResult->result_table = NULL;
   pResult->numrows = 0;
   pResult->numcols = 0;
   sprintf(statement,
   "SELECT name FROM sqlite_master WHERE type='table' and 
name='%s'",
   table);
   do {
   rc = sqlite_get_table(pHnd->db,
 statement,
 >result_table,
 >numrows,
 >numcols,
 );
   if ((rc==SQLITE_BUSY)||(rc==SQLITE_LOCKED)){
#ifdef _VXWORKS
   taskDelay(DBA_DB_WAIT_TIME);
#endif
   if (time_delays > DBA_DB_WAIT_MAX){
   /* it looks like there might be a problem with db */
   /* its time to bail */
   rc = SQLITE_ERROR;
   break;
   }
   time_delays++;
   if(dba_debug){
#ifdef _VXWORKS
   logMsg("%s: # of Delays 
%d\n",__FUNCTION__,time_delays,0,0,0,0);
#endif
   }

   }
   } while((rc==SQLITE_BUSY)||(rc==SQLITE_LOCKED));
   if (pErrMsg != NULL)
   {
   if(dba_debug)
   printf("dba_exec: %s\n", pErrMsg);
   dba_free(pErrMsg);
   }
   if ( rc == SQLITE_OK )
   {
   if(pResult->numrows == 1){
   status = DBA_TABLE_EXIST;
   }
   else {
   status = DBA_TABLE_NOT_EXIST;
   }
   if(dba_debug){
   printf("dba_exec: Exists =%s\n", table);
   }
   }
   /* free if any rows */
   dba_free_table (pResult);
   } /* if( ( pHnd->db != NULL ) && (statement != NULL) ) */
   } /* ( (pHnd != NULL) && (pHnd->magic == DBA_HANDLE_MAGIC) ) */
   if (status == DBA_TABLE_EXIST){
   return (status);
   }
   else {
   return (rc);
   }
  /* return (status);*/
} /* end of dba_check_table_exist */
Original Message Follows
From: [EMAIL PROTECTED]
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Best way to check for existence of a table?
Date: Mon, 14 Feb 2005 15:35:53 -0500


did you try:
SELECT NULL FROM sqlite_master WHERE tbl_name = 'table';
Regards,
[EMAIL PROTECTED]
NCCI
Boca Raton, Florida
561.893.2415
greetings / avec mes meilleures salutations / Cordialmente
mit freundlichen Grüßen / Med vänlig hälsning

  "Richard Boyd"
  <[EMAIL PROTECTED]To:   

  .com>cc:
   Subject:  RE: [sqlite] Best 
way to check for existence of a table?
  02/14/2005 03:05
  PM
  Please respond to
  sqlite-users



Thanks for the prompt reply...
I tried what you suggested and I always get the error message:
"SQL error: no such column: table32"
Whether the table exists or not, I always get returned value of 1 from
sqlite3_exec().
The exact command that I use is:
SELECT count(*) FROM sqlite_master WHERE name=table32 AND type='table';
I also tried single quotes around the table32 name:
SELECT count(*) FROM sqlite_master WHERE name='table32' AND type='table';
And get no errors whether the table exists or not
When I try the other method suggested ("SELECT NULL FROM sqlite_master
WHERE
tbl_name = 'your-table';") I don’t get any error messages whether the 
table
exists or not. The return value is always 0.

I'm obviously missing where the error is being flagged, have you any more
pointers?
Sorry if I'm being dense here but I'm new to SQL databases.
Thanks again,
Richard.

-Original Message-
From: D. 

[sqlite] HOWTO: Overcome the single threadness of the in-memory database...

[Mr Tezozomoc]

There is a systemic problem with sqlite and multi-thread access to the in memory 
database.

1.  Create a RAM Drive. [OS specific, see your development software.]
2.  Point database to the RAM Drive.  You should not suffer too much speed degredation 
from the RAM Drive.  
3.  This will give you all the locking capabilities of the file system and over come 
single threadness.

Tezozomoc.

Re: [sqlite] .db file size is not changing

[tezozomoc]

Use VACUUM.

tezo.

- Original Message - 
From: "Unnikrishnan Nair" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 17, 2004 7:56 AM
Subject: [sqlite] .db file size is not changing


> Hi all,
> 
> I have a sqlite database. I had thousands of rows in
> it. I did a test with the database and everything was
> good. So before I ship it to production, I went to the
> database and I did drop all the index and 'deleted'
> all the rows from the table. I thought the size of the
> database will come down so that I can ship the model
> database for production. But the size didn't change.
> How can I reduce the size or is it possible at all?
> 
> Thanks.
> Unni
> 
> 
> 
> __
> Do you Yahoo!?
> Yahoo! Mail Address AutoComplete - You start. We finish.
> http://promotions.yahoo.com/new_mail 
> 

Re: [sqlite] Deadlock when doing threaded updates and inserts

[tezozomoc]

Good point...

I have several different tasks(vxworks)

Perhaps, this is why its working well for me...

Tezo.

- Original Message - 
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 4:30 PM
Subject: Re: [sqlite] Deadlock when doing threaded updates and inserts


> On Aug 11, 2004, at 4:05 PM, tezozomoc wrote:
> 
> > I have solved this problem by writing a wrappers around sql_exec and
> > sql_query, sql_step, etc...
> > In these wrappers I handle the waiting for busy and the lock file 
> > issue...
> 
> I was doing the same, calling usleep() whenever I got a SQLITE_BUSY 
> return and trying the command again, but it doesn't help in the case 
> where two threads are both in a transaction and trying to write..
> 
> -D
> 
> 

Re: [sqlite] Deadlock when doing threaded updates and inserts

[tezozomoc]

I have solved this problem by writing a wrappers around sql_exec and
sql_query, sql_step, etc...
In these wrappers I handle the waiting for busy and the lock file issue...

It is not elegant but it allowed me to preserve the interface the same way
without having to do it at the application level.

Tezozomoc.

- Original Message - 
From: "Dave Hayden" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, August 11, 2004 3:48 PM
Subject: Re: [sqlite] Deadlock when doing threaded updates and inserts


> On Aug 11, 2004, at 6:49 AM, D. Richard Hipp wrote:
>
> > Oops.  The db1 should do a ROLLBACK, not a COMMIT.  Or db2 can
> > do an END TRANSACTION (since it never made any changes) and allow
> > db1 to complete instead.  The point is that when two threads or
> > processes are trying to write at the same time, one of the two
> > must back off, abandon their transaction (using ROLLBACK) and let
> > the other proceed.
>
> Wow. That adds a whole lot of complexity to my code. Every transaction
> would be inside a loop that checks for a busy return from any statement
> within. And most of the places I'm using a transaction, I'm doing a few
> hundred inserts or updates from a number of different functions.
>
> This really is something I'd expect to run under the hood. Since only
> one of the competing threads will have completed a write (right?),
> can't the others "postpone" their transactions somehow until they can
> get a write lock?
>
> For now, I've solved the problem by adding my own locks to exclude
> simultaneous transactions on the same database file. I'm only using
> transactions for writes (is there any reason for a read-only
> transaction?) so if there's no way to resolve two opened write
> transactions, you shouldn't be able to open two in the first place.
>
> Please let me know if there's something I'm missing here..
>
> Thanks,
> -Dave
>
>

Re: [sqlite] Row Count

[tezozomoc]

BlankConsider doing a 

SELECT COUNT (*) FROM db WHERE etc

This should give you the number of rows before you... do the select again... Use where 
to match the condition.


Tezo.
  - Original Message - 
  From: Drew, Stephen 
  To: '[EMAIL PROTECTED]' 
  Sent: Wednesday, August 11, 2004 4:59 AM
  Subject: [sqlite] Row Count


  Hello,

  Is there any way to obtain the number of rows returned by a SELECT statement, before 
receiving all the rows returned?  I am using the sqlite_exec() / sqlite_step() 
functions to return the data, but need to populate a progress bar.

  Regards,
  Stephen Drew 

  DISCLAIMER: This e-mail and any files transmitted with it contain confidential and 
proprietary information and is intended solely for the use of the intended recipient. 
If you are not the intended recipient, please return the e-mail to the sender and 
delete it from your computer and you must not use, disclose, distribute, copy, print 
or rely on this e-mail. The contents of this e-mail and any files transmitted with it 
may have been changed or altered without the consent of the author. Email transmission 
cannot be guaranteed to be secure or error-free. 

Re: [sqlite] HOWTO: Adding Natural Sort To Sqlite Database...

[Mr Tezozomoc]

The current implementation of SQlite resorts  strcmp which does a
lexicographical compare...

for example consider the following items...
Field NAME
GPI_1
GPI_2
GPI_20
GPI_10

if I run the following sql statement SELECT * FROM list ORDER BY NAME
I will get the following data sorted:
GPI_1
GPI_10
GPI_2
GPI_20

Which is not really what I meant.

If you patch sqlite for natural sort the same code will yield the following:
GPI_1
GPI_2
GPI_10
GPI_20

This is what's called a  natural sort.. its what we are used to as non
computers...

Tezozomoc.
- Original Message -
From: "gohaku" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "tezozomoc" <[EMAIL PROTECTED]>
Sent: Saturday, July 31, 2004 6:20 AM
Subject: Re: [sqlite] HOWTO: Adding Natural Sort To Sqlite Database...


>
> On Jul 30, 2004, at 1:49 PM, tezozomoc wrote:
>
> >
> > HOWTO: Adding Natural Sort To Sqlite Database...
> >
> > Assumption... this is based on SQLITE 2.8.6... forgive the aging..
> >
> >
> > I will make this very short and sweet...
> >
> > 1.  Get the following two files:
> >
> > a.. strnatcmp.c, strnatcmp.h - the algorithm itself from
> > a..
> > http://sourcefrog.net/projects/natsort/
> >
> > add to your sqlite build project, I am using VxWorks, so the way that
> > I did it will probably will not make sense...
> >
> > 2.  Open util.c
> >
> > 3.  Replace the following:
> > 
>
> This sounds great.  can you give an SQL Statement example and explain
> what was wrong with sqliteSortCompare?
>
> Thanks.
>
>

[sqlite] HOWTO: Adding Natural Sort To Sqlite Database...

[tezozomoc]

HOWTO: Adding Natural Sort To Sqlite Database...

Assumption... this is based on SQLITE 2.8.6... forgive the aging..


I will make this very short and sweet... 

1.  Get the following two files:

a.. strnatcmp.c, strnatcmp.h - the algorithm itself from 
a.. 
http://sourcefrog.net/projects/natsort/

add to your sqlite build project, I am using VxWorks, so the way that I did it will 
probably will not make sense...

2.  Open util.c

3.  Replace the following: 
  **
  ** $Id: util.c,v 1.66 2003/07/30 12:34:12 drh Exp $
  */
  #include "sqliteInt.h"
  #include 
  #include 

 with 

  ** $Id: util.c,v 1.66 2003/07/30 12:34:12 drh Exp $
  */
  #include "sqliteInt.h"
  #include 
  #include 
  #include "strnatcmp.h"

4.  Find the following function sqliteSortCompare(...)

Replace

  assert( a[0]==b[0] );
  if( (dir=a[0])=='A' || a[0]=='D' ){
   /*-->*/res = strcmp([1],[1]);
if( res ) break;
  }else{
isNumA = sqliteIsNumber([1]);
isNumB = sqliteIsNumber([1]);
if( isNumA ){
  double rA, rB;
  if( !isNumB ){
res = -1;
break;
  }
  rA = atof([1]);
  rB = atof([1]);
  if( rArB ){
res = +1;
break;
  }
}else if( isNumB ){
  res = +1;
  break;
}else{
/*-->*/res = strcmp([1],[1]);
  if( res ) break;
}
  }

with 

  assert( a[0]==b[0] );
  if( (dir=a[0])=='A' || a[0]=='D' ){
/*res = strcmp([1],[1]);*/
res = strnatcmp([1],[1]);
if( res ) break;
  }else{
isNumA = sqliteIsNumber([1]);
isNumB = sqliteIsNumber([1]);
if( isNumA ){
  double rA, rB;
  if( !isNumB ){
res = -1;
break;
  }
  rA = atof([1]);
  rB = atof([1]);
  if( rArB ){
res = +1;
break;
  }
}else if( isNumB ){
  res = +1;
  break;
}else{
/*res = strcmp([1],[1]);*/
res = strnatcmp([1],[1]);
  if( res ) break;
}
  }

This has made my SQLITE experience much better... no need for COLLATE Function .

by
Tezozomoc.