Re: [sqlite] "make test" was failed

[Wenbo Zhao]

i can't see the attachment
why don't you use the sqlite-amalgamation*.zip ? that's very easy to compile

2009/9/14 TEZ 

> "make test" became faild. What's wrong?
>
> I tried with sqlite-3.6.17.tar.gz and sqlite-3.6.18.tar.gz, but both
> packages
> failed by running "make test". Environments are Cygwin on WindowsXP SP3 and
> VineLinux4.2 (http://www.vinelinux.org/).
>
> Attached files were recorded by "make test >& foo.log" after running
> "./configure && make".
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 

Best Regards,
ZHAO, Wenbo

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

Re: [sqlite] accessing bound variables in trigger

[Igor Tandetnik]

Rich Rattanni  wrote:
> Can a trigger reference the bound variables?  I figure the answer is
> NO.

The answer is NO.

Igor Tandetnik 



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

[sqlite] accessing bound variables in trigger

[Rich Rattanni]

I have an application that performs the following SQL action:

UPDATE table SET value = date('now', ?1, 'localtime') WHERE id = ?2

In my code I bind an integer to ?1.  This results in null.  I never intended
to code it that way, it is an honest to goodness bug.  I wanted to bind
'+1 days' or something similar.  However, I wanted
to know if I could cheat.

Can a trigger reference the bound variables?  I figure the answer is NO.
I realize exposing bound variables to a trigger is silly for many reasons,
but I figured I would ask the experts.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question on converting objects into relational db

[CityDev]

Simon Slavin-2 wrote:
> 
> You can use property lists.  
> 

It's an interesting question as to what that gives you. It's clearly not a
relation if you know how it was formed but - just thinking about it briefly
- it may still behave as a relation as far as SQL is concerned. But it looks
dodgy to me.

-- 
View this message in context: 
http://www.nabble.com/Question-on-converting-objects-into-relational-db-tp25385539p25443743.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] Slow SELECT query

[Kees Nuyt]

On Mon, 14 Sep 2009 19:58:31 +0200, Kees Nuyt
 wrote:

Oops, that should read

> 100 events per second during 15 years 
>   = 4730400
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question on converting objects into relational db

[Simon Slavin]

On 14 Sep 2009, at 10:38am, CityDev wrote:

> Bear in mind a relational database is designed around relations.  
> It's not
> obvious what your 'data model' is but there is a suggestion it  
> contains
> variable types of things. If you want to store variable things then  
> many of
> the features of a relational database don't work.
>
> Often an object maps reasonably to an entity, and an entity shares its
> structure with other entities of the same entity type. In this case  
> you can
> easily map object attributes to fields in a table.
>
> Maybe you are storing something more esoteric. You may have to  
> serialize the
> object rather than store it as fields.

You can use property lists.  And you can use a relational database to  
implement property lists.  For instance, TABLE properties:

Object  PropertyValue
Car PurchaseDate2001
Car Colour  Orange
Camera  Colour  Silver
Camera  MakePanasonic
Cat Colour  Black

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

Re: [sqlite] Slow SELECT query

[Kees Nuyt]

On Mon, 14 Sep 2009 06:09:45 -0700 (PDT), wlof
 wrote:

>
>Thanks Kees. My app still needs some optimizations, your suggestions look
>good.
>
>One question though: what will happen when id reaches 2^32 - 1 ? (This app
>is going to log a lot of events, 24/7 for a long time, so it's not a purely
>theoretical question.)

SQLite uses 64 bit signed integers, so you get a max value
in the order of 2^63
= 9223372036854775810
10 events per second during 15 years 
= 4730400

See the difference? No issue.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_blob_read declaration

[Pavel Ivanov]

> eVC++ 4 is not the same as VC++ 4.  eVC++ 4 is an embedded C/C++ compiler.

I do apologize. Thought that this 'e' was just a typo.
The same wiki page says that eVC++ was replaced by VC++ 2005
suggesting that it's much newer compiler. And it makes more strange
that it has such bug...

Pavel

On Mon, Sep 14, 2009 at 10:28 AM, Wilson, Ronald  wrote:
> eVC++ 4 is not the same as VC++ 4.  eVC++ 4 is an embedded C/C++ compiler.
>
> http://www.microsoft.com/downloads/details.aspx?FamilyId=1DACDB3D-50D1-41B2-A107-FA75AE960856=en
>
> RW
>
> Ron Wilson, Engineering Project Lead
> (o) 434.455.6453, (m) 434.851.1612, www.harris.com
>
> HARRIS CORPORATION   |   RF Communications Division     
> assuredcommunications(tm)
>
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Pavel Ivanov
>> Sent: Monday, September 14, 2009 9:53 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] sqlite3_blob_read declaration
>>
>>
>> > And your compiler seems to be buggy
>>
>> BTW, Wikipedia says that VC++ 4.0 "was designed for Windows 95 and
>> Windows NT" suggesting that it was released about 15 years ago (which
>> definitely mean that it has buggy C++ part). I'm just curious: for
>> what OS are you developing with this compiler now?
>>
>> Pavel
>>
>> On Mon, Sep 14, 2009 at 9:33 AM, Igor Tandetnik 
>> wrote:
>> > Filipe Madureira wrote:
>> >> I have an error for sometime compiling the latest versions of SQLite
>> >> amalgamation on eVC++ 4
>> >> error: "'Z' : use of class template requires template argument list"
>> >>
>> >>
>> >> The problem is in the declaration of "sqlite3_blob_read prototype".
>> >> It is declared in "sqlite3.h" and "sqlite3.c" as:
>> >>
>> >> SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int
>> >> iOffset);
>> >
>> > You have a template class Z declared in some header that you include
>> > before sqlite3.h. And your compiler seems to be buggy - parameter
>> names
>> > shouldn't be subject to name lookup, and thus shouldn't conflict with
>> > class names.
>> >
>> > Igor Tandetnik
>> >
>> >
>> >
>> > ___
>> > 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] sqlite3_blob_read declaration

