Re: [sqlite] Return Value from sqlite3_exec()

[Stephen Chrzanowski]

Which is where you put into the decision 'black box' of your program a
check to see how many rows were modified.  If something was supposed to be
changed, but zero rows were updated, throw an alert.  If one or a
reasonable amount were changed, don't inform the user.  If more than a
reasonable amount of rows were changed, inform the user that there could
have been a problem.  Albeit, you're correct if you're going to be alerting
the user to TOO many rows changing, you MIGHT want to open a transaction,
count the affected rows, then confirm the change..

In an N-Tiered application, (Data, Business, and User Interface being the
basics) the database did its job successfully.  Updated all rows you
required to be changed.  Because it was zero rows means nothing to that
tier (The data tier) of your application.  However, to the business logic
MIGHT care that zero rows were affected, which in this case, the UI might
need to be told about it.


On Fri, Feb 22, 2013 at 6:06 PM, Frederick Wasti  wrote:

> Thanks for your replies. What you have all said makes sense (and, in my
> defense, I did suggest that a nonsense - but properly formed - SQL
> statement could still be processed "OK"). :-)
>
> I first noticed this situation when studying the sample code for the
> "SmartDB" C++ wrapper for SQLite. The author's code uses the return value
> from sqlite3_exec() to report to the user whether the SQL entered by the
> user succeeded or not. Having a msgbox report to the user that the "Query
> was executed successfully" is actually, from a software ~user's~ point of
> view, rather misleading.
>
> From a ~user's~ point of view, I think that if the code tested for the
> possibility of ~not~ actually being able to modify a DB with an attempted
> UPDATE query (perhaps by internally using a SELECT statement first, just to
> see if the UPDATE's WHERE condition would even be feasible) might be a good
> strategy. [The ~user~ might just like to be informed that the ~expected~ DB
> write did ~not~ occur, even if the SQL looked superficially OK (and indeed
> was properly processed by sqlite3_exec() "OK", too).]
>
> Thanks.
>
> Fred
>
> ___
> 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] Question about aggregate returning empty row

[Pavel Ivanov]

That's SQL standard -- query with aggregate functions always return at
least one row.

Pavel

On Fri, Feb 22, 2013 at 12:06 PM, Pierre Chatelier  
wrote:
> Hello,
>
> [tested under 3.6.12 and 3.7.15.2]
>
> I have a question regarding the use of aggregate functions.
>
> Let's imagine the following db :
>>create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER);
> It is empty.
>
> The following query :
>>select id1,id2 from A;
> returns nothing, there is no row.
>
> However, the following query, using the aggregate min() :
>>select min(id1),id2 from A;
> returns an empty line (displays '|' in the shell).
>
> Using avg(), max()... will do the same.
>
> With the C interface, SQLITE_ROW is returned, and I must test 
> sqlite_column_type() against SQLITE_NULL to check that in fact, there is no 
> result.
>
> Is this expected behaviour ?
>
> Regards,
>
> Pierre Chatelier
> ___
> 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] Ongoing SELECT sees INSERTed rows.

[James K. Lowden]

On Thu, 21 Feb 2013 10:32:51 -0800 (PST)
Peter Aronson  wrote:

> an outer select loop was reading records, and an inner select loop
> was inserting records based on the records found into the same
> table.  

For every row, this design requires 2 trips to the database.  Besides
being inefficient, I suspect it's unnecessary.  The problem you
encountered goes away if you use your application to construct sets of
information with which to update the database.  

If, as you say, the inserted row is "based on the the records found", is
it not possible to issue just one insert statement instead, something
like

insert into new_rows
select ... from old_rows
where key between x and y;

If not -- if the application is adding information -- then could you
insert *only* that information (into a different table) and then

begin transaction;

insert into new_rows
select ... from old_rows
natural join other_table 
where key between x and y;

delete other_table;

commit;

For N rows, that would save as much as 2N-1 database calls *and* solve
the "new rows" surprise.  

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

Re: [sqlite] column totals

[Clemens Ladisch]

