[sqlite] What is the best way to select "evenly" ?

[Ron Aaron]

Hello, all -

I'm trying to select from a table which contains pairs of "id", "name",
where the "name" may appear in multiple "ids".  For example, the table
might have

"10", "abc"
"20", "abc"
"10", "def"
"10", "ghi"
"20", "ghi"
etc.That is, not every id will have every name in it.

What I would like to accomplish, is to select distinct names, along with
an id, such that the id is as uniformly represented as possible.

My current solution is to create a temporary table with a unique key for
the name, and select into it like:

insert or ignore into tbl
select id,name
from originaltbl
where name in (...)
order by random() ;

This sort of works, in that I get each name just once, but the
distribution of ids is not as uniform as I would like, and I'm
scratching my head as to how to accomplish this.

The purpose here is to distribute jobs over processing units,
distributing the load as evenly as possible.

Suggestions very welcome!


-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+1 425.296.0766
+972 52.652.5543
GPG Key: 91F92EB8
<https://pgp.mit.edu/pks/lookup?op=get=0xC90C1BD191F92EB8>
<http://8th-dev.com>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Astonishingly, the problem went away when I switched from using "clang" 
to using "gcc", on Linux 32 bit.

The actual problem was a 'memcpy' crash, due to incorrect stack 
alignment.  Normally, this is a gcc problem on 64-bit, but here it 
turned up on Linux 32 bit.  Perhaps something to do with compiler flags, 
though I don't set the 'arch' or 'tune' to anything.

So.  Problem solved, and nothing really to do with sqlite per se; but I 
do wish compilers would do less on my behalf...


-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

On 4/14/15 21:06, Richard Hipp wrote:
> On 4/14/15, Ron Aaron  wrote:
>> Something is very wrong, then.  The file I downloaded from the
>> sqlite.org site, and unzipped, has these:
> Yes.  My previous email had the wrong version of sqlite3..  Version
> 3.8.9 states:
>
> Size:  5507061 bytes
> Lines:  154633
> MD5: a99b92d9e510a8107999c474dca017d7
> SHA1: 49f1c3ae347e1327b5aaa6c7f76126bdf09c6f42
>
> So apparently you have the right sqlite3.c.  And yet line 26091 of
> that file is a blank line that is in between two procedures.  It is
> not a part of the findInodeInfo() procedure.  The findInodeInfo()
> function is 528 lines further down in the file.
>
> Do you have any idea why the line numbers on your stack track do not
> align with line numbers in the source file?  Are you sure you used the
> correct source code file in your build?

Given that my lin32 compile is done in a schroot environment, it could 
perhaps be the case that an incorrect sqlite is getting picked up, 
though I don't see how.

I'll have to investigate this further tomorrow; I thank you all for your 
time, and I'll let you know what I determine.

Best regards,
Ron

>
>>
>> ~/src/sqlite-amalgamation-3080900$ ls -l
>> total 11856
>> -rw-r--r--@ 1 ron  staff   147581 Apr  8 17:38 shell.c
>> -rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
>> -rw-r--r--@ 1 ron  staff   376915 Apr  8 17:38 sqlite3.h
>> -rw-r--r--@ 1 ron  staff27763 Apr  8 17:38 sqlite3ext.h
>>
>>
>> ~/src/sqlite-amalgamation-3080900$ md5 sqlite3.c
>> MD5 (sqlite3.c) = a99b92d9e510a8107999c474dca017d7
>>
>>
>> On 4/14/15 20:41, Richard Hipp wrote:
>>> On 4/14/15, Ron Aaron  wrote:
>>>> On 4/14/15 18:13, Dan Kennedy wrote:
>>>>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>>>>>> Just updated with the version from sqlite.org and have the same
>>>>>> problem:
>>>>> The line numbers still don't match the 3.8.9 amalgamation on the
>>>>> website. Are you running [make sqlite3.c] yourself or downloading
>>>>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>>>> sqlite-amalgamation-3080900.zip
>>> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
>>> size, 154880 lines long, and have an MD5 hash of
>>> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
>>> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
>>> using the correct sqlite3.c source file.
>>>
>>> D. Richard Hipp
>>> drh at sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> --
>> Ron Aaron, CTO
>> Aaron High-Tech, Ltd.
>> +972.52.652.5543
>> <http://8th-dev.com>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Yes:


I issued the command:  unzip ~/Downloads/sqlite-amalgamation-3080900.zip


file reports: Downloads/sqlite-amalgamation-3080900.zip: Zip archive 
data, at least v1.0 to extract


md5 sum: 02e9c3a6daa8b8587cf6bef828c2e33f