[Wilson, Ronald]

eVC++ 4 is not the same as VC++ 4.  eVC++ 4 is an embedded C/C++ compiler.

http://www.microsoft.com/downloads/details.aspx?FamilyId=1DACDB3D-50D1-41B2-A107-FA75AE960856=en

RW

Ron Wilson, Engineering Project Lead
(o) 434.455.6453, (m) 434.851.1612, www.harris.com

HARRIS CORPORATION   |   RF Communications Division 
assuredcommunications(tm)


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Monday, September 14, 2009 9:53 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] sqlite3_blob_read declaration
> 
> 
> > And your compiler seems to be buggy
> 
> BTW, Wikipedia says that VC++ 4.0 "was designed for Windows 95 and
> Windows NT" suggesting that it was released about 15 years ago (which
> definitely mean that it has buggy C++ part). I'm just curious: for
> what OS are you developing with this compiler now?
> 
> Pavel
> 
> On Mon, Sep 14, 2009 at 9:33 AM, Igor Tandetnik 
> wrote:
> > Filipe Madureira wrote:
> >> I have an error for sometime compiling the latest versions of SQLite
> >> amalgamation on eVC++ 4
> >> error: "'Z' : use of class template requires template argument list"
> >>
> >>
> >> The problem is in the declaration of "sqlite3_blob_read prototype".
> >> It is declared in "sqlite3.h" and "sqlite3.c" as:
> >>
> >> SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int
> >> iOffset);
> >
> > You have a template class Z declared in some header that you include
> > before sqlite3.h. And your compiler seems to be buggy - parameter
> names
> > shouldn't be subject to name lookup, and thus shouldn't conflict with
> > class names.
> >
> > Igor Tandetnik
> >
> >
> >
> > ___
> > 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] sqlite3_blob_read declaration

[Pavel Ivanov]

> And your compiler seems to be buggy

BTW, Wikipedia says that VC++ 4.0 "was designed for Windows 95 and
Windows NT" suggesting that it was released about 15 years ago (which
definitely mean that it has buggy C++ part). I'm just curious: for
what OS are you developing with this compiler now?

Pavel

