Are there any good books on the following ?

1. For webapps, it is common for the app to just be a simple CRUD wrapper around a Postgres / MySQL / Sqlite3 database.

2. For desktop/mobile apps, it is common to use Sqlite3 as the file storage format.

3. My question is: is there a style of programming (and books on this) that uses Sqlite3 as the "source of truth" for a desktop app ? I.e. instead of using Vecs, HashMaps, ... for data structures, we just:

3a. try to make the desktop app a simple MVC 3b. the View is "sql queries -> render them" 3c. the Control is "mouse keyboard events -> SQL queries" 3d. the Model is just a sqlite3 in process

The observation being: given the sqlite3 is in process, for non-dumb queries, we can run them on modern hardware fast enough for the app to be real time-ish feel

If so, is there a good book on these types of techniques? (Most books involving SQL seems to go the CRUD webapp route).

Thanks!

I know of no books are articles on "SQLite-oriented programming". However, I observe that the Forum software that you used to post your question is an example of SQLite-oriented programming. The program is Fossil. It uses an SQLite database as its source of truth. It runs on both desktop and as a web app.

Do you have any advice / recommended reading on this general direction ?

I really like this Fred Brooks quote:

"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious."

Typically, we tend to think of program state in terms of Lists, Vectors, Hashmaps, ... other data structures + algorithms to manipulate them. This tends to result in a situation where:

1. state / "truth" is spread all over the place
2. state / "truth" is often not easy to access (encapsulation), behind nested data structures
3. debugging is messy; even figuring out how to print the data is non trivial

Now, traditionally we may have had to do this due to "efficiency" and ensuring good big-Oh running time. However, with modern CPUs, it seems we can just stuff everything w/ tables ... and with proper indexes, it should be fast enough.

This type of thinking seems to require quite a bit of "unlearning"; where we:

1. view tables as the core data strcuture
2. have state/truth be a collection of tables (instead of multi level nested data structures)
3. read/write via SQL (via pointers / references)

Fossil technically satisfies my definition above, but was not quite what I had in mind for the following reason. Fossil sorta == forum software. Forum software sorta == CRUD apps, where it's tolerable to have latency on actions measured in 100ms - single digit seconds.

I was thinking of even lower latency requirements -- say something like a notepad editor, a vim/emacs, a window manager, ...

Anyway, any advice in this generation direction is welcome. I'm very interested in how far "table/sql is all you need" can take one in modern software.

Forum software sorta == CRUD apps, where it's tolerable to have latency on actions measured in 100ms - single digit seconds.

Interestingly this very page tells me that:

This page was generated in about 0.004s by Fossil 2.26

And I've never noticed any particular latency.

Perhaps your requirements are even more specific?

1. I did not realize Fossil was that fast. Thank you for pointing it out.

2. I tend to view "Forum Software" as "we can tolerate slow" -- because we do lots of writing, then we hit "Post" once in a while.

3. I'm especially interested in "interactive software" things where we want to be able to hit 30fps (30 ms / render -- which Fossil is beating at 4ms / render) -- things like a text editor, an IDE, a window manager, etc ... -- things that "refresh" on every key stroke.

realize Fossil was that fast.

Fossil is a single-file executable that essentially wraps domain-specific knowledge around a heavily-optimized, local database. Even running as a CGI, it'll be quick for the previous two reasons and, also, Unix systems can fork and exec processes like mad.

In fairness, that timer starts when the HTTP request has been fully parsed and passed down to the Fossil CGI, and ends as last bytes of the page content are being sent back to the web server from the CGI. It does not include network latency. (How could it?). The time shown is just the time needed by the Fossil CGI to generate the page, not the total round-trip time. The time shown does include total round-trip time for all SQL queries needed to generate the page, though, and perhaps that is what is relevant to this conversation.

I think the core issue of this discussion so far is:

I have done a terrible job of defining "what type of software" is the target here ?

To address this issue, let us define it as:

1. IntelliJ /IDEA style IDE
2. Window Manager

the second is drastically simpler than the first; but I think both are situations where:

1. we want to display lots of data
2. we want near instant (< 30ms) response on every key stoke / mouse movement
3. these are situations where scripting / extending would be much much much easier if all we had to do was write an SQL query

I am wondering if anyone has readings / advice towards an IDE / window manager style software where sqlite3 serves as the "source of truth"

Would any of the numerous SQLite guis fit your bill of requirements? For example, I use a program called TablePlus, on occasion, to view the data or to test convoluted queries, primarily for the ease it’s built in editor offers. Data/result update is immediate.

That said, I can’t imagine any program even slightly more involved than a db gui that could be driveable solely with SQL.

That said, I can’t imagine any program even slightly more involved than a db gui that could be driveable solely with SQL.

With all due respect, but without much evidence, I disagree with this line (and am looking for resources that shows precisely how to break this barrier.)

I am often reminded of these two quotes:

Any sufficiently complicated C or Fortran program contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of Common Lisp.

Any sufficiently complicated concurrent program in another language contains an ad hoc informally-specified bug-ridden slow implementation of half of Erlang.

and, as I stare down nested levels of List / Vec / HashMap / adhoc indexing, I wonder if there is something similar dealing with "state / source of truth" and "containing a half-impl of SQL / Relational DB" -- and sqlite3 being special here in that sqlite3 can go inprocess, unlike mysql/postgres.

Hi,

the question is how much data loss are you ready to accept to persist truth/state.

This question exists independently of the data structures in use.

If your IDE or Window Manager groups input to save it less frequently (e.g. every second if changes occured) most users would be satisfied. If reading from “disk“ is too slow/ressource intensice you can always attach in memory databases to cache/buffer data, so you can use the same abstraction. There are many frameworks that allow for simple object/relational mapping.

cheers, ralf

state / "truth"

You can build state machines using SQL.

Tables are just data structures and queries et al. are just functions, procedures tied to that table (+ connection).

It seems SQL + CTE + Windowing is Turing complete.

I'm very interested in how far "table/sql is all you need" can take one in modern software.

It may look like a hammer, but not everything is a nail

Two interesting contributions:

https://blog.coelho.net/page/2/#turing-machine-in-sql-15/

https://board.phpbuilder.com/d/10403827-a-turing-machine-in-standard-sql-no-not-in-php/

Great insights! Replying to you in general, not any point in particular:

Alan Perlis has this great quote: 9. It is better to have 100 functions operate on one data structure than 10 functions on 10 data structures. Two languages that take this to the extreme are: LISP: LIST processing language; APL: ARRAY processing language.

1. I think what I really want is more "TABLE processing language" than "sqlite3 oriented" ; i.e. treating Table s the fundamental data structure. I.e. in Rust, I am now playing with code of the form:

pub struct MyTable {
  data: Vec<T>,
  idx_1: HashMap<.., usize>,
  idx_2: HashMap<..., usize>,
}

1. Never though about Lua as "Table oriented" -- great insight -- thanks! (Always been bothered by the index-by-1).

2. There is this interesting entry "Table Oriented Programming" over at https://wiki.c2.com/?TableOrientedProgramming -- but I'm not sure about the content. I.e. I agree with the title, but can't figure out if the content is actual helpful or flamewars.

Since you mention reactivity and event handling; I am curious if you have tried https://curtclifton.net/papers/MoseleyMarks06a.pdf ("Functional Relational Programming")

On multiple occasions, I have run into this paper, found it to describe the problem I'm facing well; but I have never managed to use this in practice.