Multiple Gimmicks

[gordonjb]

Figure out how to deal with duplicates.

Currently any duplicate workers, shows, or promotions are skipped. For workers, we really want to check if their ID exists, and if it does, append the gimmick name to the "name" field if it's not already in there. This could be a "nice to have" though.

[gordonjb]

Some test SQL to try:

SELECT * FROM workers;

INSERT OR IGNORE INTO workers(worker_id, name)
                 VALUES(1234,"Elizabeth")
                 
INSERT OR IGNORE INTO workers(worker_id, name)
                 VALUES(1234,"Laura Di Matteo");
                 
INSERT OR IGNORE INTO workers(worker_id, name)
                 VALUES(1234,"Laura Di Matteo");
                 
INSERT OR IGNORE INTO workers(worker_id, name)
                 VALUES(1234,"Vegan Moth");
                 
INSERT OR IGNORE INTO workers(worker_id, name)
                 VALUES(1234,"Laura Di Matteo");
                 
SELECT * FROM workers;

INSERT INTO workers(worker_id, name)
  VALUES(1234,"Laura Di Matteo")
  ON CONFLICT(worker_id) DO UPDATE SET name=name + "/" + excluded.name;
                 
INSERT INTO workers(worker_id, name)
  VALUES(1234,"Laura Di Matteo")
  ON CONFLICT(worker_id) DO UPDATE SET name=name + "/" + excluded.name;
                 
INSERT INTO workers(worker_id, name)
  VALUES(1234,"Vegan Moth")
  ON CONFLICT(worker_id) DO UPDATE SET name=name + "/" + excluded.name;
                 
INSERT INTO workers(worker_id, name)
  VALUES(1234,"Laura Di Matteo")
  ON CONFLICT(worker_id) DO UPDATE SET name=name + "/" + excluded.name;
  
SELECT * FROM workers;

This possible solution starter uses SQLite's new(ish) UPSERT, added as of 3.24.0 (2018-06-04)

[gordonjb]

Never mind, none of the tooling (sqlite3 or sqlitebrowser) support UPSERT 🙄

Going to try some if/elses

[gordonjb]

OK, this seems to work as a starter for 10:

UPDATE workers
SET name = "Vegan Moth" || "/" || name
WHERE workers.worker_id == 1234 AND instr(name, "Vegan Moth") == 0

Need to figure out how do do this conditionally if a record already exists and insert if not. Simplest simplest way is probably to just avoid doing it in SQL and do a separate test for existance in the Python and then let python decide wether to call for an INSERT or an UPDATE.

[gordonjb]

Just realised this wouldn't work for, for instance, if we had a "Ray Rowe" entry, and tried to add the ring name "Rowe", this wouldn't be added.

I was also thinking it would be nice if the most used name was at the front. That would be much more complicated though...

[gordonjb]

#6 (comment) isn't going to work either, it's just going to keep adding names to the name every time there's an appearance 🤣

[gordonjb]

Maybe the naming burden should be put into appearances? We could then do things like names in order of most used, just have the workers table be IDs

At that point is it worth having at all

who'd be a dba

[gordonjb]

OK, maybe the thing to do is request the worker page, and grab the current name from there and just use that. We could also grab AKAs from there. Or any other info we want in future

[gordonjb]

AKAs could go in a separate field, and we could apply them just from appearances! If the text name does not match the field name, add to AKA