ArcEmu: Database Updater Tool - ArcEmu

Jump to content

Toggle shoutbox Lastest Announcements

dfighter  : (07 December 2014 - 12:06 PM) Arcemu is in hibernation mode, please read http://arcemu.org/fo...showtopic=26903
dfighter  : (01 January 2013 - 05:56 PM) Arcemu wishes you all a happy new year!
Hasbro  : (12 September 2012 - 10:01 AM) Please excuse our outage from the web! Our web host had a major malfunction!
dfighter  : (01 September 2012 - 04:05 PM) Since the spam bots just don't want to stop, I've enabled admin verification when registering.
dfighter  : (23 January 2012 - 09:56 PM) Please note that from now on you will need to confirm your email on the wiki in order to edit it!
Hasbro  : (31 December 2011 - 12:50 PM) Happy New Years all!
Navid  : (26 December 2011 - 04:09 AM) Merry Christmas !!!!!! Happy holidays all :)
WAmadeus  : (24 December 2011 - 03:54 PM) Merry Christmas to all!
dfighter  : (24 December 2011 - 11:05 AM) The Arcemu team wishes y'all a Merry Christmukkah!
Hasbro  : (05 October 2011 - 12:53 PM) Looking for web designers for upcoming web related project. If you're interested in designing user interfaces contact me
dfighter  : (02 September 2011 - 03:47 PM) So who here wants vehicles in Arcemu? :P http://arcemu.org/fo...showtopic=25440
Hasbro  : (14 August 2011 - 03:25 PM) Join us on irc, grab an irc client and connect to irc.freenode.net join channel #arcemu /server irc.freenode.net:6667 /join #arcemu
jackpoz  : (03 August 2011 - 05:33 AM) to all Lua Engine (old one) users: please check http://arcemu.org/fo...showtopic=25274
Hasbro  : (20 May 2011 - 05:27 PM) Looking for people experienced with CMake configuration and setup! Contact me asap
Hasbro  : (15 May 2011 - 05:03 PM) ArcEmu is recruiting C++ programmers, contact Hasbro if interested.
paroxysm  : (03 May 2011 - 06:26 PM) Updated luabridge gossip example to describe the whole gossip creation process rather than just how to create menu. Gossip tutorial
paroxysm  : (23 April 2011 - 11:35 AM) Lua writers can refer to the Luabridge Tutorials section in the Wiki to learn how to write gossip code correctly.
Hasbro  : (20 April 2011 - 05:22 PM) Thank you for your continuous contribution of bug reports, we are working on them.
Hasbro  : (17 April 2011 - 03:20 AM) Please consider donating to support our bills. Donations can be sent using PayPal to donations@arcemu.org - Thank you for your support.
paroxysm  : (10 April 2011 - 12:43 AM) Refer to the Luabridge Tutorials section in the Wiki to learn the new syntax of luabridge.
Resize Shouts Area

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Database Updater Tool

#1 User is offline   RageD 

  • Occasional Poster
  • PipPip
  • Group: Members
  • Posts: 131
  • Joined: 11-June 08
  • Gender:Male
  • Location:Chicago, IL

Posted 30 May 2010 - 01:37 PM

Hey everyone,

It's been a while since I've really been here, so I decided I'd start off again by contributing something light. I don't believe I could find any other tools for this, so I decided to make a quick database updater tool. It basically just makes things easier for users on *nix systems to update their databases provided that they have the .sql files they need. Realistically I created it for myself because I recently had a few database updates I was making and it's really not efficient to always be importing the query files manually. So I wrote a dynamic bash script to do it!

tool.sh
#!/bin/bash
######################################################################
# MySQL DB Updater Tool v1.1 by RageD				     #
#								     #
# Because sys admins are lazy and don't have time to do this         #
# stuff manually! Sooo... I decided to create a script which takes   #
# me more time than if I was to just do it manually XD		     #
#								     #
# ha ENJOY!							     #
#								     #
# This script is provided AS IS and comes with _ABSOLUTELY_NO_	     #
# _WARRANTY_ whatsoever. Please use it at your own risk. I cannot    #
# be responsible for data loss or any other adverse effects which    #
# may be caused by this script or any modified version of it	     #
#								     #
# Please note:							     #
#	This script can be executed from anywhere. Ideally, in the   #
#	same directory as the .sql files (all updates should end     #
#	with .sql). Below is an example of using it in another dir   #
#	using relative paths. It is _ALWAYS_ ok to use static paths  #
#	if your paths will always remain the same. From a	     #
#	development standpoint, however, it's less efficient.	     #
#								     #
#		Example: *.sql files are in raged/db		     #
#		tool.sh should be in directory "raged" and point     #
#		the db_dir variable to "db"			     #
#								     #
# Updated: 2:58:12 PM 5/30/2010					     #
######################################################################

