4 ms·

Unless you also store contextual information alongside the raw bytes. Curious if you can clarify / specify / examplify what "semantic" means in this case?

Kalium 6y ago
Understanding a file format means knowing the significance of a given bit. Not just that X has value 13, but that 13 means something and might influence the significance of Y or Z. Having a table with columns A, B, and C doesn't say a ton about what the values in them mean. Or how they relate to one another. If it's a bunch of decimal numbers, it could be financial data, and the sums expected to sum depending on how another column that indicates a type is set. Or it could be temperature readings. Or distances, with the unit implied or tracked elsewhere. Generally this is the kind of data and rules for reasoning about data that isn't stored in a database. Certainly not a sqlite database. Column and table names are often vague or unclear, and in my experience it's an exceptionally rare developer who leaves detailed commentary on the relationships between columns and tables and their possible values in their create statements.
- mongol 6y ago
  Can you give an example where the semantic data you refer to is in the file format in a way that is better?
  - barrkel 6y ago
    The point is that having a way of accessing a column rather than needing to parse to find a byte is only a partial answer to the problem of file formats. The consuming application still needs custom code to comprehend the format, and the physical layout of data within is often only a small part of that. IMO Sqlite would be a terrible format for e.g. a word processor. Database style formats are good when partial updates are the norm, but if your application typically needs the entire data model in memory, a relational mapping is unpleasant overhead.
- dragonwriter 6y ago
  > If it's a bunch of decimal numbers, it could be financial data, and the sums expected to sum depending on how another column that indicates a type is set. Ideally, this type of invariant would be in the DB, and not as documentation but enforced in code (i.e., by triggers, since it's to complex to be a simple referential integrity constraint.) > Generally this is the kind of data and rules for reasoning about data that isn't stored in a database. The ones that are invariants should be enforced in the DB, and the ones that aren't should generally be in in-db documentation. Sqlite in particular lacks the ability to attach the metadata usually used for that purpose, but does store code comments in the schema, which provides roughly-equivalent functionality.
  - Kalium 6y ago
    > The ones that are invariants should be enforced in the DB, and the ones that aren't should generally be in in-db documentation. Sqlite in particular lacks the ability to attach the metadata usually used for that purpose, but does store code comments in the schema, which provides roughly-equivalent functionality. What level of in-db documentation are you in the habit of writing? I can honestly say I've never worked on or encountered a codebase that explained fully the significance of any of its database columns with in-db comments. I would count myself exceptionally lucky to find natural-language prose documenting such details! Generally I find myself puzzling things out by a combination of reading application code, tinkering with tests, and interrogating developers. I think I've only ever once seen any real use of comments in a real-life codebase. It was a postgres database, where columns that contained PII were commented according to whether it was plaintext or encrypted.
    - anarazel 6y ago
      > I think I've only ever once seen any real use of comments in a real-life codebase. It was a postgres database, where columns that contained PII were commented according to whether it was plaintext or encrypted. ~10 years ago I worked on a database frontend that stored schema metadata (version, additional type info) in database comments ;) I've maybe seen database comments used a handful of times. But I also worked a couple years as a consultant, so saw a lot of databases...
- sriku 6y ago
  It is well possible to capture complex structures in a set of tables. For example, the fossil SCM uses sqlite to store file contents, commit history, deltas and metadata, issue tickets, user access info, custom ticket formats if any, and wiki documentation all in a single file, driven by a single executable. Most applications don't have more complex structure than that. Furthermore, parts of the application can pull the exact bits they need at a given time instead of having to parse all the data, since sqlite can run full fledged queries. Total win IMO. The argument for this has already been made very well by the sqlite creator. https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html
  - deleted 6y ago
    [deleted]

Kalium 6y ago

Understanding a file format means knowing the significance of a given bit. Not just that X has value 13, but that 13 means something and might influence the significance of Y or Z. Having a table with columns A, B, and C doesn't say a ton about what the values in them mean. Or how they relate to one another. If it's a bunch of decimal numbers, it could be financial data, and the sums expected to sum depending on how another column that indicates a type is set. Or it could be temperature readings. Or distances, with the unit implied or tracked elsewhere. Generally this is the kind of data and rules for reasoning about data that isn't stored in a database. Certainly not a sqlite database. Column and table names are often vague or unclear, and in my experience it's an exceptionally rare developer who leaves detailed commentary on the relationships between columns and tables and their possible values in their create statements.

mongol 6y ago
Can you give an example where the semantic data you refer to is in the file format in a way that is better?
- barrkel 6y ago
  The point is that having a way of accessing a column rather than needing to parse to find a byte is only a partial answer to the problem of file formats. The consuming application still needs custom code to comprehend the format, and the physical layout of data within is often only a small part of that. IMO Sqlite would be a terrible format for e.g. a word processor. Database style formats are good when partial updates are the norm, but if your application typically needs the entire data model in memory, a relational mapping is unpleasant overhead.
dragonwriter 6y ago
> If it's a bunch of decimal numbers, it could be financial data, and the sums expected to sum depending on how another column that indicates a type is set. Ideally, this type of invariant would be in the DB, and not as documentation but enforced in code (i.e., by triggers, since it's to complex to be a simple referential integrity constraint.) > Generally this is the kind of data and rules for reasoning about data that isn't stored in a database. The ones that are invariants should be enforced in the DB, and the ones that aren't should generally be in in-db documentation. Sqlite in particular lacks the ability to attach the metadata usually used for that purpose, but does store code comments in the schema, which provides roughly-equivalent functionality.
- Kalium 6y ago
  > The ones that are invariants should be enforced in the DB, and the ones that aren't should generally be in in-db documentation. Sqlite in particular lacks the ability to attach the metadata usually used for that purpose, but does store code comments in the schema, which provides roughly-equivalent functionality. What level of in-db documentation are you in the habit of writing? I can honestly say I've never worked on or encountered a codebase that explained fully the significance of any of its database columns with in-db comments. I would count myself exceptionally lucky to find natural-language prose documenting such details! Generally I find myself puzzling things out by a combination of reading application code, tinkering with tests, and interrogating developers. I think I've only ever once seen any real use of comments in a real-life codebase. It was a postgres database, where columns that contained PII were commented according to whether it was plaintext or encrypted.
  - anarazel 6y ago
    > I think I've only ever once seen any real use of comments in a real-life codebase. It was a postgres database, where columns that contained PII were commented according to whether it was plaintext or encrypted. ~10 years ago I worked on a database frontend that stored schema metadata (version, additional type info) in database comments ;) I've maybe seen database comments used a handful of times. But I also worked a couple years as a consultant, so saw a lot of databases...
sriku 6y ago
It is well possible to capture complex structures in a set of tables. For example, the fossil SCM uses sqlite to store file contents, commit history, deltas and metadata, issue tickets, user access info, custom ticket formats if any, and wiki documentation all in a single file, driven by a single executable. Most applications don't have more complex structure than that. Furthermore, parts of the application can pull the exact bits they need at a given time instead of having to parse all the data, since sqlite can run full fledged queries. Total win IMO. The argument for this has already been made very well by the sqlite creator. https://sqlite.org/appfileformat.html https://sqlite.org/appfileformat.html
- deleted 6y ago
  [deleted]