Re: [sqlite] pragma vs select for introspection

[Darren Duncan]

Darren Duncan wrote:
> Wols Lists wrote:
>> Dunno how well that approach translates into a relational engine,
>> because Pick has several very non-relational quirks (every "row" MUST
>> have a primary key, the dictionary DEscribes, not PREscribes the FILE,
>> etc etc).
> 
> Can you say more about this last paragraph.  These last couple items don't 
> necessarily mean that Pick is non-relational given how they can be 
> interpreted. 
>   (I don't know anything about Pick.)

Actually, nevermind.  Google is your friend. -- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma vs select for introspection

[Darren Duncan]

Wols Lists wrote:
> On 12/12/10 00:29, Darren Duncan wrote:
>> Nonsense.  An information schema is a *good* thing, and is generally the 
>> *best* 
>> tool for introspecting a database.  It lets you use all the power features 
>> you 
>> have when querying data, anything a SELECT can do, and you can query the 
>> database structure likewise.  This is the way a relational database is 
>> supposed 
>> to work. -- Darren Duncan
>> ___
> Okay, I'm not describing a relational database ...
> 
> But one of the very nice features of Pick is it is self-describing. The
> top level is a pick "table" called MD. This is described by a Pick
> "table" called the dictionary. Everything in Pick is a FILE (the Pick
> name for a table), from the top down. Even indices.
> 
> Dunno how well that approach translates into a relational engine,
> because Pick has several very non-relational quirks (every "row" MUST
> have a primary key, the dictionary DEscribes, not PREscribes the FILE,
> etc etc).

Can you say more about this last paragraph.  These last couple items don't 
necessarily mean that Pick is non-relational given how they can be interpreted. 
  (I don't know anything about Pick.)

> But it means you can use your standard query tools to query EVERYTHING.

-- Darren Duncan

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

Re: [sqlite] R: R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Sylvain Pointeau]

What does it mean?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma vs select for introspection

[Wols Lists]

On 12/12/10 00:29, Darren Duncan wrote:
> Petite Abeille wrote:
>> On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote:
>>
>>> Section 21 of the (SQL92) standard.
>> Yes, the notorious information schema:
> Nonsense.  An information schema is a *good* thing, and is generally the 
> *best* 
> tool for introspecting a database.  It lets you use all the power features 
> you 
> have when querying data, anything a SELECT can do, and you can query the 
> database structure likewise.  This is the way a relational database is 
> supposed 
> to work. -- Darren Duncan
> ___
Okay, I'm not describing a relational database ...

But one of the very nice features of Pick is it is self-describing. The
top level is a pick "table" called MD. This is described by a Pick
"table" called the dictionary. Everything in Pick is a FILE (the Pick
name for a table), from the top down. Even indices.

Dunno how well that approach translates into a relational engine,
because Pick has several very non-relational quirks (every "row" MUST
have a primary key, the dictionary DEscribes, not PREscribes the FILE,
etc etc).

But it means you can use your standard query tools to query EVERYTHING.

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

[sqlite] R: R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Marco Turco]

Hi,
tried but unfortunately it doesn't runs.
The window application running under Wine hasn't direct access to the
absolute path.

Marco


-Messaggio originale-
Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Per conto di Sylvain Pointeau
Inviato: domenica 12 dicembre 2010 22:44
A: General Discussion of SQLite Database
Oggetto: Re: [sqlite] R: R: R: Lock problem opening a Sqlite db on a
Samba/CIFS shared disk

... and if you use the "unix-dotfile" as the VFS name in your open call.
Does it work?
___
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] R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Sylvain Pointeau]

... and if you use the "unix-dotfile" as the VFS name in your open call.
Does it work?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] R: R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Marco Turco]

>Argh.  Any chance of trying it with something other than Vista ?  I don't
know it's definitely the problem, I'm just allergic to Vista.
;-)) I agree with you. I connected now the Mac to a Window XP 2002 sp3. The
same lock problem remains.
 
>One stage of that process is defeating the locking process.  Please try
mapping the shared drive directly from the Wine stage.
I think it isn't possible. It seems Wine only permit to assign a drive
letter to an already mounted remote drive.

>That bug was fixed years ago.  And I think that if it was a problem your
Macintosh application would have the same problem.  However, if you want to
see how to mount that drive from the command-line on a Mac, type 'man
mount_smbfs'.
 
Tried. Unfortunately seems that the "nobrl" option is not available.
Gr...

Marco


-Messaggio originale-
Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Per conto di Simon Slavin
Inviato: domenica 12 dicembre 2010 22:09
A: General Discussion of SQLite Database
Oggetto: Re: [sqlite] R: R: Lock problem opening a Sqlite db on a Samba/CIFS
shared disk


On 12 Dec 2010, at 8:31pm, Marco Turco wrote:

>> What OS (including version) is the host computer running ?
> Windows Vista Business sp2

Argh.  Any chance of trying it with something other than Vista ?  I don't
know it's definitely the problem, I'm just allergic to Vista.

>> How is Wine accessing the server ?  Did you mount the server in the
> Macintosh layer, using an 'SMB://' URL, or did you use the Windows 
> facilities to mount it inside Wine ?
> I mounted the server using the Mac layer (finder->Connect to server) 
> then I mapped a drive (Z:\) into the Wine configuration.