On Mon, Sep 14, 2009 at 9:33 AM, Igor Tandetnik  wrote:
> Filipe Madureira wrote:
>> I have an error for sometime compiling the latest versions of SQLite
>> amalgamation on eVC++ 4
>> error: "'Z' : use of class template requires template argument list"
>>
>>
>> The problem is in the declaration of "sqlite3_blob_read prototype".
>> It is declared in "sqlite3.h" and "sqlite3.c" as:
>>
>> SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int
>> iOffset);
>
> You have a template class Z declared in some header that you include
> before sqlite3.h. And your compiler seems to be buggy - parameter names
> shouldn't be subject to name lookup, and thus shouldn't conflict with
> class names.
>
> Igor Tandetnik
>
>
>
> ___
> 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] sqlite3_blob_read declaration

[Igor Tandetnik]

Filipe Madureira wrote:
> I have an error for sometime compiling the latest versions of SQLite
> amalgamation on eVC++ 4
> error: "'Z' : use of class template requires template argument list"
>
>
> The problem is in the declaration of "sqlite3_blob_read prototype".
> It is declared in "sqlite3.h" and "sqlite3.c" as:
>
> SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int
> iOffset);

You have a template class Z declared in some header that you include 
before sqlite3.h. And your compiler seems to be buggy - parameter names 
shouldn't be subject to name lookup, and thus shouldn't conflict with 
class names.

Igor Tandetnik 



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

Re: [sqlite] one liner for insert or update ?

[Jay A. Kreibich]

On Mon, Sep 14, 2009 at 01:25:29AM -0700, yogibabu scratched on the wall:
> 
> 
> 
> Jean-Christophe Deschamps wrote:
> > 
> > Do you have a short example where "INSERT OR REPLACE INTO ..." can be 
> > shown to actually _delete_ rows?
> > Which query are you refering to?
> 
> sorry, i meant "columns" not rows, whet i do insert command, specifying
> existing id and only some of the columns then the rest of the columns are
> deleted

  The whole row is deleted.  The command is INSERT OR REPLACE, as in
  "INSERT OR (DELETE AND INSERT)", not "INSERT OR UPDATE".  And if you
  want to get real picky, it is actually a variation on "INSERT,
  DELETE ON CONFLICT." In other words, there are situations when an 
  INSERT OR REPLACE can end up DELETEing more than one existing row
  before it INSERTs the new row.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_blob_read declaration

[Pavel Ivanov]

Error message suggests that you're compiling C code of SQLite as C++.
So the first suggestion will be - do not do that, It's not always
compatible. Change settings for compilation of sqlite3.c to C language
(I don't know how it's done in VC++ 4, sorry).

Pavel

On Mon, Sep 14, 2009 at 9:21 AM, Filipe Madureira
 wrote:
> Hi,
>
> I have an error for sometime compiling the latest versions of SQLite
> amalgamation on eVC++ 4
> error: "'Z' : use of class template requires template argument list"
>
>
> The problem is in the declaration of "sqlite3_blob_read prototype".
> It is declared in "sqlite3.h" and "sqlite3.c" as:
>
> SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int
> iOffset);
>
>
> and it is implemented in "sqlite3.c" as:
>
> SQLITE_API int sqlite3_blob_read(sqlite3_blob *pBlob, void *z, int n,
> int iOffset){
>  return blobReadWrite(pBlob, z, n, iOffset, sqlite3BtreeData);
> }
>
>
> If I change the uppercase "void *Z" in the declarations to lowerase
> "void *z" the compilation error disappears.
>
> Is this supposed to be declared like this?
> The other functions declarations (for example "sqlite3_blob_write") are
> all in lowercase.
>
>
> Thanks
>
> Cumprimentos / Best Regards
>
> Filipe Madureira
> -
> SYSDEV, LDA - Mobile Solutions
> (www.sysdevsolutions.com)
> Tel: +351 234188027
> Fax: +351 234188400
> -
>
>
> ___
> 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] sqlite3_blob_read declaration

[Jay A. Kreibich]

On Mon, Sep 14, 2009 at 02:21:51PM +0100, Filipe Madureira scratched on the 
wall:
> Hi,
> 
> I have an error for sometime compiling the latest versions of SQLite 
> amalgamation on eVC++ 4

  SQLite cannot be compiled with a C++ compiler.  You must use a C
  compiler.

   -j

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

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_blob_read declaration

