Re: [sqlite] About extension-functions.c

[Stephan Beal]

On Wed, Sep 14, 2011 at 6:06 AM, Freddy López wrote:

> I would like to know if you can help me with it. My knowledge of C is poor.
>

We can't possibly help until we know what the problem is. Can you please
post the compiler output?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite on AIX

[Grice, Lynton (L)]

Hi there,

I will need to get SQLite working soon on AIX. I have read the compile notes 
from 2003 at http://www.sqlite.org/cvstrac/wiki?p=HowToCompile but was just 
wondering if anyone has it running happily on AIX with the newer versions of 
SQLIte?

Any hints or suggestions from people who have it compiled and working 100% on 
AIX?

I will try install GCC on AIX for the compile

Thanks

Lynton



NOTICE: Please note that this eMail, and the contents thereof, 
is subject to the standard Sasol eMail legal notice which may be found at: 
http://www.sasol.com/legalnotices   
   

If you cannot access the legal notice through the URL attached and you wish 
to receive a copy thereof please send an eMail to 
legalnot...@sasol.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] About extension-functions.c

[Freddy López]

Hello.

I have recently downloaded extension-functions.c file (
http://pastebin.com/QF6WbrW5) and I have used the command:

gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so

such as compile instruction said but I don't get good results.

I'm now under Linux Mint 11 64bits and I remember that when I was under LM
10 32bits that instruction compiled it successfully.

I would like to know if you can help me with it. My knowledge of C is poor.

Thanks in advance.

-- 
«But Gwindor answered: 'The doom lies in yourself, not in your name.'»

JRR Tolkien
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Jay A. Kreibich]

On Tue, Sep 13, 2011 at 09:16:32PM +0100, Simon Slavin scratched on the wall:
> 
> On 13 Sep 2011, at 7:22pm, Petite Abeille wrote:
> 
> > On Sep 13, 2011, at 2:15 PM, Simon Slavin wrote:
> > 
> >> It's about chunkiness, and which of the values you're looking for.
> > 
> > Chunkiness? Surely you mean selectivity, no?
> 
> I'm sorry, but I've failed to find a better word.

  Clumpy, not chunky.  8-)

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Simon Slavin]

On 13 Sep 2011, at 7:22pm, Petite Abeille wrote:

> On Sep 13, 2011, at 2:15 PM, Simon Slavin wrote:
> 
>> It's about chunkiness, and which of the values you're looking for.
> 
> Chunkiness? Surely you mean selectivity, no?

I'm sorry, but I've failed to find a better word.  You can have a table with 
ten thousand rows.  One column of the table has a thousand different values.  
Another column of the same table has only four different values, and one of 
them appears only once.  That column is more chunky.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems

[Richard Hipp]

Another thing you might think about is to modify the membase code (it is
open-source after all) such that membase itself invokes the backup using the
sqlite3_backup() interfaces.  If the backup is being performed using the
same database connection that is doing the writes, then the backup will know
which pages of the original database file change, and only the changed pages
will have to be rewritten.  Hence, the backup doesn't need to start over
again at the beginning as it does when a write occurs because of a separate
process.

On Tue, Sep 13, 2011 at 2:39 PM, David Holtkamp  wrote:

> I was unaware of this. I thought it started to write to a Journal file
> during the backup and then persisted it afterward. However, if this
> scenario was happening, wouldn't the file size tend to jump around
> sporadically around 200Mb instead of settling into one specific size?
>

I don't follow your reasoning here.



>
> >
> > On Tue, Sep 13, 2011 at 2:29 PM, Richard Hipp  wrote:
> >> On Tue, Sep 13, 2011 at 2:23 PM, David Holtkamp 
> wrote:
> >>
> >>> Hello,
> >>> I have been using Membase (a nosql database), which uses sqlite3 to
> >>> persist data to disk and have run into some problems when attempting
> >>> to backup the database files. Membase uses 4 different database files
> >>> to store the data on each Node and in my particular case, each file is
> >>> around 800 Mb. When I issue the backup statement:
> >>>
> >>> /opt/membase/bin/sqlite3 /mnt/data-store/default-data/default-1.mb
> >>> '.backup /mnt/data-backup/mbfiles/default-1-backup.mb'
> >>>
> >>
> >> When some other process writes to the database file, the backup
> >> automatically starts over again from the beginning.  (It has to do this,
> >> since it has no way of knowing what parts of the file changed.)
>  Probably
> >> membase is writing to the database a periodic intervals that are
> >> approximately the same amount of time it takes you to copy 200MB.
> >>
> >> Is there any way to ask membase to stop persisting for long enough for
> you
> >> to complete your backup?
> >>
> >>
> >>>
> >>> The command executes and almost immediately copies 180Mb and then
> >>> comes to a near standstill. It continues in tiny spurts until it
> >>> reaches 260915200 bytes, and then the file stops growing but the
> >>> process continues to consume CPU resources. I've been trying to get a
> >>> successful backup for weeks now with no avail.
> >>>
> >>> If it matters, this system is set up on the Amazon Cloud via EC2 and
> >>> is using EBS drives. Additionally, this is a production environment so
> >>> the db is being accessed and restarts cannot be performed.
> >>>
> >>> Also here is a list of the files (I have set them and the destination
> >>> directory to 777 permissions to fix some access issues)
> >>>
> >>> drwxrwxrwx 2 membase  membase   4096 Sep 13 18:19 .
> >>> drwxrwxrwx 4 membase  root  4096 Aug  5 01:10 ..
> >>> -rwxrwxrwx 1 membase  membase  53248 Sep 13 18:19 default
> >>> -rwxrwxrwx 1 membase  membase  892068864 Sep 13 18:19 default-0.mb
> >>> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-0.mb-shm
> >>> -rwxrwxrwx 1 membase  membase1858136 Sep 13 18:19 default-0.mb-wal
> >>> -rwxrwxrwx 1 membase  membase  892246016 Sep 13 18:19 default-1.mb
> >>> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-1.mb-shm
> >>> -rwxrwxrwx 1 membase  membase1422168 Sep 13 18:19 default-1.mb-wal
> >>> -rwxrwxrwx 1 membase  membase  891885568 Sep 13 18:19 default-2.mb
> >>> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-2.mb-shm
> >>> -rwxrwxrwx 1 membase  membase1322608 Sep 13 18:19 default-2.mb-wal
> >>> -rwxrwxrwx 1 membase  membase  892113920 Sep 13 18:18 default-3.mb
> >>> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-3.mb-shm
> >>> -rwxrwxrwx 1 membase  membase1359288 Sep 13 18:19 default-3.mb-wal
> >>> -rwxrwxrwx 1 ec2-user ec2-user 0 Sep 12 17:12 default-4.mb
> >>> -rwxrwxrwx 1 ec2-user ec2-user 0 Sep 12 16:27 default.mb
> >>> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-shm
> >>> -rwxrwxrwx 1 membase  membase1083664 Sep 13 18:19 default-wal
> >>>
> >>> Any help would be greatly appropriated.
> >>> Thanks!
> >>> David
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users@sqlite.org
> >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >>
> >>
> >>
> >> --
> >> D. Richard Hipp
> >> d...@sqlite.org
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > David Holtkamp
> > Crimson Moon Entertainment LLC
> > Founder / CEO
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.

