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

11–20 of 68 posts

Re: The Design Of SQLite4

#11

Post by bobbyi_settv »

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)

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.

Re: The Design Of SQLite4

#12

Post by _wmd »

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…

I'm perplexed how you arrived at this conclusion from the linked document.. every major DBMS with a "fully functional ALTER" has a key/value store underneath it, as did SQLite 3, as does PostgreSQL and MySQL, as does..

The only difference with the new design is that the SQL engine and storage engine have been explicitly modularized and exported as a public API, potentially allowing simultaneous k/v and SQL use within a single transaction, or flexible use of SQL with an existing store.

Re: The Design Of SQLite4

#13

Post by jasode »

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 there would be any compelling use case for the older SQLite3 for reasons other than backward compatibility. It would be great if the SQLite designers could answer: "You'd want to use v3 instead of v4 for greenfield projects if...?"

Re: The Design Of SQLite4

#14

Post by zzzeek »

_wmd wrote: post #12
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…

I'm perplexed how you arrived at this conclusion from the linked document.. every major DBMS with a "fully functional ALTER" has a key/value store underneath it, as did SQLite 3, as does PostgreSQL and MySQL, as does.. The only difference with the new design is that the SQL engine and storage engine have been explicitly modularized and exported as a public API, potentially allowing simultaneous k/v and SQL use within…

> I'm perplexed how you arrived at this conclusion from the linked document..

it is based on:

https://news.ycombinator.com/item?id=5887053

> I am very familiar with SQLite internals. The answer is already in there. SQLite stores each row as each column value encoded sequentially corresponding to the declared order of the columns. Changing column order or deletions/inserts require a rewrite of every row... > ...A SQLite provided ALTER TABLE implementation would do exactly what was stated - start a transaction, rename the existing table to a temporary name, create a new one with the desired schema, and copy data across mangling as appropriate before deleting the old table and finishing the transaction.

the document here appears to suggest that the internal structure idea is being largely maintained as is, except that it will be organized in one giant blob, rather than blob-per-table. If the structure were being changed such that ALTER were suddenly much more feasible, I'd assume that would be one of the giant headlines of this story. But it's not.

Of course we'd always welcome hearing from actual SQLite developers someday on this issue.

Re: The Design Of SQLite4

#15

Post by seanalltogether »

I'm sure this sounds arbitrary, but I'd love it if the sqlite file header allowed for more room to add program specific metadata. The fact that more and more apps are using sqlite files to store user facing files, it would be nice if space in the header was given to identify the expected usage of the sql file.

Re: The Design Of SQLite4

#16

Post by _wmd »

zzzeek wrote: post #14
_wmd wrote: post #12

Earlier quoted context omitted.

I'm perplexed how you arrived at this conclusion from the linked document.. every major DBMS with a "fully functional ALTER" has a key/value store underneath it, as did SQLite 3, as does PostgreSQL and MySQL, as does.. The only difference with the new design is that the SQL engine and storage engine have been explicitly modularized and exported as a public API, potentially allowing simultaneous k/v and SQL use within…

> I'm perplexed how you arrived at this conclusion from the linked document.. it is based on: https://news.ycombinator.com/item?id=5887053 > I am very familiar with SQLite internals. The answer is already in there. SQLite stores each row as each column value encoded sequentially corresponding to the declared order of the columns. Changing column order or deletions/inserts require a rewrite of every row... > ...A SQLi…

SQLite 4 will have one storage engine record for each table row, just as it was in SQLite 3.

Fundamentally, there is little difference in how SQLite stores data in the storage engine as compared to, say, Postgres, except that Postgres' SQL implementation is more tightly bound to its storage engine (e.g. index tuples are encoded using knowledge of the engine, whereas in SQLite they simply use the record's primary key)

Postgres and suchlike don't have some magical data structure that makes ALTER TABLE possible, all row oriented stores have the same choice: either implement the alter immediately (involves a scan and rewrite) or lazily (on next record update).

Not sure if there are any that take the latter approach, but the method is commonplace elsewhere

Re: The Design Of SQLite4

#17

Post by SoftwareMaven »

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…

I don't think there is an answer to your question beyond "it's been battle tested extremely well", but there are strong reasons to move to sqlite4 (in particular, multiple linked instances in eg mobile apps and mobile OSes). It's not like Python where the weight of the existing community has drawn you towards python 2 (something that has changed a lot recently).

Re: The Design Of SQLite4

#18

Post by ccgus »

seanalltogether wrote: post #15

I'm sure this sounds arbitrary, but I'd love it if the sqlite file header allowed for more room to add program specific metadata. The fact that more and more apps are using sqlite files to store user facing files, it would be nice if space in the header was given to identify the expected usage of the sql file.

You're aware of the application_id pragma? That's what this guy was created for: http://www.sqlite.org/pragma.html#pragma_application_id

Re: The Design Of SQLite4

#19

Post by jqm »

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!
Post reply on HN