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

61–68 of 68 posts

Re: The Design Of SQLite4

#61

Post by swah »

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…

Agree with you on the importance of backwards compatibility. But there is also the idea that you should at some point draw the line and break compabitibility so that you can really fix things is also defendable. In my opinion by releasing the library/project under a new name, like python does.

The worst and most common though is the "break all things for no reason at all"...

Re: The Design Of SQLite4

#62

Post by nscanlon »

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 agree that adding this, even if it is horribly inefficient, would be better than everyone reimplementing it at the application layer. I just don't see how you've drawn this conclusion from the linked summary. This sounds to me like a pet gripe of yours and you are just looking for a forum to air it.

Re: The Design Of SQLite4

#63

Post by emillon »

rat87 wrote: post #55
furyofantares wrote: post #34

Earlier quoted context omitted.

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

sqlite3 is the package that provides the command-line tool. A lot more packages depend on the dynamic library: "apt-cache rdepends libsqlite3-0 | wc -l" returns 407 results.

Re: The Design Of SQLite4

#64

Post by emillon »

frik wrote: post #53
ccgus wrote: post #18

Earlier quoted context omitted.

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

You can always embed a "metadata" table in your schema. Not ideal but would probably do the job in a lot of cases.

Re: The Design Of SQLite4

#65

Post by PythonicAlpha »

I don't know, if the author of SQLite also reads here, but ...

I am not sure, if having a single key space seems to have possible counter-productive effects on the overall speed.

One example: You want to scan all db entries of one table and the key are random English words. The table itself only has several hundred entries, but there are also tables with text keys with millions of entries.

When I understand the concept right (I don't know), wouldn't that mean, that a huge (common) index would have to be searched and many, many entries skipped, because you use one single key space? Wouldn't that hurt performance very much for that specific operation?

Re: The Design Of SQLite4

#66

Post by jqm »

jqm wrote: post #49
eyko wrote: post #46

Earlier quoted context omitted.

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.

would someone care to explain why they down voted that comment?

Re: The Design Of SQLite4

#67

Post by zzzeek »

thesz wrote: post #60
zzzeek wrote: post #14

Earlier quoted context omitted.

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

Great! So they can add a full blown ALTER. But so far, the only indication we can get is, "never".

Re: The Design Of SQLite4

#68

Post by panzi »

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 was a joke! Hence the ":P".
Post reply on HN