[sqlite] \1.8g-@.+o0O8d1n+O0ß..8t."0Śß+e.

[Patko Sándor]

ż1
g9O.r1.1 1l^a0/.a^1ßtgJ0L\l
0l. e.
.v
.:O
.ll.z.l0,1.t1.XN.
.
l.ß.
.gT1l1/..8g.9s..J1.9lBf0.0lÁ
.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] beginner question: help required to retrieve "filename" from an open "sqlite3*" handle

[Andreas Otto]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi,

with "sqlite2_open" the parameter "filename" is used to specify the
database location.

Q: how I can retrieve this parameter from an open handle?

sqlite-version: latest


mfg, Andreas Otto
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.15 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJNS6z9AAoJEGTcPijNG3/AqHcH/AyN5F9s0twi1ffozHdDG3IV
YYvO5FW+jiyOB24KnN+c5j7Ffqc2jGFCbD2PMC8hIy8KP/ZZfCS+qsEKL54x82w1
5bse7X4c121cG4Wi2PCrLD0Mg+u/ZzavZXJEpYPdJsVtIhRUvFKLTtvkKKi8Ngkd
iN5ErCZDrVjZxr4dU/pijT7GSRNYW7bTSi8d/XzbRzoMV5NF8QzHFmJmQsZCY1yi
xyZ39fnOh+Fi2C659fldzmPd1Nzc3GSmE/XTTLX3qqrXbCQPinIBmmJvbImAVz1B
I72HxkP49Q+LwcdLoIIASlrBOaSH53w6rQIDoscoSM7czFtDu59VXv8Ptpwqh/E=
=ARbF
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger for incrementing a column is slow

[Dan Kennedy]

On 02/04/2011 06:01 AM, Kevin Wojniak wrote:
> On Feb 3, 2011, at 2:27 PM, Jim Wilcoxson wrote:
>
>> On Thu, Feb 3, 2011 at 5:07 PM, Kevin Wojniak  wrote:
>>
>>>
>>> On Feb 3, 2011, at 11:41 AM, Petite Abeille wrote:
>>>
 On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:

> The trigger is ran once via sqlite3_exec();

 Hmm... you mean the trigger is run every single time you perform an
