[sqlite] how to compile DBD::SQLite with the latest source and FTS3

[P Kishor]

http://www.sqlite.org/cvstrac/wiki?p=CompilingFtsThree

works for me.

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fts3 complains no column named row_id

[Scott Hess]

On Mon, Feb 18, 2008 at 9:48 PM, P Kishor <[EMAIL PROTECTED]> wrote:
>  I get the error --
>
>  table fts_article has no column named row_id
>
>  Needless to say, this didn't happen with fts2. Any guidance would be
>  appreciated.

Are you sure?  fts2 didn't have row_id, either.  In either case, it
should be rowid.

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

[sqlite] fts3 complains no column named row_id

[P Kishor]

I am following the fts2 instructions in fts3. Assuming a table

CREATE TABLE article (article_id INTEGER PRIMARY KEY, article_name,
article_text);

I created

CREATE VIRTUAL TABLE fts_article USING fts3 (article_name, article_text);

But when I try to

INSERT INTO fts_article (row_id, article_name, article_text)
VALUES (article_d, article_name, article_text)

I get the error --

table fts_article has no column named row_id

Needless to say, this didn't happen with fts2. Any guidance would be
appreciated.

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

Re: [sqlite] Can more than one user connect to an in-memory database?

[Brian Smith]

Scott Chapman wrote:
> Can more than one user connect to an in-memory database?
> I don't see how.  Any clues?

If you have a memory filesystem (e.g. /dev/shm on Linux), you can just
create the database within that filesystem, and it will be "in-memory".

- Brian

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

Re: [sqlite] Outer join between two tables?

[Gilles]

At 19:37 18/02/2008 -0500, Igor Tandetnik wrote:
>What makes you feel the above is a non-standard solution? Or perhaps you 
>mean it is, in your opinion, sub-standard?

I didn't say it was, but in books on SQL, they usually talk about joints, 
hence my wondering if this were a more usual way of SELECTing rows that 
don't exist in a second table.

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

Re: [sqlite] Outer join between two tables?

[Igor Tandetnik]

"Gilles Ganault" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> On Mon, 18 Feb 2008 15:03:27 -0500, "Igor Tandetnik"
> <[EMAIL PROTECTED]> wrote:
>> where phones_contact_tel not in (select tel from contacts)
>
> Thanks, it worked:
>
> SELECT * FROM phones WHERE phones_tel NOT IN (SELECT
> contacts_phones_tel FROM contacts);
>
> Out of curiosity, isn't the standard solution to use JOIN instead of a
> sub-SELECT?

What makes you feel the above is a non-standard solution? Or perhaps you 
mean it is, in your opinion, sub-standard?

Igor Tandetnik 



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

Re: [sqlite] Outer join between two tables?

[Gilles Ganault]

On Mon, 18 Feb 2008 15:03:27 -0500, "Igor Tandetnik"
<[EMAIL PROTECTED]> wrote:
>where phones_contact_tel not in (select tel from contacts)

Thanks, it worked:

SELECT * FROM phones WHERE phones_tel NOT IN (SELECT
contacts_phones_tel FROM contacts);

Out of curiosity, isn't the standard solution to use JOIN instead of a
sub-SELECT?

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

Re: [sqlite] which is faster, PHP or SQLite?

[Sam Carleton]

On Feb 18, 2008 11:33 AM, Scott Baker <[EMAIL PROTECTED]> wrote:

>
> The less database hits you have to do, the faster your code will be.
> Getting all the data into a PHP data structure should be the way to go.

After reading all the replies, I have to agree with Scott for my
particular situation.  The dataset I am getting from SQLite is only
selected images, more often then not less then 10 records.  Storing
them in a PHP array and checking the array to see if it contains the
file that is being displayed seems the most speedy approach.

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

Re: [sqlite] Can more than one user connect to an in-memory database?

[Fin Springs]

> Subject: [sqlite] Can more than one user connect to an in-memory
database?
> I don't see how.  Any clues?

You could share the sqlite3* handle from the process that opens that
in-memory database, through some mechanism of your own. Since all your
users would be using the same connection, you would need to do your own
locking though. For example, I have an application on a mobile device
that uses an in-memory SQLite database. The application exposes a server
socket that lets me issue SQL to the database remotely, through some
wrapper functions in the application. Those functions have locking in
them to prevent using the database handle from two threads at once.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which is faster, PHP or SQLite?

[John Stanton]

Sqlite uses cacheing.  I would suggest not storing large amounts of data 
in PHP arrays.  It is buffer shadowing.  Ideally with Sqlite you would 
use a cursor (the sqlite3_step logic) and pick up rows as you need them 
from the Sqlite cache.

