Re: [sqlite] query trouble

[Dusty Burns]

I'm developing this for a tracker that will be designed to handle a lot of
Requests/s.. so I was wanting to optimize the queries as much as possible..
I'll try and run some more benchmarks and large result sets and see what I
get.. thanks for the input though.

On Wed, Sep 30, 2009 at 10:54 PM, Igor Tandetnik wrote:

> Dusty Burns wrote:
> > SELECT peers.compact, torrents.seeds, torrents.peers FROM torrents,
> > peers WHERE
> > torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
> > AND peers.info_hash=torrents.info_hash
> > LIMIT 50;
> >
> > which gives something like this
> >
> > peers.compact | torrents.seeds | torrents.peers
> > ---
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > data  | 1234   | 1234
> > etc...
> >
> > however, i'm getting torrents.seeds, torrents.peers with every row
> > returned which is redundant (possibly inefficient?)..
> > i only want the torrents.seeds, torrents.peers once.. the
> > peers.compact can be returned as usual
>
> If this bothers you so much, you can run two separate queries:
>
> SELECT torrents.seeds, torrents.peers FROM torrents
> WHERE torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17';
>
> SELECT peers.compact FROM peers
> WHERE peers.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
> LIMIT 50;
>
> I predict you'll find performance difference immeasurably small.
> --
> With best wishes,
>Igor Tandetnik
>
> With sufficient thrust, pigs fly just fine. However, this is not
> necessarily a good idea. It is hard to be sure where they are going to
> land, and it could be dangerous sitting under them as they fly
> overhead. -- RFC 1925
>
>
>
> ___
> 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] query trouble

[Igor Tandetnik]

Dusty Burns wrote:
> SELECT peers.compact, torrents.seeds, torrents.peers FROM torrents,
> peers WHERE
> torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
> AND peers.info_hash=torrents.info_hash
> LIMIT 50;
>
> which gives something like this
>
> peers.compact | torrents.seeds | torrents.peers
> ---
> data  | 1234   | 1234
> data  | 1234   | 1234
> data  | 1234   | 1234
> data  | 1234   | 1234
> data  | 1234   | 1234
> etc...
>
> however, i'm getting torrents.seeds, torrents.peers with every row
> returned which is redundant (possibly inefficient?)..
> i only want the torrents.seeds, torrents.peers once.. the
> peers.compact can be returned as usual

If this bothers you so much, you can run two separate queries:

SELECT torrents.seeds, torrents.peers FROM torrents
WHERE torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17';

SELECT peers.compact FROM peers
WHERE peers.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
LIMIT 50;

I predict you'll find performance difference immeasurably small.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



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

[sqlite] query trouble

[Dusty Burns]

Been a while since using SQL of any type and I can't seem to remember how to
do a particular select.

At present, using something like this...

SELECT peers.compact, torrents.seeds, torrents.peers FROM torrents, peers
WHERE torrents.info_hash='517a5ae2e1d79ad252f4c126e4ea30d9c6e51c17'
AND peers.info_hash=torrents.info_hash
LIMIT 50;

which gives something like this

peers.compact | torrents.seeds | torrents.peers
---
data  | 1234   | 1234
data  | 1234   | 1234
data  | 1234   | 1234
data  | 1234   | 1234
data  | 1234   | 1234
etc...

however, i'm getting torrents.seeds, torrents.peers with every row returned
which is redundant (possibly inefficient?)..
i only want the torrents.seeds, torrents.peers once.. the peers.compact can
be returned as usual like so

peers.compact | torrents.seeds | torrents.peers
---
data  | 1234 | 1234
data  | null   | null
data  | null   | null
data  | null   | null
data  | null   | null
etc...

Anyone have any ideas? this would be appreciated.
btw, if this query could be optimized even further, i would welcome that.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing two tables?

[Simon Slavin]

On 30 Sep 2009, at 10:48pm, Petite Abeille wrote:

> On Sep 30, 2009, at 11:41 PM, Simon Slavin wrote:
>
>> It would be technically possible to write some clever SQL to do it  
>> all
>> in one operation.
>
> Why the "clever" characterization?

It's more SQL than most people bother to learn.  What I was trying to  
get across was that my answer was not "This is basic SQL, dummy, learn  
it now." but more along the lines of "This solution is available, but  
it's more than most people would bother to do.".  I did bother to tell  
him it was possible -- I thought it was worth mentioning in case he  
wanted to pursue it.  I could have just not mentioned it.

>> But it would be complicated to debug and ... well,
>> do you really want to waste the time getting it right ?
>
> Hmmm... but then... why bother with a SQL engine in the first place?

He appears to want a persistent database which can do complicated  
searches at a reasonable speed, and SQL is a good way to do that.

>> And also make
>> it difficult for anyone (including you) who has to modify your code
>> later ?
>
> Hmmm... why? What's more complicated about SQL than, say, Python?

