Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The Design Of SQLite4

sqlite.org

Post reply on HN
Print view View original

41–50 of 68 posts

Re: The Design Of SQLite4

#41

Post by dragonwriter »

furyofantares wrote: post #34
skue wrote: post #31

Earlier quoted context omitted.

Hopefully there aren't many people designing data models with NULL primary keys. However, if SQLite never enforced non-NULL behavior then it's certainly possible that along the way some applications could have written an errant record to a database with a NULL primary key (instead of treating it as an error case). And if a "fixed" version of SQLite3 ever shipped, then these previously-valid data files would suddenly…

> the OS happened to upgrade the SQLite3 library I don't think this actually detracts from your point, but, are there cases where that can actually happen? Isn't SQLite always statically linked with the applicaton?

> Isn't SQLite always statically linked with the applicaton?

SQLite can be dynamically linked, though static linking is probably more common.

Re: The Design Of SQLite4

#42

Post by mbell »

d4mi3n wrote: post #6
zzzeek wrote: post #2

Doubling down on the key/value model gives me the impression that a real and fully functional ALTER command is becoming even more unlikely than it already is, not to mention that this widely desired feature is not even mentioned here. A key advantage to the relational model is that we get to have schemas. But if you can't make reasonable changes to those schemas without explicitly rewriting all your data from scratch…

Please correct me if I'm wrong, but isn't a major use-case for SQLite a database for embedded systems? In that light, I can't picture too many situations off the top of my head where one would want to change the schema. That being said, I've personally seem SQLite used for more prototype-y stuff (default db for new instances of a Rails app, ad-hoc data stores for mobile phone apps, etc)

I think at least part of the confusion here is the definition of 'embedded'. To some people 'embedded' means 'within the same process or thread as the core application', to others it means 'firmware running on small hardware devices'.

Re: The Design Of SQLite4

#43

Post by keeperofdakeys »

d4mi3n wrote: post #29
zzzeek wrote: post #7

Earlier quoted context omitted.

I am asked constantly for ALTER support in SQLite, including by the maintainer of GNU Mailman, which is neither embedded nor a prototype one-off. SQLite is extremely flexible and used in an enormous variety of situations. References: http://dustycloud.org/blog/sqlite-alter-pain/ https://news.ycombinator.com/item?id=5886898 https://bitbucket.org/zzzeek/alembic/issue/21/column-renames... plus https://bitbucket.org/zzze…

Very interesting! Thanks for the references. Having read some of these, though, it's pretty clear that ALTER support is a major pain point. Would you have any insight as to why the SQLite maintainers would not choose to prioritize that issue? From what I know of SQLite, it's a very well built piece of software. It seems odd to me that such a smart bunch of guys would disregard community feedback without a good reason…

The problem is that it's hard to support for a database like sqlite. Things have a very specific on-disk representation, so you can't just add data to the middle of a file; rewriting the file also means others can't read it at the same time, so you can't do long operations.

When you look at this operation on a normal database, it isn't too bad. The database can change the on-disk representation while still serving requests, and it can use whatever storage it wants, since it's the only one that operates on the data.

Re: The Design Of SQLite4

#44

Post by keeperofdakeys »

jasode wrote: post #13

Reading the "Key Changes" section, it looks like the sqlite4_env environment will address the global mutex problem that was discussed in a recent HN thread. The pluggable db engine looks interesting but if I remember right, in MySQL the pluggable db engine initially attracted some niche providers but the marketplace of ideas eventually settled on ISAM/Innodb. What's still not clear from the Executive Summary is if th…

Sqlite3 is used in so many places, they would be fools to deprecate it. Maybe in five years, they'll revisit the issue, but for now they want to improve the fundamentals of sqlite.

One good thing is I expect most of their testing code can be ported over to sqlite4 http://www.sqlite.org/testing.html

Re: The Design Of SQLite4

#45

Post by marcosdumay »

d4mi3n wrote: post #30
bobbyi_settv wrote: post #11

Earlier quoted context omitted.

It's needed, for example, when upgrading applications that use sqlite for storage. Like in the mobile phone case, if a new version of the app comes out that has a new feature that requires a new column in one of the tables, when you upgrade the app on your phone, the app probably wants to migrate the sqlite db to the new schema rather than throwing away all your saved data.

This is something I hadn't considered. Being able to do firmware updates to a device with an embedded instance of SQLite seems huge. Seeing as ALTER support is currently lacking, what kinds of approaches are commonly taken to work around this issue? I'm particularly interested in hearing about how people manage workaround to this problem for things like upgrading embedded devices. I imagine the scarcity of resources…

The usual way to deal with that (and recommended by the SQLite docs) is creating a new table, copying the data, and dropping the old table.

Needless to say that this is VERY error prone, and limited resources make it even more so.

Re: The Design Of SQLite4

#46

Post by eyko »

panzi wrote: post #38
jqm wrote: post #19

I love sqlite and find it extremely useful. I just wish they could figure the multi-user issues out (maybe it's not practical...probably not, I don't know how it works internally). But, if they could do this it would be truly awesome!

Easy: Put a server process in front of it and route access to the DB through that. :P

Doesn't that just defeat the purpose? At that point you're probably much better off just using postgressql (or similar).

Re: The Design Of SQLite4

#47

Post by genwin »

panzi wrote: post #38
jqm wrote: post #19

I love sqlite and find it extremely useful. I just wish they could figure the multi-user issues out (maybe it's not practical...probably not, I don't know how it works internally). But, if they could do this it would be truly awesome!

Easy: Put a server process in front of it and route access to the DB through that. :P

I did that, using Go and its channels feature. Not much code and works like a charm.

Re: The Design Of SQLite4

#49

Post by jqm »

eyko wrote: post #46
panzi wrote: post #38

Earlier quoted context omitted.

Easy: Put a server process in front of it and route access to the DB through that. :P

Doesn't that just defeat the purpose? At that point you're probably much better off just using postgressql (or similar).

That's what I'm thinking. Probably less trouble just to set up postgres at that point and not deal with unexpected troubles and complications.

But I hadn't thought of putting a server process in front of sqlite so thanks for the tip. It might come in handy at some point.

Re: The Design Of SQLite4

#50

Post by ksec »

I did a Skim Read. So what exactly is new? Because I thought this design document were a few years old already.

When is SQLite 4 expected?

Post reply on HN