5 ms·

That's just begging to be an actual database. Imagine if you said everything with SQLite instead of Excel, and all of a sudden your just talking about structur

That's just begging to be an actual database. Imagine if you said everything with SQLite instead of Excel, and all of a sudden your just talking about structured config in a database. Not new, not crazy, and generally a decent practice.

michaelbuckbee 3y ago
Now I just want a spreadsheet style front end to SQLite and I'm nerd sniped into trying to figure out how to do formulas (triggers I suppose).
- speed_spread 3y ago
  Congratulations, you're about to reinvent MS Access / DBase / FoxPro... 30 years later.
  - michaelbuckbee 3y ago
    That's an excellent point.
    - speed_spread 3y ago
      I'm sorry for the initial sarcasm, it would actually be pretty cool to have those type of apps back. With everything going web-first nowadays, you could take inspiration from SQLite's own SCM, Fossil, that can run both as a CLI or a Web server.
      - eternityforest 3y ago
        I've been thinking it would be cool to build something like this. What gets me stuck is version controllability and backups. I think ideally you'd want multiple backends, either SQLite, or flat files that are Git/SyncThing friendly. I was even thinking the file format could have a file UUID and record timestamps, so that if you put a different version of the same file in the same folder(Like with a SyncThing conflict file) it would give you a merged view, with newest-record-wins logic. Formulas I think would be the easy part. Just write it all in Python and use one of the many Excel compatible formulas implementations, and just make sure all changes went through the app. Maybe you could even have a REST API to build other things on top of it. The web frontend wouldn't be too hard, it could just be a Vue3 app with an HTML table element. Then you could have a cell type who's value was a query, which would embed a DB browser list widget in the cell, and that cell type could have the option to bind its selected row to another cell. Like VB+Excel+Access+My fork of Freeboard with inputs in one!
- masklinn 3y ago
  For the most simplistic row-wise formulas, sqlite has generated columns. I’d assume the issue will be that an sql table is not free form, you can’t randomly decide to write in some other cell.
dagw 3y ago
The big difference is that every Windows computer (in a professional environment) comes with a very nice GUI tool for easily editing Excel files. A tool that basically everybody know how to use. The same cannot be said for SQLite. SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit.
- johannes1234321 3y ago
  While I'm not sure I'd be happy about "end users" to edit the Excel files as config. Somehow they get the cells mixed up and you get utter mess. And then excel confuses some value for a date and stores some other mess ... For a somewhat trained audience however it can be quite interesting for some specific problem domain ...
- eternityforest 3y ago
  Why doesn't Microsoft just add SQLite to Excel, so your formulas can query it?
  - btreecat 3y ago
    Because how else would they push MS Access?
- btreecat 3y ago
  >SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit. I think you are conflating the file with the workflow. A proper UI is the solution to making something not "terrible for end users to edit".
harperlee 3y ago
Well, that's a complete apples-to-oranges proposal with completely different requirements. With the original solution, you have an autocontained file that virtually any user knows how to edit, structure, expand, version, email, compare, discuss... The level of user knowledge that you need for a similar solution based on a standalone SQLite file that you can version is another different world, e.g. to relate two values you would need to perhaps create a view or a trigger. And even with the most knowledgeable user you would still lack functionality such as simply pasting an image as a means of documentation and be able to see it, or WYSIWYG colors.
- sofixa 3y ago
  > version, compare Gonna have to disagree with you here. Very few people know how to version, compare, let alone if there are multiple collaborators, Excel files. Is there even a decent way of doing that outside of Excel Online / Google Sheets?
  - harperlee 3y ago
    Well I've seen a great deal of people comparing two Excels by quickly alt-tabbing, and doing Sheet1!A1=Sheet2!A1 comparisons in a third sheet, and dragging the formula. Yes, that's horrible. But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file. Same with version control: you just add _v17_20230909_final to the name. Yes, it's horrible. Yes, it's buggy. But yes, it also runs the world.
    - btreecat 3y ago
      >But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file. Are you trying to suggest that either you can't do that with sqlite, or that people didn't require training to do this in Excel?
      - harperlee 3y ago
        harperlee: open list of affordances that an average user comfortably identifies with an Excel file, not really with sqlite sofixa: version and comparison is not really supported in Excel harperlee: agree that software support is not there, but in practice average user knows how to do it and is quite comfortable doing it in Excel btreecat: sqlite also has filenames and you can learn how to use it harperlee: agree on the filename, Excel files and sqlite files are externally, opaquely versionable in the same way. the point about people learning is moot though, average user already knows Excel because they were forced to in the past, but does not have the time to learn new things.