He defined the problem in a procedural manner: do /this/, then do / 
that/.  Which means he thinks about it in a procedural manner.  Which  
means that his natural programming style would be to use a procedural  
system to write it in.  Which means he'll find that easiest.  Yes, he  
can drop everything and spend a day learning some complicated SQL, but  
isn't he best off using tools he is already familiar with ?

Had he said that his system was running too slowly, or that speed was  
important I might have written something else.  But premature  
optimisation is a huge time-waster when you just need to get your  
program written and easy to debug.

Many people on this list are SQL experts.  To them, a one line Python  
program that runs a 200 word SQL command with all sorts of rare  
clauses is natural.  That's fine for them, but I was trying to provide  
a good solution, not to encourage the OP to use as many SQLite  
features as possible.  I saw your own fine answers to the problem.   
One of the best things about lists like this is that a question gets  
many answers and the OP can pick the one that appeals most.

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

Re: [sqlite] Comparing two tables?

[Petite Abeille]

On Sep 30, 2009, at 11:41 PM, Simon Slavin wrote:

> It would be technically possible to write some clever SQL to do it all
> in one operation.

Why the "clever" characterization?

>  But it would be complicated to debug and ... well,
> do you really want to waste the time getting it right ?

Hmmm... but then... why bother with a SQL engine in the first place?

> And also make
> it difficult for anyone (including you) who has to modify your code
> later ?

Hmmm... why? What's more complicated about SQL than, say, Python?

Curious :)

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

Re: [sqlite] C code spanish character insert problem

[Bible Trivia Extreme]

Ok thanks for all the great feedback!  Im a bit familiar with
MySQL but new to Sqlite, and certainly dont know any
of the optimizations you've suggested, so I appreciate all
of the feedback.

Using iconv on the original text file, then running my program
worked.  Ill look into the other suggestions next.  I run this
program once before I install the DB's, so performance isnt
an issue, but I will still look at the other suggestions to learn
more.

Thank you all so much!

On Wed, Sep 30, 2009 at 5:10 AM, Simon Slavin
wrote:

>
> On 30 Sep 2009, at 4:56am, Bible Trivia Extreme wrote:
>
> > Open your spanish.txt file in a hex editor.  The letter 'ñ' should be
> >
> >> encoded as C3 B1.  If you see F1 instead, it means your file is in
> >> ISO-8859-1 or something similar.
> >> ___
> >>
> >> Thanks Dan, it seems to be F1.  So what do I do exactly?
> > Im assuming I need to fix the .txt file somehow, but how?
>
> The text file you are reading your data from is an ASCII text file,
> not a Unicode text file.  So the ñ character you read from it is in
> ASCII, and that's what you're writing to your database.
>
> You can convert the text file you're reading from into Unicode (iconv
> is fine).  Or you can use a C call to convert the data you read from
> the file, line by line, from whatever standard it is in (ISO-8859-1 ?)
> into Unicode before you use it in your sqlite3_exec() call.  Depends
> on whether you're just using the file once or it it's constantly being
> remade by something else you can't control.
>
> Roger advised you upthread to stop using sqlite3_exec() and do
> _prepare, _step, _finalise.  There are good reasons why he's right,
> but there are also good reasons to use _exec under certain
> circumstances.  You'll have to weigh up the advantages and
> disadvantages yourself.  He's right about the PRAGMA, though: you
> don't need it.
>
> 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] Comparing two tables?

[Simon Slavin]

On 30 Sep 2009, at 9:55pm, Joe Bennett wrote:

> Find the first row of data in TableB, take Column1 and Column2's data
> from row one and see if that data exists in TableA. Something like
> this: SELECT * FROM TableA WHERE Column1=Row1Data AND
> Column2=Row1Data.

That's a JOIN.  There are lots of types of them but all you need is a  
simple and easy kind.

> When the corresponding data is returned from TableA, I then want to
> check each column in the returned row from TableA matches its
> cooresponding column/ row in TableB... If there is not a match, let me
> know... Then move on to the next row in TableB and do it all over
> again
>
> I'm attempting to do this in Python and am wondering if it would be
> easier to do this with a query or bring each row in as a dictionary
> and compare...???

Probably not.  Write the obvious thing in Python and just use it: one  
SELECT with a JOIN, and then some code to check every row returned.

It would be technically possible to write some clever SQL to do it all  
in one operation.  But it would be complicated to debug and ... well,  
do you really want to waste the time getting it right ?  And also make  
it difficult for anyone (including you) who has to modify your code  
later ?  Does your application currently run slowly enough to cause a  
problem ?

You have one procedural language (Python) and on declarative one  
(SQL).  I think it's safe to do part of your task in a procedural  
manner.

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

Re: [sqlite] Comparing two tables?

[Petite Abeille]

On Sep 30, 2009, at 10:55 PM, Joe Bennett wrote:

> I'm not sure I'm far enough along with sqlite to maybe get what ya'll
> are telling me...

Right... with SQL it helps to thing in terms of set operations instead  
of procedural code... here is an illustration to get you started:

"A Visual Explanation of SQL Joins"
http://www.codinghorror.com/blog/archives/000976.html

