Re: [sqlite] compiling Sqlite with ICU

[Sylvain Pointeau]

Hi,

thank you very much,

I enjoy so much sqlite with ICU,
I even installed the latest version of readline to enjoy characters like öäü
and with the ICU collation the sort order is right with those characters.

sqlite is really splendid!
Thank you so much for having made this software.

Best regards,
Sylvain

On Thu, Dec 10, 2009 at 9:30 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Sylvain Pointeau wrote:
> > just one last thing:
> > -lpthread -lm   -L/usr/local/lib -licui18n -licuuc -licudata  -lpthread
> -lm
> >
> >
> > could it be annoying for sqlite to be linked against pthread?
>
> Unless you compiled SQLite with SQLITE_THREADSAFE=0 then it has to use
> pthread.  In any event if that -lpthread came from icu-config then your ICU
> was deliberately compiled to use pthread so you can't avoid it.  (This is
> the whole point of pkg-config and icu-config - you don't have to know how
> they were compiled.)
>
> But the way libraries work means you do not need to be concerned.  The
> linker looks for symbols used by the program in each library.  If none of
> the symbols are found in a particular library then that library is ignored.
>  This means you could add the X windows libraries, the Python shared
> library
> and the ones for you favourite IM framework to the list and it won't make
> any difference on the final binary.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkshWmUACgkQmOOfHg372QSPEgCg0HqyVL971Hnn3MlPm2ap9rQf
> K3MAn2lj8GzIGwHKYdAs6HXTZPmaNo52
> =Sfj5
> -END PGP SIGNATURE-
> ___
> 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] Using pivot table

[Walter]

Walter  wrote:

> >  I have the following tables
> >  
> >  CREATE TABLE data(
> >  id   INTEGER PRIMARY KEY NOT NULL,
> >  name TEXT DEFAULT '',
> >  titleTEXT DEFAULT '',
> >  
> >  )
> >  
> >  
> >  CREATE TABLE pivot(
> >  id   INTEGER PRIMARY KEY  NOT NULL,
> >  link1INTEGER DEFAULT  0,
> >  link2INTEGER DEFAULT  0,
> >  rank INTEGER DEFAULT  0,
> >  mdateMYDATE  DEFAULT  0,
> >  status   TEXTDEFAULT  '',
> >  )
> >  
> >  link1 and link2 are id's from tbl1
> >  
> >  With this sql I get half way to what I want
> >  
> >  SELECT name,title,pivot.id AS id,mdate,status
> >  FROM data,pivot
> >  WHERE data.id=pivot.link1
> >  ORDER BY name,pivot.rank
> >  
> >  How do I get the name,title from data onto the same row
> >  
> >  WHERE data.id=pivot.link1  and pivot.link2=a different data.id
> >  this obviously does not work
>
select d1.name, d1.title, d2.name. d2.title, pivot.id, mdate, status
from pivot join data d1 on (pivot.link1 = d1.id) join data d2 on (pivot.link2 = 
d2.id)
order by d1.name, pivot.rank;

Many thanks Igor
Apart from a '.' where a ',' should have been it's working a treat
Cheers Walter

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

Re: [sqlite] .importing file into a BLOB

[Jean-Christophe Deschamps]

>Something like (one line, wrapped by email):
>
>echo "INSERT INTO t1 (id,pic) VALUES (1,X'$(od -A n -t x1
>picture.gif | tr -d '\r\n\t\ ')')" | sqlite3 mydb
>
>(The od utility may be smarter than that, optimization left
>to the OP)

Looks good, you know better than I do.  Just checked: od and tr are 
available under MinGW.

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

Re: [sqlite] compiling Sqlite with ICU

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sylvain Pointeau wrote:
> just one last thing:
> -lpthread -lm   -L/usr/local/lib -licui18n -licuuc -licudata  -lpthread -lm
> 
> 
> could it be annoying for sqlite to be linked against pthread?

Unless you compiled SQLite with SQLITE_THREADSAFE=0 then it has to use
pthread.  In any event if that -lpthread came from icu-config then your ICU
was deliberately compiled to use pthread so you can't avoid it.  (This is
the whole point of pkg-config and icu-config - you don't have to know how
they were compiled.)

