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

SQLite File Format Viewer

sqlite-internal.pages.dev

Post reply on HN
Print view View original

21–30 of 45 posts

Re: SQLite File Format Viewer

#21

Post by Retr0id »

hinkley wrote: post #18
jchw wrote: post #13

This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page…

I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.

sqlite itself supports a binary encoding of JSON: https://sqlite.org/jsonb.html

Re: SQLite File Format Viewer

#22

Post by hinkley »

jchw wrote: post #20
hinkley wrote: post #18

Earlier quoted context omitted.

I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.

If you want to maintain the properties that SQLite has for read use cases, you'll need to replicate a couple of features. At the very least, you'll probably want the format to still be page-based with a BTree structure. You really could get away with just using the SQLite format if you didn't mind the weirdness; a functional SQLite parser that can read tables would not be a significant amount of code. I think, though…

Yeah if I wasn’t clear I’m talking about a minimal file that SQLite can still open read only without errors, not a third party implementation. Though there might be a few tweaks that would allow SQLite to be a bit more lenient. For instance missing metadata that can be assumed. Maybe b tree nodes exceeding the usual load factor.

Re: SQLite File Format Viewer

#23

Post by runjake »

senkora wrote: post #19
NoSalt wrote: post #12

Really nice, looks great! However, I am not a fan of uploading databases to "strange" sites on the internet, so I will probably never use this.

I wish that we had a browser version of “pledge” that would: 1. Permanently restrict the browser tab from accessing the network. 2. Show an indication of this in the browser UI outside of the content area. It would be perfect for this kind of app.

In this case, itt's easier to just run it locally: https://github.com/invisal/sqlite-internal

But you can get sort of what you want by:

1. Loading a site.

2. Go into the Web Dev Tools.

3. Select the Network tab.

4. Click on the "No Throttling" dropdown and check "Offline".

5. You should now see a yellow warning icon in your Network tab and it should prevent all network access. If you reload the tab you should get the "No Internet Connection" error page.

Re: SQLite File Format Viewer

#24

Post by hinkley »

Retr0id wrote: post #21
hinkley wrote: post #18

Earlier quoted context omitted.

I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.

sqlite itself supports a binary encoding of JSON: https://sqlite.org/jsonb.html

When I said binary JSON I didn’t mean literal JSON. I meant “common denominator interchange format”. It’s too chatty by far and has dismal performance for queries. So you’re better off asking a specific question and getting a larger document that could answer many questions that you do t yet have. For CDNs things like this matter a lot.

Re: SQLite File Format Viewer

#25

Post by cwmma »

hinkley wrote: post #18
jchw wrote: post #13

This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page…

I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.

Parquet or some other column oriented data format is probably closest to what you want without getting into indexing your flat files or similar

Re: SQLite File Format Viewer

#26

Post by creatonez »

runjake wrote: post #23
senkora wrote: post #19

Earlier quoted context omitted.

I wish that we had a browser version of “pledge” that would: 1. Permanently restrict the browser tab from accessing the network. 2. Show an indication of this in the browser UI outside of the content area. It would be perfect for this kind of app.

In this case, itt's easier to just run it locally: https://github.com/invisal/sqlite-internal But you can get sort of what you want by: 1. Loading a site. 2. Go into the Web Dev Tools. 3. Select the Network tab. 4. Click on the "No Throttling" dropdown and check "Offline". 5. You should now see a yellow warning icon in your Network tab and it should prevent all network access. If you reload the tab you should get the…

What a fantastic method, I'm gonna start using this for all the random tools I upload data to expecting they are fully clientside.

Re: SQLite File Format Viewer

#27

Post by TheRealPomax »

latexr wrote: post #8

I especially like that it provides a sample database. Would be nice it were downloadable as a file, so we could explore it with our own tools as well and get a better feeling for how the tool works.

https://github.com/invisal/sqlite-internal/tree/main/public, or just download it by copying the URL from your network tab when you load it.

Re: SQLite File Format Viewer

#28

Post by w10-1 »

hinkley wrote: post #18
jchw wrote: post #13

This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page…

I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.

MongoDB's BSON?

Re: SQLite File Format Viewer

#29

Post by 79a6ed87 »

hinkley wrote: post #18
jchw wrote: post #13

This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page…

I really want a data format that is effectively binary JSON. What is the subset of all of the features of SQLite that makes either a read-only or an updatable data set that is compact. But better searchability than a streaming parser.

Have you tried MessagePack[0]?

0: https://msgpack.org/index.html

Re: SQLite File Format Viewer

#30

Post by burntcaramel »

jchw wrote: post #13

This really would've come in handy when I was debugging my own SQLite parser a couple weeks ago. One thing that initially confused me was how exactly the pages worked w.r.t. the first page on disk... I misunderstood the SQLite documentation in different ways, but it's really rather simple: the very first page is just treated as containing the file header in it, and it pushes down the rest of the data, making the page…

Yes I’m also working on a SQLite parser, mine is in raw WebAssembly. Is yours open source too? This tool will be so useful. I have basic page reading and parsing of the CREATE TABLE schema: https://github.com/RoyalIcing/SilverOrb/blob/9dacad0ce521b0d...

My plan is to create a miniature .wasm module to read .sqlite files that works in the browser. It will be in the tens of kilobytes rather than the 1 megabyte that the official fantastic sqlite.wasm is. The reduced download means even on 3G you ought to be able to load within a few seconds. You can use SQLite files as your network payloads then, and perhaps even as the working mutable state synced between server and clients.

Post reply on HN