[sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?

[Jens Miltner]

Hi,

on the Wiki page listing SQLite Management Tools 
, there are columns for Web, Windows, Linux and Misc. as supported  
platforms. Since Mac OS X is a well-supported SQLite platform and  
there are more and more applications on Mac OS X that are directly or  
indirectly using SQLite, I'd suggest to add a column for "Mac OS X"  
support.

Now, I realize that I could just add the column by editing the Wiki  
page, but I didn't want to rush in making such a relatively prominent  
change without at least discussing this first.

Any objections or comments?



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

Re: [sqlite] How do I do this join on multiple columns?

[Greg Palmer]

yaconsult wrote:
> SQL newbie here.  I have data in a couple of tables that I need to relate. 
> But I don't know how to relate on more than one column.  I'll not go into
> detail about why the data is formed the way it is - it comes from other
> programs.
>
> For example, let's say there are a couple of tables:
>
> server login name last_login email ...
>
> and we have a second table that contains columns for server and login:
>
> server login
>
> How can I, for example, select rows of the first table where their server
> and login, combined, are NOT in the second table.
> It's sorta like a composite key.  Do I do it with a couple of joins?  Could
> I see an example?
>
> If it were only a single column I could use a "not in" subselect, but I'm
> not sure how to do it when it involves more than one column.
>
> Thanks for any help!
>   
Let me apologize up front since I have no SQLite experience but I'll 
give you some samples that work under SQL Server. There are going to be 
some syntactic differences moving to SQLite but I'll leave that as an 
exercise for you! :-)