But the way libraries work means you do not need to be concerned.  The
linker looks for symbols used by the program in each library.  If none of
the symbols are found in a particular library then that library is ignored.
 This means you could add the X windows libraries, the Python shared library
and the ones for you favourite IM framework to the list and it won't make
any difference on the final binary.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkshWmUACgkQmOOfHg372QSPEgCg0HqyVL971Hnn3MlPm2ap9rQf
K3MAn2lj8GzIGwHKYdAs6HXTZPmaNo52
=Sfj5
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .importing file into a BLOB

[Kees Nuyt]

On Thu, 10 Dec 2009 04:57:58 +0100, Jean-Christophe
Deschamps  wrote:

> is to 
>use a command-line tool or combination thereof that will open your 
>binary file, read each byte in turn and output its hex value in plain 
>ASCII characters, until end of file. 

Something like (one line, wrapped by email):

echo "INSERT INTO t1 (id,pic) VALUES (1,X'$(od -A n -t x1
picture.gif | tr -d '\r\n\t\ ')')" | sqlite3 mydb

(The od utility may be smarter than that, optimization left
to the OP)

>That (possibly long) chain of 
>text can be stored as a single BLOB without any problem using for 
>instance an SQL file fed to sqlite3.exe.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compiling Sqlite with ICU

[Sylvain Pointeau]

super thank you! :-D

just one last thing:
-lpthread -lm   -L/usr/local/lib -licui18n -licuuc -licudata  -lpthread -lm


could it be annoying for sqlite to be linked against pthread?

Many thanks,
Sylvain

On Thu, Dec 10, 2009 at 7:45 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Sylvain Pointeau wrote:
> > seems I found:
> >
> > ./configure CFLAGS="-DSQLITE_ENABLE_ICU" LDFLAGS="-L/usr/local/lib"
> > LIBS="-licudata -licui18n -licuio -licule -liculx -licutu -licuuc"
> >
> > is there any way to not list all the icu libs?
>
> Use icu-config.
>
> ./configure CFLAGS="-DSQLITE_ENABLE_ICU `icu-config --cppflags`"
> LDFLAGS="`icu-config --ldflags`"
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkshQdsACgkQmOOfHg372QRF1wCfXLYH6fA7IpQ0dlxSq2hEImw0
> Q+AAniTKzfM3qx9Jm9d8TnZUAJHylJIP
> =nSJM
> -END PGP SIGNATURE-
> ___
> 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] undefined reference to sqlite3_mutex_held

[Shawn Wilsher]

On Thu, Dec 10, 2009 at 10:18 AM, Daniel Mierswa wrote:

> My concolusion is that the TB folks shouldn't assume that the system
> sqlite library was built with debugging symbols or provide a mechanism
> to opt out said function call with an easy switch/compiler flag.
>
Well, there is your problem.  Building Thunderbird with system SQLite is not
supported.

Cheers,

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

Re: [sqlite] compiling Sqlite with ICU

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sylvain Pointeau wrote:
> seems I found:
> 
> ./configure CFLAGS="-DSQLITE_ENABLE_ICU" LDFLAGS="-L/usr/local/lib"
> LIBS="-licudata -licui18n -licuio -licule -liculx -licutu -licuuc"
> 
> is there any way to not list all the icu libs?

Use icu-config.

./configure CFLAGS="-DSQLITE_ENABLE_ICU `icu-config --cppflags`"
LDFLAGS="`icu-config --ldflags`"

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkshQdsACgkQmOOfHg372QRF1wCfXLYH6fA7IpQ0dlxSq2hEImw0
Q+AAniTKzfM3qx9Jm9d8TnZUAJHylJIP
=nSJM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: The FTS3 is broken in reliase 3.6.21

[Dan Kennedy]

On Dec 10, 2009, at 1:13 PM, Alexey Pechnikov wrote:

> Hello!
>
> In new relise doesn't some queries. The simplest non-working query  
> is like to:
>
> sqlite> select 1 from file_text where rowid=1 and file_text match  
> 'document';
> Error: SQL logic error or missing database
>
> sqlite> select 1 from file_text where docid=1 and file_text match  
> 'document';
> Error: SQL logic error or missing database
>
> In previous versions these work fine.

Thanks. Fixed here:

   http://www.sqlite.org/src/vinfo/6cbbae849990d99b7ffe252b642d6be49d0c7235

Dan.

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

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Daniel Mierswa]

