Re: [sqlite] SQLite question

[Simon Slavin]

On 11 Apr 2011, at 3:59am, Guilherme wrote:

> Well, I thought that if the execution followed a plan.. I could get for
> example, the cardinality from the results until a point, say a join.
> Imagine a query with 15 joins.. the execution would make a join, than, with
> the results from this join, make another join with results form other
> joins.. and so on..  I could get the cardinalities before the joins and see
> if it is too different from the estimated.. and maybe change the way the
> remainder of joins will be executed...
> 
> now, if the execution it's just a nested loop, I can't get any partial
> cardinality...

You can tell what it's doing by doing what Igor told you two posts ago: use 
EXPLAIN and EXPLAIN QUERY PLAN and look at the output.  If you read the output 
of EXPLAIN QUERY PLAN using this page

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

you will understand what SQLite has chosen to do with your statement.  If you 
read the output of EXPLAIN using the opcodes on this page

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

you will understand how SQLite decided to implement that.

Your underlying task appears to be second-guessing how SQLite's search 
optimisation works.  I have to warn you that the people who wrote it are good 
at what they do, and they've had 11 years of feedback from real-world use to 
improve the programming behind it.  I'd suggest that you learn how the existing 
algorithms work in great detail (i.e. read the source code) before trying to 
improve on what they've done.

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

Re: [sqlite] SQLite question

[Igor Tandetnik]

Guilherme  wrote:
> Well, I thought that if the execution followed a plan.. I could get for
> example, the cardinality from the results until a point, say a join.

I don't see how the conclusion follows from the premise. Why does "follows 
execution plan" have to imply "provides ready access to the data I happen to 
want"?

> Imagine a query with 15 joins.. the execution would make a join

What exactly does "make a join" mean? A join is a declarative construct - it 
specifies *what* data you want, not *how* to procur it. The query engine then 
does whatever it deems necessary to produce the data you've described with a 
join. 

> than, with
> the results from this join, make another join with results form other
> joins.. and so on..

That's not how database engines normally work. They don't explicitly produce 
intermediate resultsets.

Imagine that you have a phonebook, sorted by last name, first name. Suppose you 
want to find all people named John Smith. Would you first write down all 
Smiths, then go through that intermediate list looking for Johns? Or would you 
check for the first name as you go through the Smiths in the phonebook?

> I could get the cardinalities before the joins

You are engaging in wishful thinking.
-- 
Igor Tandetnik

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

Re: [sqlite] SQLite question

[Igor Tandetnik]

Guilherme  wrote:
> I believed that databases in general evaluate their queries, following a
> execution plan...
> e.g. first do a table scan and apply a filter from the where clause.. than
> use the results to execute a join with other table.. than use the results to
> excute with another join..
> 
> but now, from what I've seen, sqlite just does a nested loop, and returns
> (if available) a row in each loop...

I don't understand the difference. How does "does a nested loop" differ from 
"follows an execution plan"? SQLite follows an execution plan that, often, 
involves a loop. How exactly do you think other databases "do a table scan", if 
not with a loop?

In SQLite, execute any SQL statement with the keyword EXPLAIN prepended, e.g. 
"explain select * from SomeTable;". The output is SQLite's execution plan for 
that query. See also:

http://sqlite.org/vdbe.html
http://sqlite.org/opcode.html

-- 
Igor Tandetnik

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

[sqlite] SQLite question

[Guilherme]

Hi!

I believed that databases in general evaluate their queries, following a
execution plan...
e.g. first do a table scan and apply a filter from the where clause.. than
use the results to execute a join with other table.. than use the results to
excute with another join..

but now, from what I've seen, sqlite just does a nested loop, and returns
(if available) a row in each loop...
This type of execution is specific from sqlite, or there are more databases
that use the same algorithm?

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

Re: [sqlite] SQLite Question: Is there a SQLite function that can "copy" one database to another (programmatically)?

[Pavel Ivanov]

