6 ms·

This will probably get buried, but I'm wondering: Has any scripting language attempted to use SQLite as its memory model? You get GC, memory safety, atomicity g

This will probably get buried, but I'm wondering: Has any scripting language attempted to use SQLite as its memory model? You get GC, memory safety, atomicity guarantees, core dump functionality, and rich data structures for free. If configured to run in-memory, I reckon it wouldn't impose enough overhead to be problematic. It sounds crazy, but most scripting languages add this stuff anyway. With rqlite this can be extended to distributed systems programming as well.

aziis98 2y ago
I also think about this sometimes, maybe not as only memory model but a simple scripting language with tight integration with sqlite would be pretty cool. P.S. Actually if think I saw something similar somewhere in the past, if it comes to mind I'll send it
- Imustaskforhelp 2y ago
  yeah dude send it , I am thinking of writing a scripting language some years later and I have absolutely loved this idea.
  - acuozzo 2y ago
    I'm not aziis98, but Smalltalk had something similar which was significantly greater in scope. IIRC, your application shipped with the entire development environment.
    - igouy 2y ago
      Unless you removed it.
emseetech 2y ago
That's interesting. There would be overhead compared to directly managing memory. But it would be wild to be able to declare an array of structs that you could directly query. var people person[5] people[5] // { name: "Human Person", age: 39 } people.query("SELECT name FROM a WHERE age = 39") // "Human Person" If everything in memory was a table structure, you could do joins across variables too. Would it have to be a scripted language? Seems like you could do it as a compiled language. I love SQL so if I had the option to use it within in application I'd be hard pressed not to. But from what I've learned from the popularity of ORMs and NoSQL, most don't share that feeling.
- acuozzo 2y ago
  > that you could directly query To take it even further, the language grammar could be a superset of the SQL grammar. > Would it have to be a scripted language? Not at all. It's just the first thing which came to mind. I'm tickled by the idea of having the entire program state (stack frames and all!) in SQLite so that you could ship a snapshot of your program around. > There would be overhead compared to directly managing memory. Of course, but if the critical queries were pre-compiled via sqlite3_prepare, I doubt the overhead would so greatly exceed the memory management overhead in language runtimes which ship with a GC that it would be a show-stopper.
  - Imustaskforhelp 2y ago
    wow the idea of shipping a snapshot is also crazy good , bookmarking your comment as well the parent comment as well. Because snapshotting is a rather crazy thing which have to be managed by criu or qemu this could even be faster than a vm shipped. MY goal with snapshots can be literally anything (yet , I like the idea of extraction snapshots for extremely large files very lucrative idea)
    - acuozzo 2y ago
      Thank you! Considering this further... I think Lua might be a good match for this idea. Everything it can do can be pretty easily mapped to SQLite queries and its type system is pretty close to the limited set of types offered by SQLite.
      - Imustaskforhelp 2y ago
        yup , I was actually looking at the tglite bytecode engine. I am really interested in this project. My thought was about arturo lang though https://arturo-lang.io/ https://arturo-lang.io/ since it has https://github.com/arturo-lang/grafito https://github.com/arturo-lang/grafito , and it just felt like having a better integration with sql though lua is also fine. We can also integrate this directly to something like lua running on sqlite compiled by cosmopolitan project to even ship a cross platform single executable sqlite. The possibilites are endless. If you are genuinely interested in this project. I am willing to help in my free time. But I am a novice and I don't have experience in low level langauges like C/rust and much prefer golang.
