Re: [sqlite] The database disk image is malformed on windows mobile system.

[Zhonghuifeng163]

At 2010-09-25，"Simon Slavin"  wrote:

>
>On 25 Sep 2010, at 5:17am, Zhonghuifeng163 wrote:
>
>> At 2010-09-25，"Simon Slavin"  wrote:
>> 
>>> On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote:
>>> 
 I use sqlite 3.6.20 on windows mobile system,but the sqlite often return 
 error message "The database disk image is malformed", then the sqlite 
 database file can not be used! I also use sqlite 3.6.20 on other operate 
 system on mobile phone, and do not have this problem.
>>> 
>>> When it does this, what does the integrity check say ?
>> 
>> *** in database main ***
>> 
>> On tree page 766 cell 64: 2nd reference to page 764
>> 
>> On tree page 766 cell 64: Child page depth differs
>> 
>> On page 12 at right child: 2nd reference to page 770
>
>Stop using a corrupt database file.  Start again from one which gives no 
>errors when you integrity check it.  Check this new one frequently to see if 
>the things you do create errors in this one too.
>
I have already done this, and the error occurs again. I want to know how it 
happened? What is the primary reason that occurs the error?

This error occured frequently when the phone broken down.
>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] The database disk image is malformed on windows mobile system.

[Simon Slavin]

On 25 Sep 2010, at 5:17am, Zhonghuifeng163 wrote:

> At 2010-09-25，"Simon Slavin"  wrote:
> 
>> On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote:
>> 
>>> I use sqlite 3.6.20 on windows mobile system,but the sqlite often return 
>>> error message "The database disk image is malformed", then the sqlite 
>>> database file can not be used! I also use sqlite 3.6.20 on other operate 
>>> system on mobile phone, and do not have this problem.
>> 
>> When it does this, what does the integrity check say ?
> 
> *** in database main ***
> 
> On tree page 766 cell 64: 2nd reference to page 764
> 
> On tree page 766 cell 64: Child page depth differs
> 
> On page 12 at right child: 2nd reference to page 770

Stop using a corrupt database file.  Start again from one which gives no errors 
when you integrity check it.  Check this new one frequently to see if the 
things you do create errors in this one too.

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

Re: [sqlite] The database disk image is malformed on windows mobile system.

[Zhonghuifeng163]

At 2010-09-25，"Simon Slavin"  wrote:

>
>On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote:
>
>> I use sqlite 3.6.20 on windows mobile system,but the sqlite often return 
>> error message "The database disk image is malformed", then the sqlite 
>> database file can not be used! I also use sqlite 3.6.20 on other operate 
>> system on mobile phone, and do not have this problem.
>
>When it does this, what does the integrity check say ?
>
>Simon.

*** in database main ***

On tree page 766 cell 64: 2nd reference to page 764

On tree page 766 cell 64: Child page depth differs

On page 12 at right child: 2nd reference to page 770


>___
>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] The database disk image is malformed on windows mobile system.

[Simon Slavin]

On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote:

> I use sqlite 3.6.20 on windows mobile system,but the sqlite often return 
> error message "The database disk image is malformed", then the sqlite 
> database file can not be used! I also use sqlite 3.6.20 on other operate 
> system on mobile phone, and do not have this problem.

When it does this, what does the integrity check say ?

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

[sqlite] The database disk image is malformed on windows mobile system.

[Zhonghuifeng163]

I use sqlite 3.6.20 on windows mobile system,but the sqlite often return error 
message "The database disk image is malformed", then the sqlite database file 
can not be used! I also use sqlite 3.6.20 on other operate system on mobile 
phone, and do not have this problem.

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

Re: [sqlite] FTS3 MATCH syntax

[Richard Hipp]

On Fri, Sep 24, 2010 at 7:16 PM, GHCS Software wrote:

>  I'm just started with generating a table using FTS3 and understand
> most of the query formats, but am stuck on one minor point of syntax. My
> FTS3 query will make reference to a specific table, e.g.:
>
> MATCH 'surname:smith'
>
> The problem is that I can't figure out how to specify it if the string
> to be searched for has a space in it. I've tried several varieties of
> quotes without any success, e.g.:
>
> MATCH '"given:john q"'
> MATCH 'given:"john q"'
>
> and so on. Is there a way to get this to work?
>


SELECT * FROM fts3table WHERE surname MATCH "john q";



>
> --
> Doug Gordon
> *GHCS Software*
> http://www.ghcssoftware.com
> g...@ghcssoftware.com 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] FTS3 MATCH syntax

[Sam Roberts]

I'm not an authority, but I've been using FTS3.

FTS3 tokenizes strings on whitespace (and other chars), so I think the
best you can do would be something
like given:john given:q.

It doesn't work really well out of the box for substring matching.


On Fri, Sep 24, 2010 at 4:16 PM, GHCS Software  wrote:
>  I'm just started with generating a table using FTS3 and understand
> most of the query formats, but am stuck on one minor point of syntax. My
> FTS3 query will make reference to a specific table, e.g.:
>
>     MATCH 'surname:smith'
>
> The problem is that I can't figure out how to specify it if the string
> to be searched for has a space in it. I've tried several varieties of
> quotes without any success, e.g.:
>
>     MATCH '"given:john q"'
>     MATCH 'given:"john q"'
>
> and so on. Is there a way to get this to work?
>
> --
> Doug Gordon
> *GHCS Software*
> http://www.ghcssoftware.com
> g...@ghcssoftware.com 
>
> ___
> 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] Need help with self-join (I think)

[Oliver Peters]

Am 25.09.2010 01:47, schrieb Kristoffer Danielsson:

[...]

>
> Because, given a certain algorithm, generating statistics will become a lot 
> easier if each value combination is represented in the returned row set.
>

really? NULL means there are no values present or there are unknown 
values - statistics with NULL should be without consequences

if you think you should calculate instead of NULL with 0 (the number) it 
still isn't correct (try it with an average, ie sales figures: you 
assume that NULL = 0 but that assumption is a mistake)


[...]

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Yeah. USING is good to have :)

 

And yes, 123 might very well be a random number. I should have made that clear!
 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:19:44 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson  writes:
> 
> > 
> > 
> > Ah, this one's easier to follow. What do you mean by "in this case"? What 
> > was
> the condition that made it
> 
> the point was not to remove something but to take into consideration that 
> there
> has to be a JOIN on the TestIDs (in this case results doesn't differ because 
> you
> only have 123 as SomeValue) - and because it's too late/early here is my 
> assumed
> last correction (see the USING I forgot, if you forget this you will get a
> Cartesian Product)
> 
> SELECT DISTINCT t.Year, b.Name,
> (SELECT SomeValue FROM Test INNER JOIN Test2 USING(TestID) WHERE Year = t.Year
> AND Name =
> b.Name) AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> 
> 
> 
> > possible to remove the rest?
> > 
> > Again, thanks!
> > 
> > 
> > > To: sqlite-us...@...
> > > From: oliver@...
> > > Date: Sat, 25 Sep 2010 00:05:11 +
> > > Subject: Re: [sqlite] Need help with self-join (I think)
> > > 
> > > sry,
> > > 
> > > a little mistake - here's the correction:
> > > 
> > > SELECT DISTINCT t.Year, b.Name,
> > > (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name 
> > > =
> > > b.Name) AS SomeValue
> > > FROM Test t
> > > CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> > > ;
> > > 
> > > 
> > > (results don't differ - in this case!)
> > > 
> > > Oliver
> > > 
> > > ___
> > > sqlite-users mailing list
> > > sqlite-us...@...
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-us...@...
> > 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] Need help with self-join (I think)

[Oliver Peters]

Kristoffer Danielsson  writes:

> 
> 
> Ah, this one's easier to follow. What do you mean by "in this case"? What was
the condition that made it

the point was not to remove something but to take into consideration that there
has to be a JOIN on the TestIDs (in this case results doesn't differ because you
only have 123 as SomeValue) - and because it's too late/early here is my assumed
last correction (see the USING I forgot, if you forget this you will get a
Cartesian Product)

SELECT DISTINCT t.Year, b.Name,
(SELECT SomeValue FROM Test INNER JOIN Test2 USING(TestID) WHERE Year = t.Year
AND Name =
b.Name) AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;




> possible to remove the rest?
> 
> Again, thanks!
> 
>  
> > To: sqlite-us...@...
> > From: oliver@...
> > Date: Sat, 25 Sep 2010 00:05:11 +
> > Subject: Re: [sqlite] Need help with self-join (I think)
> > 
> > sry,
> > 
> > a little mistake - here's the correction:
> > 
> > SELECT DISTINCT t.Year, b.Name,
> > (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> > b.Name) AS SomeValue
> > FROM Test t
> > CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> > ;
> > 
> > 
> > (results don't differ - in this case!)
> > 
> > Oliver
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-us...@...
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-us...@...
> 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] Need help with self-join (I think)

[Kristoffer Danielsson]

Ah, this one's easier to follow. What do you mean by "in this case"? What was 
the condition that made it possible to remove the rest?


Again, thanks!

 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:05:11 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> sry,
> 
> a little mistake - here's the correction:
> 
> SELECT DISTINCT t.Year, b.Name,
> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> b.Name) AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> 
> (results don't differ - in this case!)
> 
> Oliver
> 
> ___
> 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] Need help with self-join (I think)

[Oliver Peters]

sry,

a little mistake - here's the correction:

SELECT DISTINCT t.Year, b.Name,
(SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
b.Name) AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;


(results don't differ - in this case!)

Oliver

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Neat! Many thanks :)

 

Putting this logic together with my original query will be an interesting 
challenge, hehe. Do you believe this is the best solution?

 

Chris
 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Fri, 24 Sep 2010 23:47:59 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson  writes:
> 
> SELECT DISTINCT t.Year, b.Name,
> CASE
> WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
> THEN NULL
> ELSE (SELECT SomeValue FROM Test2)
> END AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> Oliver
> 
> ___
> 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] Need help with self-join (I think)

[Oliver Peters]

Kristoffer Danielsson  writes:

SELECT DISTINCT t.Year, b.Name,
CASE
WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
THEN NULL
ELSE (SELECT SomeValue FROM Test2)
END AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;

Oliver

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

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Hi,

 

Thanks for your reply. You're right, it's an outer-join I'm looking for. 
Unfortunately, your suggestion does not do the trick.

 

"You didn't have a Test1 row for (2007, 'C'), so why would you get 2007|C|NULL?"

Because, given a certain algorithm, generating statistics will become a lot 
easier if each value combination is represented in the returned row set.

 

Perhaps a UNION is needed for this type of query?

 

> Date: Fri, 24 Sep 2010 18:17:51 -0500
> From: nicolas.willi...@oracle.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
> > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT 
> > NOT NULL);
> > INSERT INTO Test (Year, Name) VALUES (2007, 'A');
> > INSERT INTO Test (Year, Name) VALUES (2007, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2008, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'A');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'C');
> > CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);
> > 
> > [...]
> > My problem is that I want each combination of Year+Name to be represented - 
> > with zero/NULL for SomeValue. How do I accomplish this?
> > 
> > 2007|A|123
> > 2007|B|123
> > 2007|C|NULL
> 
> You didn't have a Test1 row for (2007, 'C'), so why would you get
> 2007|C|NULL? You also had one (and just one) row in Test2 for every
> TestID in Test, so there are no NULLs that could appear as you request.
> 
> But, if you did:
> 
> INSERT INTO Test (Year, Name) VALUES (2007, 'C');
> 
> without a corresponding row in Test2, then your SELECT would not return
> 2007|C|NULL. Try this:
> 
> SELECT Year, Name, SomeValue
> FROM Test LEFT OUTER JOIN Test2 ON (Test.TestID = Test2.TestID)
> ORDER BY Year, Name;
> 
> (Self-join is when both sides of the JOIN use the same table. That's
> not the case here. What you were looking for here is an OUTER JOIN
> instead of an INNER JOIN; NATURAL JOINs are a type of INNER JOIN.)
> 
> Nico
> -- 
> ___
> 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] Need help with self-join (I think)

[Nicolas Williams]

On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
> CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT 
> NOT NULL);
> INSERT INTO Test (Year, Name) VALUES (2007, 'A');
> INSERT INTO Test (Year, Name) VALUES (2007, 'B');
> INSERT INTO Test (Year, Name) VALUES (2008, 'B');
> INSERT INTO Test (Year, Name) VALUES (2009, 'A');
> INSERT INTO Test (Year, Name) VALUES (2009, 'B');
> INSERT INTO Test (Year, Name) VALUES (2009, 'C');
> CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
> INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);
>  
> [...]
> My problem is that I want each combination of Year+Name to be represented - 
> with zero/NULL for SomeValue. How do I accomplish this?
> 
> 2007|A|123
> 2007|B|123
> 2007|C|NULL

