diff options
author | Frederick Muriuki Muriithi | 2024-04-16 11:58:51 +0300 |
---|---|---|
committer | Frederick Muriuki Muriithi | 2024-04-16 11:58:51 +0300 |
commit | e1cfcced925e5f290c67b0194817b1af907ddeb6 (patch) | |
tree | e49a2b0bcca29500cd7c49156a87b68baadd4911 | |
parent | b1897696f6564301feefa113599d51aa29a04f3a (diff) | |
download | gn-gemtext-e1cfcced925e5f290c67b0194817b1af907ddeb6.tar.gz |
New issue: Data integrity issue with table identifiers
-rw-r--r-- | issues/gn-uploader/data-integrity-for-table-ids.gmi | 39 |
1 files changed, 39 insertions, 0 deletions
diff --git a/issues/gn-uploader/data-integrity-for-table-ids.gmi b/issues/gn-uploader/data-integrity-for-table-ids.gmi new file mode 100644 index 0000000..7e9d815 --- /dev/null +++ b/issues/gn-uploader/data-integrity-for-table-ids.gmi @@ -0,0 +1,39 @@ +# Data Integrity for Table Identifiers + +## Tags + +* type: bug +* status: open +* assigned: fredm +* priority: critical +* keywords: data integrity, mariadb, gn-uploader + +## Description + +The use of + +``` +SELECT MAX(Id) FROM … +``` + +to get the identifier for a new row is not very robust, especially for auto-increment fields. + +It also leads to problems when we have tables that have multiple identifiers, e.g table 'InbredSet' with identifiers 'Id' and 'InbredSetId'. + +Instead, we need to do something like: + +``` +︙ +cursor.execute("INSERT INTO InbredSet(…) …") +new_id = cursor.lastrowid +cursor.execute("UPDATE InbredSet SET InbredSetId=%s WHERE Id=%s", + (new_id, new_id)) +︙ +``` + +To see the modules that need to be updated, do + +``` +$ cd /path/to/gn-uploader/ +$ find ./ -name '*.py' -print0 | xargs --no-run-if-empty --null --max-procs=7 grep 'MAX(Id)' +``` |