Imustaskforhelp 2y ago
this is such a crazy good take , I was thinking of creating a golang based module loader / code loader directly from a sqllite file and a tool / cli that can help you with this thing where what I intend to do is lets say my cli name is goter (go + motor IDK) goter init (creates a sqllite and a main.go etc. but all main.go etc. are just a symlink to something inside sqllite I am not sure , its just a really really hypothetical idea) goter add dep <some dependency> (which it also then loads in that sqllite file) goter run main.go (which can run) goter share (which can output a sqllite file) goter load .db file (which could even have whole golang versions inside of it) goter build docker (creates a docker image , if possible) goter collab ipc (creates a ipc gateway between two servers using something like piping-web if behind nat or directly as well) (to have really easy multi deployments) and integration with popular tools like go-blueprint / even the ability to create custom templates like go-blueprint if possible since clack has been migrated to go by some guy. this doesn't have to be golang specific , it can be language agnostic. My thoughts about this were creating a dead simple cloud editor / code runner (where the code runs inside bubblewrap) and this has the golang code and the whole codebase of a single user is just a sqllite file , insanely simple to transport , Insanely efficient. I am not sure if this makes sense , but you have guided me in a direction that feels just as enlightening as the day I discovered cosmopolitan project on hackernews / redbean (offtopic) (cross platform executables say what!) THANKS A LOT , I AM BOOKMARKING THIS , THIS IS SO SO GOOD OF AN IDEA THAT I MIGHT'VE LOST A BIT OF MY MIND AT 1 AM Comprehending how good of an idea this might be.
dpe82 2y ago
Not crazy at all; just uncommon. It would be an implementation of the basic recommendation from the excellent paper, "Out of the Tar Pit". https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf
- acuozzo 2y ago
  Thank you for the link! Reading now.