Re: [sqlite] Database schema has changed?

[Brad Stiles]

> Mmmm. Looks like there's no elegant way to do it. I looked into this a couple 
> of years ago when designing the setup. So:
>
> 1) Leave things as they are. Downside is the unexplained error every few 
> months and it's a slightly clumsy method. Upside is if the schema changes 
> there's no extra work to do.
>
> 2) Gerry suggests listing out the columns explicitly. Upside is this 
> simplifies the move operation, downside is extra maintenance. I suppose I 
> could get clever and store the column names in a Settings database I already 
> have and use that to generate the SQL.
>
> 3) Use your suggestion. Upsides as you describe - simple move. Downside is an 
> extra column with the same value in it for all rows. The irritating part is 
> that there is a unique value for each database stored in another table in the 
> same db. But it appears I can't do: PRIMARY KEY (OTHERTABLE.ORIG, ABSID) 
> which would have been nice.

4. Use one of the myriad code generators available on the web to
generate the code for this specific task.  When the schema changes,
regenerate the code.

5. Dynamically build your SQL statement based on the schema as it
exists in the copy of the database you're using.  This has the
advantage of allowing the same code to service multiple database
versions.  Reading the schema and building the query could be done
when the DB is opened and saved, perhaps even in the DB itself, rather
than doing every time the query is called.

/bs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to compare time stamp

[Brad Stiles]

>        • TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").

I personally have had the best luck with this storage mechanism.  I'm
lazy, and my platforms all understand this format readily, and it has
the advantage of being human readable as well.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems

[David Holtkamp]

I was unaware of this. I thought it started to write to a Journal file
during the backup and then persisted it afterward. However, if this
scenario was happening, wouldn't the file size tend to jump around
sporadically around 200Mb instead of settling into one specific size?

>
> On Tue, Sep 13, 2011 at 2:29 PM, Richard Hipp  wrote:
>> On Tue, Sep 13, 2011 at 2:23 PM, David Holtkamp  wrote:
>>
>>> Hello,
>>> I have been using Membase (a nosql database), which uses sqlite3 to
>>> persist data to disk and have run into some problems when attempting
>>> to backup the database files. Membase uses 4 different database files
>>> to store the data on each Node and in my particular case, each file is
>>> around 800 Mb. When I issue the backup statement:
>>>
>>> /opt/membase/bin/sqlite3 /mnt/data-store/default-data/default-1.mb
>>> '.backup /mnt/data-backup/mbfiles/default-1-backup.mb'
>>>
>>
>> When some other process writes to the database file, the backup
>> automatically starts over again from the beginning.  (It has to do this,
>> since it has no way of knowing what parts of the file changed.)  Probably
>> membase is writing to the database a periodic intervals that are
>> approximately the same amount of time it takes you to copy 200MB.
>>
>> Is there any way to ask membase to stop persisting for long enough for you
>> to complete your backup?
>>
>>
>>>
>>> The command executes and almost immediately copies 180Mb and then
>>> comes to a near standstill. It continues in tiny spurts until it
>>> reaches 260915200 bytes, and then the file stops growing but the
>>> process continues to consume CPU resources. I've been trying to get a
>>> successful backup for weeks now with no avail.
>>>
>>> If it matters, this system is set up on the Amazon Cloud via EC2 and
>>> is using EBS drives. Additionally, this is a production environment so
>>> the db is being accessed and restarts cannot be performed.
>>>
>>> Also here is a list of the files (I have set them and the destination
>>> directory to 777 permissions to fix some access issues)
>>>
>>> drwxrwxrwx 2 membase  membase       4096 Sep 13 18:19 .
>>> drwxrwxrwx 4 membase  root          4096 Aug  5 01:10 ..
>>> -rwxrwxrwx 1 membase  membase      53248 Sep 13 18:19 default
>>> -rwxrwxrwx 1 membase  membase  892068864 Sep 13 18:19 default-0.mb
>>> -rwxrwxrwx 1 membase  membase      32768 Sep 13 18:19 default-0.mb-shm
>>> -rwxrwxrwx 1 membase  membase    1858136 Sep 13 18:19 default-0.mb-wal
>>> -rwxrwxrwx 1 membase  membase  892246016 Sep 13 18:19 default-1.mb
>>> -rwxrwxrwx 1 membase  membase      32768 Sep 13 18:19 default-1.mb-shm
>>> -rwxrwxrwx 1 membase  membase    1422168 Sep 13 18:19 default-1.mb-wal
>>> -rwxrwxrwx 1 membase  membase  891885568 Sep 13 18:19 default-2.mb
>>> -rwxrwxrwx 1 membase  membase      32768 Sep 13 18:19 default-2.mb-shm
>>> -rwxrwxrwx 1 membase  membase    1322608 Sep 13 18:19 default-2.mb-wal
>>> -rwxrwxrwx 1 membase  membase  892113920 Sep 13 18:18 default-3.mb
>>> -rwxrwxrwx 1 membase  membase      32768 Sep 13 18:19 default-3.mb-shm
>>> -rwxrwxrwx 1 membase  membase    1359288 Sep 13 18:19 default-3.mb-wal
>>> -rwxrwxrwx 1 ec2-user ec2-user         0 Sep 12 17:12 default-4.mb
>>> -rwxrwxrwx 1 ec2-user ec2-user         0 Sep 12 16:27 default.mb
>>> -rwxrwxrwx 1 membase  membase      32768 Sep 13 18:19 default-shm
>>> -rwxrwxrwx 1 membase  membase    1083664 Sep 13 18:19 default-wal
>>>
>>> Any help would be greatly appropriated.
>>> Thanks!
>>> David
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> David Holtkamp
> Crimson Moon Entertainment LLC
> Founder / CEO
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Tim Streater]

On 13 Sep 2011 at 17:03, Simon Slavin  wrote: 

> On 13 Sep 2011, at 4:38pm, Tim Streater wrote:
>
>> In general the row could get moved around any number of databases and then
>> back to the original one.
>
> That's one reason I think making absid unique then manually manipulating absid
> values is a bad idea.  Labelling which system each row originated in, and
> including that column in the unique key, is enough for you to avoid all
> collisions.  Then you no longer have to worry about changing any absids: leave
> the absid exactly as it is.  Move your rows from one database file to another
> with a simple INSERT, never worrying about changing anything in the record.