Here is one *flawed* possibility. I assume here that table_1 is some 
sort of user table which defines a login and a server that could be 
logged into while Table_2 defines actual logins to a server with a 
particular login. First you match the tables up on the server columns 
being identical and then select the records that do not have a login in 
the second table. This subquery by itself would pick up all records 
where the server had been added to a login but would miss records if the 
server had never been logged into by anyone. (Since there would be no 
records in the table which records specific logins, there would be no 
matches. Therefore we add the second subquery where we match them on the 
login and then grab servers that had never been logged into. Problem is 
if we have a login with a server which has never been logged into and a 
user who has never logged into any server we'd still miss them. It is a 
very quick query though and if you know there will never be any servers 
that have not been logged into you can drop the second half of the query.

SELECTDISTINCT T1.Server, T1.Login
FROMTABLE_1 T1,
TABLE_2 T2
WHERE(
T1.Server = T2.Server
AND(T1.Login
NOT IN(
SELECTDISTINCT Login
FROMTABLE_2
WHERETABLE_2.Server = T1.Server
)
)
)
OR(T1.Login = T2.Login
AND(T1.Server
NOT IN(SELECTDISTINCT Server
FROMTABLE_2
WHERETABLE_2.Login = T1.Login
)
)
)

Now, another query that would do the job would be. We still don't like 
it since it uses too many reqources. Here we query or manufacture a row 
for every row in the user table. We then check for rows that have been 
manufactured and grab them.

SELECTDISTINCT T1.Server User_Table_Server, T1.Login 
User_Table_Login, T2.Server Login_Table_Server, T2.Login Login_Login
FROMTABLE_1 T1
LEFT OUTER JOIN TABLE_2 T2
ON(T1.Server = T2.Server
AND
T1.Login = T2.Login )
WHERE T2.Server IS NULL

Lastly, the query you ACTUALLY want is:
SELECT*
FROMTABLE_1 T1
WHERENOT EXISTS (
SELECT*
FROMTABLE_2 T2
WHERET1.Server = T2.Server
ANDT1.Login = T2.Login
)

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

Re: [sqlite] Performance impact for heavy accessing the database file

[P Kishor]

On Mon, Mar 2, 2009 at 9:24 PM, Eversogood  wrote:
> Hi,
>
> How is the SQLite performance when a lot number of concurrent users access
> the database file?
>


why? what have you experienced?

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

[sqlite] Performance impact for heavy accessing the database file

[Eversogood]

Hi,

How is the SQLite performance when a lot number of concurrent users access
the database file?

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

Re: [sqlite] sqlite3_finalize and threading (was sqlite_master table and SQLITE_MISUSE)

[D. Richard Hipp]

On Mar 2, 2009, at 9:52 PM, Lukhnos D. Liu wrote:

> Hi,
>
> I posted a question a few days ago on the cause of getting
> SQLITE_MISUSE when I called sqlite3_prepare_v2. I thought I both ruled
> out the possibilites advised by the replies and solved the probelm by
> finalizing statements on the sqlite_master table earlier.
>
> Well the problem didn't get solved this way (as my hunch told me).
> What really happened surprised me. Again I was wondering the root
> cause of it.
>
> It turned out that I have the following execution history:
>
> Time Thread AThread B
>  0  prepare foo
>  1  prepare bar
>  2  finalize foo
>  3  step bar
>  4  finalize bar
>  5  prepare baz (*)
>
> (*) indicates that I started getting SQLITE_MISUSE from this point on.
>
> I'm still wondering why it is impossible to call sqlite3_finalize in a
> situation like this. The document doesn't seem to warn against it
> (although the doc warns the evilness of threading in general). Or is
> the root cause deeper?
>

The above is suppose to work:

Please do this for me.  Run your program in a debugger.  And every  
time you enter the sqlite3_prepare function, print the value of db- 
 >magic in hex.  Send me the results, especially for the case when you  
invoke "prepare baz."

D. Richard Hipp
d...@hwaci.com



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

[sqlite] sqlite3_finalize and threading (was sqlite_master table and SQLITE_MISUSE)

[Lukhnos D. Liu]

Hi,

I posted a question a few days ago on the cause of getting  
SQLITE_MISUSE when I called sqlite3_prepare_v2. I thought I both ruled  
out the possibilites advised by the replies and solved the probelm by  
finalizing statements on the sqlite_master table earlier.

Well the problem didn't get solved this way (as my hunch told me).  
What really happened surprised me. Again I was wondering the root  
cause of it.

It turned out that I have the following execution history:

 Time Thread AThread B
  0  prepare foo
  1  prepare bar
  2  finalize foo
  3  step bar
  4  finalize bar
  5  prepare baz (*)

(*) indicates that I started getting SQLITE_MISUSE from this point on.

In essence, a statement prepared in Thread A was finalized in-between  
a prepare-finalize block running in Thread B.

After that, when I prepared another statement in Thread A, I started  
getting SQLITE_MISUSE. And all subsequent sqlite3_prepare calls  
returned the same.

One interesting thing about my code was that statement foo wasn't  
really used. It was prepared by some parent class init code, and was  
discarded by child class's init. As it turned out, I didn't  
immediately call sqlite3_finalize(foo). Rather I threw the object in a  
deferred release pool (auto object management, or autorelease pool in  
Objective-C speak). Finalize could be called before Thread B's prepare- 
finalize block started or during the block, as is the nature of  
threaded execution.

My code broke every time when the execution history exhibited a  
pattern like above. Finalizing foo right before Thread B's prepare- 
finalize block stopped the problem. I no longer get SQLITE_MISUSE.

I'm still wondering why it is impossible to call sqlite3_finalize in a  
situation like this. The document doesn't seem to warn against it  
(although the doc warns the evilness of threading in general). Or is  
the root cause deeper?

Thanks again.

d.






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

Re: [sqlite] How do I do this join on multiple columns?

[Greg Palmer]

Rich Shepard wrote:
> On Mon, 2 Mar 2009, yaconsult wrote:
>
>   
>> SQL newbie here.
>> 
>If you'll be doing a lot of coding in SQL, take a look at Joe Celko's
> other books. He's been writing on database issues for more than a quarter
> century, and I remember reading his columns in DBMS Advisor in the
> mid-1980s. FWIW, he recommended van der Laan's book when I asked him for
> references for time-based queries.
>
> Rich
>
>   
+1 on the Celko recommendation. His books are extraordinary, the writing 
is clear, concise and accurate. One of them will take you a long way to 
becoming an expert...

Regards,
  Greg

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

Re: [sqlite] How do I do this join on multiple columns?

[BareFeet]

Hi ya (well that's the only name you give ;-) )

> SQL newbie here.  I have data in a couple of tables that I need to  
> relate.
> But I don't know how to relate on more than one column.

> For example, let's say there are a couple of tables:
>
> server login name last_login email ...
>
> and we have a second table that contains columns for server and login:
>
> server login

It would help to see the actual schema. I'm guessing that your schema  
is something like:

create table Servers
(
  ID integer primary key
, Server text
, Login text
, Name text
, Last_login datetime
, Email text
)
;
create table Attempts
(
  ID integer primary key
, Server text
, Login text
)
;

> How can I, for example, select rows of the first table where their  
> server and login, combined, are NOT in the second table.

Something like this:

select * from Servers where not exists (select 1 from Attempts where  
Servers.Server = Attempts.Server and Servers.Login = Attempts.Login);

or:

select Server, Login from Servers
except
select Server, Login from Attempts
;

> If it were only a single column I could use a "not in" subselect,  
> but I'm not sure how to do it when it involves more than one column.

Whether it's matching on one or multiple columns, it's best not to use  
"not in" or "in", but instead use "not exists (select 1..." or "not  
exists (select 1..." as above. That way, the database engine only has  
to find the first match (or non match), rather than scan the whole  
table redundantly.

HTH,
Tom
BareFeet

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/

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

Re: [sqlite] How do I do this join on multiple columns?

[Rich Shepard]

On Mon, 2 Mar 2009, Rich Shepard wrote:

>   Two books you should seriously consider purchasing are Rick van der Laan's

   Correction: his name is Rick van der Lans.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I do this join on multiple columns?

[John Machin]

On 3/03/2009 12:48 PM, yaconsult wrote:
> SQL newbie here.  I have data in a couple of tables that I need to relate. 
> But I don't know how to relate on more than one column.  I'll not go into
> detail about why the data is formed the way it is - it comes from other
> programs.
> 
> For example, let's say there are a couple of tables:
> 
> server login name last_login email ...
> 
> and we have a second table that contains columns for server and login:
> 
> server login
> 
> How can I, for example, select rows of the first table where their server
> and login, combined, are NOT in the second table.
> It's sorta like a composite key.  Do I do it with a couple of joins?  Could
> I see an example?
> 
> If it were only a single column I could use a "not in" subselect, but I'm
> not sure how to do it when it involves more than one column.

You're on the right track now, you need something like a "not in" 
subselect; a join is the *opposite* of what you want.

select * from first f where not exists (select 1 from second s where 
f.login = s.login and f.server = s.server) -- untested, OTTOMH, YMMV, 
etc :-)

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

Re: [sqlite] How do I do this join on multiple columns?

[Rich Shepard]

On Mon, 2 Mar 2009, yaconsult wrote:

> SQL newbie here.

   Allow me to offer a recommendation rather than a solution to your problem.
(I'd want more information on the attributes in each table before suggesting
a SELECT statement.)

   Two books you should seriously consider purchasing are Rick van der Laan's
"Introduction to SQL, 4th Ed." and Joe Celko's "SQL Programming Style." The
former is the most comprehensive introduction to SQL I've ever seen, and has
a great deal of information on time-based queries. The latter is a must read
for anyone starting in SQL before they bring bad habits to their code. He
discusses how to name things (and why), how to write portable SQL code, and
how to organize data so a simple query (SELECT) will suffice rather than
cobbling together a convoluted mess.

   If you'll be doing a lot of coding in SQL, take a look at Joe Celko's
other books. He's been writing on database issues for more than a quarter
century, and I remember reading his columns in DBMS Advisor in the
mid-1980s. FWIW, he recommended van der Laan's book when I asked him for
references for time-based queries.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How do I do this join on multiple columns?

[yaconsult]

SQL newbie here.  I have data in a couple of tables that I need to relate. 
But I don't know how to relate on more than one column.  I'll not go into
detail about why the data is formed the way it is - it comes from other
programs.

For example, let's say there are a couple of tables:

server login name last_login email ...

and we have a second table that contains columns for server and login:

server login

How can I, for example, select rows of the first table where their server
and login, combined, are NOT in the second table.
It's sorta like a composite key.  Do I do it with a couple of joins?  Could
I see an example?

If it were only a single column I could use a "not in" subselect, but I'm
not sure how to do it when it involves more than one column.

Thanks for any help!


-- 
View this message in context: 
http://www.nabble.com/How-do-I-do-this-join-on-multiple-columns--tp22301000p22301000.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Data Modeling Tools

[Rich Shepard]

On Mon, 2 Mar 2009, Mike McGonagle wrote:

> I am working on a project that is based around SQLite as a data format (so
> to speak), and as such, I would like to be able to show others various
> diagrams of the data models. Are there any programs that will work with
> SQLite directly? Or any generic tools that anyone could suggest?

Mike,

   I draw all my vector diagrams using PSTricks. There are many other tools
available, but none that will automagically extract the schema from the DDL
and draw a picture of it for you.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Data Modeling Tools

[Mike McGonagle]

Hello all,

I am working on a project that is based around SQLite as a data format
(so to speak), and as such, I would like to be able to show others
various diagrams of the data models. Are there any programs that will
work with SQLite directly? Or any generic tools that anyone could
suggest?

Thanks,

Mike


-- 

Dick Cavett  - "If your parents never had children, chances are...
neither will you."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERTing OR REPLACEing Together or one at a time

[jose isaias cabrera]

"Martin Engelschalk" wrote...

> Hi,
>
> If you could post how it does fail, exactly, maybe we can help 
>
> Martin


I wish I could... the wrapper that I have is very old and I have put too 
much time on this to update, right now.  I am sure it is the wrapper, since 
most times it works and sometimes it does not.  So, I will have to keep 
track of that.  But, thanks for the support.  I will keep #2 as the desired 
SQL.

josé




>
> jose isaias cabrera wrote:
>> Ok.  That is what I am doing now.  Every so often, it fails, for some
>> reason.  It could be a network problem, but I don't think so.  Anyway,
>> thanks for the help.
>>
>> josé
>>
>> - Original Message - 
>> From: "Martin Engelschalk" 
>> To: "General Discussion of SQLite Database" 
>> Sent: Monday, March 02, 2009 1:14 PM
>> Subject: Re: [sqlite] INSERTing OR REPLACEing Together or one at a time
>>
>>
>>
>>> Hi,
>>>
>>> it seems to me that between LSOpenProjects and LSOpenSubProjects you
>>> have a 1:n relationship, and also between LSOpenSubProjects and
>>> LSOpenJobs.
>>> Also, it seems that you want to copy data from an attached database "c"
>>> to the main database.
>>> So, if you want to preserve this relationship in your main database, you
>>> have to use one transaction, Command #2, and rollback in case of 
>>> failure.
>>>
>>> Martin
>>>
>>> jose isaias cabrera wrote:
>>>
 Greetings.

 which command is safer to use:

 Command #1:

 BEGIN;
 INSERT OR REPLACE INTO LSOpenProjects
 SELECT * FROM c.LSOpenProjects
 WHERE ProjID = 2000;
 COMMIT;
 BEGIN;
 INSERT OR REPLACE INTO LSOpenSubProjects
 SELECT * FROM c.LSOpenSubProjects
 WHERE ProjID = 2000;
 COMMIT;
 BEGIN;
 INSERT OR REPLACE INTO LSOpenJobs
 SELECT * FROM c.LSOpenJobs
 WHERE ProjID = 2000;
 COMMIT;


 Command #2:

 BEGIN;
 INSERT OR REPLACE INTO LSOpenProjects
 SELECT * FROM c.LSOpenProjects
 WHERE ProjID = 2000;
 INSERT OR REPLACE INTO LSOpenSubProjects
 SELECT * FROM c.LSOpenSubProjects
 WHERE ProjID = 2000;
 INSERT OR REPLACE INTO LSOpenJobs
 SELECT * FROM c.LSOpenJobs
 WHERE ProjID = 2000;
 COMMIT;

 This latter one is failing every so often.  But, it should not matter
 much,
 since it is writing to three different tables.  I should say that there
 is
 always, just one LSOpenProject record; at least, one LSOpenSubProjects
 records and, at least, one LSOpenJobs record.  But 99% of the time, 
 there
 is
 always more than one LSOpenJobs.

 Any ideas?

 thanks,

 josé


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



>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> ___
>> 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] Is snapshot backup supported for SQLite2?

[Greg Palmer]

Hello Folks,

  I am dropping by to get a definitive answer to a question recently 
raised. Can anyone here tell me definitively if backing up SQLite2 by 
taking a snapshot of a live database is supported? The particular 
operating system is Solaris 10 but I am curious about any platform where 
snapshots are available. If it isn't supported, what would the harmful 
effects be?

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

Re: [sqlite] Joins

[Kees Nuyt]

On Mon, 2 Mar 2009 13:59:13 -0500, "Fazoogled"
 wrote:

>I used to have a good cheat sheet on Joins that I cannot find anywhere. Must
>have been cleaning house and had a \delete' attack. But I'm going to need it
>for a little exercise I'm doing at home where I have a many to many table
>with a 'connector' (what I call it) table in between. I'm going to be
>futzing around with the joins 'til I get them right (I'm a C/C++ coder, not
>a db guy) and I'm missing that cheat sheet! I got it somewhere in the web,
>just can't seem to find it..
>
>TIA