atombender 2y ago
I think the core idea is fascinating. This model of making the data of the application transparently persistent was a fad in the 1980s-1990s when object databases like Objectivity and GemStone were all the rage. Those faded from the mainstream for various reasons, though they're still around and still heavily used in industries such as finance. Object databases like Objectivity, POET, Matisse, and others provide tools that took your source code and augmented your classes with low-level code that automatically loaded objects from the database. So you wrote your classes and ran the tools, and you suddenly got magical persistent objects. (A lot like CORBA and COM, but usually without an IDL.) These databases initially only did C++, but later they supported Java, which had the big benefit that the tool could take the compiled byte code, disassemble it, inject the persistent glue, and then assemble it again. (GemStone was Smalltalk, which already has this idea of persistent built in.) Someone eventually applied this to Python, which had the benefit, being a dynamic language like Python, of letting everything be done entirely at runtime. The most successful project was Zope, the CMS, which came with the ZODB. With ZODB, you could treat entire graphs of Python values as transparently persisted to a database file: You just did "db.users[1].name = 'bob'" and that was it. Of course, this results in the access path problem described in the tar pit paper. Without a way to slice and dice data from any angle, your stuck with the ad-hoc graph structure you've created through values and their relationships, as opposed to the relational model. I'm not sure this is entirely an unsolved problem, though lack of queryability was the Achilles' heel of all these objects databases. Another persistent language is M [1], also known as MUMPS, a quirky language and toolchain that came out of NIH and has been popular in the medical industry since the 1970s, and also in banking (Fidelity National). M is tightly coupled with a kind of hierarchical array-oriented database; any variable can be declared as persistent, meaning that they will be read from and written to the data store, and persisted across runs. Since the underlying representation are in the form of sparse arrays, you can have lots of huge, nested data structures that are super efficient to read and write. I'm not sure there is anything else like it. In fact, InterSystems, the main commercial vendor of M software, markets their implementation as an object database (it also does SQL, on top of M). [1] https://en.wikipedia.org/wiki/MUMPS https://en.wikipedia.org/wiki/MUMPS
vvern 2y ago
Have you taken a peek at sqlsync? It's a cool take where you write your entire application logic to operate as transactions and queries over a sqlite database but you do it in wasm so that the same transaction logic can run everywhere. The end result is nifty. That's at least my recollection of the thing. https://sqlsync.dev/ https://sqlsync.dev/
prirun 2y ago
I had a situation with HashBackup (I'm the author) where I needed a map-like data structure that could get rather large (hundreds of MB to a few GB) with big backups. In Python, storing this data in a dict was not really feasible because every unique integer takes 24 bytes, and since the data structure stores things like blockid and file offsets, there are a lot of unique integers. Instead, I created an in-memory SQLite database for the data structure and it was something like 1/3 the size of the Python dict. It was around 10-15% slower, but for the memory savings it was an okay trade-off.
Imustaskforhelp 2y ago
So I just found brainfck written in sql and its crazy good I was hoping that I could find something like lisp intepreter in brainfck and AI hallucinated a hell lot , I implemented a brainfck interpreter in pure SQL using CTEs 23 points•15 comments•submitted 6 years ago by chunkyksSQLite, db of champions to r/SQL I did this in SQLite, but it'll probably work in other databases. Why? No good reason. Anyways, this program is a bit traditional: $ sqlite3 < bf.sql Hello World! WITH RECURSIVE program AS (SELECT '++++++++++[>+++++++>++++++++++>+++>+<<<<-]>++.>+.+++++++..+++.>++.<<+++++++++++++++.>.+++.------.--------.>+.>.' AS p, '' AS input, 3 AS width ), jumpdepth AS (SELECT 0 AS idx, 0 AS jumpdepth, '' AS jumplist, NULL as jumpback, NULL AS direction, p || '0' AS p, width FROM program UNION ALL SELECT idx+1, CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN jumpdepth+1 WHEN ']' THEN jumpdepth-1 ELSE jumpdepth END, CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN SUBSTR('0000000' || (idx+1), -width) || jumplist WHEN ']' THEN SUBSTR(jumplist,width+1) ELSE jumplist END, CASE SUBSTR(p, idx+1, 1) WHEN ']' THEN CAST(SUBSTR(jumplist,1,width) AS INTEGER) ELSE NULL END, CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN 'L' WHEN ']' THEN 'R' ELSE NULL END, p, width FROM jumpdepth WHERE LENGTH(p)>=idx), jumptable(a,b,dir) AS (SELECT idx,jumpback,'L' FROM jumpdepth WHERE jumpback IS NOT NULL UNION ALL SELECT jumpback,idx+1,'R' FROM jumpdepth WHERE jumpback IS NOT NULL), bf(ep, p, width, defaulttapeentry, ip, dp, instruction, output, input, tape) AS (SELECT 0, p, width, SUBSTR('0000000', -width), 1, 1, '', '', input, SUBSTR('000000', -width) FROM program UNION ALL SELECT ep+1, p, width, defaulttapeentry, CASE WHEN jumptable.b IS NOT NULL AND ((dir='R' AND CAST(SUBSTR(tape, width(dp-1)+1, width) AS INTEGER)=0) OR (dir='L' AND CAST(SUBSTR(tape, width(dp-1)+1, width) AS INTEGER)!=0)) THEN jumptable.b ELSE ip+1 END, CASE SUBSTR(p, ip, 1) WHEN '>' THEN dp+1 WHEN '<' THEN MAX(dp-1,1) ELSE dp END, SUBSTR(p, ip, 1), CASE WHEN SUBSTR(p, ip, 1)='.' THEN (output || CHAR(SUBSTR(tape, (dp-1)width+1, width))) ELSE output END, CASE WHEN SUBSTR(p, ip, 1)=',' THEN SUBSTR(input, 2) ELSE input END, CASE SUBSTR(p, ip, 1) WHEN '<' THEN CASE WHEN dp=1 THEN defaulttapeentry || tape ELSE tape END WHEN '>' THEN CASE WHEN dpwidth=LENGTH(tape) THEN tape || defaulttapeentry ELSE tape END WHEN '+' THEN SUBSTR(tape,1,width(dp-1)) || SUBSTR('0000000' || (CAST(SUBSTR(tape,width(dp-1)+1,width) AS INTEGER)+1), -width) || SUBSTR(tape,widthdp+1) WHEN '-' THEN SUBSTR(tape,1,width(dp-1)) || SUBSTR('0000000' || (CAST(SUBSTR(tape,width(dp-1)+1,width) AS INTEGER)-1), -width) || SUBSTR(tape,widthdp+1) WHEN ',' THEN SUBSTR(tape,1,width(dp-1)) || SUBSTR('0000000' || (UNICODE(SUBSTR(input,1,1))), -width) || SUBSTR(tape,width*(dp+1)) ELSE tape END FROM bf LEFT JOIN jumptable ON jumptable.a=ip WHERE LENGTH(p) >= ip) SELECT output FROM bf ORDER BY ep DESC LIMIT 1; https://www.reddit.com/r/brainfuck/comments/83cw7l/i_implemented_a_brainfck_interpreter_in_pure_sql/ https://www.reddit.com/r/brainfuck/comments/83cw7l/i_impleme...

