Re: [sqlite] Most Frequent Occurrence Problem

[Macgyver7]

I am not sure exactly how I am to work in the coalesce function to get the
result, I tried some experiments and I could get the first and or second
fields in another column, but not as part of the bracketed group. 

This is the argument as it stands now, (without the coalesce function) how
should I introduce the coalesce function to get the desired result?  

SELECT  '( ' ||(select English1)|| ', ' ||(select english2)|| ', ' ||(select
english3 )|| ' )'  AS English FROM UniqueAramaic4;  

I apologise if this sounds like a silly question, but I am new to SQL and am
trying to get syntax, order and function use straight in my head.

Cheers,
Tim.




Igor Tandetnik wrote:
> 
> Macgyver7  wrote:
>> Thanks Igor, that works, however there is a problem.  This will only
>> return a
>> result when there are no null fields in the last two columns.  The first
>> column has results in every field, but columns 2 and 3 don't have results
>> in
>> every field. How do I get around this not returning anything when there
>> are
>> null fields?
> 
> coalesce((select ...), '--');
> 
> -- 
> 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://old.nabble.com/Most-Frequent-Occurrence-Problem-tp32900981p32906732.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] Testing PRAGMA integrity_check functionality

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/12/11 06:44, George Eckert wrote:
> Does anyone know of a "good" way to forfce an error in a database so
> that PRAGMA integrity_check would produce an error?

Truncating the database a bit after a vacuum will definitely do it, and it
will likely open correctly and maybe even do some queries.  You can
scribble all over the first few pages to make it an invalid database.

If you look at the file format you can see how to do specific corruptions
such as the free list:

  http://www.sqlite.org/fileformat2.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk7ZqlQACgkQmOOfHg372QQIYwCg4GFPTPkEBAJ4Kb9elXnFpVW9
BuYAn1dl9jnvKycd/GkufIG6s8aMqnTT
=PHlw
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re versing Text not Numbers

[Macgyver7]

Thank you for that, I hadn't been able to find a function, I will have to
explore the link you posted.

Cheers,
Tim.




Igor Tandetnik wrote:
> 
> Macgyver7  wrote:
>> I have a column of text that has numbers in some fields and words in
>> others.
>> I need to reverse the letter order of the text eg.  'abcde' becomes
>> 'edcba'
>> without changing the order of the fields with numbers.  How do I reverse
>> the
>> letter order and how do I avoid doing that to the numbers as well?
> 
> There is no built-in reverse() function. You'll have to write your own:
> 
> http://www.sqlite.org/c3ref/create_function.html
> 
> That function can also check the type of its parameter and return it
> unchanged if it's not a string. Or, you could do that in SQL:
> 
> select (case when typeof(field) = 'text' then reverse(field) else field
> end)
> from myTable;
> 
> -- 
> 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://old.nabble.com/Reversing-Text-not-Numbers-tp32906645p32906707.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] Re versing Text not Numbers

[Igor Tandetnik]

Macgyver7  wrote:
> I have a column of text that has numbers in some fields and words in others.
> I need to reverse the letter order of the text eg.  'abcde' becomes 'edcba'
> without changing the order of the fields with numbers.  How do I reverse the
> letter order and how do I avoid doing that to the numbers as well?

There is no built-in reverse() function. You'll have to write your own:

http://www.sqlite.org/c3ref/create_function.html

That function can also check the type of its parameter and return it unchanged 
if it's not a string. Or, you could do that in SQL:

select (case when typeof(field) = 'text' then reverse(field) else field end)
from myTable;

-- 
Igor Tandetnik

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

Re: [sqlite] Most Frequent Occurrence Problem

[Igor Tandetnik]

Macgyver7  wrote:
> Thanks Igor, that works, however there is a problem.  This will only return a
> result when there are no null fields in the last two columns.  The first
> column has results in every field, but columns 2 and 3 don't have results in
> every field. How do I get around this not returning anything when there are
> null fields?

coalesce((select ...), '--');

-- 
Igor Tandetnik

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

[sqlite] Re versing Text not Numbers

[Macgyver7]

I have a column of text that has numbers in some fields and words in others. 
I need to reverse the letter order of the text eg.  'abcde' becomes 'edcba' 
without changing the order of the fields with numbers.  How do I reverse the
letter order and how do I avoid doing that to the numbers as well?