Paul Sanderson wrote:
> SELECT cat, COUNT(*) AS occ, COUNT(DISTINCT tes) AS uni, COUNT(tag) AS
> tagged FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu
>
> The table would look something like
>
> 1 54 3
> 2 26 4
> 3 56 8
>
> I want to modify the above sql query to sum the second and third columns
> and get a resultant table something like
>
> 1 54 3
> 2 26 4
> 3 56 8
> tot  136  15

Use UNION to add a second subquery without grouping:

SELECT ...your query...
UNION ALL
SELECT 'tot', COUNT(*), COUNT(DISTINCT tes), COUNT(tag)
FROM table
WHERE qu BETWEEN 1 AND 3


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

[sqlite] column totals

[Paul Sanderson]

I have the following query that produces a summary table

SELECT cat, COUNT(*) AS occ, COUNT(DISTINCT tes) AS uni, COUNT(tag) AS
tagged FROM rtable WHERE qu > 0 AND qu < 4 GROUP BY qu

The table would look something like

1 54 3
2 26 4
3 56 8

I want to modify the above sql query to sum the second and third columns
and get a resultant table something like

1 54 3
2 26 4
3 56 8
tot  136  15

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

Re: [sqlite] Return Value from sqlite3_exec()

[Frederick Wasti]

Thanks for your replies. What you have all said makes sense (and, in my
defense, I did suggest that a nonsense - but properly formed - SQL
statement could still be processed "OK"). :-)

I first noticed this situation when studying the sample code for the
"SmartDB" C++ wrapper for SQLite. The author's code uses the return value
from sqlite3_exec() to report to the user whether the SQL entered by the
user succeeded or not. Having a msgbox report to the user that the "Query
was executed successfully" is actually, from a software ~user's~ point of
view, rather misleading.

>From a ~user's~ point of view, I think that if the code tested for the
possibility of ~not~ actually being able to modify a DB with an attempted
UPDATE query (perhaps by internally using a SELECT statement first, just to
see if the UPDATE's WHERE condition would even be feasible) might be a good
strategy. [The ~user~ might just like to be informed that the ~expected~ DB
write did ~not~ occur, even if the SQL looked superficially OK (and indeed
was properly processed by sqlite3_exec() "OK", too).]

Thanks.

Fred

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

Re: [sqlite] Return Value from sqlite3_exec()

[Simon Slavin]

On 22 Feb 2013, at 10:23pm, Simon Slavin  wrote:

> DELETE FROM customers WHERE name = "John Johnson"

That should of course have been

DELETE FROM customers WHERE name = 'John Johnson'

I'm terribly sorry to have shamed the SQLite community this way.

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

Re: [sqlite] Return Value from sqlite3_exec()

[Eric Sink]

In your example, the only way SQLite can do what you expect is to notice 
that your UPDATE didn't modify any rows.


But you don't want an UPDATE statement throwing errors simply because it 
didn't modify any rows.  There are many situations where that happens 
and is considered normal behavior.


UPDATE table1 SET site='Site1' WHERE site='garbage';

In your example, you know what garbage looks like.  SQLite does not.  
Mere absence of that value from the column does not qualify it as garbage.


OTOH, I do sometimes like to put this line (or something like it):

assert(1 == sqlite3_changes(db));

after an UPDATE when I know that it should have changed exactly one row.

--
E


On 2/22/13 3:15 PM, Frederick Wasti wrote:

[A "newbie to SQLite" here...]

>From the documentation on sqlite3_exec(), it seems as if it should return
SQLITE_OK (=0) upon processing a successful SQL query. However, I was a
bit surprised to see that an SQL statement such as "UPDATE table1 SET
site='Site1' WHERE site='garbage' (where garbage really is garbage, as in
not being present in the database) results in an SQLITE_OK return.

I guess there are two ways of looking at this: On the one hand, the SQL
statement cannot succeed, so the return should not be SQLITE_OK. On the
other hand, an SQL statement which would be impossible to process, but
which would nonetheless be handled with aplomb by sqlite3_exec(), should
return SQLITE_OK after all (in the sense that sqlite3_exec() did do its job
OK). (???)

So, my question is: Is it correct for sqlite3_exec() to return SQLITE_OK if
the SQL query is doomed to failure (but is otherwise properly formed)?

Thanks.

Fred

___
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] Return Value from sqlite3_exec()