aziis98 2y ago

I also think about this sometimes, maybe not as only memory model but a simple scripting language with tight integration with sqlite would be pretty cool. P.S. Actually if think I saw something similar somewhere in the past, if it comes to mind I'll send it

Imustaskforhelp 2y ago
yeah dude send it , I am thinking of writing a scripting language some years later and I have absolutely loved this idea.
- acuozzo 2y ago
  I'm not aziis98, but Smalltalk had something similar which was significantly greater in scope. IIRC, your application shipped with the entire development environment.
  - igouy 2y ago
    Unless you removed it.

emseetech 2y ago

That's interesting. There would be overhead compared to directly managing memory. But it would be wild to be able to declare an array of structs that you could directly query. var people person[5] people[5] // { name: "Human Person", age: 39 } people.query("SELECT name FROM a WHERE age = 39") // "Human Person" If everything in memory was a table structure, you could do joins across variables too. Would it have to be a scripted language? Seems like you could do it as a compiled language. I love SQL so if I had the option to use it within in application I'd be hard pressed not to. But from what I've learned from the popularity of ORMs and NoSQL, most don't share that feeling.

acuozzo 2y ago
> that you could directly query To take it even further, the language grammar could be a superset of the SQL grammar. > Would it have to be a scripted language? Not at all. It's just the first thing which came to mind. I'm tickled by the idea of having the entire program state (stack frames and all!) in SQLite so that you could ship a snapshot of your program around. > There would be overhead compared to directly managing memory. Of course, but if the critical queries were pre-compiled via sqlite3_prepare, I doubt the overhead would so greatly exceed the memory management overhead in language runtimes which ship with a GC that it would be a show-stopper.
- Imustaskforhelp 2y ago
  wow the idea of shipping a snapshot is also crazy good , bookmarking your comment as well the parent comment as well. Because snapshotting is a rather crazy thing which have to be managed by criu or qemu this could even be faster than a vm shipped. MY goal with snapshots can be literally anything (yet , I like the idea of extraction snapshots for extremely large files very lucrative idea)
  - acuozzo 2y ago
    Thank you! Considering this further... I think Lua might be a good match for this idea. Everything it can do can be pretty easily mapped to SQLite queries and its type system is pretty close to the limited set of types offered by SQLite.
    - Imustaskforhelp 2y ago
      yup , I was actually looking at the tglite bytecode engine. I am really interested in this project. My thought was about arturo lang though https://arturo-lang.io/ https://arturo-lang.io/ since it has https://github.com/arturo-lang/grafito https://github.com/arturo-lang/grafito , and it just felt like having a better integration with sql though lua is also fine. We can also integrate this directly to something like lua running on sqlite compiled by cosmopolitan project to even ship a cross platform single executable sqlite. The possibilites are endless. If you are genuinely interested in this project. I am willing to help in my free time. But I am a novice and I don't have experience in low level langauges like C/rust and much prefer golang.

Imustaskforhelp 2y ago

this is such a crazy good take , I was thinking of creating a golang based module loader / code loader directly from a sqllite file and a tool / cli that can help you with this thing where what I intend to do is lets say my cli name is goter (go + motor IDK) goter init (creates a sqllite and a main.go etc. but all main.go etc. are just a symlink to something inside sqllite I am not sure , its just a really really hypothetical idea) goter add dep <some dependency> (which it also then loads in that sqllite file) goter run main.go (which can run) goter share (which can output a sqllite file) goter load .db file (which could even have whole golang versions inside of it) goter build docker (creates a docker image , if possible) goter collab ipc (creates a ipc gateway between two servers using something like piping-web if behind nat or directly as well) (to have really easy multi deployments) and integration with popular tools like go-blueprint / even the ability to create custom templates like go-blueprint if possible since clack has been migrated to go by some guy. this doesn't have to be golang specific , it can be language agnostic. My thoughts about this were creating a dead simple cloud editor / code runner (where the code runs inside bubblewrap) and this has the golang code and the whole codebase of a single user is just a sqllite file , insanely simple to transport , Insanely efficient. I am not sure if this makes sense , but you have guided me in a direction that feels just as enlightening as the day I discovered cosmopolitan project on hackernews / redbean (offtopic) (cross platform executables say what!) THANKS A LOT , I AM BOOKMARKING THIS , THIS IS SO SO GOOD OF AN IDEA THAT I MIGHT'VE LOST A BIT OF MY MIND AT 1 AM Comprehending how good of an idea this might be.