[Filipe Madureira]

Hi,

I have an error for sometime compiling the latest versions of SQLite 
amalgamation on eVC++ 4
error: "'Z' : use of class template requires template argument list"


The problem is in the declaration of "sqlite3_blob_read prototype".
It is declared in "sqlite3.h" and "sqlite3.c" as:

SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int 
iOffset);


and it is implemented in "sqlite3.c" as:

SQLITE_API int sqlite3_blob_read(sqlite3_blob *pBlob, void *z, int n, 
int iOffset){
  return blobReadWrite(pBlob, z, n, iOffset, sqlite3BtreeData);
}


If I change the uppercase "void *Z" in the declarations to lowerase 
"void *z" the compilation error disappears.

Is this supposed to be declared like this?
The other functions declarations (for example "sqlite3_blob_write") are 
all in lowercase.


Thanks

Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234188027
Fax: +351 234188400
- 


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

Re: [sqlite] Slow SELECT query

[wlof]

Thanks Kees. My app still needs some optimizations, your suggestions look
good.

One question though: what will happen when id reaches 2^32 - 1 ? (This app
is going to log a lot of events, 24/7 for a long time, so it's not a purely
theoretical question.)


Kees Nuyt wrote:
> 
> On Fri, 4 Sep 2009 06:43:05 -0700 (PDT), wlof
>  wrote:
> 
>>
>>Hi everyone,
>>
>>I'm using SQLite to maintain a log of events in my application.
>>
>>There is only one table:
>>CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT,
>>timestamp REAL, event TEXT)
>>
>>I don't want the log to grow indefinitely, so I periodically call a method
>>which makes the following query:
>>SELECT id FROM log ORDER BY timestamp DESC LIMIT 1,999
>>
>>A DELETE query is executed on every result.
>>
>>The problem is that, on a 1.3MB table (1 entries), the SELECT query
>>takes 12 seconds to be executed! This is way too long.
>>
>>I've added an index on the timestamp column:
>>CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp)
>>
>>But there is little to no performance improvement.
>>
>>What am I doing wrong?
> 
> Apparently you want to delete all log entries except the
> last 1, without caring about the value of the timestamp.
> 
> Your table already has a monotonically increasing primary
> key (historical order). Maintaining an index on timestamp is
> redundant and slows down inserts.
> 
> I would suggest:
> 
> DELETE FROM log 
>  WHERE id < (
>   SELECT max(id) - 1
>  FROM log
>);
> 
> And if that isn't fast enough, you can even try:
> DELETE FROM log 
>  WHERE id < (
>   SELECT seq - 1
>  FROM sqlite_sequence
> WHERE name = 'log'
>);
> 
> (untested)
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Slow-SELECT-query-tp25294597p25435503.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] get query from sqlite database with triggers

[Sara1409]

Thanks a lot. That was the problem.

Igor Tandetnik wrote:
> 
> Sara1409 wrote:
>> It is clear that those variables (new.contactnom, new.contactage,
>> new.contactmobile, new.contactmail, new.contactadresse) are token as
>> String, I tried many combinations (' + new.contactnom +', ' " +"
>> new.contactnom "+"',:) to resolve this problem but I failed  !
> 
> In SQL, string concatenation opeartor is || (two vertical bars).
> 
> Igor Tandetnik
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/get-query-from-sqlite-database-with-triggers-tp25434506p25434941.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] SQLite3.dll fix

[Pavel Ivanov]

I bet you need to check version of sqlite3.dll in c:\windows\system32
or c:\windows and change it to the new one.

Pavel

On Sun, Sep 13, 2009 at 11:48 AM, Irene & Mickey Demoran
 wrote:
> I am trying to fix a problem with a friend's computer that started 2 days 
> ago. When trying to use Picasa,Google's picture viewer,she get's this error 
> message"Picasa3.exe-Entry Point Not Found. The procedure entry point 
> sqlite3_prepare_v2 could not be located in the dynamic link library 
> SQLite3.dll. It is also affecting ITunes with the same error message. Can 
> anyone help? Mark Rogers.
> ___
> 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] get query from sqlite database with triggers