-- 
View this message in context: 
http://old.nabble.com/Reversing-Text-not-Numbers-tp32906645p32906645.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] Most Frequent Occurrence Problem

[Macgyver7]

Thanks Igor, that works, however there is a problem.  This will only return a
result when there are no null fields in the last two columns.  The first
column has results in every field, but columns 2 and 3 don't have results in
every field. How do I get around this not returning anything when there are
null fields?

Cheers,
Tim.



Igor Tandetnik wrote:
> 
> Macgyver7  wrote:
>> Many thanks Igor, that worked beautifully.  If I wanted to put the three
>> top
>> meanings in one column  separated by commas, instead of three columns, is
>> there an easy way to do that? It would be good to have them in brackets
>> too.
>> eg. (meaning 1, meaning 2, meaning 3)
> 
> '(' || (select Meaning ...) || ',' || (select Meaning ...) || ',' ||
> (select Meaning ...) || ')'
> 
> -- 
> 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://old.nabble.com/Most-Frequent-Occurrence-Problem-tp32900981p32906628.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] Most Frequent Occurrence Problem

[Igor Tandetnik]

Macgyver7  wrote:
> Many thanks Igor, that worked beautifully.  If I wanted to put the three top
> meanings in one column  separated by commas, instead of three columns, is
> there an easy way to do that? It would be good to have them in brackets too.
> eg. (meaning 1, meaning 2, meaning 3)

'(' || (select Meaning ...) || ',' || (select Meaning ...) || ',' || (select 
Meaning ...) || ')'

-- 
Igor Tandetnik

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

Re: [sqlite] Most Frequent Occurrence Problem

[Sean Pieper]

one possibility: use || for concatenation.

e.g. "(" || select(..) ||"," select() ||")"

maybe there is a cleaner way in sqlite.

-sean

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Macgyver7
Sent: Friday, December 02, 2011 4:04 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Most Frequent Occurrence Problem


Many thanks Igor, that worked beautifully.  If I wanted to put the three top
meanings in one column  separated by commas, instead of three columns, is
there an easy way to do that? It would be good to have them in brackets too.
eg. (meaning 1, meaning 2, meaning 3)  

Thanks for the help,
Tim.




Igor Tandetnik wrote:
> 
> Macgyver7  select
> (select Col1 from MyTable where Col2=ThisCol2),
> ThisCol2,
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 0),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 1),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 2),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 3),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 4)
> from (select distinct Col2 as ThisCol2 from MyTable);
> 
> -- 
> 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://old.nabble.com/Most-Frequent-Occurrence-Problem-tp32900981p32906113.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
---
This email message is for the sole use of the intended recipient(s) and may 
contain
confidential information.  Any unauthorized review, use, disclosure or 
distribution
is prohibited.  If you are not the intended recipient, please contact the 
sender by
reply email and destroy all copies of the original message.
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most Frequent Occurrence Problem

[Macgyver7]

Many thanks Igor, that worked beautifully.  If I wanted to put the three top
meanings in one column  separated by commas, instead of three columns, is
there an easy way to do that? It would be good to have them in brackets too.
eg. (meaning 1, meaning 2, meaning 3)  

Thanks for the help,
Tim.




Igor Tandetnik wrote:
> 
> Macgyver7  select
> (select Col1 from MyTable where Col2=ThisCol2),
> ThisCol2,
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 0),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 1),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 2),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 3),
> (select Meaning from MyTable where Col2=ThisCol2
>  group by Meaning order by count(*) desc limit 1 offset 4)
> from (select distinct Col2 as ThisCol2 from MyTable);
> 
> -- 
> 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://old.nabble.com/Most-Frequent-Occurrence-Problem-tp32900981p32906113.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] union-having bug

[Igor Tandetnik]

Gillman, David  wrote:
> Is this behavior known?  The third query returns no rows even though bar = 1.
> 
> sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 
> bar union select 1 ind, 1 foo, 0 bar) group by ind
> having foo > 0; 
> ind|foo|bar
> 1|1|1
> sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 
> bar union select 1 ind, 1 foo, 0 bar) group by ind
> having bar > 0; 