[Jay A. Kreibich]

On Fri, Feb 22, 2013 at 05:15:15PM -0500, Frederick Wasti scratched on the wall:

> So, my question is: Is it correct for sqlite3_exec() to return SQLITE_OK if
> the SQL query is doomed to failure (but is otherwise properly formed)?

  The SQL query did not fail.  It did exactly what you asked: updated
  every single record that met the specified condition.

   -j

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

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

Re: [sqlite] Return Value from sqlite3_exec()

[Simon Slavin]

On 22 Feb 2013, at 10:15pm, Frederick Wasti  wrote:

> From the documentation on sqlite3_exec(), it seems as if it should return
> SQLITE_OK (=0) upon processing a successful SQL query. However, I was a
> bit surprised to see that an SQL statement such as "UPDATE table1 SET
> site='Site1' WHERE site='garbage' (where garbage really is garbage, as in
> not being present in the database) results in an SQLITE_OK return.
> 
> I guess there are two ways of looking at this: On the one hand, the SQL
> statement cannot succeed, so the return should not be SQLITE_OK.

This is where you make your error.  If I say "I'll pay you $100 to paint every 
frog in my yard black." and there are no frogs in my yard, you're still going 
to claim the money, aren't you.  If I argue with you you'll just tell me I 
should have counted the frogs before making the offer.

So yes, a SQL statement succeeds even if it does nothing.  Exactly the same as

DELETE FROM customers WHERE name = "John Johnson"

succeeds even if John Johnson isn't a customer.

If you want an analogy between SQL and a programming language, consider what 
happens if I execute

LET B = 6

when B is 6.  Would you expect the programming language to give you an error 
saying "Statement cannot be executed because B is already 6." ?

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

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Simon Slavin]

On 22 Feb 2013, at 9:35pm, Didier Morandi  wrote:

> As far as I understood, SQLite 3 only understands PDO (as I'm
> implementing a program in PHP.) This is why I talked about OOP. I
> thought everyone knew that OOP means Object Oriented Programming and
> PDO is the OOP way of programming with PHP.

There are three ways of handling SQLite databases in PHP.  One of them isn't 
used any more.  The other two are based around OOP ideas because SQLite is 
based around OOP ideas.

One of them is used via PDO and you need to make little changes to your code no 
matter whether you're using SQLite3 or MySQL or some other SQL engine.  The 
advantage of this one is fast switching between using different SQL engines.  
The disadvantage is that some problems are very difficult to diagnose because 
the PDO calls aren't ideally suited to how any one particular SQL engine works.

The other is just a very thin shim which allows you to call the SQLite3 library 
directly from PHP. The advantage of this one is that you can use almost all the 
SQLite3 API and the documentation for SQLite3 is perfectly useful in getting 
your PHP code working, with all errors reported exactly the way the 
documentation says they are.  The disadvantage is that if you switch from 
SQLite3 to another database engine you have to rewrite every database call in 
your code.

But in both cases apart from the actual call which interacts with SQLite, you 
can write the rest of your program procedurally.  So if you don't like OOP 
techniques then apart from the line of your program which invokes a SQLite3 
method you can write the rest of your program completely ignoring OOP ideas.  
In fact many of the examples in the PHP documentation show people doing it that 
way.

So please don't be scared off of using SQLite3 just because it's OOP.  Apart 
from slavishly following that weird arrow syntax for the SQLite calls, you 
don't have to worry about it.  And the PHP documentation includes many examples 
for each call, letting you easily copy stuff without having to understand it 
too well.  In fact that's exactly how I got my own PHP coding done before I 
learned how PHP OOP worked under the covers.

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

[sqlite] Return Value from sqlite3_exec()

[Frederick Wasti]

[A "newbie to SQLite" here...]

>From the documentation on sqlite3_exec(), it seems as if it should return
SQLITE_OK (=0) upon processing a successful SQL query. However, I was a
bit surprised to see that an SQL statement such as "UPDATE table1 SET
site='Site1' WHERE site='garbage' (where garbage really is garbage, as in
not being present in the database) results in an SQLITE_OK return.

