[Release] [PhoenixDB] SQL Query: Deleting all records relating to non-existant users.

[DarkoroDragon]

Hai Guys! Darkoro from buzz hotel here!

I've delved into a little bit of SQL coding, and have managed to come up with the below codes. Im looking for critique on my coding logic and such, possible improvements that could be made, bugs, etc.

Also, im taking requests on SOME SQL queries. Please keep it to cross-table deletes if possible, but i can always look into other SQL coding things.

Please note!
This SQL code works with PhoenixDB as of version 3.6.4. I cannot guarantee that this will work for previous versions, and it may be incompatible with newer versions that appear.

Be Warned!
The querys have the potential to lag your hotel while they run, depending on the number of records they requires to delete/the size of your db. I advise that you dont run these query during peak times, rather when your user count is at a relatively low level.

Im not looking for credit or anything, i just ask that you dont change the creation details in the code. C+Ping this code then claiming this code as your own creation is just being a credit stealer, and in doing so you deserve to be flamed by everyone :3

Disclaimer:
I take no responsibility for any problems these codes create IF you make any edits to the codes. I made the codes the way they are because they're SUPPOSED to work the way they're set up. If you edit the codes and something goes wrong, its your own fault. Also, you should test these queries on a dummy table, that wont effect your hotel in anyway. This way if any problems occur, whether the sql has been editted or not, your hotel is in no way effected and you can then report back and say the code doesnt work for such and such a reason. You have been warned.

Delete obselete user records
Deletes any records relating to users that have been deleted from your tables for what ever reasons

SQL Query:

/*
Query Creation Info
-------------------
Creator: Darkoro (David)
Created: 05/11/11

WARNING! ONLY EVER RUN THIS QUERY WHEN USER COUNT IS LOW!
THIS QUERY CAN CAUSE MAJOR LAG, DEPENDING ON THE NUMBER OF RECORDS BEING DELETED!

This Query is used to delete all records relating to non-existant users.
Run this query after deleting one or more users from the users table.
*/

#Deletes rooms from non-existant users
DELETE FROM rooms WHERE
(rooms.roomtype = 'private' AND
rooms.`owner` NOT IN
(SELECT username FROM users where users.username = rooms.`owner`));

#Deletes items from non-existant users
DELETE FROM items WHERE
items.user_id NOT IN
(SELECT id FROM users where users.id =items.user_id);

#Deletes items from non-existant rooms
DELETE FROM items WHERE 
(items.room_id > 0 AND
items.room_id NOT IN
(SELECT id FROM rooms where rooms.id = items.room_id));

#Deletes achievements from non-existant users
DELETE FROM user_achievements WHERE
user_achievements.user_id NOT IN
(SELECT id FROM users where users.id =user_achievements.user_id);

#Deletes effects from non-existant users
DELETE FROM user_effects WHERE
user_effects.user_id NOT IN
(SELECT id FROM users where users.id =user_effects.user_id);

#Deletes badges from non-existant users
DELETE FROM user_badges WHERE
user_badges.user_id NOT IN
(SELECT id FROM users where users.id =user_badges.user_id);

#Deletes favorites from non-existant users
DELETE FROM user_favorites WHERE
user_favorites.user_id NOT IN
(SELECT id FROM users where users.id = user_favorites.user_id);

#Deletes favorites from non-existant rooms
DELETE FROM user_favorites WHERE
user_favorites.room_id NOT IN
(SELECT id FROM rooms where rooms.id = user_favorites.room_id);

#Deletes info from non-existant users
DELETE FROM user_info WHERE
user_info.user_id NOT IN
(SELECT id FROM users where users.id = user_info.user_id);

#Deletes pets from non-existant users
DELETE FROM user_pets WHERE
user_pets.user_id NOT IN
(SELECT id FROM users where users.id = user_pets.user_id);

#Deletes pets from non-existant rooms
DELETE FROM user_pets WHERE
user_pets.room_id NOT IN
(SELECT id FROM rooms where rooms.id = user_pets.room_id);

#Deletes non-existant presents
DELETE FROM user_presents WHERE
user_presents.item_id NOT IN
(SELECT id FROM items where items.id = user_presents.item_id);

#Deletes quests from non-existant users
DELETE FROM user_quests WHERE
user_quests.user_id NOT IN
(SELECT id FROM users where users.id = user_quests.user_id);

#Deletes roomvisits from non-existant users
DELETE FROM user_roomvisits WHERE
user_roomvisits.user_id NOT IN
(SELECT id FROM users where users.id = user_roomvisits.user_id);