Digging a string of holes and filling them in is a tedious way to move a 
hole in the ground

Zbigniew Baniewski wrote:
> On Mon, Feb 18, 2008 at 08:33:49AM -0800, Scott Baker wrote:
> 
>> The less database hits you have to do, the faster your code will be. 
>> Getting all the data into a PHP data structure should be the way to go.
> 
> But, if one really is "loading all the data into memory at once" (just
> "SELECT * FROM xyz") - where are, actually, any benefits from using SQL
> database engine?
> 
> Using plain file you can have about the same:  open/read_all/close... done.

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

[sqlite] Can more than one user connect to an in-memory database?

[Scott Chapman]

I don't see how.  Any clues?

Thanks!
Scott

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

Re: [sqlite] SQLite Like Query Optimization

[drh]

"Kalyani Phadke" <[EMAIL PROTECTED]> wrote:
> I have not recompiled sqlite 3 before. I am having trouble to find
> documentation.
> 
> Could anyone pls tell me how can I compile SQLite3 source code on
> windows xp machine. Do I need to download FTS3 files ? Where can I find
> those files? How can I add this extension to my sqlite???
> 

  (1)  Download the amalgamation.
  (2)  Compile with -DSQLITE_ENABLE_FTS3=1

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] SQLite Like Query Optimization

[P Kishor]

On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>  I have not recompiled sqlite 3 before. I am having trouble to find
> documentation.
>
> Could anyone pls tell me how can I compile SQLite3 source code

http://www.sqlite.org/cvstrac/wiki?p=CompilingFts

> windows xp machine.

no idea about Windows.

> Do I need to download FTS3 files ? Where can I find
> those files? How can I add this extension to my sqlite???

The FTS source comes with the SQLite source (look in the ext folder).
See the link above for detailed steps that worked for me on a Mac.

>
> Thanks
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> Sent: Monday, February 18, 2008 11:10 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> >  I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
>
> > to enable FTS3?
>
> yes.
>
> >
> > From the command prompt I tried the following things
> >
> > Sqlite> .load libfts3.dll
> > Unable to open shared library libfts3.dll
> > Sqlite>select load_extension('libfts3.dll');
> > Sql error:unable to open shared library libfts3.dll
> >
> > So my question is The FTS3 module is available in SQLite version 3.5.6
>
> > and later?
> >
> > Thanks,
> >
> >
> >
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> > Sent: Monday, February 18, 2008 9:50 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite Like Query Optimization
> >
> > On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> > >
> > > Suppose User typed 'test' in search text box, I would like to search
>
> > > the 'test' string in all the coulmns ... I do not want exact match..
> > > The columns could contain strings like 'tester'  or 'tested' . I
> > > should be able to get these records as well..
> > >
> > > Hope I am clear explaining what I want..
> >
> > you definitely should look into implementing full-text search using
> > fts3. It will solve your problems as well as world peace.
> >
> >
> > >
> > > -Thanks
> > >
> > >
> > > -Original Message-
> > > From: [EMAIL PROTECTED]
> > > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> > > Sent: Friday, February 15, 2008 4:38 PM
> > > To: General Discussion of SQLite Database
> > > Subject: Re: [sqlite] SQLite Like Query Optimization
> > >
> > > Hi Kalyani,
> > >
> > > > select ID from TableA where column2 like '%test%'  or column4like
> > > > '%test%' or column5 like '%test%' or column6 like '%test%' or
> > > > column7 like '%test%'  or column8 like '%test%' order by column3
> > > > desc;
> > >
> > > As already stated, the like operator can't use indexes if you use
> > > "or", or start with a wild card.
> > >
> > > Is each '%test%' in your example meant to be the same string, or
> > > different strings? If different, then what exactly is each column
> > > storing, and what are you trying to search for? Perhaps you could
> > > make
> >
> > > each column more "atomic" by splitting the contents into more
> > > columns,
> >
> > > which you could then search using "=" instead of "like" and so use
> > > indexes.
> > >
> > > Tom
> > > BareFeet
> > >
> > >   --
> > > One stop Australian on-line shop for Macs and accessories
> > > http://www.tandb.com.au/forsale/?ml
> > >
> > > ___
> > > 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-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-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] which is faster, PHP or SQLite?

[Zbigniew Baniewski]

On Mon, Feb 18, 2008 at 08:33:49AM -0800, Scott Baker wrote:

> The less database hits you have to do, the faster your code will be. 
> Getting all the data into a PHP data structure should be the way to go.

But, if one really is "loading all the data into memory at once" (just
"SELECT * FROM xyz") - where are, actually, any benefits from using SQL
database engine?

Using plain file you can have about the same:  open/read_all/close... done.
-- 
pozdrawiam / regards

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