Apparently you're writing to the sqlite-users mailing list but don't
read all answers that people give writing to the same mailing list -
people usually do not reply to you directly and I'd appreciate if you
didn't reply to me directly too. Write to the list and read from the
list.

Pavel

On Thu, Jan 28, 2010 at 12:44 PM, Trapper Schuler
 wrote:
> Hi,
>
> I do not understand the question.
>
> I appreciate the information that you have provided.
>
> Thank you.
>
> Pavel Ivanov wrote:
>>
>> What's wrong with the link already given you in another thread?
>> http://www.sqlite.org/backup.html
>> Also see http://www.sqlite.org/c3ref/backup_finish.html.
>>
>>
>> Pavel
>>
>>
>>>
>>> Hello,
>>>
>>> Is there a SQLite function that can "copy" one database to another
>>> (programmatically)?
>>>
>>> I would imagine that there would be a function that could take a "From"
>>> handle and a "To" handle that could copy an entire database.
>>>
>>> A "duplicate" function should work for me as well if there is no "copy"
>>> function.
>>>
>>> Hopefully, then I could switch between types of databases if necessary
>>> since it appears you get the same type of database connection no matter
>>> what type of database you have.
>>>
>>> I am mostly just trying to understand what my options are.
>>>
>>> ==
>>> Some Relevant Links:
>>> ==
>>> http://www.sqlite.org/c3ref/funclist.html
>>> ==
>>>
>>> Thank you.
>>>
>>> ___
>>> 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 Question: Is there a SQLite function that can "copy" one database to another (programmatically)?

[Pavel Ivanov]

What's wrong with the link already given you in another thread?
http://www.sqlite.org/backup.html
Also see http://www.sqlite.org/c3ref/backup_finish.html.


Pavel

On Thu, Jan 28, 2010 at 9:43 AM, Trapper Schuler
 wrote:
> Hello,
>
> Is there a SQLite function that can "copy" one database to another
> (programmatically)?
>
> I would imagine that there would be a function that could take a "From"
> handle and a "To" handle that could copy an entire database.
>
> A "duplicate" function should work for me as well if there is no "copy"
> function.
>
> Hopefully, then I could switch between types of databases if necessary
> since it appears you get the same type of database connection no matter
> what type of database you have.
>
> I am mostly just trying to understand what my options are.
>
> ==
> Some Relevant Links:
> ==
> http://www.sqlite.org/c3ref/funclist.html
> ==
>
> Thank you.
>
> ___
> 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] SQLite Question: Is there a SQLite function that can "copy" one database to another (programmatically)?

[Trapper Schuler]

Hello,

Is there a SQLite function that can "copy" one database to another
(programmatically)?

I would imagine that there would be a function that could take a "From"
handle and a "To" handle that could copy an entire database.

A "duplicate" function should work for me as well if there is no "copy"
function.

Hopefully, then I could switch between types of databases if necessary
since it appears you get the same type of database connection no matter
what type of database you have.

I am mostly just trying to understand what my options are.

==
Some Relevant Links:
==
http://www.sqlite.org/c3ref/funclist.html
==

Thank you.

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

Re: [sqlite] SQLite Question: What is the best way to "buffer" the database in memory while still being able to save the database to disk when needed (programmatically)?

[Kees Nuyt]

On Wed, 27 Jan 2010 23:20:01 -0500, Trapper Schuler
 wrote:

>Hello,
>
>I am new to SQLite, but have experience with other database management 
>systems.  Performance is very critical in my application, but the data 
>is getting too large to keep in memory.  My application is not just a 
>database front end.  The database is just a part of the overall application.
>
>What is the best way to "buffer" the database in memory while still 
>being able to save the database to disk when needed later?

The data is too large to keep in memory, so the in-memory
database can only contain part of it.
You can attach a second, file based database to flush unused
data to disk with 
INSERT INTO filedb.tbl .. (SELECT .. FROM memdb.tbl ..) and
DELETE FROM memdb.tbl ... , but that's not a simple action.
You have to age the data and decide what to keep.