On 4/14/15 21:05, Nelson, Erik - 2 wrote:
> Ron Aaron wrote on Tuesday, April 14, 2015 1:51 PM
>
>> Something is very wrong, then.  The file I downloaded from the sqlite.org 
>> site, and unzipped, has these:
>> -rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
> Can you confirm that this file came from the .zip file, not the .gz file?
>
>>> On 4/14/15 18:13, Dan Kennedy wrote:
>>>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>>>>> Just updated with the version from sqlite.org and have the same problem:
>>>> The line numbers still don't match the 3.8.9 amalgamation on the
>>>> website. Are you running [make sqlite3.c] yourself or downloading
>>>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>>> sqlite-amalgamation-3080900.zip
>> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
>> size, 154880 lines long, and have an MD5 hash of
>> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
>> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
>> using the correct sqlite3.c source file.
> --
> This message, and any attachments, is for the intended recipient(s) only, may 
> contain information that is privileged, confidential and/or proprietary and 
> subject to important terms and conditions available at 
> http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
> recipient, please delete this message.
> _______
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Something is very wrong, then.  The file I downloaded from the 
sqlite.org site, and unzipped, has these:


~/src/sqlite-amalgamation-3080900$ ls -l
total 11856
-rw-r--r--@ 1 ron  staff   147581 Apr  8 17:38 shell.c
-rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
-rw-r--r--@ 1 ron  staff   376915 Apr  8 17:38 sqlite3.h
-rw-r--r--@ 1 ron  staff27763 Apr  8 17:38 sqlite3ext.h


~/src/sqlite-amalgamation-3080900$ md5 sqlite3.c
MD5 (sqlite3.c) = a99b92d9e510a8107999c474dca017d7


On 4/14/15 20:41, Richard Hipp wrote:
> On 4/14/15, Ron Aaron  wrote:
>>
>> On 4/14/15 18:13, Dan Kennedy wrote:
>>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>>>> Just updated with the version from sqlite.org and have the same problem:
>>> The line numbers still don't match the 3.8.9 amalgamation on the
>>> website. Are you running [make sqlite3.c] yourself or downloading
>>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>> sqlite-amalgamation-3080900.zip
> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
> size, 154880 lines long, and have an MD5 hash of
> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
> using the correct sqlite3.c source file.
>
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

On 4/14/15 18:13, Dan Kennedy wrote:
> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>> Just updated with the version from sqlite.org and have the same problem:
>
> The line numbers still don't match the 3.8.9 amalgamation on the 
> website. Are you running [make sqlite3.c] yourself or downloading 
> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
sqlite-amalgamation-3080900.zip

>
> Are you able to share the test app with us so that we can reproduce 
> the crash?
I can't do that, unfortunately.  I'll try to make an independent test 
scenario.  I do appreciate your efforts!
>
> What is output if you run [where full 7] in gdb?

A pile of output:

ok> "test.db" db:open
CALL_or_compile: 0x92014b8 xt 0x82e5990 name open

Program received signal SIGSEGV, Segmentation fault.
findInodeInfo (pFile=0x928a110, ppInode=0x928a118) at sqlite3.c:26091
26091
(gdb) where full 7
#0  findInodeInfo (pFile=0x928a110, ppInode=0x928a118) at sqlite3.c:26091
 rc = 0
 fd = 5
 fileId = {dev = 613319152320380928, ino = 4432731307}
 statbuf = {st_dev = 25, __pad1 = 420, __st_ino = 85073599, 
st_mode = 33188, st_nlink = 1, st_uid = 1000, st_gid = 1000, st_rdev = 
0, __pad2 = 420,
   st_size = 0, st_blksize = 4096, st_blocks = 16, st_atim = 
{tv_sec = 1429027578, tv_nsec = 801386751}, st_mtim = {tv_sec = 1429027578,
 tv_nsec = 801386751}, st_ctim = {tv_sec = 1429027578, 
tv_nsec = 801386751}, st_ino = 85073599}
 pInode = 0x0
#1  0x085d31c4 in fillInUnixFile (pVfs=0x89422b0 , 
h=5, pId=0x928a110, zFilename=0x928a200 "/home/ron/proj/8th/test.db", 
ctrlFlags=0)
 at sqlite3.c:30092
 pLockingStyle = 0x8917cec 
 pNew = 0x928a110
 rc = 0
#2  0x084fa62e in unixOpen (pVfs=0x89422b0 , 
zPath=0x928a200 "/home/ron/proj/8th/test.db", pFile=0x928a110, flags=262,
 pOutFlags=0xd288) at sqlite3.c:30697
 p = 0x928a110
 fd = 5
 openFlags = 32834
 eType = 256
 noLock = 0
 rc = 0
 ctrlFlags = 0
 isExclusive = 0
 isDelete = 0
 isCreate = 4
 isReadonly = 0
 isReadWrite = 2
 syncDir = 0
 zTmpname = 
"\226\b\370d\230\b\033\000\000\000S\f6\b\000`\236\000\310d\230\b\002\000\000\000F\000\000\000C\000\000\000H\000\000\000\234`\240\b\000\000\375\367`\t\375\367I\000\000\000s\000\000\000\000\300\222\b\033\000\000\000\370d\230\b\200\002\000\000\000\340(\t\000`\236\b\002\000\000\000\230\320\377\377\221\267\065\b\221\267\065\b\001\000\000\000X\250\375\367\221\267\065\b@?\b<\276\226\bT\320\377\377\370d\230\b\240*\226\b\330\320\377\377T\320\377\377\000\300\222\b
 
