Nightmare request/suggestion

Discussion on running your own board and editing MajorMUD.
Post Reply
User avatar
BearFather
Posts: 652
Joined: Sun Feb 09, 2014 6:27 pm
Location: Portland, OR
Contact:

Nightmare request/suggestion

Post by BearFather »

Would it be possible to get an export to SQL option for the DB. There is a mud-db.sql file that Crain posted up in FB dev, and would love to use that as a template to some tools that read from SQL.


User avatar
syntax
Site Admin
Posts: 517
Joined: Tue Jun 02, 2009 10:02 am

Re: Nightmare request/suggestion

Post by syntax »

You should be able to go from NMR -> Access DB -> To whatever as there are a million tools out there to manipulate data from an Access DB. Which I'm pretty sure is all the other person did.

Like this maybe: http://www.bullzip.com/products/a2m/info.php

Or: https://support.microsoft.com/en-us/hel ... r-database

Many other options: https://www.google.com/search?num=50&ne ... TZL-5NTpes


User avatar
BearFather
Posts: 652
Joined: Sun Feb 09, 2014 6:27 pm
Location: Portland, OR
Contact:

Re: Nightmare request/suggestion

Post by BearFather »

I haven't played with access in over 10 yrs. I didn't realize it was such a simple task. I will look into this. Thanks.


User avatar
BearFather
Posts: 652
Joined: Sun Feb 09, 2014 6:27 pm
Location: Portland, OR
Contact:

Re: Nightmare request/suggestion

Post by BearFather »

To bring up a dead subject,

So I had did the convert ages ago and imported into an sql DB and played with it a little and it got shelved.

Well I jumped back into this and noticed a ton of info looked like "Estwall Street Bend Of Cliff age " and "Lucky Strike Casino g Area . & Crown St. "

So I dived into the SQL and notice all the rooms but handful had nonsense in it. So I pulled a new access DB from MME, loading it into a convertor and then dumped that into a new SQL DB and looked it and it's full of garbage too.

So has anyone else ever noticed this? or have even tried this?


User avatar
BearFather
Posts: 652
Joined: Sun Feb 09, 2014 6:27 pm
Location: Portland, OR
Contact:

Re: Nightmare request/suggestion

Post by BearFather »

So I did some more work on this,

I went and install access to look at the data in the MDB file. So I look at it in there and it is clean no issues. Then open up mysql workbench, go thru config it with access and do an import and the data is all munched up. So there something happening in the process of going from MDB to SQL. So makes me wonder is there a setting somewhere I didn't pay attenton too?


User avatar
syntax
Site Admin
Posts: 517
Joined: Tue Jun 02, 2009 10:02 am

Re: Nightmare request/suggestion

Post by syntax »

I took a look at this this morning for you. It looks like what is happening is the raw data from the mud files is exported to the access databases. A NULL character usually terminates the end of the actual text field, with any other previous characters still remaining there afterwords (e.g. "I am the room nameNULLleftover portion of previously saved (longer) room name"). NMR and Access both only display up to that null character. However, the entire field is being imported into MySQL and what you're seeing is the previous characters that were in any particular records. I came up with the following command to keep only the text up to the NULL character:

Code: Select all

UPDATE `rooms` SET `Name` = SUBSTRING_INDEX(`Name`, CHAR(0x00 using utf8), 1) WHERE `Name` LIKE CONCAT("%", CHAR(0x00 using utf8), "%");
Few notes as I was testing this.

1) I followed the instructions here to execute a "[ CurrentProject.Connection.Execute "GRANT SELECT ON MSysRelationships TO Admin" ]" in order to give mysql workbench access to the databases's schema before the migration. -- https://dev.mysql.com/doc/workbench/en/ ... ccess.html

2) You may want to set primary keys in Access before the migration as well. You can also do it after though. You would go into each table's properties --> Indexes --> Set the index to "primary" and "unique". The rooms and textblocks would have two fields for this:
access_pk.png
access_pk.png (8.99 KiB) Viewed 6777 times
3) You may want to check other tables and all other string fields for similar issues. If you find enough other fields, we could come up this something like this (with some additional checking) to iterate over an entire table's columns: https://stackoverflow.com/questions/495 ... lumn-names


User avatar
BearFather
Posts: 652
Joined: Sun Feb 09, 2014 6:27 pm
Location: Portland, OR
Contact:

Re: Nightmare request/suggestion

Post by BearFather »

Sweet, I will go threw the tables in the next few days and look into this. Thanks, I'm no SQL pro I can open and read shit from them and even write but anything above that and I'm like duh. Even permissions shit I have to google each time.

This would also explain why data looked good till I exported to SQL. I even tried to pull just the table to a dump file and that dump file was dirty too.


Post Reply