Re: [sqlite] SQLite Like Query Optimization

[Kalyani Phadke]

 I have not recompiled sqlite 3 before. I am having trouble to find
documentation.

Could anyone pls tell me how can I compile SQLite3 source code on
windows xp machine. Do I need to download FTS3 files ? Where can I find
those files? How can I add this extension to my sqlite???

Thanks

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Monday, February 18, 2008 11:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Like Query Optimization

On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>  I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3

> to enable FTS3?

yes.

>
> From the command prompt I tried the following things
>
> Sqlite> .load libfts3.dll
> Unable to open shared library libfts3.dll
> Sqlite>select load_extension('libfts3.dll');
> Sql error:unable to open shared library libfts3.dll
>
> So my question is The FTS3 module is available in SQLite version 3.5.6

> and later?
>
> Thanks,
>
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> Sent: Monday, February 18, 2008 9:50 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> >
> > Suppose User typed 'test' in search text box, I would like to search

> > the 'test' string in all the coulmns ... I do not want exact match..
> > The columns could contain strings like 'tester'  or 'tested' . I 
> > should be able to get these records as well..
> >
> > Hope I am clear explaining what I want..
>
> you definitely should look into implementing full-text search using 
> fts3. It will solve your problems as well as world peace.
>
>
> >
> > -Thanks
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED] 
> > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> > Sent: Friday, February 15, 2008 4:38 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite Like Query Optimization
> >
> > Hi Kalyani,
> >
> > > select ID from TableA where column2 like '%test%'  or column4like 
> > > '%test%' or column5 like '%test%' or column6 like '%test%' or
> > > column7 like '%test%'  or column8 like '%test%' order by column3 
> > > desc;
> >
> > As already stated, the like operator can't use indexes if you use 
> > "or", or start with a wild card.
> >
> > Is each '%test%' in your example meant to be the same string, or 
> > different strings? If different, then what exactly is each column 
> > storing, and what are you trying to search for? Perhaps you could 
> > make
>
> > each column more "atomic" by splitting the contents into more 
> > columns,
>
> > which you could then search using "=" instead of "like" and so use 
> > indexes.
> >
> > Tom
> > BareFeet
> >
> >   --
> > One stop Australian on-line shop for Macs and accessories 
> > http://www.tandb.com.au/forsale/?ml
> >
> > ___
> > 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-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-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] Outer join between two tables?

[Igor Tandetnik]

"Gilles Ganault" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> Since the syntax "WHERE phones_contact_tel not in contacts;" doesn't
> seem to exist

But this does:

where phones_contact_tel not in (select tel from contacts)

Igor Tandetnik 



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

Re: [sqlite] SQLite Like Query Optimization

[P Kishor]

On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>  I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
> to enable FTS3?

yes.

>
> From the command prompt I tried the following things
>
> Sqlite> .load libfts3.dll
> Unable to open shared library libfts3.dll
> Sqlite>select load_extension('libfts3.dll');
> Sql error:unable to open shared library libfts3.dll
>
> So my question is The FTS3 module is available in SQLite version 3.5.6
> and later?
>
> Thanks,
>
>
>
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
> Sent: Monday, February 18, 2008 9:50 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
> >
> > Suppose User typed 'test' in search text box, I would like to search
> > the 'test' string in all the coulmns ... I do not want exact match..
> > The columns could contain strings like 'tester'  or 'tested' . I
> > should be able to get these records as well..
> >
> > Hope I am clear explaining what I want..
>
> you definitely should look into implementing full-text search using
> fts3. It will solve your problems as well as world peace.
>
>
> >
> > -Thanks
> >
> >
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> > Sent: Friday, February 15, 2008 4:38 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] SQLite Like Query Optimization
> >
> > Hi Kalyani,
> >
> > > select ID from TableA where column2 like '%test%'  or column4like
> > > '%test%' or column5 like '%test%' or column6 like '%test%' or
> > > column7 like '%test%'  or column8 like '%test%' order by column3
> > > desc;
> >
> > As already stated, the like operator can't use indexes if you use
> > "or", or start with a wild card.
> >
> > Is each '%test%' in your example meant to be the same string, or
> > different strings? If different, then what exactly is each column
> > storing, and what are you trying to search for? Perhaps you could make
>
> > each column more "atomic" by splitting the contents into more columns,
>
> > which you could then search using "=" instead of "like" and so use
> > indexes.
> >
> > Tom
> > BareFeet
> >
> >   --
> > One stop Australian on-line shop for Macs and accessories
> > http://www.tandb.com.au/forsale/?ml
> >
> > ___
> > 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-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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Counting rows in multiple tables and joining on an index

[Trey Mack]

