[sqlite] Automatic column aliasing, SELECT vs VALUES
Most interestingly, a case of duplicate column names check that seems to
have slipped through the net allowing a table to have three columns
named the same, but only if they're empty and the user has used a
different delimiter set each time (which by the way is discouraged and
we should stick to ""):
CREATE TABLE x ("" int, '' int, [] int);
Also interesting is that if you now create another table based on this
using the CREATE-SELECT syntax, it will have column names "", ":2", ";3"
to substitute the three empty ones, just as VALUES() seemed to do
before, although in this case there is no VALUES:
CREATE TABLE y AS SELECT * FROM x;
- Original message -
From: Jean Chevalier
To: sqlite-users at mailinglists.sqlite.org
Subject: Automatic column aliasing, SELECT vs VALUES
Date: Wed, 17 Jun 2015 22:07:47 +0200
I read that VALUES(expr-list) means the same as SELECT(expr-list), but
apparently not with regards to the metadata that's sent out along with
the values.
If I issue these two commands, in one case I end up with column names
"", ":1", ":2" (sequential), and in the other I end up with "1", "2",
"1000" (representational):
CREATE TABLE a AS VALUES (1,2,1000);
CREATE TABLE b AS SELECT 1,2,1000;
I'm not advocating that someone uses this table creation mechanism and
then relies on the column names, as I reckon that that's probably
unsupported, but shouldn't the two above match?
Shouldn't they also match to do whatever the SELECT already does, since
the VALUES option is probably newer and fewer people would be relying on
its behaviour already, also because an empty column name for the first
column is kind of an irregularity, even though SQLite allows commands
such as CREATE TABLE "" (""); without complaining.
[sqlite] Automatic column aliasing, SELECT vs VALUES
I read that VALUES(expr-list) means the same as SELECT(expr-list), but
apparently not with regards to the metadata that's sent out along with
the values.
If I issue these two commands, in one case I end up with column names
"", ":1", ":2" (sequential), and in the other I end up with "1", "2",
"1000" (representational):
CREATE TABLE a AS VALUES (1,2,1000);
CREATE TABLE b AS SELECT 1,2,1000;
I'm not advocating that someone uses this table creation mechanism and
then relies on the column names, as I reckon that that's probably
unsupported, but shouldn't the two above match?
Shouldn't they also match to do whatever the SELECT already does, since
the VALUES option is probably newer and fewer people would be relying on
its behaviour already, also because an empty column name for the first
column is kind of an irregularity, even though SQLite allows commands
such as CREATE TABLE "" (""); without complaining.
[sqlite] Mozilla wiki 'avoid SQLite'
On 2015-06-17 9:50 PM, david at andl.org wrote: > The question for now is: does a new database programming language have a > place? When you ask the question as broadly as that, the answer is most definitely "yes". Just look at the wider world and you see there are dozens of application programming languages that have widespread use (and hundreds more that are more niche), and new ones that become widespread are appearing at a rate of around 1 a year on average, such as Swift and Go. The fact that these catch on means there are large numbers of people who think that there are worthwhile new languages, that don't just think we already have all the ones we need. I look at the database world in contrast, that is languages that are particularly savvy for the relational model like SQL, and I notice the world is largely passing it by in attempts to make a serious alternative. I also believe the world is ripe to have SQL alternatives, its just a matter of ones appearing that are compelling to users for real work and not just an academic exercise. The fact we're still generally with SQL means this hasn't happened yet, but that doesn't mean it won't. -- Darren Duncan
[sqlite] Possible bug in shell .open command?
The aftermath... dir *. /x /b h8 08 @_8 0o8 hN Q N xa8 b8 0f8 10 soubor?, 14,336 bajt?
[sqlite] Possible bug in shell .open command?
I took that the .open command could be issued as ".open" to open a new in-memory database and ".open ''" (followed by a pair of single quotes) to open a new unnamed temporary file database. I wonder what is going on here: After issuing a short combination of these commands with/without putting anything into the databases, or saving them, it ends up printing an error "unable to open database ; unable to open file."? Would the printing of gibberish mean is trying to read a file name where there isn't one, i.e., reading a memory address that wasn't previously written to? To reproduce, save the following as a script and invoke the shell with the init clause to call the script.? If you see no error, quit the shell and try again, you should soon see it.? Notice it's not a problem of the init clause since the same happens if I type this interactively. .open '' .open .open create table x(y); .open '' .open '' .open .open This would not be a problem unless someone could potentially write a function as part of an API that would attempt to reset the database prior to doing some other work, a reset sometimes unnecessarily but not expected to be harmful.? Then repeated calls to a db open command that normally doesn't cause a problem on its own could cause the above problem.
[sqlite] Problems in reading a record containing a blob
I have created a table to store images. The columns are file name and file content. FileContent field is the blob. I am using Samsung Tablet with Android Version 4.4.2. Most of the tablets that I tested the software in, it works fine. However, on one specific Android table I am having problems. I have given the description of the problem below: I am able to store the image in the blob field. However, while attempting to read, I get the following error two times (as seen in the LogCat entries): W/CursorWindow(26737): Window is full: requested allocation 3184896 bytes, free space 2096605 bytes, window size 2097152 bytes Then the following error: E/CursorWindow(26737): Failed to read row 0, column 0 from a CursorWindow which has 0 rows, 7 columns. NOTES: * A walkthrough has been done on the code to ensure that there are no issues in closing the cursors. All cursors are getting closed. (Used StrictMode also to ensure this.) * There is enough memory. The tablet with the problem is a 32GB Samsung tablet. There is a large amount of free available memory at runtime. * The preference is to store the image in the database as a BLOB instead of storing the image in the file system and storing the path in the database record (which would, probably, resolve the issue as far as retrieving the image is concerned).. * Android details o Samsung Galaxy Note 10.1, 2014 Edition o Model Number: SM-P605V o Kernel Version 3.4.0 o Hardware Version: P605V.02 o Device Memory: Total space - 32 GB; Available space - 21.94GB Questions: * Could you please help me understand why the application works fine on six Samsung tablets that I have tested the app on, but it does not work on the seventh tablet with the same OS version? * What is the solution to this problem? This email transmission and any accompanying attachments may contain Capricorn Systems, Inc. privileged and confidential information intended only for the use of the intended addressee. Any dissemination, distribution, copying or action taken in reliance on the contents of this email by anyone other than the intended recipient is strictly prohibited. If you have received this email in error please immediately delete it and notify sender at the above Capricorn Systems, Inc. email address. Sender and Capricorn Systems, Inc. accept no liability for any damage caused directly or indirectly by receipt of this email.
[sqlite] What's the best way to pass function information to virtual table?
The override of match() trick works pretty well for cases like this. I've overridden match in my virtual table implementation to allow me to pass arbitrary specialized queries directly to my virtual table modules for cases that I know the virtual table can do a better job that SQLite on that query. Downside is if you're exposing the SQL to users of course as using match in such a manner is non-standard. MikeN -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clemens Ladisch Sent: Wednesday, June 17, 2015 1:23 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] What's the best way to pass function information to virtual table? Jerry wrote: > With xBestIndex and xFilter, we can pass the constraint information > (e.g., those from WHERE clause) to virtual table (through struct > sqlite3_index_info), so that we can locate the cursor to narrow the > search space. > However, it does not provide information about functions used in SQL > queries. > > For example, > >> SELECT MAX(key) from Table tab; > > The virtual table has no way to know the function MAX is called SQLite has a special optimization for this particular query, and rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1". > The virtual table provides xFindFunction to override functions (maybe > this can be used to pass some information). For example, the full-text search module overrides the match() function; you could do something similar: SELECT key FROM tab WHERE tab MATCH 'max(key)' > But it seems only general functions can be override -- it has not > effect on aggregate functions. The virtual table interface does not allow access to all the internals of the query optimizer. When there is an aggregate function, you can filter the rows that will be given to it, but the actual aggregation is still done by SQLite. If you can compute aggregates more efficiently than SQLite, you could create a separate virtual table: SELECT max_key FROM tab_agg but this would not work for more complex queries. Regards, Clemens ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mozilla wiki 'avoid SQLite'
I agree. SQL is quite deficient in terms of set-oriented updates. INSERT is more or less UNION, but UPDATE and DELETE have no set-oriented forms. The relational algebra describes operations on sets of tuples, where the only operation on attributes is to compare them by name or equal value. SQL implements most of the relational algebra directly, and all of it by combining operations. Updates should be semantically equivalent to an operation from the Relational Algebra followed by assignment (new value replaces old). INSERT works like that, but UPDATE and DELETE do not. Try writing a query to update all the salaries for a company where all the new salaries are found in some other table (ie a JOIN). Try writing a query to delete all the employees listed in some other table (another JOIN). Andl already has set-oriented UPDATE and DELETE, as well as the familiar predicate and computed styles. Thank you for asking. As it happens I have not been able to fully implement them on SQLite so far, because of limitations in the underlying SQL. Andl cannot perform any alterations on the columns of known tables, because that would change its relational type. It's easy to create a new table and copy data, but the issue of the proper way to handle versioning and migrations is still open. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, 17 June 2015 6:11 AM To: sqlite-users at sqlite.org; General Discussion of SQLite Database Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' On 16 Jun 2015, at 7:44pm, James K. Lowden wrote: > wrote: > >> What would make a database programming better, or best? > > Two things I've often pointed to are namespaces and regular > expressions. Another is compound datatypes. I don't have your problem with namespaces since, to me, they really are just prefixes. I do agree that regular expressions are a problem. They don't really belong in the language but they are very convenient when they are there. SQLite has JOINs (or sub-selects, which amount to the same thing at a low level) for INSERT and for SELECT but not for UPDATE. A few times when working with SQLite I've found myself writing UPDATE ... JOIN. And then having to do the job in my own code instead. And if you add JOIN to UPDATE you should probably add it to DELETE FROM too. The other problem with SQLite is the lack of ALTER TABLE ... DROP COLUMN. But to support it you need SQLite to have a proper internal model of which columns are used for what, rather than to just store and reparse the CREATE TABLE commands. The thing I always found interesting about SQL was that it picks three English words, INSERT, DELETE, UPDATE, and says that that's all you need to do. And it's right ! Is there something special about the 'three-ness' of database operations ? Or are you meant to think of it as two writing operations (INSERT, DELETE) and a convenience operation which combines them (UPDATE) ? If there was another word, what would it be ? REPLACE ? DUPLICATE ? Also, why is there only one English word needed for reading operations ? What would a database language look like if it has more than one word ? Would there be a difference between FIND and SCAN ? Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is recursive CTE fully capable?
A recursive function contains a computation and a decision: whether to terminate or go deeper. Any recursive function/query will fail to terminate if the termination condition is not satisfied. Here are two similar CTEs. The first terminates, the second does not. WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<100) SELECT x FROM cnt; WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x>0) SELECT x FROM cnt; A recursive query on DAG data will still not terminate if the recursive part of the query keeps returning the same results instead of advancing through the data. Of course that would not be a 'correct query'. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden Sent: Wednesday, 17 June 2015 4:45 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is recursive CTE fully capable? On Mon, 15 Jun 2015 11:03:17 +1000 wrote: > >>>Unless the recursion is circular, I don't see how an SQL query over > >>>a finite database could fail to terminate. > > What does this mean? It is trivial to write a recursive CTE that does > not terminate, and the property of "circularity" is not what makes the > difference. Hmm, for a correctly written recursive query not to terminate, is it not a requirement that the data contain a cycle? I can't prove it, but no counterexample springs to mind. In the positive: a correct recursive query always terminates if the data represent a directed acyclic graph. By "correct" I mean the CTE expresses a recursive relation. If you recurse over with R (a, b) as (select 1 as a, 1 as b) you have no right to expect termination. But you might be able to fry an egg on the processor. --jkl ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What should a database language do? (was: RE: Mozilla wiki 'avoid SQLite')
Thank you for the comments. Andl already has regular expressions and compound datatypes. They do everything you list here. [Regex is pretty obvious, and user types are as per TTM.] Namespaces: interesting idea. I'm not sure a hierarchical model is the best choice, but I can definitely see that 'packages' of data and code could be useful. Andl has the concept of a catalog, to store persistent information about relations (tables), operators and types (they have to go together). The catalog name would make a natural namespace. Definitely one for the todo list. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden Sent: Wednesday, 17 June 2015 4:45 AM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' On Tue, 16 Jun 2015 09:56:38 +1000 wrote: > The question is: what should a database language do? Andl can already > match or surpass SQL on database programming tasks, but is that > interesting enough? > > What would make a database programming better, or best? Two things I've often pointed to are namespaces and regular expressions. Another is compound datatypes. SQL and C both suffer from a single variable namespace. We get around it by using prefixes, e.g., "local_memcpy" or "annualized_returns". C++ added namespaces to the language. I suggest SQL's successor do the same, but use the Unix filesystem's hierarchical namespace as a model. Putatively, ATTACH DATABASE 'foo.db' as /db/local/foo; CHANGE DATABASE /db/local/foo; CREATE TABLE annualized/returns ; As far as I can tell, all the basic file and link management features of the filesystem have analogous utility in a database. (I would extend that idea to permission bits, about which we could have a robust discussion if you're interested.) Regular expressions likewise belong in a query language. The LIKE operator, an NIH relic of SQL's IBM origins, belongs on the ash heap of history. Best to follow Russ Cox's advice and restrict the regex syntax to constructs with O(n) complexity. Finally, compound datatypes would simplify and encourage the use of natural keys. Something along these lines, CREATE UDT stock_key ( asof datetime, cusip char(8) ); CREATE TABLE prices( stock_key, price float ); CREATE TABLE returns( days int, return float, stock_key references prices ); Constraints defined on the compound user-defined type would of course apply to whatever table it appears in. I thought I'd pass these along because you asked and because I don't remember seeing them in TTM. I assume you're supporting row-generators. Do you intend to support table-comparison, too? What about insert/update as assignment? --jkl ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What should a database language do? [was: RE: Mozilla wiki 'avoid SQLite']
Thank you for your comments. My target is developers, particularly those who are strong on the business domain knowledge and UI/UX, but not so strong on the database stuff. My aim is that they can write code to do sophisticated queries and data manipulation without becoming an SQL guru and without needing to master an ORM. Your mum is not on my list, sorry. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Jonathan Moules Sent: Wednesday, 17 June 2015 1:33 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' > The question is: what should a database language do? Andl can already match or surpass SQL on database programming tasks, but is that interesting enough? As much as anything, that depends on what problem you're targeting, and even your audience. At the risk of rekindling the High/low/assembly level discussion, certainly at the high level, languages generally all have different design goals, and because of this they appeal to different people. This list appears to have a very high proportion of computer science types, so we see discussions about recursions and "syntactic sugar" and all that jazz; conversely, ask on a list for web-developers and you'll get a very different set of answers. Personally, as someone whose SQL-fu is weak, and who isn't a computer scientist, one of the best things about SQL is that it's English-like and there's a very low barrier to entry. Consider this statement, which is about as complex as 90% of my SQL ever gets: SELECT * from buildings where height > 30 and colour = 'mauve' With just 2 minutes of explaining I could probably get my mum to understand what was going on there. Ok, that's an unrealistically low bar, but many people who use SQL just have simple queries/problems. While I appreciate andl doesn't have documentation yet, it doesn't look like it will pass the "not a computer scientist" test for usability. Just my 2c. Cheers, Jonathan -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of david at andl.org Sent: Tuesday, June 16, 2015 12:57 AM To: ajm at zator.com; 'General Discussion of SQLite Database' Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' >>>I think the best database language should reflect how earthlings >>>think about the data, and the best computer programming language would reflect easily the result we want get from them. Care to expand on that? I'm developing a new database language: Andl. My starting point has been the relational model, Codd-Date-Darwen and The Third Manifesto. My (only) competitor seems to be SQL, which has a 40+ year lead. Nothing like a challenge! The question is: what should a database language do? Andl can already match or surpass SQL on database programming tasks, but is that interesting enough? What would make a database programming better, or best? Regards David M Bennett FACS Andl - A New Database Language - andl.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This message has been scanned for viruses by MailControl - www.mailcontrol.com Click https://www.mailcontrol.com/sr/43VYmEOYO7bGX2PQPOmvUj!GOBh06pKK8EdQhM6i4Fvmo G0cFV2y0WTEr3dfsj1m6uKlINFphbL3KeH4!zzvzA== to report this email as spam. HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them. If you have received this message in error please advise us immediately and destroy all copies of it. HR Wallingford Limited Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in England No. 02562099 ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mozilla wiki 'avoid SQLite'
> -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of david at andl.org > Sent: Monday, June 15, 2015 2:28 AM > To: 'General Discussion of SQLite Database' > Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' > > I won't abuse the patience of our hosts by prolonging this debate, but > I disagree strongly with this theme. > > I have almost certainly written more C/C++ code than you or most of the > people on this list, and I never choose it first. I am personally at > least 3 times as productive in C# as I am in C (slightly narrower > margin in C++), and computers are far cheaper than brains. If productivity is important is there any reason why you're not working in F#? I don't know that language myself, but I've seen some impressive demos. -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > > This theme is strongly reminiscent of arguments over moving from > assembly language, and it's basically wrong. The best tool is the one > that gets the required job done with maximal speed at minimal cost. > > And just for the record, C# does not compile into byte code. I suggest > you check your facts. > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of > Aleksey Tulinov > Sent: Monday, 15 June 2015 10:32 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' > > On 15/06/15 01:00, Simon Slavin wrote: > > Simon, > > > Perhaps the next generation of computer languages will be designed by > computer, to let us speak to them in an efficient manner. > > > > I'm sure computer would insist on C, if not, then it's apparently a > software bug. > > On a serious note, i think it's rather question of programming > computers or programming another programs. As you've mentioned, higher > level languages often compiles into byte-code which is then interpreted > by virtual machine. > So you don't speak to machine, you speak to mediator who speak to > machine. > This by definition an overhead, with growing complexity of the program, > overhead will grow accordingly, this is unavoidable. > > I think it's also fair to say that SQL is not for programming machines > (no offense), it's for programming SQLite and other database > implementations. > Even if something is called "virtual machine", VM always behaves > somehow differently from The Machine, thus programmer' > efforts has mediated effect on latter. > > Of course VM could do a good job in a specific domain, but each VM > limits the liberty of expressing yourself to the machine and vice > versa. > > In my opinion best database language would reflect the way in which > database works and best computer programming language would reflect the > way in which computer works, as close as reasonably possible. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] Possible bug in shell .open command?
Should be fixed now on trunk. On 6/17/15, Jean Chevalier wrote: > I took that the .open command could be issued as ".open" to open a new > in-memory database and ".open ''" (followed by a pair of single quotes) > to open a new unnamed temporary file database. > > I wonder what is going on here: After issuing a short combination of > these commands with/without putting anything into the databases, or > saving them, it ends up printing an error "unable to open database > ; unable to open file." Would the printing of gibberish mean > is trying to read a file name where there isn't one, i.e., reading a > memory address that wasn't previously written to? > > To reproduce, save the following as a script and invoke the shell with > the init clause to call the script. If you see no error, quit the shell > and try again, you should soon see it. Notice it's not a problem of the > init clause since the same happens if I type this interactively. > > .open '' .open .open create table x(y); .open '' .open '' .open .open > > This would not be a problem unless someone could potentially write a > function as part of an API that would attempt to reset the database > prior to doing some other work, a reset sometimes unnecessarily but > not expected to be harmful. Then repeated calls to a db open command > that normally doesn't cause a problem on its own could cause the > above problem. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] What's the best way to pass function information to virtual table?
Jerry wrote: > With xBestIndex and xFilter, we can pass the constraint information (e.g., > those from WHERE clause) to virtual table (through struct > sqlite3_index_info), so that we can locate the cursor to narrow the search > space. > However, it does not provide information about functions used in SQL > queries. > > For example, > >> SELECT MAX(key) from Table tab; > > The virtual table has no way to know the function MAX is called SQLite has a special optimization for this particular query, and rewrites it as "SELECT key FROM tab ORDER BY key DESC LIMIT 1". > The virtual table provides xFindFunction to override functions (maybe this > can be used to pass some information). For example, the full-text search module overrides the match() function; you could do something similar: SELECT key FROM tab WHERE tab MATCH 'max(key)' > But it seems only general functions can be override -- it has not effect on > aggregate functions. The virtual table interface does not allow access to all the internals of the query optimizer. When there is an aggregate function, you can filter the rows that will be given to it, but the actual aggregation is still done by SQLite. If you can compute aggregates more efficiently than SQLite, you could create a separate virtual table: SELECT max_key FROM tab_agg but this would not work for more complex queries. Regards, Clemens
[sqlite] Mozilla wiki 'avoid SQLite'
On 17 Jun 2015, at 3:44am, Marc L. Allen wrote: > I don't know. Back in the day, assembly was low-level because it was directly > converted to machine code. C was high level because you could express more > complex structures without worrying about the underlying architecture. C was designed to be a processor-independent assembler code. C was called low-level because C pointers could be used to manipulate memory and the stack. C was called low-level because you had to understand the platform's memory map, stack and word size to use it, in contrast to FORTRAN/COBOL/BASIC where you blindly wrote your program with no understanding of your hardware. Thus, you get the argument between programmers about whether C was high-level or low-level. > I still like that distinction. I think people are trying to call C low level > simply because there are even higher level languages. It's not just 'high' or > 'low'. It's a spectrum. Characteristics, perhaps. Simon.
[sqlite] Mozilla wiki 'avoid SQLite'
Marco, >There's never been that kind of tone, the article has been written to >point >new Mozilla codebase contributors at possible pitfalls we already hit in >the past, and actually help them making informed decisions. That's not how one reads it. Start with only the title: "Performance/Avoid SQLite In Your Next Firefox Feature" Do you read with me? "If you want performance at any rate, AVOID SQLite ..." Then it starts enumerating pretended "SQLite pitfalls", pointing out several topics which are presented as inherent drawbacks to using SQLite from a FF pluggin. This is not "help them making their decision", just simply bashing the library which you seem to expect being almost reserved to core code. >It actually briefly explains in which cases a JSON log can work better Starting at ~1Mb data it advises storing stuff as compressed JSON. Now you're seriously trying to tell me that with a multi-Mb compressed complex JSON: -) locking the compressed JSON file -) reading it up in full, in memory -) unpacking it (with much more memory consumption, of course) -) parsing it (again more memory workspace needed) -) scanning the object tree in search of the data you're after -) reading and changing a single value in the tree -) flattening the whole tree back to JSON form -) repacking it -) rewriting the lot -) releasing the lock ALL of this sequence (in a multi-threaded environment with all the extra burden that comes with it) is going to take less time, waste less CPU cycles and use less RAM than a single SQLite UPDATE wrapped in a safe-to-use API, all in a smaller "footprint"? Seriously? And what does happen when two or more compressed JSON have to be used together to obtain/access the required information? Looks like a can or worms in this case. Note how the footprint argument is hypocritical since the SQLite library is already loaded and ready for work since it's much in use for the main thread, so the footprint is always there, that SQLite be used in pluggins or not. Any decently written safe wrapping API is ridiculously small and doesn't even count. I don't seem the only one to be utterly doubtful about this "compressed JSON > SQLite" assertion, when applied to the general case. >It tries to make people think before doing. Yes a simple flat file can be the best solution in simple cases but that shouldn't trigger the AVOID verb in general cases. And less simple flat files can fit the bill for another range of use cases. It would be wise to shape the arguments (a number of them being valid) in a different way. >It states to evaluate alternatives. Existing alternatives like OS.File >writeAtomic and JSON. Those are already used with success in both the >products and add-ons, when it makes sense. The last part of your sentence, "when it makes sense", clashes with the title (AVOID). Avoiding is not evaluating. >Sure the developer mus think to durability, backups, coherence, but >would it not be the same if he'd use SQLite? Definitely not, because you know that SQLite already takes greatest care of all the possible issues on countless platforms and this comes free: the code is already there, highly optimized and working. Code written by "average" pluggin developper can't compete with years of experience with those issues which are prone to overlook. Whenever a dark corner resulting in a bug in SQLite surfaces, the fix comes in no time and fixes all applications using SQLite at a time. > > After all, it's Mozilla devs themselves who designed pluggin APIs > and let > > "spurious main-thread SQL statements" be possible. If they were sooo > > clever, they would never had allowed that and they also would have > wrapped > > SQLite interface in a strictly limited set of rules enforced by a > safe API. > > That, they won't tell you. > >And they'd have a 100% bug free product with millions lines of code... No, >that's unrealistic. Great! So I've news for you: you can produce proven correct *-code-* using today's formal methods tools. Granted it would actually be irrealistic to dream of switching to a formal development process within months, even years, but such things do exist. Yet I never pretended that offering a safer API would solve all of the issues, just a number of those the text is about. > The Mozilla codebase comes from the 90s Netscape >codebase, at that time the most common thing was a single-threaded and >single-process browser, able to show text, some images and tables. The >reality evolved A LOT and the code had to evolve to cope with it. At the >time mozStorage (the SQLite wrapper) was written, there was still that >kind >of vision, and it was written as a main-thread synchronous API. Sure, now >we all know it was wrong, but at that time it was the right-ish thing to >do. The API grew a purely asynchronous alternative, but when you have >hundreds millions of users and thousands of add-ons using an API, you >can't >just say "sorry, we now break you