Luckily, an in-memory database is not needed for this
purpose. SQLite will maintain a cache of recently used
database pages in a smart way, with some priority for index
pages. That cache works perfectly if the database is only
used by one process, your application.


>==
>Some Relevant Links:
>==
>http://www.sqlite.org/inmemorydb.html
>http://www.sqlite.org/faq.html   (See #19 "INSERT is really slow...")
>http://www.sqlite.org/c3ref/funclist.html
>==
>
>I have read about "In-Memory Databases".
>
>I have read about "Temporary Databases".  This seems to be useful.  How 
>well does it detect "memory pressure"?  How large is a database that 
>becomes "too large"?  In my application, the database will have to share 
>memory with other parts of my application.  How soon does it detect 
>"memory pressure" before the application itself starts to use Virtual 
>Memory?

I don't think SQLite detects memory pressure. Your
application can. SQLite, the cache and housekeeping data is
in the memory footprint of your application.

The only thing you have to take care of is the size of the
cache (PRAGMA default_cache_size and/or PRAGMA cache_size).
That's deterministic.

Also, have a look at the sqlite3_memory_() functions.

>If I use a "Temporary Database" or an "In-Memory Database", is there an 
>easy way to take that database and write it to disk when I need to 
>(programmatically)?  (Is there a database copying function that will 
>take a "From" handle and a "To" handle or something similar?)  (When I 
>state "write it to disk", I want to write it to a file with a specific 
>name.)

The easy way would be the backup interface, but that saves
all of the database. And you already told us the in-memory
database can't hold all the data you need, because it is too
large. So, the easy way is not feasible.

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

Re: [sqlite] SQLite Question: What is the best way to "buffer" the database in memory while still being able to save the database to disk when needed (programmatically)?

[Igor Tandetnik]

Trapper Schuler wrote:
> If I use a "Temporary Database" or an "In-Memory Database", is there an
> easy way to take that database and write it to disk

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

Igor Tandetnik

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

Re: [sqlite] SQLite Question: What is the best way to "buffer" the database in memory while still being able to save the database to disk when needed (programmatically)?

[Pavel Ivanov]

I can't see all your requirements for this database but I believe
you'd better not use temporary or in-memory databases. You'd better
use regular database but use all kinds of tricks to speed up work with
it (like "pragma synchronous off", "pragma journal_mode off", in some
cases some tricks can be incorporated into VFS if you write your own
one). Yes, those tricks can lead to corrupted database in case of
application or OS crash or sudden power outage. But I believe you can
tolerate that and just delete old database if it is corrupted (in case
of in-memory or temporary database you wouldn't see it after crash at
all). If your application needs to be sure that all data is written
and can be restored later you can either call fsync() for the database
or make a backup copy of it (depending on what your application will
do after that and what requirements you have about possibility of
corruption later). But if you need for example only to store the data
on application's exit and then restore it on next run then with
regular database you'll get that automatically.


Pavel

On Wed, Jan 27, 2010 at 11:20 PM, Trapper Schuler
 wrote:
> Hello,
>
> I am new to SQLite, but have experience with other database management
> systems.  Performance is very critical in my application, but the data
> is getting too large to keep in memory.  My application is not just a
> database front end.  The database is just a part of the overall application.
>
> What is the best way to "buffer" the database in memory while still
> being able to save the database to disk when needed later?
>
> ==
> Some Relevant Links:
> ==
> http://www.sqlite.org/inmemorydb.html
> http://www.sqlite.org/faq.html   (See #19 "INSERT is really slow...")
> http://www.sqlite.org/c3ref/funclist.html
> ==
>
> I have read about "In-Memory Databases".
>
> I have read about "Temporary Databases".  This seems to be useful.  How
> well does it detect "memory pressure"?  How large is a database that
> becomes "too large"?  In my application, the database will have to share
> memory with other parts of my application.  How soon does it detect
> "memory pressure" before the application itself starts to use Virtual
> Memory?
>
> If I use a "Temporary Database" or an "In-Memory Database", is there an
> easy way to take that database and write it to disk when I need to
> (programmatically)?  (Is there a database copying function that will
> take a "From" handle and a "To" handle or something similar?)  (When I
> state "write it to disk", I want to write it to a file with a specific
> name.)
>
> Any information that you can provide is appreciated.
>
> Thank you.
> ___
> 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] SQLite Question: What is the best way to "buffer" the database in memory while still being able to save the database to disk when needed (programmatically)?

