background tasks - considering celery+rabbitmq, rq with redis

Currently the first access to the "editor" page incurs a significant delay while pictures are generated in the background. While this task only runs the first time and could be moved elsewhere in the code to distribute the latency more evenly, I expect the need for a task queue to arise.
https://www.fullstackpython.com/task-queues.html

Celery has workers. RQ has both workers and a queue; RabbitMQ has just the queueing system. Source

Celery versus RQ:

• https://stackoverflow.com/a/29837939/1164295
• https://www.reddit.com/r/learnpython/comments/czc0fl/celery_vs_rq_for_small_scale_projects/

RQ is simpler and Celery has more features. RQ only works with Redis.

Migrating from Celery to RQ: https://frappe.io/blog/technology/why-we-moved-from-celery-to-rq

migrating to tables in version 8

Currently the Physics Derivation Graph is "version 7: pkl and web interface". While v7 started as a Python Pickle data file, it then moved to JSON, and is now a JSON file stored as a string in Redis.

While I could rewrite the "JSON as string in Redis" into a proper Redis-based interface, my plan is to rewrite the code to support an SQLite3 backend. This would mean rewriting all the functions to use tables rather than nested dictionaries and lists.

While the in-memory data of Redis sounds attractive for low-latency, the downside is that the Redis server needs to be running in order to query the content. An SQLite3 database is available offline.

Three issues have held me back from implementing the database as tables. Two of the issues are about translating the nested dictionaries and lists to tables. I want the translation to be to a schema design that is compact (not too many tables) and tidy (no element should contain lists).

Issue: a symbol can be a constant or a variable, for constants there may be multiple values. Should this be one table with multiple rows per value for constants, or a table of symbols + a table for values?
The multiple tables is a better schema but not as intuitive for users.
Resolution: The HTML table displayed in the web interface doesn't have to be the same as the backend schema. I will use a single table for the web frontend and multiple tables in SQLite3.

Issue: the derivation table columns could be
['step id', 'inference rule', 'input expr1', 'input expr 2', 'input expr 3', 'feed 1', 'feed 2', 'feed 3', 'output expr 1', 'output expr 2', 'output expr 3']
which is one row per step and not tidy
or
['step id', 'in connection type', 'in id', 'out connection type', 'out id']
which has multiple rows per step and is tidy.
Resolution: use the tidy table schema and write a converter to the dictionary with lists?

Issue: I'm not comfortable with SQL
Resolution: learn SQL.

notes on learning redis

I'm running redis in a docker container and connecting to it from Python using
https://github.com/andymccurdy/redis-py

>>> from redis import Redis

I initially wasn't able to connect until I found this
https://stackoverflow.com/a/57681086/1164295

>>> rd.ping()
True
>>> rd = Redis(host='docker.for.mac.localhost', port=6379)

Then I used https://realpython.com/python-redis/#ten-or-so-minutes-to-redis

What keys exist?

>>> rd.keys()
[b'hits']

Look for a key that doesn't exist:

>>> print(rd.get('mykey'))
None
>>> rd.get('mykey')
>>>

Better method for looking for keys:

>>> rd.exists('mykey')

0

>>> rd.exists('hits')

1

a terrible hack to get JSON into a database

I've been using JSON to store Physics Derivation Graph content. The motive is that JSON is capable of storing data in a way that most closely reflects how I think of the data structure in Python (nested dictionaries and lists).

To support multiple concurrent users, JSON doesn't work. The multiple users with concurrent writes would require locks to ensure changes are not lost.
Migrating from JSON to a table-based data structure (e.g., MySQL, PostGRESQL, SQLite) incurs a significant rewrite. Another option would be to use Redis, specifically the ReJSON plugin that alters the flat hashes in Redis to a nested structure closer to JSON.

I'm wary of using a plugin for data storge, and I'm reluctant to rewrite the PDG as tables.
There is a terrible hack that allows me to stick with JSON while also resolving the concurrency issue that doesn't require a significant rewrite: I could serialize the JSON and store it in Redis as a very long string.