You didn't have a Test1 row for (2007, 'C'), so why would you get
2007|C|NULL?  You also had one (and just one) row in Test2 for every
TestID in Test, so there are no NULLs that could appear as you request.

But, if you did:

INSERT INTO Test (Year, Name) VALUES (2007, 'C');

without a corresponding row in Test2, then your SELECT would not return
2007|C|NULL.  Try this:

SELECT Year, Name, SomeValue
FROM Test LEFT OUTER JOIN Test2 ON (Test.TestID = Test2.TestID)
ORDER BY Year, Name;

(Self-join is when both sides of the JOIN use the same table.  That's
not the case here.  What you were looking for here is an OUTER JOIN
instead of an INNER JOIN; NATURAL JOINs are a type of INNER JOIN.)

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

[sqlite] FTS3 MATCH syntax

[GHCS Software]

  I'm just started with generating a table using FTS3 and understand 
most of the query formats, but am stuck on one minor point of syntax. My 
FTS3 query will make reference to a specific table, e.g.:

 MATCH 'surname:smith'

The problem is that I can't figure out how to specify it if the string 
to be searched for has a space in it. I've tried several varieties of 
quotes without any success, e.g.:

 MATCH '"given:john q"'
 MATCH 'given:"john q"'

and so on. Is there a way to get this to work?

-- 
Doug Gordon
*GHCS Software*
http://www.ghcssoftware.com
g...@ghcssoftware.com 

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

[sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Hi,

 

I have an interesting SQL problem where I want certain rows to always be 
represented. It feels like a self-join, but I'm not sure. Please help!

 

Create a database as follows:

 



CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT NOT 
NULL);

INSERT INTO Test (Year, Name) VALUES (2007, 'A');
INSERT INTO Test (Year, Name) VALUES (2007, 'B');
INSERT INTO Test (Year, Name) VALUES (2008, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'A');
INSERT INTO Test (Year, Name) VALUES (2009, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'C');

 

CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);

INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);



 

SELECT Year, Name, SomeValue FROM Test NATURAL JOIN Test2 ORDER BY Year, Name;

 

2007|A|123
2007|B|123
2008|B|123
2009|A|123
2009|B|123
2009|C|123

 

My problem is that I want each combination of Year+Name to be represented - 
with zero/NULL for SomeValue. How do I accomplish this?

 

2007|A|123
2007|B|123
2007|C|NULL
2008|A|NULL
2008|B|123
2008|C|NULL
2009|A|123
2009|B|123
2009|C|123

 

If there is an "easy" solution it would be great, as the original query is 
quite complex... :P

 

Thank you for your help!
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query to compare two sqlite databases

[Oliver Peters]

Oliver Peters  writes:

sry
 

> 
> the result is what is not in table01
> 

I meant:
the result is what is in table01 but NOT in table02

Oliver


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

Re: [sqlite] Query to compare two sqlite databases

[Oliver Peters]

Am 24.09.2010 20:41, schrieb John Reed:
> Hello,
>
> I compare an application every few days which has sqlite as it's client 
> database.
>   I look at the content and check whether documents have made it into the 
> application after it has been built. I also check the metadata in the sqlite 
> client database for changes.
> So, I am constantly comparing the last database with the newer database.
> Both databases have exactly the same tables, with only the data being changed 
> in most of the 51 tables.The largest table has about 3,700,000 rows. Most 
> other tables have much less rows in them.
> Could someone suggest an sql query to find the difference in the same table 
> (ta) for both the last database (db1) and the newer database (db2)?
> I can use SQLiteSpy to connect and attach to the databases.
>
use EXCEPT


SELECT col01, col02,...
FROM table01
EXCEPT
SELECT col01, col02,...
FROM table02
;

table01 is the newest table while table02 is its predecessor

the result is what is not in table01

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

Re: [sqlite] Query to compare two sqlite databases

[John Reed]

Hello,

Thanks for your response.
I'm almost there but I keep getting sql syntax error (near col1) with the last 
part of the query 
where(not exists in db2);

Here's where I'm at:

select 'db1', db1.table1.col1, 'db2', db2.table2.col1 from db1.table1, 
db2.table2 WHERE (NOT EXISTS col1 IN db2);


Thanks.

--- On Fri, 9/24/10, Rich Shepard  wrote:

From: Rich Shepard 
Subject: Re: [sqlite] Query to compare two sqlite databases
To: "General Discussion of SQLite Database" 
Date: Friday, September 24, 2010, 12:51 PM

On Fri, 24 Sep 2010, luuk34 wrote:

> you mean something like:
> select  id1, id2 from callprog a where id1 not in (select b.id1 FROM
> callprog b where b.id1=a.id1 );

   Yeah; much better.

> But what is there is more than 1 column? it will grow in complexity when
> you have a lot of columns.

   My understanding of the original problem was that rows were about the
same, so finding a common column in db1 which was not in db2 was the need.
As long as there is one unique column common to both tables it makes no
difference how many other columns there are in each one.

Rich
___
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 to compare two sqlite databases

[Rich Shepard]

On Fri, 24 Sep 2010, luuk34 wrote:

> you mean something like:
> select  id1, id2 from callprog a where id1 not in (select b.id1 FROM
> callprog b where b.id1=a.id1 );

   Yeah; much better.

> But what is there is more than 1 column? it will grow in complexity when
> you have a lot of columns.

   My understanding of the original problem was that rows were about the
same, so finding a common column in db1 which was not in db2 was the need.
As long as there is one unique column common to both tables it makes no
difference how many other columns there are in each one.

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

Re: [sqlite] Query to compare two sqlite databases

[luuk34]

  On 24-09-10 21:06, Rich Shepard wrote:
> On Fri, 24 Sep 2010, John Reed wrote:
>
>> I compare an application every few days which has sqlite as it's client
>> database. I look at the content and check whether documents have made it
>> into the application after it has been built. I also check the metadata in
>> the sqlite client database for changes. So, I am constantly comparing the
>> last database with the newer database. Both databases have exactly the
>> same tables, with only the data being changed in most of the 51 tables.The
>> largest table has about 3,700,000 rows. Most other tables have much less
>> rows in them. Could someone suggest an sql query to find the difference in
>> the same table (ta) for both the last database (db1) and the newer
>> database (db2)? I can use SQLiteSpy to connect and attach to the
>> databases.
> You'll want to tune the syntax, but try something like:
>
> SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2);
>
> The idea is to match rows in each table and where the equivalent row in db1
> is not in db2, add that to the results table.
>
> Rich
you mean something like:
select  id1, id2 from callprog a where id1 not in (select b.id1 FROM 
callprog b where b.id1=a.id1 );