One stage of that process is defeating the locking process.  Please try
mapping the shared drive directly from the Wine stage.

> It seems the problem is due to a bug on the debian distribution of 
> Samba and the only way to solve it is at this moment to mount the net 
> disk using the nobrl parameter see 
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg20409.html
> anyway I am not sure this parameter is supported on Mac Os X mount 
> command but  I am not an expert in Mac & Unix Os.

That bug was fixed years ago.  And I think that if it was a problem your
Macintosh application would have the same problem.  However, if you want to
see how to mount that drive from the command-line on a Mac, type 'man
mount_smbfs'.

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] R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Simon Slavin]

On 12 Dec 2010, at 8:31pm, Marco Turco wrote:

>> What OS (including version) is the host computer running ?
> Windows Vista Business sp2

Argh.  Any chance of trying it with something other than Vista ?  I don't know 
it's definitely the problem, I'm just allergic to Vista.

>> How is Wine accessing the server ?  Did you mount the server in the
> Macintosh layer, using an 'SMB://' URL, or did you use the Windows
> facilities to mount it inside Wine ?
> I mounted the server using the Mac layer (finder->Connect to server) then I
> mapped a drive (Z:\) into the Wine configuration.

One stage of that process is defeating the locking process.  Please try mapping 
the shared drive directly from the Wine stage.

> It seems the problem is due to a bug on the debian distribution of Samba and
> the only way to solve it is at this moment to mount the net disk using the
> nobrl parameter see
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg20409.html
> anyway I am not sure this parameter is supported on Mac Os X mount command
> but  I am not an expert in Mac & Unix Os.

That bug was fixed years ago.  And I think that if it was a problem your 
Macintosh application would have the same problem.  However, if you want to see 
how to mount that drive from the command-line on a Mac, type 'man mount_smbfs'.

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

[sqlite] R: R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Marco Turco]

>The situation you're having a problem with ... am I right in saying that
the application with problems is a Windows application running inside the
Wine layer ?
Yes. 

>What OS (including version) is the host computer running ?
Windows Vista Business sp2

>What OS (including version) is the client computer running ?  Include the
version of Wine if Wine is involved.
Mac OS X 10.5.8. Wine 1.1.44

>How is Wine accessing the server ?  Did you mount the server in the
Macintosh layer, using an 'SMB://' URL, or did you use the Windows
facilities to mount it inside Wine ?
I mounted the server using the Mac layer (finder->Connect to server) then I
mapped a drive (Z:\) into the Wine configuration.

It seems the problem is due to a bug on the debian distribution of Samba and
the only way to solve it is at this moment to mount the net disk using the
nobrl parameter see
http://www.mail-archive.com/sqlite-users@sqlite.org/msg20409.html
anyway I am not sure this parameter is supported on Mac Os X mount command
but  I am not an expert in Mac & Unix Os.

Any ideas ?

Marco

-Messaggio originale-
Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Per conto di Simon Slavin
Inviato: domenica 12 dicembre 2010 21:13
A: General Discussion of SQLite Database
Oggetto: Re: [sqlite] R: Lock problem opening a Sqlite db on a Samba/CIFS
shared disk


On 12 Dec 2010, at 7:58pm, Marco Turco wrote:

> this problem appear only in accessing the sqlite database from Mac OS 
> X on a Windows shared disk.

The situation you're having a problem with ... am I right in saying that the
application with problems is a Windows application running inside the Wine
layer ?

> On full Windows networks and also on Mac OS X in local all runs fine.

What OS (including version) is the host computer running ?

What OS (including version) is the client computer running ?  Include the
version of Wine if Wine is involved.

How is Wine accessing the server ?  Did you mount the server in the
Macintosh layer, using an 'SMB://' URL, or did you use the Windows
facilities to mount it inside Wine ?

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] R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Simon Slavin]

On 12 Dec 2010, at 7:58pm, Marco Turco wrote:

> this problem appear only in accessing the sqlite database from Mac OS X on a
> Windows shared disk.

The situation you're having a problem with ... am I right in saying that the 
application with problems is a Windows application running inside the Wine 
layer ?

> On full Windows networks and also on Mac OS X in local all runs fine.

What OS (including version) is the host computer running ?

What OS (including version) is the client computer running ?  Include the 
version of Wine if Wine is involved.

How is Wine accessing the server ?  Did you mount the server in the Macintosh 
layer, using an 'SMB://' URL, or did you use the Windows facilities to mount it 
inside Wine ?

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

Re: [sqlite] Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Kees Nuyt]

On Sun, 12 Dec 2010 20:09:49 +0100, "Marco Turco"
 wrote:

>Hi all,
>
>I am having a problem running my Window Sqlite app on Mac OS X with Wine
>emulator.
>
>It runs well in local but when I try to access to a network disk hosted on
>Windows XP then a lock error appears.
>
>I checked on internet about this and as I know Sqlite at this moment doesn't
>support the Samba/CIFS disk with reference to the locking system.
>
>I am really in trouble because I have more than 50 customers with mixed
>network (Windows/Mac OS X) having this problem in the next future.
>
>Any ideas or turn-around to solve this problem ?

Alternatives:

1) Use a database server, like PostgreSQL.
http://www.sqlite.org/whentouse.html
You can develop with SQLite and run production with PostgreSQL,
because the SQL 'dialect' of postgres and sqlite are alike.

2) Redevelop your application so it accesses your sqlite databases
via a webinterface. That way the webserver is the only one to access
the sqlite files.
Make sure they are on local disk or SAN, not on a SMB or NFS share.

3) Use a SQLite stub on the client, connecting to a central SQLite
server instance.
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork

Hope this helps.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] R: Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Marco Turco]

Hi,
this problem appear only in accessing the sqlite database from Mac OS X on a
Windows shared disk.
On full Windows networks and also on Mac OS X in local all runs fine.

I tried with the oplock enable and disable on the Window server but the
Sqlite db always remains locked from the Mac.

I have more than 97% of my customers that work stand-alone or in a network
environment with less than 4 computer 
and I haven't any control on the hardware they use so I think the Sqlite
solution is preferable instead of a client-server db. 
I have also some customers with 10-12 computer but due the kind of product
there isn't an high concurrency in writing
and tracking the locks it appears that only 4-5 locks/day for just 0.3
seconds each one executed on these bigger network. 

Marco


-Messaggio originale-
Da: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
Per conto di Simon Slavin
Inviato: domenica 12 dicembre 2010 20:38
A: General Discussion of SQLite Database
Oggetto: Re: [sqlite] Lock problem opening a Sqlite db on a Samba/CIFS
shared disk


On 12 Dec 2010, at 7:09pm, Marco Turco wrote:

> I am having a problem running my Window Sqlite app on Mac OS X with 
> Wine emulator.
> 
> It runs well in local but when I try to access to a network disk 
> hosted on Windows XP

The Wine emulator is an excellent emulator but it doesn't correctly emulate
all the obscure elements of Windows.  Please try it on a proper Windows
computer.

> I checked on internet about this and as I know Sqlite at this moment 
> doesn't support the Samba/CIFS disk with reference to the locking system.

Sorry, I don't know.  However if you have both Mac and Windows clients
trying to access the database simultaneously, you should be very careful
with your settings for oplocks and such things.  Perhaps someone with
experience of that kind of setup can help.

> I am really in trouble because I have more than 50 customers with 
> mixed network (Windows/Mac OS X) having this problem in the next future.

I know this is not what you asked but I have advice.  If you have users with
more than 5 or ten computers trying to access the database simultaneously,
you may want to use a proper multi-user SQL engine instead of SQLite.  If
you use a SQL engine with client/server architecture no disk locking is
done: the only computer actually accessing the database files is the server.

I'm not saying that SQLite will fail, I'm saying that MySQL (and several
similar systems) are specially designed for simultaneous multi-user access,
and they do the job without having to worry about file locking and access
from different types of computer.

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] Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Simon Slavin]

On 12 Dec 2010, at 7:09pm, Marco Turco wrote:

> I am having a problem running my Window Sqlite app on Mac OS X with Wine
> emulator.
> 
> It runs well in local but when I try to access to a network disk hosted on
> Windows XP 

The Wine emulator is an excellent emulator but it doesn't correctly emulate all 
the obscure elements of Windows.  Please try it on a proper Windows computer.

> I checked on internet about this and as I know Sqlite at this moment doesn't
> support the Samba/CIFS disk with reference to the locking system.

Sorry, I don't know.  However if you have both Mac and Windows clients trying 
to access the database simultaneously, you should be very careful with your 
settings for oplocks and such things.  Perhaps someone with experience of that 
kind of setup can help.

> I am really in trouble because I have more than 50 customers with mixed
> network (Windows/Mac OS X) having this problem in the next future.

I know this is not what you asked but I have advice.  If you have users with 
more than 5 or ten computers trying to access the database simultaneously, you 
may want to use a proper multi-user SQL engine instead of SQLite.  If you use a 
SQL engine with client/server architecture no disk locking is done: the only 
computer actually accessing the database files is the server.

I'm not saying that SQLite will fail, I'm saying that MySQL (and several 
similar systems) are specially designed for simultaneous multi-user access, and 
they do the job without having to worry about file locking and access from 
different types of computer.

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

Re: [sqlite] Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Mathieu Schroeter]

Le 12. 12. 10 20:09, Marco Turco a écrit :
> Hi all,
> 
> I am having a problem running my Window Sqlite app on Mac OS X with Wine
> emulator.
> 
> It runs well in local but when I try to access to a network disk hosted on
> Windows XP 
> 
> then a lock error appears.
> 
> I checked on internet about this and as I know Sqlite at this moment doesn't
> support the Samba/CIFS disk with reference to the locking system.
> 
> I am really in trouble because I have more than 50 customers with mixed
> network (Windows/Mac OS X) having this problem in the next future.

Hi,

It works fine for me with a *Linux* Samba Server (SMB1). it is very bugged
with the SMB1, SMB2 and SMB2.1 provided by Microsoft. Even if the Oplocks
are disabled with SMB1.

-- 
Mathieu SCHROETER

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

[sqlite] Lock problem opening a Sqlite db on a Samba/CIFS shared disk

[Marco Turco]

Hi all,

I am having a problem running my Window Sqlite app on Mac OS X with Wine
emulator.

 