create table t1 (rpsIndex INTEGER);
create table t2 (rpsIndex INTEGER);
create table t3 (rpsIndex INTEGER);

insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (2);
insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (3);
insert into t3 values (3);
insert into t3 values (3);
insert into t3 values (3);

select rpsIndex, count(1) frequency from
(
select rpsIndex from t1
union all
select rpsIndex from t2
union all
select rpsIndex from t3
)
group by rpsIndex;

- Trey

jrpfinch wrote:
> I have three tables, each of which has the column rpsIndex.  This column is
> not a unique index/primary key.
>
> I would like to count the number of times each rpsIndex appears in all three
> tables. E.g.
>
> Table1
> rpsIndex=1
> rpsIndex=1
> rpsIndex=2
> Table2
> rpsIndex=1
> rpsIndex=2
> rpsIndex=3
> Table3
> rpsIndex=3
> rpsIndex=3
> rpsIndex=3
>
> Query would return:
> rpsIndex  Frequency
> 13
> 22
> 34
>
> It is possible to do this in pure SQL in SQLite?
>
> Many thanks
>
> jon
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Like Query Optimization

[Kalyani Phadke]

 I am trying to use FTS3 with SQlite3 . Do I need to recompile SQlite3
to enable FTS3?

>From the command prompt I tried the following things

Sqlite> .load libfts3.dll
Unable to open shared library libfts3.dll
Sqlite>select load_extension('libfts3.dll');
Sql error:unable to open shared library libfts3.dll

So my question is The FTS3 module is available in SQLite version 3.5.6
and later?

Thanks,





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of P Kishor
Sent: Monday, February 18, 2008 9:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Like Query Optimization

On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>
> Suppose User typed 'test' in search text box, I would like to search 
> the 'test' string in all the coulmns ... I do not want exact match.. 
> The columns could contain strings like 'tester'  or 'tested' . I 
> should be able to get these records as well..
>
> Hope I am clear explaining what I want..

you definitely should look into implementing full-text search using
fts3. It will solve your problems as well as world peace.


>
> -Thanks
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> Sent: Friday, February 15, 2008 4:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> Hi Kalyani,
>
> > select ID from TableA where column2 like '%test%'  or column4like 
> > '%test%' or column5 like '%test%' or column6 like '%test%' or 
> > column7 like '%test%'  or column8 like '%test%' order by column3 
> > desc;
>
> As already stated, the like operator can't use indexes if you use 
> "or", or start with a wild card.
>
> Is each '%test%' in your example meant to be the same string, or 
> different strings? If different, then what exactly is each column 
> storing, and what are you trying to search for? Perhaps you could make

> each column more "atomic" by splitting the contents into more columns,

> which you could then search using "=" instead of "like" and so use 
> indexes.
>
> Tom
> BareFeet
>
>   --
> One stop Australian on-line shop for Macs and accessories 
> http://www.tandb.com.au/forsale/?ml
>
> ___
> 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-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] Sqlite Version 3.2.2

[jose isaias cabrera]

Walt,

I am doing it with 10 users, right now...  However, if you read Dr. Hipp's 
reply, he is correct and that is the way to go.  I will be doing what Dr. 
Hipp said in a few months.

josé

- Original Message - 
From: "Walt" <[EMAIL PROTECTED]>
To: 
Sent: Monday, February 18, 2008 1:13 PM
Subject: [sqlite] Sqlite Version 3.2.2


> Is it feasible to a DataBase on one computer and have multiple users on a 
> LAN accessing the DataBase at the same time?
>
> Thanks for your input
>
> Walt Mc Whirter
> ___
> 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] Sqlite Version 3.2.2

[drh]

"Walt" <[EMAIL PROTECTED]> wrote:
> Is it feasible to a DataBase on one computer and have multiple
> users on a LAN accessing the DataBase at the same time?
> 

This can be made to work.  But you will probably be much happier
with a client/server database such as MySQL or PostgreSQL.  They
are designed for concurrent network access from multiple clients.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

[sqlite] Sqlite Version 3.2.2

[Walt]

Is it feasible to a DataBase on one computer and have multiple users on a LAN 
accessing the DataBase at the same time?

Thanks for your input

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

Re: [sqlite] which is faster, PHP or SQLite?

[Kees Nuyt]

On Mon, 18 Feb 2008 11:25:16 -0500, you wrote:

>I am new to SQLite and databases, so I am stil learning how to
>optimize their use...
>
>I am working on a "shopping cart" type of feature, it is actually a
>favorites feature for a system that displays images in multiple
>galleries.  There is a SQLite table that contains the user_id,
>gallery_id, and image_id.  When a index page is displayed, only a sub
>set of the images in the gallery are displayed.  So the question is
>what will be faster:
>
>1: Doing a SELECT for each image on the favorites table to see if it is 
>selected
>2: Doing one SELECT to get all the images for the current gallery and
>store that into a PHP array and then simply look in the PHP for each
>image?
>
>My thought is option 2.  Is that correct?