To further confuse you:

"Double-thinking in SQL"
http://explainextended.com/2009/07/12/double-thinking-in-sql/


> Have two tables that have the same columns, but different number of
> rows (rows do not match but columns do. 86 matching columns in each
> table):

Right... here is sketch of a possible query:

select  TableA.Column1 as Column1_A,
 TableA.Column2 as Column2_A,
 case
 when coalesce( TableB.Column3, '?' ) =  
coalesce( TableA.Column3, '?' ) then '='
 else '!='
 end as Column3,
 coalesce( TableA.Column3, '?' ) as Column3_A,
 coalesce( TableB.Column3, '?' ) as Column3_B,
 case
 when coalesce( TableB.Column4, '?' ) =  
coalesce( TableA.Column4, '?' ) then '='
 else '!='
 end as Column4,
 coalesce( TableA.Column4, '?' ) as Column4_A,
 coalesce( TableB.Column4, '?' ) as Column4_B

fromTableA

left join   TableB
on  TableB.Column1 = TableA.Columns1
and TableB.Column2 = TableB.Columns1

where   TableB.Column1 is null
or  coalesce( TableB.Column3, '?' ) !=  
coalesce( TableA.Column3, '?' )
or  coalesce( TableB.Column4, '?' ) !=  
coalesce( TableA.Column4, '?' )

In other words, for all rows in TableA, show me all the rows in TableB  
which do not match TableA, either because they don't exist in TableB  
in the first place ("TableB.Column1 is null") or because one of their  
value is different ( "TableB.Column3 != TableA.Column3" ).


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

Re: [sqlite] Comparing two tables?

[Rich Shepard]

On Wed, 30 Sep 2009, Joe Bennett wrote:

> Have two tables that have the same columns, but different number of rows
> (rows do not match but columns do. 86 matching columns in each table):

   That's to be expected; why would the rows match?

> Now, I'm looking to do this:
>
> Find the first row of data in TableB, take Column1 and Column2's data from
> row one and see if that data exists in TableA. Something like this: SELECT
> * FROM TableA WHERE Column1=Row1Data AND Column2=Row1Data.

   It appears that you want the INTERSECT operator; it finds the set of all
rows in two tables based on common columns. It removes duplicates.

   This is a standard SQL operation (after all, SQL is a language designed to
work with sets). See Section 15.4 (Combining With INTERSECT) in Rick van der
Lans's "The SQL Guide to SQLite" or the equivalent section in his
"Introduction to SQL, 4th Edition."

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] Ideas for the wiki

[Ing. Marcos L. Ortí­z Valmaseda]

I have several ideas to the wiki:
1- Programming part:
   * SQlite + Ruby (this is my new programming language)
   * SQLite + Python (this is my favorite)
   * SQLite on IPhone (I don´t know how is this but I ´ll google it and 
I can publish on the wiki for all users)

   * SQLite on Symbian (the same)
   * SQlite + Java
   * SQLite + PHP

  
2- Second part:  I want to search information about how SQLite works 
with ORM; for example with Propel,Doctrine o SQLAlchemy


And for the finish this first contribution: you can say me what clean 
sheets we can include on the wiki:

* mainly useful pieces of code

What do you think?
I want to start the friday to do all this
You can say if this is useful for the community.

Regards

--
"DBAs must implements decisions based on the best fit of the application,DBMS, 
and platform
..for that reason...I use PostgreSQL + Linux + SQLite + BSD"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

FreeBSD User
http://www.freebsd.org/es

Linux User # 418229
http://counter.li.org

PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/

SQLite User 
http://www.sqlite.org



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

Re: [sqlite] In-memory database backup

[Andres Velasco Garcia]

thanks, that's exactly what I was looking for. 
Sorry for the  too basic question. 

Andres Velasco 
M: +34 670 40 73 69 
Skype: newwwave.andres.velasco




> Date: Wed, 30 Sep 2009 17:37:10 +0200
> From: engelsch...@codeswift.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] In-memory database backup
> 
> Hi,
> 
> you can use the online backup api described here:
> http://www.sqlite.org/backup.html
> 
> Martin
> 
> Andres Velasco Garcia schrieb:
> > Hello,
> >
> > Do any of you know if it is possible to backup an in-memory database to 
> > disk so it can be recovered later from disk.
> >
> > Thanks
> >
> > Andres Velasco 
> > M: +34 670 40 73 69 
> > Skype: newwwave.andres.velasco
> >
> >
> >   
> > ___
> > 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] Inclusion of real examples of the use of SQLite on the official documentation

[Ing. Marcos L. Ortí­z Valmaseda]

P Kishor escribió:

2009/9/30 "Ing. Marcos L. Ortí­z Valmaseda" :
  

P Kishor escribió:

2009/9/30 "Ing. Marcos L. Ortí­z Valmaseda" :


Why we can´t include real examples of the use of SQLite on the official
documentation?
That would be very useful for the new SQLite users.


What is a real example?

Would you want a tutorial on how to use SQL? Google for it; there are
many already.