It runs well in local but when I try to access to a network disk hosted on
Windows XP 

then a lock error appears.

 

I checked on internet about this and as I know Sqlite at this moment doesn't
support the Samba/CIFS disk with reference to the locking system.

I am really in trouble because I have more than 50 customers with mixed
network (Windows/Mac OS X) having this problem in the next future.

 

Any ideas or turn-around to solve this problem ?

 

Thanks in advance

 

Marco Turco

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

Re: [sqlite] Inserting data using a compound SELECT

[Simon Slavin]

On 12 Dec 2010, at 5:39pm, George Roberge wrote:

> And the "why" of this particular query: I don't know any better...LOL.  
> I'm tooling around with the database as I read my SQL books, and try 
> experiments on the fly.  Eventually I'll try to piece together a nice 
> functioning GUI or a better web-based movie database than I have now, 
> but it'll take a little while.

Generally speaking, avoid the compound statements like INSERT ... SELECT and 
SELECT ... SELECT.  They are complicated and slow.  For your INSERT just supply 
the values you need, and if you need a two-table SELECT try to find an 
appropriate SELECT ... JOIN.

As you wrote above, you should definitely read a new books or web sites on SQL 
for beginners.

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

Re: [sqlite] Inserting data using a compound SELECT

[George Roberge]

YES, that was it, thank you!

It was a simple issue of the word "values" and the addition of the extra 
parentheses.  I can see it now: the two SELECTS in parentheses are the 
equivalent of the "values" that would be entered.

Looks simple after seeing it.  Maybe I should have taken a step back for 
a couple of hours..and had more coffee!!

To round out the question: I don't have Access at home, and I'm learning 
SQLite as an easier and more portable alternative to PostgreSQL.  I 
don't use Windows at home, and don't want to.

And the "why" of this particular query: I don't know any better...LOL.  
I'm tooling around with the database as I read my SQL books, and try 
experiments on the fly.  Eventually I'll try to piece together a nice 
functioning GUI or a better web-based movie database than I have now, 
but it'll take a little while.

Thank you again for your reples!  This list has been very helpful and 
educational!
George R.

Igor Tandetnik wrote:
> Drake Wilson  wrote:
>   
>>  INSERT INTO "cast" (titleID, castID)
>>((SELECT titleID FROM titles WHERE title = 'Alien'),
>> (SELECT artistID FROM artists WHERE lastname = 'Weaver'));
>> 
>
> Make it
>
>   INSERT INTO "cast" (titleID, castID)
>   VALUES
> ((SELECT titleID FROM titles WHERE title = 'Alien'),
>  (SELECT artistID FROM artists WHERE lastname = 'Weaver'));
>
> Note the added VALUES keyword.
>   

-- 
www.andforthelamb.org
www.outreachhockey.org

They deem me mad for I will not sell my days for gold; I deem them mad for they 
think my days have a price.


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

Re: [sqlite] Inserting data using a compound SELECT

[George Roberge]

Ah, yes, I'm sorry: I mistyped.  I have "titleID" and "artistID" in that 
table.



Puneet Kishor wrote:
>
>
> George Roberge wrote:
>> Greetings, all.
>>
>> I'm having trouble getting an insert statement to function properly, and
>> am not sure if it is me, or this isn't supposed to be done.
>>
>> This is not the complete database, but to keep this issue simple:
>>
>> I have three tables:
>>
>> artists (artistID integer, lastname text) contains 1, 'Weaver'
>> titles (titleID integer, title text) contains 1, 'Alien'
>> cast (castID, artistID)
>
>
> Note the definition of the table 'cast' -- it has columns 'castID' and 
> 'artistID')
>
>>
>> I am experimenting with inserting data into the cast table using values
>> that will be entered either by a user or selected from a list.  In the
>> meantime, I am manually entering the values "Weaver" and "Alien" to
>> acquire title ID number and artist ID to store into the cast table.
>>
>> This simple statement works fine and yield the expected "1": insert into
>> cast (titleID) select titleID from titles where title='Alien';
>>
>> I get into trouble when I attempt to add the extra field: insert into
>> cast (titleID, castID) select titleID from titles where title='Alien'
>
>
> Note what you are trying to insert into cast above, namely 'titleID' 
> and 'castID'. Per your table definition, you have no 'titleID' in the 
> table 'cast'
>
>
>> select artistID from artists where lastname='Weaver';
>>
>> I know that the above syntax is incorrect.  I have tried adding
>> parentheses around the select statements (SELECT XXX), (SELECT XXX) and
>> have tried the UNION in between, which I now understand adds separate
>> rows into the table.  I assumed that I could replace simple values with
>> SELECT statements, but either I am wrong, or I have bogus syntax.
>>
>> I am new to using SQL outside of the "drag-and-drop" Access method, so
>> this might be a silly question.
>>
>> If this can't be done I suppose I could add one value as a SELECT
>> statement, then try to locate that row in the table and do an UPDATE to
>> the other column (in this case, the cast column) where I just inserted
>> the data.
>>
>> Thank you!
>> George R.
>>
>>
>>
>>
>>
>>
>
>
>

-- 
www.andforthelamb.org
www.outreachhockey.org

They deem me mad for I will not sell my days for gold; I deem them mad for they 
think my days have a price.


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