A quick search shows there are many of those, I have no idea
which one you are looking for.

http://www.google.com/search?hl=en&q=SQL+join+"cheat+sheet";
http://www.google.com/search?hl=en&q=SQL+join+"many+to+many"+"cheat+sheet";
http://www.google.com/search?hl=en&q=SQL+join+"cheat+sheet"+sqlite


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

[sqlite] Joins

[Fazoogled]

I used to have a good cheat sheet on Joins that I cannot find anywhere. Must
have been cleaning house and had a \delete' attack. But I'm going to need it
for a little exercise I'm doing at home where I have a many to many table
with a 'connector' (what I call it) table in between. I'm going to be
futzing around with the joins 'til I get them right (I'm a C/C++ coder, not
a db guy) and I'm missing that cheat sheet! I got it somewhere in the web,
just can't seem to find it..

TIA

 

Michael

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

Re: [sqlite] INSERTing OR REPLACEing Together or one at a time

[Martin Engelschalk]

Hi,

If you could post how it does fail, exactly, maybe we can help 

Martin

jose isaias cabrera wrote:
> Ok.  That is what I am doing now.  Every so often, it fails, for some 
> reason.  It could be a network problem, but I don't think so.  Anyway, 
> thanks for the help.
>
> josé
>
> - Original Message - 
> From: "Martin Engelschalk" 
> To: "General Discussion of SQLite Database" 
> Sent: Monday, March 02, 2009 1:14 PM
> Subject: Re: [sqlite] INSERTing OR REPLACEing Together or one at a time
>
>
>   
>> Hi,
>>
>> it seems to me that between LSOpenProjects and LSOpenSubProjects you
>> have a 1:n relationship, and also between LSOpenSubProjects and 
>> LSOpenJobs.
>> Also, it seems that you want to copy data from an attached database "c"
>> to the main database.
>> So, if you want to preserve this relationship in your main database, you
>> have to use one transaction, Command #2, and rollback in case of failure.
>>
>> Martin
>>
>> jose isaias cabrera wrote:
>> 
>>> Greetings.
>>>
>>> which command is safer to use:
>>>
>>> Command #1:
>>>
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenProjects
>>> SELECT * FROM c.LSOpenProjects
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenSubProjects
>>> SELECT * FROM c.LSOpenSubProjects
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenJobs
>>> SELECT * FROM c.LSOpenJobs
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>>
>>>
>>> Command #2:
>>>
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenProjects
>>> SELECT * FROM c.LSOpenProjects
>>> WHERE ProjID = 2000;
>>> INSERT OR REPLACE INTO LSOpenSubProjects
>>> SELECT * FROM c.LSOpenSubProjects
>>> WHERE ProjID = 2000;
>>> INSERT OR REPLACE INTO LSOpenJobs
>>> SELECT * FROM c.LSOpenJobs
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>>
>>> This latter one is failing every so often.  But, it should not matter 
>>> much,
>>> since it is writing to three different tables.  I should say that there 
>>> is
>>> always, just one LSOpenProject record; at least, one LSOpenSubProjects
>>> records and, at least, one LSOpenJobs record.  But 99% of the time, there 
>>> is
>>> always more than one LSOpenJobs.
>>>
>>> Any ideas?
>>>
>>> thanks,
>>>
>>> josé
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
> ___
> 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] INSERTing OR REPLACEing Together or one at a time