>>> insert, no?
>>>
>>> Yes. I should say the trigger is created once via sqlite3_exec().
>>>
> Any insight as to why the trigger is significantly slower?
>>>
>>
>> Perhaps SQLite is having to recompile the trigger SQL on every use, whereas
>> your update stmt is prepared.  I tried triggers once, and they were slow for
>> me too.
>>
>> Jim
>> --
>> HashBackup: easy onsite and offsite Unix backup
>> http://www.hashbackup.com
>
> If that were the case that'd seem like a major design flaw.
>
>
> I created a timing profile using the trigger and without. On the version 
> without, most of the time is spent all in sqlite, as expected. The one with 
> triggers had a ton more time spent in the various kernel file system 
> functions (hfs, I'm on Mac OS 10.6.6). So it seems like the triggers are 
> creating significant more file access. I have all the inserts surrounded by 
> BEGIN/END TRANSACTION, so I don't see why this would be doing any more 
> necessary work.

Do you have a test program that we can use to reproduce this phenomenon?

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

Re: [sqlite] Trigger for incrementing a column is slow

[Jim Wilcoxson]

On Thu, Feb 3, 2011 at 5:07 PM, Kevin Wojniak  wrote:

>
> On Feb 3, 2011, at 11:41 AM, Petite Abeille wrote:
>
> > On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:
> >
> >> The trigger is ran once via sqlite3_exec();
> >
> > Hmm... you mean the trigger is run every single time you perform an
> insert, no?
>
> Yes. I should say the trigger is created once via sqlite3_exec().
>
> >> Any insight as to why the trigger is significantly slower?
>

Perhaps SQLite is having to recompile the trigger SQL on every use, whereas
your update stmt is prepared.  I tried triggers once, and they were slow for
me too.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger for incrementing a column is slow

[Simon Slavin]

On 3 Feb 2011, at 7:41pm, Petite Abeille wrote:

> On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:
> 
>> The trigger is ran once via sqlite3_exec();
> 
> Hmm... you mean the trigger is run every single time you perform an insert, 
> no?
> 
>> Any insight as to why the trigger is significantly slower?
> 
> It adds significant overhead for each and every insert.

But his alternative to doing INSERT with a TRIGGER is to do an INSERT and then 
a manual UPDATE.  Are triggers really so inefficient that it's that much faster 
to do it manually ?

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

Re: [sqlite] Trigger for incrementing a column is slow

[Petite Abeille]

On Feb 3, 2011, at 6:53 PM, Kevin Wojniak wrote:

> The trigger is ran once via sqlite3_exec();

Hmm... you mean the trigger is run every single time you perform an insert, no?

> Any insight as to why the trigger is significantly slower?

It adds significant overhead for each and every insert.

> I hope I'm missing something basic.

Yes! Don't use triggers :)

Do you really need to store that value? As oppose to have it computed? After 
all, you should always be able to query for it, no?

Alternatively, update it in bulk, e.g: update root set num_children = ( select 
count( * ) from root as parent where parent.parent_rowid = root.rowid ) where 
rowid in ( select distinct parent_rowid from root ) or something
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Announcement: new db access abstraction API w/ sqlite3 support

[Stephan Beal]

Hello, all!

i've been using sqlite3 since 2004 or 2005, and i can't believe i'm just no
subscribing to the list. (sqlite3 is so easy to use, who needs support? ;)

i'd like to announce a new C library called cpdo (because it's modeled after
PHP's PDO API):

http://fossil.wanderinghorse.net/repos/cpdo/

The sqlite3-based driver is the "reference implementation" for the API, and
it supports all of the features DB-using apps "normally need" (not including
highly driver-dependent operations like scrollable cursors and random access
to parts of blobs). We've also got a MySQL driver, since a DB abstraction
API supporting only one back-end is pretty senseless.

If there are any other C hackers left out there aside from DRH himself, i
would love to hear your input/suggestions/comments/etc. (Off-list please -
they're not topical here.)

Happy Hacking!

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

Re: [sqlite] SQLite3 and threading

[Ulrich Telle]

Am 03.02.2011 15:53, schrieb Pavel Ivanov:
> It seems that this explanation as well as all other statements in the
> thread you linked are coming from the wrong assumption that SQLite's
> handles cannot be used from any thread other than the one created that
> handle.

The explanation I gave to Stefano is *not* based on the assumption that
SQlite handles can't be passed from one thread to another. But certainly
passing them around *can* lead to problems.

> Although this was true in some earlier versions of SQLite it's
> not true in the current version. So if SQLite is compiled with
> THREADSAFE=1 (as mentioned in that thread) then you can do with it
> whatever you want. Just beware of possible data races and potentially
> uncommitted transactions because of some open statement handles. And
> if as you say there's no simultaneous access to the database from
> different threads then there's no difference in your usage pattern
> from single-threaded one.

I cite from http://sqlite.org/faq.html#q6:

"The restriction on moving database connections across threads was
relaxed somewhat in version 3.3.1. With that and subsequent versions, it
is safe to move a connection handle across threads as long as the
connection is not holding any fcntl() locks. You can safely assume that
no locks are being held if no transaction is pending and all statements
have been finalized."

The problem is that Stefano wants to pass a result set from one thread
to another. The result set has an associated SQLite statement handle
which is inherently *not* finalized. And that might cause trouble.

If access to the database handle is serialized as Stefano claims passing
the statement handle between threads shouldn't cause problems, true, but
in that case I don't see the advantages of having a separate thread over
using a global database access instance.

Regards,

Ulrich

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

Re: [sqlite] Multithreading problem

[Tiberio, Sylvain]

You're right!

I checked the Solaris documentation the correct flag to compile
multithread program is -D_REENTRANT.

I have reconfigured and remade sqlite libs:
  ./configure --enable-threadsafe CFLAGS=-D_REENTRANT
  make

I have remade my test file ...and it is working well now!
Thanks you very much!!!

Another question: Why ./configure doesn't set itself this flag when I
use --enable-threadsafe in a Solaris system?

Regards,

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, February 03, 2011 5:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithreading problem

On 02/03/2011 11:00 PM, Tiberio, Sylvain wrote:
> Here the modification in sqlite3.c:
>
> if( unlink(zPath)==(-1)&&  errno!=ENOENT ){
>   perror(zPath);
>   return SQLITE_IOERR_DELETE;
> }
>
> And here is the result:
>
> /home/tiberio/perso/source/sql/bug/try.db-wal: No such file or
directory

That error message suggests that errno should be set to ENOENT.
And when you used the main thread to do the work it seems like
it was, since you didn't get the error then.

Do you have to do something special in Solaris to get errno
to work in multi-threaded apps? Something like
-D_POSIX_C_SOURCE=199506L or -mt perhaps?

Is SQLite being compiled with the same thread-related switches as
the rest of the app?

Dan.



>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: Thursday, February 03, 2011 4:51 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multithreading problem
>
> On 02/03/2011 10:22 PM, Tiberio, Sylvain wrote:
>>
>> Dan,
>>
>> Thanks for your attention.
>>
>> sqlite3_extended_errcode() return 0xA0A that means
> SQLITE_IOERR_DELETE.
>
> Earlier versions of SQLite ignored the return code of unlink(). That
> is probably why you're not seeing a problem with 3.6.22.
>
> Search the code for a function called "unixDelete". In sqlite3.c if
> you are using the amalgamation, os_unix.c otherwise. Near the top
> of that function is this:
>
> if( unlink(zPath)==(-1)&&  errno!=ENOENT ){
>   return SQLITE_IOERR_DELETE;
> }
>
> That's where your error is coming from. If you can put a call to
> perror() or print the value of errno just before SQLITE_IOERR_DELETE,
> it might show why that call to unlink() is failing. Printing out
> "zPath" as well is probably a good idea.
>
> Dan.
>
>
>
>
>>
>> Here are others information:
>> - My problem occurs in Sparc/Solaris 10 system.
>> - After my program error, the file try.db exists and has the correct
>> right -rw-r--r--, correct owner/group and a null size.
>>
>> Regards,
>>
>> Sylvain
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
>> Sent: Thursday, February 03, 2011 3:16 PM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Multithreading problem
>>
>> On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
>>> Hi!
>>>
>>>
>>>
>>> I have a problem when I try to create a new database in a thread and
>> try
>>> to add a table on it.
>>>
>>>
>>>
>>> The following C code (see in the end of this e-mail) produces:
>>
>> The program is working Ok with 3.7.5 here.
>>
>> After the IO error in sqlite3_exec(), what value does
>> sqlite3_extended_errcode() return?
>>
>> Dan.
>>
>>
>> ___
>> 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] How do I query for a specific count of items?

[Igor Tandetnik]

On 2/3/2011 12:47 PM, Puneet Kishor wrote:
> A... I see now. It is trickier than I thought. How about
>
> SELECT *
> FROM Customers
> WHERE Type = 'Apple' AND EntryID NOT IN (SELECT * FROM Customers WHERE
> Type != 'Apple');

I assume you meant "NOT IN (SELECT EntryID..." . Naturally, an EntryID 
for an entry having Type='Apple' won't appear in the list of entries 
having Type != 'Apple'. The second condition is always true whenever the 
first is.
-- 
Igor Tandetnik

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

[sqlite] Trigger for incrementing a column is slow

[Kevin Wojniak]

I've got a tree structure where whenever I insert a new node, I want its parent 
entry's number of children to increment.

I figured a trigger would be great for this, however it is very slow compared 
to just a standard UPDATE manually ran after the INSERT.

Here is the table:
CREATE TABLE root (rowid INTEGER PRIMARY KEY, parent_rowid INTEGER, name TEXT, 
num_children INTEGER);

and trigger:
CREATE TRIGGER update_num_children AFTER INSERT ON root
BEGIN
UPDATE root SET num_children = num_children + 1 WHERE rowid = NEW.parent_rowid;
END;

The trigger is ran once via sqlite3_exec();

I am testing with inserting 200,000 entries. With the trigger enabled, it takes 
about 15.5 seconds. When I disable the trigger, and run the UPDATE via a cached 
statement, it takes about 2.5 seconds:
UPDATE root SET num_children = num_children + 1 WHERE rowid = ?;

Any insight as to why the trigger is significantly slower? I hope I'm missing 
something basic.

Thanks,
Kevin

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

Re: [sqlite] How do I query for a specific count of items?

[Igor Tandetnik]

On 2/3/2011 12:10 PM, Scott Baker wrote:
> CREATE Table Customers (
>   EntryID INTEGER PRIMARY KEY,
>   CustomerID INT,
>   Type ENUM
> );
>
> #1) Query for customers who *ONLY* bought apples

select CustomerID from Customers
group by CustomerID
having sum(Type = 'Apple')>0 and sum(Type != 'Apple')=0;

> #2) Query for customers who bought apples *AND* bananas

select CustomerID from Customers
group by CustomerID
having sum(Type = 'Apple')>0 and sum(Type = 'Banana')>0;

> #3) Query for customers who bought exactly 2 apples?

select CustomerID from Customers
group by CustomerID
having sum(Type = 'Apple') = 2;

-- or

select CustomerID from Customers
where Type = 'Apple'
group by CustomerID
having count(*) = 2;

-- 
Igor Tandetnik

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

Re: [sqlite] How do I query for a specific count of items?

[Puneet Kishor]

Igor Tandetnik wrote:
> On 2/3/2011 12:26 PM, Puneet Kishor wrote:
>> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote:
>>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana');
>> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs.
>
> That's how you tell SQLite to generate IDs automatically.
>

Neat. I didn't know that. I would simply *not* insert anything for that 
column in order to get the automagic PK like

INSERT INTO Customers (CustomerID, Type) VALUES (...)

>> Your CustomerID seems like it should be unique, yet you have identical rows 
>> inserted.
>
> It's not declared unique, why do you think it should be?
>

Now that I know that NULL actually triggers off an automatic PK, that 
makes sense. However, while there might be deeper mystery to the OP, 
having identical rows other than the PK makes no sense to me. For what 
its worth, it was an "editorial" comment.

>> For example, what is the difference between the first and the second row?
>
> EntryID.
>

Right. Now I know.

>>> #1) Query for customers who *ONLY* bought apples
>> SELECT *
>> FROM Customers
>> WHERE Type = 'Apple';
>
> That would also report customers that bought something else besides apples.
>

A... I see now. It is trickier than I thought. How about

SELECT *
FROM Customers
WHERE Type = 'Apple' AND EntryID NOT IN (SELECT * FROM Customers WHERE 
Type != 'Apple');


>>> #2) Query for customers who bought apples *AND* bananas
>> SELECT *
>> FROM Customers
>> WHERE Type = 'Apple' OR Type = 'Banana';
>
> That would report customers that only bought apples, as well as those
> that only bought bananas.

I am tired.


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

Re: [sqlite] How do I query for a specific count of items?

[Jim Morris]

Only apples
SELECT distinct customerid
FROM Customers c1
WHERE Type = 'Apple' AND not exists (select 1 from customers c2 where 
c2.customerid=c1.customerid and  not Type = 'Apple')
;


Apples and Bananas
SELECT distinct  customerid
FROM Customers c1
WHERE Type = 'Apple' AND exists (select 1 from customers c2 where 
c2.customerid=c1.customerid and  Type = 'Banana')
;



On 2/3/2011 9:40 AM, Igor Tandetnik wrote:
> On 2/3/2011 12:26 PM, Puneet Kishor wrote:
>> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote:
>>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana');
>> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs.
> That's how you tell SQLite to generate IDs automatically.
>
>> Your CustomerID seems like it should be unique, yet you have identical rows 
>> inserted.
> It's not declared unique, why do you think it should be?
>
>> For example, what is the difference between the first and the second row?
> EntryID.
>
>>> #1) Query for customers who *ONLY* bought apples
>> SELECT *
>> FROM Customers
>> WHERE Type = 'Apple';
> That would also report customers that bought something else besides apples.
>
>>> #2) Query for customers who bought apples *AND* bananas
>> SELECT *
>> FROM Customers
>> WHERE Type = 'Apple' OR Type = 'Banana';
> That would report customers that only bought apples, as well as those
> that only bought bananas.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I query for a specific count of items?

[Igor Tandetnik]

On 2/3/2011 12:26 PM, Puneet Kishor wrote:
> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote:
>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana');
>
> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs.

That's how you tell SQLite to generate IDs automatically.

> Your CustomerID seems like it should be unique, yet you have identical rows 
> inserted.

It's not declared unique, why do you think it should be?

> For example, what is the difference between the first and the second row?

EntryID.

>> #1) Query for customers who *ONLY* bought apples
>
> SELECT *
> FROM Customers
> WHERE Type = 'Apple';

That would also report customers that bought something else besides apples.

>> #2) Query for customers who bought apples *AND* bananas
> SELECT *
> FROM Customers
> WHERE Type = 'Apple' OR Type = 'Banana';

That would report customers that only bought apples, as well as those 
that only bought bananas.
-- 
Igor Tandetnik

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

Re: [sqlite] How do I query for a specific count of items?

[Puneet Kishor]

On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote: 
> If I have the following (highly simplified) customer table how do I:
> 
> #1) Query for customers who *ONLY* bought apples
> #2) Query for customers who bought apples *AND* bananas
> #3) Query for customers who bought exactly 2 apples?
> 
> --
> 
> DROP TABLE IF EXISTS Customers;
> CREATE Table Customers (
>  EntryID INTEGER PRIMARY KEY,
>  CustomerID INT,
>  Type ENUM
> );
> 
> INSERT INTO Customers VALUES (NULL, 1234, 'Banana');
> INSERT INTO Customers VALUES (NULL, 1234, 'Banana');
> INSERT INTO Customers VALUES (NULL, 1235, 'Apple');
> INSERT INTO Customers VALUES (NULL, 1236, 'Banana');
> INSERT INTO Customers VALUES (NULL, 1237, 'Banana');
> INSERT INTO Customers VALUES (NULL, 1237, 'Banana');
> INSERT INTO Customers VALUES (NULL, 1237, 'Apple');
> INSERT INTO Customers VALUES (NULL, 1238, 'Apple');
> INSERT INTO Customers VALUES (NULL, 1238, 'Apple');
> INSERT INTO Customers VALUES (NULL, 1239, 'Apple');
> INSERT INTO Customers VALUES (NULL, 1239, 'Banana');

Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs. Your 
CustomerID seems like it should be unique, yet you have identical rows 
inserted. For example, what is the difference between the first and the second 
row?

That said,

> #1) Query for customers who *ONLY* bought apples

SELECT * 
FROM Customers 
WHERE Type = 'Apple'; 
> 
> #2) Query for customers who bought apples *AND* bananas
SELECT * 
FROM Customers 
WHERE Type = 'Apple' OR Type = 'Banana';

> 
> #3) Query for customers who bought exactly 2 apples?

SELECT CustomerID 
FROM Customers 
WHERE Type = 'Apple' 
GROUP BY CustomerID 
HAVING Count(CustomerID) = 2;



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

[sqlite] How do I query for a specific count of items?

[Scott Baker]

If I have the following (highly simplified) customer table how do I:

#1) Query for customers who *ONLY* bought apples
#2) Query for customers who bought apples *AND* bananas
#3) Query for customers who bought exactly 2 apples?

--

DROP TABLE IF EXISTS Customers;
CREATE Table Customers (
EntryID INTEGER PRIMARY KEY,
CustomerID INT,
Type ENUM
);

INSERT INTO Customers VALUES (NULL, 1234, 'Banana');
INSERT INTO Customers VALUES (NULL, 1234, 'Banana');

INSERT INTO Customers VALUES (NULL, 1235, 'Apple');

INSERT INTO Customers VALUES (NULL, 1236, 'Banana');

INSERT INTO Customers VALUES (NULL, 1237, 'Banana');
INSERT INTO Customers VALUES (NULL, 1237, 'Banana');
INSERT INTO Customers VALUES (NULL, 1237, 'Apple');

INSERT INTO Customers VALUES (NULL, 1238, 'Apple');
INSERT INTO Customers VALUES (NULL, 1238, 'Apple');

INSERT INTO Customers VALUES (NULL, 1239, 'Apple');
INSERT INTO Customers VALUES (NULL, 1239, 'Banana');

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

Re: [sqlite] Attach to file in same directory

[Igor Tandetnik]

On 2/3/2011 9:48 AM, BareFeetWare wrote:
> I could be using any SQLite utility, whether the command line, SQLite
> Manager, Froq etc. I happen to be using my own app, developed for the
> iPad/iPhone. In any of these environments, I open my main SQLite data
> file then want to run an SQL script/procedure which starts with an
> attach command.

Somehow, you managed to build the path to the main file. Set the 
process' working directory to the same path (on Posix systems, see chdir).
-- 
Igor Tandetnik

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

Re: [sqlite] Attach to file in same directory

[BareFeetWare]

>> On 03-02-11 16:18, BareFeetWare wrote:
>> 
>> What SQLite or C library call could I put before that to set the current 
>> directory, that the sqlite3_prepare_v2 function would observe when 
>> processing the attach statement?

> On 04/02/2011, at 2:25 AM, Luuk wrote:
> 
> i'm not a C-programmer

Me either, well, just enough to use the SQLite libraries. The rest of my code 
is in Objective-C.

> but:
> http://www.delorie.com/gnu/docs/glibc/libc_268.html

My question was how can I set the working directory in such a way that the SQL 
attach command will look for files in that directory, if a path isn't specified.

I have just experimented with the C function chdir() and the Objective C method 
changeCurrentDirectoryPath. Both seem to be observed by the SQLite running of 
attach commands. So this solves the problem. Thanks Pavel, Luuk, Igor and Simon 
for steering me in the right direction and for taking the time to reply. I 
appreciate it.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Multithreading problem

[Dan Kennedy]

On 02/03/2011 11:00 PM, Tiberio, Sylvain wrote:
> Here the modification in sqlite3.c:
>
> if( unlink(zPath)==(-1)&&  errno!=ENOENT ){
>   perror(zPath);
>   return SQLITE_IOERR_DELETE;
> }
>
> And here is the result:
>
> /home/tiberio/perso/source/sql/bug/try.db-wal: No such file or directory

That error message suggests that errno should be set to ENOENT.
And when you used the main thread to do the work it seems like
it was, since you didn't get the error then.

Do you have to do something special in Solaris to get errno
to work in multi-threaded apps? Something like
-D_POSIX_C_SOURCE=199506L or -mt perhaps?

Is SQLite being compiled with the same thread-related switches as
the rest of the app?

Dan.



>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: Thursday, February 03, 2011 4:51 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multithreading problem
>
> On 02/03/2011 10:22 PM, Tiberio, Sylvain wrote:
>>
>> Dan,
>>
>> Thanks for your attention.
>>
>> sqlite3_extended_errcode() return 0xA0A that means
> SQLITE_IOERR_DELETE.
>
> Earlier versions of SQLite ignored the return code of unlink(). That
> is probably why you're not seeing a problem with 3.6.22.
>
> Search the code for a function called "unixDelete". In sqlite3.c if
> you are using the amalgamation, os_unix.c otherwise. Near the top
> of that function is this:
>
> if( unlink(zPath)==(-1)&&  errno!=ENOENT ){
>   return SQLITE_IOERR_DELETE;
> }
>
> That's where your error is coming from. If you can put a call to
> perror() or print the value of errno just before SQLITE_IOERR_DELETE,
> it might show why that call to unlink() is failing. Printing out
> "zPath" as well is probably a good idea.
>
> Dan.
>
>
>
>
>>
>> Here are others information:
>> - My problem occurs in Sparc/Solaris 10 system.
>> - After my program error, the file try.db exists and has the correct
>> right -rw-r--r--, correct owner/group and a null size.
>>
>> Regards,
>>
>> Sylvain
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
>> Sent: Thursday, February 03, 2011 3:16 PM
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Multithreading problem
>>
>> On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
>>> Hi!
>>>
>>>
>>>
>>> I have a problem when I try to create a new database in a thread and
>> try
>>> to add a table on it.
>>>
>>>
>>>
>>> The following C code (see in the end of this e-mail) produces:
>>
>> The program is working Ok with 3.7.5 here.
>>
>> After the IO error in sqlite3_exec(), what value does
>> sqlite3_extended_errcode() return?
>>
>> Dan.
>>
>>
>> ___
>> 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] Multithreading problem

