[sqlite] wal and shm files
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
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
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
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)
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
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
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
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
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
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
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 Kennedywrote: > 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
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
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)
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
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
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
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