[sqlite] Newbie issue - Linux error malloc.c:2372: sysmalloc: Assertion `(old_top == (((mbinptr) (((char *)
Hello everyone, I am new to the list. I am working on an application in which I will be embedding SQLite as the database engine. The application is written in C. I am currently having an issue which I am not able to resolve at the moment so I thought I would ask here since I am just starting out with SQLite. My problem is, from my point of view, that I am not able to perform an action to the same database file in the following manner: 1) I open a database via: sqlite3_initialize() sqlite3_open_v2 2) I do some work on getting metadata from the database like table names and their fields and then 3) I close the connection via: sqlite3_close_v2 sqlite3_shutdown 4) After all of this is done I wish to process an import file so I need to open another connection to the same database file and run some statements but when I try to do that I get this on the open call in step 1 (above): malloc.c:2372: sysmalloc: Assertion `(old_top == (((mbinptr) (((char *) &((av)->bins[((1) - 1) * 2])) - __builtin_offsetof (struct malloc_chunk, fd && old_size == 0) || ((unsigned long) (old_size) >= (unsigned long)__builtin_offsetof (struct malloc_chunk, >fd_nextsize))+((2 *(sizeof(size_t)) < __alignof__ (long double) ? >__alignof__ (long double) : 2 *(sizeof(size_t))) - 1)) & ~((2 >*(sizeof(size_t)) < __alignof__ (long double) ? __alignof__ (long >double) : 2 *(sizeof(size_t))) - 1))) && ((old_top)->size & 0x1) && >((unsigned long) old_end & pagemask) == 0)' failed. Aborted (core >dumped) This happens in on line 17149 when calling: p = SQLITE_MALLOC( nByte+8 ) nByte is 64000 in sqlite3.c (amalgamation latest version sqlite-amalgamation-3081002.zip) I am compiling and running the code on: Linux x140e 3.13.0-53-generic #89-Ubuntu SMP Wed May 20 10:34:28 UTC 2015 i686 athlon i686 GNU/Linux NAME="Ubuntu" VERSION="14.04.2 LTS, Trusty Tahr" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 14.04.2 LTS" VERSION_ID="14.04" HOME_URL="http://www.ubuntu.com/; SUPPORT_URL="http://help.ubuntu.com/; BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/; I have compiled sqlite.o with the following: gcc -c -Wall -O0 -g -std=c99 -Dlinux -I/usr/local/include sqlite3.c \ -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION -o obj/sqlite3.o Any suggestions or directions greatly appreciated. TIA, George
[sqlite] UDTs
On 5 Jun 2015, at 9:14pm, Joe Mucchiello wrote: > All these subject lines about UDTs in SQLite and the one and only thing I > would use such a thing for is not listed: Date/Time values. I'd love for > there to be native date/time formats in SQLite. I'm surprised it never came > up. Although I'm also surprised the NoSQL-ite came up. One advantage I can think of of having a DateTime type -- enforcement of storing the correct thing in the correct column -- won't work in SQLite anyway, because rather than enforce column types is uses only affinities. How would your code profit from having a DateTime type in SQLite, as opposed to you just standardising on strings of the format described in ISO8601: -MM-DDThh:mm:ssTZD ? Would you require another format too -- TimeInterval -- so that you could subtract one DateTime from another ? Would you want automatic reformatting of dates to and from your preferred date format (which would require a list of locales to be added to SQLite) ? Strings of the above format can be searched and sorted. As long as the programmer for a particular database is consistent about whether they use TimeZones or not, the NOCASE collation works fine for date/times. It's a solution that works fine without needing any extra code in SQLite. Simon.
[sqlite] UDTs
All these subject lines about UDTs in SQLite and the one and only thing I would use such a thing for is not listed: Date/Time values. I'd love for there to be native date/time formats in SQLite. I'm surprised it never came up. Although I'm also surprised the NoSQL-ite came up. > That's six sets of knowledge to do one thing.? Granted, they do different > things in different ways but it's still rather a lot to fit into m noggin.? > And it's > annoying when you think you're debugging a JavaScript problem but > it turns out that a SQL command doesn't mean what you thought it did: > switching my> brain between JavaScripting and SQLing seems to require a lot > of effort. Switching gears between on lang and another never bothered me in the slightest. They have different domains and thus different syntax spaces. Big deal.
[sqlite] UDTs
On Fri, Jun 05, 2015 at 09:36:09PM +0100, Simon Slavin wrote: > One advantage I can think of of having a DateTime type -- enforcement > of storing the correct thing in the correct column -- won't work in > SQLite anyway, because rather than enforce column types is uses only > affinities. > > How would your code profit from having a DateTime type in SQLite, as > opposed to you just standardising on strings of the format described > in ISO8601: > > -MM-DDThh:mm:ssTZD > > ? Would you require another format too -- TimeInterval -- so that you > could subtract one DateTime from another ? Would you want automatic > reformatting of dates to and from your preferred date format (which > would require a list of locales to be added to SQLite) ? Ideally numeric types (i.e., ones for whose values there are arithmetic operators) could have optional (but strongly typed and algebraic) dimensions and units, and an absolute vs. scalar (relative) form. This would help prevent many accidents (though unit conversions in a limited precision environment is a source of errors, so care is still required). Time should behave like a numeric type with "time" dimension and some unit such as seconds, say, or microseconds. The internal representation of time wouldn't have to be numeric. Type conversions -casts- to/from external representations (seconds since Unix epoch, seconds since Windows epoch, ISO8601 time strings, ...) should be available; some might be the identity function applied to the internal representation. (Also, obviously, there should be no time unit like "month".) A variant of the same type, coercing the internal representation to whatever is best for the user, would also be nice. > Strings of the above format can be searched and sorted. As long as > the programmer for a particular database is consistent about whether > they use TimeZones or not, the NOCASE collation works fine for > date/times. It's a solution that works fine without needing any extra > code in SQLite. I agree that it works well enough. Datetime is not really a great source of motivation for UDTs. If anything datetime is a poor motivation for UDTs because it is so special (having so many possible representations). If anything, UDTs are probably not as interesting as adding something like type-tagged blobs and maybe bitstrings. Nico --
[sqlite] User-defined types
On Thu, 4 Jun 2015 15:11:55 -0700 Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or > standardization? The first and best defense of SQL is that it has at least some basis in the relational model. It expresses relational project, select, join, union, and intersection directly, and with contortions relational division. Like Algol-60, it's an improvement on its predecessors and on many of its successors. > If you're more comfortable and familiar with JSON > the yes it is easier and you can avoid an unnecessary conversion step. I wonder how many applications you've profiled for which SQL generation and parsing were a significant share of the run time. Usually once the data are of any appreciable size I/O becomes the most important component. That's why modern DBMSs have so many features to minimize I/O. While you're working on your new syntax, I hope you'll keep Richard's query in mind. SQL, verbose as it is, is pretty clean compared to most ORM syntaxes I've seen. A syntax that can express his query more succinctly and is also "better" along the lines you describe would be an achievement. --jkl
[sqlite] User-defined types
On Fri, 5 Jun 2015 13:07:59 -0400 Stephen Chrzanowski wrote: > If N-Tier software development is 'annoying' and you are not happy, > either get help by other members of your team, or, find a different > hobby, because anything less than 3-tier programming dealing with > multiple languages, technologies and functionality just isn't going > away. Quite honestly, and this is just my opinion, but I think it is > absolutely wrong of you to go into a place of employment angry or > annoyed at ANY level because of the multiple languages and > technologies used to bring your product to life. Get mad at the > politics, not the tools being used. Whew! Most days this list is as well behaved as a mature poodle, and then once in a while someone writes a rant that segfaults on the first paragraph. You're saying complexity here to stay, it's inevitable. Simon is saying it's unnecesary (ergo annoying). The more you know about inherent and accidental complexity, the lower your threshold for being annoyed by the latter. There's no reason your 6-tier application couldn't be written in a single language. You could have one syntax for data structures, one representation (and semantics) for missing data, one binary representation for each type. You could throw an exception at tier-0 couldn't be caught in tier-5. The jumble of technologies we use is very much a happenstance accident of industrial history. No one designed it that way. Why else would we have no less than two completely different abstractions of the machine -- the OS and the browser -- connected by nothing more sophisticated than a stream of bytes? > Second... Come on... Really? This "switching...requires a lot of > effort" comment is a pretty weak sauce excuse coming from any > developer It's not an excuse, "sauce" (whatever that means) or otherwise. It's a fact. It's called "cognitive load" and it's been recognized since the dawn of software. --jkl
[sqlite] User-defined types
On 5 Jun 2015, at 3:05pm, Don V Nielsen wrote: > I do all kinds of --stuff-- > using Ruby and PHP. And the --stuff-- gets translated to SQL and sent to > my favorite db, Sqlite. I find it very annoying that in order to do good Web-facing systems I have to know all the following: HTML (and CSS if you consider that separate) JavaScript HTML5 APIs for sound/movies/drag/forms/whatever PHP SQLite3 API SQL That's six sets of knowledge to do one thing. Granted, they do different things in different ways but it's still rather a lot to fit into m noggin. And it's annoying when you think you're debugging a JavaScript problem but it turns out that a SQL command doesn't mean what you thought it did: switching my brain between JavaScripting and SQLing seems to require a lot of effort. Simon.
[sqlite] User-defined types
Yes, the relational model is the key, that is my point. The SQL language is an entirely arbitrary syntax applied to it. You don't need it to work a relational database, just like you don't have to program in C to write a program for a typical processor. I don't care about how many applications have a performance problem with SQL generation. *My* application has a problem with it. I also don't care about Richard's query. *My* queries are much easier to form without contorting them into a SQL query. This is the entire point of my changes: avoid arbitrary bottlenecks and conventions that get in the way of performance and ease of use. It's not useful for your average person or typical application, but it's extremely useful for people who want to get the benefit of the database engine encapsulated within it. On Fri, Jun 5, 2015 at 2:10 PM, James K. Lowden wrote: > On Thu, 4 Jun 2015 15:11:55 -0700 > Darko Volaric wrote: > > > Are you seriously saying that that SQL syntax is friendly? How can you > > defend SQL syntax other than on grounds of history or > > standardization? > > The first and best defense of SQL is that it has at least some > basis in the relational model. It expresses relational project, > select, join, union, and intersection directly, and with contortions > relational division. Like Algol-60, it's an improvement on its > predecessors and on many of its successors. > > > If you're more comfortable and familiar with JSON > > the yes it is easier and you can avoid an unnecessary conversion step. > > I wonder how many applications you've profiled for which SQL generation > and parsing were a significant share of the run time. Usually once the > data are of any appreciable size I/O becomes the most important > component. That's why modern DBMSs have so many features to minimize > I/O. > > While you're working on your new syntax, I hope you'll keep Richard's > query in mind. SQL, verbose as it is, is pretty clean compared to > most ORM syntaxes I've seen. A syntax that can express his query more > succinctly and is also "better" along the lines you describe would be > an achievement. > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] User-defined types
On Fri, Jun 5, 2015 at 11:07 AM, Stephen Chrzanowski
wrote:
> First, who said that you had to keep all 6 sets of languages in your head
> at once? I've never been told that, and I've been doing software
> development since I was 8, taken several training courses in elementary,
> high school, college, and while employed by three different companies (At
> different times). I don't know VB6 all that well today, but, if I could
> find my CDs and install I'm sure I'd be able to figure out sprintf doesn't
> work. I've got a lot of junk in my head going back to Vic-20 Assembly, to
> V2 Basic, to VB3->6, to Borland Pascal 3 to Delphi 2010, to PHP, to bash,
> to freak'n nearly anything else dealing with web and internet
> technologies. It ain't all going to fit up in my nogin, but sure know
> where to get the info when I need it. php.net is my friend when I need to
> look up how strpos, substr and str_replace work several times an hour.
>
> Second... Come on... Really? This "switching...requires a lot of effort"
> comment is a pretty weak sauce excuse coming from any developer and is
>
{snipped}
This is a lot of commentary for an informal off the cuff remark. :) I mean,
I agree with what you're saying, but I also agree with Simon. Here's why:
I also have lots of mental state cruft that has accumulated in my noggin
over the past 30+ years, going back to a Commodore Pet at my school. Back
in the day, you could comfortably hold the entire state of the system in
your head, your program was a single tasking single user beast that took
over the entire computer, a team of one could easily write many types of
software, you used one language for the entire project, and data stores
were so small, and memory so constrained that you didn't use mega-flexible
systems like those based on SQL (whether embedded like SQLite or not). Of
course, there was no SQLite to use, or even SQL...
Today one person can't hope to keep the entire state of a system, has to
play well with others (as in your program won't have exclusive use of the
hardware in 99.999% of cases *and* must often be able to work in a team
environment where tasks are delegated so that things can be done in a
reasonable amount of time), must work with a variety of technologies &
languages that don't always play nice together (which complicates
debugging), and etc.
Task switching involves overhead, whether it is in a computer or in our
brains.
Sometimes I miss the simplicity of programming my Commodore 8-bit
computers. The rest of the time I love the world we live in where I have
far more computing power in my shirt pocket than I could have ever imagined
back in the day.
SDR
[sqlite] User-defined types
First, who said that you had to keep all 6 sets of languages in your head at once? I've never been told that, and I've been doing software development since I was 8, taken several training courses in elementary, high school, college, and while employed by three different companies (At different times). I don't know VB6 all that well today, but, if I could find my CDs and install I'm sure I'd be able to figure out sprintf doesn't work. I've got a lot of junk in my head going back to Vic-20 Assembly, to V2 Basic, to VB3->6, to Borland Pascal 3 to Delphi 2010, to PHP, to bash, to freak'n nearly anything else dealing with web and internet technologies. It ain't all going to fit up in my nogin, but sure know where to get the info when I need it. php.net is my friend when I need to look up how strpos, substr and str_replace work several times an hour. Second... Come on... Really? This "switching...requires a lot of effort" comment is a pretty weak sauce excuse coming from any developer and is borderline grasping at straws to try and make a statement, ESPECIALLY when it comes to COMPLETELY different technologies that have EXACTLY NOTHING to do with each other as individual components. Seriously, that is like saying you're having a hard time switching between using a ball-peen hammer and a chain saw, and not quite understanding where the screwdriver comes into play. They are ENTIRELY different technologies and tools. .. Ok... So all three devices can open a paint can, but, only one is going to keep the mess to a minimum, and it ain't the chainsaw. (Trust me... .. Tried it, and had fun, but hated the clean up) N-Tier programming is a freak'n (Awesomely wonderful, and sometimes completely evil) beast when it comes to decent development, and that is EXPECTED even at the most BASIC database driven web site, and I'm just talking a basic login page, which is at minimum a 3-tier application. (UI to get the creds from the user, logic to validate the form, access to a data source, be it flat file, LDAP or SQL database, then logic to check to see if what was provided by the UI is correct, then tell the UI to display something.) When I get into my "deep code runs" in Win32, I can easily escape into a wonderful world by writing a 6-tier application. UI, Authentication, Business Logic, Data Storage, error trapping and handling, and finally logging to see where the other 5 tiers screw up. Every tier 100% independent of the other, has the ability to talk to any tier as needed. If I want the UI to talk to the DB directly, so be it. I don't HAVE to know how all 6 levels work. I didn't write all of them. I SUCK at LDAP authentication. I'll NEVER retain how the 6 domain trees we have at work are named and structured, and I don't CARE to know. I just need to provide a way for a user/admin to do that config, and beg or bribe someone to give me a connection string that works so that I can do my coding. Thank goodness the coffee the company provides tastes like mud, because I love driving, and enjoy my Tim Hortons coffee, and I can usually bribe my way out of issues by buying coffee. That said, where it gets annoying is NOT the technology that is being used or to swap brains frequencies, but when one of the technologies isn't doing what I expect. I know the tools and how they work. Its when I pull the rope 30 times on the chainsaw and it not starting, only to realize I forgot to set the choke, prime the fuel, or pull the throttle while pulling. THAT gets annoying. Its also annoying when spending hours or days trying to figure out why a UI component isn't getting the proper information from the SQL server, only to find out I had typed the URI for the LDAP server wrong and the logic code was asking the database for information in the wrong table or database. Valid connection, just wrong point of interest. If you're going to work on something that looks fancy for the web, then yeah, you're going to need to deal with HTML and CSS. No way around it. If you're going to make a rock solid database, then of course, you're going to have to have a firm understanding of how a particular database language works to get the job done. Again, unavoidable. And if you want to just skip the whole understanding the high level HTML/CSS language and the low-level SQL calls, you can easily get into the middle of things and get into a framework type of scenario where you can deal with what the framework does best for you and not worry about HTML/CSS/SQL at all, except that they exist and function somehow, someway, and you have interfaces to each level of technology. Then you only need to be aware of how the framework works, and not the database language or the HTML markup. If N-Tier software development is 'annoying' and you are not happy, either get help by other members of your team, or, find a different hobby, because anything less than 3-tier programming dealing with multiple languages, technologies and functionality just
[sqlite] User-defined types
> How can you defend SQL syntax other than on grounds of history or standardization? Short answer: QWERTY. Long answer: IBM mainframe DOS -> Z/OS. A 1960's o/s that is still supported by the inner workings of its most modern o/s. There's is nothing wrong with supporting the past. Sometimes things we don't like have long histories that we may not like. But like it, or not, those histories created a standard and got us to where we are today. Why forget the past? We can enhance and embrace at the same time. I do all kinds of --stuff-- using Ruby and PHP. And the --stuff-- gets translated to SQL and sent to my favorite db, Sqlite. Why? Because SQL works, and so much understands it. It has a legacy and an understanding and it is documented and it is well vetted and and and and. My 1 cent. On Fri, Jun 5, 2015 at 8:48 AM, Etienne Charland wrote: > What you're looking for seems similar to LINQ to SQLite > (System.Data.SQLite). When programming in C#, I don't code any SQL. I use a > strongly-typed interface that then generates SQL queries in the background. > > Besides LINQ, you could create another interface that suits your needs, > and that can then communicate with any database since all databases > recognize SQL. Nothing needs to change on SQLite's side. > > My 2 cents. > > > Etienne > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] User-defined types
What you're looking for seems similar to LINQ to SQLite (System.Data.SQLite). When programming in C#, I don't code any SQL. I use a strongly-typed interface that then generates SQL queries in the background. Besides LINQ, you could create another interface that suits your needs, and that can then communicate with any database since all databases recognize SQL. Nothing needs to change on SQLite's side. My 2 cents. Etienne
[sqlite] User-defined types
There's a bit of confusion as to what I'm actually proposing. I can't reply to everyone so I'll just post the APIs and/or patches when they're done and we can argue those on their merits. On Thu, Jun 4, 2015 at 5:03 PM, Darko Volaric wrote: > Well, I've been using SQL for about 30 years so I'm unlikely to change my > view, but I think you bring up a much more important point: instead of > arguing online I should get back to work! > > > On Thu, Jun 4, 2015 at 4:11 PM, Richard Hipp wrote: > >> On 6/4/15, Darko Volaric wrote: >> > >> > What is motivating this for me is that I generate many unique queries >> in my >> > code for almost any operation. Converting those to SQL is error prone >> and >> > uses a lot of memory compared to the operation involved. The database >> > engine is so fast and efficient yet I'm wasting resources making SQL! >> > >> >> You are welcomed to go off and try to come up with a new and better >> interface. That's the beauty of open-source. Maybe you will come up >> with some new and innovative ideas that will change the industry! >> It's happened before! >> >> I just want to ensure that if, after working on your new approach for >> a while, you eventually decide that SQL isn't quite as bad a language >> as you originally thought it was, that you don't come back and say I >> didn't warn you. >> >> -- >> D. Richard Hipp >> drh at sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
[sqlite] User-defined types
On 2015-06-05 12:11 AM, Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or standardization? If > you're more comfortable and familiar with JSON the yes it is easier and you > can avoid an unnecessary conversion step. And I now regret using JSON as an example since everyone wants me to convert SQL to JSON for them now, but my point isn't any particular notation, I want an API of sorts instead of a notation or syntax. Then you can adapt anything you like and make it efficient with the platform you're using. So for example you send a native, binary JavaScript object (or record, whatever its called) as your query instead of SQL text. What is motivating this for me is that I generate many unique queries in my code for almost any operation. Converting those to SQL is error prone and uses a lot of memory compared to the operation involved. The database engine is so fast and efficient yet I'm wasting resources making SQL! I just want to skip that SQL bottleneck, because it has no technical justification other than "standardization" and pass my query straight through. The defense of SQL is much like the Defense of the English language. It isn't the best language out there, it is full of idiosyncrasies and vestigial bits of long dead habits. It just happens to be the language spoken by most people whom you wish to talk with. SQL is even better than that - not only does it present a language understood by all serious database engines, it is understood by people and even reads like English. Some of it can be better I suppose, but that is down to preference. I think you underestimate exactly how useful and efficient SQL is. It can produce infinitely many and widely differing, yet algebraically correct, data compilations from a very limited vocabulary. We are not stubbornly clinging to the old and deprecated - we actually think the way it works is quite neat. That said, I get your point. You want to send the Query parameters in an object or binary format because it's more like programming or OO than SQL is, and safer. The premise is wrong though. There is nothing intrinsically safer or more efficient about compiling and sending bits from an object than bits of text. The parser will parse it either way and produce an outcome. If you think that you can suggest to the parser sufficiently via a binary object to avoid a lot of parsing, then I have great news for you - you can already do that by simply adjusting the properties of the *stmnt object returned by sqlite3_prepareV2() in glorious bit-byte detail. An attempt to do so will quickly prove that making the SQL and letting the parser do its bit is by far the preferred method. The second premise is also wrong - there is nothing about the properties of an object that make them less prone to bugs/inefficiencies or human error than an SQL string. (JAVA objects do not even have proper templates, they are especially prone to error). Also, the lifetime of any SQL-text bug is limited to the first time the query is run. The only other thing I can think of might be that you do not test queries before implementing them or they are created on the fly by possibly a user process. In the latter case, any bugs there would equally likely be propagated through an object or binary representation of sorts. To be clear of what I intended to say: You might prove to have something useful still, and I'd be interested to see how it may work, but please note that if SQL is a bottleneck, it's an extremely low-cost unobtrusive one, and its only justification is NOT merely "standardization", not more than the use of Liters as a measure of volume is merely tolerated because it is standardized - it might not be particularly fantastic, but happens to be no worse a measure of volume than any other, and until the converse can be shown, it is likely to remain the standard.
[sqlite] User-defined types
On 5 Jun 2015, at 12:11am, Richard Hipp wrote: > I just want to ensure that if, after working on your new approach for > a while, you eventually decide that SQL isn't quite as bad a language > as you originally thought it was, that you don't come back and say I > didn't warn you. I'm on my third attempt now, I think. I've had three tries at designing a better NFS or a better Unix file-handling library and by the time I've got down to the nitty-gritty of configuration methods and error-handling the only result is a renewed respect for the elegance and efficiency of the original. Simon.
[sqlite] User-defined types
On 2015-06-04 11:16 PM, Darko Volaric wrote:
> My point about JSON, etc is that there is no reason not to use that as a
> query language if that makes it easier. If your system is efficient with
> JSON, why not accept a query that is formatted as JSON? It's not
> semantically different to SQL syntax. Here's an example (with a roughly
> JSON notation):
>
> {
>operation: "insert"
>table: "blah"
>columns: ["a", "b", "c"]
>values: [1.3, 2.0, 3.1]
>on-conflict: "replace"
> }
It's an interesting idea and I for one am willing to entertain the
thought, but I'm having difficulty seeing the "simpler" and "easier"
things you claim, or the memory saving for that matter.
Just take the above JSON query and consider that in SQL that would
simply look like:
REPLACE INTO blah (a,b,c) VALUES (1.3, 2.0, 3.1);
If we have to open a pole on which version seems simpler or use less
memory, the result would probably be indecisive if not plainly favouring
the latter.
I am willing to learn though, for instance, how do you see this next
query represented in the JSON way?:
INSERT INTO blah (1,b,c) VALUES
(1.1, 2.2, 3.3),
(3.1, 3.2, 3.4),
(5.1, 4.2, 3.5),
(7.1, 5.2, 3.6);
Or maybe this one:
SELECT MAX(A.Code), MAX(A.Name), B.Age, MAX(B.LastEditedDate) AS LastDT
FROM CodeNames AS A
LEFT JOIN Codehist AS B ON A.Code = B.Code AND B.Age > 30
WHERE A.Name LIKE 'SomeVal%'
GROUP BY B.Age
ORDER BY LastDT DESC
LIMIT 50;
(I'll forgo the "Having" clause for simplicity).
I'm finding it difficult to imagine a better layout for that query in a
JSON (or any other Markup-based) document - but I am quite willing (and
even interested) to be shown a way that makes more sense and satisfies
the claims of simplicity and memory efficiency.
Once a layout is found that works, I imagine it would be a whole other
can of spaghetti to make any program author the syntax sensibly, but
that is a worthy bridge to cross once the first question is answered well.
Alternatively, you might be able to show how the other notation might
ease the query-planner's work, or how it might help any other SQL
process work better or faster. Some significant improvement in
functionality or efficiency will make a much stronger case than "It's
easier to compose".
Ryan
[sqlite] User-defined types
On 4 Jun 2015, at 11:11pm, Darko Volaric wrote: > Are you seriously saying that that SQL syntax is friendly? How can you > defend SQL syntax other than on grounds of history or standardization? If > you're more comfortable and familiar with JSON the yes it is easier and you > can avoid an unnecessary conversion step. JSON is not a query language. You can devise a query language using JSON components (the one you showed was a SQL rip-off but would work for simple cases). Once you've done that you have to deal with possibilities for ambiguity and the difficulty of describing how to handle errors. And once you've done that you still have to prove it's competitive with the existing query language using text called SQL, which millions of people already know. Simon.