[Tiberio, Sylvain]

Michael,

The database try.db is created in the directory where I test this issue.
In this directory I edit the source file, compile it and execute the
test.

- If I use SQL 3.6.22 it runs well.
- I have the save issue if I put the datafile in:
* Ram disk (/tmp/)
* local disk
* my NFS home disk
- If I use ":memory:" it works.
- If I run the test() function directly in main() it works.
- If I create the try.db with SQL 3.6.22 and if I run the 3.7.5 with the
existing file, it works (I have added my query "IF NOT EXISTS" in the
table creation query)

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael
(IS)
Sent: Thursday, February 03, 2011 4:34 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Multithreading problem

Can you "su" as the owner you are are expecting and see if you can
delete it?
Maybe the directory permissions are messed up?

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate

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

Re: [sqlite] Multithreading problem

[Tiberio, Sylvain]

Here the modification in sqlite3.c:

   if( unlink(zPath)==(-1) && errno!=ENOENT ){
perror(zPath);
 return SQLITE_IOERR_DELETE;
   }

And here is the result:

/home/tiberio/perso/source/sql/bug/try.db-wal: No such file or directory

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, February 03, 2011 4:51 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithreading problem

On 02/03/2011 10:22 PM, Tiberio, Sylvain wrote:
>
> Dan,
>
> Thanks for your attention.
>
> sqlite3_extended_errcode() return 0xA0A that means
SQLITE_IOERR_DELETE.