I guess there are two ways of looking at this: On the one hand, the SQL
statement cannot succeed, so the return should not be SQLITE_OK. On the
other hand, an SQL statement which would be impossible to process, but
which would nonetheless be handled with aplomb by sqlite3_exec(), should
return SQLITE_OK after all (in the sense that sqlite3_exec() did do its job
OK). (???)

So, my question is: Is it correct for sqlite3_exec() to return SQLITE_OK if
the SQL query is doomed to failure (but is otherwise properly formed)?

Thanks.

Fred

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

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Richard Hipp]

On Fri, Feb 22, 2013 at 4:47 PM, Gregory Moore wrote:

> It was my understanding that any version of SQLite is written using the C
> programming language which happens to be a procedural language (as in not
> OOP) so I'm not sure why OOP is even part of the discussion.
>

OOP is a design philosophy, not a programming langauge.  SQLite is OOP even
though it is coded in C.  There are objects (sqlite3, sqlite3_stmt,
sqlite3_vfs, etc) and methods on those objects.  And there is inheritance.

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

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Gregory Moore]

It was my understanding that any version of SQLite is written using the C 
programming language which happens to be a procedural language (as in not OOP) 
so I'm not sure why OOP is even part of the discussion.

Or maybe I just need to be educated further. :-)

Greg Moore
thewatchful...@gmail.com

On Feb 22, 2013, at 3:35 PM, Didier Morandi  wrote:

> Ladies and Gentlemen,
> 
> As far as I understood, SQLite 3 only understands PDO (as I'm
> implementing a program in PHP.) This is why I talked about OOP. I
> thought everyone knew that OOP means Object Oriented Programming and
> PDO is the OOP way of programming with PHP.
> Anyway, I do not wish to spam this list with my considerations on all this.
> Sorry for the noise, good bye and thank you for all the fish.
> D.
> 
> 2013/2/22 Petite Abeille :
>> 
>> You seem to be missing the point entirely.  No one mentioned anything about 
>> OOP at all, whatever that is. Merely that you may be better off using a more 
>> contemporary version of SQLite. That's all.
> 
> 2013/2/22 Tim Streater :
> you don't need to do anything OOP in PHP in order to use SQLite. I do
> a bit of OOP here and there but by and large not.
> ___
> 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 on a Mac: PHP doesn't know about :-(

[Didier Morandi]

Ladies and Gentlemen,

As far as I understood, SQLite 3 only understands PDO (as I'm
implementing a program in PHP.) This is why I talked about OOP. I
thought everyone knew that OOP means Object Oriented Programming and
PDO is the OOP way of programming with PHP.
Anyway, I do not wish to spam this list with my considerations on all this.
Sorry for the noise, good bye and thank you for all the fish.
D.

2013/2/22 Petite Abeille :
>
> You seem to be missing the point entirely.  No one mentioned anything about 
> OOP at all, whatever that is. Merely that you may be better off using a more 
> contemporary version of SQLite. That's all.

2013/2/22 Tim Streater :
>
you don't need to do anything OOP in PHP in order to use SQLite. I do
a bit of OOP here and there but by and large not.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Tim Streater]

On 22 Feb 2013 at 20:51, Didier Morandi  wrote: 

> very reason why noone (but Geeks) will ever move from VBScript to
> PowerShell. Richard, I will not start learning OOP at 62 to be able to
> use SQLite. Sorry for that. I'll stick to MySQL and good old
> procedural PHP.

Not only do you not need to install anything at all on your Mac in order to use 
SQLite/PHP/apache/ajax, but you don't need to do anything OOP in PHP in order 
to use SQLite. I do a bit of OOP here and there but by and large not.



--
Cheers  --  Tim (older than you are)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Petite Abeille]

On Feb 22, 2013, at 9:51 PM, Didier Morandi  wrote:

> .

You seem to be missing the point entirely.  No one mentioned anything about OOP 
at all, whatever that is. Merely that you may be better off using a more 
contemporary version of SQLite. That's all.

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

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Didier Morandi]