Redis has a maximum string length of 512 MB (!) according to
https://redis.io/topics/data-types

What I'm currently doing:
>>> import json
>>> path_to_db = 'data.json'
>>> with open(path_to_db) as json_file:
     dat = json.load(json_file)

Terrible hack:

Read the content as text, then save to redis
>>> with open(path_to_db) as jfil:
    jcontent = jfil.read()
>>> rd.set(name='data.json', value=jcontent)
True

which can be simplified to

>>> with open(path_to_db) as jfil:
    rd.set(name='data.json', value=jfil.read())

Then, to read the file back in, use

>>> file_content = rd.get('data.json')
>>> dat = json.loads(file_content)

data in JSON does not scale to multiple users

In version 7 of the Physics Derivation Graph I realized that I could use Python's Pickle format to serialize the data stored in memory without having to decide what storage format (CSV, XML, SQLite) is best.  That insight lead to use of JSON because everything needed fits in dictionaries and lists.

The use of Pickle and then then JSON enabled development of many features, so it was a worthwhile investment. However, some operations are not well suited to the nested dictionaries and lists. A set of tables might be better for some operations. Converting from the current dictionaries and lists to tables would be a big rewrite, so I haven't started that yet.

If I move away from JSON for storage, the current candidates are Redis and PostgreSQL and SQLite3.

Use of a relational database would require a significant rewrite since most of the functions in the PDG rely on the nested dictionaries.

As a potentially easier transition, Redis has a plugin that supports JSON:
https://redislabs.com/blog/redis-as-a-json-store/
https://redislabs.com/redis-best-practices/data-storage-patterns/json-storage/
However, I'm not comfortable with the PDG being dependent on a plugin.

If I go with a relational database, I'll need to choose which one.
MySQL or PostgreSQL versus SQLite
https://stackoverflow.com/a/5102105/1164295
"SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter)."
https://www.sqlite.org/whentouse.html
"Any site that gets fewer than 100K hits/day should work fine with SQLite."
To improve concurrency, reads can happen without blocking writes: https://www.sqlite.org/wal.html

why implementing a single feature took 12 hours

Yesterday I started investigating how to get d3.js working for the Physics Derivation Graph.  I already had an implementation working on the live website, so I didn't expect the update to take too much effort or time.

Below is the sequence of challenges I encountered for this feature update.

1. I learned that I had used v3; the current version is v5
2. v5 doesn't support the .force() used in v3
3. I found a v5-based force directed graph on https://observablehq.com/@d3/force-directed-graph
4. Although I was able to get the code running locally, I found the files seemed to depend on remote resources. 
5. I found a better instance that was pure d3.v5.js instead of relying on observable code: https://bl.ocks.org/mapio/53fed7d84cd1812d6a6639ed7aa83868
6. Figured out how to get images associated with nodes
7. The JSON file needs images to have distinct and consistent names
8. Instead of temporary image file names, use expr_global_id and expr_name
9. The functions using "return False, error_message" meant the errors didn't propagate to the web interface. The "right" method is to use "raise Exception" 
10. With exceptions raised in compute, needed to add "try/except" in controller.py
11. With Exceptions caught in controller.py, use flash() to tell the user there was a problem
12. With Exceptions now sent to user via web interface, I learned that the PNG wasn't being created due to a missing command, "braket"
13. I found that "braket" is a latex package available from CTAN
14. I tried to install "braket" using "tlmgr install"; see https://tex.stackexchange.com/questions/73016/how-do-i-install-an-individual-package-on-a-linux-system
15. I wasn't able to run "tlmgr" in Docker due to not having wget
16. I wasn't able to install wget in Docker using "apt-get install -y wget", possible due to using phusion as a base image?
17. Looked up instructions on installing packages manually; opened https://github.com/allofphysicsgraph/proofofconcept/issues/82
18. In the process of debugging the PDF generation (notice that I strayed from the d3js effort), realized the migration of inference rules was incomplete -- new style is to have words separated by spaces in create_tmp_db.py
19. Added an exception in compute.py to identify inconsistent inference rule names
20. Manually fixed inference rule entries in create_tmp_db.py
21. Altered the inference rule schema in compute.py -- use feeds+inputs+outputs
22. Manually updated inference rules in create_tmp_db.py to reflect revised schema
23. Compiling derivation PDF failed due to incorrect implementation of inference rule
24. Realized that the "braket" issue wasn't a missing package, it was custom macros defined in an old version of the PDG
25. Wrote function to generate JSON needed for d3js
26. In the process of iterating that, added page latency measurement

