GunZ Basic Database Management.

[NeoDio]

SQL language is like talking to a dabatase, this will be the basic for GunZ database editing, I will start this how I learned it, I was given some examples and I learned through them adapting those queries and using them with other database tables.

You should have account registration scripts, search for them in this website, otherwise if you need to manually create accounts, let's go with the first example.

To create account manually you will have two insert values into two tables, which are ACCOUNT and LOGIN.

ACCOUNT has your account information, the id number (AID), the name, the ingame status (255 admin 254 gamemaster 253 banned 252 hiddenpowers 104 muted), date of registration, email, etc..

LOGIN contains the name, the AID, password, last connection date and the last ip used.

So to create an account you will need two queries:

INSERT INTO ACCOUNT (UserID, Ugradeid, PgradeID, RegDate, name, email) VALUES ('shadowking', '0', '1' ,'2007-07-30', 'shadowking', 'shadowking@shadowking.com')

This account will generate an AID, which you will need for the login table query, in this case the AID will be 1337.

Then with the AID 1337 in hand let's go for the second query:
INSERT INTO LOGIN (USerID, AID, password) values ('shadowking', '1337', 'shadowking123')

You just created account number 1337, called shadowking, with shadowking123 as password and [Only registered and activated users can see links. ] as registered email.

Sequentially, registered emails are used for password recovery, but if you don't have an account recovery system and you need to change a password then you will write the following query:

UPDATE LOGIN SET PASSWORD = 'shadowking456' WHERE AID = '1337'

And the password on the account 1337 is now shadowking456.

Now let's take advantadge of this query to go directly into character editing, you can create characters manually, but it's far easier to create them ingame, but first you need to search the character name with the search query:

SELECT * FROM CHARACTER WHERE NAME = 'shadowking'

And it will give you a CID, a character identification number, which you will
use to change the sex like this:

UPDATE CHARACTER SET SEX = '1' WHERE CID = '69'

On the GunZ database, male sex = 0 and female sex = 1, so you just changed the character named shadowking whose CID is 69 to female.

Note that you can use just the NAME to change any character table value, I used the CID so I could explain already the SELECT - search query, which brings us to another query, giving items, and for that query you definetely need the CID.

So let's say someone won an event and you want to give that person an item.

First you need to find the CID of that person, using the query above again:

SELECT * FROM CHARACTER WHERE NAME = 'shadowking'

That will give us CID number 69, that we will now use like this:

INSERT INTO CHARACTERITEM (CID, ITEMID) VALUES ('69', '8044')

And this way, item 8044 on the zitem was given to the character number 69.

Now let's go to the delete query using the same example, but be carefull with the delete query thought, always use with a "where Table = "/destination, unless you want to wipe an entire table.

For example, if you want to delete the whole inventory table, just write the query with no "where"/destination, like this:

DELETE from Characteritem
And all inventories are wiped.

If you want to delete only one character's inventory you add its information as a destination:

DELETE from Characteritem where CID = '69'
Only Character 69's inventory is deleted.

If you want to delete a specific item from a specific character then you add not only the character information but also the item id number both as destination of the query:

DELETE from Characteritem where CID = '69' and itemid = '8044'
Only item 8044 is deleted from character 69.

Now I will try to explain the meaning and usage of these basic GunZ SQL database commands, I will use 3 queries and explain them.

1 - Search query -> SELECT * FROM CHARACTER WHERE NAME = 'shadowking'

SELECT - means search, what will be searchED
* - means all the values from the table
FROM - where the info comes from
CHARACTER - it's the table being searched, you can write here any other table name, like account, login, clan, etc..
WHERE - gives the query a destination
NAME - the column being searched, you can write here other column names like sex, level, CID, AID, etc..
= - equals
'shadowking' - the value being searched on the character table.

2 - Update query -> UPDATE CHARACTER SET NAME = 'NEO' WHERE NAME
= 'shadowking'

UPDATE - means update or change
CHARACTER - it's the table on which you will make changes, you can write here any other table name, like account, login, clan, etc..
SET - tells the database to set a new value
NAME - the column being changed, you can write here other column names like sex, level, CID, AID, etc..
= - equals
'NEO' - the new name/value
WHERE - gives the query a destination
NAME '- the column used to find the original name/value
= - equals
'shadowking' - the old name

3 - Insert query -> INSERT INTO CHARACTERITEM (CID, ITEMID) VALUES ('69', '8044')

INSERT - insert or give
INTO - to what table it goes
CHARACTERITEM - it's the table where the values are being added, you can write any other table name, like account, login, clan, etc..
Between the ( ) and separated by commas , should be the columns to where you're going to add any values
VALUES - needs to be between the specified columns and the values being entered into them.
Between the ( ) separated by commas , and inside '' are the values that are going to be entered into the database table.

4 - Delete Query - DELETE from Characteritem where CID = '69' and itemid = '8044'

DELETE - The delete command
from - from what table
Characteritem - table being deleted
where - first destination for deletion
CID - character id
= - equals
'69' - location of first destination
and - and
itemid - second destination for deletion
= equals
'8044' - location of the second destination

Note: Always keep the values between ' ' in all queries.

Also, important, you can always edit the tables by double clicking on the table and choosing "edit", but using queries is far more faster and efficient. To open an whole table for editing go to Menu - View - Object Explorer - open it and connect it, then open the GunZ database - right click on the table you want to edit.

This is not much and I'm no SQL expert, what I've learned was as I stated above was through some examples and I know the minimum needed to fully administrate a GunZ database, I hope this can help someone at least to get started with your own server.