Your condition involves an expression that neither appears in GROUP BY clause, 
nor uses an aggregate function. The value of foo (in the first query) and bar 
(in the second) will come from some row in the group - it is undefined which 
one. Purely by accident, SQLite chose the row that satisfies the condition in 
the first case, and one that doesn't in the second.
-- 
Igor Tandetnik

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

Re: [sqlite] union-having bug

[Gillman, David]

Hmm, the mailer changed my output.  Here's what I meant:

sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind;
ind|foo|bar
1|1|1

sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind having foo > 0;
ind|foo|bar
1|1|1

sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind having bar > 0;

sqlite> select * from (select ind, sum(foo) foo, sum(bar) bar from (select 1 
ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind) where bar > 0;
ind|foo|bar
1|1|1

sqlite>

-Original Message-
From: Gillman, David [mailto:dgill...@akamai.com] 
Sent: Friday, December 02, 2011 6:37 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] union-having bug

Hi,

Is this behavior known?  The third query returns no rows even though bar = 1.

sqlite> select region, sum(edge) edge, sum(infra) infra from (select 1 region, 
0 edge, 1 infra union select 1 region, 1 edge, 0 infra) group by 
region;region|edge|infra
1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind;
ind|foo|bar
1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind having foo > 0;
ind|foo|bar
1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind having bar > 0;
sqlite> select * from (select ind, sum(foo) foo, sum(bar) bar from (select 1 
ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind) where bar > 0;
ind|foo|bar
1|1|1
sqlite>

___
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] union-having bug

[Gillman, David]

Hi,

Is this behavior known?  The third query returns no rows even though bar = 1.

sqlite> select region, sum(edge) edge, sum(infra) infra from (select 1 region, 
0 edge, 1 infra union select 1 region, 1 edge, 0 infra) group by 
region;region|edge|infra
1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind;
ind|foo|bar
1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind having foo > 0;
ind|foo|bar
1|1|1
sqlite> select ind, sum(foo) foo, sum(bar) bar from (select 1 ind, 0 foo, 1 bar 
union select 1 ind, 1 foo, 0 bar) group by ind having bar > 0;
sqlite> select * from (select ind, sum(foo) foo, sum(bar) bar from (select 1 
ind, 0 foo, 1 bar union select 1 ind, 1 foo, 0 bar) group by ind) where bar > 0;
ind|foo|bar
1|1|1
sqlite>

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

Re: [sqlite] Slow View when it is used in a where clause

[Pavel Ivanov]

> Specifically, when I said "using directly the select of the view", I mean
> execute this query:
>
> EXPLAIN QUERY PLAN SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount,
> count(DISTINCT dw.Document_idDocument) AS documentsCount
>
> FROM WordGroup wg
>    LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
>    LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
> Where idWordGroup = 1
> GROUP BY wg.idWordGroup;

This is a completely different query and its good performance is not
surprising at all.

> Anyway, I am thinking that perhaps the solution is create some triggers in
> order to calculate these statistical measures, and add some new columns with
> this data to my current tables.

That's always a better solution for any statistical information IMHO.


Pavel


On Fri, Dec 2, 2011 at 11:01 AM, Manuel Jesus Cobo Martin
 wrote:
> Hello Pavel,
>
> Thank you for your answer.
>
> Probably, as you have commented, the problem is that the optimizer of SQLite
> does not work fine in this case.
>
> Specifically, when I said "using directly the select of the view", I mean
> execute this query:
>
> EXPLAIN QUERY PLAN SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount,
> count(DISTINCT dw.Document_idDocument) AS documentsCount
>
> FROM WordGroup wg
>    LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
>    LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
> Where idWordGroup = 1
> GROUP BY wg.idWordGroup;
>
> This query perform properly, whereas the same query using the view performs
> an SCAN over the table WordGroup when it is unnecessary.
>
> Anyway, I am thinking that perhaps the solution is create some triggers in
> order to calculate these statistical measures, and add some new columns with
> this data to my current tables.
>
> Best Regards,
>
> Manuel Jesús.
>
> El 02/12/2011 15:49, Pavel Ivanov escribió:
>
>>> I have been analyzing the Query PLAN. In the query "Select * FROM
>>> WordGroupView Where idWordGroup = 1;" the database engine perform a scan
>>> over WordGroup table, and it is not necessary since the where clause is
>>> defined. However, using directly the query of the view change the SCAN by
>>> a
>>> SEARCH, so the query is faster answered.
>>>
>>> Please, someone know what is the problem?
>>
>> Probably SQLite's optimizer is not so smart to figure out that
>> combination of GROUP BY in the inner SELECT and WHERE in the outer one
>> on the same field can result in removing of GROUP BY and adding WHERE
>> to inner query.
>>
>> Or when you say that using select from view directly results in faster
>> query do you use query like the following?
>>
>> Select * FROM (
>> SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT
>> dw.Document_idDocument) AS documentsCount
>> FROM WordGroup wg
>>     LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
>>     LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
>> GROUP BY wg.idWordGroup;
>> )
>> Where idWordGroup = 1;
>>
>>
>> Pavel
>>
>>
>> On Thu, Dec 1, 2011 at 7:42 PM, Manuel Jesus Cobo Martin
>>   wrote:
>>>
>>> Hello,
>>>
>>> I am new in this mailing list.
>>>
>>> I am developing a Java tool and I use SQLite as file format. At this
>>> moments, I did not have any problem, and SQLite works quite fine.
>>>
>>> I want to create some views to generate statistical and aggregate data in
>>> order to show more information to the user.
>>>
>>> The follow code is an example of a View:
>>>
>>> CREATE VIEW WordGroupView AS
>>> SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT
>>> dw.Document_idDocument) AS documentsCount
>>> FROM WordGroup wg
>>>     LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
>>>     LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
>>> GROUP BY wg.idWordGroup;
>>>
>>> The problem is when I am using the view in other query.
>>>
>>> For example, the query "Select * FROM WordGroupView Where idWordGroup =
>>> 1;"
>>> is very slow. Whereas, if I use directly the main query of the view, is
>>> faster (1400ms vs 7ms).
>>>
>>> I think that I am doing something in wrong way but I do not understand
>>> what
>>> is happens.
>>>
>>> I have been analyzing the Query PLAN. In the query "Select * FROM
>>> WordGroupView Where idWordGroup = 1;" the database engine perform a scan
>>> over WordGroup table, and it is not necessary since the where clause is
>>> defined. However, using directly the query of the view change the SCAN by
>>> a
>>> SEARCH, so the query is faster answered.
>>>
>>> Please, someone know what is the problem?
>>>
>>> Thank you in advance.
>>>
>>> Best Regards,
>>>
>>> Manuel Jesús.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> 

Re: [sqlite] Slow View when it is used in a where clause

[Manuel Jesus Cobo Martin]

Hello Pavel,

Thank you for your answer.

Probably, as you have commented, the problem is that the optimizer of 
SQLite does not work fine in this case.


Specifically, when I said "using directly the select of the view", I 
mean execute this query:


EXPLAIN QUERY PLAN SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, 
count(DISTINCT dw.Document_idDocument) AS documentsCount

FROM WordGroup wg
LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
Where idWordGroup = 1
GROUP BY wg.idWordGroup;

This query perform properly, whereas the same query using the view 
performs an SCAN over the table WordGroup when it is unnecessary.


Anyway, I am thinking that perhaps the solution is create some triggers 
in order to calculate these statistical measures, and add some new 
columns with this data to my current tables.


Best Regards,

Manuel Jesús.

El 02/12/2011 15:49, Pavel Ivanov escribió:

I have been analyzing the Query PLAN. In the query "Select * FROM
WordGroupView Where idWordGroup = 1;" the database engine perform a scan
over WordGroup table, and it is not necessary since the where clause is
defined. However, using directly the query of the view change the SCAN by a
SEARCH, so the query is faster answered.

Please, someone know what is the problem?

Probably SQLite's optimizer is not so smart to figure out that
combination of GROUP BY in the inner SELECT and WHERE in the outer one
on the same field can result in removing of GROUP BY and adding WHERE
to inner query.

Or when you say that using select from view directly results in faster
query do you use query like the following?

Select * FROM (
SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT
dw.Document_idDocument) AS documentsCount
FROM WordGroup wg
 LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
 LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
GROUP BY wg.idWordGroup;
)
Where idWordGroup = 1;