Earlier versions of SQLite ignored the return code of unlink(). That
is probably why you're not seeing a problem with 3.6.22.

Search the code for a function called "unixDelete". In sqlite3.c if
you are using the amalgamation, os_unix.c otherwise. Near the top
of that function is this:

   if( unlink(zPath)==(-1) && errno!=ENOENT ){
 return SQLITE_IOERR_DELETE;
   }

That's where your error is coming from. If you can put a call to
perror() or print the value of errno just before SQLITE_IOERR_DELETE,
it might show why that call to unlink() is failing. Printing out
"zPath" as well is probably a good idea.

Dan.




>
> Here are others information:
> - My problem occurs in Sparc/Solaris 10 system.
> - After my program error, the file try.db exists and has the correct
> right -rw-r--r--, correct owner/group and a null size.
>
> Regards,
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: Thursday, February 03, 2011 3:16 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multithreading problem
>
> On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
>> Hi!
>>
>>
>>
>> I have a problem when I try to create a new database in a thread and
> try
>> to add a table on it.
>>
>>
>>
>> The following C code (see in the end of this e-mail) produces:
>
> The program is working Ok with 3.7.5 here.
>
> After the IO error in sqlite3_exec(), what value does
> sqlite3_extended_errcode() return?
>
> Dan.
>
>
> ___
> 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] Multithreading problem