#Deletes roomvisits from non-existant rooms
DELETE FROM user_roomvisits WHERE
user_roomvisits.room_id NOT IN
(SELECT id FROM rooms where rooms.id = user_roomvisits.room_id);

#Deletes stats from non-existant users
DELETE FROM user_stats WHERE
user_stats.id NOT IN
(SELECT id FROM users where users.id = user_stats.id);

#Deletes subscriptions from non-existant users
DELETE FROM user_subscriptions WHERE
user_subscriptions.user_id NOT IN
(SELECT id FROM users where users.id = user_subscriptions.user_id);

#Deletes tags from non-existant users
DELETE FROM user_tags WHERE
user_tags.user_id NOT IN
(SELECT id FROM users where users.id = user_tags.user_id);

#Deletes wardrobe from non-existant users
DELETE FROM user_wardrobe WHERE
user_wardrobe.user_id NOT IN
(SELECT id FROM users where users.id = user_wardrobe.user_id);

#Deletes non-existant wired_items
DELETE FROM wired_items WHERE
wired_items.item_id NOT IN
(SELECT id FROM items where items.id = wired_items.item_id);

#Deletes non-existant teleports(1)
DELETE FROM tele_links WHERE
tele_links.tele_one_id NOT IN
(SELECT id FROM items where items.id = tele_links.tele_one_id);

#Deletes non-existant teleports(2)
DELETE FROM tele_links WHERE
tele_links.tele_two_id NOT IN
(SELECT id FROM items where items.id = tele_links.tele_two_id);

#Deletes bans from non-existant users
DELETE FROM bans WHERE 
(bans.bantype = 'user' AND
bans.`value` NOT IN
(SELECT username FROM users where users.username = bans.`value`));

#Deletes appeals from non-existant bans
DELETE FROM bans_appeals WHERE
bans_appeals.ban_id NOT IN
(SELECT id FROM bans where bans.id = bans_appeals.ban_id);

#Deletes chatlogs from non-existant users
DELETE FROM chatlogs WHERE
chatlogs.user_id NOT IN
(SELECT id FROM users where users.id = chatlogs.user_id);

#Deletes chatlogs from non-existant rooms
DELETE FROM chatlogs WHERE
chatlogs.room_id NOT IN
(SELECT id FROM rooms where rooms.id = chatlogs.room_id);

#Deletes rights from non-existant users
DELETE FROM room_rights WHERE
room_rights.user_id NOT IN
(SELECT id FROM users where users.id = room_rights.user_id);

#Deletes rights from non-existant rooms
DELETE FROM room_rights WHERE
room_rights.room_id NOT IN
(SELECT id FROM rooms where rooms.id = room_rights.room_id);

#Deletes bots from non-existant rooms
DELETE FROM bots WHERE
bots.room_id NOT IN
(SELECT id FROM rooms where rooms.id = bots.room_id);

#Deletes responses from non-existant bots
DELETE FROM bots_responses WHERE
bots_responses.bot_id NOT IN
(SELECT id FROM bots where bots.id = bots_responses.bot_id);

#Deletes speech from non-existant bots
DELETE FROM bots_speech WHERE
bots_speech.bot_id NOT IN
(SELECT id FROM bots where bots.id = bots_speech.bot_id);

#Deletes non-existant moodlights
DELETE FROM room_items_moodlight WHERE
room_items_moodlight.item_id NOT IN
(SELECT id FROM items where items.id = room_items_moodlight.item_id);

Download Link: http://www.mediafire.com/?uxx7xazi0nt8649

Delete rare users
Deletes users that have only been on the hotel for a few moments. No SQL file provided due to the need to customise the code to your own needs.

SQL Query:

/*
Query Creation Details
----------------------
Creator: Darkoro (David)
Created: 05/08/11

This query deletes all users who havent logged on to the hotel.

HOW TO EDIT:
------------
* users.id <= 60000 *
Change the 60000 to the highest id that you want to delete to.
This prevents you from deleting newly registered users

* user_stats.RoomVisits <= 10 *
Change this to the number of room visits you want users to have a minimum of.

I used 10 from 60000 downwards, because our current id count is in the 70k region
if i was to go to 70000, id change roomvisits to 0-2, depending.
*/

DELETE FROM users WHERE
(users.id <= 60000 AND
users.id IN
(SELECT id FROM user_stats where user_stats.OnlineTime = 0 OR user_stats.RoomVisits <= 10));

DELETE FROM user_stats WHERE
user_stats.id NOT IN
(SELECT id FROM users where users.id = user_stats.id);

Deleting non-existing furni
Delete any records from the items table who's base_item no longer exists in the furni table

