clean_mob_table

Share files with other Dawn of Light users

Moderator: Support Team

clean_mob_table

Postby dyerseve » Thu Feb 06, 2014 2:28 am

-- Name: clean_mob_table.sql
-- Author: BlindDog
-- Date: 2-5-14
-- Description: Gets rid of duplicate table entries and creates backup table m_mob_deleted
Attachments
clean_mob_table.sql.txt
Gets rid of duplicate table entries and creates backup table m_mob_deleted
(806 Bytes) Downloaded 181 times
dyerseve
DOL Guest
 
Posts: 4
Joined: Fri Jan 24, 2014 4:56 pm
Location: North Carolina

Re: clean_mob_table

Postby Tolakram » Thu Feb 06, 2014 12:53 pm

Be careful as this drops the original mob table and renames the new mob table without the dups. Might be better to rename the original mob table rather than dropping it. Just in case. :)

Thanks for the script.
- Mark
User avatar
Tolakram
Storm / Storm-D2 Admin
 
Posts: 9178
Joined: Tue Jun 13, 2006 1:49 am
Location: Kentucky, USA

Re: clean_mob_table

Postby dyerseve » Thu Feb 06, 2014 1:16 pm

It also creates a table of all the deleted mobs so you won't miss anything.
dyerseve
DOL Guest
 
Posts: 4
Joined: Fri Jan 24, 2014 4:56 pm
Location: North Carolina

Re: clean_mob_table

Postby Leodagan » Thu Feb 06, 2014 4:33 pm

The logic for this script is a bit weird...

It creates a temp table with all "unique" mob record.

Then it inserts ONE of all "duplicate" mob records into the temp table.

After that it lists all mobs in the temp table appended to the current mob table, and the records only appearing "once" will be the "deleted" records, sent to the backup table.


It's way simpler to make a temp table with a UNIQUE constraint (Name, X, Y, Z, Region), then INSERT IGNORE, or REPLACE INTO the whole mob table, finally LEFT JOIN, on mob_id, the mob table to the temp table and select only record where the join result in "NULL" this will give the "deleted" records...
User avatar
Leodagan
Developer
 
Posts: 1227
Joined: Tue May 01, 2012 9:30 am
Location: Lyon

Re: clean_mob_table

Postby Tolakram » Thu Feb 06, 2014 5:17 pm

Is that MySQL specific syntax? I can certainly understand why it wasn't used if so. Nothing like that in TSQL (Microsoft). :)
- Mark
User avatar
Tolakram
Storm / Storm-D2 Admin
 
Posts: 9178
Joined: Tue Jun 13, 2006 1:49 am
Location: Kentucky, USA

Re: clean_mob_table

Postby Leodagan » Thu Feb 06, 2014 8:40 pm

CREATE TABLE ... AS SELECT... is MySQL only I think.

INSERT ... SELECT... should be standard (but maybe above SQL99 standard)

RENAME is not SQL standard.
User avatar
Leodagan
Developer
 
Posts: 1227
Joined: Tue May 01, 2012 9:30 am
Location: Lyon

Re: clean_mob_table

Postby dyerseve » Fri Feb 07, 2014 1:15 am

I'm not a sql person at all. This was my first attempt at doing anything like that. I just knew what I wanted to do and read as much as possible to get something I personally saw would work, tested it and shared it for others. Is there other ways to skin a cat? You bet. However, I didn't see one here.
dyerseve
DOL Guest
 
Posts: 4
Joined: Fri Jan 24, 2014 4:56 pm
Location: North Carolina


Return to User Files

Who is online

Users browsing this forum: No registered users and 1 guest