[Dan Kennedy]

On 02/03/2011 10:22 PM, Tiberio, Sylvain wrote:
>
> Dan,
>
> Thanks for your attention.
>
> sqlite3_extended_errcode() return 0xA0A that means SQLITE_IOERR_DELETE.

Earlier versions of SQLite ignored the return code of unlink(). That
is probably why you're not seeing a problem with 3.6.22.

Search the code for a function called "unixDelete". In sqlite3.c if
you are using the amalgamation, os_unix.c otherwise. Near the top
of that function is this:

   if( unlink(zPath)==(-1) && errno!=ENOENT ){
 return SQLITE_IOERR_DELETE;
   }

That's where your error is coming from. If you can put a call to
perror() or print the value of errno just before SQLITE_IOERR_DELETE,
it might show why that call to unlink() is failing. Printing out
"zPath" as well is probably a good idea.

Dan.




>
> Here are others information:
> - My problem occurs in Sparc/Solaris 10 system.
> - After my program error, the file try.db exists and has the correct
> right -rw-r--r--, correct owner/group and a null size.
>
> Regards,
>
> Sylvain
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: Thursday, February 03, 2011 3:16 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multithreading problem
>
> On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
>> Hi!
>>
>>
>>
>> I have a problem when I try to create a new database in a thread and
> try
>> to add a table on it.
>>
>>
>>
>> The following C code (see in the end of this e-mail) produces:
>
> The program is working Ok with 3.7.5 here.
>
> After the IO error in sqlite3_exec(), what value does
> sqlite3_extended_errcode() return?
>
> Dan.
>
>
> ___
> 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] Multithreading problem

[Black, Michael (IS)]

Can you "su" as the owner you are are expecting and see if you can delete it?
Maybe the directory permissions are messed up?

Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tiberio, Sylvain [sylvain.tibe...@cassidian.com]
Sent: Thursday, February 03, 2011 9:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Multithreading problem

Dan,

Thanks for your attention.

sqlite3_extended_errcode() return 0xA0A that means SQLITE_IOERR_DELETE.

Here are others information:
- My problem occurs in Sparc/Solaris 10 system.
- After my program error, the file try.db exists and has the correct
right -rw-r--r--, correct owner/group and a null size.

Regards,

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, February 03, 2011 3:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithreading problem

On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
> Hi!
>
>
>
> I have a problem when I try to create a new database in a thread and
try
> to add a table on it.
>
>
>
> The following C code (see in the end of this e-mail) produces:

The program is working Ok with 3.7.5 here.

After the IO error in sqlite3_exec(), what value does
sqlite3_extended_errcode() return?

Dan.


___
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] Attach to file in same directory

[BareFeetWare]

>> On 3 Feb 2011, at 2:59pm, BareFeetWare wrote:
>> 
>> But if a I have an arbitrary SQL script/procedure to perform, that starts 
>> with an attach statement, I don't have creation control over the path 
>> specified in the script.

> On 04/02/2011, at 2:18 AM, Simon Slavin wrote:
> 
> If you were able to open the original database without specifying a path, 
> then you can ATTACH to it another database from the same folder without a 
> path.  If you had to specify a path to open the original database, then use 
> the same path when you open the attached database.

It goes something like this:

1. User opens database file. App therefore knows location/path of the chosen 
file. But this path may be different next time (eg on different device).

2. User runs an SQL script/procedure containing an attach statement. The attach 
statement specifies a file, but no path, since the path is just the same 
directory containing the main file. In other words, the relative path doesn't 
change between runs, but the absolute path will.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Attach to file in same directory

[Luuk]

On 03-02-11 16:18, BareFeetWare wrote:
> What SQLite or C library call could I put before that to set the current 
> directory, that the sqlite3_prepare_v2 function would observe when processing 
> the attach statement?

i'm not a C-programmer but:
http://www.delorie.com/gnu/docs/glibc/libc_268.html
less than 5 minutes of Google, and some 'general' programming
experiance ;-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about database design

[Jay Kreibich]

On Feb 3, 2011, at 3:38 AM, Simon Slavin  wrote:

> SQLite creates some indexes the programmer doesn't specifically ask for: on 
> the rowid, on the primary key, and on any column declared as UNIQUE.  Of 
> course, in a particular table all three of these might actually be the same 
> column, so it might need just one.  But every table has at least one index.

The ROWID of a table doesn't have an "index," so it isn't really fair to say 
that every table has at least one index.  At least, not when using the word 
"index" to mean a secondary data structure in the database that must be updated 
and maintained in conjunction with the table data structure.

Both tables and indexes are stored within the SQLite file as a tree structure.  
The raw table data is stored in a tree structure that happens to be sorted by 
ROWID.  For every index, SQLite creates an addition tree structure that is 
sorted by whatever columns make up the index.  The structures are extremely 
similar, since they serve the same purpose.

Both explicit indexes (CREATE INDEX...), and implicit indexes (non-integer 
PRIMARY KEYs, UNIQUE constraints) create additional tree structures in the 
database.  However, if no PK is defined, or if the PK is defined as an INTEGER 
PRIMARY KEY, then no external index is created.  The only data structure is the 
table itself.