What else is real? Embedded sqlite? sqlite with Perl or Ruby? sqlite
inside Adobe Lightroom? sqlite on iPhone or on VxWorks? sqlite with C
or C# or Java? sqlite in an XUL application?

What is real?




Regards



..
  



For instance, that same examples that you mentioned here,
- SQLite + Ruby
- SQLite on IPhone
- SQLite on Symbian
etc.
The question is not to google it, but we can give it on the docs,
very basic, but with a background of the fact.
This is my personal opinion ok?



..

Great idea! You know there is a SQLite wiki. If you write the above
examples up, you can add them to the wiki yourself. I am sure others
will be most grateful for it.



  

OK, this will be my first contribution to the project
Thanks.


--
"DBAs must implements decisions based on the best fit of the application,DBMS, 
and platform
..for that reason...I use PostgreSQL + Linux + SQLite + BSD"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

FreeBSD User
http://www.freebsd.org/es

Linux User # 418229
http://counter.li.org

PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/

SQLite User 
http://www.sqlite.org



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

Re: [sqlite] Inclusion of real examples of the use of SQLite on the official documentation

[P Kishor]

2009/9/30 "Ing. Marcos L. Ortí­z Valmaseda" :
> P Kishor escribió:
>
> 2009/9/30 "Ing. Marcos L. Ortí­z Valmaseda" :
>
>
> Why we can´t include real examples of the use of SQLite on the official
> documentation?
> That would be very useful for the new SQLite users.
>
>
> What is a real example?
>
> Would you want a tutorial on how to use SQL? Google for it; there are
> many already.
>
> What else is real? Embedded sqlite? sqlite with Perl or Ruby? sqlite
> inside Adobe Lightroom? sqlite on iPhone or on VxWorks? sqlite with C
> or C# or Java? sqlite in an XUL application?
>
> What is real?
>
>
>
>
> Regards
>
..
>
>
>
>
> For instance, that same examples that you mentioned here,
> - SQLite + Ruby
> - SQLite on IPhone
> - SQLite on Symbian
> etc.
> The question is not to google it, but we can give it on the docs,
> very basic, but with a background of the fact.
> This is my personal opinion ok?
>
..

Great idea! You know there is a SQLite wiki. If you write the above
examples up, you can add them to the wiki yourself. I am sure others
will be most grateful for it.



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

Re: [sqlite] Inclusion of real examples of the use of SQLite on the official documentation

[Ing. Marcos L. Ortí­z Valmaseda]

P Kishor escribió:

2009/9/30 "Ing. Marcos L. Ortí­z Valmaseda" :
  

Why we can´t include real examples of the use of SQLite on the official
documentation?
That would be very useful for the new SQLite users.



What is a real example?

Would you want a tutorial on how to use SQL? Google for it; there are
many already.

What else is real? Embedded sqlite? sqlite with Perl or Ruby? sqlite
inside Adobe Lightroom? sqlite on iPhone or on VxWorks? sqlite with C
or C# or Java? sqlite in an XUL application?

What is real?


  

Regards

--
"DBAs must implements decisions based on the best fit of the
application,DBMS, and platform
..for that reason...I use PostgreSQL + Linux + SQLite + BSD"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

FreeBSD User
http://www.freebsd.org/es
Linux User # 418229
http://counter.li.org
PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/

SQLite User http://www.sqlite.org



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







  

For instance, that same examples that you mentioned here,
- SQLite + Ruby
- SQLite on IPhone
- SQLite on Symbian
etc.
The question is not to google it, but we can give it on the docs,
very basic, but with a background of the fact.
This is my personal opinion ok?

Regards..

--
"DBAs must implements decisions based on the best fit of the application,DBMS, 
and platform
..for that reason...I use PostgreSQL + Linux + SQLite + BSD"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

FreeBSD User
http://www.freebsd.org/es

Linux User # 418229
http://counter.li.org

PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/

SQLite User 
http://www.sqlite.org



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

Re: [sqlite] Comparing two tables?

[Joe Bennett]

I'm not sure I'm far enough along with sqlite to maybe get what ya'll
are telling me... I'll try explaining it this way...


Have two tables that have the same columns, but different number of
rows (rows do not match but columns do. 86 matching columns in each
table):

TableA:

Column1Column 2Column3 Column4
Row1Data  Row1Data   Row1DataRow1Data
Row2Data  Row2Data   Row2DataRow2Data
Row3Data  Row3Data   Row3DataRow3Data
Row4Data  Row4Data   Row4DataRow4Data


TableB:
Column1Column 2Column3 Column4
Row1Data  Row1Data   Row1DataRow1Data
Row2Data  Row2Data   Row2DataRow2Data



Now, I'm looking to do this:

Find the first row of data in TableB, take Column1 and Column2's data
from row one and see if that data exists in TableA. Something like
this: SELECT * FROM TableA WHERE Column1=Row1Data AND
Column2=Row1Data.