Mmmm. Looks like there's no elegant way to do it. I looked into this a couple 
of years ago when designing the setup. So:

1) Leave things as they are. Downside is the unexplained error every few months 
and it's a slightly clumsy method. Upside is if the schema changes there's no 
extra work to do.

2) Gerry suggests listing out the columns explicitly. Upside is this simplifies 
the move operation, downside is extra maintenance. I suppose I could get clever 
and store the column names in a Settings database I already have and use that 
to generate the SQL.

3) Use your suggestion. Upsides as you describe - simple move. Downside is an 
extra column with the same value in it for all rows. The irritating part is 
that there is a unique value for each database stored in another table in the 
same db. But it appears I can't do: PRIMARY KEY (OTHERTABLE.ORIG, ABSID) which 
would have been nice.

> I believe the latter.  Five columns will be copied into five columns in column
> order, regardless of column names.

If that's the case I can't see where the occasional error is coming from.

Thanks for your responses.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problems

[Richard Hipp]

On Tue, Sep 13, 2011 at 2:23 PM, David Holtkamp  wrote:

> Hello,
> I have been using Membase (a nosql database), which uses sqlite3 to
> persist data to disk and have run into some problems when attempting
> to backup the database files. Membase uses 4 different database files
> to store the data on each Node and in my particular case, each file is
> around 800 Mb. When I issue the backup statement:
>
> /opt/membase/bin/sqlite3 /mnt/data-store/default-data/default-1.mb
> '.backup /mnt/data-backup/mbfiles/default-1-backup.mb'
>

When some other process writes to the database file, the backup
automatically starts over again from the beginning.  (It has to do this,
since it has no way of knowing what parts of the file changed.)  Probably
membase is writing to the database a periodic intervals that are
approximately the same amount of time it takes you to copy 200MB.

Is there any way to ask membase to stop persisting for long enough for you
to complete your backup?


>
> The command executes and almost immediately copies 180Mb and then
> comes to a near standstill. It continues in tiny spurts until it
> reaches 260915200 bytes, and then the file stops growing but the
> process continues to consume CPU resources. I've been trying to get a
> successful backup for weeks now with no avail.
>
> If it matters, this system is set up on the Amazon Cloud via EC2 and
> is using EBS drives. Additionally, this is a production environment so
> the db is being accessed and restarts cannot be performed.
>
> Also here is a list of the files (I have set them and the destination
> directory to 777 permissions to fix some access issues)
>
> drwxrwxrwx 2 membase  membase   4096 Sep 13 18:19 .
> drwxrwxrwx 4 membase  root  4096 Aug  5 01:10 ..
> -rwxrwxrwx 1 membase  membase  53248 Sep 13 18:19 default
> -rwxrwxrwx 1 membase  membase  892068864 Sep 13 18:19 default-0.mb
> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-0.mb-shm
> -rwxrwxrwx 1 membase  membase1858136 Sep 13 18:19 default-0.mb-wal
> -rwxrwxrwx 1 membase  membase  892246016 Sep 13 18:19 default-1.mb
> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-1.mb-shm
> -rwxrwxrwx 1 membase  membase1422168 Sep 13 18:19 default-1.mb-wal
> -rwxrwxrwx 1 membase  membase  891885568 Sep 13 18:19 default-2.mb
> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-2.mb-shm
> -rwxrwxrwx 1 membase  membase1322608 Sep 13 18:19 default-2.mb-wal
> -rwxrwxrwx 1 membase  membase  892113920 Sep 13 18:18 default-3.mb
> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-3.mb-shm
> -rwxrwxrwx 1 membase  membase1359288 Sep 13 18:19 default-3.mb-wal
> -rwxrwxrwx 1 ec2-user ec2-user 0 Sep 12 17:12 default-4.mb
> -rwxrwxrwx 1 ec2-user ec2-user 0 Sep 12 16:27 default.mb
> -rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-shm
> -rwxrwxrwx 1 membase  membase1083664 Sep 13 18:19 default-wal
>
> Any help would be greatly appropriated.
> Thanks!
> David
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to compare time stamp

[Petite Abeille]

On Sep 13, 2011, at 8:18 AM, Akash Agrawal wrote:

> I have table in which i have column of Date contain both *date and
> time*

Well, considering that SQLite doesn't have any "date and time" data type it's 
hard to tell...

To quote the fine manual:

SQLite does not have a storage class set aside for storing dates and/or times. 
Instead, the built-in Date And Time Functions of SQLite are capable of storing 
dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in 
Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian 
calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 
UTC.
Applications can chose to store dates and times in any of these formats and 
freely convert between formats using the built-in date and time functions.

http://www.sqlite.org/datatype3.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problems

[David Holtkamp]

Hello,
I have been using Membase (a nosql database), which uses sqlite3 to
persist data to disk and have run into some problems when attempting
to backup the database files. Membase uses 4 different database files
to store the data on each Node and in my particular case, each file is
around 800 Mb. When I issue the backup statement:

/opt/membase/bin/sqlite3 /mnt/data-store/default-data/default-1.mb
'.backup /mnt/data-backup/mbfiles/default-1-backup.mb'

The command executes and almost immediately copies 180Mb and then
comes to a near standstill. It continues in tiny spurts until it
reaches 260915200 bytes, and then the file stops growing but the
process continues to consume CPU resources. I've been trying to get a
successful backup for weeks now with no avail.

If it matters, this system is set up on the Amazon Cloud via EC2 and
is using EBS drives. Additionally, this is a production environment so
the db is being accessed and restarts cannot be performed.

Also here is a list of the files (I have set them and the destination
directory to 777 permissions to fix some access issues)

drwxrwxrwx 2 membase  membase   4096 Sep 13 18:19 .
drwxrwxrwx 4 membase  root  4096 Aug  5 01:10 ..
-rwxrwxrwx 1 membase  membase  53248 Sep 13 18:19 default
-rwxrwxrwx 1 membase  membase  892068864 Sep 13 18:19 default-0.mb
-rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-0.mb-shm
-rwxrwxrwx 1 membase  membase1858136 Sep 13 18:19 default-0.mb-wal
-rwxrwxrwx 1 membase  membase  892246016 Sep 13 18:19 default-1.mb
-rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-1.mb-shm
-rwxrwxrwx 1 membase  membase1422168 Sep 13 18:19 default-1.mb-wal
-rwxrwxrwx 1 membase  membase  891885568 Sep 13 18:19 default-2.mb
-rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-2.mb-shm
-rwxrwxrwx 1 membase  membase1322608 Sep 13 18:19 default-2.mb-wal
-rwxrwxrwx 1 membase  membase  892113920 Sep 13 18:18 default-3.mb
-rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-3.mb-shm
-rwxrwxrwx 1 membase  membase1359288 Sep 13 18:19 default-3.mb-wal
-rwxrwxrwx 1 ec2-user ec2-user 0 Sep 12 17:12 default-4.mb
-rwxrwxrwx 1 ec2-user ec2-user 0 Sep 12 16:27 default.mb
-rwxrwxrwx 1 membase  membase  32768 Sep 13 18:19 default-shm
-rwxrwxrwx 1 membase  membase1083664 Sep 13 18:19 default-wal

