[sqlite] wal and shm files

2011-02-11 Thread Sam Carleton 
Just wondering, are the wal and shm files suppose to stick around after the
process exits?

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

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

2011-02-11 Thread Jim Wilcoxson 
On Fri, Feb 11, 2011 at 10:50 PM, Thomas Fjellstrom <
tfjellst...@strangesoft.net> wrote:

> I've seen numbers on the internet stating that sqlite is super fast, should
> be
> able to handle tens of thousands of inserts or updates in a second or two,
> even in sync mode. So I'm a bit stumped as to why its performance is so low
> in
> my case. One time, I saw it take 5 seconds to flush almost 3k rows back to
> the
> db.
>
> No hosts are actually ever removed from the database, which has about 120k
> hosts in it by now, totaling up to a file size of around 7-8MB total. So
> its
> not a lot of data, and the queries are pretty simple. So I really am
> stumped.
>

You mentioned your db is 8mb.  If you are using the default page size of 1k,
that means you have 8k pages in your db.  The default cache size is 2000
pages, so your db doesn't fit into SQLite's default cache.  I'd suggest
using pragma page_size=4096.  This will have the effect of increasing your
cache size from 2mb to 8mb and prevent cache overflow.  You might want to
use pragma cache_size=3000 as a safety margin.  This would be a 12MB cache
with 4KB pages.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

2011-02-11 Thread Thomas Fjellstrom 
On February 11, 2011, Teg wrote:
> Hello Thomas,
> 
> I download off the net at between 400Mbps to 1 Gbps and have to pack
> the data away into a Sqlite DB. I use an intermediate buffer on disk
> and a background thread to write the data to the DB. Obviously, I
> couldn't keep up if the data was continuous but, the lulls in between
> the peak data rate times give me time to pack it away. Even if I get
> an extended burst of data, it'll eventually make it in. If I was you,
> I'd start by having something service the interface and buffer the
> packet data and something else pulling the packet data from the buffer
> and feeding it to Sqlite. Then you won't be dropping packets.

The program is split into two separate threads, one is dedicated to just 
capturing packets off the network, and storing them in a circular buffer in 
memory. The second thread is dedicated to parsing the packets, calculating 
stats, and occasionally flushing data to storage.

What normally happens, is as hosts haven't been seen for a while, they get 
saved to the db, and are removed from the in memory hash table. But that 
doesn't happen for local hosts, the fake host used to capture traffic totals, 
or 
any external hosts that keep showing up. And temporarily I've added some code 
to auto save all in memory hosts to the database, as I'm seeing some 
instability in the interface with sqlite, causing the process to loose 
information once in a while.

I've seen numbers on the internet stating that sqlite is super fast, should be 
able to handle tens of thousands of inserts or updates in a second or two, 
even in sync mode. So I'm a bit stumped as to why its performance is so low in 
my case. One time, I saw it take 5 seconds to flush almost 3k rows back to the 
db.

No hosts are actually ever removed from the database, which has about 120k 
hosts in it by now, totaling up to a file size of around 7-8MB total. So its 
not a lot of data, and the queries are pretty simple. So I really am stumped.

> 
> T
> 
> Friday, February 11, 2011, 6:49:16 PM, you wrote:
> 
> TF> Hi, I have a small problem with a program I've developed.
> 
> TF> It basically captures packets from a network device on linux and stores
> TF> transfer stats on each host seen. To store the stats long term I
> decided to TF> use sqlite, and when the program saves the stats every few
> minutes, it takes TF> about 4-5 seconds, and if I have the synchronous
> pragma turned off, it takes TF> 1-2 seconds.
> 
> TF> These are the relevant sql commands:
> 
> TF> CREATE TABLE hosts (id INTEGER PRIMARY KEY,\
> TF>   address INTEGER UNIQUE, \
> TF>   first_seen INTEGER DEFAULT CURRENT_DATETIME, \
> TF>   last_on INTEGER DEFAULT CURRENT_DATETIME, \
> TF>   last_off INTEGER, \
> TF>   rx_bytes INTEGER, \
> TF>   tx_bytes INTEGER);
> 
> TF> UPDATE hosts SET last_on = ?, last_off = ?, rx_bytes = ?, tx_bytes = ?
> WHERE id TF> = ?
> TF> INSERT INTO hosts ( address, first_seen, last_on, rx_bytes, tx_bytes )
> VALUES ( TF> ?, ?, ?, ?, ? )
> 
> TF> The stats saving code will interleave a bunch of UPDATEs and INSERTs,
> inside a TF> single transaction, maybe that has something to do with it,
> I'm not sure.
> 
> TF> 1s to update them all isn't too bad, but it still can mean I'm
> potentially TF> dropping packets, which I'd really rather not do.
> 
> TF> Thanks.