Yes.

>Sam

Since a gallery usually has more pictures than will
fit on one index page, and you probably want the users
to be able to jump to the next page, this page might
be of interest for you:

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Counting rows in multiple tables and joining on an index

[jrpfinch]

I have three tables, each of which has the column rpsIndex.  This column is
not a unique index/primary key.

I would like to count the number of times each rpsIndex appears in all three
tables. E.g.

Table1
rpsIndex=1
rpsIndex=1
rpsIndex=2
Table2
rpsIndex=1
rpsIndex=2
rpsIndex=3
Table3
rpsIndex=3
rpsIndex=3
rpsIndex=3

Query would return:
rpsIndex  Frequency
13
22
34

It is possible to do this in pure SQL in SQLite?

Many thanks

jon
-- 
View this message in context: 
http://www.nabble.com/Counting-rows-in-multiple-tables-and-joining-on-an-index-tp15547081p15547081.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] SQLite Like Query Optimization

[P Kishor]

On 2/18/08, Kalyani Phadke <[EMAIL PROTECTED]> wrote:
>
> Suppose User typed 'test' in search text box, I would like to search the
> 'test' string in all the coulmns ... I do not want exact match.. The
> columns could contain strings like 'tester'  or 'tested' . I should be
> able to get these records as well..
>
> Hope I am clear explaining what I want..

you definitely should look into implementing full-text search using
fts3. It will solve your problems as well as world peace.


>
> -Thanks
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
> Sent: Friday, February 15, 2008 4:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite Like Query Optimization
>
> Hi Kalyani,
>
> > select ID from TableA where column2 like '%test%'  or column4like
> > '%test%' or column5 like '%test%' or column6 like '%test%' or column7
> > like '%test%'  or column8 like '%test%' order by column3 desc;
>
> As already stated, the like operator can't use indexes if you use "or",
> or start with a wild card.
>
> Is each '%test%' in your example meant to be the same string, or
> different strings? If different, then what exactly is each column
> storing, and what are you trying to search for? Perhaps you could make
> each column more "atomic" by splitting the contents into more columns,
> which you could then search using "=" instead of "like" and so use
> indexes.
>
> Tom
> BareFeet
>
>   --
> One stop Australian on-line shop for Macs and accessories
> http://www.tandb.com.au/forsale/?ml
>
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] which is faster, PHP or SQLite?

[Scott Baker]

Michael Hooker wrote:
> This is an issue which interests me too.  The answer Scott gives makes 
> absolute sense to me, but all the PHP/MySQL books I've seen (and MySQL can't 
> be that different from Sqlite3 in this respect) seem to go the way of a new 
> query to the database every time a different set of data is needed, rather 
> than keeping all the data in an array, even with small inconsequential 
> tables. I suppose if there is a possibility that the db tables may be 
> updated while the user is online this approach is valid.  Or maybe there is 
> a downside to PHP arrays, I can see that a huge array might have memory 
> implications, especially on a busy shared server.

My experience has been that each sqlite DB hit on my server takes 
about .01 seconds to run. So if you're hitting that DB 20 times to 
get the 20 most popular images that really adds up.

I've spent countless hours optimizing my site to do things like:

SELECT * FROM Entry WHERE ID IN (1,2,3,4);

instead of

SELECT * FROM Entry WHERE ID = 1;
SELECT * FROM Entry WHERE ID = 2;
SELECT * FROM Entry WHERE ID = 3;
SELECT * FROM Entry WHERE ID = 4;

If you can (sometimes its hard programatically) reducing the number 
of database hits will always speed up your application.

Unless of course the data you're loading in RAM is huge. When you're 
storing megs of data in ram just to speed up your queries you 
probably should look at other routes for optimization.

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Like Query Optimization

[Kalyani Phadke]

 
Suppose User typed 'test' in search text box, I would like to search the
'test' string in all the coulmns ... I do not want exact match.. The
columns could contain strings like 'tester'  or 'tested' . I should be
able to get these records as well..

Hope I am clear explaining what I want..

-Thanks

 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of BareFeet
Sent: Friday, February 15, 2008 4:38 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite Like Query Optimization

Hi Kalyani,

> select ID from TableA where column2 like '%test%'  or column4like 
> '%test%' or column5 like '%test%' or column6 like '%test%' or column7 
> like '%test%'  or column8 like '%test%' order by column3 desc;

As already stated, the like operator can't use indexes if you use "or",
or start with a wild card.

