 |
| bothkill |
Posted: Tue Jul 14, 2009 7:36 am
Recycling the numbers of deleted rooms |
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
 Posted: Sun Jul 19, 2009 4:25 pm
|
There's something not right about the ExitIdTo field in the ExitTbl. There are too many '-1's in there. In the original file the only time there was a '-1' was when there was a stub exit leading nowhere.
In the processed file there are '-1's that are valid links, it now just lacks the proper number in the ExitIdTo field to link it to is matching entry. Strangely enough, Cmud doesn't seem to care and presents the links correctly in the program, but they aren't linked to their matching pair anymore like all the other links are.
I've been going over the code trying to see where this is happening, but all the code looks necessary and correct. So I'm a little confused.
Here's an example of what I mean:
ExitId ExitIdToFromID ToID
4564 -1 450 1831
4565 -1 1831 450
These two shouldn't have a -1 in the ExitIdTo they should link to each other like this:
ExitId ExitIdToFromID ToID
4564 4565 450 1831
4565 4564 1831 450
I still can't put my finger on the cause, but I think it should be possible to repair these in a subsequent post-processing. |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Sun Jul 19, 2009 4:44 pm
|
| bothkill wrote: |
Nice findings!
I added these two statements to line 139 where database connectivity is set for the first time:
| Code: |
| objConn.Open "Driver={SQLite3 ODBC Driver};Database="& objFSO.FileName &";StepAPI=;Timeout=" |
so that it looks:
| Code: |
| objConn.Open "Driver={SQLite3 ODBC Driver};Database="& objFSO.FileName &";StepAPI=;Timeout=;PRAGMA journal_mode=OFF; PRAGMA synchronous=0" |
but didn't notice any improvement on time consumption . |
Is there a way you can confirm that it was actually applied? I was searching on Google for examples and couldn't find many examples. I did find some other setting that might help as well:
PRAGMA synchronous = OFF
PRAGMA journal_mode = OFF
PRAGMA locking_mode = EXCLUSIVE
PRAGMA temp_store = MEMORY |
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Sun Jul 19, 2009 7:28 pm
|
| charneus wrote: |
| ...I work with Windows 7... |
From what I remember VBScript is not supported anymore from Vista on. I think it was replaced with .NET framework (but I'm not sure about this).
| charneus wrote: |
| PS. By the way, not sure which table it is in the internal database. I just don't like using #NEW MyEQ {Name=blahblahblah|Stats=blahblahblah} and having it be record number 4002421. :P |
I have no idea about that atm.
| ReedN wrote: |
| There's something not right about the ExitIdTo field in the ExitTbl. There are too many '-1's in there. |
Check the pre-recycle version of the table and see the value of ExitIdTo for the corresponding ExitId.
As far as I can say for now, there are three possibilities:
1.either there really was a '-1',
2.either there was a value indicating to an ExitId which was not valid (i.e. the value in the ExitIdTo is not present in the ExitId column),
3.or the script is wrong somewhere and needs fixing.
| ReedN wrote: |
| Is there a way you can confirm that it was actually applied? |
Following your question, I modified some of the PRAGMA code by replacing the key word synchronous with gibberish and the code ran with no problems.
My conclusion: These PRAGMA tags either don't work at all, or I didn't put them in the right place.
Important !!!
I realized I didn't include in the recycling process the Favorites table.
In fact every table that contains the re-numbered fields (ObjID, ExitID, etc.) should be included in the script.
I will do this in the future, but I imagine you are now so familiar with this script that you are able to do this yourself  . |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Sun Jul 19, 2009 9:27 pm
|
I don't have any data under my Favorites table, or I would have definitely mentioned it at the same time I mentioned the NoteTbl.
I'll need to figure out how to get the PRAGMA working, because I can't spend 7 hours doing that again. I'm sure I'll burn out my harddrive if I do that too many times.
I'll keep digging into these things as well. I'll let you know what I find as I do things. |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Sun Jul 19, 2009 10:18 pm
|
Another interesting snippet I found:
| Quote: |
Use transactions when updating tables
Make sure that you wrap up all multiple updates inside a transaction, e.g.:
BEGIN TRANSACTION;
UPDATE table1 SET col1='1';
UPDATE table1 SET col1='2';
...
INSERT INTO table1(col1) VALUES ('2');
...
COMMIT TRANSACTION;
(The word "TRANSACTION" is optional.)
Using a transaction is the fastest way to update data in SQLite. Basically, this is how it works: After each transaction the SQLite engine closes and opens the database file. When SQLite opens a database file it populates the SQlite internal structures, which takes time. So if you have 100 updates and don't use a transaction then SQlite will open and close the database 100 times. Using transactions improves speed. Use them.
|
|
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Mon Jul 20, 2009 4:37 am
|
Bringing the topic back to the issue of the extra '-1's I think the issue is this code:
| Code: |
' If exit number_to_recycle is found in ExitIdTo column then it becomes -1
objConn.Execute ("UPDATE ExitTbl SET ExitIdTo=-1 WHERE ExitIdTo="& number_to_recycle &"")
|
All the ExitTbl IDs come in pairs like the example below:
ExitId ExitIdTo FromID ToID
4 3 4 5
3 4 5 4
There shouldn't be a case when the ExitIdTo field refers to a number you are recycling. For any number in use (besides -1), there it will exist in both the ExitId field and the ExitIdTo field. It shouldn't exist anywhere else. So every time your code found a case where it changed it, that was where the extra -1s were coming from.
You had mentioned about deleted rooms or exits causing this, but I didn't see that in my database. I deleted a few and looked for more than one instance and didn't see anything. If something gets deleted and it is only a stub then it changes it to a '-1' in the ExitIdTo field.
Edit:
In a perfect map db, the above is true. Here's an example from a well used 4+ year old map db:
ExitId ExitIdTo FromID ToID
7117 7124 2636 2635
7114 7118 2635 2636
You can clearly see that the ExitIdTo is totally bogus. But like I said before, Cmud seems to ignore the field anyway, so I could either correct it or let it be messed up, I guess. Perhaps I'll just correct the darn things. |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Mon Jul 20, 2009 6:43 am
|
Things are going pretty well in my efforts. I've created a Perl program and it is looking pretty good. On a perfect map db it works flawlessly. Right now I'm working on correcting the ExitTbl inaccuracies before I call it done.
In my program it recycles the zone numbers first, reordering them according to alphabetical order.
Then it reorders and recycles the object numbers. It groups them according to the zone they are in so that all your rooms in a zone will be in the same range.
Then it recycles the exit numbers.
I've been able to get the run time to about 2 minutes. It has a progress indicator and is looking pretty nice. I'll post it here when I polish it a little more. |
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Mon Jul 20, 2009 7:40 am
|
| ReedN wrote: |
| You can clearly see that the ExitIdTo is totally bogus. But like I said before, Cmud seems to ignore the field anyway, so I could either correct it or let it be messed up, I guess. Perhaps I'll just correct the darn things. |
From my experience with the map database structure, I concluded that the value of '-1' in the ExitIdTo field is responsible for the drawing of the exit link little arrow, showing an One-Way exit.
Although this arrow is not what defines a real One-Way exit (you can have an arrow (i.e. ExitIdTo = -1) but can still move between rooms if there are the proper exit links), still the arrow should be used only if there really is a One-Way exit. In other words, if you correct all fields where ExitIdTo = -1, you will lose the arrows of One-Way exits.
What's more, you can have more links from room A to room B and some of them may be One-Way (depending on the special exit commands), so correcting the '-1' thing seems rather complicated to me.
I hope this is of some help to you.
| ReedN wrote: |
Things are going pretty well in my efforts. I've created a Perl program and it is looking pretty good.
...
I've been able to get the run time to about 2 minutes. It has a progress indicator and is looking pretty nice. I'll post it here when I polish it a little more. |
You rock 
It was about time a real programmer to tackle this issue!!!
I can't wait to see your program  |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Mon Jul 20, 2009 7:52 am
|
I was mainly concerned with the ExitIdTo fields that don't have identical FromID and ToID fields (exit stub) and there exists another entry with swapped FromID and ToID fields.
In general if I find an exit link like this:
ExitId ExitIdTo FromID ToID
1 2 1 2
I then search for a room with ToID = 1 and FromID = 2. If I've found such a room it is exactly the return path from this link. Say I find this:
ExitId ExitIdTo FromID ToID
2 1 2 1
The above would be correct. However if I find this:
ExitId ExitIdTo FromID ToID
3 4 2 1
Then I would correct the ExitIdTo field for each of the entries so that they were this:
ExitId ExitIdTo FromID ToID
1 3 1 2
3 1 2 1
If I did find a ExitIdTo field with a -1 value but there existed another entry with swapped To and From fields, then that means I probably incorrectly labeled it a one-way exit, because there definitely exists a return exit coming from the other direction. |
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Mon Jul 20, 2009 8:06 am
|
I see. Your algorithm seems correct to me.
So, first correcting the ExitIdTo and then recycling the ExitId.
Do you intend the program to have a message announcing if ExitIdTo fields needed correction?
Waiting for your program!!!
A little off-topic: don't you need some sleep? Judging from your posts dates, you work on this issue all around the clock |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Mon Jul 20, 2009 2:42 pm
|
I'll work some more on this tonight after work. I've gotten the code working that automatically corrects the ExitIdTo fields, but another problem has cropped up... dublicate links.
The above algorithm has uncovered various links going from the same FromID to the same ToID. Some of them could potentially be valid if you could arrive at the same location by both going say 'north' and 'south' from a certain location. So I could potentially detect these duplicates, but I don't see any way to actually correct them without the user actually doing it. There's no way of knowing which is truly a duplicate and which are both valid.
Are there any other integrity type checks I should perform?
Dublicate Rooms: Check
Invalid ExitIdTo fields: Check
Perhaps:
Validation that all objects are assigned to a valid zone
Validation that all NoteTbl items have a valid object |
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Mon Jul 20, 2009 3:24 pm
|
There are maze rooms where you can go from A to B using both 'north' and 'south'. Maybe that's why you encountered duplicate links.
Are you sure there are duplicate links which are not valid????? If this is the case then the Mapper is buggy and have to be reported to Zugg.
I can't think of any other checks to perform. |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Tue Jul 21, 2009 6:59 am
|
I finished my script.
Here's an example run I did on my own map:
| Code: |
C:\Documents and Settings\Reed\Desktop\temp\db>Cmud_map_doctor.exe Achaea_2009_07_17.db3
Summary of: Achaea_2009_07_17.db3
==================================================
Zone: Quantity = 203, Largest Id = 218, Delta = 15
Object: Quantity = 23136, Largest Id = 29076, Delta = 5940
Exit: Quantity = 88301, Largest Id = 120051, Delta = 31750
Checking Exit Stub Integrity: 88301 / 88301 completed ( 108 corrected)
Checking for Exit Duplicates: 88301 / 88301 completed ( 12 corrected)
Checking Exit Return Integrity: 88289 / 88289 completed ( 151 corrected)
Checking Exit Object Integrity: 88289 / 88289 completed ( 4 corrected)
Checking ObjectTbl Orphans: 23136 / 23136 completed ( 12 corrected)
Checking Object Zone Integrity: 23136 / 23136 completed ( 1 corrected)
Checking Zone Parent Integrity: 203 / 203 completed ( 0 corrected)
Zone Renumber: 406 / 406
Object Renumber: 46270 / 46270
Exit Renumber: 176572 / 176572
Summary of: Achaea_2009_07_17_new.db3
==================================================
Zone: Quantity = 203, Largest Id = 203, Delta = 0
Object: Quantity = 23135, Largest Id = 23135, Delta = 0
Exit: Quantity = 88286, Largest Id = 88286, Delta = 0
|
Total run time is about 100 seconds.
What it does:
- Tries to fix 7 different types of map corruption.
= Exit Stub Doesn't have -1 as the ExitToId field
= Duplicate Exits doing the same thing
= ExitToId not referring to the correct return link
= ExitTbl contains FromId and ToId fields that reference an object that isn't there
= ObjectTbl contains orphans that aren't anywhere on your map
= ObjectTbl contains items which are marked as belonging to a zone that doesn't exist
= Zone parents that don't exist (this is the only one I didn't suffer from)
- Sorts and renumbers the Zones so that when you pull up the list of zones it is sorted by default
- Groups and renumbers the Rooms so that numbers for zones are all in the same range. The renumbering recycles deleted numbers so your room numbers aren't crazy big.
- Renumbers Exit Links recycling deleted numbers
- Creates a copy of your map db and operates on the copy
- Creates a log file you can use to examine what changes were made
The exit links were by far the biggest mess and consequently the biggest challenge. The program is written in perl and I've run the PAR-packer on it to convert it to an EXE file which you can download here:
http://www.mediafire.com/?sharekey=981bdd40e22303cac79b87b207592a1ce04e75f6e8ebb871
If you can, examine the output carefully to help me make sure I didn't miss anything. I tried to be as thorough as possible, but this was a bit complex. |
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Tue Jul 21, 2009 8:10 am
|
Ok, it works.
I'll do the checkings. |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Wed Jul 22, 2009 3:13 am
|
I've updated the file slightly, it was about midnight last night when I finally finished it so it was a little rough around the edges. I've changed the following:
- Fixed that it would detect orphaned rooms, but it wasn't deleting them
- Fixed the ordering of the checks so that multiple runs are not required to fix all items. In the case of room orphans then needing their exits deleted.
- Added a 'vacuum' at the end to compact the file.
- Slight speed improvements
http://www.mediafire.com/?sharekey=981bdd40e22303cac79b87b207592a1ce04e75f6e8ebb871
Edit: If you're good with perl you can actually unzip the exe (it's actually an archive) to look at the code. Although the log it produces should provide plenty of information on why it changed what it did. |
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Wed Jul 22, 2009 7:59 am
|
I'm not good with any programing language, but I'll look into the code since I'm extremely curious how you managed to get this incredible speed !!!
Congrats on this, your work is awesome
I'll do the checkings these days when I'll go in vacation and have more time. |
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Wed Jul 22, 2009 2:01 pm
|
Thanks, I'm glad you like it.
The speed is simple. Don't commit any transaction until the end, and use all the speed enhancement settings I mentioned before.
Your script was committing every single transaction immediately which is why it made my disk go crazy for 6 hours. Each and every change had to rebuild the db and then commit it in an ultra safe way.
Of more interest than the speed should be how I went about correcting errors and inconsistencies in the data. There were a lot of inconsistencies in my db, and I'm curious what yours looks like and whether you have as many. After making the corrections, renumbering them was a relatively easy exercise. |
|
|
|
|
Anaristos
Sorcerer
Joined: 17 Jul 2007
Posts: 821
Location: California
|
Posted: Wed Jul 22, 2009 10:26 pm
|
On the subject of the -1. This flag is used by the mapper to determine if the link has been "tested". If this field is -1, and you have the "grey link" flag in the mapper properties, the link will be displayed in grey. To fix this, just use #MOV command to move into the room. Alternatively, you can move in and out of the room until the proper testing direction is found. If you don't have the "Show untested links as gray" box checked then the field has no use (though your generated speedwalks from %walk or %pathfrom may not use the link).
|
|
_________________
Sic itur ad astra. |
 |
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Wed Jul 22, 2009 10:56 pm
|
Anaristos: Which -1 are you taking about? Which table and which field? There is a tested field in the ExitTbl, but I didn't so anything to change it and I never used it for anything either. So I'm not sure what your post is addressing.
|
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
|
|
|
|
wrym
Magician
Joined: 06 Jul 2007
Posts: 349
Location: The big palace, My own lil world
|
Posted: Wed Oct 14, 2009 9:52 pm
|
ReedN, your links no longer seem to work, could you repost? or maybe Zugg would host it similarly to the zmapper zone copy program?
|
|
_________________
"To the engineer, all matter in the universe can be placed into one of two categories: (1) things that need to be fixed, and (2) things that will need to be fixed after you've had a few minutes to play with them" - Scott Adams, The Dilbert Principle |
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
|
|
|
|
bothkill
Apprentice
Joined: 13 Mar 2005
Posts: 125
Location: Bucharest
|
Posted: Thu Mar 04, 2010 1:17 pm
|
I've identified an issue when running your script.
The problem seems to be the fixing of duplicate links.
It goes like this.
There are two rooms: Room A (ID=2261) and Room B (ID=2260)
There are two exits from A to B and only one exit from B back to A.
Before recycling, the picture looks like this:
| Code: |
ExitId ExitIdTo FromID ToID Name
-------------------------------------------------
5870 -1 2261 2260 "enter house"
5873 5871 2261 2260
5871 5873 2260 2261 "out"
|
After running your script, the duplicate links from Room A to Room B is addressed by deleting the first link, that which has ExitIdTo=-1 and so the most important info regarding the connection from A to B is lost.
In fact, the first link is functional while the second is not.
Although it would make perfect sense to delete the first link from a scripting perspective, the problem is that the first link, which is deleted, is the one which contains the door name ('enter house').
Why these two exits are like this, you can blame the man behind the mapping process (me, in this case ). At first I didn't know to make proper 'other exits' links and that's why I ended up having these weird duplicate links.
Nevertheless, the point I want to make is that, in case of duplicate links, no script can decide which of the links is the correct one.
I think the script should, at most, make a warning about this (in the log file) and let the user decide which link to delete and which to keep.
I'd like to make the decision within CMUD mapper (and not within the script) to make sure my actions keep the links functional.
I have another two recommendations:
1. Orphans should not be deleted. Maybe I want to map a new area, I create the first room there with no links yet and intend to continue later on. Again I'd like to be warned about orphans (their ID numbers) and decide for myself if to delete them or not. (P.S. After writing this I understood your point. Rooms whose zones do not exist got to be deleted. But I experienced a case when an isolated room, that I could see on the map, got deleted. That's what I understood initially by an orphan room - a room not connected to anything else)
2. Zone numbers should be recycled in the same way as rooms' and not in alphabetical order. This has also the effect of renumbering rooms according to zone numbers so that my first central mud room (which at creation had no.1) after recycling has no.4000 or somth.
I hope it wouldn't be difficult for you to go back to this script (it always gives me headaches when trying to understand a previous algorithm of mine).
Remember Zugg needs you, since he intends to incorporate this script in the future release of the CMapper
P.S.
Ofc I could comment the lines in your Perl script to invalidate those sections that deal with orphans, links deletion and zones renumbering, but I thought my recommendations would help other users of your script if you would find them useful and update your script accordingly. |
|
|
|
|
kittyfish
Novice
Joined: 20 Sep 2011
Posts: 37
|
|
|
|
|
ReedN
Wizard
Joined: 04 Jan 2006
Posts: 1279
Location: Portland, Oregon
|
Posted: Mon Nov 06, 2017 10:17 am
|
I looked for it, but apparently I've lost the code over the years. Sorry.
|
|
|
|
|
|
|
|