Re: [sqlite] Inserting data using a compound SELECT

[Drake Wilson]

Quoth Igor Tandetnik , on 2010-12-12 10:30:24 -0500:
> Make it
> 
>   INSERT INTO "cast" (titleID, castID)
>   VALUES
> ((SELECT titleID FROM titles WHERE title = 'Alien'),
>  (SELECT artistID FROM artists WHERE lastname = 'Weaver'));
> 
> Note the added VALUES keyword.

Oh yes.  D'oh!  I think I accidentally hit kill-word before sending;
sorry about that.  (The other response about the table definitions is
useful too.)

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

Re: [sqlite] Inserting data using a compound SELECT

[Igor Tandetnik]

Drake Wilson  wrote:
>  INSERT INTO "cast" (titleID, castID)
>((SELECT titleID FROM titles WHERE title = 'Alien'),
> (SELECT artistID FROM artists WHERE lastname = 'Weaver'));

Make it

  INSERT INTO "cast" (titleID, castID)
  VALUES
((SELECT titleID FROM titles WHERE title = 'Alien'),
 (SELECT artistID FROM artists WHERE lastname = 'Weaver'));

Note the added VALUES keyword.
-- 
Igor Tandetnik

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

Re: [sqlite] Inserting data using a compound SELECT

[Drake Wilson]

Quoth George Roberge , on 2010-12-12 09:49:13 -0500:
> I get into trouble when I attempt to add the extra field: insert into 
> cast (titleID, castID) select titleID from titles where title='Alien' 
> select artistID from artists where lastname='Weaver';

There's two INSERT syntaxes.  One takes only a SELECT and inserts all
the rows from it, and one takes a list of expressions (which might
include subquery SELECTs) and inserts one row with the resulting
values.  Expression INSERT is clearer in this case, and requires
parentheses around the entire expr list and commas to separate the
exprs.  Then, a subquery requires parentheses to separate it from its
surroundings.

So:

  INSERT INTO "cast" (titleID, castID)
((SELECT titleID FROM titles WHERE title = 'Alien'),
 (SELECT artistID FROM artists WHERE lastname = 'Weaver'));

"cast" can also be an SQL keyword, so it's better to quote it as an
identifier.  The keywords don't need to be in all-caps, but I tend to
prefer that for stylistic reasons.

I'm curious why you're doing this type of query in the first place,
though, especially since you mention that your interactive experience
is mostly with Access.  Having additional context might allow more
useful suggestions beyond purely syntactic issues.

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

Re: [sqlite] new user

[Artur Reilin]

> I couldn't understand, development environment is 32-bit version. However,
> production environment is 64-bit version.
>

I think that's the problem. As I read somewhere on the mailing list you
need to set flags in sqlite for 64bit system. But I can be wrong.

I also think there were a similar question already somewhere in the archives.

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

Re: [sqlite] Inserting data using a compound SELECT

[Puneet Kishor]

George Roberge wrote:
> Greetings, all.
>
> I'm having trouble getting an insert statement to function properly, and
> am not sure if it is me, or this isn't supposed to be done.
>
> This is not the complete database, but to keep this issue simple:
>
> I have three tables:
>
> artists (artistID integer, lastname text) contains 1, 'Weaver'
> titles (titleID integer, title text) contains 1, 'Alien'
> cast (castID, artistID)


Note the definition of the table 'cast' -- it has columns 'castID' and 
'artistID')

>
> I am experimenting with inserting data into the cast table using values
> that will be entered either by a user or selected from a list.  In the
> meantime, I am manually entering the values "Weaver" and "Alien" to
> acquire title ID number and artist ID to store into the cast table.
>
> This simple statement works fine and yield the expected "1": insert into
> cast (titleID) select titleID from titles where title='Alien';
>
> I get into trouble when I attempt to add the extra field: insert into
> cast (titleID, castID) select titleID from titles where title='Alien'


Note what you are trying to insert into cast above, namely 'titleID' and 
'castID'. Per your table definition, you have no 'titleID' in the table 
'cast'


> select artistID from artists where lastname='Weaver';
>
> I know that the above syntax is incorrect.  I have tried adding
> parentheses around the select statements (SELECT XXX), (SELECT XXX) and
> have tried the UNION in between, which I now understand adds separate
> rows into the table.  I assumed that I could replace simple values with
> SELECT statements, but either I am wrong, or I have bogus syntax.
>
> I am new to using SQL outside of the "drag-and-drop" Access method, so
> this might be a silly question.
>
> If this can't be done I suppose I could add one value as a SELECT
> statement, then try to locate that row in the table and do an UPDATE to
> the other column (in this case, the cast column) where I just inserted
> the data.
>
> Thank you!
> George R.
>
>
>
>
>
>



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

[sqlite] new user

[Limbu Bhagi Raj]

Dear sir or madam:

I am new user of sqlite. I developed application in asp.net 3.5 and it works 
perfect in development environment. However, After i deploy my project in 
windows server 2008 IIS 7.0, It throws an error message. 