But what is there is more than 1 column?
it will grow in complexity when you have a lot of columns.

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

Re: [sqlite] Query to compare two sqlite databases

[Rich Shepard]

On Fri, 24 Sep 2010, John Reed wrote:

> I compare an application every few days which has sqlite as it's client
> database. I look at the content and check whether documents have made it
> into the application after it has been built. I also check the metadata in
> the sqlite client database for changes. So, I am constantly comparing the
> last database with the newer database. Both databases have exactly the
> same tables, with only the data being changed in most of the 51 tables.The
> largest table has about 3,700,000 rows. Most other tables have much less
> rows in them. Could someone suggest an sql query to find the difference in
> the same table (ta) for both the last database (db1) and the newer
> database (db2)? I can use SQLiteSpy to connect and attach to the
> databases.

   You'll want to tune the syntax, but try something like:

   SELECT colA FROM db1 WHERE (NOT EXIST colA IN db2);

The idea is to match rows in each table and where the equivalent row in db1
is not in db2, add that to the results table.

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

[sqlite] Query to compare two sqlite databases

[John Reed]

Hello,

I compare an application every few days which has sqlite as it's client 
database.
 I look at the content and check whether documents have made it into the 
application after it has been built. I also check the metadata in the sqlite 
client database for changes.
So, I am constantly comparing the last database with the newer database.
Both databases have exactly the same tables, with only the data being changed 
in most of the 51 tables.The largest table has about 3,700,000 rows. Most other 
tables have much less rows in them.
Could someone suggest an sql query to find the difference in the same table 
(ta) for both the last database (db1) and the newer database (db2)?
I can use SQLiteSpy to connect and attach to the databases.


Thanks for any help to point me in the direction.





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

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

[Richard Hipp]

On Fri, Sep 24, 2010 at 9:50 AM, Pavel Ivanov  wrote:

> Apparently you are calling sqlite3_free twice on the same statement
> pointer. Try to add assigning to NULL after freeing and checking for
> NULL before freeing.
>

The second step (checking for NULL before freeing) is unnecessary.  Calling
sqlite3_free() on a NULL pointer is defined to be a safe and harmless no-op.



>
>
> Pavel
>
> On Thu, Sep 23, 2010 at 10:57 AM, Borra, Kishore Babu
>  wrote:
> > Hi,
> >
> > I require some help in getting the fix for some memory corruption issues,
> occurring while using the sqlite3 library. It would be very helpful, if you
> can guide to fix the below issues or atleast provide some info on this, to
> minimize the memory corruption, occurring mostly while sqlite3_free or doing
>  some query.
> >
> > Thanks,
> > Kishore
> >
> > The below backtraces observed for crash are-
> >
> > 1. #0  0x0feb1a98 in raise () from /lib/libc.so.6
> > #1  0x0feb3418 in abort () from /lib/libc.so.6
> > #2  0x0fee847c in __libc_message () from /lib/libc.so.6
> > #3  0x0feef5ec in malloc_printerr () from /lib/libc.so.6
> > #4  0x0fef0244 in free () from /lib/libc.so.6
> > #5  0x0fc1f0d8 in dbi_result_free () from /usr/local/lib/libdbi.so.0
> > #6  0x0fc97d00 in FreeDBIResult (result=0x1037ec88) at dbInterface.c:1008
> > #7  0x1003ebc4 in FillDartInfo (nodeType=975170328, HmpDataLocalCopy=0x2)
> at configMgrPollDartGnrl.c:424
> > #8  0x10037820 in ConfigManagerPollerThread () at configManager.c:490
> > #9  0x0fd35d38 in start_thread () from /lib/libpthread.so.0
> > #10 0x0ff45f44 in clone () from /lib/libc.so.6
> > Backtrace stopped: previous frame inner to this frame (corrupt stack?)
> >
> >
> > 2. *** glibc detected *** agent/.libs/lt-snmpd: corrupted double-linked
> list: 0x08310c18 ***
> > === Backtrace: =
> > /lib/i686/cmov/libc.so.6[0xb7af9624]
> > /lib/i686/cmov/libc.so.6[0xb7afb557]
> > /lib/i686/cmov/libc.so.6(cfree+0x96)[0xb7afb826]
> > /usr/local/lib/libsqlite3.so.0[0xb788a330]
> > /usr/local/lib/libsqlite3.so.0(sqlite3_free+0x69)[0xb78547b9]
> > /usr/local/lib/libsqlite3.so.0[0xb785628a]
> > /usr/local/lib/libsqlite3.so.0[0xb7856539]
> > /usr/local/lib/libsqlite3.so.0[0xb785677c]
> > /usr/local/lib/libsqlite3.so.0[0xb7862613]
> > /usr/local/lib/libsqlite3.so.0[0xb7862648]
> > /usr/local/lib/libsqlite3.so.0[0xb7875a8a]
> > /usr/local/lib/libsqlite3.so.0[0xb7875d24]
> > /usr/local/lib/libsqlite3.so.0[0xb787a22a]
> > /usr/local/lib/libsqlite3.so.0[0xb789c3ba]
> > /usr/local/lib/libsqlite3.so.0(sqlite3_step+0x57)[0xb78907c7]
> > /usr/local/lib/libsqlite3.so.0(sqlite3_exec+0xf7)[0xb7890bd7]
> > /usr/local/lib/libsqlite3.so.0(sqlite3_get_table+0xd8)[0xb7890f68]
> > /usr/local/lib/dbd//libdbdsqlite3.so(dbd_query+0x45)[0xb792b215]
> > /usr/lib/libdbi.so.0(dbi_conn_query+0x4b)[0xb79123fb]
> >
> /home/kishore/prism_N5Dev7.0/Dev7.0/libs/dbInterface/installable/usr/local/lib/libdbInterface.so.1(ExcecuteSelectQuery+0x17f)[0xb793531f]
> > /home/kishore/prism_N5Dev7
> >
> >
> > Sqlite version details are as follows:-
> >
> > Sqlite3 Library using are Sqlite-3.6.23.1
> > Library - libsqlite3.so.0.8.6
> >
> > Using libdbi as wrappers-
> > libdbi drivers - 0.8.2.1 version. On top of sq1lite3.
> > Libdbi -0.8.3 version
> >
> >
> > ___
> > 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
>



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

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