\031\226\b\350\320\377\377d\320\377\377\212\273\065\b 
\031\226\b\001\000\000\000\344\320\377\377\350\320\377\377\324\320\377\377\240*\226\b\244\320\377\377\000\300\222\b\344\320\377\377
 
\031\226\b\264\320\377\377"...
 zName = 0x928a200 "/home/ron/proj/8th/test.db"
#3  0x0851a3e3 in sqlite3OsOpen (pVfs=0x89422b0 , 
zPath=0x928a200 "/home/ron/proj/8th/test.db", pFile=0x928a110, flags=262,
 pFlagsOut=0xd288) at sqlite3.c:16093
 rc = 153570696
#4  0x08515c18 in sqlite3PagerOpen (pVfs=0x89422b0 
, ppPager=0x9286008, zFilename=0x926e248 
"test.db", nExtra=76, flags=0,
 vfsFlags=262, xReinit=0x85160a0 ) at sqlite3.c:45520
 fout = 262
 pPtr = 0x928a21c "/home/ron/proj/8th/test.db-journal"
 pPager = 0x928a008
 rc = 0
 tempFile = 0
 memDb = 0
 readOnly = 0
 journalFileSize = 80
---Type  to continue, or q  to quit---
 zPathname = 0x9274d88 "/home/ron/proj/8th/test.db"
 nPathname = 26
 useJournal = 1
 pcacheSize = 48
 szPageDflt = 1024
 zUri = 0x926e250 ""
 nUri = 1
#5  0x08514882 in sqlite3BtreeOpen (pVfs=0x89422b0 
, zFilename=0x926e248 "test.db", db=0x9192b48, 
ppBtree=0x9192d04, flags=0,
 vfsFlags=262) at sqlite3.c:54119
 pBt = 0x9286008
 p = 0x919fa08
 mutexOpen = 0x0
 rc = 0
 nReserve = 8 '\b'
 zDbHeader = 
"\000\263'Q\bH+\031\t'\264\202\b\001\000\000\000\t\270O\b\350a!\t\000\300\222\b'\264\202\b\000\300\222\b\000\000\000\000H+\031\t\000\300\222\b~\347P\bH+\031\t\000\000\000\000'\264\202\b\001\000\000\000\000\300\222\b\000\000\000\000\001\000\000\000\374\002\000\200\000\000\000\000Ha!\t\a\000\000\000\032\264\202"
 isTempDb = 0
 isMemdb = 0
#6  0x0850de27 in openDatabase (zFilename=0x919f9d0 "test.db", 
ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
 db = 0x9192b48
 rc = 0
 isThreadsafe = 0
 zOpen = 0x926e248 "test.db"
 zErrMsg = 0x0
(More stack frames follow...)
(gdb)

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Just updated with the version from sqlite.org and have the same problem:


#0  findInodeInfo (pFile=0x9434118, ppInode=0x9434120) at sqlite3.c:26091
#1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0 , 
h=5, pId=0x9434118, zFilename=0x9434208 "/home/ron/proj/8th/test.db", 
ctrlFlags=0)
 at sqlite3.c:30092
#2  0x084fa69e in unixOpen (pVfs=0x89422b0 , 
zPath=0x9434208 "/home/ron/proj/8th/test.db", pFile=0x9434118, flags=262,
 pOutFlags=0xd074) at sqlite3.c:30697
#3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0 , 
zPath=0x9434208 "/home/ron/proj/8th/test.db", pFile=0x9434118, flags=262,
 pFlagsOut=0xd074) at sqlite3.c:16093
#4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0 
, ppPager=0x91c6af0, zFilename=0x92a3cf0 
"test.db", nExtra=76, flags=0,
 vfsFlags=262, xReinit=0x8516110 ) at sqlite3.c:45520
#5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0 
, zFilename=0x92a3cf0 "test.db", db=0x9192fd0, 
ppBtree=0x919318c, flags=0,
 vfsFlags=262) at sqlite3.c:54119
#6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db", 
ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
#7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db", 
ppDb=0x91a2028) at sqlite3.c:127601


