11 ms·
SQLite 3.42.0
- gred 3y ago> Enhance the JSON SQL functions to support JSON5 extensions Wait, what's JSON5?? > Object keys may be unquoted identifiers. > Objects may have a single trailing comma. > Arrays may have a single trailing comma. > Strings may be single quoted. > Strings may span multiple lines by escaping new line characters. > Strings may include new character escapes. > Numbers may be hexadecimal. > Numbers may have a leading or trailing decimal point. > Numbers may be "Infinity", "-Infinity", and "NaN". > Numbers may begin with an explicit plus sign. > Single (//...) and multi-line (/.../) comments are allowed. > Additional white space characters are allowed. Oh crap, the levees have broken!
- staz 3y agoall that and still no datetime support which is the most annoying thing missing in JSON imho
- stusmall 3y agoHuh, I'd never thought twice about that. What would native datetime support in JSON get you that a ISO 8601 string doesn't?
- masklinn 3y agoWell one thing it could get you (probably wouldn't, but could) is symbolic timezones, as ISO 8601 only supports offsets.
- dspillett 3y agoType validation for one. The reduced chance that a client has put some backwards format in there like MM-DD-YYYY (or DD-MM-YYYY for that matter) or just an invalid date completely. You might as well ask what native numeric or boolean support offer over just jamming stuff in a string in an agreed format. Some might argue that dates are a compound value so differ from atomic types like a number, but they are wrong IMO as a datetime can be treated as a simple numeric¹ with the compound display being just that – a display issue. Others will point out that JS doesn't have a native date/datetime/time type, but JSON is used for a lot more that persisting JS structures at this point. -- [1] caveat: this stops being true if you have a time portion with a timezone property
- masklinn 3y ago> You might as well ask what native numeric or boolean support offer over just jamming stuff in a string in an agreed format. A big difference is that a numeric or a boolean are quite limited datatypes with agreed upon semantics (mostly). > Others will point out that JS doesn't have a native date/datetime/time type It does, in fact. And it's absolute shit. > but JSON is used for a lot more that persisting JS structures at this point. So what I'm reading here is that you don't need types to be supported natively in order to serialize to JSON.
- dspillett 3y ago> It does, in fact. And it's absolute shit. Maybe it is "just us", maybe it is part of the general shitness you point out, maybe it is the lack of literal representation (even VB and relatives had one) other than an ISO8601 string, but dates don't feel native like simpler types, arrays, objects, fictions, … > So what I'm reading here is that you don't need types to be supported natively in order to serialize to JSON. Yes. But not absolutely needing something does not mean it isn't (or wouldn't be) exceptionally useful to have.
- regularfry 3y agoWhat it gets you is not having to deal with breakage when someone doesn't know the difference between ISO8601 and RFC3339 or, in fact, `date` strings. Shuffle all that mess off down the stack to where someone else has made one decision, once, rather than having to relitigate it every time.
- 8organicbits 3y ago> It is a conformant subset of the ISO 8601 extended format. Huh, TIL. https://www.rfc-editor.org/rfc/rfc3339 https://www.rfc-editor.org/rfc/rfc3339
- flatline 3y agoInconsistent datetime storage has been a consistent issue for me providing cross-provider and cross-application/framework support for SQLite.
- masklinn 3y agoJSON will never have datetime support, since Javascript does not have datetime literals (and that's a good thing given how horrible the Date object is). Probably more importantly, all of that and still not proper datetimes in sqlite. Also even more so no domains (for custom datatypes).
- Mister_Snuggles 3y ago> Probably more importantly, all of that and still not proper datetimes in sqlite. Home Assistant recently did a ton of changes to work around the issues caused by this. The short story is that they stopped storing timestamps as 'timestamp' datatypes and started storing them as unix times stored in numeric columns. Since timestamps turn into strings in SQLite, this was a huge improvement for storage space, performance, etc. The problem is that this change also affects databases which have a real datetime datatype. So PostgreSQL, which internally stores timestamps as unix times, is now being told to store a numeric value. To treat it as a timestamp you have to convert it while querying. Since I used PostgreSQL for my Home Assistant installation, this feels like a giant step backwards for me. I wish that they had used this change as an opportunity to refactor the database code a bit so that they could store timestamps as numeric for SQLite, but use a real timestamp datatype for MySQL and PostgreSQL. I'm sure that this isn't a simple thing to do though.
- tracker1 3y agoGenerally speaking... DateTime/TimeStamp fields between databases are in general treated differently either in practice or purpose much of the time. When migrating from one database to another, this is almost always an issue.
- chris-orgmenta 3y agoIn people's opinions: Would this feature be appropriate to implement, or beyond the scope of what the JSON project should aim for?
- pdimitar 3y agoI don't know what "appropriate" or even "beyond the scope" mean in this context but, having in mind that datetime data needing to be stored is a fact of life that's not going away then I'd say yes, it does belong in JSON. It also belongs in SQLite.
- Thaxll 3y agoYou can remove JSON, when is SQLite adding proper date type?
- thunderbong 3y agoIt's already available - https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- pdimitar 3y agoWrong, there's no datetime there. Only integer can be used for it in a more space-saving manner. But that means you have to convert and invoke calculation functions. It's error-prone.
- deleted 3y ago[deleted]
- NelsonMinar 3y agoIs JSON5 a thing people use? I see it's from 2012 but it's the first I've heard of it. It looks fairly sensible; I'd take it for trailing commas alone. And comments!
- yamtaddle 3y agoHow does it affect speed of parsing in JavaScript? I kinda thought the whole reason this deeply-mediocre format caught on in the first place was it was especially natural & fast to serialize/deserialize in JavaScript, on account of being a subset of that language. (XML also had the "fast" going for it thanks to browser APIs, but not so much the "natural")
- masklinn 3y ago> this deeply-mediocre format Most of the "popular" publicly available formats at the time were singularly worse, even ignoring commonly limited or inconvenient language support. SOAP? ASN.1? plists? CSV? uuencode? I'll still take JSON over all of them, especially when it comes to sending shit to the browser (plists might be workable with a library isolating you from it, but it is way too capable for server to browser communications, or even S2S for that matter not all languages expose a URL or an OSet type). > it was especially natural & fast to serialize/deserialize in JavaScript, on account of being a subset of that language. That is certainly a factor, specifically that you could parse it "natively", initially via eval, and relatively quickly[0] via built-in JSON support (for more safety as the eval methods needed a few tricks to avoid full RCE). But an other factor was almost certainly that it's simple to parse and the data model is a lower common denominator for pretty much every dynamically typed language. And you didn't need to waste time on schemas and codegen, which at the time was a breath of fresh air. > XML also had the "fast" going for it thanks to browser APIs, but not so much the "natural" XML never has "fast" going on in any situation, the browser XML APIs are horrible, and you had to implement whatever serialization format you wanted in javascript over that, so that was even slower (especially at a time when JS was mostly interpreted) [0] compared to the time it started being used: Crockford invented / extracted JSON in 2001, but services started using JSON with the rise of webapps / ajax in the mid aughts, and all of Firefox, Chrome, and Safari added native json support mid-2009
- nilsbunger 3y agoComments in JSON? Why isn’t this everywhere?
- cryptonector 3y agoBecause preserving them across processors (think jq) is hard or impossible.
- Salgat 3y agoIt's a shame because it's a rather trivial thing to filter out. Personally I wish they would have left out the "//" single-line commenting to avoid the whitespace dependency.
- cryptonector 3y agoFiltering them out is indeed trivial, but then they're not stable / preserved, and so why bother writing them? Ensuring that comments and their locations in the text are preserved by processors is quite difficult if not impossible. Reformatting a JSON text alone can "break" comments by not necessarily placing them where they belong. Any schema transformation means comments must be dropped.
- fiddlerwoaroof 3y agoYou can write a parser that generates an AST node for comments and then pretty prints that AST. So it’s not impossible, but it does require that your parser gives you the option to not just drop comments on the floor.
- cryptonector 3y agoAlright, here's a JSON5 text: { // start x: "y", // the following is foo foo: "hey", // the following is bar bar: "there" // end } When parsing it, should I attach each comment to the following name or the previous name? If I reformat to change indentation, should I change the indentation of the comments too? How would the comment that reads "the following is bar" be re-indented? What if there are duplicate names? JSON does allow duplicates. If I attach comments to preceding (or following) names, then while parsing I find a dup... what should I do with the preceding comment and the new name?
- cryptonector 3y ago> > Strings may be single quoted. Hmmm... It's super convenient that SQL uses single quotes for string literals while JSON uses double quotes. Changing that is going to cause pain. > > Strings may span multiple lines by escaping new line characters. I really can't recommend this. Yeah it's annoying to have to write \n, but still.
- masklinn 3y ago> Hmmm... It's super convenient that SQL uses single quotes for string literals while JSON uses double quotes. Changing that is going to cause pain. Surely you're not constructing queries by concatenating JSON to text? > I really can't recommend this. Yeah it's annoying to have to write \n, but still. I can only disagree, the ability to just put newlines in a string literal in languages like rust is refreshing.
- cryptonector 3y ago> Surely you're not constructing queries by concatenating JSON to text? Of course not, but I do have code that generates SQL and which uses JSON. (And no, the code in question is not subject to SQL injection.)
- masklinn 3y agoI fail to see how this is relevant. Your code feeds JSON to SQL, that remains supported. Or your code fetches SQL-generated value from SQL, in which case as literally stated before the JSON5 listing: > JSON text generated by [JSON] routines will always be strictly conforming to the canonical definition of JSON.
- throw0101a 3y ago> > Single (//...) and multi-line (/.../) comments are allowed. We can now have different parsers have pragmas that specify different behaviour depending on whether those pragmas are recognized or not. In case anyone was wondering, the history is that comments were considered an anti-feature by Douglas Crockford, the creator of JSON: > I removed comments from JSON because I saw people were using them to hold parsing directives, a practice which would have destroyed interoperability. I know that the lack of comments makes some people sad, but it shouldn't. > Suppose you are using JSON to keep configuration files, which you would like to annotate. Go ahead and insert all the comments you like. Then pipe it through JSMin before handing it to your JSON parser. * https://web.archive.org/web/20150105080225/https://plus.google.com/+DouglasCrockfordEsq/posts/RK8qyGVaGSr https://web.archive.org/web/20150105080225/https://plus.goog... * https://en.wikipedia.org/wiki/Douglas_Crockford https://en.wikipedia.org/wiki/Douglas_Crockford
- arp242 3y agoAnd for a data interchange format this is also 100% reasonable. The problem is that people have started using JSON for configuration files and the like, which IMHO has always been – and continues to be – the wrong tool for the job.
- hgsgm 3y agoHow does that relate? Your config parser can disregard commments.
- arp242 3y agoYes, but JSON was never intended for that use case – the feature set isn't geared towards it.
- mikepurvis 3y agoI think the point is that your config parser should be using yaml or toml, though in fairness neither of those existed in the early 2000s when JSON was being developed/discovered/adopted— the 800lb gorilla in that space at the time was XML. And XML remained dominant for a long time— for example, the original Google Maps from 2005 received its server responses as XML blobs, and API v2 even exposed the relevant parsing functionality as the GXml JavaScript class. By around 2007, it was all JSONp I think, and GXml was deprecated and removed in API v3 and v4 respectively.
- teddyh 3y agoDespite the name, “JSON5” is not an official successor to JSON.
- stjohnswarts 3y agoGood to know, I would still like to see it become defacto successor though.
- chris-orgmenta 3y ago"> Objects may have a single trailing comma." Any arguments against this one? My knee jerk reaction is 'yay'
- givemeethekeys 3y agoWhy would you put a trailing comma when there's nothing after it? A comma isn't a period :-/
- Taywee 3y agoWhen manually modifying JSON, it really helps avoid accidentally missing a comma when you add or reorder elements. JSON isn't English. The comma doesn't mean what it does in English, so I don't see why a period would be appropriate either.
- dangerlibrary 3y agoBecause it follows the general principle of making it easier to change [0]. Fewer edits, cleaner diffs. Useless from the perspective of a wire format, but nice for things like config files, which seem to be the use cases json5 is targeting. [0] Bullet pt 3: https://betterprogramming.pub/5-essential-takeaways-from-the-pragmatic-programmer-6bb3db986294 https://betterprogramming.pub/5-essential-takeaways-from-the...
- Tmpod 3y agoMakes copy-pasting and reordering lines easier, which is quite handy.
- everybodyknows 3y agoSimplifies JSON-generation code, typically saving a conditional.
- lordgrenville 3y agoJust noticed today that the Python formatter "black" does this, I really disagree. Of course I realise that by noticing its changes and arguing about them I am missing the whole point of using it.
- SQLite 3y agoThe important point to keep in mind is that SQLite will read JSON5, but it never writes it. The JSON that SQLite generates is canonical JSON that is fully compliant with the original JSON spec. It turns out that there is a lot of "JSON" data in the wild that is not pure and proper JSON, but instead includes some of the extensions of JSON5. The point of this enhancement is to enable SQLite to read and process most of that wild JSON. This feature was requested by multiple important users of SQLite.
- alberth 3y agoDr. Hipp Off topic: would you mind sharing any info on potential timing of begin-concurrent-pnu-wal2 branch being merged into main (or consideration of forking sqlite to have a "client/server" version)? https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2 https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-w... (Love what you have created. Thank you so much for all the years of amazing work)
- SQLite 3y agoThat branch has been renamed "bedrock" (after its principal user) and is up-to-date.
- sidewndr46 3y agoIsn't this just the YAML debacle all over again? Where virtually any sequence of text is "valid" (albeit meaningless) YAML?
- keithalewis 3y agoNo need to go Postel about it.
- atomize 3y agoI wished upon a shooting star for comments in JSON sometime in the 2010s. That took a while, but I'm glad it came through. -- EDIT: mainly back when we started using JSON to configure, well, lot of things.
- rcarmo 3y agoDoes it have "contains" and paths yet?
- say_it_as_it_is 3y agoHow are people using sqlite within a multi-threaded, asynchronous runtime: are you using a synchronization lock? SQLite seems to "kinda, sorta" support multi-threading, requiring special configuration and caveats. Is there a good reference?
- wanttocomment 3y agoEach thread opens its own connection. Set a non-zero timeout. I think that's all you need.
- masklinn 3y agoCreate a connection per task. WAL is probably a good idea. Even using SERIALIZED mode, sqlite has multiple APIs which are completely broken if two clients touch the same connection (https://github.com/rusqlite/rusqlite/issues/342#issuecomment-592624109 https://github.com/rusqlite/rusqlite/issues/342#issuecomment...). Don't bother, just don't share connections between threads and use the regular multi-thread mode (do use that though). You can use a connection pool and move connections from task to task (and thread to thread), just not use connections concurrently.
- matharmin 3y ago1. Use WAL mode - this allows reading while practically never being blocked by writes. 2. Use application-level locks (in addition to the SQLite locking system). The queuing behavior from application-level locks can work better than the retry mechanism provided by SQLite. 3. Use connection pooling with a separate connection per thread - one write connection and multiple read connections. Ideally, all of the above would be covered by a library, and not up to the app developer. I wrote a blog post covering some of this recently: https://www.powersync.co/blog/sqlite-optimizations-for-ultra-high-performance https://www.powersync.co/blog/sqlite-optimizations-for-ultra...
- chasil 3y agoBe careful of WAL mode. There are specific limitations. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
- airstrike 3y agoIs there an unavoidable reason why SQLite won't allow foreign keys to be added with ALTER TABLE, only at table creation? The whole "select * into temporary table and create new table with FK constraint" seems so verbose / convoluted, my layman's mind can't comprehend why one can't simply ALTER TABLE "foo" ADD FOREIGN KEY...
- giraffe_lady 3y agoThe alter table docs have a general explanation kinda. They don't store the parsed representation of the schema, since that would lock the table to a specific version. They store the create command itself, and each time you open a DB the schema is generated from that. Allows for flexible upgrades to the schema system without the need for migrations, and lets you use the same DB file with multiple sqlite versions. The downside is that alter table commands are just edits to the create table string, which when accounting for all the different versions is fairly difficult and risky, so alter operations are limited.
- masklinn 3y agoBut also FKs remain half-assed and seen with some disdain: it's 2023, support was added (a bit under) 14 years ago, and you still need to activate foreign keys every time you open the database file. There is still no way to enable FKs by default for a database file, let alone prevent disabling it.
- maxpert 3y agoWait! Can I store unicode escaped binary strings in JSON now?
- pyrolistical 3y agoWat > Note that SQLite interprets NaN, QNaN, and SNaN as just an alternative spellings for "null"
- jksmith 3y agoMaybe this comment is worthless and has no information content, but SQLite is in the pantheon of open source projects, along with linux itself.