A message contain "Unable to load DLL 'sqlite3': The specified module could not 
be found. (Exception from HRESULT: 0x8007007E)
Stack Trace:   at System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_v2(Byte[] 
utf8Filename, IntPtr& db, Int32 flags, IntPtr vfs)
 at System.Data.SQLite.SQLite3.Open(String strFilename, SQLiteOpenFlagsEnum 
flags, Int32 maxPoolSize, Boolean usePool)
   at System.Data.SQLite.SQLiteConnection.Open()
"
I couldn't understand, development environment is 32-bit version. However, 
production environment is 64-bit version.



Could you provide some idea, how can i handle in such situation.



Regards and metta,
Ichiorhang


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

[sqlite] Inserting data using a compound SELECT

[George Roberge]

Greetings, all.

I'm having trouble getting an insert statement to function properly, and 
am not sure if it is me, or this isn't supposed to be done.

This is not the complete database, but to keep this issue simple:

I have three tables:

artists (artistID integer, lastname text) contains 1, 'Weaver'
titles (titleID integer, title text) contains 1, 'Alien'
cast (castID, artistID)

I am experimenting with inserting data into the cast table using values 
that will be entered either by a user or selected from a list.  In the 
meantime, I am manually entering the values "Weaver" and "Alien" to 
acquire title ID number and artist ID to store into the cast table.

This simple statement works fine and yield the expected "1": insert into 
cast (titleID) select titleID from titles where title='Alien';

I get into trouble when I attempt to add the extra field: insert into 
cast (titleID, castID) select titleID from titles where title='Alien' 
select artistID from artists where lastname='Weaver';

I know that the above syntax is incorrect.  I have tried adding 
parentheses around the select statements (SELECT XXX), (SELECT XXX) and 
have tried the UNION in between, which I now understand adds separate 
rows into the table.  I assumed that I could replace simple values with 
SELECT statements, but either I am wrong, or I have bogus syntax.

I am new to using SQL outside of the "drag-and-drop" Access method, so 
this might be a silly question.

If this can't be done I suppose I could add one value as a SELECT 
statement, then try to locate that row in the table and do an UPDATE to 
the other column (in this case, the cast column) where I just inserted 
the data.

Thank you!
George R.






-- 
www.andforthelamb.org
www.outreachhockey.org

They deem me mad for I will not sell my days for gold; I deem them mad for they 
think my days have a price.


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

Re: [sqlite] WAL index in memory - multiple connections

[Yoni Londner]

Hi,

since in memory index file is already implemented (WAL_HEAPMEMORY_MODE), 
I think that adding locks when needed will add the support for single 
process multithreaded programs.
Am I wrong?

Yoni.

On 10/12/2010 5:32 PM, Pavel Ivanov wrote:
>> Perhaps using async VFS mode would better suit Yoni's application?
>>
>> http://www.sqlite.org/asyncvfs.html
>
>  From my experience asyncVFS is not suitable for applications with high
> throughput expecting high performance, because with big load and big
> writeback queue asyncVFS consumes a lot of CPU for each reading from
> file (it scans through the whole queue on each request to read, lock
> or unlock database file) which I guess generally slows down each query
> significantly (apart from causing a big CPU load).
>
> Such application needs custom VFS designed specifically for its needs
> (e.g. you can eliminate actual locking/unlocking of database file - it
> gives pretty significant benefit but again at the price of never be
> able to connect to database while your application is running). Also
> this custom VFS can be coupled with custom PCache to get some
> additional perks: e.g. VFS can schedule every write to background
> thread and tell PCache that whatever SQLite says it shouldn't evict
> this page until it's written to disk. This way you'll be able to write
> everything in background without causing additional pressure on
> queries - all pages they need are either in the cache or were not
> changed recently and are not in VFS background queue. Of course such
> system will corrupt database immediately if application exits/crashes
> with non-empty background queue. Also such system has danger of going
> out of memory in case of too big throughput, so it needs to have
> additional guards for that.
>
>
> Pavel
>
> On Fri, Dec 10, 2010 at 10:05 AM, Christian Smith
>   wrote:
>> On Fri, Dec 10, 2010 at 09:49:46AM -0500, Pavel Ivanov wrote:
 Given that the WAL index is mmap'ed, you're unlikely to see improvement
 in performance by storing it in heap memory. Reads/writes will go at
 main memory speeds once mapped into your address space, and under memory
 pressure, it will be no slower than if the heap was pushed to the swapfile.
>>>
>>> Still I think pushing actual memory to swap file has bigger memory
>>> pressure threshold than pushing cache pages that are backed by actual
>>> file data out of physical memory. Also writing to mmaped file will
>>> still force OS to write it to disk from time to time and that brings
>>> additional pressure on the system overall.
>>>
>>
>> Once you're pushing working memory to disk, you've basically lost the
>> performance battle either way.
>>
>> Given the OP problem, it doesn't sound like memory is the limiting
>> factor anyway.
>>
>>  From the past posts, it appears Yoni is after predictable performance
>> with high throughput (logging system?) but without the durability
>> gaurantees provided by SQLite by default.
>>
>> Perhaps using async VFS mode would better suit Yoni's application?
>>
>> http://www.sqlite.org/asyncvfs.html
>>
>> This way, the foreground thread handles writes to the SQLite IO queue,
>> and the background SQLite IO thread handles any latencies that result
>> from the commits. Yoni's already mentioned in other threads that
>> durability is not the main priority.
>>
>> I'm not sure how this async VFS fits in with WAL. Might be that normal
>> rollback journalling only is supported, but from a performance
>> standpoint, that's probably not a problem.
>>
>> Christian
>> ___
>> 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] WAL index in memory - multiple connections

