layout db: Entries created using the LTF interface are not always editable with the python interface

[aaronpearlman]

In the Layout DB, it is not always possible to edit certain fields using the python interface (peewee) if they were created using the LTF interface.

For example, see the entry with component name allenby_cylinder created by @mondana using the LTF text interface. It is not possible to add/sever connexions for this entry (and others created using LTF). This is just one example, but this issue is general.

If the components are added using python (peewee), then there is no issue.

Traceback (most recent call last):
  File "add_allenby_outrigger_components.py", line 45, in <module>
    if __name__ == main():
  File "add_allenby_outrigger_components.py", line 42, in main
    fix_allenby_cylinder_name() # Fix barcode name of allenby cylinder
  File "add_allenby_outrigger_components.py", line 36, in fix_allenby_cylinder_name
    layoutdb_utils.change_component_serial_num(serial_num, new_serial_num, user_name)
  File "../utilities/layoutdb_utils.py", line 86, in change_component_serial_num
    component_id.save()
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 6532, in save
    rows = self.update(**field_dict).where(self._pk_expr()).execute()
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 1898, in inner
    return method(self, database, *args, **kwargs)
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 1969, in execute
    return self._execute(database)
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 2465, in _execute
    cursor = database.execute(self)
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 3142, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/data/user-data/pearlman/.conda/envs/layoutdb/lib/python3.7/site-packages/chimedb/core/connectdb.py", line 253, in execute_sql
    cursor = super(RetryOperationalError, self).execute_sql(sql, params, commit)
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 3136, in execute_sql
    self.commit()
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 2902, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "/home/pearlman/.local/lib/python3.7/site-packages/peewee.py", line 3129, in execute_sql
    cursor.execute(sql, params or ())
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/pearlman/.local/lib/python3.7/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
peewee.IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`ch_data`.`connexion`, CONSTRAINT `connexion_ibfk_2` FOREIGN KEY (`comp_sn1`) REFERENCES `component` (`sn`))')

[mondana]

Just to be precise, components are not "created" through that interface, but https://bao.chimenet.ca/layout/add_component.php . The error is surprising because almost all (maybe most)
allenby parts have already been created through that interface similar to many of chime components.
What is fix_allenby_cylinder_name? why change it?

[mondana]

Confirmed, this is the bug in the code trying to change the name of the component. nothing wrong with that component itself. Question:
why are you changing those names? Just tried in python and can fetch component called "allenby_cyliner".

[aaronpearlman]

@mondana I am not changing any names. You asked for an example of the bug that I encountered in the past, and that's what I am showing here. I happened to have this example saved, which is why I am showing it.

Please ignore the function name fix_allenby_cylinder_name. This is not something I am trying to do right now. It is just a demonstration of the problem.

[aaronpearlman]

To summarize, if you try to add a connexion to the component allenby_cylinder using the python interface, then you receive the above error. Again, this is because the component was not created using the python interface (peewee).

You also won't be able to add a property or other attributes for this component to the database using the python interface because of this issue.

Again, this is a general issue, not specific just to allenby_cylinder.

[mondana]

I just added a connection from allenby_site to LNA2643C in ltf and then accessed them in peewee. Try this: connections = layout.connexion.select().where((layout.connexion.comp_sn1=="allenby_cylinder") | (layout.connexion.comp_sn2=="allenby_cylinder")).execute();

[mondana]

This gives you 32 FSA connecctions on one side and LNA connections on the other side. Maybe try severing the LNA connection.

[aaronpearlman]

It doesn't work - I am unable to sever the allenby_cylinder and LNA2643C connection using python (peewee). Here's the result:

(layoutdb) [pearlman@frb-analysis pipelines]$ python remove_lna_allenby_cylinder.py
INFO:chimedb:Read-only connection to MySQL database at bao.chimenet.ca port 3307 defined by chimedb.config established.
INFO:chimedb:Read-write connection to MySQL database at bao.chimenet.ca port 3307 defined by chimedb.config established.
INFO:_db_tables:Severed no connexion.

I never have a problem for components that are created using python (peewee), so the LTF interface must create entries differently in the SQL database, which makes it incompatible for modification using the python tools.

For the Outriggers, we're going to be creating a large number of components, making connections, adding properties, etc..., so this all needs to be scripted.

[mondana]

I just tried to break the connection from allenby_cylinder <-> LNA2643C in Python and it worked. Original connexion created in LTF, parts created in web interface. Here is what I used:

from ch_util import layout
layout.connect_database()
from ch_util import layout
import datetime

connections = layout.connexion.select().where((layout.connexion.comp_sn1=="a                                                                      llenby_cylinder") | (layout.connexion.comp_sn2=="allenby_cylinder")).execute();
conn = layout.connexion.select().where(layout.connexion.comp_sn1=="allenby_cylinder", layout.connexion.comp_sn2=="LNA2643C")
layout.set_user("Mandana Amiri")
layout.connect_database(read_write=True)
layout.sever_connexion(conn, time=datetime.datetime(2021, 12, 8, 23, 55), notes="example to sever connections in peewee for aaron"                )
for x in conn:
  print(x.id, x.comp_sn1, x.comp_sn2)

So can you try the code above and give an example that does not work for you along with the error and a particular line. Please do not use scripts like remove_lna_allenby_cylinder.py, just the line in python that fails along with the error code, and attach the script if you wish (e.g remove_lna_allenby_cylinder.py)

[aaronpearlman]

When I can find some free cycles, I will try your example and report back.

One obvious difference that I am noticing, based on the layout db web output, is that your code seems to add an end date for the connexion but doesn't remove it permanently from the SQL database.

My code will deactivate the connexion and delete the entry from the database.

Here's a code snippet that I would use in my layoutdb_utils repo to sever the connexion (i.e., what is contained in remove_lna_allenby_cylinder.py), which produces the output that I included above:

import datetime
import logging
import peewee
from ch_util import layout
import numpy as np

def sever_connexion(serial_num_component1, serial_num_component2, time=datetime.datetime.now(), notes=None, force=False, user_name="Aaron Pearlman"):
    """ Sever the connexion between two components in the layout db. """

    layout.connect_database(read_write=True)

    layout.set_user(user_name)

    component_id1 = layout.component.get(sn=serial_num_component1)
    component_id2 = layout.component.get(sn=serial_num_component2)

    connexion = []
    connexion.append(layout.connexion.from_pair(component_id1, component_id2))

    layout.sever_connexion(connexion, time=time, notes=notes, force=force)

def sever_allenby_cylinder_lna_connection():

    serial_num_component1 = "allenby_cylinder"
    serial_num_component2 = "LNA2643C"
    user_name = "Aaron Pearlman"

    permanent = False

    year = 2021
    month = 12
    day = 7
    time = datetime.datetime(year, month, day)

    notes = ""

    force = True

    sever_connexion(serial_num_component1, serial_num_component2, time, notes, force, user_name)

sever_allenby_cylinder_lna_connection()

We are both using the sever_connexion function in ch_util/layout.py, so I don't understand why one case works for LTF-added components and the other does not.

[mondana]

One obvious difference that I am noticing, based on the layout db web output, is that your code seems to add an end date for the connexion but doesn't remove it permanently from the SQL database.

aha! You should not remove connections. You always put an end date to a connection. The database is based on an event-driven design, so we can retrieve the history of a component. If you remove from the database the history of that component is lost and that is undesirable.