-- 
Thomas Fjellstrom
tfjellst...@strangesoft.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

2011-02-11 Thread Teg 
Hello Thomas,

I download off the net at between 400Mbps to 1 Gbps and have to pack
the data away into a Sqlite DB. I use an intermediate buffer on disk
and a background thread to write the data to the DB. Obviously, I
couldn't keep up if the data was continuous but, the lulls in between
the peak data rate times give me time to pack it away. Even if I get
an extended burst of data, it'll eventually make it in. If I was you,
I'd start by having something service the interface and buffer the
packet data and something else pulling the packet data from the buffer
and feeding it to Sqlite. Then you won't be dropping packets.


T

Friday, February 11, 2011, 6:49:16 PM, you wrote:

TF> Hi, I have a small problem with a program I've developed.

TF> It basically captures packets from a network device on linux and stores
TF> transfer stats on each host seen. To store the stats long term I decided to
TF> use sqlite, and when the program saves the stats every few minutes, it takes
TF> about 4-5 seconds, and if I have the synchronous pragma turned off, it takes
TF> 1-2 seconds.

TF> These are the relevant sql commands:

TF> CREATE TABLE hosts (id INTEGER PRIMARY KEY,\
TF>   address INTEGER UNIQUE, \
TF>   first_seen INTEGER DEFAULT CURRENT_DATETIME, \
TF>   last_on INTEGER DEFAULT CURRENT_DATETIME, \
TF>   last_off INTEGER, \
TF>   rx_bytes INTEGER, \
TF>   tx_bytes INTEGER);

TF> UPDATE hosts SET last_on = ?, last_off = ?, rx_bytes = ?, tx_bytes = ? 
WHERE id
TF> = ?
TF> INSERT INTO hosts ( address, first_seen, last_on, rx_bytes, tx_bytes ) 
VALUES (
TF> ?, ?, ?, ?, ? )

TF> The stats saving code will interleave a bunch of UPDATEs and INSERTs, 
inside a
TF> single transaction, maybe that has something to do with it, I'm not sure.

TF> 1s to update them all isn't too bad, but it still can mean I'm potentially
TF> dropping packets, which I'd really rather not do.

TF> Thanks.


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

Re: [sqlite] Speed up count(distinct col)

2011-02-11 Thread BareFeetWare 
On 11/02/2011, at 11:51 PM, Yuzem wrote:

>> you want the number of each unique (capacity, person). No problem. We'll 
>> just add a People column to the statistics, and change the uniqueness 
>> constraint.

Hmm, seems I didn't quite change the uniqueness constraint. I meant to allow 
multiple capacities and multiple people, but keep each combination of 
(capacity, person) unique. So we need a Capacity_ID column and to use it 
instead of ID when inserting. The schema should therefore be:

begin immediate
;
drop table if exists "Capacity People Statistics"
;
create table "Capacity People Statistics"
(   ID integer primary key not null
,   Capacity_ID integer not null references "Capacity" (ID) on delete 
cascade
,   People_ID integer not null references "People" (ID) on delete cascade
,   Count integer not null
,   unique (Capacity_ID, People_ID)
)
;
insert into "Capacity People Statistics" (Capacity_ID, People_ID, Count)
select Capacity_ID, People_ID, count(*) from "Movie People"
group by Capacity_ID, People_ID having Count > 0
;
drop trigger if exists "Movie People insert"
;
create trigger "Movie People insert"
on "Movie People"
after insert
begin
insert or replace into "Capacity People Statistics" (Capacity_ID, People_ID, 
Count)
select
new.Capacity_ID
,   new.People_ID
,   (   select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where Capacity_ID = new.Capacity_ID and People_ID = 
new.People_ID
)
;
end
;
drop trigger if exists "Movie People delete"
;
create trigger "Movie People delete"
on "Movie People"
after delete
begin
insert or replace into "Capacity People Statistics" (Capacity_ID, People_ID, 
Count)
select
old.Capacity_ID
,   old.People_ID
,   (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where Capacity_ID = old.Capacity_ID and People_ID = 
old.People_ID
)
;
end
;
drop trigger if exists "Movie People update"
;
create trigger "Movie People update"
on "Movie People"
after update of Capacity_ID, People_ID
begin
insert or replace into "Capacity People Statistics" (Capacity_ID, People_ID, 
Count)
select
old.Capacity_ID
,   old.People_ID
,   (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where Capacity_ID = old.Capacity_ID and People_ID = 
old.People_ID
)
;
insert or replace into "Capacity People Statistics" (Capacity_ID, People_ID, 
Count)
select
new.Capacity_ID
,   new.People_ID
,   (   select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where Capacity_ID = new.Capacity_ID and People_ID = 
new.People_ID
)
;
end
;
commit
;