Any help would be greatly appropriated.
Thanks!
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Petite Abeille]

On Sep 13, 2011, at 2:15 PM, Simon Slavin wrote:

> It's about chunkiness, and which of the values you're looking for.

Chunkiness? Surely you mean selectivity, no?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help to get the value of file change counter

[Michael Stephenson]

Just a guess, but you may have to use one of the Pager functions to examine
the buffer for database page 1, which is always pinned in memory and which
represents the first  bytes of the database file.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Akash Agrawal
Sent: Tuesday, September 13, 2011 9:08 AM
To: General Discussion of SQLite Database; Simon Slavin
Subject: Re: [sqlite] help to get the value of file change counter

hii

thank you for suggestion.
but when reading the value from sqlite header it printing same value no
change i can see i pasting the c++ code below

/CODE***
*/
#include 
#include 
using namespace std;

int main ()
{

  FILE * pFile;
  int c;

  int n = 0;
  char fcc[35] = {0};
  int i = 0;
  int j =0;
  while(j < 4)
  {
  int n = 0;
  sleep(10);  // sleep is used so that i can make any change in
database so that value of counter will change

  pFile=fopen ("demo.sqlite","r");   //demo.sqlite is my sqlite  file

  if (pFile==NULL)
  {
  perror ("Error opening file");
  exit(-1);
  }
  else
  {
  do
  {
  c = fgetc (pFile);
  n++;

  if(n >23 && n < 28)
  {
  printf("%x",c);// printing the value of header whic in
hex format
  i++;
  }
  }while (c != EOF);
  fclose (pFile);
  cout< wrote:

>
> On 12 Sep 2011, at 1:35pm, Richard Hipp wrote:
>
> > On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik 
> > 
> wrote:
> >
> >> Simon Slavin  wrote:
> >>> 
> >>>
> >>> int sqlite3_total_changes(sqlite3*);
> >>>
> >>> My understanding (which might be wrong) is that this count 
> >>> includes all changes made by all connections to that database: not 
> >>> only changes made using your connection but also chances made by
> another
> >>> computer, process, or thread.
> >>
> >> I'm 99% sure your understanding is wrong [snip]
> >
> > Igor is right.  The sqlite3_total_changes() function only reports 
> > the
> number
> > of rows that have been changed by the same database connection that
> issued
> > the sqlite3_total_changes() call.
>
> Okay,  I find that page in the documentation ambiguous then.  Could 
> that sentence, or something like it, be added for clarity ?
>
> > There is no API for accessing the database change counter.  But you 
> > can
> read
> > it yourself by looking at bytes 24-27 of the database file.
>
> How hard would it be to implement this safely using sqlite3's own 
> filehandle to the database file ?  I know we're meant to treat 
> sqlite3* as a black box but could it be done relatively safely, with a 
> warning that it might fail under certain weird conditions ?  Would one 
> use
> sqlite3_file_control() ?
>
> Alternatively, suppose one was doing a lot of in-memory caching for a 
> SQLite database but didn't want to block other apps from accessing it.  
> Is there a clean way to say "Has anyone been messing with this but me ?" ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Akash Agrawal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Gerry Snyder]

On 9/13/2011 8:38 AM, Tim Streater wrote:

I don't see an easy way of solving conflicting absids.
The hard way of solving this is to select all the fields of the messages table 
explicitly (except absid), so I can then insert them into a new row in the 
destination table. But I'm trying to avoid this as a maintenance headache (I 
may wish to change the schema for messages from time to time).


The explicit column selection sure seems like the right way of solving 
it to me. What you are doing to avoid it is (obviously from the 
discussion) difficult and error-prone.


Yes, you will have to change the statement when the schema changes. I 
think of that as a task, not a headache.



Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encoding and Collation

[Jean-Christophe Deschamps]

> It's very easy to replace the SQLite functions with user-defined ones,
> so if someone wants to go the easy way (partial support for just the
> common western scripts) it's easy. And already done by many, if you
> search the mailing list.


It's exactly what I'm looking for. It could be my mistake but I 
searched the
list and I couldn't find it. If not asking too much, can you suggest 
better

terms to use in my search?


You can try my quick and dirty "pseudo-universal" extension for 
less-than-perfect Unicode support.


Go download the extension at 
http://dl.dropbox.com/u/26433628/unifuzz.zip.  Take the time to fully 
read the explanations at top of the source file.  Please report on my 
personal mail bugs and other issues.



--
j...@antichoc.net  


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Simon Slavin]

On 13 Sep 2011, at 4:38pm, Tim Streater wrote:

> In general the row could get moved around any number of databases and then 
> back to the original one.

That's one reason I think making absid unique then manually manipulating absid 
values is a bad idea.  Labelling which system each row originated in, and 
including that column in the unique key, is enough for you to avoid all 
collisions.  Then you no longer have to worry about changing any absids: leave 
the absid exactly as it is.  Move your rows from one database file to another 
with a simple INSERT, never worrying about changing anything in the record.

SQLite will use its own internal 'rowid' for unique row numbering and your app 
need never care about them.

> Here's a simple question. If I do this:
> 
>insert into dst.messages select * from src.messages
> 
> do the databases as represented by src and dst have to have the same schema, 
> or merely the same number of columns?

I believe the latter.  Five columns will be copied into five columns in column 
order, regardless of column names.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Tim Streater]

On 13 Sep 2011 at 13:04, Simon Slavin  wrote: 

> On 13 Sep 2011, at 12:13pm, Tim Streater wrote:
>
>>  SQLite error: general code: HY000 error: 17, database schema has changed
>>
>> and since all the rest of the time the code works fine I'm having some
>> trouble pinning down whether it's my bug (more likely) and if so where, or an
>> SQLite bug (less likely).
>
> The schema of a database is not the data in it -- values of fields, new rows
> inserted, etc. -- but things like what columns there are in a database.

Yeah, I know.

>> What I have is two databases (each is a mailbox) and I wish, from time to
>> time, to move a row from one database to the other (the schemas are the same
>> - but see below). Each row has a unique id, (defined as: absid integer
>> PRIMARY KEY - but see below) and when the row is moved, I want to allocate a
>> new absid value to the row in its new location. I have to do this to avoid
>> clashing with absid values for already-existing rows.
>
> I don't see an easy way of solving conflicting absids.