When the corresponding data is returned from TableA, I then want to
check each column in the returned row from TableA matches its
cooresponding column/ row in TableB... If there is not a match, let me
know... Then move on to the next row in TableB and do it all over
again

I'm attempting to do this in Python and am wondering if it would be
easier to do this with a query or bring each row in as a dictionary
and compare...???


-Joe

On Tue, Sep 29, 2009 at 2:39 PM, Petite Abeille
 wrote:
>
> On Sep 29, 2009, at 6:50 PM, Cory Nelson wrote:
>
>> i believe he means except, not minus.
>
> Correct. Got my SQL dialects intermingled :)
>
>> If all you need is differing
>> rows, this will work like a charm.  Otherwise if you need a more
>> fine-grained delta like only returning columns that changed, you will
>> need a more complex (but still pretty simple) join.
>>
>> SELECT * FROM t_foo EXCEPT SELECT * FROM t_bar;
>
> And for the "fancy" join, something like:
>
> select     *
> from       bar
> left join  foo
> on         foo.id = bar.id
> where      foo.id is null
> or         foo.baz != bar.baz
>
> etc...
>
> As always, details might vary.
>
>
> ___
> 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] Inclusion of real examples of the use of SQLite on the official documentation

[P Kishor]

2009/9/30 "Ing. Marcos L. Ortí­z Valmaseda" :
> Why we can´t include real examples of the use of SQLite on the official
> documentation?
> That would be very useful for the new SQLite users.

What is a real example?

Would you want a tutorial on how to use SQL? Google for it; there are
many already.

What else is real? Embedded sqlite? sqlite with Perl or Ruby? sqlite
inside Adobe Lightroom? sqlite on iPhone or on VxWorks? sqlite with C
or C# or Java? sqlite in an XUL application?

What is real?


>
> Regards
>
> --
> "DBAs must implements decisions based on the best fit of the
> application,DBMS, and platform
> ..for that reason...I use PostgreSQL + Linux + SQLite + BSD"
>
> Ing. Marcos L. Ortiz Valmaseda
> Línea Soporte y Despliegue
> Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)
>
> FreeBSD User
> http://www.freebsd.org/es
> Linux User # 418229
> http://counter.li.org
> PostgreSQL User
> http://www.postgresql.org
> http://www.planetpostgresql.org/
> http://www.postgresql-es.org/
>
> SQLite User http://www.sqlite.org
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>



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

[sqlite] How I can to become on a new SQLite developer

[Ing. Marcos L. Ortí­z Valmaseda]

I don´t need anything to collaborate with the project, I just want to 
help with anything that you desire.
For example, for this moment I can help on the translation work to my 
language (Spanish)

Regards and I´ll be waiting your answers.

"DBAs must implements decisions based on the best fit of the 
application,DBMS, and platform

..for that reason...I use PostgreSQL + Linux + SQLite + BSD"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

FreeBSD User
http://www.freebsd.org/es

Linux User # 418229
http://counter.li.org

PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/

SQLite User
http://www.sqlite.org


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

[sqlite] Inclusion of real examples of the use of SQLite on the official documentation

[Ing. Marcos L. Ortí­z Valmaseda]

Why we can´t include real examples of the use of SQLite on the official 
documentation?

That would be very useful for the new SQLite users.

Regards

--
"DBAs must implements decisions based on the best fit of the application,DBMS, 
and platform
..for that reason...I use PostgreSQL + Linux + SQLite + BSD"

Ing. Marcos L. Ortiz Valmaseda
Línea Soporte y Despliegue
Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

FreeBSD User
http://www.freebsd.org/es
Linux User # 418229
http://counter.li.org
PostgreSQL User
http://www.postgresql.org
http://www.planetpostgresql.org/
http://www.postgresql-es.org/

SQLite User 
http://www.sqlite.org



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

Re: [sqlite] multiple prepare statements

[Scott Hess]

On Wed, Sep 30, 2009 at 11:58 AM, Sam Carleton  wrote:
> On Wed, Sep 30, 2009 at 10:02 AM, Scott Hess  wrote:
>> Since SQLite is an embedded database, it generally does not pay to
>> count statements, unless they add additional disk I/O.  You can code
>> like this:
>>
>>  BEGIN
>>    SELECT ...
>>    if (select results A)
>>      INSERT ...
>>    else
>>      UPDATE ...
>>  END
>
> I am assuming the conditional stuff is pseudo code in the host
> language, in my case C+, not in SQLite's SQL.  Correct?
>
> In my case, my C+ code will ALWAYS know if it is an insert or update
> because the PK for the update will be greater the -1, for an insert
> the PK is always -1.  So, unless I am mistaken, there is no need to
> "check" for existence first.  Not being a DB guru, I might be making a
> fundamental mistake, though...

Guess I don't understand your question then.  Igor's response of "as
many statements as you need" is probably the right one.

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

Re: [sqlite] multiple prepare statements

[Sam Carleton]

