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

51–60 of 68 posts

Re: The Design Of SQLite4

#51

Post by malkia »

asveikau wrote: post #32
sanxiyn wrote: post #26

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are.…

Thankfully from what I have seen sqlite is maintained with that old-school style of library development, the one you scarcely see these days, and don't hear much about on HN: the one where "thou shalt not break existing code" has some weight. I'd be nervous about depending on a library where the whim of the developer thinking "I find this sad" breaks my program. sqlite4 OTOH seems to be the "let's break things" phase…

Upvoted. SQLITE as an API does so many things right:

- Providing it's own malloc/free "proxies" to work away with different linked CRT versions (Microsoft) - Prefixing ("namespacing") everything, and not exporting symbols outside of the API - Configuration of the API before it's started, where it makes sense (threading model for example) - Ability to retrieve compilation options back (e.g. how it was compiled and with what features) - many other things

Re: The Design Of SQLite4

#52

Post by mamcx »

elchief wrote: post #33

Think I might start a kickstarter to fund PostgreSQL-SQLite replication over WebSocket. Good idea?

Yes! I need a sync engine between both of this, and I is harder than I tough. I wish to join forces on this, but not much love for SQL this day with all "all backend is NOSQL!"

Re: The Design Of SQLite4

#53

Post by frik »

ccgus wrote: post #18
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

That's good, but is not enough. A metadata support would be useful!

e.g. It's like with photos (JPG) were you would only know it was shoot with a Canon camera, or a music file (MP3) that only stores the application name media player.

Most common file formats support metadata formarts like JPEG with its EXIV, ITPC, XMP and MP3 with ID3 v1 & v2, etc.

So for SQLite4 a very simple key value format would be great, with some predefined key-fields.

Re: The Design Of SQLite4

#54

Post by dschiptsov »

sanxiyn wrote: post #26

"SQLite4 requires all elements of the PRIMARY KEY to be non-null. This is an SQL standard. Due to an oversight in early versions, SQLite3 does not enforce the NOT NULL constraint on PRIMARY KEY columns since by the time the omission was discovered SQLite3 was in such widespread use, activation of NOT NULL enforcement would have broken too many programs." I find this sad, and I wonder what those too many programs are.…

If you would think for a couple of minutes you will [probably] understand that "gaps" (NULLs) in a PRIMARY KEY column is a plain stupidity.

Re: The Design Of SQLite4

#55

Post by rat87 »

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?

according to

`apt-cache rdepends slite3`

on ubuntu 14.04 packages including the following link to sqlite dynamic packages(this may be partially due to debian heavily discouraging statistically linked packages.

  zabbix-proxy-sqlite3
  yokadi
  uwsgi
  usermetricsservice
  trove-common
  sympa
  sqliteman
  sisu-sqlite
  rt4-db-sqlite
  roundcube-sqlite3
  redmine-sqlite
  ratbox-services-sqlite
  postfix-cluebringer-sqlite3
  pdns-backend-sqlite3
  pbnj
  opendnssec-enforcer-sqlite3
  lire
  krecipes
  kamailio-sqlite-modules
  inspircd
  imms-common
  hud-tools
  gbrowse
  eurephia
  dotclear
  beancounter
  adminer
  bacula-director-sqlite3

Re: The Design Of SQLite4

#56

Post by dtech »

mbell wrote: post #42
d4mi3n wrote: post #6

Earlier quoted context omitted.

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'.

> To some people 'embedded' means 'within the same process or thread as the core application'

Only that definition is silly here, since SQLite is by its serverless nature always embedded inside another program.

Re: The Design Of SQLite4

#57

Post by nikbackm »

keeperofdakeys wrote: post #43
d4mi3n wrote: post #29

Earlier quoted context omitted.

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 re…

Wouldn't a database generally be offline when you ALTER it? At least one that is used in scenarios where SQLITE is appropriate.

In that case it should not hurt too much if the operation takes some time.

Re: The Design Of SQLite4

#58

Post by nikbackm »

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).

It might be a neat solution if you already have a server process for other tasks anyway and the data access needs are quite simple.

Re: The Design Of SQLite4

#59

Post by ghshephard »

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!

From: http://www.sqlite.org/whentouse.html

SQLite is not designed to replace Oracle. It is designed to replace fopen().

Re: The Design Of SQLite4

#60

Post by thesz »

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…

1) That would be true for any row-oriented DBMS.

2) LSM tree is blazingly fast in inserts (my own experiments shows two orders of magnitude difference for BerkeleyDB and my own LSM tree implementation in C# for bulk random inserts).

So I think SQLite4 is safe here.

Post reply on HN