The hard way of solving this is to select all the fields of the messages table 
explicitly (except absid), so I can then insert them into a new row in the 
destination table. But I'm trying to avoid this as a maintenance headache (I 
may wish to change the schema for messages from time to time).

> Whatever strategy you
> use, there's still a chance you'll get a crash.  And you have the problem of
> each copy of the database showing different absid numbers for the same data.

This is not a problem.

> I'd like to suggest an alternative strategy:
>
> Create a new column which indicates which database the row originated in.  For
> each of those two databases, when you create a new row, set the right value
> for this 'origin' column.  Make your primary key not just 'absid' but
> '(origin,absid)'.
>
> You can still define 'absid' as 'INTEGER AUTOINCREMENT', but not 'INTEGER
> PRIMARY KEY'.  Something like
>
> CREATE TABLE myTable (orig TEXT, absid INTEGER AUTOINCREMENT, a, b, c, d,
> PRIMARY KEY (orig, absid))

In general the row could get moved around any number of databases and then back 
to the original one.

>> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
>> source database):
>>
>> $dbh->query ("attach database ':memory:' as mem");
>> $dbh->query ($create_messages);   // Create the messages table in the
>> memory database
>> $dbh->query ("attach database '" . $mailbox . "' as dst");
>> $dbh->query ("insert into mem.messages select * from main.messages where
>> absid='$absid'");// *** The failing statement ***
>> $dbh->query ("update mem.messages set absid=null");
>> $dbh->query ("insert into dst.messages select * from mem.messages");
>> $absid = $dbh->lastInsertId ();
>>
>> The only way I could find to do what I need regarding a new absid value is,
>> as above, to copy the row to a memory database, set its absid to null, and
>> then copy to the destination database. Even to do this I've had to define
>> absid in the memory database as "absid integer" rather than "absid integer
>> PRIMARY KEY". Is this the cause of the error message? If so, why does it work
>> 99.9% of the time?
>
> Two records with the same absid will violate your PRIMARY KEY contraint,
> because they break the UNIQUE requirement.  Perhaps the time they fail is when
> by coincidence both databases generate entries with the same absid.

I don't see how this can have an impact. The row is copied to an intermediate 
database (the memory one). Its absid is then set to null (in the memory 
database), so that when the second insert is done, the destination database can 
choose a new absid value.

Here's a simple question. If I do this:

insert into dst.messages select * from src.messages

do the databases as represented by src and dst have to have the same schema, or 
merely the same number of columns?

Meanwhile I think I'll refresh my understanding of INTEGER PRIMARY KEY and 
INTEGER AUTOINCREMENT.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Creating SQLite database from other databases

[Anthony Scarpelli]

I was wondering if there is a good way to create SQLite databases from either 
SQL Server or FoxPro?
 
Thanks.
 

CONFIDENTIALITY NOTICE:  This email message, including any attachments, is for 
the use of the intended recipient(s) only and may contain information that is 
privileged, confidential, and prohibited from unauthorized disclosure under 
applicable law.  If you are not the intended recipient of this message, any 
dissemination, distribution, or copying of this message is strictly prohibited. 
 If you received this message in error, please notify the sender by reply email 
and destroy all copies of the original message and attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem in cross compiling sqlite for MIPS Little endian target

[imran_sarwar]

try this

./configure --host=mips-linux-gnu CFLAGS=-EL CXXFLAGS=-EL LDFLAGS=-EL
CPPFLAGS=-EL ASFLAGS=-EL CC="mips-linux-gnu-gcc -EL"

it will work!


Navaneeth Sen B wrote:
> 
> Is there any helping suggestions on this topic..??
> 
> Regards,
> Sen
> 
> *
> *
> 
> 
> On 4/5/2010 3:04 PM, Navaneeth Sen B wrote:
>> Hello everyone,
>>
>> My aim is to build SQLite for a mips, little endian target
>>
>> I have downloaded the
>> sqlite-amalgamation-3.6.23.1.tar.gz
>> from the SQLite download page.
>> I am using the Code Sourcery gcc 4.3.2  toolchain for mips.
>>
>> I have toolchain in my path and i gave the following commands :
>>
>> host>  ./configure --prefix=/usr/local/sqlite --host=mips-linux-gnu
>> host>  make
>> host>  make install
>>
>> It was successful.
>>
>> But when i do a :
>> host>  file /usr/local/sqlite/bin/sqlite3
>>
>> It gives :
>>
>> /usr/local/sqlite/bin/sqlite3: ELF 32-bit MSB executable, MIPS, MIPS64
>> version 1, for GNU/Linux 2.6.12, dynamically linked (uses shared libs),
>> for GNU/Linux 2.6.12, not stripped
>>
>> It is giving ELF-32 MSB(The correct form should be ELF-32 LSB)
>>
>> So, I added -EL to the line CC=mips-linux-gnu-gcc in the generated
>> Makefile
>>
>> That ended up with an error
>>
>> first three lines of the error log :
>>
>> "
>> /home/sdk/mips-4.3/bin/../lib/gcc/mips-linux-gnu/4.3.2/../../../../mips-linux-gnu/bin/ld:
>> .libs/sqlite3.o: compiled for a little endian system and target is big
>> endian
>> /home/sdk/mips-4.3/bin/../lib/gcc/mips-linux-gnu/4.3.2/../../../../mips-linux-gnu/bin/ld:
>> .libs/sqlite3.o: endianness incompatible with that of the selected
>> emulation
>> /home/sdk/mips-4.3/bin/../lib/gcc/mips-linux-gnu/4.3.2/../../../../mips-linux-gnu/bin/ld:
>> failed to merge target specific data of file .libs/sqlite3.o"
>>
>> I even tried giving -EL in the CFLAGS option in the Makefile.
>> I got the same error message.
>>
>> Where am i making the mistake?
>> Please help.
>>
>> 
>> -- /
>>
>> Regards,
>> SEN
>> ///
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Problem-in-cross-compiling-sqlite-for-MIPS-Little-endian-target-tp28138377p32455822.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help to get the value of file change counter

[Akash Agrawal]

hii

thank you for suggestion.
but when reading the value from sqlite header it printing same value no
change i can see i pasting the c++ code below

/CODE/
#include 
#include 
using namespace std;

int main ()
{

  FILE * pFile;
  int c;

  int n = 0;
  char fcc[35] = {0};
  int i = 0;
  int j =0;
  while(j < 4)
  {
  int n = 0;
  sleep(10);  // sleep is used so that i can make any change in
database so that value of counter will change

  pFile=fopen ("demo.sqlite","r");   //demo.sqlite is my sqlite  file

  if (pFile==NULL)
  {
  perror ("Error opening file");
  exit(-1);
  }
  else
  {
  do
  {
  c = fgetc (pFile);
  n++;

  if(n >23 && n < 28)
  {
  printf("%x",c);// printing the value of header whic in
hex format
  i++;
  }
  }while (c != EOF);
  fclose (pFile);
  cout< wrote:

>
> On 12 Sep 2011, at 1:35pm, Richard Hipp wrote:
>
> > On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik 
> wrote:
> >
> >> Simon Slavin  wrote:
> >>> 
> >>>
> >>> int sqlite3_total_changes(sqlite3*);
> >>>
> >>> My understanding (which might be wrong) is that this count includes all
> >>> changes made by all connections to that database: not
> >>> only changes made using your connection but also chances made by
> another
> >>> computer, process, or thread.
> >>
> >> I'm 99% sure your understanding is wrong [snip]
> >
> > Igor is right.  The sqlite3_total_changes() function only reports the
> number
> > of rows that have been changed by the same database connection that
> issued
> > the sqlite3_total_changes() call.
>
> Okay,  I find that page in the documentation ambiguous then.  Could that
> sentence, or something like it, be added for clarity ?
>
> > There is no API for accessing the database change counter.  But you can
> read
> > it yourself by looking at bytes 24-27 of the database file.
>
> How hard would it be to implement this safely using sqlite3's own
> filehandle to the database file ?  I know we're meant to treat sqlite3* as a
> black box but could it be done relatively safely, with a warning that it
> might fail under certain weird conditions ?  Would one use
> sqlite3_file_control() ?
>
> Alternatively, suppose one was doing a lot of in-memory caching for a
> SQLite database but didn't want to block other apps from accessing it.  Is
> there a clean way to say "Has anyone been messing with this but me ?" ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Akash Agrawal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory strategy guide

[jerome moliere]

Sorry for any inconveniance
as I supposed the second strategy works like a charm while the first
one fails...

Thanks
J.MOLIERE - Mentor/J
auteur Eyrolles
blog: http://romjethoughts.blogspot.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Richard Hipp]

On Tue, Sep 13, 2011 at 8:21 AM, François wrote:

> Richard, Igor,
>
> I made some tests based on a) and b) cases described by Igor.
>
> I can see much faster SELECT with an index than without it: so Igor
> seems to be right. Richard, can you tell me in what cases performance
> will be less good?
>

CREATE TABLE t1(x,y,z BOOLEAN);
SELECT * FROM t1 WHERE z;

The SELECT above will generally be much faster without an index, unless most
values of z are false.


>
> Thank you,
>
> Best Regards,
>
> François
>
>
> On 13 sep, 14:12, François  wrote:
> > Thank you Igor!.
> >
> > Igor, your a) and b) cases do concern me. But reading your both
> > answers I am quite confused now :-)
> >
> > Best Regards,
> >
> > François
> >
> > On 13 sep, 14:03, "Igor Tandetnik"  wrote:
> >
> >
> >
> >
> >
> >
> >
> > > François  wrote:
> > > > Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This
> table
> > > > may contain up to 100 000 entries and those entries can be selected
> > > > using "WHERE ITEM.FLAG = ?" conditions.
> >
> > > > Is then a good or a bad practice to add an index on this field if we
> > > > want to improve SELECT time execution ?
> >
> > > This may help if and only if a) you have many more records with FLAG=1
> than with FLAG=0 (or vice versa); and b) most of the time, you are looking
> up the records belonging to the small subset. For example, if there's a
> small number of "active" or recent records that need to be processed, and a
> large archive of "processed" records.
> >
> > > However, in such a case, you might be even better off splitting the
> small subset into its own separate table.
> > > --
> > > Igor Tandetnik
> >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-us...@sqlite.orghttp://
> sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.orghttp://
> sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Igor Tandetnik]