On Wed, Sep 30, 2009 at 10:02 AM, Scott Hess  wrote:
>
> Since SQLite is an embedded database, it generally does not pay to
> count statements, unless they add additional disk I/O.  You can code
> like this:
>
>  BEGIN
>    SELECT ...
>    if (select results A)
>      INSERT ...
>    else
>      UPDATE ...
>  END

I am assuming the conditional stuff is pseudo code in the host
language, in my case C+, not in SQLite's SQL.  Correct?

In my case, my C+ code will ALWAYS know if it is an insert or update
because the PK for the update will be greater the -1, for an insert
the PK is always -1.  So, unless I am mistaken, there is no need to
"check" for existence first.  Not being a DB guru, I might be making a
fundamental mistake, though...

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

Re: [sqlite] Compiler warnings with Sun Studio 12.1 on Solaris 10

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Nicolas Williams wrote:
> Oh, I think you meant that the L suffix would break on anything other
> than LP64 or ILP64 models.

Correct.  L means long.  On platforms where long is 32 bits, shifting a long
value by more than 32 bits will give zero.

> I'm not entirely sure of the use of the L
> suffix, and perhaps that would be a problem. 

It would on all platforms in 32 bit mode, and those 64 bit platforms that
are P64 such as Windows.  There is a LL suffix for 64 bit constants (ie long
long) but it isn't universally available.

> If you can't find a
> constant that works equally well in ILP32 and LP64 then you can use
> #ifdefs.  But I think that it is possible to have such a constant
> without causing compiler warnings 

And that is exactly what the case was.  It is only your compiler that
whined!  (As a general rule the compilers aren't too good at distinguishing
between code that knows what it is doing when mixing different sizes and
code that is "broken" when doing so.  "broken" means unintentionally losing
or retaining portions of the values being used.)

> (for one, you can have the literal constant, in decimal).

You can't.  If a value is too large then the compiler will either error or
truncate the most significant part.  You can stop the truncation by having
the LL (or ULL) suffixes but that isn't standard/portable enough.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkrDpFkACgkQmOOfHg372QRwDACfebSMj2NzQNFFJQuC1RnEUWu6
0WYAoMq2qZm3vLMhadlMQhurfMe0J8lP
=arsu
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiler warnings with Sun Studio 12.1 on Solaris 10

[Nicolas Williams]

On Tue, Sep 29, 2009 at 05:28:39PM -0700, Roger Binns wrote:
> Nicolas Williams wrote:
> > On Tue, Sep 29, 2009 at 11:21:30AM -0700, Roger Binns wrote:
> >> Nicolas Williams wrote:
> >>> If you move the cast to the left the warning should go away: 
> >>> ((sqlite3_int64)(1L<<63))
> >> And this is why making warnings go away leads to bugs.  The replacement
> >> above will only work if sizeof(long)==sizeof(long long) which is not the
> >> case on Windows in 64 bit mode or in 32 bit mode in general on any 
> >> platform.
> > 
> > Where is long long entering the picture here? 
> 
> Really? The cast is to a 64 bit quantity.  Pretty much every compiler uses
> 'long long' to represent a 64 bit quantity (even in 32 bit mode), although
> some also have __int64 and others int64_t depending on age and standards
> compliance.

Oh, I think you meant that the L suffix would break on anything other
than LP64 or ILP64 models.  I'm not entirely sure of the use of the L
suffix, and perhaps that would be a problem.  If you can't find a
constant that works equally well in ILP32 and LP64 then you can use
#ifdefs.  But I think that it is possible to have such a constant
without causing compiler warnings (for one, you can have the literal
constant, in decimal).

> Another example of compilers whining is gcc saying that in one part of the
> code, a parameter to memset could be zero (which usually indicates a
> programming error).  Except the compiler is wrong as human inspection
> proves.  Just because a warning is present does not mean it is right or that
> the compiler is perfect.  Hiding these warnings is even more dangerous
> because it obfuscates the original intention of the code.  (This is why a
> universal no warnings policy can hurt.)

Indeed, and I've expressed agreement on that.

> Demonstrate the code is wrong functionality (ie tests are broken) or that it
> isn't written in a standards compliant way :-)  The compiler warnings could
> help point to those locations.

In this case, there's no problem with ignoring this warning.

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

Re: [sqlite] multiple prepare statements

[Ken]

--- On Wed, 9/30/09, Scott Hess  wrote:

> From: Scott Hess 
> Subject: Re: [sqlite] multiple prepare statements
> To: "General Discussion of SQLite Database" 
> Date: Wednesday, September 30, 2009, 9:02 AM
> Since SQLite is an embedded database,
> it generally does not pay to
> count statements, unless they add additional disk
> I/O.  You can code
> like this:
> 
>   BEGIN
>     SELECT ...
>     if (select results A)
>       INSERT ...
>     else
>       UPDATE ...
>   END
> 
> and it will be about as fast as either the INSERT or the
> UPDATE run
> independently.  This is because the INSERT or the
> UPDATE will have to
> read in all the pages the SELECT would have read in, so the
> SELECT is
> essentially free (just a small cost in CPU).  Well,
> assuming that your
> SELECT is selecting the rows you mean to UPDATE or INSERT
> ...
> 
> -scott
> 