Is each '%test%' in your example meant to be the same string, or
different strings? If different, then what exactly is each column
storing, and what are you trying to search for? Perhaps you could make
each column more "atomic" by splitting the contents into more columns,
which you could then search using "=" instead of "like" and so use
indexes.

Tom
BareFeet

  --
One stop Australian on-line shop for Macs and accessories
http://www.tandb.com.au/forsale/?ml

___
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] which is faster, PHP or SQLite?

[Michael Hooker]

This is an issue which interests me too.  The answer Scott gives makes 
absolute sense to me, but all the PHP/MySQL books I've seen (and MySQL can't 
be that different from Sqlite3 in this respect) seem to go the way of a new 
query to the database every time a different set of data is needed, rather 
than keeping all the data in an array, even with small inconsequential 
tables. I suppose if there is a possibility that the db tables may be 
updated while the user is online this approach is valid.  Or maybe there is 
a downside to PHP arrays, I can see that a huge array might have memory 
implications, especially on a busy shared server.

Michael Hooker

- Original Message - 
From: "Scott Baker" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" 
Sent: Monday, February 18, 2008 4:33 PM
Subject: Re: [sqlite] which is faster, PHP or SQLite?


> Sam Carleton wrote:
>> I am new to SQLite and databases, so I am stil learning how to
>> optimize their use...
>>
>> I am working on a "shopping cart" type of feature, it is actually a
>> favorites feature for a system that displays images in multiple
>> galleries.  There is a SQLite table that contains the user_id,
>> gallery_id, and image_id.  When a index page is displayed, only a sub
>> set of the images in the gallery are displayed.  So the question is
>> what will be faster:
>>
>> 1: Doing a SELECT for each image on the favorites table to see if it is 
>> selected
>> 2: Doing one SELECT to get all the images for the current gallery and
>> store that into a PHP array and then simply look in the PHP for each
>> image?
>>
>> My thought is option 2.  Is that correct?
>
> The less database hits you have to do, the faster your code will be.
> Getting all the data into a PHP data structure should be the way to go.
>
>
> -- 
> Scott Baker - Canby Telcom
> RHCE - System Administrator - 503.266.8253
> ___
> 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] which is faster, PHP or SQLite?

[Paul Smith]

At 16:25 18/02/2008, you wrote:
>I am new to SQLite and databases, so I am stil learning how to
>optimize their use...
>
>I am working on a "shopping cart" type of feature, it is actually a
>favorites feature for a system that displays images in multiple
>galleries.  There is a SQLite table that contains the user_id,
>gallery_id, and image_id.  When a index page is displayed, only a sub
>set of the images in the gallery are displayed.  So the question is
>what will be faster:
>
>1: Doing a SELECT for each image on the favorites table to see if it 
>is selected
>2: Doing one SELECT to get all the images for the current gallery and
>store that into a PHP array and then simply look in the PHP for each
>image?
>
>My thought is option 2.  Is that correct?

My thought is that it would depend.

I'd guess that If you have 100 images, and you are wanting to show 
20, then (2) may be quicker, but if you have 1,000,000 images, then 
(1) would be quicker. (Assuming you have a usable index on the table).

Leaving aside possible database design considerations, it's generally 
best to let the database engine do the work if it can.

With SQLite, I'd qualify that to say that it's best to let SQLite do 
the work if the queries are simple enough that its optimiser will use 
indices to do the work. We have found that it can be quicker to do 
things partially in SQLite and partially in C++. When SQLite would 
have to do a sequential scan to get the result, it can be quicker to 
do, say, two indexed scans in SQLite and then operate on the two 
result sets (eg doing a union or intersect) to produce the final 
result set, but this is the exception rather than the rule (for us anyway).


Paul Smith


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

Re: [sqlite] which is faster, PHP or SQLite?

[Scott Baker]

Sam Carleton wrote:
> I am new to SQLite and databases, so I am stil learning how to
> optimize their use...
> 
> I am working on a "shopping cart" type of feature, it is actually a
> favorites feature for a system that displays images in multiple
> galleries.  There is a SQLite table that contains the user_id,
> gallery_id, and image_id.  When a index page is displayed, only a sub
> set of the images in the gallery are displayed.  So the question is
> what will be faster:
> 
> 1: Doing a SELECT for each image on the favorites table to see if it is 
> selected
> 2: Doing one SELECT to get all the images for the current gallery and
> store that into a PHP array and then simply look in the PHP for each
> image?
> 
> My thought is option 2.  Is that correct?

The less database hits you have to do, the faster your code will be. 
Getting all the data into a PHP data structure should be the way to go.


-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] which is faster, PHP or SQLite?

[Sam Carleton]