[Trapper Schuler]

Hello,

I am new to SQLite, but have experience with other database management 
systems.  Performance is very critical in my application, but the data 
is getting too large to keep in memory.  My application is not just a 
database front end.  The database is just a part of the overall application.

What is the best way to "buffer" the database in memory while still 
being able to save the database to disk when needed later?

==
Some Relevant Links:
==
http://www.sqlite.org/inmemorydb.html
http://www.sqlite.org/faq.html   (See #19 "INSERT is really slow...")
http://www.sqlite.org/c3ref/funclist.html
==

I have read about "In-Memory Databases".

I have read about "Temporary Databases".  This seems to be useful.  How 
well does it detect "memory pressure"?  How large is a database that 
becomes "too large"?  In my application, the database will have to share 
memory with other parts of my application.  How soon does it detect 
"memory pressure" before the application itself starts to use Virtual 
Memory?

If I use a "Temporary Database" or an "In-Memory Database", is there an 
easy way to take that database and write it to disk when I need to 
(programmatically)?  (Is there a database copying function that will 
take a "From" handle and a "To" handle or something similar?)  (When I 
state "write it to disk", I want to write it to a file with a specific 
name.)

Any information that you can provide is appreciated.

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

Re: [sqlite] SQLite: question

[Keith Roberts]

On Tue, 27 Oct 2009, Ken wrote:

> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> From: Ken <kennethinbox-sql...@yahoo.com>
> Subject: Re: [sqlite] SQLite: question
> 
>
> Sql uses a single quote or a tick mark to delimit strings. 
> C, C++ and other languages use Dobule quote to delimit 
> strings.
>
> I think though that the column names may be double quoted 
> to differentiate them from data.

Right. See http://www.sqlite.org/lang_keywords.html for 
details.

'keyword'  A keyword in single quotes is a string literal.

"keyword"  A keyword in double-quotes is an identifier.

[keyword]  A keyword enclosed in square brackets is an 
identifier. This is not standard SQL. This quoting mechanism 
is used by MS Access and SQL Server and is included in 
SQLite for compatibility.

`keyword`  A keyword enclosed in grave accents (ASCII code 
96) is an identifier. This is not standard SQL. This quoting 
mechanism is used by MySQL and is included in SQLite for 
compatibility.

*snip*

SQLite adds new keywords from time to time when it takes on 
new features. So to prevent your code from being broken by 
future enhancements, you should normally quote any 
identifier that is an English language word, even if you do 
not have to.

My 2cents - So if you only use "" double quotes for 
identifier names, and only '' single quotes for string 
constants, you should be OK for any future upgrades to 
SQLite.

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: question

[Ken]

Sql uses a single quote or a tick mark to delimit strings. C, C++ and other 
languages use Dobule quote to delimit strings.

I think though that the column names may be double quoted to differentiate them 
from data.

--- On Tue, 10/27/09, D. Richard Hipp <d...@hwaci.com> wrote:

> From: D. Richard Hipp <d...@hwaci.com>
> Subject: Re: [sqlite] SQLite: question
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Cc: "Sergiu _" <sergiu...@gmail.com>
> Date: Tuesday, October 27, 2009, 7:56 AM
> Question forwarded to the
> sqlite-users mailing list.
> 
> Quick answer:  String are quoted in SQL using single
> quotes, not  
> double-quotes.  What you are seeing is not a
> bug.  You are misusing  
> the string quoting mechanism.
> 
> On Oct 27, 2009, at 8:51 AM, Sergiu _ wrote:
> 
> > Hello,
> >
> > I use SQLite in one of my projects for quite long time
> and it looked  
> > to be a very good product.
> > Though, I think I spotted a bug, but I am not sure.
> Please confirm.
> >
> > Scenario: Create a table having at least one column of
> TEXT type  
> > (let's say "myColumn"). Insert a row, having the value
> "STATUS" on  
> > that TEXT column; Then try to select the row by using
> WHERE myColumn  
> > = "STATUS". It does not work for me.
> >
> > Could you please tell me whether this is a bug or I am
> just using it  
> > wrong ?
> > Thank you.
> >
> > Sergiu
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite: question

[D. Richard Hipp]

Question forwarded to the sqlite-users mailing list.

Quick answer:  String are quoted in SQL using single quotes, not  
double-quotes.  What you are seeing is not a bug.  You are misusing  
the string quoting mechanism.

On Oct 27, 2009, at 8:51 AM, Sergiu _ wrote:

> Hello,
>
> I use SQLite in one of my projects for quite long time and it looked  
> to be a very good product.
> Though, I think I spotted a bug, but I am not sure. Please confirm.
>
> Scenario: Create a table having at least one column of TEXT type  
> (let's say "myColumn"). Insert a row, having the value "STATUS" on  
> that TEXT column; Then try to select the row by using WHERE myColumn  
> = "STATUS". It does not work for me.
>
> Could you please tell me whether this is a bug or I am just using it  
> wrong ?
> Thank you.
>
> Sergiu

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



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

Re: [sqlite] Sqlite question: group by column with multiple tags?

[P Kishor]

On Tue, Feb 24, 2009 at 7:44 AM, Yuzem  wrote:
>
>
>
> P Kishor-3 wrote:
>> Google for "normalizing a database" and then read up on it. It will help.
>>
>
> Ok, thanks, thats the solution. Now there is another problem that arise.
> Lets say I have a table called movies with 3 columns (id, titles, keywords):
>
> 1|title1|keyword1
> 1|title1|keyword2
> 1|title1|keyword3
> 2|title2|keyword1
> 2|title2|keyword2
>
> The id column is unique, the same from imdb so if a want to add twice the
> same movie it doesn't let me.
>
> After normalization I have two tables (movies (id, titles) and keywords (id,
> keywords)):
> movies:
> 1|title1
> 2|title2
> keywords:
> 1|keyword1
> 1|keyword2
> 1|keyword3
> 2|keyword1
> 2|keyword2
>
> How do I prevent inserting the same keyword for the same movie?


Make a primary key out of the combo of keyword_id and keyword_name.
Add a constraint to the table to throw and error.

Oh, be sure to read up on a normalization tutorial as well as the docs
on sqlite.org. My advice here is hardly a substitute for either of
those.

Good luck.


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite question: group by column with multiple tags?

[Yuzem]

P Kishor-3 wrote:
> Google for "normalizing a database" and then read up on it. It will help. 
> 

Ok, thanks, thats the solution. Now there is another problem that arise.
Lets say I have a table called movies with 3 columns (id, titles, keywords):

1|title1|keyword1
1|title1|keyword2
1|title1|keyword3
2|title2|keyword1
2|title2|keyword2

The id column is unique, the same from imdb so if a want to add twice the
same movie it doesn't let me.

After normalization I have two tables (movies (id, titles) and keywords (id,
keywords)):
movies:
1|title1
2|title2
keywords:
1|keyword1
1|keyword2
1|keyword3
2|keyword1
2|keyword2

How do I prevent inserting the same keyword for the same movie?
-- 
View this message in context: 
http://www.nabble.com/Sqlite-question%3A-group-by-column-with-multiple-tags--tp22153722p22180987.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] Sqlite question: group by column with multiple tags?

[Yuzem]

Thanks for the answer.

Igor Tandetnik wrote:
> This monstrosity gives the correct answer in your specific example, but 
> it relies on there being exactly two tags per folder.
It can be any number of tags per folder.

Igor Tandetnik wrote:
> 
> Consider normalizing your database. Split into two tables - folders and 
> tags - with a one-to-many relationship between them. Then the solution 
> would be trivial.
How can I do that considering that it can be any numbers of tags?


Igor Tandetnik wrote:
> 
> select
> (select count(*) from (select distinct folder from t1)),
> (select count(*) from (
> select replace(rtrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') 
> from t1
> union
> select replace(ltrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') 
> from t1));
> 
> Same caveat applies.
Ok, this answer my question at least for normal columns. Thanks.

How should I do to store tags in my database and been able to count them? Do
you know how other programs like firefox do this?
-- 
View this message in context: 
http://www.nabble.com/Sqlite-question%3A-group-by-column-with-multiple-tags--tp22153722p22159627.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] Sqlite question: group by column with multiple tags?

[Igor Tandetnik]

"Yuzem"  wrote in
message news:22153722.p...@talk.nabble.com
> Suppose that have 2 columns: folders and tags:
> ~/Music|classic,rock
> ~/Music|classic,rock
> ~/Pictures|art,photos
> ~/Pictures|art,photos
> ~/Pictures|art,photos
>
> To know the folder count I do:
> sqlite3 test.db  "select folder, count(folders) from t1 group by
> folder"
>
> Returns:
> ~/Music|2
> ~/Pictures|3
>
> How can I do the same for tags using only sqlite to get this:
> art|3
> classic|2
> photos|3
> rock|2

Consider normalizing your database. Split into two tables - folders and 
tags - with a one-to-many relationship between them. Then the solution 
would be trivial.

If you insist on keeping the database as is:

select tag, count(*)
from
(select replace(rtrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') as tag 
from t1
 union
 select replace(ltrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') as tag 
from t1) as alltags
join t1 on t1.tags like '%' || tag || '%'
group by tag;

This monstrosity gives the correct answer in your specific example, but 
it relies on there being exactly two tags per folder.

> Another question, is there any way to get max count for all grouped
> columns in one single consult?
> It should return something like this:
> 2|4
>
> Meaning that the first column has 2 unique values (~/Music and
> ~/Pictures) and the second column has 4 (art, classic. photos and
> rock)

The second column doesn't have 4 unique values - it has two: 
'classic,rock' and 'art,photos'. A comma in the middle of a field value 
has no special meaning in SQL - it doesn't magically turn one value into 
two. Anyway:

select
(select count(*) from (select distinct folder from t1)),
(select count(*) from (
select replace(rtrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') 
from t1
union
select replace(ltrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') 
from t1));

Same caveat applies.

Igor Tandetnik 



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

[sqlite] Sqlite question: group by column with multiple tags?

[Yuzem]

Suppose that have 2 columns: folders and tags:
~/Music|classic,rock
~/Music|classic,rock
~/Pictures|art,photos
~/Pictures|art,photos
~/Pictures|art,photos

To know the folder count I do:
sqlite3 test.db  "select folder, count(folders) from t1 group by folder"

Returns:
~/Music|2
~/Pictures|3

How can I do the same for tags using only sqlite to get this:
art|3
classic|2
photos|3
rock|2

Another question, is there any way to get max count for all grouped columns
in one single consult?
It should return something like this:
2|4

Meaning that the first column has 2 unique values (~/Music and ~/Pictures)
and the second column has 4 (art, classic. photos and rock)

Many thanks in advance! 
-- 
View this message in context: 
http://www.nabble.com/Sqlite-question%3A-group-by-column-with-multiple-tags--tp22153722p22153722.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] Sqlite question---->how to impose order on query

[Jay A. Kreibich]

On Thu, Apr 10, 2008 at 09:56:31PM -0500, Dewey Gaedcke scratched on the wall:

> Set @a = 0;
> Select T1.rownumber, T1.Col2 from
>   (
>   Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2
>   from Table where Col1 = 'abc' Order by Col1
>   ) as T1
> Where T1.rownumber between 21 and 30;

  As I understand it, most systems are going to do a full table scan
  for that sub-select.  You're then using the outer select and
  the rownumber to essentially implement an LIMIT/OFFSET.

  If my assumptions about the full table scan are correct, this is
  extremely inefficient-- but if it works, it works.  If you're getting
  the kind of performance you need, I think it would be much easier
  to just get rid of the outer select and put a LIMIT/OFFSET directly
  on the subselect.  For example, the query above would turn into this:

SELECT Col2 FROM Table WHERE Col1 = 'abc' ORDER BY Col1 LIMIT 10 OFFSET 20;

  This is still pretty inefficient when the offset starts to get large,
  but at least the query will terminate as soon as the limit is hit.
  I don't think that's the case for the sub-select you've got setup.
  So this is not the best, but it is still better than the original.

  (I also assume this is a contrived example, because as-written the
  ORDER BY clause is pointless thanks to the WHERE clause.)

   -j

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

"'People who live in bamboo houses should not throw pandas.' Jesus said that."
   - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite question---->how to impose order on query

[Dewey Gaedcke]

Dennis,
Thanks for the info/link.if we must go this route
(http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor), we will, but I'd
much rather pay someone to write a simple embedded "sequence" function for
us because we've got almost 10 (very complex) queries in mySQL that use the
example in my earlier post (see below).  We want to maintain the queries as
close as possible to each other because the user needs to see the same
results and not know the difference between running the query on the server
or the (sqlite) client.

Is it possible to find someone for hire to implement a "sequence" function
so I don't have to rework all this query logic and distract my C++
programmer with the example implementation that you've provided??

Thanks again for responding!!
Dewey

We would replace "@a:[EMAIL PROTECTED]" in the query below with "f_sequence()" 
to
generate a new relative rownum for each record found.  Or more correctly,
since the "Order by" execution phase of sqlite may not match mySQL, perhaps
the function would best go in the outer query.in either case, is someone
willing to build this for us:
Query:
Set @a = 0;
Select T1.rownumber, T1.Col2 from
(
Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2
from Table where Col1 = 'abc' Order by Col1
) as T1
Where T1.rownumber between 21 and 30;

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.10/1367 - Release Date: 4/9/2008
7:10 AM
 

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

Re: [sqlite] Sqlite question---->how to impose order on query result for paging

[Dennis Cote]

Dewey Gaedcke wrote:
> 
> For example, if my query returns 80 rows, I'd like an extra column holding
> sequential values 1 to 80 to help with paging
> 

Check out this information page 
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

It explains how to do paging efficiently in SQLite.

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

[sqlite] Sqlite question---->how to impose order on query result for paging

[Dewey Gaedcke]

Hello,
I hope this is the right place to get sqlite help.

I'm trying to figure out how to generate a relative row # in a sqlite query
result>(in native SQL if possible---I'm not a C++ guy).

For example, if my query returns 80 rows, I'd like an extra column holding
sequential values 1 to 80 to help with paging

Suppose each page displays 10 records, to get page 3, in mySQL, I can use
the @a session variable with a materialized view like this:

Set @a = 0;
Select T1.rownumber, T1.Col2 from
(
Select @a:[EMAIL PROTECTED] as rownumber, Col1, Col2
from Table where Col1 = 'abc' Order by Col1
) as T1
Where T1.rownumber between 21 and 30; -- gets only records for page 3

If this is not possible via the sql syntax, has someone written (or willing
to write) a "sequence" function that would do the same thing so we don't
have to do it ourselves in C??

Thanks in advance for any help,
Dewey

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.10/1367 - Release Date: 4/9/2008
7:10 AM
 

No virus found in this outgoing message.
Checked by AVG. 
Version: 7.5.519 / Virus Database: 269.22.10/1367 - Release Date: 4/9/2008
7:10 AM
 

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