News for hackers. Stuff that matters.
Re:Appropriate Uses for SQLite by mwexler on Monday February 12, 2018 @08:40AM (#16358461)
I find this page to be somewhat vague. For example, any data that revolves around dates or time may require some addition hoops to jump through since, as https://www.sqlite.org/datatype3.html points out, "SQLite does not have a storage class set aside for storing dates and/or times."

As much as I love SQLite, I find this little issue always adding an extra bit of work to deal with. Not a blocker, but useful to know. I've found that in some (rare) cases, H2 became a better solution (horrendous start time, slow data load, but ability to use real date and time types reduced errors and improved analyses).

Finally, knowing that SQLite is not right for a situation is great, but it wouldn't hurt for them to mention a potential alternative, if the devs or community know of one.

Re:Appropriate Uses for SQLite by coleifer on Monday February 12, 2018 @10:52AM (#16359402)
The lack of a datetime data-type hasn't been a problem in my experience. I have, at different times, stored the timestamp as an integer representing number of microseconds since the epoch, or stored in YYYY-mm-dd HH:MM:SS (which sorts lexicographically so comparisons come out right). SQLite has some built-in facilities for working with datetimes as well, along the lines of strftime, etc.

H2, being java, is in a whole different universe from SQLite...not a valid comparison in my view.

Re:Appropriate Uses for SQLite by mulmen on Monday February 12, 2018 @02:47PM (#16361426)
...in UTC. Right? RIGHT?!
Re:Appropriate Uses for SQLite by HumanDrivenDev on Monday February 12, 2018 @04:10PM (#16362189)
Not going to lie, after reading that I quickly went to check that "Date.now()" in javascript returned UTC.
Re:Appropriate Uses for SQLite by kragen on Monday February 12, 2018 @09:27AM (#16358730)
For others who didn't know about H2, it's apparently an embeddable SQL database, like SQLite, but in Java: https://github.com/h2database/h2database

It's dual-licensed under the Mozilla Public License and the Eclipse Public License.

Re:Appropriate Uses for SQLite by hinkley on Monday February 12, 2018 @11:49AM (#16359847)
It grew up arm in arm with Hibernate, one of the better ORM wrappers. Fast enough for local use that you could do functional tests against your domain objects without using mocks, or crying.
Re:Appropriate Uses for SQLite by module0000 on Monday February 12, 2018 @01:15PM (#16360574)
This may not be helpful to you, but I've always used the epoch time stored in sqlite. It's blazing fast, indexes well(integers !), and is 100% convertible to any format you might need to represent.
Re:Appropriate Uses for SQLite by hueving on Monday February 12, 2018 @01:40PM (#16360802)
Make sure you're using signed integers larger than 32 bits if you ever need dates before ~1970 or after ~2030.
Re:Appropriate Uses for SQLite by duskwuff on Monday February 12, 2018 @02:46PM (#16361418)
SQLite's INTEGER type is 64 bits, and its REAL type is double-precision (64-bit) floating point. Either type is quite sufficient for representing a time. :)
Re:Appropriate Uses for SQLite by oneweekwonder on Monday February 12, 2018 @11:39AM (#16359772)
Not as lightweight as sqllite. But I always wonder why firebird embedded-able server[0] does not get more love? The 32-bit so library just under 7MB and then you have a full rdbms available to your application.

[0] https://www.firebirdsql.org/pdfmanual/html/fbmetasecur-embed...

Re:Appropriate Uses for SQLite by lwf on Monday February 12, 2018 @02:04PM (#16361031)
Well, for one, SQLite's 32-bit dll is only 858KiB :)
Re:Appropriate Uses for SQLite by oneweekwonder on Monday February 12, 2018 @02:31PM (#16361291)
Feature richness comparable to oracle, mysql, postgres, comes at a price.

Another perspective; embeddable py 10MB, electron app 50MB[0], free pascal exe 1.5MB.

So 7MB for a RDBMS seems reasonable, but not comparable to SQLite as you pointed out.

[0]: https://github.com/electron/electron/issues/2003#issuecommen...

Re:Appropriate Uses for SQLite by jug on Monday February 12, 2018 @03:47PM (#16361962)
Poorly advertised? With a lack of marketing/funding I can understand it though. But as for me, I had heard of Firebird but not the embeddable, and of course of SQLite everywhere. Maybe it's also in part because the OSS space has limited room for this niche and SQLite kind of happened to grab the largest share.
Re:Appropriate Uses for SQLite by le-mark on Monday February 12, 2018 @08:51AM (#16358493)
That's a valid observation, but the status of sqlite as a couple of ansi c file you can compile with or link to any damn thing is really what we're talking about here. And H2, awesome as it is, is no comparison.
Re:Appropriate Uses for SQLite by mikeash on Monday February 12, 2018 @09:08AM (#16358610)
I don’t know if they want to be in the business of recommending alternatives. Doing so has the unfortunate tendency of making people think it’s your problem if you use them and they don’t work out.
Re:Appropriate Uses for SQLite by pmontra on Monday February 12, 2018 @01:36PM (#16360772)
That bite me more than once with Sqlite. I inherited small webapp with extremely messed up datetimes because Sqlite doesn't defend its data in any way. Same column, three different formats. Ouch. It's also a problem with the original developer but the database should not tolerate such mistakes.
Re:Appropriate Uses for SQLite by HumanDrivenDev on Monday February 12, 2018 @03:47PM (#16361954)
One of my (many) tasks at my current position is to maintain a SQL Server database where a lot of the date times are stored as raw strings in various different formats.

If you're determined enough you can turn any datastore into a mess, though I suppose SQLite gives you less tools.

Re:Appropriate Uses for SQLite by Cyberdog on Monday February 12, 2018 @02:50PM (#16361446)
Though I would agree that the option to have stricter type checking in SQLite would be a good thing, I don't think your criticism is fair in this case, since it sounds like a case of poor discipline on behalf of the coder(s).