dpe82 2y ago

Not crazy at all; just uncommon. It would be an implementation of the basic recommendation from the excellent paper, "Out of the Tar Pit". https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf

acuozzo 2y ago
Thank you for the link! Reading now.

atombender 2y ago

I think the core idea is fascinating. This model of making the data of the application transparently persistent was a fad in the 1980s-1990s when object databases like Objectivity and GemStone were all the rage. Those faded from the mainstream for various reasons, though they're still around and still heavily used in industries such as finance. Object databases like Objectivity, POET, Matisse, and others provide tools that took your source code and augmented your classes with low-level code that automatically loaded objects from the database. So you wrote your classes and ran the tools, and you suddenly got magical persistent objects. (A lot like CORBA and COM, but usually without an IDL.) These databases initially only did C++, but later they supported Java, which had the big benefit that the tool could take the compiled byte code, disassemble it, inject the persistent glue, and then assemble it again. (GemStone was Smalltalk, which already has this idea of persistent built in.) Someone eventually applied this to Python, which had the benefit, being a dynamic language like Python, of letting everything be done entirely at runtime. The most successful project was Zope, the CMS, which came with the ZODB. With ZODB, you could treat entire graphs of Python values as transparently persisted to a database file: You just did "db.users[1].name = 'bob'" and that was it. Of course, this results in the access path problem described in the tar pit paper. Without a way to slice and dice data from any angle, your stuck with the ad-hoc graph structure you've created through values and their relationships, as opposed to the relational model. I'm not sure this is entirely an unsolved problem, though lack of queryability was the Achilles' heel of all these objects databases. Another persistent language is M [1], also known as MUMPS, a quirky language and toolchain that came out of NIH and has been popular in the medical industry since the 1970s, and also in banking (Fidelity National). M is tightly coupled with a kind of hierarchical array-oriented database; any variable can be declared as persistent, meaning that they will be read from and written to the data store, and persisted across runs. Since the underlying representation are in the form of sparse arrays, you can have lots of huge, nested data structures that are super efficient to read and write. I'm not sure there is anything else like it. In fact, InterSystems, the main commercial vendor of M software, markets their implementation as an object database (it also does SQL, on top of M). [1] https://en.wikipedia.org/wiki/MUMPS https://en.wikipedia.org/wiki/MUMPS

vvern 2y ago

Have you taken a peek at sqlsync? It's a cool take where you write your entire application logic to operate as transactions and queries over a sqlite database but you do it in wasm so that the same transaction logic can run everywhere. The end result is nifty. That's at least my recollection of the thing. https://sqlsync.dev/ https://sqlsync.dev/

prirun 2y ago