[Sara1409]

Hello,

I want to get the query excuted on every table in my sqlite data base with
the new values inserted or updated and save the query in another table in
the same database. I try with this code to create the trigger:

CREATE TRIGGER trig_insert_contact_req AFTER INSERT ON contact
BEGIN
INSERT INTO synctable (s_tab,s_req,s_id) VALUES ('contact','INSERT INTO
contact VALUES (new.contactnom, new.contactage, new.contactmobile,
new.contactmail, new.contactadresse);',new._idcontact) ; 
END;

It is clear that those variables (new.contactnom, new.contactage,
new.contactmobile, new.contactmail, new.contactadresse) are token as String,
I tried many combinations (‘ + new.contactnom +’, ‘ " +" new.contactnom
"+"’,…) to resolve this problem but I failed  !

Could someone help me.

Thank you in advance.

-- 
View this message in context: 
http://www.nabble.com/get-query-from-sqlite-database-with-triggers-tp25434506p25434506.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

[sqlite] "make test" was failed

[TEZ]

"make test" became faild. What's wrong?

I tried with sqlite-3.6.17.tar.gz and sqlite-3.6.18.tar.gz, but both packages
failed by running "make test". Environments are Cygwin on WindowsXP SP3 and
VineLinux4.2 (http://www.vinelinux.org/).

Attached files were recorded by "make test >& foo.log" after running
"./configure && make".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3.dll fix

[Irene & Mickey Demoran]

I am trying to fix a problem with a friend's computer that started 2 days ago. 
When trying to use Picasa,Google's picture viewer,she get's this error 
message"Picasa3.exe-Entry Point Not Found. The procedure entry point 
sqlite3_prepare_v2 could not be located in the dynamic link library 
SQLite3.dll. It is also affecting ITunes with the same error message. Can 
anyone help? Mark Rogers.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Undesired query plan when using GROUP BY

[Pavel Ivanov]

> My guess is that
> sqlite isn't built to understand that word_id is unique (i.e. GROUP BY
> is guaranteed to do absolutely nothing in this case).

SQLite is not smart enough to guess these things. And in fact I doubt
that any database is smart enough to guess such things. So I'd suggest
you to change your application to not generate such queries.

Pavel

On Mon, Sep 14, 2009 at 12:57 AM, George Bills  wrote:
> Hi all, I was wondering if someone here could help me with understanding
> / solving an sqlite problem I'm having. Any help would be appreciated.
>
> =SCHEMA=
>
> CREATE TABLE pop_words_wpk (
> word_id INTEGER PRIMARY KEY,
> occurrences INTEGER
> );
> CREATE INDEX pop_words_wpk_oidx ON pop_words_wpk(occurrences);
>
> =QUERY ONE - FAST=
>
> Query: "SELECT word_id, occurrences FROM pop_words_wpk ORDER BY
> occurrences DESC LIMIT 10;"
> Time: 0.005s
>
> Query plan is:
> 0|0|TABLE pop_words_wpk WITH INDEX pop_words_wpk_oidx ORDER BY
>
> =QUERY TWO - SLOW=
>
> Query: "SELECT word_id, SUM(occurrences) FROM pop_words_wpk GROUP
> BY(word_id) ORDER BY SUM(occurrences) DESC LIMIT 10;"
> Time: 7s (1400 times slower)
>
> Query plan is:
> 0|0|TABLE pop_words_wpk USING PRIMARY KEY ORDER BY
>
>
>
> Both of these queries return exactly the same result, but the first
> query is much, much faster.
>
> My assumption is that for the first query, sqlite realizes that it can
> just grab the rows with the 10 highest occurrences in the occurrences
> index. In the second query, it doesn't realize that, and grabs every
> single row, iterating over the word_id PRIMARY KEY. My guess is that
> sqlite isn't built to understand that word_id is unique (i.e. GROUP BY
> is guaranteed to do absolutely nothing in this case).
>
> Is there any way of tricking sqlite into running the same query plan as
> in the first query? I've regenerated my database and ran the queries
> again using the newest sqlite (3.6.18), and that didn't change either of
> the plans. Running ANALYZE didn't improve things either. I also get the
> same behavior when using a unique index instead of a primary key.
>
> I could just change my application so that it understands when it can
> get away without using GROUP BY and SUM (in the real application, there
> are some slightly different tables that might actually need a GROUP BY
> in some circumstances), but it seems a little messy to me, especially if
> someone here knows of a much "neater" solution.
>
> On a slightly related note, the EXPLAIN documentation states that the
> "EXPLAIN QUERY PLAN['s] behavior is undocumented, unspecified, and
> variable" - should I just ignore the numbers shown in the query plan, or
> could they be useful for my understanding of what sqlite is doing?
>
> Thanks,
> George.
> ___
> 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] one liner for insert or update ?