Pavel


On Thu, Dec 1, 2011 at 7:42 PM, Manuel Jesus Cobo Martin
  wrote:

Hello,

I am new in this mailing list.

I am developing a Java tool and I use SQLite as file format. At this
moments, I did not have any problem, and SQLite works quite fine.

I want to create some views to generate statistical and aggregate data in
order to show more information to the user.

The follow code is an example of a View:

CREATE VIEW WordGroupView AS
SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT
dw.Document_idDocument) AS documentsCount
FROM WordGroup wg
 LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
 LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
GROUP BY wg.idWordGroup;

The problem is when I am using the view in other query.

For example, the query "Select * FROM WordGroupView Where idWordGroup = 1;"
is very slow. Whereas, if I use directly the main query of the view, is
faster (1400ms vs 7ms).

I think that I am doing something in wrong way but I do not understand what
is happens.

I have been analyzing the Query PLAN. In the query "Select * FROM
WordGroupView Where idWordGroup = 1;" the database engine perform a scan
over WordGroup table, and it is not necessary since the where clause is
defined. However, using directly the query of the view change the SCAN by a
SEARCH, so the query is faster answered.

Please, someone know what is the problem?

Thank you in advance.

Best Regards,

Manuel Jesús.
___
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] SQLite's FAIL ALGORITHM IN ONCONFLICT CLAUSE

[Jay A. Kreibich]

On Fri, Dec 02, 2011 at 12:44:53AM -0800, destiny12 scratched on the wall:
> 
> I m facing some problem in execution of sqlite's fail algorithm. Its
> definition says that it don't rollback changes made prior to encountering
> constraint voilation and changes to that row where constraint voilation is
> occured and beyond never occur. I executed it. But when I update values
> after the row which is encountering constraint voilation,it do that. But
> definition is saying different thing. What can be the problem. Please tell
> by an example that how it is executed. Thanks.

  Remember that the rows of a table have no inherent order, so you
  don't always know what order of the update.  This can make it
  difficult to understand which rows re "after" the row violation, and
  which were processed first.

  Also understand that the fail is only applied once per UPDATE
  statement.  If you re-run the update, the command will run until it
  re-encounters the violation.  it is possible the order will change,
  so the row causing the constraint violation may not be the first row
  subsequent UPDATE commands attempt to modify.

   -j


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

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Testing PRAGMA integrity_check functionality

[Richard Hipp]

On Fri, Dec 2, 2011 at 9:44 AM, George Eckert wrote:

> Does anyone know of a "good" way to forfce an error in a database so that
> PRAGMA integrity_check would produce an error?
>

We have lots of test procedures for SQLite in which we change individual
bytes or individual bits of a database file, then run "PRAGMA
integrity_check" to make sure that the change is detected.

Obviously, if the bit or byte that changed is in the middle of raw data or
in an unused portion of the file, then nothing will be found.  But expect
any change to the metadata to be detected by PRAGMA integrity_check.



>
> Thanks,
>
> George Eckert
> ___
> 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] Slow View when it is used in a where clause

[Pavel Ivanov]

> I have been analyzing the Query PLAN. In the query "Select * FROM
> WordGroupView Where idWordGroup = 1;" the database engine perform a scan
> over WordGroup table, and it is not necessary since the where clause is
> defined. However, using directly the query of the view change the SCAN by a
> SEARCH, so the query is faster answered.
>
> Please, someone know what is the problem?

Probably SQLite's optimizer is not so smart to figure out that
combination of GROUP BY in the inner SELECT and WHERE in the outer one
on the same field can result in removing of GROUP BY and adding WHERE
to inner query.

Or when you say that using select from view directly results in faster
query do you use query like the following?

Select * FROM (
SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT
dw.Document_idDocument) AS documentsCount
FROM WordGroup wg
    LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
    LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
GROUP BY wg.idWordGroup;
)
Where idWordGroup = 1;


Pavel


On Thu, Dec 1, 2011 at 7:42 PM, Manuel Jesus Cobo Martin
 wrote:
> Hello,
>
> I am new in this mailing list.
>
> I am developing a Java tool and I use SQLite as file format. At this
> moments, I did not have any problem, and SQLite works quite fine.
>
> I want to create some views to generate statistical and aggregate data in
> order to show more information to the user.
>
> The follow code is an example of a View:
>
> CREATE VIEW WordGroupView AS
> SELECT wg.*, count(DISTINCT w.idWord) AS itemsCount, count(DISTINCT
> dw.Document_idDocument) AS documentsCount
> FROM WordGroup wg
>     LEFT OUTER JOIN Word w ON wg.idWordGroup = w.WordGroup_idWordGroup
>     LEFT OUTER JOIN Document_Word dw ON w.idWord = dw.Word_idWord
> GROUP BY wg.idWordGroup;
>
> The problem is when I am using the view in other query.
>
> For example, the query "Select * FROM WordGroupView Where idWordGroup = 1;"
> is very slow. Whereas, if I use directly the main query of the view, is
> faster (1400ms vs 7ms).
>
> I think that I am doing something in wrong way but I do not understand what
> is happens.
>
> I have been analyzing the Query PLAN. In the query "Select * FROM
> WordGroupView Where idWordGroup = 1;" the database engine perform a scan
> over WordGroup table, and it is not necessary since the where clause is
> defined. However, using directly the query of the view change the SCAN by a
> SEARCH, so the query is faster answered.
>
> Please, someone know what is the problem?
>
> Thank you in advance.
>
> Best Regards,
>
> Manuel Jesús.
> ___
> 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] Testing PRAGMA integrity_check functionality

[George Eckert]

Does anyone know of a "good" way to forfce an error in a database so that
PRAGMA integrity_check would produce an error?

Thanks,

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

Re: [sqlite] SQLite's FAIL ALGORITHM IN ONCONFLICT CLAUSE

[Igor Tandetnik]

destiny12  wrote:
> I m facing some problem in execution of sqlite's fail algorithm. Its
> definition says that it don't rollback changes made prior to encountering
> constraint voilation and changes to that row where constraint voilation is
> occured and beyond never occur. I executed it. But when I update values
> after the row which is encountering constraint voilation,it do that. But
> definition is saying different thing. What can be the problem. Please tell
> by an example that how it is executed. Thanks.

No - *you* show an example where the observed results differ from your 
expectations. You have a problem and seek help - the burden is on you to 
provide sufficient information for others to investigate the issue.
-- 
Igor Tandetnik

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

Re: [sqlite] Most Frequent Occurrence Problem

[Igor Tandetnik]

Macgyver7  wrote:
> I have a table with three columns, Col1, Col2 and Meaning.  I have multiple
> occurrences of distinct words within the column, Col2, with it's specific
> meaning for each word listed in the Meaning column.  There are multiple
> occurrences of each distinct word and meaning combination.  How do I return
> a table showing in one column each distinct word that occurs in Col2 and in
> the next column to that (or ideally the next five columns), have the top 5
> most frequently occurring meanings for the particular Col2 word?
> 
> I have some associated data in Col1 and would also like to display that in
> the resulting table, before the Col2 word.   Any help would be greatly
> appreciated.

select
(select Col1 from MyTable where Col2=ThisCol2),
ThisCol2,
(select Meaning from MyTable where Col2=ThisCol2
 group by Meaning order by count(*) desc limit 1 offset 0),
(select Meaning from MyTable where Col2=ThisCol2
 group by Meaning order by count(*) desc limit 1 offset 1),
(select Meaning from MyTable where Col2=ThisCol2
 group by Meaning order by count(*) desc limit 1 offset 2),
(select Meaning from MyTable where Col2=ThisCol2
 group by Meaning order by count(*) desc limit 1 offset 3),
(select Meaning from MyTable where Col2=ThisCol2
 group by Meaning order by count(*) desc limit 1 offset 4)
from (select distinct Col2 as ThisCol2 from MyTable);

-- 
Igor Tandetnik

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

Re: [sqlite] Checkpoint time

[Richard Hipp]

On Fri, Dec 2, 2011 at 2:15 AM, Sreekumar TP  wrote:

> There is another reader but sharing the same connection. So checkpoint
> process cannot be blocked by another reader.
>

The other reader doesn't have to be active - it merely needs to be holding
open a read transaction.  Perhaps you have failed to run sqlite3_reset() or
sqlite3_finalize() on one or more of the statements from the reader.