SQL Query:

#Deletes non-existing furni items
DELETE FROM items WHERE
items.base_item NOT IN
(SELECT id FROM furniture where furniture.id = items.base_item);

Download link: http://www.mediafire.com/?vgjtijy6pwc7m6e

EDITS
07/11/11
o Fixed the original code for public rooms, and the user_info problem
o Added my "Delete one-time users" sql code
o Added the code i made for leenster, for deleting furni that no longer exists
o Added SQL file download links for those too lazy to copy and paste.
o Changed the info

[JohnHearfield]

Wow, thanks bud.

Also, i need one for 3.6.0 Phoenix

[DarkoroDragon]

Wow, thanks bud.

Also, i need one for 3.6.0 Phoenix

I think the only thing you need to do is remove anything with user_stats in the query for 3.6.0. Dont quote me on that though.

As i said, create a dummy table, remove the user_stats bit in the query, and run it. If it works, then hey presto, you have a working query for 3.6.0 :3

[Jonteh]

Nice, but you could do it with a smaller amount of work in PHP.

Furthermore, why should this be necessary? User accounts shouldn't be deleted, only renamed. It can mess with your database.

[Divide]

Nice, but you could do it with a smaller amount of work in PHP.

Furthermore, why should this be necessary? User accounts shouldn't be deleted, only renamed. It can mess with your database.

This has 0 PHP scripts and its deleting the bits that "mess with your database" :)

[joopie]

LOL @ hejula xd.

But after removing big amount of record you still need to do a optimise proces to clean the blank spaces.

By removing record doesnt mean your database is getting smaller.
Posted via Mobile Device

[Jonteh]

Thought you'd love this... you love resetting your database

Meh. If my shitty technician didn't delete my database because the desktop was messy on the dedi then i'd be fine. Same with my shit co owner refusing to upload the backups. I'd more say forced into resetting.

None-the-less, on-topic:

I read through the queries and I realised that I probably couldn't do a quicker job in PHP, so nice work. Though I still don't understand why it would be needed. User accounts don't get deleted unless you delete them.. which would be a stupid idea.

Jonty

[Jonteh]

No.

It's Jonteh*

-Mithex

I can sign a post in whichever way I want. It so happens my real name is Jonty.

Jonty look, I can do it too!

[joopie]

I can sign a post in whichever way I want. It so happens my real name is Jonty.

Jonty look, I can do it too!

You forgot the - thing before your name :3
Posted via Mobile Device

[Jonteh]

You forgot the - thing before your name :3
Posted via Mobile Device

Do you have an issue with me? I am simply posting on the forum. No wonder this section is dying.

[Livar]

Do you have an issue with me? I am simply posting on the forum. No wonder this section is dying.

Kudos to you, sir.

[DarkoroDragon]

Meh. If my shitty technician didn't delete my database because the desktop was messy on the dedi then i'd be fine. Same with my shit co owner refusing to upload the backups. I'd more say forced into resetting.

None-the-less, on-topic:

I read through the queries and I realised that I probably couldn't do a quicker job in PHP, so nice work. Though I still don't understand why it would be needed. User accounts don't get deleted unless you delete them.. which would be a stupid idea.

Jonty

I have deleted user accounts on a number of occaisions. after 2 months of changing our cma from phoenix to uber, i deleted all accounts thay hadnt logged in since the switch over. also, i delete accounts on a regular basis ehen users have more than the maximum allowed ammount of accounts on their mail/ip address. recently i also created a query that deletes any accounts that have been created that have never logged into the actual client. this way im freeing up unused usernames that other people may take, and im reducing the amount of records our back up sql filea need to run through.

i hope this answers your question as to what it can be used for.

as for you constantly getting flamed, i personally have no problem with you, but understand why other people might. meh. im not gonna stop them, or jpin them

also, i normally sont type like noob, blame my pgone handset. kthnx.

[leenster]

nice set of queries, we have a few rooms that wont load because they contain non existing furniture ....

It would be nice to add a query that would fixes that.

[DarkoroDragon]

nice set of queries, we have a few rooms that wont load because they contain non existing furniture ....

It would be nice to add a query that would fixes that.

check back tomorrow and ill right you up a line of query code for that, leenster. i could do it now but its 1.30am and im on my phone.

i take it you mean furniture that is no longer in your furniture tables, not missing dat from the items table yeah?

[leenster]

check back tomorrow and ill right you up a line of query code for that, leenster. i could do it now but its 1.30am and im on my phone.

i take it you mean furniture that is no longer in your furniture tables, not missing dat from the items table yeah?

that is correct