This is also why INTEGER PRIMARY KEYs are so desirable over other PK types.  
Not only do they reduce database size, by using the inherent structure of the 
main table data store as their "index," they also tend to be  about twice as 
fast at doing explicit row lookups (vs a traditional index), as only one tree 
structure needs to be searched to retrieve any column.  That speed difference 
means the break-even point for indexed lookup vs table scan for a set of  
INTEGER PK values is closer to 15% to 20% of the rows, vs the traditional 5% to 
10% rule of thumb that applies to standard indexes.

 -j



> 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] Multithreading problem

[Tiberio, Sylvain]

Dan,

Thanks for your attention.

sqlite3_extended_errcode() return 0xA0A that means SQLITE_IOERR_DELETE.

Here are others information:
- My problem occurs in Sparc/Solaris 10 system.
- After my program error, the file try.db exists and has the correct
right -rw-r--r--, correct owner/group and a null size.

Regards,

Sylvain

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy
Sent: Thursday, February 03, 2011 3:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Multithreading problem

On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
> Hi!
>
>
>
> I have a problem when I try to create a new database in a thread and
try
> to add a table on it.
>
>
>
> The following C code (see in the end of this e-mail) produces:

The program is working Ok with 3.7.5 here.

After the IO error in sqlite3_exec(), what value does
sqlite3_extended_errcode() return?

Dan.


___
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] Attach to file in same directory

[Simon Slavin]

On 3 Feb 2011, at 2:59pm, BareFeetWare wrote:

> But if a I have an arbitrary SQL script/procedure to perform, that starts 
> with an attach statement, I don't have creation control over the path 
> specified in the script.

If you were able to open the original database without specifying a path, then 
you can ATTACH to it another database from the same folder without a path.  If 
you had to specify a path to open the original database, then use the same path 
when you open the attached database.

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

Re: [sqlite] Attach to file in same directory

[BareFeetWare]

> On 04/02/2011, at 2:08 AM, Pavel Ivanov wrote:
> 
> What's wrong with the following suggestion to you?
> 
>>> Just start sqlite3 in such a way that the directory where your database 
>>> files reside is the current one.

Because I am not using "sqlite3", ie the command line utility.

> You can read it as: change your app so that before executing the script it 
> sets current directory to the one with the original database.

I am programming in Cocoa Touch and Objective C for my app, and using C for the 
standard SQLite calls.

I use the SQLite C function:

rc = sqlite3_prepare_v2(_db, cSQL, -1, , );

where cSQL is the C string containing the SQL attach statement.

What SQLite or C library call could I put before that to set the current 
directory, that the sqlite3_prepare_v2 function would observe when processing 
the attach statement?

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

[sqlite] Yet another question - this time about using two tables

[Ian Hardingham]

I have a table called multiturnTable which records games between two 
players, so has two fields "player1" and "player2".  Currently when, for 
instance, trying to find all games involving a specific player, I search 
based on player1=x OR player2=x.  I'm fairly sure this is anti-good db 
design.

I am considering adding a gamesInvolving table which would be simply:

TABLE gamesInvolving (id INTEGER PRIMARY KEY AUTOINCREMENT, player TEXT 
COLLATE NOCASE, multiturnId INT, won INT)

I would then replace my player1= OR player2= queries with some kind of 
join based on a select from gamesInvolving.

My two questions are simply:

1. Would you expect this to be significantly faster?
2. Is there a better way?

Thanks, as always.
Ian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Attach to file in same directory

[Pavel Ivanov]

>> Instead use operating system commands to retrieve the full path to the first 
>> file, then construct a full path to the second file.
>
> But if a I have an arbitrary SQL script/procedure to perform, that starts 
> with an attach statement, I don't have creation control over the path 
> specified in the script.

What's wrong with the following suggestion to you?

>> Just start sqlite3 in such a way that the directory where your database 
>> files reside is the current one.

You can read it as: change your app so that before executing the
script it sets current directory to the one with the original
database.


> For the moment, I've actually had to use a regex to change the SQL script to 
> insert a path if it's missing, but that is unscrambling the egg and will only 
> help my app, not if the database files and script are opened in another app.

If you want to execute such script with attach from different apps and
those apps don't know about your attach command and can't set current
directory accordingly that can only mean that you try to hack these
apps and perform some illegal operation in them. Don't do that.


Pavel

On Thu, Feb 3, 2011 at 9:59 AM, BareFeetWare  wrote:
>>> On 3 Feb 2011, at 1:03am, BareFeetWare wrote:
>>>
>>> How can I attach to a local file in the same directory, without specifying 
>>> the full absolute path?
>
>> On 03/02/2011, at 12:17 PM, Simon Slavin wrote:
>>
>> No easy way.
>
> Argh. That's kind of mental, that SQLite, a file based database system, can't 
> in SQL either tell you the path to the main database (eg via a function) or 
> let you open a secondary file in the same directory.
>
>> Instead use operating system commands to retrieve the full path to the first 
>> file, then construct a full path to the second file.
>
> But if a I have an arbitrary SQL script/procedure to perform, that starts 
> with an attach statement, I don't have creation control over the path 
> specified in the script.
>
> For the moment, I've actually had to use a regex to change the SQL script to 
> insert a path if it's missing, but that is unscrambling the egg and will only 
> help my app, not if the database files and script are opened in another app.
>
> I am replacing occurrences of:
>  attach\s+'([^/].*?)'
> with:
>  attach '/$1'
>
> Thanks,
> Tom
> BareFeetWare
>
>  --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?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] Attach to file in same directory

[BareFeetWare]

>> On 3 Feb 2011, at 1:03am, BareFeetWare wrote:
>> 
>> How can I attach to a local file in the same directory, without specifying 
>> the full absolute path?

> On 03/02/2011, at 12:17 PM, Simon Slavin wrote:
> 
> No easy way.

Argh. That's kind of mental, that SQLite, a file based database system, can't 
in SQL either tell you the path to the main database (eg via a function) or let 
you open a secondary file in the same directory.

> Instead use operating system commands to retrieve the full path to the first 
> file, then construct a full path to the second file.

But if a I have an arbitrary SQL script/procedure to perform, that starts with 
an attach statement, I don't have creation control over the path specified in 
the script.

For the moment, I've actually had to use a regex to change the SQL script to 
insert a path if it's missing, but that is unscrambling the egg and will only 
help my app, not if the database files and script are opened in another app.

I am replacing occurrences of:
  attach\s+'([^/].*?)'
with:
  attach '/$1'

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] SQLite3 and threading

[Stefano Mtangoo]

Thanks Pavel,
I consider my case closed though any thought is welcomed!