[jose isaias cabrera]

Ok.  That is what I am doing now.  Every so often, it fails, for some 
reason.  It could be a network problem, but I don't think so.  Anyway, 
thanks for the help.

josé

- Original Message - 
From: "Martin Engelschalk" 
To: "General Discussion of SQLite Database" 
Sent: Monday, March 02, 2009 1:14 PM
Subject: Re: [sqlite] INSERTing OR REPLACEing Together or one at a time


> Hi,
>
> it seems to me that between LSOpenProjects and LSOpenSubProjects you
> have a 1:n relationship, and also between LSOpenSubProjects and 
> LSOpenJobs.
> Also, it seems that you want to copy data from an attached database "c"
> to the main database.
> So, if you want to preserve this relationship in your main database, you
> have to use one transaction, Command #2, and rollback in case of failure.
>
> Martin
>
> jose isaias cabrera wrote:
>> Greetings.
>>
>> which command is safer to use:
>>
>> Command #1:
>>
>> BEGIN;
>> INSERT OR REPLACE INTO LSOpenProjects
>> SELECT * FROM c.LSOpenProjects
>> WHERE ProjID = 2000;
>> COMMIT;
>> BEGIN;
>> INSERT OR REPLACE INTO LSOpenSubProjects
>> SELECT * FROM c.LSOpenSubProjects
>> WHERE ProjID = 2000;
>> COMMIT;
>> BEGIN;
>> INSERT OR REPLACE INTO LSOpenJobs
>> SELECT * FROM c.LSOpenJobs
>> WHERE ProjID = 2000;
>> COMMIT;
>>
>>
>> Command #2:
>>
>> BEGIN;
>> INSERT OR REPLACE INTO LSOpenProjects
>> SELECT * FROM c.LSOpenProjects
>> WHERE ProjID = 2000;
>> INSERT OR REPLACE INTO LSOpenSubProjects
>> SELECT * FROM c.LSOpenSubProjects
>> WHERE ProjID = 2000;
>> INSERT OR REPLACE INTO LSOpenJobs
>> SELECT * FROM c.LSOpenJobs
>> WHERE ProjID = 2000;
>> COMMIT;
>>
>> This latter one is failing every so often.  But, it should not matter 
>> much,
>> since it is writing to three different tables.  I should say that there 
>> is
>> always, just one LSOpenProject record; at least, one LSOpenSubProjects
>> records and, at least, one LSOpenJobs record.  But 99% of the time, there 
>> is
>> always more than one LSOpenJobs.
>>
>> Any ideas?
>>
>> thanks,
>>
>> josé
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

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

