This is a general question, nothing specifically wrong with my archive at the moment. I'm wanting to upgrade my archive to the latest version - it's an older 2.x.x version and not secure obviously, and I really need to upgrade because of spam problems. However, I've been using the script from the 1.x days, from before "sitewide characters" was implemented - my archive has multiple categories, and each has the same list of characters. (For example, Category A has Char1, Char2, and Char3, and Category B also has its own instance of Char1, Char2, and Char3.) I would like to merge all the category character lists into one sitewide list, but I'd also like to have this change apply to all current fanfics (i.e., for each fic, change each individual instance of a category-specific character to the sitewide character). Is there any script/code snippet that can do this en masse, or any coding I could input in phpmyadmin? If I have to run the coding/script for each character (and input 'before' and 'after' character numbers manually into the code), that would be fine. The problem is that the archive has over 1,000 fics, so I don't have the time to manually edit every individual fic. I apologize if this is addressed in the most recent version of the script - I'm basing this off of an upgrade attempt I tried many months ago (3.3 or so, maybe?). Thanks for any help.
This one can be handled in PHPMyAdmin, I think. You shouldn't have to edit each story.
To change characters to sitewide, change their parentcatid in fanfiction_characters to -1. But you'd probably want to change the characters over first...
The issue is you'll have duplicates. So I think you can build a query that will work, because each character, though having the same character name, will have a different catid and charid assigned.
You'd probably want to arbitrarily pick one of the categories and change all the characters for each story into the characters for that category, and then assign that category sitewide and delete the rest. I'd have to think a bit on a query that would do that for you. I think it can be done but it would be complicated. How many characters are we talking about here? (Including all the duplicates.)
Yeah, the big issue during my test-upgrade was the duplicate characters. Right now, there are four categories, each with the same 25 characters, so there are 100 characters total across the site.
I can do some manually fiddling around with phpmyadmin (like, as you said, changing parentcatid), if it makes a potential query easier. Thank you for your reply!
I think any query would have to be targeted towards the stories. I don't know your charids, but say you have two instances of "Bob Smith" each in a different category. Instance one is charid 8 and instance two is charid 25.
Maybe the easiest would be to create all new sitewide characters, and then move the others into there, just to avoid confusion. So say the new sitewide "Bob Smith" is charid 107. You'd want find all the instances of charid = 8 and 25 in fanfiction_stories and change them to charid = 107.
A query would be something like: UPDATE fanfiction_stories SET charid = 107 where charid = 8
Note: I haven't run this; it's more of a "theory" type thing.
The other thing that might simplify things a tad (but could be more confusing numbers-wise) is to take the characters from your biggest category and change those to sitewide, and then move everything else into them. Then you wouldn't have to move the characters that were assigned from that category. But, for some reason that strikes me as more confusing. But simpler at the same time? LOL
I did this on my site, and it wasn't fun. You'll have to do it like Lyndsie says.
I wish I'd done it. Our archive has been around since 1.0, and I didn't really think about it and went and deleted all the characters and created new site-wide ones. So there are a bunch of stories that now have no characters. : (
Yeah, I didn't want to lose all the characters for the fics. 🙁 I'll try the query in a few days when I have time to set up a test upgrade; I'll report back then. Thank you again! <3
Finally got around to upgrading and testing the query. Unfortunately, it doesn't seem to work properly. 🙁 If a string starts with a number, it'll change the entire string to just that one number. For example, if charid string is 9,10,11,12, and I try "UPDATE fanfiction_stories SET charid = 1 where charid = 9", it'll change the string to just 1. If a charid string is 13,14,9, then it won't change the string at all.
Do them one at a time or add OR between them.
UPDATE fanfiction_stories SET charid = 1 where charid=9 OR charid=13
(I think I may be misunderstanding you though...if so, ignore this and explain more.)
charIDs:
Character A [site-wide id] = 1
Character A [category1] = 2
Character B [category1] = 3
Character C [category1] = 4
In phpmyadmin, Example Story's charID string reads: "2,3,4". When I run the query "UPDATE fanfiction_stories SET charid = 1 where charid = 2", Example Story's charID line becomes "1". However, 3 and 4 are deleted from the string entirely. Additionally, Example Story #2, which has charID string "4,2" remains completely unchanged - the query won't recognize the string at all since it doesn't start with 2.
Basically, the query seems to change the first charID in the string, but it erases any other remaining charIDs. So, it works for stories that just have one character, but not for stories with multiple characters. (Nearly all the stories in the archive have multiple characters.) And it seems not to work if a string doesn't start with the charID being searched for.
I hope that makes more sense! I am running each query individually, one at a time, changing only one charID at a time.
I see what you mean. I don't remember that problem, so perhaps I changed mine manually (It was a long time ago!)
I'll bump this again in case anyone has any other suggestions 🙂
Is there a way to modify the upgrade script so when it updates the character names, instead of assigning stories charids based on individual catid, it would just assign charids from one catid (without actually changing the stories' catids)? That way all stories, no matter their category, would use the same charids (so, one set of charids only would be used).
Or, is there a way I could modify the old mysql database file (2.x.x) so that when I do upgrade, all the stories would be assigned the same set of charids?
Thanks!
I'm pretty sure that's not going to happen because some people, like people running multi-fandom archives, don't want to use sitewide characters. The problem you are having is a result of a pretty rare case in that 1.1 did not allow for sitewide characters so all characters had to be set according to categories, regardless of if they were repeated or not.
It's a bit of work to change category based characters to sitewide characters, but the only real way to make sure it's done properly is to do it manually. 🙁 Sad but true.
Oh, I didn't mean changing the entire script for everyone just for this situation. I meant if there was a way I could personally modify my own instance of the upgrade script and run it myself - as in, if there were some coding I could do myself in my script. Or there were something I could do to modify my 2.x.x database.