Depending upon your system and your data. 
Say you have some type of Primary Key or unique index.

For the case where updates happen infrequently code this way.
Begin
Insert into ..
IF PK failure
   Update
Commit

If you tend to load up the data then have more updates.

Begin
 Update 
 IF No data Found (0 rows updated)
 Insert
Commit


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

Re: [sqlite] In-memory database backup

[Jim "Jed" Dodgen]

or simply .dump it

On Wed, Sep 30, 2009 at 8:37 AM, Martin.Engelschalk
 wrote:
> Hi,
>
> you can use the online backup api described here:
> http://www.sqlite.org/backup.html
>
> Martin
>
> Andres Velasco Garcia schrieb:
>> Hello,
>>
>> Do any of you know if it is possible to backup an in-memory database to disk 
>> so it can be recovered later from disk.
>>
>> Thanks
>>
>> Andres Velasco
>> M: +34 670 40 73 69
>> Skype: newwwave.andres.velasco
>>
>>
>>
>> ___
>> 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
>



-- 
Jim "Jed" Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory database backup

[Martin.Engelschalk]

Hi,

you can use the online backup api described here:
http://www.sqlite.org/backup.html

Martin

Andres Velasco Garcia schrieb:
> Hello,
>
> Do any of you know if it is possible to backup an in-memory database to disk 
> so it can be recovered later from disk.
>
> Thanks
>
> Andres Velasco 
> M: +34 670 40 73 69 
> Skype: newwwave.andres.velasco
>
>
> 
> ___
> 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] In-memory database backup

[Sebastian Bermudez]

you can attach a new slqite DB..
do insert into new_table_in_new_db select * from mem_table;

deatach db





- Mensaje original 
De: Andres Velasco Garcia 
Para: sqlite-users@sqlite.org
Enviado: miércoles 30 de septiembre de 2009, 12:27:24
Asunto: [sqlite] In-memory database backup


Hello,

Do any of you know if it is possible to backup an in-memory database to disk so 
it can be recovered later from disk.

Thanks

Andres Velasco 
M: +34 670 40 73 69 
Skype: newwwave.andres.velasco


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



  Yahoo! Cocina

Encontra las mejores recetas con Yahoo! Cocina.


http://ar.mujer.yahoo.com/cocina/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C code spanish character insert problem

[Simon Slavin]

On 30 Sep 2009, at 4:56am, Bible Trivia Extreme wrote:

> Open your spanish.txt file in a hex editor.  The letter 'ñ' should be
>
>> encoded as C3 B1.  If you see F1 instead, it means your file is in
>> ISO-8859-1 or something similar.
>> ___
>>
>> Thanks Dan, it seems to be F1.  So what do I do exactly?
> Im assuming I need to fix the .txt file somehow, but how?

The text file you are reading your data from is an ASCII text file,  
not a Unicode text file.  So the ñ character you read from it is in  
ASCII, and that's what you're writing to your database.

You can convert the text file you're reading from into Unicode (iconv  
is fine).  Or you can use a C call to convert the data you read from  
the file, line by line, from whatever standard it is in (ISO-8859-1 ?)  
into Unicode before you use it in your sqlite3_exec() call.  Depends  
on whether you're just using the file once or it it's constantly being  
remade by something else you can't control.

Roger advised you upthread to stop using sqlite3_exec() and do  
_prepare, _step, _finalise.  There are good reasons why he's right,  
but there are also good reasons to use _exec under certain  
circumstances.  You'll have to weigh up the advantages and  
disadvantages yourself.  He's right about the PRAGMA, though: you  
don't need it.

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

Re: [sqlite] multiple prepare statements

[Scott Hess]

On Wed, Sep 30, 2009 at 7:13 AM, Pavel Ivanov  wrote:
>> Well, assuming that your
>> SELECT is selecting the rows you mean to UPDATE or INSERT ...
>
> Also assuming that
> - all data necessary for these statements can fit into sqlite's cache;

Generally the desire to UPDATE-if-present-else-INSERT involves a
single row and the UPDATE involves a WHERE clause on a primary key.
I'm sure other cases could be constructed, but an all-in-one statement
to handle them would not be reasonable.

> - several instances of your application cannot be executed in parallel
> (otherwise you have to do a special treatment for SQLITE_BUSY return
> value);

In some cases the easy fix to this is "BEGIN IMMEDIATE".

> And finally could you say it once more: what is the benefit from doing
> select first and then insert/update as opposed to just insert/update
> without select? I see that in your case insert/update could be
> executed faster, without disk I/O, but if we look at them combined
> what's the difference?

The advantage is that the code is easier to read.  It essentially says
"Is there a row there?  If so, update it, otherwise insert a new row."