On 02/03/2011 05:53 PM, Pavel Ivanov wrote:
> It seems that this explanation as well as all other statements in the
> thread you linked are coming from the wrong assumption that SQLite's
> handles cannot be used from any thread other than the one created that
> handle. Although this was true in some earlier versions of SQLite it's
> not true in the current version. So if SQLite is compiled with
> THREADSAFE=1 (as mentioned in that thread) then you can do with it
> whatever you want. Just beware of possible data races and potentially
> uncommitted transactions because of some open statement handles. And
> if as you say there's no simultaneous access to the database from
> different threads then there's no difference in your usage pattern
> from single-threaded one.


-- 
_
The purpose of man is to know his Maker Be known by his Maker And make his 
Maker known So that others may know his Maker as their Maker(Emeal Zwayne)

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

Re: [sqlite] SQLite3 and threading

[Pavel Ivanov]

It seems that this explanation as well as all other statements in the
thread you linked are coming from the wrong assumption that SQLite's
handles cannot be used from any thread other than the one created that
handle. Although this was true in some earlier versions of SQLite it's
not true in the current version. So if SQLite is compiled with
THREADSAFE=1 (as mentioned in that thread) then you can do with it
whatever you want. Just beware of possible data races and potentially
uncommitted transactions because of some open statement handles. And
if as you say there's no simultaneous access to the database from
different threads then there's no difference in your usage pattern
from single-threaded one.


Pavel

On Thu, Feb 3, 2011 at 7:29 AM, Stefano Mtangoo  wrote:
>  From Urlich's explanation (I respect him as he is in the 'game of
> programming ' many years ahead me) is this, I quote:
> --
> This decreases the chance of failure but doesn't eliminate it, since
> still SQLite handles are passed around. As soon as the database thread
> accesses the database independently in parallel to the thread consuming
> the result set this could result in problems.
> ---
>
> So what advice do you give me in such need that are thread safe? Any
> other approach?
>
>
> On 02/03/2011 03:09 PM, Pavel Ivanov wrote:
>> What problems did you meet when you tried to do what you want?
>>
>>
>> Pavel
>>
>> On Thu, Feb 3, 2011 at 4:39 AM, Stefano Mtangoo  
>> wrote:
>>> Hi,
>>> I use SQLite3 with wxSQLite3 wrapper and all is fine until I wanted to
>>> shift the DB thing into the secondary thread.
>>> What I want to do is send string containing query to secondary thread
>>> and the secondary thread is supposed to query db and post back the
>>> resultset.
>>> Urlich had doubts about the approach and full posts is here:
>>> http://forum.wxwidgets.org/viewtopic.php?t=29991
>>>
>>>
>>> How can I do that with SQLite3? No access to database at the same time.
>>> With thanks,
>>> Stefano
>>>
>>> --
>>> _
>>> The purpose of man is to know his Maker Be known by his Maker And make his 
>>> Maker known So that others may know his Maker as their Maker(Emeal Zwayne)
>>>
>>> ___
>>> 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
>
>
> --
> _
> The purpose of man is to know his Maker Be known by his Maker And make his 
> Maker known So that others may know his Maker as their Maker(Emeal Zwayne)
>
> ___
> 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] Attach to file in same directory

[BareFeetWare]

>> On 2/2/2011 8:03 PM, BareFeetWare wrote:
>> I use the attach command to attach another SQLite database file that resides 
>> in the same directory as my main file. I tried:
>> 
>> attach 'Import.sqlitedb';
>> 
>> But it fails to find the file. If I specify the full path:
>> 
>> attach '/Users/tom/Documents/Work/Databases/Import.sqlitedb';
>> 
>> Then it works.

> On 03/02/2011, at 12:15 PM, Igor Tandetnik wrote:
> 
> Just as with any command line utility, relative paths are resolved relative 
> to the current working directory. Just start sqlite3 in such a way that the 
> directory where your database files reside is the current one. Read the fine 
> manual for your shell of choice.

I'm not using the command line utility, though my question would equally apply 
there.

>> How can I attach to a local file in the same directory, without specifying 
>> the full absolute path?
> 
> How did you manage to open the main file in the first place? Specify the path 
> to the second file in the same manner.

I could be using any SQLite utility, whether the command line, SQLite Manager, 
Froq etc. I happen to be using my own app, developed for the iPad/iPhone. In 
any of these environments, I open my main SQLite data file, then want to run an 
SQL script/procedure which starts with an attach command. I don't want to and 
usually can't edit the script each time to insert a different path in the 
attach file name.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml



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

Re: [sqlite] Multithreading problem

[Dan Kennedy]

On 02/02/2011 09:31 PM, Tiberio, Sylvain wrote:
> Hi!
>
>
>
> I have a problem when I try to create a new database in a thread and try
> to add a table on it.
>
>
>
> The following C code (see in the end of this e-mail) produces:

The program is working Ok with 3.7.5 here.

After the IO error in sqlite3_exec(), what value does
sqlite3_extended_errcode() return?

Dan.


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

[sqlite] Multithreading problem

[Tiberio, Sylvain]

Hi!

 

I have a problem when I try to create a new database in a thread and try
to add a table on it.

 

The following C code (see in the end of this e-mail) produces:

in SQLite 3.7.5: Disk I/O error (same problem with 3.7.4)

  SQLite Treadsafe. Yes (1).

  SQLite Lib version... 3.7.5.

  SQLite Lib vernumber. 3007005.

  Open/Create try.db...

  Exec 'CREATE TABLE test (id INTEGER PRIMARY KEY, name TEST)'...

  !!! ERROR:Can't exec: disk I/O error(10)

  Close...

 

In SQlite 3.6.22: No problem

  SQLite Treadsafe. Yes (1).

  SQLite Lib version... 3.6.22.

  SQLite Lib vernumber. 3006022.

  Open/Create try.db...

  Exec 'CREATE TABLE test (id INTEGER PRIMARY KEY, name TEST)'...

  Close...

 

If I open the database in the main function (moving openDatabase from
test() to main()), there is no problem with both SQLite version.

 

Any ideas?

 

Regards,

 

Sylvain

 

 

 

== C Source Code =

 

#include 

#include 

#include 

#include 

 

static sqlite3   *db_p=NULL;

static const int  openMode=SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;

static char  *errmsg_p=NULL;

 

static void openDatabase(const char *filename_p)

{

  printf("Open/Create %s...\n",filename_p);

  int rc = sqlite3_open_v2(filename_p, _p,openMode,NULL);

  if ( rc ) printf("Can't open database %s: %s(%d)\n", filename_p,
sqlite3_errmsg(db_p), rc);

}

 

static void closeDatabase(void)