Re: [sqlite] Random Syntax changed in 3.6.11?

[Alberto Simões]

Hello, DRH,

On Mon, Mar 2, 2009 at 6:32 PM, D. Richard Hipp  wrote:
> The RANDOM() function takes zero arguments.  This has always been the
> case.  But 3.6.11 has begun to enforce that rule.  Prior to 3.6.11,
> the error was ignored.

Oh. That explains it.
Thanks

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

Re: [sqlite] Random Syntax changed in 3.6.11?

[Martin Engelschalk]

Hi,

in http://www.sqlite.org/lang_corefunc.html the random() function is 
documented as taking no arguments,
So, use
SELECT word FROM dict ORDER BY RANDOM() LIMIT 1;

I tried an older version of sqlite (can't say which, but 3.*), and could 
call random() with 0, 1, 2, 3 and 4 parameters. I would be interested 
what this means.

Martin

Alberto Simões wrote:
> Hello
>
> I am using random as:
>
> SELECT word FROM dict ORDER BY RANDOM(term) LIMIT 1;
>
> And I am getting:
>
> DBD::SQLite::db prepare failed: wrong number of arguments to function
> RANDOM()(1) at dbdimp.c line 271 at OpenDict.pm line 64.
>
> (yes, using it through DBD::SQLite, but I do not think that is the problem).
>
> Thanks
> Alberto
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Random Syntax changed in 3.6.11?

[D. Richard Hipp]

On Mar 2, 2009, at 1:16 PM, Alberto Simões wrote:

> Hello
>
> I am using random as:
>
> SELECT word FROM dict ORDER BY RANDOM(term) LIMIT 1;
>
> And I am getting:
>
> DBD::SQLite::db prepare failed: wrong number of arguments to function
> RANDOM()(1) at dbdimp.c line 271 at OpenDict.pm line 64.
>
> (yes, using it through DBD::SQLite, but I do not think that is the  
> problem).
>

The RANDOM() function takes zero arguments.  This has always been the  
case.  But 3.6.11 has begun to enforce that rule.  Prior to 3.6.11,  
the error was ignored.


D. Richard Hipp
d...@hwaci.com



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

[sqlite] Random Syntax changed in 3.6.11?

[Alberto Simões]

Hello

I am using random as:

SELECT word FROM dict ORDER BY RANDOM(term) LIMIT 1;

And I am getting:

DBD::SQLite::db prepare failed: wrong number of arguments to function
RANDOM()(1) at dbdimp.c line 271 at OpenDict.pm line 64.

(yes, using it through DBD::SQLite, but I do not think that is the problem).

Thanks
Alberto
-- 
Alberto Simões
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERTing OR REPLACEing Together or one at a time

[Martin Engelschalk]

Hi,

it seems to me that between LSOpenProjects and LSOpenSubProjects you 
have a 1:n relationship, and also between LSOpenSubProjects and LSOpenJobs.
Also, it seems that you want to copy data from an attached database "c" 
to the main database.
So, if you want to preserve this relationship in your main database, you 
have to use one transaction, Command #2, and rollback in case of failure.

Martin

jose isaias cabrera wrote:
> Greetings.
>
> which command is safer to use:
>
> Command #1:
>
> BEGIN;
> INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM c.LSOpenProjects
> WHERE ProjID = 2000;
> COMMIT;
> BEGIN;
> INSERT OR REPLACE INTO LSOpenSubProjects
> SELECT * FROM c.LSOpenSubProjects
> WHERE ProjID = 2000;
> COMMIT;
> BEGIN;
> INSERT OR REPLACE INTO LSOpenJobs
> SELECT * FROM c.LSOpenJobs
> WHERE ProjID = 2000;
> COMMIT;
>
>
> Command #2:
>
> BEGIN;
> INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM c.LSOpenProjects
> WHERE ProjID = 2000;
> INSERT OR REPLACE INTO LSOpenSubProjects
> SELECT * FROM c.LSOpenSubProjects
> WHERE ProjID = 2000;
> INSERT OR REPLACE INTO LSOpenJobs
> SELECT * FROM c.LSOpenJobs
> WHERE ProjID = 2000;
> COMMIT;
>
> This latter one is failing every so often.  But, it should not matter much, 
> since it is writing to three different tables.  I should say that there is 
> always, just one LSOpenProject record; at least, one LSOpenSubProjects 
> records and, at least, one LSOpenJobs record.  But 99% of the time, there is 
> always more than one LSOpenJobs.
>
> Any ideas?
>
> thanks,
>
> josé
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERTing OR REPLACEing Together or one at a time

[jose isaias cabrera]

Greetings.

which command is safer to use:

Command #1:

BEGIN;
INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM c.LSOpenProjects
WHERE ProjID = 2000;
COMMIT;
BEGIN;
INSERT OR REPLACE INTO LSOpenSubProjects
SELECT * FROM c.LSOpenSubProjects
WHERE ProjID = 2000;
COMMIT;
BEGIN;
INSERT OR REPLACE INTO LSOpenJobs
SELECT * FROM c.LSOpenJobs
WHERE ProjID = 2000;
COMMIT;


Command #2:

BEGIN;
INSERT OR REPLACE INTO LSOpenProjects
SELECT * FROM c.LSOpenProjects
WHERE ProjID = 2000;
INSERT OR REPLACE INTO LSOpenSubProjects
SELECT * FROM c.LSOpenSubProjects
WHERE ProjID = 2000;
INSERT OR REPLACE INTO LSOpenJobs
SELECT * FROM c.LSOpenJobs
WHERE ProjID = 2000;
COMMIT;

This latter one is failing every so often.  But, it should not matter much, 
since it is writing to three different tables.  I should say that there is 
always, just one LSOpenProject record; at least, one LSOpenSubProjects 
records and, at least, one LSOpenJobs record.  But 99% of the time, there is 
always more than one LSOpenJobs.

Any ideas?

thanks,

josé


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

Re: [sqlite] View - What happens internally

[Lloyd]

Thank you Igor and Dan. Now I am clear about the concept.

On Sat, 28 Feb 2009 20:07:45 +0530, Dan  wrote:

>
> On Feb 28, 2009, at 8:44 PM, Lloyd wrote:
>
>>
>> Hi,
>>
>> When we create a view what happens internally in sqlite? Does it
>> select
>> all the data from the table and then "insert" it in to the view? or
>> would
>> the records in the view keep references to records in main table?
>> just to
>> know whether creating more views on a big in-memory database would
>> require
>> more memory.
>
> A view in SQLite is just a SELECT statement that is saved in the
> database
> schema. If you do:
>
>CREATE VIEW v1 AS SELECT * FROM t1;
>SELECT * FROM v1;
>
> The SQL compiler (the thing that runs when you call
> sqlite3_prepare_v2())
> converts the select statement to "SELECT * FROM (SELECT * FROM t1)"
> before compiling a prepared statement to implement the query.
>
> So adding a view doesn't require a huge amount of extra memory.
>
> Dan.
>
>
>>
>>
>> Thanks,
>>   Lloyd
>>
>> __
>> Scanned and protected by Email scanner
>> ___
>> 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



__
Scanned and protected by Email scanner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] memory allocation in sqlite3