What is your source for the data? If I have that, I can test my schema 
properly, rather than sitting here entering a pile of dummy data. I looked at 
IMDB, but they only seem to have a paid license download.

> That should work, if I understand it correctly now I can count directors with:
> SELECT count(*) FROM "Capacity People Statistics" WHERE ID = 'directors';

No. Sorry, I should have included that. You count directors like this:

select sum(Count) from "Capacity People Statistics"
where Capacity_ID = (select ID from Capacity where Name = 'director')
;

or count how many times a particular person is involved in movies:

select sum(Count) from "Capacity People Statistics"
where People_ID = (select ID from People where Name = 'Clint Eastwood')
;

or count the number of times a particular person is a director (notice no sum 
needed):

select Count from "Capacity People Statistics"
where Capacity_ID = (select ID from Capacity where Name = 'director')
and People_ID = (select ID from People where Name = 'Clint Eastwood')
;

> It is similar to using different tables with only the IDs.
> Instead of using a table for directors another for writers, etc... this is
> specified in a column, it is slower but it uses only one table.

I don't think you'll find a noticeable speed difference. It's searching just 
indexed primary key integer columns, which is very fast. It is optimized (with 
auto indexes) for searching by Capacity_ID, People_ID in that order, and 
searching by Capacity_ID alone. If you also do a lot of searches by People_ID 
alone, then add an index:

create index "Capacity People Statistics - People"
on "Capacity People Statistics" (People_ID)
;

> If I use different tables the result is instant, I don't know if it will be 
> much faster to count from "Capacity People Statistics" than counting from
> "Movie People".

I expect the above to be about the same speed or faster (since part of the 
counting is already done) than separate tables, but far more flexible (eg no 
need to add a table to accommodate a new capacity), and better normalized.

> Another thing: I don't understand the purpose of the Count column in table
> "Capacity People Statistics"

It hopefully now makes sense with my correction and example

[sqlite] UPDATE/INSERTing 1-2k rows slower than expected

2011-02-11 Thread Thomas Fjellstrom 
Hi, I have a small problem with a program I've developed.

It basically captures packets from a network device on linux and stores 
transfer stats on each host seen. To store the stats long term I decided to 
use sqlite, and when the program saves the stats every few minutes, it takes 
about 4-5 seconds, and if I have the synchronous pragma turned off, it takes 
1-2 seconds.

These are the relevant sql commands:

CREATE TABLE hosts (id INTEGER PRIMARY KEY,\
  address INTEGER UNIQUE, \
  first_seen INTEGER DEFAULT CURRENT_DATETIME, \
  last_on INTEGER DEFAULT CURRENT_DATETIME, \
  last_off INTEGER, \
  rx_bytes INTEGER, \
  tx_bytes INTEGER);

UPDATE hosts SET last_on = ?, last_off = ?, rx_bytes = ?, tx_bytes = ? WHERE id 
= ?
INSERT INTO hosts ( address, first_seen, last_on, rx_bytes, tx_bytes ) VALUES ( 
?, ?, ?, ?, ? )

The stats saving code will interleave a bunch of UPDATEs and INSERTs, inside a 
single transaction, maybe that has something to do with it, I'm not sure.

1s to update them all isn't too bad, but it still can mean I'm potentially 
dropping packets, which I'd really rather not do.

Thanks.

-- 
Thomas Fjellstrom
tfjellst...@strangesoft.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

2011-02-11 Thread Jim Morris 
I tihnk the firefox plug-in Sqlite Manager does.