If you run "PRAGMA wal_checkpoint(RESTART)" then the SQLite connection
running that pragma will block until all readers and writers clear and the
checkpoint can run to completion - to the point of resetting the WAL file.
If that pragma then blocks forever, then you know you have a stuck
transaction somewhere.




>
>
> On Thu, Dec 1, 2011 at 8:54 PM, Yves Goergen  >wrote:
>
> >  On 01.12.2011 18:58 CE(S)T, Sreekumar TP wrote:
> > > During the measurement of manual checkpoint times, I see the following
> > > behaviour -
> > >
> > > # of WAL frames checkpointed is over 1000
> > > size of DB has not changed after checkpoint.(measured using fstat)
> > > Time for the checkpoint if around 500 ms.
> > >
> > > Why is it that eventhough 1000+ frames are checkpointed, the DB size
> has
> > > not increased ?
> > > All records inserted are new and unique records and insertions start
> from
> > > empty database.
> >
> > Could it be that the pages were not actually written to the database
> > because there was a reader lock on them? Did any other process access
> > the database (and thus hold any locks) while you did the checkpointing?
> >
> > (Wild guess. I've only learned about WAL and started using it yesterday.
> > I've read the whole WAL documentation page [1] though.)
> >
> > [1] http://sqlite.org/wal.html
> >
> > --
> > Yves Goergen "LonelyPixel" 
> > Visit my web laboratory at http://beta.unclassified.de
> > ___
> > 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] SQLite ordering data from multiple columns

[Simon Slavin]

On 2 Dec 2011, at 9:03am, Kit wrote:

> SELECT id, name FROM emp WHERE name LIKE '%emp%'
> UNION ALL
> SELECT id, descr FROM emp WHERE descr LIKE '%emp%';

I note very interesting names for the columns in this one.  The question I was 
asking was whether the second column was called 'name' for all rows returned by 
this query.

I also note an alternative to the above which might prove useful to the OP:

SELECT id, name, descr FROM emp WHERE (name || descr) LIKE '%emp%'

Or, if you want to be anal about it

SELECT id, name, descr FROM emp WHERE (name || 'xoxoxox' || descr) LIKE '%emp%'

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

Re: [sqlite] SQLite ordering data from multiple columns

[Kit]

2011/12/2, colombus :
> I want to search this database I will search Emp Name & Emp Desc for example
> If Search EMP I will get results as EMP1 , this is EMP1, EMP2, this is EMP2.
> I need to order this search in such a way that I get the Emp Name Column
> first then I will get the Emp Desc Column. So the result should be as
> follows. EMP1, EMP2, this is EMP1, this is EMP2. Is it possible to implement
> this in one query in Sqlite ???

SELECT id, name FROM emp WHERE name LIKE '%emp%'
UNION ALL
SELECT id, descr FROM emp WHERE descr LIKE '%emp%';
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Most Frequent Occurrence Problem

[Macgyver7]

I have a table with three columns, Col1, Col2 and Meaning.  I have multiple
occurrences of distinct words within the column, Col2, with it's specific
meaning for each word listed in the Meaning column.  There are multiple
occurrences of each distinct word and meaning combination.  How do I return
a table showing in one column each distinct word that occurs in Col2 and in
the next column to that (or ideally the next five columns), have the top 5
most frequently occurring meanings for the particular Col2 word? 

I have some associated data in Col1 and would also like to display that in
the resulting table, before the Col2 word.   Any help would be greatly
appreciated.
-- 
View this message in context: 
http://old.nabble.com/Most-Frequent-Occurrence-Problem-tp32900981p32900981.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] SQLite's FAIL ALGORITHM IN ONCONFLICT CLAUSE

[destiny12]

I m facing some problem in execution of sqlite's fail algorithm. Its
definition says that it don't rollback changes made prior to encountering
constraint voilation and changes to that row where constraint voilation is
occured and beyond never occur. I executed it. But when I update values
after the row which is encountering constraint voilation,it do that. But
definition is saying different thing. What can be the problem. Please tell
by an example that how it is executed. Thanks.
-- 
View this message in context: 
http://old.nabble.com/SQLite%27s-FAIL-ALGORITHM-IN-ONCONFLICT-CLAUSE-tp32900964p32900964.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