[duc chinh]

hi,

i have a project about memory statistics, and i must get transition of
memory during execution of sql statement,
i used Hook API to hook SqliteMalloc, SqliteRealloc and SqliteFree, but it
is well with sqlite2, and sqlite3 fail

thanks,

-- 
Nguyen Duc Chinh
Email: ducchin...@gmail.com
Mobile: 0922 44 2468
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite write ( HELP )

[Dan]

On Mar 2, 2009, at 1:12 PM, Rajesh Nair wrote:

> Hi all
>
> When I use INSERT INTO stmt how fast sqlite writes it in to the
> database. Is it depends up on the OS. Will the record be written to
> the database on returning from the exec of INSERT stmt?

Results should be in the db when the transaction is committed. If
you have issued a "BEGIN" command, this means when your "COMMIT"
has finished. If you have not issued a BEGIN, then the results should
be in the database once the INSERT statement has finished running.


>
>
> I am inserting some records in to DB using some threads and in a
> perticular condition the threads will be suspended and when I used
> SELECT * FROM statement from another function called from one of the
> thread, some of the records were not written in to the database. But
> after some times the record was written in the DB. What could be the
> problem.
>
> The threads are using individual sqlite handles. and the function that
> reads the record is also using seperate sqlite handle.
>
> Please help
>
> Regards
> Rajesh Nair
> ___
> 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