On 2/11/2011 2:30 PM, Simon Slavin wrote:
> On 11 Feb 2011, at 7:19pm, prad wrote:
>
>> does the sqlite3 interface have completion of sql key words?
>> in postgresql you can type SEL and press tab to complete.
>> is there such a thing for sqlite3?
> sqlite3 doesn't have an interface.  It is only a programming API.
>
> Although you can download a command-line tool for sqlite3 from the sqlite3 
> web site, it's provided just for convenience and many people don't use it (or 
> even know about it).
>
> Simon.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] completion of sql words

2011-02-11 Thread Simon Slavin 

On 11 Feb 2011, at 7:19pm, prad wrote:

> does the sqlite3 interface have completion of sql key words?
> in postgresql you can type SEL and press tab to complete.
> is there such a thing for sqlite3?

sqlite3 doesn't have an interface.  It is only a programming API.

Although you can download a command-line tool for sqlite3 from the sqlite3 web 
site, it's provided just for convenience and many people don't use it (or even 
know about it).

Simon.

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

[sqlite] BUG: capi3e.test when compiled with SQLITE_OMIT_UTF16

2011-02-11 Thread Noah Hart 

capi3e.test needs 
  ifcapable utf16 logic before capi3e-2.1.$i 
to properly pass tests when compiled with SQLITE_OMIT_UTF16

~ Noah Hart


-- 
View this message in context: 
http://old.nabble.com/BUG%3A-capi3e.test-when-compiled-with-SQLITE_OMIT_UTF16-tp30905474p30905474.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] completion of sql words

2011-02-11 Thread prad 
does the sqlite3 interface have completion of sql key words?
in postgresql you can type SEL and press tab to complete.
is there such a thing for sqlite3?

-- 
in friendship,
prad

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

Re: [sqlite] upgrading DB from 3.6.23 to 3.7.5

2011-02-11 Thread Sam Carleton 
Ok, it looks like it is my environment, I just created a little .Net test
program and it worked fine.  Maybe I have the other process running already,
though I didn't think that was the case.  I am off to do some more digging.

On Fri, Feb 11, 2011 at 9:14 AM, Dan Kennedy  wrote:

> On 02/11/2011 08:08 PM, Sam Carleton wrote:
> > On Fri, Feb 11, 2011 at 6:54 AM, Philip Graham Willoughby<
> > phil.willoug...@strawberrycat.com>  wrote:
> >
> >> Hi Sam,
> >>
> >> On 11 Feb 2011, at 05:29, Sam Carleton wrote:
> >>> I am sure it is bad form, but attached is one of the 3.6.23 DB, it is
> >> only
> >>> 12K.
> >>
> >> The mailing list software strips attachments; can you share it on
> dropbox
> >> (free account here: http://www.dropbox.com/referrals/NTEwMzQxNDc1OQ )
> or
> >> some similar service?
> >>
> >
> > dropbox is very handy, but I don't like now their public share does NOT
> have
> > a web page front end, it is just a link that start downloading the file.
> > Here is a link to the file via yousendit.com, it has a bit nicer
> > presentation:)  Thanks for the help!
> >
> > https://www.yousendit.com/download/MzZHT213TXZtUUh2Wmc9PQ
>
> This database works fine with 3.7.5 here. What error are you
> getting?
> ___
> 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] upgrading DB from 3.6.23 to 3.7.5

2011-02-11 Thread Dan Kennedy 
On 02/11/2011 08:08 PM, Sam Carleton wrote:
> On Fri, Feb 11, 2011 at 6:54 AM, Philip Graham Willoughby<
> phil.willoug...@strawberrycat.com>  wrote:
>
>> Hi Sam,
>>
>> On 11 Feb 2011, at 05:29, Sam Carleton wrote:
>>> I am sure it is bad form, but attached is one of the 3.6.23 DB, it is
>> only
>>> 12K.
>>
>> The mailing list software strips attachments; can you share it on dropbox
>> (free account here: http://www.dropbox.com/referrals/NTEwMzQxNDc1OQ ) or
>> some similar service?
>>
>
> dropbox is very handy, but I don't like now their public share does NOT have
> a web page front end, it is just a link that start downloading the file.
> Here is a link to the file via yousendit.com, it has a bit nicer
> presentation:)  Thanks for the help!
>
> https://www.yousendit.com/download/MzZHT213TXZtUUh2Wmc9PQ