[Dan Kennedy]

On Sep 23, 2010, at 9:57 PM, Borra, Kishore Babu wrote:

> Hi,
>
> I require some help in getting the fix for some memory corruption  
> issues, occurring while using the sqlite3 library. It would be very  
> helpful, if you can guide to fix the below issues or atleast provide  
> some info on this, to minimize the memory corruption, occurring  
> mostly while sqlite3_free or doing  some query.

Run your app under valgrind: http://www.valgrind.org/


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

Re: [sqlite] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

[Pavel Ivanov]

Apparently you are calling sqlite3_free twice on the same statement
pointer. Try to add assigning to NULL after freeing and checking for
NULL before freeing.


Pavel

On Thu, Sep 23, 2010 at 10:57 AM, Borra, Kishore Babu
 wrote:
> Hi,
>
> I require some help in getting the fix for some memory corruption issues, 
> occurring while using the sqlite3 library. It would be very helpful, if you 
> can guide to fix the below issues or atleast provide some info on this, to 
> minimize the memory corruption, occurring mostly while sqlite3_free or doing  
> some query.
>
> Thanks,
> Kishore
>
> The below backtraces observed for crash are-
>
> 1. #0  0x0feb1a98 in raise () from /lib/libc.so.6
> #1  0x0feb3418 in abort () from /lib/libc.so.6
> #2  0x0fee847c in __libc_message () from /lib/libc.so.6
> #3  0x0feef5ec in malloc_printerr () from /lib/libc.so.6
> #4  0x0fef0244 in free () from /lib/libc.so.6
> #5  0x0fc1f0d8 in dbi_result_free () from /usr/local/lib/libdbi.so.0
> #6  0x0fc97d00 in FreeDBIResult (result=0x1037ec88) at dbInterface.c:1008
> #7  0x1003ebc4 in FillDartInfo (nodeType=975170328, HmpDataLocalCopy=0x2) at 
> configMgrPollDartGnrl.c:424
> #8  0x10037820 in ConfigManagerPollerThread () at configManager.c:490
> #9  0x0fd35d38 in start_thread () from /lib/libpthread.so.0
> #10 0x0ff45f44 in clone () from /lib/libc.so.6
> Backtrace stopped: previous frame inner to this frame (corrupt stack?)
>
>
> 2. *** glibc detected *** agent/.libs/lt-snmpd: corrupted double-linked list: 
> 0x08310c18 ***
> === Backtrace: =
> /lib/i686/cmov/libc.so.6[0xb7af9624]
> /lib/i686/cmov/libc.so.6[0xb7afb557]
> /lib/i686/cmov/libc.so.6(cfree+0x96)[0xb7afb826]
> /usr/local/lib/libsqlite3.so.0[0xb788a330]
> /usr/local/lib/libsqlite3.so.0(sqlite3_free+0x69)[0xb78547b9]
> /usr/local/lib/libsqlite3.so.0[0xb785628a]
> /usr/local/lib/libsqlite3.so.0[0xb7856539]
> /usr/local/lib/libsqlite3.so.0[0xb785677c]
> /usr/local/lib/libsqlite3.so.0[0xb7862613]
> /usr/local/lib/libsqlite3.so.0[0xb7862648]
> /usr/local/lib/libsqlite3.so.0[0xb7875a8a]
> /usr/local/lib/libsqlite3.so.0[0xb7875d24]
> /usr/local/lib/libsqlite3.so.0[0xb787a22a]
> /usr/local/lib/libsqlite3.so.0[0xb789c3ba]
> /usr/local/lib/libsqlite3.so.0(sqlite3_step+0x57)[0xb78907c7]
> /usr/local/lib/libsqlite3.so.0(sqlite3_exec+0xf7)[0xb7890bd7]
> /usr/local/lib/libsqlite3.so.0(sqlite3_get_table+0xd8)[0xb7890f68]
> /usr/local/lib/dbd//libdbdsqlite3.so(dbd_query+0x45)[0xb792b215]
> /usr/lib/libdbi.so.0(dbi_conn_query+0x4b)[0xb79123fb]
> /home/kishore/prism_N5Dev7.0/Dev7.0/libs/dbInterface/installable/usr/local/lib/libdbInterface.so.1(ExcecuteSelectQuery+0x17f)[0xb793531f]
> /home/kishore/prism_N5Dev7
>
>
> Sqlite version details are as follows:-
>
> Sqlite3 Library using are Sqlite-3.6.23.1
> Library - libsqlite3.so.0.8.6
>
> Using libdbi as wrappers-
> libdbi drivers - 0.8.2.1 version. On top of sq1lite3.
> Libdbi -0.8.3 version
>
>
> ___
> 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] Bug? "no such column" referring to a table by name, works by alias

[Drake Wilson]

Quoth Igor Tandetnik , on 2010-09-24 08:16:07 -0400:
> sqlite> create table A (id integer primary key);
> sqlite> create table B (id integer primary key, name text);
> sqlite> select * from A as X order by (select name from B where B.id = A.id);
> Error: no such column: A.id
> sqlite> select * from A as X order by (select name from B where B.id = X.id);
> sqlite>
> 
> One would expect that table name and alias should work
> interchangeably except when the name is ambiguous (which is not the
> case here).

I wouldn't, actually.  I would expect the table alias to "overwrite"
the original table name as the source name; essentially a table named
"t" when used in a SELECT statement generates a source named "t" by
default, and "t AS s" or "t s" generates a source named "s" instead.
Tables are not themselves sources in the strictest sense; a source
references a table (and "is" the table for many purposes).

http://archives.postgresql.org/pgsql-general/2000-08/msg00740.php
claims that SQL92 requires this behavior.

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

[sqlite] Bug? "no such column" referring to a table by name, works by alias

[Igor Tandetnik]

Consider this SQLite session:

sqlite> create table A (id integer primary key);
sqlite> create table B (id integer primary key, name text);
sqlite> select * from A as X order by (select name from B where B.id = A.id);
Error: no such column: A.id
sqlite> select * from A as X order by (select name from B where B.id = X.id);
sqlite>

One would expect that table name and alias should work interchangeably except 
when the name is ambiguous (which is not the case here). Looks like a bug to me 
(though, admittedly, it's rather a corner case, and there's an easy workaround).
-- 
Igor Tandetnik

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

[sqlite] Auto Reply: sqlite-users Digest, Vol 33, Issue 24

[alexander . gorrod]