I am new to SQLite and databases, so I am stil learning how to
optimize their use...

I am working on a "shopping cart" type of feature, it is actually a
favorites feature for a system that displays images in multiple
galleries.  There is a SQLite table that contains the user_id,
gallery_id, and image_id.  When a index page is displayed, only a sub
set of the images in the gallery are displayed.  So the question is
what will be faster:

1: Doing a SELECT for each image on the favorites table to see if it is selected
2: Doing one SELECT to get all the images for the current gallery and
store that into a PHP array and then simply look in the PHP for each
image?

My thought is option 2.  Is that correct?

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

Re: [sqlite] How to compile SQLite for Windows 32bit & 64bit?

[Brad House]

> Please Help
> 
> Is there a good tutorial that shows how to compile SQLite for both
> Windows 32 bit & 64bit? 
> 
> If ICU is required then I need the files to be statically linked rather
> than having any external dependencies.
> 
> Anyone know of a good tutorial for building  both a 32bit & 64bit DLL
> with any decencies statically linked?

Have you considered just adding the sqlite amalgamation to your project
and compiling it in?  That's how my company uses SQLite and supports both
32bit and 64bit windows.
Doesn't get much easier than a single .c file and a single .h file.

Also, there is a binary-form sqlite DLL that only depends on the standard
windows C library MSVCRT.DLL (that comes with Visual Studio 6, but I think
most versions of Windows ship with it too, afaik).  I don't think you can get
rid of that dependency without risking other issues with duplicate symbols.

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

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

[Gilles Ganault]

On Mon, 18 Feb 2008 12:32:55 +,
[EMAIL PROTECTED] wrote:
>The AUTOINCREMENT keyword prevents an primary key from being
>reused even after it is deleted.

Thanks for the clarification.

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

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

[Gilles Ganault]

On Mon, 18 Feb 2008 23:31:32 +1100, BareFeet
<[EMAIL PROTECTED]> wrote:
>I can create a view to display the date in my localtime:

Thanks much for the example.

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

[sqlite] How to compile SQLite for Windows 32bit & 64bit?

[Jim Harkins]

Please Help

Is there a good tutorial that shows how to compile SQLite for both
Windows 32 bit & 64bit? 

If ICU is required then I need the files to be statically linked rather
than having any external dependencies.

Anyone know of a good tutorial for building  both a 32bit & 64bit DLL
with any decencies statically linked?

Thanks,
Jim

___
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] Outer join between two tables?

[Gilles Ganault]

Hello

I have two tables:

- Phones, which has two columns: Tel and Name. A customer may have
more than one number

- Contacts, which has the expected colums (address, e-mail, etc.),
and, since phone numbers are unique,  uses the main (or only) number
in Phones as primary key. IOW, to find a contact's name, I take its
phone number in Contacts.Phones_Tel, look it up in table Phones, and
fetch its Phones.Phones_Name.

I'd like to create records in table Contacts by reading each record in
Phones, and adding those records into Contacts that don't exist yet.

Since the syntax "WHERE phones_contact_tel not in contacts;" doesn't
seem to exist, I assume I must use OUTER JOIN, but I've never used
this before.

=> Can someone tell me how to read each line in Phones and Contacts,
extract those that exist in Phones but not in Contacts, and insert
those into Contacts?

Thank you.

Here's the schema:

sqlite> CREATE TABLE phones (phones_tel TEXT PRIMARY KEY NOT NULL,
phones_name TEXT NOT NULL);

sqlite> CREATE TABLE contacts (contacts_phones_tel TEXT PRIMARY KEY
NOT NULL, contacts_address TEXT, contacts_zip TEXT, contacts_city
TEXT, contacts_fax TEXT, contacts_email TEXT, contacts_comment TEXT);

sqlite> CREATE TEMP TABLE phones_contacts (contacts_phones_tel TEXT
PRIMARY KEY NOT NULL, contacts_address TEXT, contacts_zip TEXT,
contacts_city TEXT, contacts_fax TEXT, contacts_email TEXT,
contacts_comment TEXT);