##############
# Edit Vars  #
##############

db_dir=./      # If tool.sh is located in the same directory as your files, use ./ else specify the path
ap_dir=applied # The directory which applied updates should be moved to so we don't duplicate!
trunc=false    # Truncate applied files or leave their data? bool opt = true/false
	       # It is recommended that you leave this false unless the DB updates take up too much
	       # disk space
program=mysql  # _DO_NOT_ touch this unless you know what you are doing
	       # it is intended for sys admins whose MySQL cannot be called through "mysql"
	       # in command prompt
host=localhost
user=mysql_user
pass=mysql_pass
database=database_name
port=3306

##################################
# _DO_NOT_ EDIT BELOW THIS POINT #
##################################

##############
# Change dir #
##############
cd $db_dir

####################
# Make Applied Dir #
####################
echo "Checking if applied directory exists..."
if [ -d $ap_dir ]; then
	echo "Found!"
else
	echo "Directory does not exist..."
	echo "Creating directory $ap_dir..."
	mkdir $ap_dir
	echo "Done!"
fi

####################
# Get counter info #
####################
totalcount=`ls ./*.sql | wc -l`
totalimport=0
count=1

############
# Run Loop #
############
applied=0
echo "Importing..."
for file in `ls *.sql`; do
	for file2 in `ls $ap_dir/*.sql`; do
		if [ $file2 == $ap_dir/$file ]; then
			# Check if it's the same file or just the same name!
			# But only do this if $trunc=false because otherwise we
			# cannot compare
			if [ $trunc != "true" ]; then
				if `diff $file $file2 >/dev/null` ; then
					applied=1 # Same files
				else
					applied=0 # Different files
				fi
			else
				applied=1 # For security we won't ruin the user's DB
					  # because he has trunc set to true
			fi
		fi
	done
	if [ $applied == 0 ]; then
		echo "[$count/$totalcount] Importing $file..."
		$program --user=$user --password=$pass --host=$host --port=$port --database=$database < $file
		if [ $trunc == "true" ]; then
			cat /dev/null > $file
		fi
		mv $file $ap_dir
		totalimport=$(($totalimport+1))
	else
		echo "[$count/$totalcount] Skipping already imported file $file..."
	fi
	count=$(($count+1))
	applied=0 # Set back to default
done
echo "Complete!"
echo ""
echo "A total of $totalimport file(s) were imported!"


As you can see, we loop through all the .sql files and import them from a given directory. Currently it only supports MySQL, but if I get requests for PostgreSQL or whatever else, I'll gladly update the script to be even more dynamic. Likewise for Win32 users. If I have enough requests, I'll write a Win32 version; but as you can see, it's nothing very difficult, just a simple loop :D Figured I'd share it with everyone to hopefully make everyone's lives a wee-bit easier :(
================
= INSTRUCTIONS =
================

1.) Place in appropriate directory (by default the same directory as your sql files)
2.) Appropriately modify script variables
3.) If you have run updates within this folder already, move those files to the specified directory within the script.
4.) [RECOMMENDED] It is highly recommended that you always backup your database before making ANY modifications to it.
5.) Run the script

Things to note:

The script assumes that all the files you will be updating belong to a single database. So if you have your charDB updates and worldDB updates in one directory, but in two different databases, you may have problems importing certain queries.

-RageD
[19:00] <Lavos^> How long would 1.5 million hours last you?
[19:01] <Astaelan> 720 based on 30 days a month
[19:01] <+RageD> Nott32 depends on the month
[19:02] <Lavos^> 2083 months.
[19:02] <Lavos^> Wow.
[19:02] <+RageD> .33 repeating of course
1.5 million divided by 720 = 2 083.33333 (Repeated lolz)
0

#2 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,189
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Server OS:Linux

Posted 30 May 2010 - 01:38 PM

I am sorry but this is a terrible idea. We had something like this on the SVN, but I removed it for a reason.
It causes more problems, since the old updates add and remove columns that they shouldn't!
"The demand for free goods is infinite."
0

#3 User is offline   RageD 

  • Occasional Poster
  • PipPip
  • Group: Members
  • Posts: 131
  • Joined: 11-June 08
  • Gender:Male
  • Location:Chicago, IL

Posted 30 May 2010 - 01:48 PM