michaelbuckbee 3y ago

Now I just want a spreadsheet style front end to SQLite and I'm nerd sniped into trying to figure out how to do formulas (triggers I suppose).

speed_spread 3y ago
Congratulations, you're about to reinvent MS Access / DBase / FoxPro... 30 years later.
- michaelbuckbee 3y ago
  That's an excellent point.
  - speed_spread 3y ago
    I'm sorry for the initial sarcasm, it would actually be pretty cool to have those type of apps back. With everything going web-first nowadays, you could take inspiration from SQLite's own SCM, Fossil, that can run both as a CLI or a Web server.
    - eternityforest 3y ago
      I've been thinking it would be cool to build something like this. What gets me stuck is version controllability and backups. I think ideally you'd want multiple backends, either SQLite, or flat files that are Git/SyncThing friendly. I was even thinking the file format could have a file UUID and record timestamps, so that if you put a different version of the same file in the same folder(Like with a SyncThing conflict file) it would give you a merged view, with newest-record-wins logic. Formulas I think would be the easy part. Just write it all in Python and use one of the many Excel compatible formulas implementations, and just make sure all changes went through the app. Maybe you could even have a REST API to build other things on top of it. The web frontend wouldn't be too hard, it could just be a Vue3 app with an HTML table element. Then you could have a cell type who's value was a query, which would embed a DB browser list widget in the cell, and that cell type could have the option to bind its selected row to another cell. Like VB+Excel+Access+My fork of Freeboard with inputs in one!
masklinn 3y ago
For the most simplistic row-wise formulas, sqlite has generated columns. I’d assume the issue will be that an sql table is not free form, you can’t randomly decide to write in some other cell.

dagw 3y ago

The big difference is that every Windows computer (in a professional environment) comes with a very nice GUI tool for easily editing Excel files. A tool that basically everybody know how to use. The same cannot be said for SQLite. SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit.

johannes1234321 3y ago
While I'm not sure I'd be happy about "end users" to edit the Excel files as config. Somehow they get the cells mixed up and you get utter mess. And then excel confuses some value for a date and stores some other mess ... For a somewhat trained audience however it can be quite interesting for some specific problem domain ...
eternityforest 3y ago
Why doesn't Microsoft just add SQLite to Excel, so your formulas can query it?
- btreecat 3y ago
  Because how else would they push MS Access?
btreecat 3y ago
>SQLite is great for storing application state and config options set from within the app, but it is a pretty terrible format for end users to edit. I think you are conflating the file with the workflow. A proper UI is the solution to making something not "terrible for end users to edit".

harperlee 3y ago

Well, that's a complete apples-to-oranges proposal with completely different requirements. With the original solution, you have an autocontained file that virtually any user knows how to edit, structure, expand, version, email, compare, discuss... The level of user knowledge that you need for a similar solution based on a standalone SQLite file that you can version is another different world, e.g. to relate two values you would need to perhaps create a view or a trigger. And even with the most knowledgeable user you would still lack functionality such as simply pasting an image as a means of documentation and be able to see it, or WYSIWYG colors.

sofixa 3y ago
> version, compare Gonna have to disagree with you here. Very few people know how to version, compare, let alone if there are multiple collaborators, Excel files. Is there even a decent way of doing that outside of Excel Online / Google Sheets?
- harperlee 3y ago
  Well I've seen a great deal of people comparing two Excels by quickly alt-tabbing, and doing Sheet1!A1=Sheet2!A1 comparisons in a third sheet, and dragging the formula. Yes, that's horrible. But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file. Same with version control: you just add _v17_20230909_final to the name. Yes, it's horrible. Yes, it's buggy. But yes, it also runs the world.
  - btreecat 3y ago
    >But millions of people can autonomously resort to this, and they would be incapable of doing anything with a SQLite file. Are you trying to suggest that either you can't do that with sqlite, or that people didn't require training to do this in Excel?
    - harperlee 3y ago
      harperlee: open list of affordances that an average user comfortably identifies with an Excel file, not really with sqlite sofixa: version and comparison is not really supported in Excel harperlee: agree that software support is not there, but in practice average user knows how to do it and is quite comfortable doing it in Excel btreecat: sqlite also has filenames and you can learn how to use it harperlee: agree on the filename, Excel files and sqlite files are externally, opaquely versionable in the same way. the point about people learning is moot though, average user already knows Excel because they were forced to in the past, but does not have the time to learn new things.