did not know that.
Thanks.

2013/2/22 Simon Slavin :
>
> On 22 Feb 2013, at 7:41pm, Didier Morandi  wrote:
>
>> I then installed MAMP on my Mac
>
> Why ?  OS X includes Apache which includes PHP which includes the sqlite3 
> library.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Didier Morandi]

Richard,

I understand your position, the new version is aged 6 years already,
there is obiously no reason to support the previous one. But everyone
should understand that OOP is not a necessity in IT life. I know *a
lot* of people who hate OOP just because it is not the way a computer
works. A computer is a machine driven by a program. A program is a
list of operations. A program is a procedural way of telling a machine
what it has to do. One day, some Geeks around invented OOP. Why ? We
will probably never know. But to me OOP is abnormal. A file or a drive
does not have "methods" and "instances" and such. A file or a drive is
opened, read from, written to and closed. Period. A lot could be
written on the mind of the Folks who invented OOP. Ada was a REAL
progress because the objective was to produce error free code. OOP is
like .NET. An awful counternature invention for Geeks. This is the
very reason why noone (but Geeks) will ever move from VBScript to
PowerShell. Richard, I will not start learning OOP at 62 to be able to
use SQLite. Sorry for that. I'll stick to MySQL and good old
procedural PHP.
.

Didier


2013/2/22 Richard Hipp :

>
> The following link shows the development history of SQLite2.  Pay close
> attention to the dates.
>
>  http://www.sqlite.org/src/timeline?n=200=version_2
>
> Compare with SQLite3:
>
>  http://www.sqlite.org/src/timeline?n=200
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Simon Slavin]

On 22 Feb 2013, at 7:41pm, Didier Morandi  wrote:

> I then installed MAMP on my Mac, but the PHP 5.4.10 distributed with
> MAMP doesn't have the SQLite params required in its php.ini

Why ?  OS X includes Apache which includes PHP which includes the sqlite3 
library.  Unless you're using a really old version of OS X.  Just use the 
built-in web service for the Mac.

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

Re: [sqlite] Question about aggregate returning empty row

[Stephen Chrzanowski]

I would think so.  You're asking for the minimum value of what is
encountered.  Follows along the line of count as well.

The minimum return is going to be zero or null (I've not checked), but,
you're asking for an absolute answer that will return some sort of value.

On Fri, Feb 22, 2013 at 3:06 PM, Pierre Chatelier wrote:

> Hello,
>
> [tested under 3.6.12 and 3.7.15.2]
>
> I have a question regarding the use of aggregate functions.
>
> Let's imagine the following db :
> >create table A (id1 INTEGER UNIQUE PRIMARY KEY, id2 INTEGER);
> It is empty.
>
> The following query :
> >select id1,id2 from A;
> returns nothing, there is no row.
>
> However, the following query, using the aggregate min() :
> >select min(id1),id2 from A;
> returns an empty line (displays '|' in the shell).
>
> Using avg(), max()... will do the same.
>
> With the C interface, SQLITE_ROW is returned, and I must test
> sqlite_column_type() against SQLITE_NULL to check that in fact, there is no
> result.
>
> Is this expected behaviour ?
>
> Regards,
>
> Pierre Chatelier
> ___
> 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 on a Mac: PHP doesn't know about :-(

[Stephen Chrzanowski]

SQLite 2.x shouldn't be used.  Its old, and I'm not even sure its in
'maintenance mode' anymore.

The PHP version you've installed via WAMP includes the libraries for SQLite
2.x but PROBABLY not for SQLite 3.x by default.  You'll need to install the
packages.  http://php.net/manual/en/book.sqlite3.php

I don't have a Mac (Wife does, but I get ill when I even look at it... ...
fortunately she doesn't read this particular mailing list.  Not to mention
I kind of like sleeping in a bed by allowing it on MY network. ;) ) but I
suspect that either SQLite is completely REMOVED from the MAMP version, or,
its using SQLite 3 which would probably require your getting into a bit of
OOP and using PDO.  PDO isn't a different beast from what PHP4 defaulted
with, but, it is with different mechanisms to get it working.