This database works fine with 3.7.5 here. What error are you
getting?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] upgrading DB from 3.6.23 to 3.7.5

2011-02-11 Thread Sam Carleton 
On Fri, Feb 11, 2011 at 6:54 AM, Philip Graham Willoughby <
phil.willoug...@strawberrycat.com> wrote:

> Hi Sam,
>
> On 11 Feb 2011, at 05:29, Sam Carleton wrote:
> > I am sure it is bad form, but attached is one of the 3.6.23 DB, it is
> only
> > 12K.
>
> The mailing list software strips attachments; can you share it on dropbox
> (free account here: http://www.dropbox.com/referrals/NTEwMzQxNDc1OQ ) or
> some similar service?
>

dropbox is very handy, but I don't like now their public share does NOT have
a web page front end, it is just a link that start downloading the file.
Here is a link to the file via yousendit.com, it has a bit nicer
presentation:)  Thanks for the help!

https://www.yousendit.com/download/MzZHT213TXZtUUh2Wmc9PQ

File expiration date: *permanently* *February 18, 2011*

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

Re: [sqlite] Speed up count(distinct col)

2011-02-11 Thread Yuzem 


BareFeetWare-2 wrote:
> 
> Oh, I see. I was counting the total number of each unique capacity
> (including director, writers), but you want the number of each unique
> (capacity, person). No problem. We'll just add a People column to the
> statistics, and change the uniqueness constraint.
> 
> Replace my earlier "Capacity Statistics" and triggers with this:
> 

That should work, if I understand it correctly now I can count directors
with:
SELECT count(*) FROM "Capacity People Statistics" WHERE ID = 'directors';
Instead of:
SELECT count(distinct People_ID) FROM "Movie People" WHERE Capacity_ID =
'directors';

It is similar to using different tables with only the IDs.
Instead of using a table for directors another for writers, etc... this is
specified in a column, it is slower but it uses only one table.
If I use different tables the result is instant, I don't know if it will be
much faster to count from "Capacity People Statistics" than counting from
"Movie People".

Another thing: I don't understand the purpose of the Count column in table
"Capacity People Statistics"


Simon Slavin-3 wrote:
> 
> So if I deleted one record and created another you wouldn't spot it ?
> 
Yes, any change to the database will result in a complete refresh of the
cache.


Simon Slavin-3 wrote:
> 
> Your process is trustworthy only when you are certain that the database
> file is not currently open.  If there's a chance that some application may
> be modifying the file when you check these things then the results you get
> may not be up-to-date. 
> 
Yes but what can I do about it, the same happens if I open a text document
that it is already open in another text editor.
I could check if there is a journal file but I don't know if it is necessary
since I am not keeping any connection open, I try to make the connections as
short as possible and if I get some data that it isn't up to date I don't
see much problem.

Something that I just realized, comparing INTEGER columns doesn't seems
faster than comparing TEXT columns, this:
SELECT count(distinct ROWID) FROM keywords;
Result: 83513
Is slower than this:
SELECT count(distinct keywords) FROM keywords;
Result: 17321
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30900999.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] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-11 Thread Edzard Pasma 
Op 11-feb-2011, om 10:41 heeft Nißl Reinhard het volgende geschreven:

> Hi Jay,
>
> I understand that it is not possible to define what the correct  
> output is.
>
> But what I am asking for is a simple convention which doesn't  
> behave more badly in general (i. e. it's still database behavior)  
> but much better in my case and most likely for many cases of other  
> users too.
>
> The convention is to use the dequoted column identifier as column  
> name in case the column expression translates to a single identifier.
>
> You have to dequote the identifier anyway to find the column in the  
> subselect to which this expression refers to.
>
> So all I'm asking for is to change the default database behavior to  
> yield more obvious or consistent default column names. In case the  
> database behavior doesn't fit, one has to use the AS clause anyway.
>
> For the below mentioned join, sqlite3 currently behaves like that:
>
>   select [x].[a], [y].[a] from x join x y on x.a = y.a;
>
>   a|a
>   1|1
>
> Hence, it simply uses the column names. And the next statement does  
> that too:
>
>   select [x].[a] from x;
>
>   a
>   1
>
> So in my opinion the default behavior of the database should be to  
> yield the same column name even for this statement:
>
>   select [x].[a] from (select a from x) x;
>
> But it currently returns:
>
>   [x].[a]
>   1
>
> I'd like to create a patch which changes the behavior in that way,  
> but I'm not that used to the sqlite3 internals. From a quick glance  
> at the source, I think it has something to do with TK_COLUMN and  
> TK_VARIABLE. It would be nice if you could give me a pointer where  
> to place the change in the source code.
>
> Bye.
> --
> Reinhard Nißl

Hi,

The engine already determines unquoted column names when creating  
views. This also applies with inline-vies. For instance:

sqlite>.mode line
sqlite> select * from (select [x].[a] from (select a from x) x);
 a = 123

This also cuts off the unnecessary table alias.

If column descriptions could be changed to be like this by default?

Edzard Pasma


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

Re: [sqlite] upgrading DB from 3.6.23 to 3.7.5

2011-02-11 Thread Philip Graham Willoughby 
Hi Sam,

On 11 Feb 2011, at 05:29, Sam Carleton wrote:
> I am sure it is bad form, but attached is one of the 3.6.23 DB, it is only
> 12K.

The mailing list software strips attachments; can you share it on dropbox (free 
account here: http://www.dropbox.com/referrals/NTEwMzQxNDc1OQ ) or some similar 
service?

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

2011-02-11 Thread Nißl Reinhard 
Hi Jay,

I understand that it is not possible to define what the correct output is.

But what I am asking for is a simple convention which doesn't behave more badly 
in general (i. e. it's still database behavior) but much better in my case and 
most likely for many cases of other users too.

The convention is to use the dequoted column identifier as column name in case 
the column expression translates to a single identifier.

You have to dequote the identifier anyway to find the column in the subselect 
to which this expression refers to.

So all I'm asking for is to change the default database behavior to yield more 
obvious or consistent default column names. In case the database behavior 
doesn't fit, one has to use the AS clause anyway.

For the below mentioned join, sqlite3 currently behaves like that:

select [x].[a], [y].[a] from x join x y on x.a = y.a;

a|a
1|1

Hence, it simply uses the column names. And the next statement does that too:

select [x].[a] from x;

a
1

So in my opinion the default behavior of the database should be to yield the 
same column name even for this statement:

select [x].[a] from (select a from x) x;

But it currently returns:

[x].[a]
1

I'd like to create a patch which changes the behavior in that way, but I'm not 
that used to the sqlite3 internals. From a quick glance at the source, I think 
it has something to do with TK_COLUMN and TK_VARIABLE. It would be nice if you 
could give me a pointer where to place the change in the source code.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Jay A. Kreibich
Gesendet: Donnerstag, 10. Februar 2011 17:05
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

On Thu, Feb 10, 2011 at 09:35:04AM -0600, Puneet Kishor scratched on the wall:

> that code would be very complex to cover all the possible cases. The
> simplest solution is to depend upon AS aliasing

  To be clear, that's not an excuse the development team is using
  to avoid writing a hard bit of code.  The SQL standard leaves column
  names undefined in the absence of a column alias (e.g. "AS" phrase).
  In other words, the database is free to do its best, but it is really
  up to the developer to strictly define names, via AS, if the names are
  relevant (i.e. used in code).  (The wisdom of using names as column
  identifiers is a whole different argument.)

  Consider your own example.  Is "a" really the correct output?  What
  about "x.a"?  Or "main.x.a"?  If you feel the need to quote a column
  name, such as "[a]", why shouldn't the database feel it is proper to
  quote it back at you? 
  
  What if there is both an "x.a" and a "y.a" column from an "x JOIN y"
  operation?  Should the columns be "a" and "a", or should they
  promoted to be more specific?  What about a sub-select that has an
  "a AS a" output specification, where it is an alias that just
  happens to be the same as a column, but it is no longer a
  source-column reference?  What about "a+1 AS a" where any
  source-column association (and therefore table and database
  association) is specifically broken?

  For almost any naming scheme one can come up with, it is fairly
  easy to find odd edge cases that add dozens of extra "but",
  "unless", "except" rules to your naming convention.  Your rule set
  quickly becomes so huge and fragile, you might as well treat the
  naming convention as undefined.  And, of course, the naming rules
  would be product-specific (Some DBs have schema name-spaces, some
  don't.  Some have table-spaces, some don't.  Some can access multiple
  databases, some can't.), meaning every database is going to do it
  differently anyways-- which is exactly why it isn't in the standard.

   -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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users