(what I'd like to do)
sqlite> INSERT INTO phones_contacts SELECT
phones_tel,NULL,NULL,NULL,NULL,NULL,NULL FROM phones,contacts WHERE
phones_contact_tel not in contacts;

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

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

[drh]

Gilles Ganault <[EMAIL PROTECTED]> wrote:
> On Sat, 16 Feb 2008 11:29:29 +1100, BareFeet
> <[EMAIL PROTECTED]> wrote:
> > If you designate an integer column as also being the primary key,
> > then SQLite will auto assign its  value incrementally each time
> > you insert a new row, unless you assign  a value explicitly.
> 
> In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"?
> Shouldn't "PRIMARY KEY" be enough to have SQLite auto-increment this
> column if it's NULL?

The AUTOINCREMENT keyword prevents an primary key from being
reused even after it is deleted.  Consider:

   /* 1 */  INSERT INTO table(pk, x) VALUES(NULL, 'row one');
   /* 2 */  DELETE FROM table WHERE x='row one';
   /* 3 */  INSERT INTO table(pk, x) VALUES(NULL, 'row two');

If pk is just a PRIMARY KEY (without AUTOINCREMENT) then the inserts
at 1 and 3 will both create rows with the same primary key value.  But
if the AUTOINCREMENT keyword is used, then the insert at 3 will have
a distinct primary key value from the insert at 1.

--
D. Richard Hipp <[EMAIL PROTECTED]>

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

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

[BareFeet]

Hi Gilles,

>> If you designate an integer column as also being the primary key,  
>> then SQLite will auto assign its  value incrementally each time you  
>> insert a new row, unless you assign  a value explicitly.
>
> In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"?

Adding "autoincrement" just ensures that once a number is used, it  
won't be used again. It's a mis-nomer, in my opinion since, as you  
point out, just "integer primary key" will auto increase the value.  
The web site explains it this way:

>> http://www.sqlite.org/lang_createtable.html
>> An INTEGER PRIMARY KEY column can also include the keyword  
>> AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B- 
>> Tree keys are automatically generated. Additional detail on  
>> automatic B-Tree key generation is available separately.
>> http://www.sqlite.org/autoinc.html
>> If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a  
>> slightly different ROWID selection algorithm is used. The ROWID  
>> chosen for the new row is one larger than the largest ROWID that  
>> has ever before existed in that same table.
>> The behavior implemented by the AUTOINCREMENT keyword is subtly  
>> different from the default behavior. With AUTOINCREMENT, rows with  
>> automatically selected ROWIDs are guaranteed to have ROWIDs that  
>> have never been used before by the same table in the same database.  
>> And the automatically generated ROWIDs are guaranteed to be  
>> monotonically increasing. These are important properties in certain  
>> applications. But if your application does not need these  
>> properties, you should probably stay with the default behavior  
>> since the use of AUTOINCREMENT requires additional work to be done  
>> as each row is inserted and thus causes INSERTs to run a little  
>> slower.

> Shouldn't "PRIMARY KEY" be enough to have SQLite auto-increment this  
> column if it's NULL?

Yes. As above, I think you only use the autoincrement additional  
option of you have a specific reason to do so.

>> I wouldn't expect any problems using dates in the -MM-DD  
>> format. But Julian (real) format is the preferred method and uses  
>> less memory (less bytes) and I suspect is quicker to process.
>
> Thanks for the tip.
>
>> either by creating a view in SQLite (and having PHP reference it) or
>> else using directly from PHP.
>
> I've never used views in SQLite. I'll take a look at what they do  
> and if they offer a better solution.

It's pretty straight forward. If, say, you have a table:

create table MyTable( Birth date );

and you populate it using the preferred julianday format:

insert into MyTable
values ( julianday( '2008-02-18 23:31', 'utc' ))
;

Note the utc option converts my local time to central greenwich time,  
so is globally relevant.

It will store global time as a real: 2454515.02152778  which  
represents the time I sent this email, applicable to all of earth.

I can create a view to display the date in my localtime:

create temporary view MyView
as
select datetime( Birth, 'localtime' ) as Birth
from MyTable
;

And from PHP, or wherever, I can select from the view, just like I  
would a table:

select Birth from MyView;

which gives me the original:

2008-02-18 23:31:00

since I've in the same time zone as where the date was entered.

You can use just plain "date" instead of "datetime" function to just  
show the date without the time.

If you don't care about time zones, you can omit the utc and localtime  
parameters.

Tom
BareFeet

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

Re: [sqlite] PRIMARY KEY? Date and time datatypes?

[Gilles Ganault]

On Sat, 16 Feb 2008 11:29:29 +1100, BareFeet
<[EMAIL PROTECTED]> wrote:
> If you designate an integer column as also being the primary key,
> then SQLite will auto assign its  value incrementally each time
> you insert a new row, unless you assign  a value explicitly.

In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"?
Shouldn't "PRIMARY KEY" be enough to have SQLite auto-increment this
column if it's NULL?

>I wouldn't expect any problems using dates in the -MM-DD format.  
>But Julian (real) format is the preferred method and uses less memory  
>(less bytes) and I suspect is quicker to process.

Thanks for the tip.

>either by creating a view in SQLite (and having PHP reference it) or  
>else using directly from PHP.

I've never used views in SQLite. I'll take a look at what they do and
if they offer a better solution. Thanks.

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