Simon Slavin  wrote:
> On 13 Sep 2011, at 1:07pm, François wrote:
> 
>> Can we consider that creating an index for an integer field is a good
>> practice if this field has at least 3 possible values ? Or more ?
> 
> It's about chunkiness, and which of the values you're looking for.
> 
> If all possible values are equally distributed, and you frequently look for a 
> particular value, the index will help even if you
> have only two possible values.  If you have almost all '2011' rows, and 
> you're searching for '2011', then the index won't help
> much.

Actually, the break-even point is roughly 1/10: an index helps if you are 
selecting 10% or fewer of the records in the table; otherwise, a linear scan is 
faster.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[François]

Richard, Igor,

I made some tests based on a) and b) cases described by Igor.

I can see much faster SELECT with an index than without it: so Igor
seems to be right. Richard, can you tell me in what cases performance
will be less good?

Thank you,

Best Regards,

François


On 13 sep, 14:12, François  wrote:
> Thank you Igor!.
>
> Igor, your a) and b) cases do concern me. But reading your both
> answers I am quite confused now :-)
>
> Best Regards,
>
> François
>
> On 13 sep, 14:03, "Igor Tandetnik"  wrote:
>
>
>
>
>
>
>
> > François  wrote:
> > > Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table
> > > may contain up to 100 000 entries and those entries can be selected
> > > using "WHERE ITEM.FLAG = ?" conditions.
>
> > > Is then a good or a bad practice to add an index on this field if we
> > > want to improve SELECT time execution ?
>
> > This may help if and only if a) you have many more records with FLAG=1 than 
> > with FLAG=0 (or vice versa); and b) most of the time, you are looking up 
> > the records belonging to the small subset. For example, if there's a small 
> > number of "active" or recent records that need to be processed, and a large 
> > archive of "processed" records.
>
> > However, in such a case, you might be even better off splitting the small 
> > subset into its own separate table.
> > --
> > Igor Tandetnik
>
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Simon Slavin]

On 13 Sep 2011, at 1:07pm, François wrote:

> Can we consider that creating an index for an integer field is a good
> practice if this field has at least 3 possible values ? Or more ?

It's about chunkiness, and which of the values you're looking for.

If all possible values are equally distributed, and you frequently look for a 
particular value, the index will help even if you have only two possible 
values.  If you have almost all '2011' rows, and you're searching for '2011', 
then the index won't help much.

One nice thing about SQL is the command syntax doesn't change when you make or 
delete indexes.  So without having to chance your app you can write your code, 
get it working, then experiment with CREATE and DROPping indexes and see 
whether this improves speed.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[François]

Thank you Igor!.

Igor, your a) and b) cases do concern me. But reading your both
answers I am quite confused now :-)

Best Regards,

François


On 13 sep, 14:03, "Igor Tandetnik"  wrote:
> François  wrote:
> > Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table
> > may contain up to 100 000 entries and those entries can be selected
> > using "WHERE ITEM.FLAG = ?" conditions.
>
> > Is then a good or a bad practice to add an index on this field if we
> > want to improve SELECT time execution ?
>
> This may help if and only if a) you have many more records with FLAG=1 than 
> with FLAG=0 (or vice versa); and b) most of the time, you are looking up the 
> records belonging to the small subset. For example, if there's a small number 
> of "active" or recent records that need to be processed, and a large archive 
> of "processed" records.
>
> However, in such a case, you might be even better off splitting the small 
> subset into its own separate table.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to compare time stamp