Remember, this is the mininal knowledge you need to run a GunZ database, it's not a full SQL tutorial, I made this in little time and just to try to explain the basics of GunZ SQL, so save the flaming and spamming about it please.

Thanks, sticky perhaps?

[ronnockoch]

Me likes <3

[Sulfin]

lol neodio since when are you on gzp

[angelhunter]

Yeah try a online free database also as this makes life easy [Only registered and activated users can see links. ] As less things stored on the PC also you could make your launcher on line and add music like I did [Only registered and activated users can see links. ] Not saying pefect as only a ftp server but i can help anyone should they wish.

Just a note use above server you need Microsoft SQL Server 2008 R2 cause other one is like very old now.

[Only registered and activated users can see links. ]

[NeoDio]

Me likes <3

Thanks :)

lol neodio since when are you on gzp

Well, I should have a Shadowking account somewhere that should be older then this one...lol.

Yeah try a online free database also as this makes life easy [Only registered and activated users can see links. ] As less things stored on the PC also you could make your launcher on line and add music like I did [Only registered and activated users can see links. ] Not saying pefect as only a ftp server but i can help anyone should they wish.

Just a note use above server you need Microsoft SQL Server 2008 R2 cause other one is like very old now.

[Only registered and activated users can see links. ]

We have our gunz server stored at a dedicated server, we're using MSSQL 2008 with a Windows Server 2008 Enterprise R2 operative system. We also most database tables customized, mostly the donation ones though.

[angelhunter]

Actually if anyone wants make simple one all they need is
WampDeveloper-4.0.2.7
Microsoft SQL Server 2008 R2
Hamachi cause why pay loads

[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]

One thing i found out was you can run ServerKeeper.exe on Windows SP3 but on other versions run in this order

matchserver.exe
MatchAgent
Locator.exe (All was run this Admin if using Vista or Win 7

Any problems I am sure i can fix, also I assume if you have more than one computer you can use same IP and network so you can play with others.


Or Gunz and Gunz1 and so on

[[MOP]Nessy]

Very good!

Rep+

[NeoDio]

Actually if anyone wants make simple one all they need is
WampDeveloper-4.0.2.7
Microsoft SQL Server 2008 R2
Hamachi cause why pay loads

[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]

One thing i found out was you can run ServerKeeper.exe on Windows SP3 but on other versions run in this order

matchserver.exe
MatchAgent
Locator.exe (All was run this Admin if using Vista or Win 7

Any problems I am sure i can fix, also I assume if you have more than one computer you can use same IP and network so you can play with others.


Or Gunz and Gunz1 and so on

That's why it's a Gunz Basic Database Management tutorial, it's only the minimal SQL that you need to know for GunZ.

The other tutorials already exist, about how to make a private server, how to pack and unpack mrs's, etc...this is only SQL for GunZ.

BTW, aren't you angelz121 on GZN? You posted the exact same thing..lol

Very good!

Rep+

Thanks Nessy, that's big coming from someone experienced like you :]

[Cipher]

Actually if anyone wants make simple one all they need is
WampDeveloper-4.0.2.7
Microsoft SQL Server 2008 R2
Hamachi cause why pay loads

[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]

One thing i found out was you can run ServerKeeper.exe on Windows SP3 but on other versions run in this order

matchserver.exe
MatchAgent
Locator.exe (All was run this Admin if using Vista or Win 7

Any problems I am sure i can fix, also I assume if you have more than one computer you can use same IP and network so you can play with others.


Or Gunz and Gunz1 and so on

Seriously, if you had just linked to an actual guide it wouldn't confuse the **** out of people.
This thread is a database maintenance/management thread, not a guide on how to set up a private server.
Also, your whole 'why pay loads' thing is stupid. What's the point having people have to install hamachi when you can just use services such as DynDNS. In some routers(including mine) there is an option to use such services; I added my DynDNS username and password to the router settings, along with the hostname and it will automatically update their DNS records, without the client needing to be ran on one of the computers in my house. And if you're wondering why I use such a service, it's because I have a VNC server running on my computer so that I can access it remotely.
I also want to say that hosting a private server on a residential connection is very bad.
ITT: We don't hijack threads.

Very good!

Rep+

Thanks Nessy, that's big coming from someone experienced like you :]

lol

[NeoDio]

Actually if anyone wants make simple one all they need is
WampDeveloper-4.0.2.7
Microsoft SQL Server 2008 R2
Hamachi cause why pay loads

[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]
[Only registered and activated users can see links. ]

One thing i found out was you can run ServerKeeper.exe on Windows SP3 but on other versions run in this order

matchserver.exe
MatchAgent
Locator.exe (All was run this Admin if using Vista or Win 7

Any problems I am sure i can fix, also I assume if you have more than one computer you can use same IP and network so you can play with others.


Or Gunz and Gunz1 and so on

Seriously, if you had just linked to an actual guide it wouldn't confuse the **** out of people.
This thread is a database maintenance/management thread, not a guide on how to set up a private server.
Also, your whole 'why pay loads' thing is stupid. What's the point having people have to install hamachi when you can just use services such as DynDNS. In some routers(including mine) there is an option to use such services; I added my DynDNS username and password to the router settings, along with the hostname and it will automatically update their DNS records, without the client needing to be ran on one of the computers in my house. And if you're wondering why I use such a service, it's because I have a VNC server running on my computer so that I can access it remotely.
I also want to say that hosting a private server on a residential connection is very bad.
ITT: We don't hijack threads.

Very good!

Rep+

Thanks Nessy, that's big coming from someone experienced like you :]

lol

That's way too many links for too much stuff, you should make a thread :]