[Yoni Londner]

Hi,

 > Such application needs custom VFS designed specifically for its needs
I Believe that a good library should be as robust as possible, and 
SQLite is very good at being robust. This is the reason you can find 
SQLite almost anywhere, from mobile devices, desktop applications and 
web sites. But not all of them have the same requirements, and SQLite 
should give its users the options to configure it differently.
Some users will choose to use the traditional way, and some will choose 
to boost performance, and will pay the price of the ability to connect 
from multiple processes.
Currently SQLite have good support for single process (single thread) 
applications, multiprocess applications, but does not have good enough 
support for single process multithreaded applications, as SQLite can 
perform better if use in memory index for WAL.
Maybe we should add another option: PRAGMA locking_mode=proc_exclusive, 
which means exclusive for the same process.


 > Of course such system will corrupt database immediately if
 > application exits/crashes with non-empty background queue
I think another solution I suggested to a problem of WAL file size, will 
give you the good of all worlds (but with price). There is a solution 
which will be good for ALL WAL users (cyclic WAL file), and a simpler 
solution (keep last transaction in memory - this combine WAL and async), 
that is good for some applications (But will never corrupt the DB), and 
FMHO should be configurable, so if you use it, you know the limitations 
and accept them. Bottom line: Give the user an option.

Yoni.

On 10/12/2010 5:32 PM, Pavel Ivanov wrote:
>> Perhaps using async VFS mode would better suit Yoni's application?
>>
>> http://www.sqlite.org/asyncvfs.html
>
>  From my experience asyncVFS is not suitable for applications with high
> throughput expecting high performance, because with big load and big
> writeback queue asyncVFS consumes a lot of CPU for each reading from
> file (it scans through the whole queue on each request to read, lock
> or unlock database file) which I guess generally slows down each query
> significantly (apart from causing a big CPU load).
>
> Such application needs custom VFS designed specifically for its needs
> (e.g. you can eliminate actual locking/unlocking of database file - it
> gives pretty significant benefit but again at the price of never be
> able to connect to database while your application is running). Also
> this custom VFS can be coupled with custom PCache to get some
> additional perks: e.g. VFS can schedule every write to background
> thread and tell PCache that whatever SQLite says it shouldn't evict
> this page until it's written to disk. This way you'll be able to write
> everything in background without causing additional pressure on
> queries - all pages they need are either in the cache or were not
> changed recently and are not in VFS background queue. Of course such
> system will corrupt database immediately if application exits/crashes
> with non-empty background queue. Also such system has danger of going
> out of memory in case of too big throughput, so it needs to have
> additional guards for that.
>
>
> Pavel
>
> On Fri, Dec 10, 2010 at 10:05 AM, Christian Smith
>   wrote:
>> On Fri, Dec 10, 2010 at 09:49:46AM -0500, Pavel Ivanov wrote:
 Given that the WAL index is mmap'ed, you're unlikely to see improvement
 in performance by storing it in heap memory. Reads/writes will go at
 main memory speeds once mapped into your address space, and under memory
 pressure, it will be no slower than if the heap was pushed to the swapfile.
>>>
>>> Still I think pushing actual memory to swap file has bigger memory
>>> pressure threshold than pushing cache pages that are backed by actual
>>> file data out of physical memory. Also writing to mmaped file will
>>> still force OS to write it to disk from time to time and that brings
>>> additional pressure on the system overall.
>>>
>>
>> Once you're pushing working memory to disk, you've basically lost the
>> performance battle either way.
>>
>> Given the OP problem, it doesn't sound like memory is the limiting
>> factor anyway.
>>
>>  From the past posts, it appears Yoni is after predictable performance
>> with high throughput (logging system?) but without the durability
>> gaurantees provided by SQLite by default.
>>
>> Perhaps using async VFS mode would better suit Yoni's application?
>>
>> http://www.sqlite.org/asyncvfs.html
>>
>> This way, the foreground thread handles writes to the SQLite IO queue,
>> and the background SQLite IO thread handles any latencies that result
>> from the commits. Yoni's already mentioned in other threads that
>> durability is not the main priority.
>>
>> I'm not sure how this async VFS fits in with WAL. Might be that normal
>> rollback journalling only is supported, but from a performance
>> standpoint, that's probably not a problem.
>>
>> Christian
>> 

Re: [sqlite] SQLite crashes due to invalid pointer

[Richard Hipp]

On Sun, Dec 12, 2010 at 3:28 AM, Wanadoo Hartwig <
hartwig.wiesm...@wanadoo.nl> wrote:

> Hi,
>
> sorry, but I thought that there would have been an automatic conversion to
> a float. The comparison is not 1 but a floating point literal like 1.0.
>

I'm thinking you are making other assumptions as well.   Please send

(1) Your complete schema
(2) The exact text (byte-for-byte) of your query
(3) The implementation of your function1() function