On 4/14/15 17:31, Ron Aaron wrote:
> I'm using the one from fossil...
>
>
> On 4/14/15 17:12, Richard Hipp wrote:
>> On 4/14/15, Ron Aaron  wrote:
>>> I just updated to the 3.8.9 version of SQLite for my project, and am
>>> compiling for 32-bit linux (on a 64-bit box).
>>>
>>>
>>> I'm getting a consistent crash in findInodeInfo, with this backtrace:
>>>
>>>
>>>  #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
>>>  sqlite3.c:26091
>> Corresponds to this source code line:
>> https://www.sqlite.org/src/info/25b80a3d?ln=710
>>
>>>  #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
>>>  , h=5, pId=0x9438118, zFilename=0x9438208
>>>  "/home/ron/proj/8th/test.db", ctrlFlags=0)
>>>   at sqlite3.c:30092
>> Corresponds to this source code line:
>> https://www.sqlite.org/src/info/25b80a3d?ln=4711
>>
>> So I think you must be using a non-standard build of the sqlite3.c
>> amalgamation file, huh?
>>
>>>  #2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
>>>  zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
>>>  flags=262, pOutFlags=0xd064)
>>>   at sqlite3.c:30697
>>>  #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
>>>  , zPath=0x9438208
>>>  "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
>>>  pFlagsOut=0xd064)
>>>   at sqlite3.c:16093
>>>  #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
>>>  , ppPager=0x91c6af0, zFilename=0x92a7cf0
>>>  "test.db", nExtra=76, flags=0, vfsFlags=262,
>>>   xReinit=0x8516110 ) at sqlite3.c:45520
>>>  #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
>>>  , zFilename=0x92a7cf0 "test.db", 
>>> db=0x9192fd0,
>>>  ppBtree=0x919318c, flags=0, vfsFlags=262)
>>>   at sqlite3.c:54119
>>>  #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
>>>  ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
>>>  #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
>>>  ppDb=0x91a2028) at sqlite3.c:127601
>>>
>>>
>>> Nothing has changed in my test code, and I haven't got a similar 
>>> problem
>>> on 32-bit OS X;  however, it's certainly conceivable I'm not using the
>>> right flags when compiling SQLite:
>>>
>>>  gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
>>>  -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
>>>  -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
>>>  -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c
>>>
>>>
>>> The crash occurs whether I work in a chroot lin 32 environment or a
>>> virtual machine running 'native'.  The test application is statically
>>> linked.
>>>
>>>
>>> Any clues would be appreciated!
>>>
>>>
>>> Thanks,
>>>
>>> Ron
>>>
>>>
>>>
>>> -- 
>>> Ron Aaron, CTO
>>> Aaron High-Tech, Ltd.
>>> +972.52.652.5543
>>> <http://8th-dev.com>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

I'm using the one from fossil...


On 4/14/15 17:12, Richard Hipp wrote:
> On 4/14/15, Ron Aaron  wrote:
>> I just updated to the 3.8.9 version of SQLite for my project, and am
>> compiling for 32-bit linux (on a 64-bit box).
>>
>>
>> I'm getting a consistent crash in findInodeInfo, with this backtrace:
>>
>>
>>  #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
>>  sqlite3.c:26091
> Corresponds to this source code line:
> https://www.sqlite.org/src/info/25b80a3d?ln=710
>
>>  #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
>>  , h=5, pId=0x9438118, zFilename=0x9438208
>>  "/home/ron/proj/8th/test.db", ctrlFlags=0)
>>   at sqlite3.c:30092
> Corresponds to this source code line:
> https://www.sqlite.org/src/info/25b80a3d?ln=4711
>
> So I think you must be using a non-standard build of the sqlite3.c
> amalgamation file, huh?
>
>>  #2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
>>  zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
>>  flags=262, pOutFlags=0xd064)
>>   at sqlite3.c:30697
>>  #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
>>  , zPath=0x9438208
>>  "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
>>  pFlagsOut=0xd064)
>>   at sqlite3.c:16093
>>  #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
>>  , ppPager=0x91c6af0, zFilename=0x92a7cf0
>>  "test.db", nExtra=76, flags=0, vfsFlags=262,
>>   xReinit=0x8516110 ) at sqlite3.c:45520
>>  #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
>>  , zFilename=0x92a7cf0 "test.db", db=0x9192fd0,
>>  ppBtree=0x919318c, flags=0, vfsFlags=262)
>>   at sqlite3.c:54119
>>  #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
>>  ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
>>  #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
>>  ppDb=0x91a2028) at sqlite3.c:127601
>>
>>
>> Nothing has changed in my test code, and I haven't got a similar problem
>> on 32-bit OS X;  however, it's certainly conceivable I'm not using the
>> right flags when compiling SQLite:
>>
>>  gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
>>  -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
>>  -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
>>  -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c
>>
>>
>> The crash occurs whether I work in a chroot lin 32 environment or a
>> virtual machine running 'native'.  The test application is statically
>> linked.
>>
>>
>> Any clues would be appreciated!
>>
>>
>> Thanks,
>>
>> Ron
>>
>>
>>
>> --
>> Ron Aaron, CTO
>> Aaron High-Tech, Ltd.
>> +972.52.652.5543
>> <http://8th-dev.com>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

I just updated to the 3.8.9 version of SQLite for my project, and am 
compiling for 32-bit linux (on a 64-bit box).


I'm getting a consistent crash in findInodeInfo, with this backtrace:


#0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
sqlite3.c:26091
#1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
, h=5, pId=0x9438118, zFilename=0x9438208
"/home/ron/proj/8th/test.db", ctrlFlags=0)
 at sqlite3.c:30092
#2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
flags=262, pOutFlags=0xd064)
 at sqlite3.c:30697
#3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
, zPath=0x9438208
"/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
pFlagsOut=0xd064)
 at sqlite3.c:16093
#4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
, ppPager=0x91c6af0, zFilename=0x92a7cf0
"test.db", nExtra=76, flags=0, vfsFlags=262,
 xReinit=0x8516110 ) at sqlite3.c:45520
