English > Support

Apostrophe catastrophe

<< < (4/4)

AimHere:
I just tried the new version 1.0.2.6 with my database, and find I still cannot change or delete the studio that has the apostrophe in its name.

To reiterate: If I try to delete/clear the "Studio" field's value, the original value comes right back when I save the changes. If I try to replace the value with something else entirely, the movie record winds up having TWO studios shown (the original with the apostrophe and the other value I entered). If I then delete both and save again, I'm back to having the one with the apostrophe.

It does this regardless of whether I edit individual movie records or use the Mass Editor to attempt to make changes. It also doesn't matter whether I have "ignore case" checked OR unchecked in "Preferences/Lists".

As before, every occurrence of this studio's name in my database is treated as a separate entity when "View/Group by/Studio" is selected. I have 43 titles from this studio, so the studio name is repeated 43 times in "Group By/Studio" view.

It's almost as if the apostrophe somehow set some kind of "write protected" status on the "Studio" field when the records were first added to the database, AND also made the database engine treat this studio's name as "unique" for every instance it appears.

Is there anything I can do to fix this?

Aimhere

nostra:
Only new records are added correctly. The old ones can't easily be fixed, unfortunately. You can remove those duplicates and fix the database by executing the following SQL statements:

--- Code: ---CREATE VIEW PRIMEVALUES ("vid", "value") AS
SELECT MIN(EX1."sid") minsid, EX1."value" FROM STUDIOS EX1
INNER JOIN STUDIOS EX2 ON (EX1."sid" <> EX2."sid" AND EX2."value" = EX1."value")
GROUP BY EX1."value"
ORDER BY minsid
--- End code ---

--- Code: ---CREATE VIEW DUPLICATES ("dupid", "primeid") AS
SELECT "sid", "vid" FROM STUDIOS
INNER JOIN PRIMEVALUES ON (STUDIOS."value" = PRIMEVALUES."value")
WHERE (STUDIOS."sid" <> PRIMEVALUES."vid")
--- End code ---

--- Code: ---UPDATE MOVIESTUDIOS SET "sid" = (SELECT FIRST 1 "primeid" FROM DUPLICATES WHERE "dupid" = "sid")
WHERE EXISTS (SELECT FIRST 1 1 FROM DUPLICATES WHERE "dupid" = "sid")
--- End code ---

--- Code: ---DELETE FROM STUDIOS WHERE EXISTS (SELECT FIRST 1 1 FROM DUPLICATES WHERE "dupid" = "sid")
--- End code ---

You can do this by starting PVD with -debug command line parameter and selecting Personal Video Database -> Help -> SQL from the menu

AimHere:
Will this remove just the studio names, or the actual movie records associated with them?


Never mind, I just tried it and it worked.

I don't know if this was the intent of the SQL, but all of the affected movies actually do still show the name of this studio. But they are now grouped correctly with "View/Group by/Studio", which is all I wanted. :)

Thanks again, Nostra.

nostra:
It will actually fix the studio problem and leave only one Studio, setting it for all appropriate records.

AimHere:
Ah, okay.

Navigation

[0] Message Index

[*] Previous page