[sqlite] Mozilla wiki 'avoid SQLite'
In my tool box, I have a tack hammer, a claw hammer and a nail gun. I broke my fingernail with the tack hammer. I still have the bandaid on my thumb from the claw hammer. Now I just have to read the instruction manual for the nail gun
[sqlite] Mozilla wiki 'avoid SQLite'
I've refrained to comment about the OP linked page but I can't resist that long. I won't enter the C, C--, C++, C#, Java, Python, COBOL, Basic, assembler, Ruby, YouNameIt sub-debate. I found the rant on MozillaWiki way too wrong on too many points to keep quiet. While I can agree with some of the most obvious "don't be dumb" remarks, there are many things that can't be let unchallenged. And I don't appreciate the overall tone: "WE at MozillaBigBalls are all clever enough to use SQLite smartly but you mere mortals are not, so don't even think to use that fragile piece of softawre." In it's introduction, the blurb talks about SQLite hidden complexity. Then it avdises Mozilla pluggin developers to avoid SQLite at almost any rate, due to "performance reasons", and recommends compressed JSON/logs instead. That's very odd, to say the least. All regular readers of this list have seen a number of threads where the intricacies of how hard it is to guarantee both some concurrency and ACID properties down to magnetic surface or chips' gates is difficult in the general case. You all know that SQLite actually uses all possible steps to bring this guarantee the closest possible to reality. Yet this guy(s) advocate that *-every-* pluggin devs should independently roll his own storage layer mostly from scratch, while pointing out the unstabilityor uncertainty associated with mobile devices processes/OSes. That is nothing but calling for (1) huge number of new entertaining bugs (2) at best, duplicate efforts to make that work in a highly multi-threaded environment where concurrency will need to be dealt with with greatest care. No, the "average" pluggin dev won't be able to come up in minutes with a rock solid storage layer portable to a myriad of platforms. And this is precisely what SQLite has been thru over years. As has already been pointed out, storing largish private data in compressed JSON or log file(s) will be (beyond often being a greater resource hog) a real nightmare when several tabs/windows will possibly need to concurrently read/modify/write stuff in there. Writing rants about SQLite being a resource hog and a performance breaker is one thing, guiding towards a clean way to replace it in practice is another (mystical) beast, at least for the average pluggin developper. Then another question remains: instead of putting the onus on SQLite being huge (footprint) and slow (CPU, the 22s "example"), why don't the author(s) of the blurb question the real root causes of the evil they condemn and openly recognize that the problem lies entirely elsewhere, perhaps in Mozilla core code design itself? They almost silently agree that allowing main-thread SQL access is a huge no-no. They recognize that allowing unlimited use of various (possibly conflicting) pragmas, random schemas and/or statements could harm. But what those hypocritical guys don't tell you is that the root cause is in the cahotic spiralling development history of Mozilla. 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. Also, if Mozilla devs were sooo much more clever than average Joe and sooo caring about performance, they certainly would have fixed the hundreds of memory leaks that plague FF users (at least on Windows and for almost a decade) and they would have "spring cleaned" their messy codebase so that one can't see JS fragments kept running after the FF tabs which launched it was closed and blatant bugs like these. Watch memory bytes slowy growing toward 2Gb while FF is left "idle" and the RJ45 unplugged, without any pluggin set up... Is that the fault of pluggin devs, Dr. Hipp laziness, or the result of their own careless work? With today's FF W7 x64 (ditto for x86) release, you can't let it have, say, 30 tabs open 24/7 on "passive" forum pages (no ads, no sound, no video, nothing dynamic) for more than 2-3 weeks on the average. Then the machine gets so slow and unresponsive that you can only kill FF and restart it. At the time it has reopen and refreshed all the tabs, you witness its memory bytes now sit at 600 Mb (compared to ~2Gb before), with everything as functional as before killing it. Is that also SQLite fault or is that a sad joke? These are things that you don't see with either IE or Chrome (each of them having their own drawbacks too). In short and beyond a few trivial advises, the authors of this rant are either surprisingly ignorant or utterly hypocritical, but unfair at any rate. I've nothing against Mozilla per se and I sincerely acknowledge that maintaining such a huge open product is very hard, but this page is simply plain wrong.
[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] Mozilla wiki 'avoid SQLite'
With a relational model you have a choice between relational calculus or relational algebra. SQL does one of them. I have used a language which did the other, long ago in a land far away. On Tue, Jun 16, 2015 at 9:56 AM, wrote: > >>>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 > -- Christopher Vance
[sqlite] Mozilla wiki 'avoid SQLite'
2015-06-15 18:05 GMT+02:00 Jean Chevalier : > What I'm thinking now is to what extent the developer who put up the > Mozilla wiki page was entitled to put up opinions and statements either > expressed or implied about a third-party product on behalf of the Mozilla > Foundation (though is that the same Foundation that pays the Consortium > member fee?). The mozilla wiki contains informations useful to contributors to the mozilla codebase and more generally "mozillians", it's not intended to be a global resource of information like wikipedia, nor any kind of official documentation on how to use libraries. It's targeted to code written against the Mozilla codebase and it's not even in the official MDN. The document you pointed at was created some time ago, cause we had a problem with developers taking "the easy way" too often, when they needed a store they just used SQLite (or better mozStorage, our wrapper) because the API was nice and already available, without doing any kind of analysis of their needs. The title was (likely) chosen explicitly negative to make people read the article and clarify the point before going deep into details. And it helped, now people ask what's the best store for their kind of workflow, and clearly SQLite is still a used option. Please don't attach any kind of negative bias to a wiki article, it's just a technical article about possible pitfalls our developers will hit (and have hit) if they don't think what they are doing. Nothing more than that. SQLite is used extensively in every single Mozilla product, so what? . The contents of the page came out from actual bugs and misuses we hit in years of use and experience with it and were discussed with attention. Most also have workarounds or suggested fixes. Honestly I just think you are giving too much importance to a technical document with a clear target and very well expressed points, rather than to the fact SQLite is happily used everywhere. It should just be used properly, not randomly.
[sqlite] Mozilla wiki 'avoid SQLite'
2015-06-16 1:36 GMT+02:00 Jean-Christophe Deschamps : > While I can agree with some of the most obvious "don't be dumb" remarks, > there are many things that can't be let unchallenged. And I don't > appreciate the overall tone: "WE at MozillaBigBalls are all clever enough > to use SQLite smartly but you mere mortals are not, so don't even think to > use that fragile piece of softawre." > 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. It's not intended for global consumption. Sure it's public, cause there's no reason to "hide" technical document that might be useful to contributors, just to complicate their access. Can you find any page that states Mozilla doesn't like SQLite or wants anyone to abandon it? It is used everywhere, from mercurial repositories to Firefox OS... > In it's introduction, the blurb talks about SQLite hidden complexity. Then > it avdises Mozilla pluggin developers to avoid SQLite at almost any rate, > due to "performance reasons", and recommends compressed JSON/logs instead. > It actually briefly explains in which cases a JSON log can work better and at the end it also states what to do and avoid if still SQLite is found to be the best store (and it clearly happens!). It tries to make people think before doing. > Yet this guy(s) advocate that *-every-* pluggin devs should independently > roll his own storage layer 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. Sure the developer mus think to durability, backups, coherence, but would it not be the same if he'd use SQLite? You must find the right compromise between durability and performance, you still have to handle corruption cases. Thinking about that stuff is part of normal planning for any feature, independently from the chosen store. > Then another question remains: instead of putting the onus on SQLite being > huge (footprint) and slow (CPU, the 22s "example"), why don't the author(s) > of the blurb question the real root causes of the evil they condemn and > openly recognize that the problem lies entirely elsewhere, perhaps in > Mozilla core code design itself? > Because it's clear the article is about SQLite use in the Mozilla codebase. It's clear there are issues in the Mozilla codebase itself (I'd be surprised of the opposite!). > 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. 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 all". You must play fairly. Sure, the Firefox add-ons are the most powerful add-ons around, they can do anything, and while this might be (as you point out) a downside, cause they can perform poorly, it is also their major selling point. There are still a lots of things you can do with a Firefox add-on that you can't do in any other browser add-on. Also, this has nothing to do with the fact SQLite can still be the wrong choice for certain data store needs (as it's the best for others), that is still the main purpose of that article. > Also, if Mozilla devs were sooo much more clever than average Joe and sooo > caring about performance, they certainly would have fixed the hundreds of > memory leaks that plague FF users > This is going off topic, I'd be happy to digress about this but it's not the right place. If you have suggestions or bugs, the codebase is open and the bug tracker is public, you can reach every single developer through mail or IRC. You have all the tools to make the difference. Marco
[sqlite] SQLite parser library
Hi, Some time ago I created a complete parser for SELECT statements in SQLite (based on the grammar found in the source code). Sample code below! Understanding how the Lemon parser works, creating the abstract syntax tree (AST), and implementing the parser classes turned out to be extraordinary hard - even though I do have experience with Bison and Flex. This library is indeed a piece of art, and my company has little use of it. I'm wondering if there is a commercial interest for this C++ library. Please let me know. ** SAMPLE CODE ** select_parser parser = select_parser::create_parser(sql); // Visitor pattern to allow arbitrary operations on the abstract syntax tree (AST). select_node_visitor snv; parser.accept(&snv); cout << "Result columns: " << snv.get_result_columns().size() << endl; cout << "Group columns: " << snv.get_group_by_columns().size() << endl; // Pretty-printed SQL suitable for editors. (I used it myself together with Scintilla.) // E.g. // "select x,y,foo(x) as f order by f" // --> // "SELECT x, y, foo(x) ORDER BY f" // sql_printer printer; parser.accept(&printer); cout << "Pretty-printed SQL: " << printer.get_sql() << endl; // Possible to create arbitrary operations on SELECT statements. my_select_injector injector; parser.accept(&injector); // E.g. injector.toggle_sort_order(); // DESC <-> ASC injector.make_distinct_result(); // SELECT DISTINCT ... injector.add_row_limit(3); // ... LIMIT 3 // Or maybe just... injector.perform_crazy_complex_sql_analysis();
[sqlite] malformed database schema
Dear group, We encountered a somewhat obscure scenario that leaves an sqlite database in a corrupted state (tested with sqlite 3.8.8). Our case deals with - an in-memory database "db" with an attached file database "other.db". - "db" starts a transaction, - "db" tries to create a table in the attached "other.db" using "create table ... as select ...", but the "select" raises an error. - "db" commits the transaction. As a result, the attached database is corrupted. The corruption seems to take place during the final commit. The following Tcl script reproduces the problem: package require sqlite3 sqlite3 db :memory: catch {file delete other.db} sqlite3 odb other.db odb eval {create table dummy(whatever text);} puts "check that other.db is ok:[odb eval {select count(*) from dummy}]" rename odb "" db function myerror myerror proc myerror args {error "deliberate error"} db eval {attach [other.db] as other} catch { db transaction { catch { db eval {create table other.xxx as select myerror()} } # error "--- without this error the other.db gets corrupted ---" } } sqlite3 odb2 other.db puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]" This is the output: check that other.db is ok:0 malformed database schema (?) while executing "odb2 eval {select count(*) from dummy}" invoked from within "puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]"" (file "bugreport.tcl" line 26) If the commented line is activated the other.db is not corrupted. Best Regards, Christian
[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-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of da...@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!GOBh06pKK8EdQhM6i4FvmoG0cFV2y0WTEr3dfsj1m6uKlINFphbL3KeH4!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] Mozilla wiki 'avoid SQLite'
p.s. The spellchecker just kindly advised that maybe changing the name of "andl" might help reduce future unfortunate typos / "corrections". :-) -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules Sent: Tuesday, June 16, 2015 4:33 PM 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-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of da...@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!GOBh06pKK8EdQhM6i4FvmoG0cFV2y0WTEr3dfsj1m6uKlINFphbL3KeH4!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 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] malformed database schema
On 6/16/15, Christian Nassau wrote: > Dear group, > > We encountered a somewhat obscure scenario that leaves an sqlite database in > a corrupted state (tested with sqlite 3.8.8). Thanks for the bug report and for the reproducible test case. This problem goes back to before version 3.5.1 (circa 2007) which is as far back as I tried to reproduce it. So it has been in the code for a long while. A fix is now on trunk. If you have a database that was corrupted by this bug that you need to recover, I believe you can do so by running the following SQL: PRAGMA writable_schema=ON; DELETE FROM sqlite_master WHERE name IS NULL; > > Our case deals with > > - an in-memory database "db" with an attached file database "other.db". > - "db" starts a transaction, > - "db" tries to create a table in the attached "other.db" using "create > table ... as select ...", but the "select" raises an error. > - "db" commits the transaction. > > As a result, the attached database is corrupted. The corruption seems to > take place during the final commit. > > The following Tcl script reproduces the problem: > > > package require sqlite3 > sqlite3 db :memory: > > catch {file delete other.db} > > sqlite3 odb other.db > odb eval {create table dummy(whatever text);} > puts "check that other.db is ok:[odb eval {select count(*) from dummy}]" > rename odb "" > > db function myerror myerror > proc myerror args {error "deliberate error"} > > db eval {attach [other.db] as other} > > catch { > db transaction { > catch { >db eval {create table other.xxx as select myerror()} > } > # error "--- without this error the other.db gets corrupted ---" > } > } > > sqlite3 odb2 other.db > puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]" > > > This is the output: > > check that other.db is ok:0 > malformed database schema (?) > while executing > "odb2 eval {select count(*) from dummy}" > invoked from within > "puts "check that other.db is ok:[odb2 eval {select count(*) from dummy}]"" > (file "bugreport.tcl" line 26) > > > If the commented line is activated the other.db is not corrupted. > > Best Regards, > Christian > ___ > 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] 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] Mozilla wiki 'avoid SQLite'
On Tue, 16 Jun 2015 10:36:45 +0200 Marco Bonardo wrote: > The contents of the page came out from actual bugs and misuses we hit > in years of use and experience with it and were discussed with > attention. Most also have workarounds or suggested fixes. While I'm sure that's true, the page is filled with dubious assertions. Almost everything it says about I/O is evidence-free. The implications of a file (or the database) using multiple extents on the disk ("fragmentation") is the best example. For an application like FF, how much data, and how many seeks, are we really talking about? How many of them are in name only, because the OS reads whole tracks and buffers hundreds of megabytes in the page cache? Is fsync "very expensive"? Not likely. Compared to network latency, it's near zero. On many consumer hard disks, it *is* zero, because the drive lies about completed writes. Do small, simple writes perform better in JSON? If so, by how much? What complexity then leaks back into the application to manage deletes and index seeks, no to mention concurrency? The page doesn't mention the value of constraints for correctness. It doesn't mention the convenience to the developer of being able to inspect and query the data outside the application. Yes, sure, right tool for the job. In deciding, best to know what the tool does, and what the job is. --jkl
[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] 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] Mozilla wiki 'avoid SQLite'
Since this thread has ... become what it is ... I may as well add a couple of details. The widely-used compiler LLVM uses an intermediate representation of your code, called 'Bitcode'. In other words, it's not a simple case of compiling straight to object code. This feeds into some distinctions made between high level and low level languages in previous posts. I also wanted to comment on various things said about C by saying that there is no industry agreement about whether C is a high level or low level language. Mostly because those terms were never defined very clearly because it was obvious to every programmer what they were looking at. If the language included words which looked like human languages, it was a high level language. If it was based around acronyms and very short words like MOVE is was low level. C messed up the distinction and since then we've been running to keep up. Simon.
[sqlite] Mozilla wiki 'avoid SQLite'
ASFAIK also gcc uses some kind of intermediate rapresentation during compilation process... gimple (gcc -fdump-tree-all) and rtl (-dump-rtl-all) Il 16/giu/2015 22:40, "Simon Slavin" ha scritto: > Since this thread has ... become what it is ... I may as well add a couple > of details. > > The widely-used compiler LLVM uses an intermediate representation of your > code, called 'Bitcode'. In other words, it's not a simple case of > compiling straight to object code. This feeds into some distinctions made > between high level and low level languages in previous posts. > > I also wanted to comment on various things said about C by saying that > there is no industry agreement about whether C is a high level or low level > language. Mostly because those terms were never defined very clearly > because it was obvious to every programmer what they were looking at. If > the language included words which looked like human languages, it was a > high level language. If it was based around acronyms and very short words > like MOVE is was low level. C messed up the distinction and since then > we've been running to keep up. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] What's the best way to pass function information to virtual table?
Hi, I am working on sqlite+rocksdb, i.e., using rocksdb as the back-end using sqlite virtual table. 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 -- I want this information, because I can get the max key quickly within the underlying database. Similarly, I also need this for other certain types of queries. The virtual table provides xFindFunction to override functions (maybe this can be used to pass some information). But it seems only general functions can be override -- it has not effect on aggregate functions. Anyone knows how to pass function information to virtual table? If sqlite does not provide this feature, what's the best way to tweak the source code to implement this? Thanks. -C. Lin
[sqlite] Mozilla wiki 'avoid SQLite'
On 2015-06-16 11:44 AM, James K. Lowden wrote: > 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, SQL in general actually DOES have namespaces in practice, though they are fixed-depth rather than variable-depth. Depending on the DBMS, you have the "catalog" level, the "schema" level, the Oracle "package" level, and then your functions and tables etc have otherwise unqualified names beneath those. Depending on the DBMS, each of those levels may exist or not, but the number of levels is fixed, that part unlike the Unix filesystem. I think the SQL standard specifies catalog/schema/object itself. For my part with my database-savvy Muldis D programming language (that I hope to have executing within a few months), namespaces for both routines and relations (tables) etc are arbitrary/variable depth like you propose, like either the Unix file system or like programming languages such as Perl or C# or others. Note that Muldis D and Andl have some influences in common, but David beat me to market as it were with an executable. -- Darren Duncan
[sqlite] Mozilla wiki 'avoid SQLite'
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. 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. C might be lower level to other languages, but higher level to assembler. > On Jun 16, 2015, at 4:40 PM, Simon Slavin wrote: > > Since this thread has ... become what it is ... I may as well add a couple of > details. > > The widely-used compiler LLVM uses an intermediate representation of your > code, called 'Bitcode'. In other words, it's not a simple case of compiling > straight to object code. This feeds into some distinctions made between high > level and low level languages in previous posts. > > I also wanted to comment on various things said about C by saying that there > is no industry agreement about whether C is a high level or low level > language. Mostly because those terms were never defined very clearly because > it was obvious to every programmer what they were looking at. If the > language included words which looked like human languages, it was a high > level language. If it was based around acronyms and very short words like > MOVE is was low level. C messed up the distinction and since then we've been > running to keep up. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original.