This is an auto-replied message. I am currently unavailable. I will be back at 
work on Monday 27th September. For urgent questions please contact Michael Brey 
(michael.b...@oracle.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DELETE OR IGNORE statement?

[Andy Gibbs]

On Friday, September 24, 2010 1:03 PM, Josh Gibbs wrote:

>  What's the chance that 2 people with the same surname would have
> the same problem in the same week...
>
> [ ... ]

I can only think its something in the name!

I did actually see your thread moments after creating my own.  I'd done a 
search on "DELETE OR IGNORE" without finding yours, so that'll teach me not 
to check more thoroughly!!! ;o)

I did some further tests though and found that even if a DELETE OR IGNORE 
existed it probably would not do what I would have expected it to do, since 
foreign key constraints cause an ABORT regardless of the conflict resolution 
given in the statement.  I found that an INSERT/UPDATE OR IGNORE will also 
abort with an error if the foreign key constraints fail, rather than just 
not performing the action.  Maybe my understanding/intuition of the "OR 
IGNORE" clause should do is somewhat adrift of what it should be.

Cheers
Andy


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

Re: [sqlite] DELETE OR IGNORE statement?

[Josh Gibbs]

  What's the chance that 2 people with the same surname would have
the same problem in the same week...

I believe I just solved the same problem you are asking about yesterday
thanks to a query from Richard:

CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT,
>>>  Subject TEXT);
>>>  CREATE TABLE MessageRecipient (message_id INTEGER REFERENCES
>>>  Message(message_id) ON DELETE CASCADE, recipient_ID INTEGER REFERENCES
>>>  Recipient(recipient_id));
>>>  CREATE TABLE Recipient (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT,
>>>  name);
>>>
>>>  I've tried creating a trigger after delete on MessageRecipient to remove

>>  the

> >>>  referenced Recipient, and this works if it's the only related item,
> >>>  however any
> >>>  other MessageRecipient relationship causes the delete to fail.  As there
> >>>  is no
> >>>  'or ignore' for the delete statement, I can't get this to keep my data
> >>>  clean.
>
>  DELETE FROM recipient
> WHERE recipient_id = old.recipient_id
>   AND NOT EXISTS(SELECT 1 FROM message_recipient
>   WHERE recipient.recipient_id=
> message_recipient.recipient_id);
>


That SQL statement (with minor corrections) works within and AFTER DELETE
trigger.  The key references prevent deletion, and the trigger does the
cleanup when only one item is left over.

I struggled the same way you did at first thinking there would be a
DELETE OR IGNORE clause.

Hope this helps.

Josh




On 24/09/2010 1:59 a.m., Andy Gibbs wrote:
> On Thursday, September 23, 2010 3:36 PM, Simon Slavin wrote:
>
>> On 23 Sep 2010, at 2:13pm, Andy Gibbs wrote:
>>
>>> I've got a table with a primary key and then any number of additional
>>> tables
>>> with foreign keys that reference this primary key table with "ON DELETE
>>> RESTRICT"
>> I always worry when I see descriptions like this.  Those additional
>> tables: do they all have the same columns ?  If so, can you amagamate
>> them all into one big table ?  Just insert one extra column saying what
>> kind of row this row is.
>>
>> Not only does this fix the problem you raised, but it means you don't
>> need to change your schema each time you encounter a new type of
>> information.
> Thanks for the suggestion, Simon.  If only it were that simple.
> Unfortunately, each of the foreign key tables are actually quite distinct in
> their purpose, so putting them all into one huge table would not be the
> right solution.
>
> The primary key is a timestamp (as an integer, i.e. number of seconds since
> some arbitrary epoch or other).  The primary key table holds then the
> "common" information on the "action" that has happened, i.e. timestamp, user
> name, and some other data.  The foreign key tables are all those that hold
> data for the particular actions that can be done, but really they are very
> very different from each other.
>
> Of course it would have been possible instead to merge the columns from the
> primary key table into each of the foreign key tables and not have the
> primary key table, but the really nice thing about keeping the common data
> it central, is that only one table needs to be queried e.g. to find out the
> which users have been making alterations to the system and when (this is one
> of the main design requirements).
>
> It seems to be a trade-off -- either the complexity is in the DELETE
> statement to keep the primary key table tidy or in the SELECT statement
> querying it.  If it has to be a choice, then the complexity has to be in the
> DELETE statement since this happens very infrequently.
>
> Cheers
> Andy
>
>
>
> ___
> 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] Help: Required help/guidance for the segmentation fault in sqlite3 library to proceed further...

[Borra, Kishore Babu]

Hi,

I require some help in getting the fix for some memory corruption issues, 
occurring while using the sqlite3 library. It would be very helpful, if you can 
guide to fix the below issues or atleast provide some info on this, to minimize 
the memory corruption, occurring mostly while sqlite3_free or doing  some query.

Thanks,
Kishore

The below backtraces observed for crash are-

1. #0  0x0feb1a98 in raise () from /lib/libc.so.6
#1  0x0feb3418 in abort () from /lib/libc.so.6
#2  0x0fee847c in __libc_message () from /lib/libc.so.6
#3  0x0feef5ec in malloc_printerr () from /lib/libc.so.6
#4  0x0fef0244 in free () from /lib/libc.so.6
#5  0x0fc1f0d8 in dbi_result_free () from /usr/local/lib/libdbi.so.0
#6  0x0fc97d00 in FreeDBIResult (result=0x1037ec88) at dbInterface.c:1008
#7  0x1003ebc4 in FillDartInfo (nodeType=975170328, HmpDataLocalCopy=0x2) at 
configMgrPollDartGnrl.c:424
#8  0x10037820 in ConfigManagerPollerThread () at configManager.c:490
#9  0x0fd35d38 in start_thread () from /lib/libpthread.so.0
#10 0x0ff45f44 in clone () from /lib/libc.so.6
Backtrace stopped: previous frame inner to this frame (corrupt stack?)


