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

SQLite Release 3.25.0 adds support for window functions

sqlite.org

Post reply on HN
Print view View original

31–40 of 116 posts

Re: SQLite Release 3.25.0 adds support for window functions

#31

Post by m_fayer »

sehugg wrote: post #25
cosmie wrote: post #11

Earlier quoted context omitted.

Ditto! For anyone who isn't familiar with SQLite's testing procedures, read this[1] fascinating page. The SQLite project has a mind boggling 711 times more test code than SQLite itself has. Put another way, only 0.1% of the project's code is SQLite itself. The other 99.9% consists of tests for that 0.1%. [1] https://www.sqlite.org/testing.html

Good thing too, since it's in just about everything: https://www.sqlite.org/mostdeployed.html

I find it fun to look around whatever room/bus/park that I'm in and try to count the instances of Sqlite.

Re: SQLite Release 3.25.0 adds support for window functions

#32

Post by Cthulhu_ »

sehugg wrote: post #25
cosmie wrote: post #11

Earlier quoted context omitted.

Ditto! For anyone who isn't familiar with SQLite's testing procedures, read this[1] fascinating page. The SQLite project has a mind boggling 711 times more test code than SQLite itself has. Put another way, only 0.1% of the project's code is SQLite itself. The other 99.9% consists of tests for that 0.1%. [1] https://www.sqlite.org/testing.html

Good thing too, since it's in just about everything: https://www.sqlite.org/mostdeployed.html

Is it like curl in that regard?

Re: SQLite Release 3.25.0 adds support for window functions

#33

Post by newusertoday »

yread wrote: post #27

A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite or something else?

I am interested in this topic as well, although sqlite explicitly says that it is not meant for client-server configuration. I still want to see if it is feasible and someone is using it in production.

Re: SQLite Release 3.25.0 adds support for window functions

#34

Post by davidgould »

est wrote: post #14
davidgould wrote: post #10

Earlier quoted context omitted.

I just checked a stripped copy of postgresql-10 built with all options on and it is 7MB. That could be reduced a little by leaving out language support and ssl support etc.

Do you have tutorials to strip lang and ssl? Is it possible to dynamic link system openssl to reduce size?

If you look at the build instructions you will see that in the ./configure step you can enable and disable different features. Try ./configure --help to get a list.

It is dynamically linked to openssl, but you can configure out the internal code to support the functionality.

"strip" however meant the unix strip command to remove debug symbols.

Re: SQLite Release 3.25.0 adds support for window functions

#35

Post by davidgould »

plq wrote: post #24
davidgould wrote: post #10

Earlier quoted context omitted.

I just checked a stripped copy of postgresql-10 built with all options on and it is 7MB. That could be reduced a little by leaving out language support and ssl support etc.

While binary size could certainly be a factor in a good deal of embedded environments, we also need to look at the resource requirements of the binary in question as well. Sqlite doesn't need too much more memory than what its binary needs whereas with postgresql, you need all sorts of bells and whistles just to get the database system to boot.

Well, sure, sqllite is smaller than postgres, but database system performance is largely dictated by the size of the buffer cache. For something like a configuration database (a great use of sqllite) this does not matter. But for more than a very modest amount of data more memory for buffers will benefit both postgresql and sqllite.

Anyway, not trying to make the case that postgresql is small compared to sqllite, it obviously isn't, just wanted to point out that it's not _that_ big either.

Re: SQLite Release 3.25.0 adds support for window functions

#36

Post by ifdefdebug »

silvestrov wrote: post #20
PetahNZ wrote: post #19

Earlier quoted context omitted.

Well if you don't mind rewriting tables, just create a table in the new structure, insert data from the old table to the new one, drop the old table, and rename the new table.

That’s not enough because it ignores all foreign key constraints.

Lock db exclusively, disable foreign key checking, rebuild and replace table, reconfigure foreign keys (they are gone after delete and rename), and everything should be fine.

Re: SQLite Release 3.25.0 adds support for window functions

#37

Post by ngrilly »

plq wrote: post #23
silvestrov wrote: post #6

I’d really really like if they improved “alter table” to include dropping/renaming columns/constraints, even if it required rewriting the whole table.

There is nothing stopping you from doing it yourself. The Sqlite FAQ[1] even has an entry on it. Two things holding this back: 1. code being inside our outside sqlite would not be much different. 2. the amount of additional test code would be humongous for an operation of that level of complexity. So, just do it yourself. It's not that hard. [1]: https://www.sqlite.org/faq.html#q11

Any idea on how to do this without blocking writes when SQLite is embedded in a server process?

Re: SQLite Release 3.25.0 adds support for window functions

#38

Post by ngrilly »

rypskar wrote: post #28
PetahNZ wrote: post #19

Earlier quoted context omitted.

Well if you don't mind rewriting tables, just create a table in the new structure, insert data from the old table to the new one, drop the old table, and rename the new table.

One problem is that you then might need a special case for SQLite in migrations which can lead to different db schemas in tests and production

Use the same database for test and production. Nowadays, it is really easy to install PostgreSQL or MySQL locally or in your test environment. SQLite is great, but not for replacing PostgreSQL or MySQL during tests.

Re: SQLite Release 3.25.0 adds support for window functions

#39

Post by ngrilly »

PetahNZ wrote: post #19
silvestrov wrote: post #6

I’d really really like if they improved “alter table” to include dropping/renaming columns/constraints, even if it required rewriting the whole table.

Well if you don't mind rewriting tables, just create a table in the new structure, insert data from the old table to the new one, drop the old table, and rename the new table.

Any idea on how to do this without blocking writes when SQLite is embedded in a server process?
Post reply on HN