#5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
, zFilename=0x92a7cf0 "test.db", db=0x9192fd0,
ppBtree=0x919318c, flags=0, vfsFlags=262)
 at sqlite3.c:54119
#6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
#7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
ppDb=0x91a2028) at sqlite3.c:127601


Nothing has changed in my test code, and I haven't got a similar problem 
on 32-bit OS X;  however, it's certainly conceivable I'm not using the 
right flags when compiling SQLite:

gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
-DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
-DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
-DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c


The crash occurs whether I work in a chroot lin 32 environment or a 
virtual machine running 'native'.  The test application is statically 
linked.


Any clues would be appreciated!


Thanks,

Ron



-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543
<http://8th-dev.com>

Re: [sqlite] last modified time or version of sqlite database

[Ron Aaron]

On Mon, April 3, 2006 15:49, Chris Fletcher wrote:
>> 
>
> Thanks - but this seems to give the number of changes during the
> lifetime of a db session. With CGI the sessions will be short lived.
> On a new session I want to know when the db was last modified (or
> some other indication of changes).

Why not use a trigger on insert/update etc to update a table with one row and
column, the time of last modification?  Alternatively, ensure your INSERTs etc
update that table when they are done modifying?

-- 
RonWare.ORG - Precision Crafted Software   http://ronware.org/
Reva Forth for Linux and Windows  http://ronware.org/reva/

Re: [sqlite] How to cross compile sqlite-3.2.7 for arm9 s3c2410?

[Ron Aaron]

On Tue, September 27, 2005 7:39, Frank van Vugt said:

> There's some info in the wiki on it, but basically the steps are:

Hello, Frank -

I successfully created an ARM sqlite3 (3.2.7) and it is running on my Archos
PMA400 - thanks!

I have noticed that when I create a database and do a simple INSERT, (using
the sqlite3 binary), there is an appreciable delay (1-2 seconds) before
sqlite3 returns to the prompt.  When doing SELECT there is no delay.

I have not noticed this on my Windows or Linux machines (x86).

Is this possibly because of not using transactions?  It seems a bit extreme. 
The device is storing the database on a hard-disk, so it shouldn't have to do
with paged RAM or something.

Thanks for any suggestions, and thanks again for the good instructions!

-- 
Reva Forth for Linux and Windows --  http://ronware.org/reva/
GPG key fingerprint: 8130 734C 69A3 6542 0853 CB42 3ECF 9259 AD29 415D

Re: [sqlite] Using expressions for 'DEFAULT

[Ron Aaron]

On Sun, October 31, 2004 3:58, D. Richard Hipp said:

>> However, this statement is not parsed by SQLite:
>>
>> CREATE TABLE a ( b datetime DEFAULT date('now'));

> Changes to implement this capability are already in the
> works and will be available within the next two months.
> Stay tuned...

Ooh, thanks!  I am anxiously looking forward to it ...

Ron

-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] Parameterized queries -- howto?

[Ron Aaron]

On Thu, October 21, 2004 7:51, Clay Dowling said:
> Ron,
>
> Take a look at the example application in the Linux Journal article
> http://www.linuxjournal.com/article.php?sid=7803
>
> The parameterized queries provide a much better solution than sprintf.
> You can grab a complete working app from the link at the end of the
> article.
>

Thanks.

I spent some time last night playing around with adding support for the
parameterized queries to my C++ wrapper (around SQLite).  It works nicely,
thanks!


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] Parameterized queries -- howto?

[Ron Aaron]

On Wed, October 20, 2004 18:05, Vladimir Vukicevic said:

> I'm confused -- it doesn't make them useless in practice, they can be
> used exactly for what they're intended for (precompiled statements).
> What exactly are you trying to do?  And what other database supports
> this?

I misspoke.  They're not *useless*, but useless *within SQL*.

I don't know about specific implemenations, but my copy of "A Guide to the SQL
Standard, fourth edition" gives examples like:

SQLSOURCE = 'DELETE FROM SP WHERE SP.QTY > ? and SP.QTY < ?';
EXEC SQL PREPARE SQLPREPPED FROM :SQLSOURCE ;
LOW = 100;
HIGH = 200;
EXEC SQL EXECUTE SQLPREPPED USING :LOW , :HIGH ;


We don't have any of these -- neither assignable SQL variables, nor an PREPARE
or EXECUTE statement (if we had stored procedures we would need them).

Anyway, in my particular case I have several similar queries which differ only
in a few specifics.  Right now I sprintf() to a temp buffer and execute that
buffer --  but I was hoping that I could use sqlite's dialect of SQL more
directly rather than cobbling together my query piecemeal.

It doesn't matter a whole lot, as the sprintf() or equivalent would have to be
done somewhere.  But it makes my application much more messy looking and
bigger than it would otherwise have to be.

-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] Parameterized queries -- howto?

[Ron Aaron]

