dgllghr

Oh I missed that on my first read through of the README. Very nice!

I wish the macro approach to SQLite in Swift was about generating types from a raw query rather than generating table types. I don’t want an ORM. I want to be able to write raw SQL and have the compiler check that SQL for correctness and generate the proper bindings and types for using it. Similar projects like sqlx in rust or safeql in typescript already do this.

Now I understand. Thanks!

Thank you for GRDB! I am using it in a project now and it’s been great. About the benchmarks in this repo though, how can SQLiteData be faster if it uses GRDB under the hood? Are they doing something inefficient or are they bypassing GRDB in some way?

I agree. I love my AirGradient monitor. I’ve had it for years and it still works wonderfully.

And Kirkpatrick has been pushing Americans not to believe in UFOs. When he took over AARO it was a sudden U-turn. Why would the Pentagon make him the head of AARO if he was going to go against their UFO narrative?

If you are seriously interested in this, then I would appreciate your answers to these questions! What is the specificity of your shopping list? Cheerios 12 oz, Cheerios, cereal, etc.? If cereal, is it a standin for the all the cereals you usually buy? How consistent is your list? Do you change what you buy significantly from week to week? What causes you to substitute other items for your usual items? And do you do it to save money, try new things, a little of both, something else? If you had Cheerios 12.5 oz on your list, but one of the retailers near you had a significant promotion on the family size, would you want that instead? Let’s say you put “healthy cereal” on your list and you wanted the best price on all healthy cereals. Does that mean organic, low sugar, whole grain, something else? Do you think it would be possible to figure that out from your purchasing history? How much money do you think you would have to save to visit 2 stores? Do you have loyalty program membership at all your nearby grocery retailers?

I unfortunately think this is a case of manufactured consent:

> You don’t say something because it’s true; you say it because you believe it—and you believe it because it’s what gets rewarded.

Databases like that do exist. There is ArangoDB for example. I have no idea if it’s any good

I believe AGE has unfortunately been defunded: https://github.com/apache/age/discussions/2150 It’s a shame because it seemed like being able to query data across multiple paradigms would be really useful

Sure! Here is a use case that I have now encountered multiple times. We want to do data ingestion of some kind, but one of the steps involves human intervention (editing, confirmation, annotation, etc.). For these human intervention steps, we have wanted to build a custom web UI very quickly and only for internal use. So we have turned to a low-code tool. However, we then have to do custom integration between the low-code tool and the workflow automation system. We have ultimately never actually used a workflow automation system because we end up just handling the "workflow" from the low code web UI through a DB.

But what I really want is a workflow tool that integrates with low-code in the following way: as records come in, they are stored somewhere. There is an interface where a user can "claim" a record and then do their work on it. The workflow tool handles concurrency so that a record can only be claimed by one user at a time. The user either finishes processing the record and it moves on in the workflow, they discard their work and it returns to the original state so that it can be claimed again in the future, or they save their work so that they can come back and finish processing it later. There would need to be some kind of time out mechanism or a way to return a record to the original state.

The actual processing of the record happens using a custom interface that is built with low-code. So for text annotation, they land on a custom text-annotation interface when they claim a record.

Does that make sense?

Looks very nice with a well thought out landing page and explainer video. I wish there was an offering like this that also included a low-code interface builder like Retool/Budibase/etc. so that it was easy to put a human in the loop with a web app. Appsmith has built in workflows but I have never used them because they are behind a paywall and I don't want to start paying before I know how good the workflows are.

My goal isn’t to get anyone’s hopes up about a drug fixing all issues. It’s more to encourage to cover the basics in case it helps. Because it did for me and it’s something I could have done sooner

This is a psychological issue, but everything psychological is also physical. It’s all one interconnected system. So please see a doctor and have your bloodwork done. Doing that was the first step to escaping my burnout. I wish I had done it sooner.

We had this exact problem outside my son's room. Birds would wake him up at 5 am. I put one of those fake plastic owls with a bobble head outside his window. Problem solved.

It doesn't have to be like this though. Actors and others who work on sets with David Lynch always talk about how wonderful he is to work with and how well he treats everyone. And he is also a man with singularity of vision who does things his way.

So if I understand correctly, methanol is a low emission fuel source because the CO2 is taken from the air or from plants that have captured it from the air? But if there isn’t enough supply of methanol now, won’t the gap just be filled by methanol from natural gas? And what incentive is there to change from natural gas to something more sustainable in the future? I’m skeptical that these methanol ships are an improvement.

Yea for sure. Just take your time Boeing? No pressing issues to address or anything...

That looks great, thanks for sharing! Given that the zig build system uses zig and zig just got an http client in the standard library, I think it would be feasible to port into stanchion's build. Out of curiosity, do you run unit tests locally using a container that is set up from the CI config you linked (using something like `act`)? Or do you just require sqlite to be installed when you run locally?

SQLite has a built in mechanism for loading extensions at runtime. The extensions themselves are just dynamic libraries. The main entry point for the extension is an init function that SQLite calls when the library is loaded. Within that init function, the extension can register a number of different kinds of functionality:

* custom functions, including aggregate functions, window functions, and scalar functions: https://sqlite.org/appfunc.html

* virtual tables: https://sqlite.org/appfunc.html (This is how stanchion and other extensions like FTS and sqlite-vss are implemented)

* table valued functions (also implemented through the virtual table mechanism)

* virtual file systems: https://www.sqlite.org/vfs.html

It's really impressive how extensible SQLite is, and it's the power of that extensibility that makes stanchion possible.

No diminishment taken :) I hope you find something that works for your needs

That is a great trick for COUNT(*), thank you!

That's disappointing about xFindFunction. Once I start digging into it more, I will let you know if I find any other clever tricks that you might be able to use in your extensions as well.

Have you ever reached out to the sqlite team about limitations in the virtual table mechanism that you have encountered? I'm curious how open they are to extending what is possible with virtual tables.

Stanchion already has support for a few kinds of compression that leverage patterns in a sequence of data of the same type (DuckDB calls this "lightweight compression"). Stanchion supports: bit packing for booleans and integers and constant compression (if all the values in a run of data are the same, only store the value once). The next type of lightweight compression that is planned is Run Length Encoding (RLE). And I also want to add support for lossless byte encoding (probably zstd) at some point.

Thanks for your thoughtful questions!

* SQLite has rock solid support for transactionally persisting data to disk, which is a difficult and complex thing that stanchion gets almost for free. But the downside is fitting the architecture of stanchion into sqlite's way of doing things. So that is a long way of saying yes, the biggest difficulty was first learning how sqlite works and the implementing features to work with sqlite.

* It's a good question and certainly an area worth exploring. I don't know the answer, but doing, for instance, a comparison of power consumption and performance on a phone for the same use case between sqlite+stanchion and chdb running side-by-side with sqlite would be very interesting. To spitball some ideas of areas that may benefit stanchion: caching (both code and data, application caching and hardware caching), data sharing (no need for decoding and re-encoding either within the host application or between databases), and unified transactions (this one is a stretch). As you mention, chDB (and also DuckDB) benefit from having an architecture designed for analytics.

* As mentioned elsewhere in these comments, the sqlite virtual table system does have some limitations, so I think you're right when it comes to query performance. However, those limitations are limited to the way sqlite queries data in virtual tables, so I think stanchion can be competitive on data size on disk and potentially on insert performance.

I do plan to run and publish some benchmarks of stanchion against chDB and DuckDB in the near term. So far, I haven't focused on performance with stanchion, but that will be more of a focus going forward. Plus it's good to measure first and use that to track improvements. Stay tuned!

Including the sqlite source is for the convenience of running unit tests without needing to rely on sqlite as an external dependency :)

Yes it does. I have found it easier to start simple than try to integrate the complexity of Parquet whole hog. The Parquet format is also designed with the idea of having multiple columns in the same "file". I'm sure there are ways to split the components of a Parquet file across multiple BLOBs and be able to append those BLOBs together to create a valid Parquet file, but that is more complexity and does not lend itself to existing Parquet code. Keeping the blobs separate is valuable because it means not touching data for columns that are not being read in queries.

My understanding is that Arrow has really focused on an in-memory format. It is binary, so it can be written to disk, but they are seemingly just scratching the surface on compression. Compression is a big advantage of columnar storage because really cool compression schemes like bit packing, run-length encoding, dictionary compression, etc. can be used to significantly reduce the size of data on disk.

Arrow interop especially is a great idea, though, regardless of the storage format. And if they are developing a storage-friendly version of arrow, I would certainly consider it.

DuckDB is great! But row-oriented storage is also great for many use cases. My goal is to provide a way for people to have access to the benefits of column-oriented storage without leaving behind the benefits of OLTP. Many people are already using sqlite and might not have the time/energy/budget to make a full switch to a new database. I also think there should be many options for embedded columnar storage, and right now there really aren't.

Thanks for the kind words, Alex! You have probably written more great sqlite extensions than anyone, so your feedback is very meaningful.

You are right that there are limitations. The sqlite virtual table API is very clearly designed with row-oriented access in mind. However, there are still ways that stanchion makes analytical queries faster: lazily loading segments (which are the BLOBs that contain the actual values) so data that is not accessed is skipped, using encodings like bit packing to reduce the size of data that needs to be traversed on disk, and allowing users to specify a clustered index to ensure records are ordered in a way that allows querying to minimize data access.

One area that I want to explore more deeply is the xFindFunction callback in the virtual table API. It allows the a virtual table to "overload" a function with its own implementation. I believe there are some opportunities to work around some of the limitations you are describing, but I'm not even sure at this point if they can apply to aggregate functions.

This is all theoretical until there are solid benchmarks, which is something that I want to add in the near term. And if you know of any workarounds to the limitations that you think may be useful, I am all ears!

And one would only believe in a god that can

That is why there is also solar