On Fri, Feb 22, 2013 at 2:41 PM, Didier Morandi wrote:

> Hello Friends,
>
> A new SQLite Chap in the Team, here.
> I successfully installed on my PC/Windoz WAMP and SQLite 2.8.x (I do
> not know anything about PDO...) and built my first appli. Works fine,
> thanks.
> I then installed MAMP on my Mac, but the PHP 5.4.10 distributed with
> MAMP doesn't have the SQLite params required in its php.ini
>
> Why ?
> What should I do to have SQLite 2.8.x work as fine on my Mac as it
> does on my PC ?
>
> (please, don't tell me to go SQLite 3 :-)
>
> Thanks.
>
> Didier
> Toulouse, France
> ___
> 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 on a Mac: PHP doesn't know about :-(

[Richard Hipp]

On Fri, Feb 22, 2013 at 2:41 PM, Didier Morandi wrote:

> Hello Friends,
>
> A new SQLite Chap in the Team, here.
> I successfully installed on my PC/Windoz WAMP and SQLite 2.8.x (I do
> not know anything about PDO...) and built my first appli. Works fine,
> thanks.
> I then installed MAMP on my Mac, but the PHP 5.4.10 distributed with
> MAMP doesn't have the SQLite params required in its php.ini
>
> Why ?
> What should I do to have SQLite 2.8.x work as fine on my Mac as it
> does on my PC ?
>
> (please, don't tell me to go SQLite 3 :-)
>

The following link shows the development history of SQLite2.  Pay close
attention to the dates.

 http://www.sqlite.org/src/timeline?n=200=version_2

Compare with SQLite3:

 http://www.sqlite.org/src/timeline?n=200


>
> Thanks.
>
> Didier
> Toulouse, France
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Didier Morandi]

Hello Friends,

A new SQLite Chap in the Team, here.
I successfully installed on my PC/Windoz WAMP and SQLite 2.8.x (I do
not know anything about PDO...) and built my first appli. Works fine,
thanks.
I then installed MAMP on my Mac, but the PHP 5.4.10 distributed with
MAMP doesn't have the SQLite params required in its php.ini

Why ?
What should I do to have SQLite 2.8.x work as fine on my Mac as it
does on my PC ?

(please, don't tell me to go SQLite 3 :-)

Thanks.

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

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 22 Feb 2013, at 8:31, Eduardo Morras wrote:

The only thing i can think that explain it is compilation options, 
specifically SQLITE_TEMP_STORE=0. If you do pragma temp_store=2, does 
it work better?


Nope.

I did check all this stuff out, got ideas from people in the #sqlite IRC 
channel, and so on. I am pretty sure there is an actual problem, which 
may well be fixed in 3.7, but I am sort of suspecting that there may be 
remnants, and that :memory: performance might have significant room for 
improvement.


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Seebs]

On 22 Feb 2013, at 8:07, Stephen Chrzanowski wrote:


I think I missed something;

For clarification, you mention that you're running the tests and are
monitoring memory use but (And here's what I'm missing) you don't see 
a

memory load against the application?


I do see a memory load against the application. It goes from about 1MB 
to about 10MB as the database gets populated. It's definitely doing what 
I expect in terms of storing things in memory, it's not swapping.


With SQLite 3.6, this makes it dramatically slower. With 3.7, speed is 
roughly the same.


You mention indexes.  Are these just basic Unique constraints, or, 
standard
indexes?  How many records are in the database, say on medium to heavy 
load?


A couple of actual indexes. Most of my tests ended up with 28,000 
records, but I did some with 84,000 or so (3x the size), whereupon 
memory usage went up to 20-something MB. Disk performance was unchanged, 
memory performance was about a factor of 2-3 worse.


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

Re: [sqlite] Faster query result

[Cory Nelson]

On Fri, Feb 22, 2013 at 9:56 AM, jose isaias cabrera
 wrote:
>
> Greetings.
>
> which one is faster...
>
> #1.
>
> SELECT id FROM LSOpenJobs WHERE bdate BETWEEN '2012-01-01' AND '2012-12-31';
>
> or this one...
>
> #2
>
> SELECT id FROM LSOpenJobs WHERE bdate IN ('2012-01-01', ..., '2012-12-31)';
>
> where , ..., would have all the rest of the dates.  Thanks.
>
> josé

This is a great opportunity to learn the Try It And See approach.

Note that:
- The first query will only perform 2 comparisons.
- The second query will perform 31 comparisons.
- Dates are not strongly-typed in SQLite, so this will perform string
comparisons without any special handling.

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

Re: [sqlite] Faster query result

[Etienne]

Hello José,

#1: no hash/bsearch required.
 
Regards,
Etienne


- Original message -
From: jose isaias cabrera 
To: General Discussion of SQLite Database 
Subject: [sqlite] Faster query result
Date: Fri, 22 Feb 2013 10:56:08 -0500


Greetings.

which one is faster...

#1.

SELECT id FROM LSOpenJobs WHERE bdate BETWEEN '2012-01-01' AND '2012-12-31';

or this one...

#2

SELECT id FROM LSOpenJobs WHERE bdate IN ('2012-01-01', ..., '2012-12-31)';

where , ..., would have all the rest of the dates.  Thanks.

josé

___
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] Faster query result

[Igor Tandetnik]

On 2/22/2013 10:56 AM, jose isaias cabrera wrote:

which one is faster...

#1.

SELECT id FROM LSOpenJobs WHERE bdate BETWEEN '2012-01-01' AND '2012-12-31';

or this one...

#2

SELECT id FROM LSOpenJobs WHERE bdate IN ('2012-01-01', ..., '2012-12-31)';

where , ..., would have all the rest of the dates.  Thanks.


Why don't you test both and measure the difference? For what it's worth, 
my bet would be on #1.

--
Igor Tandetnik

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

[sqlite] Faster query result

[jose isaias cabrera]

Greetings.

which one is faster...

#1.

SELECT id FROM LSOpenJobs WHERE bdate BETWEEN '2012-01-01' AND '2012-12-31';

or this one...

#2

SELECT id FROM LSOpenJobs WHERE bdate IN ('2012-01-01', ..., '2012-12-31)';

where , ..., would have all the rest of the dates.  Thanks.

josé

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

[sqlite] FTS3 prefix wildcard

[Mike King]

Hi All,

A user wants to be able to query my FTS3 database using a preceeding
wildcard. From reading back through the mailing list it looks like SQLite
may not support this (for very understandable reasons) but I cannot find
any mention of this in the docs. Is this limitation?

To get round this I came up with the following, I query the FTS4aux table
first to get a list of words that match and then use this to query the FTS
table as shown below.

select offsets(text) from text where content match (select term from
ft_terms where term like '%oo%' and col = 0)
This seems to work but I appreciate it is not the most efficient thing to
do but does this look ok?

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

Re: [sqlite] :memory: performance in sqlite 3.6

[Eduardo Morras]

On Wed, 20 Feb 2013 12:06:17 -0600
Seebs  wrote:

> On 20 Feb 2013, at 11:47, Simon Slavin wrote:
> 
> > On 20 Feb 2013, at 5:32pm, Seebs  wrote:
> >
> >> First: The SQL is completely trivial.
> >> Second: I am not having performance problems with sqlite, I am having 
> >> performance problems with :memory:. Performance on files is lovely.
> >
> > This normally means that your computer has very little actual memory 
> > to devote to 'memory' uses.  It is using a lot of swap space (or 
> > paging, or virtual memory, whatever you want to call it).  So when you 
> > think you are doing calls which reference ':memory:' it actually has 
> > to fetch and write those parts of memory to disk.
> 
> I am pretty sure that's not it.
> 
> I know I'm a newbie on the list, but please imagine for the sake of 
> argument that I am a basically competent programmer with enough sysadmin 
> background to be aware of the obvious problems.
> 
> In this case, I tested this pretty carefully over a period of about six 
> hours of testing across multiple machines. I was watching CPU load, 
> memory load, and all that stuff. And this is a machine with >4GB of 
> *free* memory -- that's over and above even the couple GB of disk cache 
> being used.
> 
> Process memory size is not appreciably different between sqlite 3.6 and 
> 3.7, or between page size of 1024 or 8192. Runtime is massively 
> different. I am pretty sure this is an actual computation-time issue, 
> and my intuition is that it's quite possible there's at least some other 
> performance issues lurking, because it appears that :memory: *used to 
> be* dramatically faster than disk, but something changed in the last 
> couple of years.


The only thing i can think that explain it is compilation options, specifically 
SQLITE_TEMP_STORE=0. If you do pragma temp_store=2, does it work better?


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

Re: [sqlite] :memory: performance in sqlite 3.6

[Stephen Chrzanowski]

I think I missed something;

For clarification, you mention that you're running the tests and are
monitoring memory use but (And here's what I'm missing) you don't see a
memory load against the application?  If you're using the :MEMORY:
database, throwing anything at it should bump up the count and NOT release
memory until you vacuum, and if its not, something isn't right.  If the
machine has a +4gb of available memory, I'd suggest maybe creating an
additional table and throwing a gig worth of a record blob and see if
memory does get bumped.

One of my apps toggles between the entire DB in either disk or memory, and
the database itself is close to 300meg in size.  No other schema changes
happen at the database level.  At app launch, if the user has opted to use
memory, the backup API is invoked, and I note that task manager shows
memory filling up accordingly for the application.  During development, one
of my tests was to throw 4gig of data at it, and memory bumped
accordingly.  When the app is configured to run off the disk, memory
utilization hangs at around the 1meg mark and stays consistent, at least
until I start actively using it.  (Undos are stored in memory via SQLite).

My daily/dev/game rig is a first gen Intel I7 @ 3.3ghz, 12gig of memory,
with twin Sata3 256gig SDDs @ RAID-0.  When I toggle between disk and
memory use, I notice a change in performance. The program just feels more
responsive with memory usage instead of 'platter' usage, considering EVERY
time I release the mouse button, the change is immediately written to the
database  (Each change amounts to about 6k of a blob).  I also wrote some
of the code on a lower end I5 with a platter and I still notice a
difference between the two modes as well, and the i5 runs off a 7200rpm
platter SATA3 and only 4gig of memory.  Both are Win7x64 machines.  Mind
you, I've not done any timing bench marks, and what I'm noticing is purely
on just the feel of the app.

You mention indexes.  Are these just basic Unique constraints, or, standard
indexes?  How many records are in the database, say on medium to heavy load?

On Wed, Feb 20, 2013 at 1:06 PM, Seebs  wrote:

> On 20 Feb 2013, at 11:47, Simon Slavin wrote:
>
>  On 20 Feb 2013, at 5:32pm, Seebs  wrote:
>>
>>  First: The SQL is completely trivial.
>>> Second: I am not having performance problems with sqlite, I am having
>>> performance problems with :memory:. Performance on files is lovely.
>>>
>>
>> This normally means that your computer has very little actual memory to
>> devote to 'memory' uses.  It is using a lot of swap space (or paging, or
>> virtual memory, whatever you want to call it).  So when you think you are
>> doing calls which reference ':memory:' it actually has to fetch and write
>> those parts of memory to disk.
>>
>
> I am pretty sure that's not it.
>
> I know I'm a newbie on the list, but please imagine for the sake of
> argument that I am a basically competent programmer with enough sysadmin
> background to be aware of the obvious problems.
>
> In this case, I tested this pretty carefully over a period of about six
> hours of testing across multiple machines. I was watching CPU load, memory
> load, and all that stuff. And this is a machine with >4GB of *free* memory
> -- that's over and above even the couple GB of disk cache being used.
>
> Process memory size is not appreciably different between sqlite 3.6 and
> 3.7, or between page size of 1024 or 8192. Runtime is massively different.
> I am pretty sure this is an actual computation-time issue, and my intuition
> is that it's quite possible there's at least some other performance issues
> lurking, because it appears that :memory: *used to be* dramatically faster
> than disk, but something changed in the last couple of years.
>
> -s
>
> __**_
> 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