On Wed, October 20, 2004 15:35, Vladimir Vukicevic said:
> On Wed, 20 Oct 2004 15:15:44 -0700 (PDT), Ron Aaron <[EMAIL PROTECTED]> wrote:
>> Thanks.  I know it can be done in the C API, that was not the question.  The
>> question was, can it be done *using SQL* ?
>
> No; there is no way to create a stored query only within sql; there's
> no place to actually store it.  So using parameters doesn't really get
> you anywhere.

That is what I thought.  Too bad, it makes them useless in practice.


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] Parameterized queries -- howto?

[Ron Aaron]

On Wed, October 20, 2004 14:57, Kurt Welgehausen said:
>> Am I missing something?
>
> Yes.  Go to , click on , and
> read the C/C++ API Reference.


Thanks.  I know it can be done in the C API, that was not the question.  The
question was, can it be done *using SQL* ?


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] Wich compiler to use in Windows

[Ron Aaron]

On Wed, October 20, 2004 5:41, Rubens Jr. said:

> Wich compiler do you recomend to use to compile sqlite3.0.8 in Windows ?

I use the mingw32 port of the gcc compiler (www.mingw.org) with great success.
 It has the advantage that it is the (essentially) same compiler used on Linux
systems.


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] CSV mode not correctly quoting output

[Ron Aaron]

On Wed, October 6, 2004 10:15, b.bum said:
> I'll file a bug on this.  Anyone know how to file a bug such that the
> formating isn't smushed into a single line?

I already logged a bug (#911) along with a fix


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

[sqlite] How to 'flatten' query results?

[Ron Aaron]

In my membership database, I have a 'people' table and a 'userviews' table. 
This second table consists of a (name,sql) pair.

What it does is allow my users to load a particular list of people from the
people table.  So for example, ('LA','where city like "LA"') might be the
entry for a "view" called "LA".

But now the users want to view a list of *specific* people, e.g. by people.id.

In the UI for my db, they can click on people and 'tag' them, and there is a
corresponding people.tagged field.  They can do various operations on the
tagged list of people.  Right now, they want to generate a 'userview' of just
the tagged people -- but since we can't keep those people tagged forever, they
need a userview.sql more complicated than 'where tagged=1'.

What I am looking for is a SQL way to do the query:

select id from people where tagged=1;

and instead of
1
2
6
34

get 1,2,6,34

Because what I ultimately want to do is a statement like:
insert into userviews values ('coolview', 'where id in (1,2,6,34)');

But I don't see any way to generate this insert statement from the prior
select.  Any ideas?

Thanks!


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] ON INSERT trigger

[Ron Aaron]

On Tue, September 21, 2004 11:26, Christian Smith said:
> On Tue, 21 Sep 2004, Alex wrote:
>

> In which case it might be better to convert the replace insert into an
> update, and use the update trigger which is guaranteed to have valid OLD
> and NEW.
>
> But that is not my decision to make. Create a new enhancement ticket in
> CVSTrac.

Also see ticket #368, which I reported on a similar issue.

-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

[sqlite] For vim users: dbext.vim supports sqlite now

[Ron Aaron]

>From the vim mailing list:

(David Fishburn, the author, says:)
I am pleased to announce an update to dbext to version 2.10.
http://vim.sourceforge.net/scripts/script.php?script_id=356

This release has increased the supported databases to 10 by adding SQLite
(www.sqlite.org).

-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

[sqlite] PATCH: [sqlite] sqlite output request: .mode csv

[Ron Aaron]

On Mon, September 13, 2004 12:14, Ron Aaron said:

Responding to my own complaint: here is a patch against current CVS version,
fixing the typo in the '-help' and implementing ".mode cvs" properly (at
least, in my opinion)


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D



shell.c.diff.gz
Description: GNU Zip compressed data

Re: [sqlite] sqlite output request: .mode csv

[Ron Aaron]

On Mon, September 13, 2004 8:28, Ron Aaron said:
> I have been asked to output an 'Excel'-friendly report for someone, which
> means dumping a "CSV" (Comma Separated Values) file.
>
> Setting ".separator ',' " gets me part of the way.  Unfortunately, I need to
> manually quote strings using something like this:
>

Oh!  I just saw that sqlite3 has a ".mode cvs".

Two comments:

1) the help says 'csv' instead of 'cvs'.
2) the mode is exactly the same as 'list'.  It should double-quote output
strings.

-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

[sqlite] sqlite output request: .mode csv

[Ron Aaron]

I have been asked to output an 'Excel'-friendly report for someone, which
means dumping a "CSV" (Comma Separated Values) file.

Setting ".separator ',' " gets me part of the way.  Unfortunately, I need to
manually quote strings using something like this:

SELECT '"' || name || '"', '"' || address || '"', zipcode
FROM clients;

It would be mighty handy if sqlite had a '.mode csv' option which would
auto-quote strings and not quote numbers, and put commas between them.  This
is an extremely common format...

Perhaps ".mode" is not the correct place to do this, but something similar
would be great.

Thanks!


-- 
My GPG public key is at http://ronware.org/
fingerprint: 8130 734C 69A3 6542 0853  CB42 3ECF 9259 AD29 415D