2. *** glibc detected *** agent/.libs/lt-snmpd: corrupted double-linked list: 
0x08310c18 ***
=== Backtrace: =
/lib/i686/cmov/libc.so.6[0xb7af9624]
/lib/i686/cmov/libc.so.6[0xb7afb557]
/lib/i686/cmov/libc.so.6(cfree+0x96)[0xb7afb826]
/usr/local/lib/libsqlite3.so.0[0xb788a330]
/usr/local/lib/libsqlite3.so.0(sqlite3_free+0x69)[0xb78547b9]
/usr/local/lib/libsqlite3.so.0[0xb785628a]
/usr/local/lib/libsqlite3.so.0[0xb7856539]
/usr/local/lib/libsqlite3.so.0[0xb785677c]
/usr/local/lib/libsqlite3.so.0[0xb7862613]
/usr/local/lib/libsqlite3.so.0[0xb7862648]
/usr/local/lib/libsqlite3.so.0[0xb7875a8a]
/usr/local/lib/libsqlite3.so.0[0xb7875d24]
/usr/local/lib/libsqlite3.so.0[0xb787a22a]
/usr/local/lib/libsqlite3.so.0[0xb789c3ba]
/usr/local/lib/libsqlite3.so.0(sqlite3_step+0x57)[0xb78907c7]
/usr/local/lib/libsqlite3.so.0(sqlite3_exec+0xf7)[0xb7890bd7]
/usr/local/lib/libsqlite3.so.0(sqlite3_get_table+0xd8)[0xb7890f68]
/usr/local/lib/dbd//libdbdsqlite3.so(dbd_query+0x45)[0xb792b215]
/usr/lib/libdbi.so.0(dbi_conn_query+0x4b)[0xb79123fb]
/home/kishore/prism_N5Dev7.0/Dev7.0/libs/dbInterface/installable/usr/local/lib/libdbInterface.so.1(ExcecuteSelectQuery+0x17f)[0xb793531f]
/home/kishore/prism_N5Dev7


Sqlite version details are as follows:-

Sqlite3 Library using are Sqlite-3.6.23.1
Library - libsqlite3.so.0.8.6

Using libdbi as wrappers-
libdbi drivers - 0.8.2.1 version. On top of sq1lite3.
Libdbi -0.8.3 version


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

[sqlite] Required clarification for the segmentation fault in sqlite3 library..

[Borra, Kishore Babu]

Hi,

I require some help in getting the fix for some memory corruption issues, 
occurring while using the sqlite3 library. It would be very helpful, if you can 
guide to fix the below issues or atleast provide some info on this, to minimize 
the memory corruption, occurring mostly while sqlite3_free or doing  some query.

Thanks,
Kishore

The backtraces observed while there is a crash are-

1. #0  0x0feb1a98 in raise () from /lib/libc.so.6
#1  0x0feb3418 in abort () from /lib/libc.so.6
#2  0x0fee847c in __libc_message () from /lib/libc.so.6
#3  0x0feef5ec in malloc_printerr () from /lib/libc.so.6
#4  0x0fef0244 in free () from /lib/libc.so.6
#5  0x0fc1f0d8 in dbi_result_free () from /usr/local/lib/libdbi.so.0
#6  0x0fc97d00 in FreeDBIResult (result=0x1037ec88) at dbInterface.c:1008
#7  0x1003ebc4 in FillDartInfo (nodeType=975170328, HmpDataLocalCopy=0x2) at 
configMgrPollDartGnrl.c:424
#8  0x10037820 in ConfigManagerPollerThread () at configManager.c:490
#9  0x0fd35d38 in start_thread () from /lib/libpthread.so.0
#10 0x0ff45f44 in clone () from /lib/libc.so.6
Backtrace stopped: previous frame inner to this frame (corrupt stack?)


2. *** glibc detected *** agent/.libs/lt-snmpd: corrupted double-linked list: 
0x08310c18 ***
=== Backtrace: =
/lib/i686/cmov/libc.so.6[0xb7af9624]
/lib/i686/cmov/libc.so.6[0xb7afb557]
/lib/i686/cmov/libc.so.6(cfree+0x96)[0xb7afb826]
/usr/local/lib/libsqlite3.so.0[0xb788a330]
/usr/local/lib/libsqlite3.so.0(sqlite3_free+0x69)[0xb78547b9]
/usr/local/lib/libsqlite3.so.0[0xb785628a]
/usr/local/lib/libsqlite3.so.0[0xb7856539]
/usr/local/lib/libsqlite3.so.0[0xb785677c]
/usr/local/lib/libsqlite3.so.0[0xb7862613]
/usr/local/lib/libsqlite3.so.0[0xb7862648]
/usr/local/lib/libsqlite3.so.0[0xb7875a8a]
/usr/local/lib/libsqlite3.so.0[0xb7875d24]
/usr/local/lib/libsqlite3.so.0[0xb787a22a]
/usr/local/lib/libsqlite3.so.0[0xb789c3ba]
/usr/local/lib/libsqlite3.so.0(sqlite3_step+0x57)[0xb78907c7]
/usr/local/lib/libsqlite3.so.0(sqlite3_exec+0xf7)[0xb7890bd7]
/usr/local/lib/libsqlite3.so.0(sqlite3_get_table+0xd8)[0xb7890f68]
/usr/local/lib/dbd//libdbdsqlite3.so(dbd_query+0x45)[0xb792b215]
/usr/lib/libdbi.so.0(dbi_conn_query+0x4b)[0xb79123fb]
/home/kishore/prism_N5Dev7.0/Dev7.0/libs/dbInterface/installable/usr/local/lib/libdbInterface.so.1(ExcecuteSelectQuery+0x17f)[0xb793531f]
/home/kishore/prism_N5Dev7


Sqlite version details are as follows:-

Sqlite3 Library using are Sqlite-3.6.23.1
Library - libsqlite3.so.0.8.6

Using libdbi as wrappers-
libdbi drivers - 0.8.2.1 version. On top of sq1lite3.
Libdbi -0.8.3 version


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

Re: [sqlite] COUNT very slow

[Michele Pradella]

  I can get a big speed up of COUNT if I first do a VIEW of what I have 
to count and than make select COUNT on the view.
Without VIEW: 9 Minutes
With VIEW: 8 Seconds!

Il 24/09/2010 10.58, Martin Engelschalk ha scritto:
>
> Am 24.09.2010 10:38, schrieb Michele Pradella:
>> ok, thank you for the advices, I'll try to use a TRIGGER.
>> The DB already has an index.
>> Anyway if I have to count something like this:
>> select COUNT(*) from logs WHERE DateTime<=yesterday
>> I can't do it with a TRIGGER
> No, but in this case an index on DateTime will help (except when most of
> the records are older than yesterday).
> Also, you could keep track of the number of records for each day with a
> table containing DateTime and RecordCount.
>
>> Il 24/09/2010 10.29, Martin Engelschalk ha scritto:
>>>  Hello Michele,
>>>
>>> sqlite does not remember the number of records in a table. Therefore,
>>> counting them requires to scan the full table, which explains the slow
>>> perfornamce.
>>>
>>> This topic has been discussed previously in this list. See
>>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html
>>>
>>> If you need the result quickly, you have to maintain the rnumber of
>>> records yourself in a different table, perhaps using triggers.
>>>
>>> Martin
>>>
>>>
>>> Am 24.09.2010 10:13, schrieb Michele Pradella:
   I have an SQLite DB of about 9GB with about 2.500.000 records.
 I can't understand why the "select COUNT(*) from log" statement is
 extremely slow, it takes me about 9-10 minutes!
 I try with:
 select COUNT(1) from logs
 select COUNT(DateTime) from logs
 same result. Have you idea of why it's so slow?
 ___
 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