[Jean-Christophe Deschamps]

At 10:25 14/09/2009, you wrote:
´¯¯¯
>sorry, i meant "columns" not rows, whet i do insert command, specifying
>existing id and only some of the columns then the rest of the columns are
>deleted
>on the other hand, UPDATE command won't allow me to insert a new row

Then you can still do something along this:

CREATE TABLE "test" (
   "k" INTEGER PRIMARY KEY ON CONFLICT REPLACE,
   "a" CHAR,
   "b" CHAR);
insert or replace into test (k, a, b) values (234, 'abc', 'uvw');
insert or replace into test (k, a, b) values (123, 'def', 'xyz');

insert or replace into test (k, a, b) values (234, 'won', (select b 
from test where k = 234));

This is a possibility. There might be others.

>there should be a possibility to check id of the last affected row and
>return it to the code
`---

Yes but in a second statement that you have to issue when the key is a 
new one.



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

Re: [sqlite] Question on converting objects into relational db

[CityDev]

Bear in mind a relational database is designed around relations. It's not
obvious what your 'data model' is but there is a suggestion it contains
variable types of things. If you want to store variable things then many of
the features of a relational database don't work.

Often an object maps reasonably to an entity, and an entity shares its
structure with other entities of the same entity type. In this case you can
easily map object attributes to fields in a table. 

Maybe you are storing something more esoteric. You may have to serialize the
object rather than store it as fields.

-- 
View this message in context: 
http://www.nabble.com/Question-on-converting-objects-into-relational-db-tp25385539p25432919.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] one liner for insert or update ?

[yogibabu]

Jean-Christophe Deschamps wrote:
> 
> Do you have a short example where "INSERT OR REPLACE INTO ..." can be 
> shown to actually _delete_ rows?
> Which query are you refering to?

sorry, i meant "columns" not rows, whet i do insert command, specifying
existing id and only some of the columns then the rest of the columns are
deleted
on the other hand, UPDATE command won't allow me to insert a new row



>From what I understand, you might be happy with making your Id column 
> Integer Primary Key (also look at autoincrement feature) then do a 
> standard insert or replace.

i have it precisely like that


Also if SQLite is passed a null Id then it will generate the "next" 
> available rowid for you. But then it's no more a one-liner since your 
> application need to know which Id the newly inserted row has been 
> given, typically with a query like Select 

there should be a possibility to check id of the last affected row and
return it to the code
-- 
View this message in context: 
http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25432143.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] one liner for insert or update ?

[Jean-Christophe Deschamps]

At 01:29 14/09/2009, you wrote:
´¯¯¯
>CREATE TABLE table1( id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
>field1 TEXT
>);
>
>but I think that the table has to already have been created this way.
`---

Yes from what he said, I also believe this is the right and simplest way.



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

Re: [sqlite] one liner for insert or update ?

[Jean-Christophe Deschamps]

I tried twice to post it but it seems it didn't find its way to the 
list. Sorry if ever it gets dupped.


Hi,


>insert or replace deletes all rows that weren't specified in the query

Do you have a short example where "INSERT OR REPLACE INTO ..." can be 
shown to actually _delete_ rows?
Which query are you refering to?


>i don't know maybe in combination with select there's possible to so 
>sth about
>it - like that:
>INSERT OR REPLACE INTO table (id) then SELECT...