Re: [sqlite] An update with LIMIT ?

[Ron Aaron]

On Fri, June 11, 2004 12:50, Jay Macaulay said:
> Hi all,
>
> I have a database where I'm selecting rows using LIMIT.  Is there a way I
> could use LIMIT in an update?  For example right now I do:
>
> SELECT * FROM address WHERE group="a" LIMIT 5;
>
> Is there some way I could instantly update those 5 I retrieved?  Something
> like:
>
> UPDATE address SET retrieved=1 WHERE group="a" LIMIT 5;
>
> So it only updates the same 5 I just selected?  I'm trying to select then
> marked as retrieved.  I don't think I can use LIMIT in an UPDATE statements.

You can only use LIMIT with SELECT statements.  You might try something like:

UPDATE address SET retrieved=1
WHERE ROWID in
(
  SELECT ROWID FROM address WHERE group='a' LIMIT 5
);



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: 'cating' sqlite databases

[Ron Aaron]

On Thursday 08 April 2004 01:49 pm, Stathy G Touloumis wrote:
> Hi,
>
> We are working on a potential architecture by which many sqlite databases
> are created.  There may be a need to aggregate data between databases and I
> was wondering if it's possible to quickly just 'cat' the databases together
> or will they need to be recreated into a single sqlite file.

You cannot just 'cat' the files together. However, you can accomplish what you 
need very easily. 

Assume the 'main' db is open, and you want to take the contents of table 'a' 
in database 'second.db' and concatinate it to main table 'a':

attach 'second.db' as second;
insert into a (select * from second.a);
detach second;


Repeat this for all tables you want to cat together.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: sqlite.org ISP problems

[Ron Aaron]

On Wednesday 31 March 2004 07:30 am, D. Richard Hipp wrote:
>
> 2.   If you can recommend a good ISP, please do so.

I'm hosting my site via dyndns.org, which works well for me. 

-- 
My GPG public key is at http://ronware.org/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: Quick import

[Ron Aaron]

On Monday 29 March 2004 09:14 am, Dembskey, E. (Evan) wrote:
> Hi again,
>
> Is there a quick way to import a text file into a sqlite db? I have 110
> records, and would naturally prefer not to manually enter them. If there is
> a way to get the field names from the same file, so much the better...
>
> Data example:
>
> ID,Messier,Name,Type,Constellation,NGC,Magnitude,Size,RA,Dec,Season
> 1,M001,Crab Nebula,Supernova remnant,Taurus,1952,8.4,5h 34.5m,22 01,Winter
>

Look at the 'COPY' keyword.  It will do the import , though you have to define 
the table yourself first.


-- 
My GPG public key is at http://ronware.org/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: Changing values from triggers

[Ron Aaron]

On Saturday 27 March 2004 11:14 pm, Avner Levy wrote:
> I have a program (which I can't change) that every time a student fails
> in a course inserts a record like (34333, "math", 1).
...

B"H, I have a solution for you.  Unfortunately, SQLite doesn't let you modify
the new.* or old.* inside a trigger, but you can accomplish what you want like
this:

-- File:test.sql
-- Description: Test file for Avner Levy 
-- Author:  Ron Aaron
-- Created: Sun 28 Mar 2004 11:21:21
-- Last updated:Mon 29 Mar 2004 08:46:02

BEGIN TRANSACTION;
CREATE TABLE grades ( id, course, failures );
CREATE TABLE helper (id, course, failures);

CREATE UNIQUE INDEX g_0 ON grades (id,course) ON CONFLICT REPLACE;
CREATE UNIQUE INDEX h_0 ON helper (id,course) ON CONFLICT REPLACE;

CREATE TRIGGER g_i BEFORE INSERT on grades
BEGIN
INSERT INTO helper
SELECT * from grades
WHERE id=new.id and course=new.course;
END;

CREATE TRIGGER g_i2 AFTER INSERT on grades
BEGIN
INSERT INTO grades 
SELECT id,course,failures+1 from helper
WHERE id=new.id and course=new.course;
END;

COMMIT;

BEGIN TRANSACTION;
INSERT INTO grades VALUES (34333, "math", 1);
INSERT INTO grades VALUES (34333, "history", 1);
INSERT INTO grades VALUES (34333, "math", 1);
INSERT INTO grades VALUES (34333, "math", 1);
INSERT INTO grades VALUES (34333, "history", 1);
INSERT INTO grades VALUES (34333, "math", 1);
COMMIT;

SELECT "grades:", * FROM grades;
SELECT "helper:", * FROM helper;


Hope this helps,
Ron
-- 
My GPG public key is at http://ronware.org/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: Report writer

[Ron Aaron]

On Tuesday 16 March 2004 06:42 am, Griggs, Donald wrote:

>
> Are you developing for Windows?   If so, I don't know if one of these might
> be of help:
>
> 1) I see there's a commercial, but not expensive product (US$40),
> SqliteManager

