[Tutorial] GunZ SQL basics
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 shadowking@shadowking.com 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.
Hope someone finds it usefull, it's not much, it's just some basics for people who don't want to fully learn SQL when they only need it for GunZ.
Re: [Tutorial] GunZ SQL basics
Not a bad tutorial at all.
You should use headers tho.
Re: [Tutorial] GunZ SQL basics
I may expand this to include how to edit stored procedures using ifs and cases and other tweaks to do some nice things that honestly, I've seen no one but myself do. I'm just fairly busy so I won't make any promises, NeoDeo feel free to do this yourself if you know how.
(Sex change in shop with bounty, Buy whole sets at once, Buy colour name with bounty, etc etc)
Simple things that are easily obtained by just editing the core of the database.
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
DawsonByrd
I may expand this to include how to edit stored procedures using ifs and cases and other tweaks to do some nice things that honestly, I've seen no one but myself do. I'm just fairly busy so I won't make any promises, NeoDeo feel free to do this yourself if you know how.
(Sex change in shop with bounty, Buy whole sets at once, Buy colour name with bounty, etc etc)
Simple things that are easily obtained by just editing the core of the database.
Do these changes take effect immediately, or will the user need to log out, and log back in again? I'm guessing the latter :P
Re: [Tutorial] GunZ SQL basics
Very nice tutorial of the basics, good sir.
This will help the others (:
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
Aristrum
Do these changes take effect immediately, or will the user need to log out, and log back in again? I'm guessing the latter :P
Unfortunately they require the user to log in and out, I'm sure some some other stored proc's I could make this not a requirement but I havn't looked into it much since I first did it, not too into gunz anymore.
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
DawsonByrd
I may expand this to include how to edit stored procedures using ifs and cases and other tweaks to do some nice things that honestly, I've seen no one but myself do. I'm just fairly busy so I won't make any promises, NeoDeo feel free to do this yourself if you know how.
(Sex change in shop with bounty, Buy whole sets at once, Buy colour name with bounty, etc etc)
Simple things that are easily obtained by just editing the core of the database.
As for buying whole sets, on our server, I just entered the 5 zitem ids of the respective set on the donation_items and 'grouped' them under 1 donation_shop entry, the user then aquires 1 full set through the donation_shop entry that reads the donation_items's 5 zitem id's
So we have 5 entries for one set on the Donation_items table:
Code:
id setid zitemid itemname type
48 48 9283 VulcanHat 2
49 48 9282 VulcanPants 2
50 48 9281 VulcanGloves 2
51 48 9280 VulcanChest 2
52 48 9279 VulcanShoes 2
Legend:
ID, the donation_item id
SETID, the donation_shop id
ZITEMID, respective zitem id
ITEMNAME...
TYPE 2 (being 2 a set, and 1 just one item)
Which leads to one entry on the Donation_shop table:
Code:
id itemid itemname type price
47 48 VulcanSet 2 20
ID - The item number on donation_shop
ITEMID - The item number on donation_item
ITEMNAME...
TYPE 2 (being 2 a set, and 1 just one item)
PRICE whatever price you want.
Quote:
Originally Posted by
Aristrum
Do these changes take effect immediately, or will the user need to log out, and log back in again? I'm guessing the latter :P
If you give an item, normally you only need to go to the character selection screen, but if you change the status of an account then the user needs to re-log for the changes to make effect.
Quote:
Originally Posted by
DawsonByrd
Unfortunately they require the user to log in and out, I'm sure some some other stored proc's I could make this not a requirement but I havn't looked into it much since I first did it, not too into gunz anymore.
And, also unfortunately, my SQL knowledge doesn't go that far :\
Quote:
Originally Posted by
wesman2232
Very nice tutorial of the basics, good sir.
This will help the others (:
Thanks :]
Re: [Tutorial] GunZ SQL basics
I didn't mean donation shop, with some simple checks in the SPInsertItem you can allow people to purchase whole normal shop sets. Like xian tacticle, or xmas set and what not. Save time, like how GunZ 1.5 has it.
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
DawsonByrd
I didn't mean donation shop, with some simple checks in the SPInsertItem you can allow people to purchase whole normal shop sets. Like xian tacticle, or xmas set and what not. Save time, like how GunZ 1.5 has it.
GunZ 1.5 doesn't have that feature. They just changed the layout of the shop and added images for each item.
GunZ 1.5 has the "avatar" item slot but that's just 1 slot. You can't buy a set or multiple items with 1 purchase.
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
DawsonByrd
I didn't mean donation shop, with some simple checks in the SPInsertItem you can allow people to purchase whole normal shop sets. Like xian tacticle, or xmas set and what not. Save time, like how GunZ 1.5 has it.
I'll have to repeat, unfortunately, my SQL knowledge doesn't go that far :\
Quote:
Originally Posted by
Phoenix
GunZ 1.5 doesn't have that feature. They just changed the layout of the shop and added images for each item.
GunZ 1.5 has the "avatar" item slot but that's just 1 slot. You can't buy a set or multiple items with 1 purchase.
I was expecting more of this 1.5 update, it's just a rearranged interface and new animations, expirable items :\
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
Phoenix
GunZ 1.5 doesn't have that feature. They just changed the layout of the shop and added images for each item.
GunZ 1.5 has the "avatar" item slot but that's just 1 slot. You can't buy a set or multiple items with 1 purchase.
I was told GunZ 1.5 had that feature, thats the main reason I went ahead and finalized my buy whole sets at a time o_o;
damn GunZ 1.5 fails.
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
DawsonByrd
I was told GunZ 1.5 had that feature, thats the main reason I went ahead and finalized my buy whole sets at a time o_o;
damn GunZ 1.5 fails.
Good old MAIET and their marketing schemes, releasing the 'almost 10 year old game' bit by bit since international gunz........
I'm already predicting that GunZ 2 will be a disappointment, the graphics will require probably pixel shader 3.0, which will cut a great share of GunZ 1 players that have FPS problems and play the game with lowest effects, and apart from new generation graphics the only great feature that I've seen are the vehicles, that actually attracted me.
Re: [Tutorial] GunZ SQL basics
Thanks Neo. Great tutorial.
Re: [Tutorial] GunZ SQL basics
To these changes take effect inmediately you need make a stored procedure(some changes.. not all).
Like: spUpdatesGiveItems
So, when you execute the query:
Code:
"insert into accoutitem etc" add exec spUpdatesGiveItems
spUpdatesGiveItems
@AID int,
@ItemID int,
AS
SET NOCOUNT ON
UPDATE AccountItem SET ItemID=@ItemID WHERE AID=@AID AND etc..
Code:
"insert into blabla
exec spUpdate.."
I think so ..
Thanks for the guide.
Re: [Tutorial] GunZ SQL basics
Quote:
Originally Posted by
mutter
To these changes take effect inmediately you need make a stored procedure(some changes.. not all).
Like: spUpdatesGiveItems
So, when you execute the query:
Code:
"insert into accoutitem etc" add exec spUpdatesGiveItems
spUpdatesGiveItems
@AID int,
@ItemID int,
AS
SET NOCOUNT ON
UPDATE AccountItem SET ItemID=@ItemID WHERE AID=@AID AND etc..
Code:
"insert into blabla
exec spUpdate.."
I think so ..
Thanks for the guide.
I'm going to try getting that on a script/panel, it's a good tool for private servers so staff members that don't have database access can give away items from events or competitions.
Thanks a lot for that information :) I already learned a bit more SQL :)