{

  printf("Close...\n");

  int rc = sqlite3_close(db_p);

  if ( rc ) printf("Can't close database: %s(%d)\n",
sqlite3_errmsg(db_p), rc);

}

 

static void exec(const char* query_p)

{

  printf("Exec '%s'...\n",query_p);

  int rc = sqlite3_exec(db_p,query_p,NULL,NULL,_p);

  if ( rc ) printf("!!! ERROR:Can't exec: %s(%d)\n", errmsg_p, rc);

}

 

static void* test(void *arg_p)

{

  openDatabase((char*)arg_p);

  exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEST)");

  closeDatabase();

  return NULL;

}

 

static void getConfig(void)

{

  printf("SQLite Treadsafe. %s
(%d).\n",sqlite3_threadsafe()==0?"No":"Yes",sqlite3_threadsafe());

  printf("SQLite Lib version... %s.\n",sqlite3_libversion());

  printf("SQLite Lib vernumber. %d.\n",sqlite3_libversion_number());

}

 

int main(int agrc, char *argv[])

{

  getConfig();

 

  pthread_t id;

  int rc=pthread_create(,NULL,test,(void*)"try.db");

  if ( rc ) perror("pthread_create");

  pthread_join(id,NULL);

}

 

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

Re: [sqlite] SQLite3 and threading

[Stefano Mtangoo]

 From Urlich's explanation (I respect him as he is in the 'game of 
programming ' many years ahead me) is this, I quote:
--
This decreases the chance of failure but doesn't eliminate it, since 
still SQLite handles are passed around. As soon as the database thread 
accesses the database independently in parallel to the thread consuming 
the result set this could result in problems.
---

So what advice do you give me in such need that are thread safe? Any 
other approach?


On 02/03/2011 03:09 PM, Pavel Ivanov wrote:
> What problems did you meet when you tried to do what you want?
>
>
> Pavel
>
> On Thu, Feb 3, 2011 at 4:39 AM, Stefano Mtangoo  wrote:
>> Hi,
>> I use SQLite3 with wxSQLite3 wrapper and all is fine until I wanted to
>> shift the DB thing into the secondary thread.
>> What I want to do is send string containing query to secondary thread
>> and the secondary thread is supposed to query db and post back the
>> resultset.
>> Urlich had doubts about the approach and full posts is here:
>> http://forum.wxwidgets.org/viewtopic.php?t=29991
>>
>>
>> How can I do that with SQLite3? No access to database at the same time.
>> With thanks,
>> Stefano
>>
>> --
>> _
>> The purpose of man is to know his Maker Be known by his Maker And make his 
>> Maker known So that others may know his Maker as their Maker(Emeal Zwayne)
>>
>> ___
>> 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


-- 
_
The purpose of man is to know his Maker Be known by his Maker And make his 
Maker known So that others may know his Maker as their Maker(Emeal Zwayne)

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

Re: [sqlite] SQLite3 and threading

[Pavel Ivanov]

What problems did you meet when you tried to do what you want?


Pavel

On Thu, Feb 3, 2011 at 4:39 AM, Stefano Mtangoo  wrote:
> Hi,
> I use SQLite3 with wxSQLite3 wrapper and all is fine until I wanted to
> shift the DB thing into the secondary thread.
> What I want to do is send string containing query to secondary thread
> and the secondary thread is supposed to query db and post back the
> resultset.
> Urlich had doubts about the approach and full posts is here:
> http://forum.wxwidgets.org/viewtopic.php?t=29991
>
>
> How can I do that with SQLite3? No access to database at the same time.
> With thanks,
> Stefano
>
> --
> _
> The purpose of man is to know his Maker Be known by his Maker And make his 
> Maker known So that others may know his Maker as their Maker(Emeal Zwayne)
>
> ___
> 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 database design

[Simon Slavin]

On 3 Feb 2011, at 10:43am, Mihai Militaru wrote:

> Nicolas Williams  wrote:
> 
>>> Any idea why pg does ok on these queries without the extra index -
>>> Maybe they're created by default?  SQLIte doesn't create any indexes
>>> automatically on primary key fields or anything else, correct?
>> 
>> No, it doesn't.  Use EXPLAIN QUERY PLAN to see what SQLite3 is doing.
> 
> Hmm SqLite does create persistent indices on UNIQUE - and consequently
> PRIMARY - keys, doesn't it?

SQLite creates some indexes the programmer doesn't specifically ask for: on the 
rowid, on the primary key, and on any column declared as UNIQUE.  Of course, in 
a particular table all three of these might actually be the same column, so it 
might need just one.  But every table has at least one index.

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

Re: [sqlite] WAL for production use

[Alexey Pechnikov]

I did see some persistent database locks in WAL mode in SQLite 3.7.4 and
previous versions (I don't test 3.7.5 yet ) and all queries are failed with
message about database locked by write query. So we need vacuum database,
try change to "delete" mode and vacuum again... In "delete" mode these
applications work fine a lot of time. But I can't write tests for
reproducing the problem. My hosts are using 32bit and 64bit debian linux
(squeeze) with ext3 filesystem. So WAL may be dangerous for some projects
now.

2011/2/3 Dustin Sallings 

>
> On Feb 2, 2011, at 8:14, Duquette, William H (318K) wrote:
>
> > In SQLite 3.7.4/3.7.5, does WAL seem to be stable enough for production
> use?
>
>
> I'm using it very, very heavily right now.
>
> --
> dustin sallings
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Question about database design

[Mihai Militaru]

On Wed, 2 Feb 2011 18:59:48 -0600
Nicolas Williams  wrote:

> > Any idea why pg does ok on these queries without the extra index -
> > Maybe they're created by default?  SQLIte doesn't create any indexes
> > automatically on primary key fields or anything else, correct?
> 
> No, it doesn't.  Use EXPLAIN QUERY PLAN to see what SQLite3 is doing.

Hmm SqLite does create persistent indices on UNIQUE - and consequently
PRIMARY - keys, doesn't it?

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

[sqlite] SQLite3 and threading

[Stefano Mtangoo]

Hi,
I use SQLite3 with wxSQLite3 wrapper and all is fine until I wanted to 
shift the DB thing into the secondary thread.
What I want to do is send string containing query to secondary thread 
and the secondary thread is supposed to query db and post back the 
resultset.
Urlich had doubts about the approach and full posts is here:
http://forum.wxwidgets.org/viewtopic.php?t=29991


How can I do that with SQLite3? No access to database at the same time.
With thanks,
Stefano

-- 
_
The purpose of man is to know his Maker Be known by his Maker And make his 
Maker known So that others may know his Maker as their Maker(Emeal Zwayne)

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