Thanks, Donald (and other who've responded).

I will investigate SqliteManager, though  I am ultimately interested in having 
my product work natively on Linux as well (it currently does work under WINE 
on Linux, but not as well as under native Windows).

-- 
My GPG public key is at http://ronware.gotdns.com/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: Report writer

[Ron Aaron]

On Sunday 14 March 2004 10:17 am, Will Leshner wrote:
> What kind of features are you looking for in such a report writer?

My program is a membership management database.  The db has a list of people,
with various information about them distributed over several tables.

For example, the 'people' table has the basic information that most people
have (name, address, spouse name, 'flags').  The 'phones' has phone numbers,
phone type and id which points back to the people db.  Same with the 'emails'
and 'notes' tables.  Of course, there are other tables as well, but these
are the ones to be concerned with at present.

In my scenario, I have two basic reports that need to be handled:  a 'list'
and a 'detail'.

A 'list' is just like  it sounds: a listing of people.  It might have various
amounts of data from other tables, depending on what's needed.  

'detail' is also just like it sounds: a dump of a particular person's data.
This one is probably ok to hard-code (which is what I am doing now).  

The request I keep getting is for the user of the db to be able to create
arbitrary reports.  I need to find out exactly how far that goes; but the idea
might be they want to be able to see only people with emails, and display name
and email only.

The program I wrote allows the user to create 'filtered' lists, which are
quite sophisticated -- but the only two reports I allow at present are a
'list' containing name, addr, phone  and a detail which has all the
information.

I'm looking to see if anyone has a more generic solution I could put into my
program to allow my users some flexibility.  At this point, *any* flexibility
would make them happy...

Thanks for any pointers,
Ron

-- 
My GPG public key is at http://ronware.gotdns.com/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51




-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Report writer

[Ron Aaron]

Hi all -

Does anyone have a generic report writer which could be used with SQLite?

I keep getting requests for the ability to have 'user created' reports, and I 
don't want to rewrite something someone else has done already if possible.

I also can't afford a commercial solution, as this is for non-profit 
organizations.

thanks!

-- 
My GPG public key is at http://ronware.gotdns.com/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Patch for MingW32 build on Windows

[Ron Aaron]

Hi all -

I'm attaching my very tiny diffs to make the MingW32 compile on Windows
generate a good sqlite.dll and a sqlite.exe which uses the dll.  It also
produces a libsqlite.a so you can link in other apps.

The zip contains:
  Length Date   TimeName
    
  648  02-24-04 09:23   ming.diff  -- against this morning's sources
   43  06-30-03 09:24   ming.awk   -- because Windows cmd line is bad
10562  02-24-04 09:21   make.ming  -- obvious :-)
    ---
11253   3 files

If you define the "UPX" variable in the makefile (by default it is defined)
then you will get a UPX compressed version ... of course, this requires you
have upx installed.

-- 
My GPG public key is at http://ronware.gotdns.com/
fingerprint:  4E91 06E9 2020 114C 8BCD  55B2 0816 60AF 2B3D 4C51


<>
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: Using SQLite

[Ron Aaron]

On Sunday 01 February 2004 04:06 pm, Demitri Muna wrote:
> Hello,
Hi, Demitri -

> I need to embed a database into my application, and was referred to
> SQLite. I narrowed down my options to it and PostgreSQL (which I am not
> completely sure I can embed into an app). One thing that I need to be
> able to do is regular expression searching, something that PostgreSQL
> can do, but I don't think SQLite can. What is the best way to work
> around this?

I don't think PostgreSQL is embeddable, but maybe I'm wrong.  It's certainly
much bigger than SQLite.

You are correct that SQLite doesn't have built-in regexp search, but you can
easily add your own function and make SQLite use it.  

> The only solution that I can see is to keep all of my data in memory.
> (I expect I'll have upwards of 10,000 records, 1K each on average.) I

That's only 10 Meg, which isn't really too bad on modern machines.  But if you
think the db may grow, or the record size change, you might be better off
leaving it on disk.

> Has anyone else run into this?

Not this specifically, but I have added my own functions to SQLite at runtime,
and this is a great feature to use and will address your first concern.0


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Re: random record

[Ron Aaron]

On Wednesday 14 January 2004 09:32 am, Caleb Groom wrote:
> Does sqlite have some way to fetch a random record?  I'm looking for
> something similar to MySQL's 'select * from tbl order by rand() limit
> 1'.

Same thing:

SELECT * FROM tbl ORDER BY random() LIMIT 1;

works for me.


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] How do I coalesce values in a join?

[Ron Aaron]

Hi all -

This may be a boneheaded question, but I cannot figure out how to
accomplish what I want.

I have two tables:  

people (id,first,last)

and phones (id, type, phone)

Where each person in the 'people' table might have up to four phones.


I can easily get a report of phones by joining the people and phones:

select p.first,p.last,ph.phone
from people as p, phones as ph
where p.id = ph.id;


The problem is that this produced one line per person/phone combination.


What I would like to do, is 'coalesce' the phones to produce another
table:

peopleandphones (id,first,last,home,cel,work,fax)

So I could make a much nicer report.  But I cannot figure out how to do
this!

Help, anyone?

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]