On 10.12.2009 07:44, Roger Binns wrote:
> sqlite3.h is not generated from a .in template.  Are you volunteering to do
> that and become the maintainer of it?
Nah I was merely wondering how/if it could be achieved at all.

> Secondly your solution would only work for autotools which not everyone uses.
True.

> Thirdly it wouldn't support the case of people supplying flags at the
> command line to the compiler which is the usual case.
Also true.

> SQLite 3 is shipped with a pkg-config data file so you could try to get the
> flags into that.
Hm that's probably a good idea, but I wouldn't know what to put in there
to fix the TB issue.

My concolusion is that the TB folks shouldn't assume that the system
sqlite library was built with debugging symbols or provide a mechanism
to opt out said function call with an easy switch/compiler flag.

Thanks for your attention and clarification on that matter. :)

-- 
Mierswa, Daniel

If you still don't like it, that's ok: that's why I'm boss. I simply
know better than you do.
   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
n
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compiling Sqlite with ICU

[Sylvain Pointeau]

seems I found:

./configure CFLAGS="-DSQLITE_ENABLE_ICU" LDFLAGS="-L/usr/local/lib"
LIBS="-licudata -licui18n -licuio -licule -liculx -licutu -licuuc"

is there any way to not list all the icu libs?

Best regards,
Sylvain

On Thu, Dec 10, 2009 at 4:09 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> ./configure CFLAGS="-DSQLITE_ENABLE_ICU
>
> but after I have a linker problem:
>
> /bin/sh ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1
>  -DSQLITE_ENABLE_ICU -I/usr/local/include  -L/usr/local/lib -o
> libsqlite3.la -rpath /usr/local/lib -no-undefined -version-info 8:6:8
> sqlite3.lo
> gcc -dynamiclib  -o .libs/libsqlite3.0.8.6.dylib  .libs/sqlite3.o
>  -L/usr/local/lib  -install_name  /usr/local/lib/libsqlite3.0.dylib
> -compatibility_version 9 -current_version 9.6
> Undefined symbols:
>   "_u_strToLower_4_2", referenced from:
>   _icuCaseFunc16 in sqlite3.o
>   "_u_strToUpper_4_2", referenced from:
>   _icuCaseFunc16 in sqlite3.o
>   "_u_errorName_4_2", referenced from:
>   _icuFunctionError in sqlite3.o
>   "_utf8_nextCharSafeBody_4_2", referenced from:
>   _icuLikeFunc in sqlite3.o
>   "_ucol_strcoll_4_2", referenced from:
>   _icuCollationColl in sqlite3.o
>   "_u_foldCase_4_2", referenced from:
>   _icuLikeCompare in sqlite3.o
>   _icuLikeCompare in sqlite3.o
>   "_utf8_countTrailBytes_4_2", referenced from:
>   _icuLikeCompare in sqlite3.o
>   _icuLikeCompare in sqlite3.o
>   _icuLikeCompare in sqlite3.o
>   _icuLikeCompare in sqlite3.o
>   _icuLikeCompare in sqlite3.o
>   "_ucol_close_4_2", referenced from:
>
>
> On Thu, Dec 10, 2009 at 3:55 PM, Sylvain Pointeau <
> sylvain.point...@gmail.com> wrote:
>
>> ... I don't have this line in my Makefile.in ...
>> Are you sure there is not an option to put in the "configure" command?
>>
>> Best regards,
>> Sylvain
>>
>>
>> On Thu, Dec 10, 2009 at 3:44 PM, Alexey Pechnikov > > wrote:
>>
>>> Hello!
>>>
>>> On Thursday 10 December 2009 17:40:24 Sylvain Pointeau wrote:
>>> > Thank you very much.
>>> > How should I do exactly with configure to enable this define ?
>>>
>>> You can simple replace the string
>>> TCC += -D_HAVE_SQLITE_CONFIG_H
>>> to
>>> TCC += -D_HAVE_SQLITE_CONFIG_H -DSQLITE_ENABLE_ICU
>>> in the Makefine.in
>>>
>>> Best regards, Alexey Pechnikov.
>>> http://pechnikov.tel/
>>> ___
>>> 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] compiling Sqlite with ICU

[Sylvain Pointeau]

./configure CFLAGS="-DSQLITE_ENABLE_ICU