>
> Am 12.12.2010 um 03:49 schrieb Richard Hipp:
>
> > On Sat, Dec 11, 2010 at 7:07 PM, Wanadoo Hartwig <
> > hartwig.wiesm...@wanadoo.nl> wrote:
> >
> >> Hi,
> >>
> >> take the following SQL statement:
> >>
> >> UPDATE tableA SET column1=(SELECT column2 FROM tableB WHERE
> >> function1(column3) < 1 ORDER BY function1(column3) LIMIT 1);
> >>
> >> Actually, this statement does not make sense because the ORDER BY
> >> expression does not fulfill the requirements of an ORDER BY expression.
> I
> >> wrote it by mistake.
> >>
> >> Interestingly SQLite only crashes if function1 is a user supplied
> function
> >> (using sqlite3_create_function). I tried the same with the core abs()
> >> function but then SQLite works.
> >>
> >
> > The statement you supply above never generates an OP_Real instruction.
> > OP_Real is only generated if your statement contains a floating point
> > literal, which yours does not.  Are you user that the UPDATE statement
> you
> > are giving above is the statement that is crashing?
> >
> >
>
> This is the more precise statement:
>
> UPDATE tableA SET column1=(SELECT column2 FROM tableB WHERE
> function1(1.0,column3) < 1.0 ORDER BY function1(1.0,column3) LIMIT 1);
>
> Actually there is a WHERE expression in the update statement, too. But
> there is no difference if it exists or not (tested).
>
> >
> >
> >> I am using SQLite 3.7.2 and the crash occurs here:
> >>
> >> case OP_Real: {/* same as TK_FLOAT, out2-prerelease */
> >> pOut->flags = MEM_Real;
> >> assert( !sqlite3IsNaN(*pOp->p4.pReal) ); <-- crashes here because of
> >> invalid pointer to p4
> >> pOut->r = *pOp->p4.pReal;
> >> break;
> >> }
> >>
> >>
> >> Hartwig
> >>
> >> ___
> >> 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
>
> Hartwig
>
>
> ___
> 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

Re: [sqlite] pragma vs select for introspection

[Simon Slavin]

On 12 Dec 2010, at 10:38am, Petite Abeille wrote:

> All in all, I'm all for a pragmatic implementation of Section 21 in SQLite.

I see what you did there.

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

Re: [sqlite] pragma vs select for introspection

[Petite Abeille]

On Dec 12, 2010, at 1:29 AM, Darren Duncan wrote:

>> On Dec 11, 2010, at 3:48 PM, Simon Slavin wrote:
>> 
>>> Section 21 of the (SQL92) standard.
>> 
>> Yes, the notorious information schema:
> 
> Nonsense.  An information schema is a *good* thing, and is generally the 
> *best* 
> tool for introspecting a database.  It lets you use all the power features 
> you 
> have when querying data, anything a SELECT can do, and you can query the 
> database structure likewise.  This is the way a relational database is 
> supposed 
> to work.

Fully agree :) 

 "Notorious" was referring to Simon's "absolutely horrible" qualification of 
Section 21 of the SQL92 standard, which describes information_schema. This was 
a weak attempt to humor Simon :))

All in all, I'm all for a pragmatic implementation of Section 21 in SQLite.



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

Re: [sqlite] SQLite crashes due to invalid pointer

[Wanadoo Hartwig]

Hi,

sorry, but I thought that there would have been an automatic conversion to a 
float. The comparison is not 1 but a floating point literal like 1.0.

Am 12.12.2010 um 03:49 schrieb Richard Hipp:

> On Sat, Dec 11, 2010 at 7:07 PM, Wanadoo Hartwig <
> hartwig.wiesm...@wanadoo.nl> wrote:
> 
>> Hi,
>> 
>> take the following SQL statement:
>> 
>> UPDATE tableA SET column1=(SELECT column2 FROM tableB WHERE
>> function1(column3) < 1 ORDER BY function1(column3) LIMIT 1);
>> 
>> Actually, this statement does not make sense because the ORDER BY
>> expression does not fulfill the requirements of an ORDER BY expression. I
>> wrote it by mistake.
>> 
>> Interestingly SQLite only crashes if function1 is a user supplied function
>> (using sqlite3_create_function). I tried the same with the core abs()
>> function but then SQLite works.
>> 
> 
> The statement you supply above never generates an OP_Real instruction.
> OP_Real is only generated if your statement contains a floating point
> literal, which yours does not.  Are you user that the UPDATE statement you
> are giving above is the statement that is crashing?
> 
> 

This is the more precise statement:

UPDATE tableA SET column1=(SELECT column2 FROM tableB WHERE 
function1(1.0,column3) < 1.0 ORDER BY function1(1.0,column3) LIMIT 1);

Actually there is a WHERE expression in the update statement, too. But there is 
no difference if it exists or not (tested).

> 
> 
>> I am using SQLite 3.7.2 and the crash occurs here:
>> 
>> case OP_Real: {/* same as TK_FLOAT, out2-prerelease */
>> pOut->flags = MEM_Real;
>> assert( !sqlite3IsNaN(*pOp->p4.pReal) ); <-- crashes here because of
>> invalid pointer to p4
>> pOut->r = *pOp->p4.pReal;
>> break;
>> }
>> 
>> 
>> Hartwig
>> 
>> ___
>> 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

Hartwig


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