Store data in PostgreSQL instead of JSON file #25

New Issue

reinhard · 2019-02-12T15:01:16Z

reinhard commented

2019-02-12 15:01:16 +00:00

Currently, each email sent is logged into a JSON file, including the complete content. Since JSON is a file format to which new records cannot simply be appended, for each email sent, the whole logfile is read into memory, parsed, then the new record is appended, then it's converted back to JSON and the whole file is newly written.

Obviously, this is very inefficient when we reach thousands of log entries.

I suggest to

reevaluate whether we really want the whole content in the logfile
consider to use a different file format where new records can be added by simply appending to the file, like YAML or just simple CSV.

Currently, each email sent is logged into a JSON file, including the complete content. Since JSON is a file format to which new records cannot simply be appended, for each email sent, the whole logfile is read into memory, parsed, then the new record is appended, then it's converted back to JSON and the whole file is newly written. Obviously, this is very inefficient when we reach thousands of log entries. I suggest to 1. reevaluate whether we really want the whole content in the logfile 2. consider to use a different file format where new records can be added by simply appending to the file, like YAML or just simple CSV.

max.mehl commented

2019-02-13 08:49:23 +00:00

For the PMPC open letter, we definitely need a machine-readable format to parse the different inputs of a form. Of course, we could restrain from logging the whole email content (so the text body) to the JSON file.

For publiccode.eu, I don't really care whether the actual file is YAML, JSON or CSV. I personally love JSON because it's less error-prone than YAML and has more features than CSV (like arrays).

For the PMPC open letter, we definitely need a machine-readable format to parse the different inputs of a form. Of course, we could restrain from logging the whole email content (so the text body) to the JSON file. For publiccode.eu, I don't really care whether the actual file is YAML, JSON or CSV. I personally love JSON because it's less error-prone than YAML and has more features than CSV (like arrays).

reinhard commented

2019-08-01 19:03:04 +00:00

Meanwhile, I've come to the conclusion that an SQL database (probably SQLite) would be best, considering that for every existing new registration all previous registrations are scanned for potential duplicates.

max.mehl commented

2019-08-02 09:59:04 +00:00

Excellent! However, we make some use of the JSON files, e.g. for publiccode.eu and perhaps also the REUSE API. Would we have to rewrite these accordingly?

reinhard commented

2019-08-02 10:08:33 +00:00

I would suggest that we log to both targets (JSON and SQLite) for some time until all systems consuming the logs can be migrated with no haste. Would you consider SQLite an useful data source for systems like publiccode.eu?

max.mehl commented

2019-08-02 10:40:55 +00:00

SQLite in general is a nice solution since it has a low complexity, so a +1 on that.

Currently, the pmpc site'S CMS hugo can read the JSON file directly, and there is no such feature for SQLite (at least native). But if it was possible to convert SQLite to JSON in the container, I wouldn't see a big problem with it.

SQLite in general is a nice solution since it has a low complexity, so a +1 on that. Currently, the pmpc site'S CMS hugo can read the JSON file directly, and there is no such feature for SQLite (at least native). But if it was possible to convert SQLite to JSON in the container, I wouldn't see a big problem with it.

reinhard commented

2019-08-02 11:51:27 +00:00

if it was possible to convert SQLite to JSON in the container

Do you mean in the forms container or in the pmpc container?

> if it was possible to convert SQLite to JSON in the container Do you mean in the forms container or in the pmpc container?

max.mehl commented

2019-08-02 12:02:17 +00:00

The PMPC container. Sorry for being so unclear :/

reinhard self-assigned this 2019-08-02 12:47:07 +00:00

reinhard changed title from ~~Logging into a JSON file is highly inefficient~~ to Store data in SQLite database instead of JSON file

2019-08-02 13:52:36 +00:00

reinhard removed their assignment 2020-03-23 14:43:03 +00:00

max.mehl referenced this issue

2021-12-06 15:04:51 +00:00

Feature: optional JSON endpoint for data export #62

linus commented

2022-11-16 08:17:18 +00:00

I would really like to rename this to "Store data in PostgreSQL instead of JSON file" as we would then slowly but surely converge on our Flask - SQLAlchemy - PostgreSQL Stack across multiple projects (REUSE API coming soon).

linus changed title from ~~Store data in SQLite database instead of JSON file~~ to Store data in PostgreSQL instead of JSON file

2022-11-16 11:37:08 +00:00

linus added the

enhancement

prio:high

labels 2022-12-19 12:02:09 +00:00

linus added this to the 2023-1 milestone 2023-03-14 09:14:09 +00:00

florian.vuillemot referenced this issue

2023-07-10 20:16:10 +00:00

WIP: Using PostgreSQL instead of JSON #114

florian.vuillemot self-assigned this 2023-07-10 20:17:03 +00:00

Sign in to join this conversation.