I'm not meaning to suggest that there is never a benefit to attempting
the UPDATE then following up with an INSERT if the row is missing (and
then an UPDATE if someone beats you in).  It's just that the benefit
to that pattern is often not NEARLY so great as it is in a
client/server system, where the communications overhead often
dominates the query time.  If you prepare your statements ahead of the
BEGIN, and then blast through them with no non-SQLite calls
interleaved, then your code often isn't going to be much less
efficient than SQLite would be doing it all internally.  I see this a
lot, where coders write a convoluted statement to save time which
would probably save time over the alternative on MYSQL, but which
doesn't help much on SQLite.  I guess measure before making it
convoluted :-).

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

Re: [sqlite] multiple prepare statements

[Pavel Ivanov]

> Well, assuming that your
> SELECT is selecting the rows you mean to UPDATE or INSERT ...

Also assuming that
- all data necessary for these statements can fit into sqlite's cache;
- several instances of your application cannot be executed in parallel
(otherwise you have to do a special treatment for SQLITE_BUSY return
value);

And finally could you say it once more: what is the benefit from doing
select first and then insert/update as opposed to just insert/update
without select? I see that in your case insert/update could be
executed faster, without disk I/O, but if we look at them combined
what's the difference?

Pavel

On Wed, Sep 30, 2009 at 10:02 AM, Scott Hess  wrote:
> Since SQLite is an embedded database, it generally does not pay to
> count statements, unless they add additional disk I/O.  You can code
> like this:
>
>  BEGIN
>    SELECT ...
>    if (select results A)
>      INSERT ...
>    else
>      UPDATE ...
>  END
>
> and it will be about as fast as either the INSERT or the UPDATE run
> independently.  This is because the INSERT or the UPDATE will have to
> read in all the pages the SELECT would have read in, so the SELECT is
> essentially free (just a small cost in CPU).  Well, assuming that your
> SELECT is selecting the rows you mean to UPDATE or INSERT ...
>
> -scott
>
>
> On Tue, Sep 29, 2009 at 7:20 PM, Sam Carleton
>  wrote:
>> The function is given a collection of data that needs to be either
>> insert or update into the database.  One options would be to use
>> INSERT OR REPLACE INTO, but the desire is not to remove N add when it
>> is an update, but simply do an update using the rowid.  All the new
>> items that need to be inserted won't have a rowid.
>>
>> Is it possible to have two different prepare statements at one time;
>> one for insert and another for update.  Then check each piece of data
>> for a rowid to determine which approach to use.  Once it is all done,
>> clean up both prepare statement.
>>
>> Sam
>> ___
>> 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] multiple prepare statements

[Scott Hess]

Since SQLite is an embedded database, it generally does not pay to
count statements, unless they add additional disk I/O.  You can code
like this:

  BEGIN
SELECT ...
if (select results A)
  INSERT ...
else
  UPDATE ...
  END

and it will be about as fast as either the INSERT or the UPDATE run
independently.  This is because the INSERT or the UPDATE will have to
read in all the pages the SELECT would have read in, so the SELECT is
essentially free (just a small cost in CPU).  Well, assuming that your
SELECT is selecting the rows you mean to UPDATE or INSERT ...

-scott


On Tue, Sep 29, 2009 at 7:20 PM, Sam Carleton
 wrote:
> The function is given a collection of data that needs to be either
> insert or update into the database.  One options would be to use
> INSERT OR REPLACE INTO, but the desire is not to remove N add when it
> is an update, but simply do an update using the rowid.  All the new
> items that need to be inserted won't have a rowid.
>
> Is it possible to have two different prepare statements at one time;
> one for insert and another for update.  Then check each piece of data
> for a rowid to determine which approach to use.  Once it is all done,
> clean up both prepare statement.
>
> Sam
> ___
> 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 you check if the linker is the GNU linker?

[Dr. David Kirkby]

Mihai Limbasan wrote:
> Hi, Dave!
> 
> That piece of code comes from the macros inserted by the libtool 
> support. Normally, you should find the macro in 
> /usr/share/aclocal/lib-ld.m4 (at least on Fedora, Debian, and Gentoo.) 
> In case it's not there, I've reproduced the M4 macro below. Please note 
> that it's licensed as follows:
> 
> dnl Copyright (C) 1996-2003 Free Software Foundation, Inc.
> dnl This file is free software; the Free Software Foundation
> dnl gives unlimited permission to copy and/or distribute it,
> dnl with or without modifications, as long as this notice is preserved.
> 
> And the actual macro:
> 
> dnl From libtool-1.4. Sets the variable with_gnu_ld to yes or no.
> AC_DEFUN([AC_LIB_PROG_LD_GNU],
> [AC_CACHE_CHECK([if the linker ($LD) is GNU ld], acl_cv_prog_gnu_ld,
> [# I'd rather use --version here, but apparently some GNU ld's only 
> accept -v.
> case `$LD -v 2>&1  *GNU* | *'with BFD'*)
>acl_cv_prog_gnu_ld=yes ;;
> *)
>acl_cv_prog_gnu_ld=no ;;
> esac])
> with_gnu_ld=$acl_cv_prog_gnu_ld
> ])
> 
> Hope this helps,
> Mihai

Thank you for that - it was helpful.

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