I had a situation with HashBackup (I'm the author) where I needed a map-like data structure that could get rather large (hundreds of MB to a few GB) with big backups. In Python, storing this data in a dict was not really feasible because every unique integer takes 24 bytes, and since the data structure stores things like blockid and file offsets, there are a lot of unique integers. Instead, I created an in-memory SQLite database for the data structure and it was something like 1/3 the size of the Python dict. It was around 10-15% slower, but for the memory savings it was an okay trade-off.

Imustaskforhelp 2y ago

So I just found brainfck written in sql and its crazy good I was hoping that I could find something like lisp intepreter in brainfck and AI hallucinated a hell lot , I implemented a brainfck interpreter in pure SQL using CTEs 23 points•15 comments•submitted 6 years ago by chunkyksSQLite, db of champions to r/SQL I did this in SQLite, but it'll probably work in other databases. Why? No good reason. Anyways, this program is a bit traditional: $ sqlite3 < bf.sql Hello World! WITH RECURSIVE program AS (SELECT '++++++++++[>+++++++>++++++++++>+++>+<<<<-]>++.>+.+++++++..+++.>++.<<+++++++++++++++.>.+++.------.--------.>+.>.' AS p, '' AS input, 3 AS width ), jumpdepth AS (SELECT 0 AS idx, 0 AS jumpdepth, '' AS jumplist, NULL as jumpback, NULL AS direction, p || '0' AS p, width FROM program UNION ALL SELECT idx+1, CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN jumpdepth+1 WHEN ']' THEN jumpdepth-1 ELSE jumpdepth END, CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN SUBSTR('0000000' || (idx+1), -width) || jumplist WHEN ']' THEN SUBSTR(jumplist,width+1) ELSE jumplist END, CASE SUBSTR(p, idx+1, 1) WHEN ']' THEN CAST(SUBSTR(jumplist,1,width) AS INTEGER) ELSE NULL END, CASE SUBSTR(p, idx+1, 1) WHEN '[' THEN 'L' WHEN ']' THEN 'R' ELSE NULL END, p, width FROM jumpdepth WHERE LENGTH(p)>=idx), jumptable(a,b,dir) AS (SELECT idx,jumpback,'L' FROM jumpdepth WHERE jumpback IS NOT NULL UNION ALL SELECT jumpback,idx+1,'R' FROM jumpdepth WHERE jumpback IS NOT NULL), bf(ep, p, width, defaulttapeentry, ip, dp, instruction, output, input, tape) AS (SELECT 0, p, width, SUBSTR('0000000', -width), 1, 1, '', '', input, SUBSTR('000000', -width) FROM program UNION ALL SELECT ep+1, p, width, defaulttapeentry, CASE WHEN jumptable.b IS NOT NULL AND ((dir='R' AND CAST(SUBSTR(tape, width(dp-1)+1, width) AS INTEGER)=0) OR (dir='L' AND CAST(SUBSTR(tape, width(dp-1)+1, width) AS INTEGER)!=0)) THEN jumptable.b ELSE ip+1 END, CASE SUBSTR(p, ip, 1) WHEN '>' THEN dp+1 WHEN '<' THEN MAX(dp-1,1) ELSE dp END, SUBSTR(p, ip, 1), CASE WHEN SUBSTR(p, ip, 1)='.' THEN (output || CHAR(SUBSTR(tape, (dp-1)width+1, width))) ELSE output END, CASE WHEN SUBSTR(p, ip, 1)=',' THEN SUBSTR(input, 2) ELSE input END, CASE SUBSTR(p, ip, 1) WHEN '<' THEN CASE WHEN dp=1 THEN defaulttapeentry || tape ELSE tape END WHEN '>' THEN CASE WHEN dpwidth=LENGTH(tape) THEN tape || defaulttapeentry ELSE tape END WHEN '+' THEN SUBSTR(tape,1,width(dp-1)) || SUBSTR('0000000' || (CAST(SUBSTR(tape,width(dp-1)+1,width) AS INTEGER)+1), -width) || SUBSTR(tape,widthdp+1) WHEN '-' THEN SUBSTR(tape,1,width(dp-1)) || SUBSTR('0000000' || (CAST(SUBSTR(tape,width(dp-1)+1,width) AS INTEGER)-1), -width) || SUBSTR(tape,widthdp+1) WHEN ',' THEN SUBSTR(tape,1,width(dp-1)) || SUBSTR('0000000' || (UNICODE(SUBSTR(input,1,1))), -width) || SUBSTR(tape,width*(dp+1)) ELSE tape END FROM bf LEFT JOIN jumptable ON jumptable.a=ip WHERE LENGTH(p) >= ip) SELECT output FROM bf ORDER BY ep DESC LIMIT 1; https://www.reddit.com/r/brainfuck/comments/83cw7l/i_implemented_a_brainfck_interpreter_in_pure_sql/ https://www.reddit.com/r/brainfuck/comments/83cw7l/i_impleme...