I see what you're saying; I generally separate my updates though. If I've already applied an update, I move it from the directory. Which gives me an idea for a fail-safe then; checking the list of files in the "applied" directory against the files in the update directory, if that makes sense? Perhaps still a bad idea but it helps negligence.

-RageD
[19:00] <Lavos^> How long would 1.5 million hours last you?
[19:01] <Astaelan> 720 based on 30 days a month
[19:01] <+RageD> Nott32 depends on the month
[19:02] <Lavos^> 2083 months.
[19:02] <Lavos^> Wow.
[19:02] <+RageD> .33 repeating of course
1.5 million divided by 720 = 2 083.33333 (Repeated lolz)
0

#4 User is offline   RageD 

  • Occasional Poster
  • PipPip
  • Group: Members
  • Posts: 131
  • Joined: 11-June 08
  • Gender:Male
  • Location:Chicago, IL

Posted 30 May 2010 - 03:03 PM

I updated the script to encompass dfighter's concern as I understood it! This new version will move the applied files to another directory. Now, some database files are rather large, and I understand that. However, the script also allows for truncation to NULL if you set that option. However, if this option is set, it can only compare via file name and not content. This means that if you have a file named "hello.sql" and after importing the file you truncate it and move it, any updates to "hello.sql" will not be executed unless you remove the truncated file from you "applied" directory.

-RageD
[19:00] <Lavos^> How long would 1.5 million hours last you?
[19:01] <Astaelan> 720 based on 30 days a month
[19:01] <+RageD> Nott32 depends on the month
[19:02] <Lavos^> 2083 months.
[19:02] <Lavos^> Wow.
[19:02] <+RageD> .33 repeating of course
1.5 million divided by 720 = 2 083.33333 (Repeated lolz)
0

#5 User is offline   dfighter 

  • Titles are overrated
  • PipPipPipPipPipPipPipPipPipPip
  • Group: Administrator
  • Posts: 5,189
  • Joined: 14-June 08
  • IRC:dfighter
  • Gender:Male
  • Server OS:Linux

Posted 30 May 2010 - 04:42 PM

View PostRageD, on 30 May 2010 - 03:03 PM, said:

I updated the script to encompass dfighter's concern as I understood it! This new version will move the applied files to another directory. Now, some database files are rather large, and I understand that. However, the script also allows for truncation to NULL if you set that option. However, if this option is set, it can only compare via file name and not content. This means that if you have a file named "hello.sql" and after importing the file you truncate it and move it, any updates to "hello.sql" will not be executed unless you remove the truncated file from you "applied" directory.

-RageD

I think you misunderstood what I posted.
The point is, that you loop thru all files. Meaning you will apply old updates too, that are no longer needed, and that can mess up the structure.
Like for example you could have the revision 9001 structure already and you apply revision 1337 on it. Now that is not right ^^
"The demand for free goods is infinite."
0

#6 User is offline   RageD 

  • Occasional Poster
  • PipPip
  • Group: Members
  • Posts: 131
  • Joined: 11-June 08
  • Gender:Male
  • Location:Chicago, IL

Posted 30 May 2010 - 05:27 PM

View Postdfighter, on 30 May 2010 - 04:42 PM, said:

I think you misunderstood what I posted.
The point is, that you loop thru all files. Meaning you will apply old updates too, that are no longer needed, and that can mess up the structure.
Like for example you could have the revision 9001 structure already and you apply revision 1337 on it. Now that is not right ^^



That is exactly the point to the "applied" directory. Moving just the shells (if truncated) of the files to this directory will stop that. Seeing as SVN will "Restore" missing components, if 1337 is an old patch found in the (user-set) "applied" directory, then it will skip that file entirely. So for someone using the fresh db structure, they would cp or mv all the "update" files into their specified applied directory (again, a truncation feature is present if disk space needs to be saved). After it is done the first time by the user, the script will do it itself from there on out and should be able to avoid duplication. For arc updates, I would recommend truncation because it seems all updates are named differently, however, this script works for other db's as well (I used it for my IFDB) which I would not recommend truncation. For these projects, they keep the same file name but change the content which the script also will compare if trunc=false.

BTW lol I do not mean to seem like I am arguing, but I rather enjoy the discussion. Discussion like this helps close the holes that we're discussing. I really do appreciate the feedback :)

-RageD
[19:00] <Lavos^> How long would 1.5 million hours last you?
[19:01] <Astaelan> 720 based on 30 days a month
[19:01] <+RageD> Nott32 depends on the month
[19:02] <Lavos^> 2083 months.
[19:02] <Lavos^> Wow.
[19:02] <+RageD> .33 repeating of course
1.5 million divided by 720 = 2 083.33333 (Repeated lolz)
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users