but after I have a linker problem:

/bin/sh ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1
 -DSQLITE_ENABLE_ICU -I/usr/local/include  -L/usr/local/lib -o
libsqlite3.la-rpath /usr/local/lib -no-undefined -version-info 8:6:8
sqlite3.lo
gcc -dynamiclib  -o .libs/libsqlite3.0.8.6.dylib  .libs/sqlite3.o
 -L/usr/local/lib  -install_name  /usr/local/lib/libsqlite3.0.dylib
-compatibility_version 9 -current_version 9.6
Undefined symbols:
  "_u_strToLower_4_2", referenced from:
  _icuCaseFunc16 in sqlite3.o
  "_u_strToUpper_4_2", referenced from:
  _icuCaseFunc16 in sqlite3.o
  "_u_errorName_4_2", referenced from:
  _icuFunctionError in sqlite3.o
  "_utf8_nextCharSafeBody_4_2", referenced from:
  _icuLikeFunc in sqlite3.o
  "_ucol_strcoll_4_2", referenced from:
  _icuCollationColl in sqlite3.o
  "_u_foldCase_4_2", referenced from:
  _icuLikeCompare in sqlite3.o
  _icuLikeCompare in sqlite3.o
  "_utf8_countTrailBytes_4_2", referenced from:
  _icuLikeCompare in sqlite3.o
  _icuLikeCompare in sqlite3.o
  _icuLikeCompare in sqlite3.o
  _icuLikeCompare in sqlite3.o
  _icuLikeCompare in sqlite3.o
  "_ucol_close_4_2", referenced from:


On Thu, Dec 10, 2009 at 3:55 PM, Sylvain Pointeau <
sylvain.point...@gmail.com> wrote:

> ... I don't have this line in my Makefile.in ...
> Are you sure there is not an option to put in the "configure" command?
>
> Best regards,
> Sylvain
>
>
> On Thu, Dec 10, 2009 at 3:44 PM, Alexey Pechnikov 
> wrote:
>
>> Hello!
>>
>> On Thursday 10 December 2009 17:40:24 Sylvain Pointeau wrote:
>> > Thank you very much.
>> > How should I do exactly with configure to enable this define ?
>>
>> You can simple replace the string
>> TCC += -D_HAVE_SQLITE_CONFIG_H
>> to
>> TCC += -D_HAVE_SQLITE_CONFIG_H -DSQLITE_ENABLE_ICU
>> in the Makefine.in
>>
>> Best regards, Alexey Pechnikov.
>> http://pechnikov.tel/
>> ___
>> 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] compiling Sqlite with ICU

[Sylvain Pointeau]

... I don't have this line in my Makefile.in ...
Are you sure there is not an option to put in the "configure" command?

Best regards,
Sylvain

On Thu, Dec 10, 2009 at 3:44 PM, Alexey Pechnikov wrote:

> Hello!
>
> On Thursday 10 December 2009 17:40:24 Sylvain Pointeau wrote:
> > Thank you very much.
> > How should I do exactly with configure to enable this define ?
>
> You can simple replace the string
> TCC += -D_HAVE_SQLITE_CONFIG_H
> to
> TCC += -D_HAVE_SQLITE_CONFIG_H -DSQLITE_ENABLE_ICU
> in the Makefine.in
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] compiling Sqlite with ICU

[Alexey Pechnikov]

Hello!

On Thursday 10 December 2009 17:40:24 Sylvain Pointeau wrote:
> Thank you very much.
> How should I do exactly with configure to enable this define ?

You can simple replace the string
TCC += -D_HAVE_SQLITE_CONFIG_H
to
TCC += -D_HAVE_SQLITE_CONFIG_H -DSQLITE_ENABLE_ICU
in the Makefine.in

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] compiling Sqlite with ICU

[Sylvain Pointeau]

Hi,

Thank you very much.
How should I do exactly with configure to enable this define ?

Cheers,
Sylvain

On Thu, Dec 10, 2009 at 11:31 AM, Simon Davies <
simon.james.dav...@googlemail.com> wrote:

> 2009/12/10 Sylvain Pointeau :
> > Hi,
> >
> > I would like to use ICU with sqlite, I am on mac os x 10.6.2
> > How should I do? I installed ICU but sqlite3 seems to not check ICU when
> > compiling.
> > I would like to use ICU via the sqlite shell.
> >
> > Please could someone explain me how to use ICU with sqlite3 ?
>
> Have you found this compilation option?
>
> http://www.sqlite.org/compile.html#enable_icu
>
> >
> > Cheers,
> > Sylvain
>
> Regards,
> Simon
> ___
> 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] iPhone App close database

[Greg Walters]

Simon,
Thanks a lot. This is just what I needed.
Greg





From: Simon Slavin 
To: General Discussion of SQLite Database 
Sent: Wed, December 9, 2009 4:34:09 PM
Subject: Re: [sqlite] iPhone App close database


On 9 Dec 2009, at 11:13pm, Greg Walters wrote:

> I am looking for some general information regarding closing the sqlite 
> database when used from an iPhone app. Since you never know when you app will 
> go away, when do you close the database. I have found one example application 
> that closes the database after getting data and doing updates. Most never 
> close the database. What is the best way to handle this so in an iPhone app. 

Your application does get warned about important like that, and is expected to 
provide a routine to respond to each warning.  Look for

applicationWillTerminate

for quitting, which is the question you asked.  But also look for

applicationWillResignActive
applicationDidBecomeActive

since, if possible without too much extra work, I'd recommend that you also 
close files while the iPhone is handling external interruptions like phone 
calls, SMS notifications, and 'push' notifications.  You might find this useful:

http://developer.apple.com/iPhone/library/documentation/iPhone/Conceptual/iPhoneOSProgrammingGuide/ApplicationEnvironment/ApplicationEnvironment.html#//apple_ref/doc/uid/TP40007072-CH7-SW6

(that's all one long URL).

Simon.
___
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] rowid of the current insert

[Tim Romano]

You should keep your id and the imdbid in separate columns, because you 
can then insert a title even if IMDB does not have it yet.

id INTEGER PRIMARY KEY
imdbid integer// you should allow this column to be null
title  not null,
et cetera


Regards
Tim Romano


Yuzem wrote:
> CREATE TABLE movies(id integer,title text,unique(id))
>
> Generally I have an unique id from imdb but some times the movie isn't
> available.
> I understand that there is a column called rowid that is the primary key.
> I would like to insert the rowid on the id column when I don't have an id
> for the movie.
>
> Example:
> insert into movies values(rowid,'title1');
>
> How can I do that?
>
>
>   
> 
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.426 / Virus Database: 270.14.101/2555 - Release Date: 12/09/09 
> 19:41:00
>
>   

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

Re: [sqlite] rowid of the current insert

[Simon Davies]

2009/12/10 Yuzem :
>
> CREATE TABLE movies(id integer,title text,unique(id))
>
> Generally I have an unique id from imdb but some times the movie isn't
> available.
> I understand that there is a column called rowid that is the primary key.
> I would like to insert the rowid on the id column when I don't have an id
> for the movie.
>
> Example:
> insert into movies values(rowid,'title1');
>
> How can I do that?

create trigger moviesTrig after insert on movies when new.id is null
begin update movies set id=rowid where rowid=new.rowid; end;

and insert via:
insert into movies( title ) values( xxx );

But there is no guarantee that the new rowid will not clash with any
existing id...

>
>

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

[sqlite] rowid of the current insert

[Yuzem]

CREATE TABLE movies(id integer,title text,unique(id))

Generally I have an unique id from imdb but some times the movie isn't
available.
I understand that there is a column called rowid that is the primary key.
I would like to insert the rowid on the id column when I don't have an id
for the movie.

Example:
insert into movies values(rowid,'title1');

How can I do that?


-- 
View this message in context: 
http://old.nabble.com/rowid-of-the-current-insert-tp26725353p26725353.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] Index Problem

[Harish Dixit]

Hi,

One more information:
I am using COLLATE NoCase.

On Thu, Dec 10, 2009 at 4:19 PM, Harish Dixit wrote:

> Hi Friends,
>
> I ran into a big problem of creating indexes taking a long time. When i
> create index on multiple colomns then its taking a long time on few
> machines. Those are windows Vista machines. I am using following command:
>
> CREATE INDEX IF NOT EXISTS New_Index (name COLLATE NoCase ,title COLLATE
> NoCase)
>
> I tried with empty database also again its taking long time. Since indexes
> are already created then why its taking long time.
> Its taking time from 500-800 ms. while on other machines its taking less
> time 40-80 ms.
>
>
> Best Regards,
> Harish.
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index Problem