[Igor Tandetnik]

Akash Agrawal  wrote:
> I have table in which i have column of Date contain both *date and
> time*when compare the value in my c++ program it is not giving me
> correct
> result . can you help to solve my problem.

Show how you are inserting the data into the table, and how you are retrieving 
it. Explain the results you observe, and how they differ from your 
expectations. What exactly do you mean by "not correct"?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[François]

Hello Richard, and thank you for this fast answer.

Can we consider that creating an index for an integer field is a good
practice if this field has at least 3 possible values ? Or more ?

Best Regards,

François

On 13 sep, 13:33, Richard Hipp  wrote:
> On Tue, Sep 13, 2011 at 6:23 AM, François 
> wrote:
>
> > Hello,
>
> > Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table
> > may contain up to 100 000 entries and those entries can be selected
> > using "WHERE ITEM.FLAG = ?" conditions.
>
> > Is then a good or a bad practice to add an index on this field if we
> > want to improve SELECT time execution ?
>
> Bad practice.  It will likely make things worse, not better.
>
>
>
> > Please note that it concerns an iPhone app.
>
> > Thank you !
>
> > Best Regards,
>
> > François
> > ___
> > sqlite-users mailing list
> > sqlite-us...@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Igor Tandetnik]

François  wrote:
> Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table
> may contain up to 100 000 entries and those entries can be selected
> using "WHERE ITEM.FLAG = ?" conditions.
> 
> Is then a good or a bad practice to add an index on this field if we
> want to improve SELECT time execution ?

This may help if and only if a) you have many more records with FLAG=1 than 
with FLAG=0 (or vice versa); and b) most of the time, you are looking up the 
records belonging to the small subset. For example, if there's a small number 
of "active" or recent records that need to be processed, and a large archive of 
"processed" records.

However, in such a case, you might be even better off splitting the small 
subset into its own separate table.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Simon Slavin]

On 13 Sep 2011, at 12:13pm, Tim Streater wrote:

>  SQLite error: general code: HY000 error: 17, database schema has changed
> 
> and since all the rest of the time the code works fine I'm having some 
> trouble pinning down whether it's my bug (more likely) and if so where, or an 
> SQLite bug (less likely).

The schema of a database is not the data in it -- values of fields, new rows 
inserted, etc. -- but things like what columns there are in a database.  If 
you're not issuing any 'CREATE' or 'DROP' instructions and you're getting the 
above error, there may be a big underlying problem with your system.

I'd recommend running PRAGMA integrity_check() on your database, at the very 
least.

> What I have is two databases (each is a mailbox) and I wish, from time to 
> time, to move a row from one database to the other (the schemas are the same 
> - but see below). Each row has a unique id, (defined as: absid integer 
> PRIMARY KEY - but see below) and when the row is moved, I want to allocate a 
> new absid value to the row in its new location. I have to do this to avoid 
> clashing with absid values for already-existing rows.

I don't see an easy way of solving conflicting absids.  Whatever strategy you 
use, there's still a chance you'll get a crash.  And you have the problem of 
each copy of the database showing different absid numbers for the same data.  
I'd like to suggest an alternative strategy:

Create a new column which indicates which database the row originated in.  For 
each of those two databases, when you create a new row, set the right value for 
this 'origin' column.  Make your primary key not just 'absid' but 
'(origin,absid)'.

You can still define 'absid' as 'INTEGER AUTOINCREMENT', but not 'INTEGER 
PRIMARY KEY'.  Something like

CREATE TABLE myTable (orig TEXT, absid INTEGER AUTOINCREMENT, a, b, c, d, 
PRIMARY KEY (orig, absid))

> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the 
> source database):
> 
> $dbh->query ("attach database ':memory:' as mem");
> $dbh->query ($create_messages);   // Create the messages table in the 
> memory database
> $dbh->query ("attach database '" . $mailbox . "' as dst");
> $dbh->query ("insert into mem.messages select * from main.messages where 
> absid='$absid'");// *** The failing statement ***
> $dbh->query ("update mem.messages set absid=null");
> $dbh->query ("insert into dst.messages select * from mem.messages");
> $absid = $dbh->lastInsertId ();
> 
> The only way I could find to do what I need regarding a new absid value is, 
> as above, to copy the row to a memory database, set its absid to null, and 
> then copy to the destination database. Even to do this I've had to define 
> absid in the memory database as "absid integer" rather than "absid integer 
> PRIMARY KEY". Is this the cause of the error message? If so, why does it work 
> 99.9% of the time?

Two records with the same absid will violate your PRIMARY KEY contraint, 
because they break the UNIQUE requirement.  Perhaps the time they fail is when 
by coincidence both databases generate entries with the same absid.  However, I 
don't know that this should lead to the error message you reported: a message 
about SCHEMA should really be about something else.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database schema has changed?

[Igor Tandetnik]

Tim Streater  wrote:
> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the 
> source database):
> 
>  $dbh->query ("attach database ':memory:' as mem");
>  $dbh->query ($create_messages);   // Create the messages table in 
> the memory database
>  $dbh->query ("attach database '" . $mailbox . "' as dst");
>  $dbh->query ("insert into mem.messages select * from main.messages where 
> absid='$absid'");// *** The failing statement ***

I'm not familiar with PDO and PHP, but my educated guess is, the language 
binding layer running on top of SQLite has cached the prepared INSERT statement 
from prior execution. But that statement's handle has been invalidated by 
intervening ATTACH statements (see http://www.sqlite.org/c3ref/prepare.html , 
in particular the difference between sqlite3_prepare and sqlite3_prepare_v2).

See if there's any way to instruct the binding to clear its cache of prepared 
statements.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In memory strategy guide

[Simon Slavin]

On 13 Sep 2011, at 10:50am, jerome moliere wrote:

> I'm about trying to use SQLite as a pure in memory mode but I am still
> facing problems,
> I wrote a small Java program doing this:
> - opening a connection using the in memory syntax
> - create a stupid table
> - insert dummy values
> - dumping the table to a file
> - dropping the table
> - restoring the contents of my dumps
> - issuing a query on the table
> 
> my query fails because the table does not exist

If the table does not exist, then obviously you did not do all the above steps 
correctly.  What makes you think that the 'restoring the contents of my dumps' 
stage did its job ?

A good way to test things like this is to use the sqlite3 command-line shell 
tool:



Execute your commands in that.  If that works, but your Java code doesn't, the 
fault is in your Java code.   If the shell tool doesn't do your commands 
correctly, there's something wrong with the commands.

> So I guess should rather use such strategy, could you confirm/infirm it ?
> - connect as in memory
> - create the table mytable in memory
> - attach the database as stored in a file : attach database 'mydb' as mydb
> - import the data using a insert into mytable query using a select *
> from mydb.mytable
> - then use data available in memory in the database using select * from 
> mytable

You might want to start off with you forget memory mode entirely.  Just use the 
database from the file and make sure everything works.  Once you've got that 
working, and if that solution doesn't give you acceptable behaviour, /then/ it 
might be worth messing with in-memory databases.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

[Richard Hipp]

On Tue, Sep 13, 2011 at 6:23 AM, François wrote:

> Hello,
>
> Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table
> may contain up to 100 000 entries and those entries can be selected
> using "WHERE ITEM.FLAG = ?" conditions.
>
> Is then a good or a bad practice to add an index on this field if we
> want to improve SELECT time execution ?
>

Bad practice.  It will likely make things worse, not better.




>
> Please note that it concerns an iPhone app.
>
> Thank you !
>
> Best Regards,
>
> François
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] changing compiled SQL statement