Lessons learned:

• In the process of implementing a new feature or updating a feature, I uncovered a few bugs and a lot of technical debt that lead to the implementation taking longer than expected
• Some of the bugs were easy to fix (aka buy down the tech debt) as I discovered them, while others were sufficiently worthy of a new ticket. 
• Some bugs were blockers -- I couldn't proceed with the desired work until I resolved architecture flaws; other issues were tangential and could be delayed.

data structure continues to evolve

I've experimented with seven different data structures for the Physics Derivation Graph:

• v1_plain_text
• v2_XML
• v3_CSV
• v4_file_per_expression
• v5_property_graph
• v6_sqlite
• v7_pickle_web_interface

Each of these have required a rewrite of the code from scratch, as well as transfer code (to move from n to n+1). 

These changes progress concurrently with my knowledge of data structures. I didn't know about property graphs when I was implementing v1, v2, and v3. I wasn't comfortable with SQL when I implemented v4. I didn't know about Tidy data when I implemented v1 to v6.  The data structures used in the PDG slightly lag my understanding of data structures. 

Within a given implementation, there are design decisions with trade-offs to evaluate. I typically don't know all the options or consequences until I implement one of them and then determine what inefficiencies exist. Knowledge gained through evolutionary iteration is expensive and takes a lot of time. 

Here's an example of two "minor" tweaks that incur a rewrite of all the code. My current data structure in v7 is

dat['derivations'] = {

  'fun deriv': { # name of derivation

     '4928482': {    # key is "step ID"   

          'inf rule': 'declare initial expr',

          'inputs':  {},

          'feeds':   {},

          'outputs': {'9428': '4928923942'}, # key is "expr local ID", value is "expr global ID"

          'linear index': 1}, # linear index for PDF and for graph orientation

     '2948592': {

          'inf rule': 'add X to both sides',

          'inputs':  {'9428': '4928923942'},

          'feeds':   {'3190': '9494829190'},

          'outputs': {'3921': '9499959299'},

          'linear index': 2},

A better data structure would be

dat['derivations'] = {

  'fun deriv': { # name of derivation

     '4928482': {    # key is "step ID"   

          'inf rule': 'declare initial expr',

          'inputs':  {},

          'feeds':   {},

          'outputs': {1: '9428'}, # key is index, value is "expr local ID"

          'linear index': 1}, # linear index for PDF and for graph orientation

     '2948592': {

          'inf rule': 'add X to both sides',

          'inputs':  {1: '9428'},

          'feeds':   {1: '3190'},

          'outputs': {1: '3921'},

          'linear index': 2},

dat['expr local to global'] = {

        '9428': '4928923942',

        '3190': '9494829190',

        '3921': '9499959299',

        '9128': '1492842000'}

The reasons this second data structure is an improvement is

1. the global expression ID does not appear in the 'derivations' dict
2. the inputs, feeds, and outputs have an index. The index is relevant for both printing in a PDF and use in inference rules. 

I'm slowly evolving towards the likelihood that there will be a "v8" based on tables. The backend database would be something like SQLite3, and the internal representation in Python would be dataframes. 

I'm not going to switch to v8 yet; I'll continue to invest effort in v7 for a bit longer to explore a few challenges (like implementation of inference rules).