[Harish Dixit]

Hi Friends,

I ran into a big problem of creating indexes taking a long time. When i
create index on multiple colomns then its taking a long time on few
machines. Those are windows Vista machines. I am using following command:

CREATE INDEX IF NOT EXISTS New_Index (name,title)

I tried with empty database also again its taking long time. Since indexes
are already created then why its taking long time.
Its taking time from 500-800 ms. while on other machines its taking less
time 40-80 ms.


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

Re: [sqlite] compiling Sqlite with ICU

[Simon Davies]

2009/12/10 Sylvain Pointeau :
> Hi,
>
> I would like to use ICU with sqlite, I am on mac os x 10.6.2
> How should I do? I installed ICU but sqlite3 seems to not check ICU when
> compiling.
> I would like to use ICU via the sqlite shell.
>
> Please could someone explain me how to use ICU with sqlite3 ?

Have you found this compilation option?

http://www.sqlite.org/compile.html#enable_icu

>
> Cheers,
> Sylvain

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

[sqlite] compiling Sqlite with ICU

[Sylvain Pointeau]

Hi,

I would like to use ICU with sqlite, I am on mac os x 10.6.2
How should I do? I installed ICU but sqlite3 seems to not check ICU when
compiling.
I would like to use ICU via the sqlite shell.

Please could someone explain me how to use ICU with sqlite3 ?

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

Re: [sqlite] SQLite bug report - large databases only - 'database or disk is full'

[Filip Navara]

On Mon, Dec 7, 2009 at 10:21 PM,   wrote:
>
> SQLite bug report
>
> Summary:
> --
>
> error message:
>
>       Error: near line 2: database or disk is full
>
> It happens with plenty of disk space available and with 'unlimited' database 
> size.
> It does not happen on all systems. It does not happen on small databases.
>

I run your scripts with SQLite 3.6.21 on Windows 7 64-bit on NTFS
drive and unfortunately I run out of disk space before the problem
manifested. Would it be possible for you to setup Process Monitor with
filter on the database path (just the path, so both journal and the
main database file are in the log) and history depth set to 1 million
(the lowest value) and then capture the file accesses during the run
of the reproduction scripts? It is possible to save and export the
data then for further analysis and hopefully it will give a clue on
why it happens.

Best regards,
Filip Navara
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Implementing Regular Expression Support...?

[Dami Laurent (PJ)]

 

>-Message d'origine-
>De : sqlite-users-boun...@sqlite.org 
>[mailto:sqlite-users-boun...@sqlite.org] De la part de J. King
>Envoyé : mardi, 8. décembre 2009 18:24
>À : General Discussion of SQLite Database
>Objet : Re: [sqlite] Implementing Regular Expression Support...?
>
>On Tue, 08 Dec 2009 12:11:13 -0500, li...@mgreg.com   
>wrote:
>
>> Hi All,
>>
>> I'm currently using SQLITE in a few production apps.  I'm 
>using various  
>> languages such as Ruby, PERL, RB, etc.  I have need to use regular  
>> expressions in some of my queries, but I'm not sure how to 
>implement  
>> "user defined functionality".  Where are the hooks?  Is there a  
>> particular mechanism/language I must use to create them?  Is this  
>> something I'm required to recompile SQLITE for?
>
>You do so by defining a user function called 'regexp'.  The 
>means by which  
>one defines a user function depends on the language.  See, for 
>instance,  
>[1] for Ruby.  For a 'regexp' function you would specify two 
>arguments,  
>pattern and string to match against.
>
>[1]  
>ase.html#M000115>
>
>-- 
>J. King

Hi,

If you use Perl, make sure to get the latest version of DBD::SQLite, where the 
SQLite "regexp" function is automatically hooked to Perl regexes. See 
http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#REGEXP_function : 

SQLite includes syntactic support for an infix operator 'REGEXP', but without 
any implementation. The DBD::SQLite driver automatically registers an 
implementation that performs standard perl regular expression matching, using 
current locale. So for example you can search for words starting with an 'A' 
with a query like

  SELECT * from table WHERE column REGEXP '\bA\w+'

If you want case-insensitive searching, use perl regex flags, like this :

  SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users