>
>


-- 
Selea s.r.l.


Michele Pradella R&D


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COUNT very slow

[Martin Engelschalk]

Am 24.09.2010 10:38, schrieb Michele Pradella:
>ok, thank you for the advices, I'll try to use a TRIGGER.
> The DB already has an index.
> Anyway if I have to count something like this:
> select COUNT(*) from logs WHERE DateTime<=yesterday
> I can't do it with a TRIGGER
No, but in this case an index on DateTime will help (except when most of 
the records are older than yesterday).
Also, you could keep track of the number of records for each day with a 
table containing DateTime and RecordCount.

> Il 24/09/2010 10.29, Martin Engelschalk ha scritto:
>> Hello Michele,
>>
>> sqlite does not remember the number of records in a table. Therefore,
>> counting them requires to scan the full table, which explains the slow
>> perfornamce.
>>
>> This topic has been discussed previously in this list. See
>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html
>>
>> If you need the result quickly, you have to maintain the rnumber of
>> records yourself in a different table, perhaps using triggers.
>>
>> Martin
>>
>>
>> Am 24.09.2010 10:13, schrieb Michele Pradella:
>>>  I have an SQLite DB of about 9GB with about 2.500.000 records.
>>> I can't understand why the "select COUNT(*) from log" statement is
>>> extremely slow, it takes me about 9-10 minutes!
>>> I try with:
>>> select COUNT(1) from logs
>>> select COUNT(DateTime) from logs
>>> same result. Have you idea of why it's so slow?
>>> ___
>>> 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] COUNT very slow

[Michele Pradella]

  ok, thank you for the advices, I'll try to use a TRIGGER.
The DB already has an index.
Anyway if I have to count something like this:
select COUNT(*) from logs WHERE DateTime<=yesterday
I can't do it with a TRIGGER

Il 24/09/2010 10.29, Martin Engelschalk ha scritto:
>Hello Michele,
>
> sqlite does not remember the number of records in a table. Therefore,
> counting them requires to scan the full table, which explains the slow
> perfornamce.
>
> This topic has been discussed previously in this list. See
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html
>
> If you need the result quickly, you have to maintain the rnumber of
> records yourself in a different table, perhaps using triggers.
>
> Martin
>
>
> Am 24.09.2010 10:13, schrieb Michele Pradella:
>> I have an SQLite DB of about 9GB with about 2.500.000 records.
>> I can't understand why the "select COUNT(*) from log" statement is
>> extremely slow, it takes me about 9-10 minutes!
>> I try with:
>> select COUNT(1) from logs
>> select COUNT(DateTime) from logs
>> same result. Have you idea of why it's so slow?
>> ___
>> 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
>
>


-- 
Selea s.r.l.


Michele Pradella R&D


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COUNT very slow

[Drake Wilson]

Quoth Michele Pradella , on 2010-09-24 10:13:59 
+0200:
>   I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is 
> extremely slow, it takes me about 9-10 minutes!
> I try with:
> select COUNT(1) from logs
> select COUNT(DateTime) from logs
> same result. Have you idea of why it's so slow?

I'm not sure why this is considered "extremely slow" when iterating
the rows of that large a file.  On my PC-class hardware I measure
around 2m30s of real time copying a 9 GB file into the null device;
it's not implausible to me that on some other hardware, using SQLite
rather than a raw copy, it would take four times as long to scan.

If you're expecting the count to be stored separately and therefore to
be quickly accessible, you might use insert/remove triggers to keep
track of that yourself in a single-row auxiliary table instead.

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

Re: [sqlite] COUNT very slow

[Martin Engelschalk]

  Hello Michele,

sqlite does not remember the number of records in a table. Therefore, 
counting them requires to scan the full table, which explains the slow 
perfornamce.

This topic has been discussed previously in this list. See 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html

If you need the result quickly, you have to maintain the rnumber of 
records yourself in a different table, perhaps using triggers.

Martin


Am 24.09.2010 10:13, schrieb Michele Pradella:
>I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is
> extremely slow, it takes me about 9-10 minutes!
> I try with:
> select COUNT(1) from logs
> select COUNT(DateTime) from logs
> same result. Have you idea of why it's so slow?
> ___
> 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] COUNT very slow

[Dan Kennedy]

On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote:

>  I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is
> extremely slow, it takes me about 9-10 minutes!

In SQLite, count() is obliged to traverse the entire table
structure. Or if the table has any indexes, it may choose to
traverse an entire index (index structures are almost always
smaller than the corresponding table). Either way, with such
a large database this is going to take some time.

The usual suggestion is to use triggers to store the number
of rows in the table of interest in some other SQL table. i.e.
use an INSERT trigger to increment a counter and a DELETE
trigger to decrement it.

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

Re: [sqlite] COUNT very slow

[Michele Pradella]

  I forgot to tell you that I'm using the sqlite3 shell to test the 
statement, and I got same slowness with DB in use or DB not in use


Il 24/09/2010 10.13, Michele Pradella ha scritto:
>I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is
> extremely slow, it takes me about 9-10 minutes!
> I try with:
> select COUNT(1) from logs
> select COUNT(DateTime) from logs
> same result. Have you idea of why it's so slow?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Selea s.r.l.


Michele Pradella R&D


SELEA s.r.l.

Via Aldo Moro 69
Italy - 46019 Cicognara (MN)
Tel +39 0375 889091
Fax +39 0375 889080
*michele.prade...@selea.com* 
*http://www.selea.com*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] COUNT very slow

[Michele Pradella]

  I have an SQLite DB of about 9GB with about 2.500.000 records.
I can't understand why the "select COUNT(*) from log" statement is 
extremely slow, it takes me about 9-10 minutes!
I try with:
select COUNT(1) from logs
select COUNT(DateTime) from logs
same result. Have you idea of why it's so slow?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users