INSERT (or update) is a different beast from SELECT.  "Then" is 
unexpected in your statement.


 From what I understand, you might be happy with making your Id column 
Integer Primary Key (also look at autoincrement feature) then do a 
standard insert or replace.  But I can't say if this is suitable for 
your precise situation.

Also if SQLite is passed a null Id then it will generate the "next" 
available rowid for you. But then it's no more a one-liner since your 
application need to know which Id the newly inserted row has been 
given, typically with a query like Select 
sqlite3_last_insert_rowid()
 
.





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

Re: [sqlite] one liner for insert or update ?

[yogibabu]

UIT DEVELOPMENT wrote:
> 
> Are you wanting something like this:
> 
> INSERT INTO 'mytable' (key, 'visitorcount', 'visitdate')
> VALUES (123456789, 1, NOW())
> ON DUPLICATE KEY UPDATE 'visitorcount' = 'visitorcount' + 1
> 
> the key statement being: ON DUPLICATE KEY UPDATE
> 
> not tested but it seems like that is what you are looking for.  Its
> sometimes refered to "upsert" processing...
> 

sqlite error 1 - near "ON": syntax error

-- 
View this message in context: 
http://www.nabble.com/one-liner-for-insert-or-update---tp25416164p25431264.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] First "SELECT" query fails

[Dan Kennedy]

On Sep 14, 2009, at 1:35 PM,  wrote:

> Hi,
> Any help to the below problem is of great help.

Please check if this happens with 3.6.18. I think these
issues may be fixed now.

Dan.



> Regards,
> Reddy
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org 
> ] On Behalf Of Kotapalle Purushotham (Nokia-D/Bangalore)
> Sent: Tuesday, September 08, 2009 12:57 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] First "SELECT" query fails
>
> Hi,
> I am using SqLite DB as a message Queue for data share between 2  
> process.
> I create a table in one process(say A) and then subcribe another  
> process(say B) to extract data from the table everytime entry is  
> added.
> Creating the table and subcribing is successful.
> However, there is a problem when trying to extract the an entry for  
> the very first time form the table. Complete problem is as described  
> below:
>
> - process A creates the table T, process B subcribes to entry  
> events(like add) of table T.
> - process A adds an entry to table T, using "INSERT INTO..." command
> - process B catches this event and tries to extract the data from  
> table T, using "SELECT *..." query. But the command fails with  
> following error :
>"no such table: responseQ Unable to execute statement"
> - however, subsequent entries added by process A are handled  
> successfully by process B.
>
> Since, I am simulating a "message queue" model, with this issue, one  
> of the messages will remain in the queue(table) unused.
> I tried to execute commit db and finish the sql query after creating  
> table, but was not helpful to solve this issue.
>
> Pls help me to find a solution to this problem.
>
> Regards,
> Reddy
>
>
> ___
> 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] First "SELECT" query fails

[purushotham.kotapalle]

Hi,
Any help to the below problem is of great help.


Regards,
Reddy

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Kotapalle Purushotham (Nokia-D/Bangalore)
Sent: Tuesday, September 08, 2009 12:57 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] First "SELECT" query fails

Hi,
I am using SqLite DB as a message Queue for data share between 2 process.
I create a table in one process(say A) and then subcribe another process(say B) 
to extract data from the table everytime entry is added.
Creating the table and subcribing is successful.
However, there is a problem when trying to extract the an entry for the very 
first time form the table. Complete problem is as described below:

 - process A creates the table T, process B subcribes to entry events(like add) 
of table T.
 - process A adds an entry to table T, using "INSERT INTO..." command
 - process B catches this event and tries to extract the data from table T, 
using "SELECT *..." query. But the command fails with following error :
"no such table: responseQ Unable to execute statement"
 - however, subsequent entries added by process A are handled successfully by 
process B.

Since, I am simulating a "message queue" model, with this issue, one of the 
messages will remain in the queue(table) unused.
I tried to execute commit db and finish the sql query after creating table, but 
was not helpful to solve this issue.

Pls help me to find a solution to this problem.

Regards,
Reddy


___
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