[Richard Hipp]

On Tue, Sep 13, 2011 at 4:36 AM, Baruch Burstein wrote:

> If I compile a SQL statement, can I immediately change that sql string?
> e.g., Is this allowed:
>
> sql = sqlite3_mprintf("select a from b where c = %Q;", d);
> sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
> sqlite3_step(stmt);
> strcpy(a, (char *)sqlite3_column_text(stmt, 0));
> sqlite3_free(sql);   //   or   sql = "blah blah";
>

Yes.  sqlite3_prepare_v2() makes it's own copy of the original SQL.



> .
> .
> .
> sqlite3_step(stmt);
> strcpy(a, (char *)sqlite3_column_text(stmt, 0));
> .
> .
> .
> sqlite3_finalize(stmt);
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database schema has changed?

[Tim Streater]

About once every few months I get this error (under OS X 10.7.1):

  SQLite error: general code: HY000 error: 17, database schema has changed

and since all the rest of the time the code works fine I'm having some trouble 
pinning down whether it's my bug (more likely) and if so where, or an SQLite 
bug (less likely).

What I have is two databases (each is a mailbox) and I wish, from time to time, 
to move a row from one database to the other (the schemas are the same - but 
see below). Each row has a unique id, (defined as: absid integer PRIMARY KEY - 
but see below) and when the row is moved, I want to allocate a new absid value 
to the row in its new location. I have to do this to avoid clashing with absid 
values for already-existing rows.

I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the 
source database):

 $dbh->query ("attach database ':memory:' as mem");
 $dbh->query ($create_messages);   // Create the messages table in the 
memory database
 $dbh->query ("attach database '" . $mailbox . "' as dst");
 $dbh->query ("insert into mem.messages select * from main.messages where 
absid='$absid'");// *** The failing statement ***
 $dbh->query ("update mem.messages set absid=null");
 $dbh->query ("insert into dst.messages select * from mem.messages");
 $absid = $dbh->lastInsertId ();

The only way I could find to do what I need regarding a new absid value is, as 
above, to copy the row to a memory database, set its absid to null, and then 
copy to the destination database. Even to do this I've had to define absid in 
the memory database as "absid integer" rather than "absid integer PRIMARY KEY". 
Is this the cause of the error message? If so, why does it work 99.9% of the 
time?

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index on BOOLEAN field

[François]

Hello,

Let us consider a table "ITEM" with a BOOLEAN field "FLAG". This table
may contain up to 100 000 entries and those entries can be selected
using "WHERE ITEM.FLAG = ?" conditions.

Is then a good or a bad practice to add an index on this field if we
want to improve SELECT time execution ?

Please note that it concerns an iPhone app.

Thank you !

Best Regards,

François
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In memory strategy guide

[jerome moliere]

Hi all,
I'm about trying to use SQLite as a pure in memory mode but I am still
facing problems,
I wrote a small Java program doing this:
- opening a connection using the in memory syntax
- create a stupid table
- insert dummy values
- dumping the table to a file
- dropping the table
- restoring the contents of my dumps
- issuing a query on the table

my query fails because the table does not exist (that 's what
sqlite_master show me)

So I guess should rather use such strategy, could you confirm/infirm it ?
- connect as in memory
- create the table mytable in memory
- attach the database as stored in a file : attach database 'mydb' as mydb
- import the data using a insert into mytable query using a select *
from mydb.mytable
- then use data available in memory in the database using select * from mytable


PS:
I got a previous DNS error from my gmail so I resent the message, I
must apologize if you already received this message
J.MOLIERE - Mentor/J
auteur Eyrolles
blog: http://romjethoughts.blogspot.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to compare time stamp

[Steffen Mangold]

Akash Agrawal wrotes:

>
> I have table in which i have column of Date contain both *date and time*when 
> compare the value in my c++ program it is not giving me correct result . can 
> you help to solve my problem.
>

Hi,
I had a similar problem. How accurate is your time? I had such a problem with 
comparing milliseconds. Let me search what exact was the problem, I come back 
here.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] changing compiled SQL statement

[Baruch Burstein]

If I compile a SQL statement, can I immediately change that sql string?
e.g., Is this allowed:

sql = sqlite3_mprintf("select a from b where c = %Q;", d);
sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
sqlite3_step(stmt);
strcpy(a, (char *)sqlite3_column_text(stmt, 0));
sqlite3_free(sql);   //   or   sql = "blah blah";
.
.
.
sqlite3_step(stmt);
strcpy(a, (char *)sqlite3_column_text(stmt, 0));
.
.
.
sqlite3_finalize(stmt);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Minimal library size

[Baruch Burstein]

I compiled down to 255Kb, but I don't use all the OMITs, only most. I
compiled with mingw, and used msys's configure.

On Sun, Sep 11, 2011 at 2:31 PM, Richard Tew wrote:

> On Sun, Sep 11, 2011 at 4:37 PM, Baruch Burstein 
> wrote:
> > Did you configure and make using the same options?
>
> No, there is no such thing as configure on Windows.  Both parser
> "lemon" and compiler were run with the same options, as building using
> "Makefile.msc" does this.
>
> Have you personally managed to compile the library down to the 180 KB
> mentioned on the website, or is this generic advice?
>
> Thanks anyway,
> Richard.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] changing compiled SQL statement

[Baruch Burstein]

If I compile a SQL statement, can I immediately change that sql string?
e.g., Is this allowed:

sql = sqlite3_mprintf("select a from b where c = %Q;", d);
sqlite3_prepare_v2(db, sql, -1, &stmt, 0);
sqlite3_step(stmt);
strcpy(a, (char *)sqlite3_column_text(stmt, 0));
sqlite3_free(sql);   //   or   sql = "blah blah";
.
.
.
sqlite3_step(stmt);
strcpy(a, (char *)sqlite3_column_text(stmt, 0));
.
.
.
sqlite3_finalize(stmt);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users