· 10 years ago · Apr 10, 2016, 09:42 PM
1Tytuł projektu: Gra MMORPG
2Autor: Mateusz Oracz
3Indeks: 412150
4-------------------------------------------------------------------
5-- DROP TABLES --
6-------------------------------------------------------------------
7IF OBJECT_ID('Player_Items', 'U') IS NOT NULL
8DROP TABLE Player_Items;
9
10IF OBJECT_ID('Item_List', 'U') IS NOT NULL
11 DROP TABLE Item_List;
12
13IF OBJECT_ID('Player_Fight', 'U') IS NOT NULL
14DROP TABLE Player_Fight;
15
16IF OBJECT_ID('Account_Ban', 'U') IS NOT NULL
17DROP TABLE Account_Ban;
18
19IF OBJECT_ID('Player', 'U') IS NOT NULL
20 BEGIN
21 ALTER TABLE Player
22 DROP CONSTRAINT add_fk_guild_id
23 END
24
25IF OBJECT_ID('Guild', 'U') IS NOT NULL
26 DROP TABLE Guild;
27
28IF OBJECT_ID('Player', 'U') IS NOT NULL
29 BEGIN
30 ALTER TABLE Player
31 DROP CONSTRAINT add_fk_account_id
32 DROP TABLE Player;
33 END
34
35IF OBJECT_ID('Account', 'U') IS NOT NULL
36DROP TABLE Account;
37
38-------------------------------------------------------------------
39-- CREATE TABLES --
40-------------------------------------------------------------------
41------ Account
42CREATE TABLE Account(
43 idAccount int identity(1,1) primary key,
44 username varchar(45) NOT NULL check (username NOT LIKE '%[^A-Z]%'),
45 password varchar(45) NOT NULL check (DATALENGTH(password) >= 4),
46 group_id varchar(10) check (group_id in ('admin', 'user')) default 'user'
47)
48-------------------------------------------------------------------
49-- Account_Ban
50CREATE TABLE Account_Ban(
51 idAccount_Ban int identity(1,1) primary key,
52 account_ban_id int,
53 reason varchar(255) NOT NULL,
54 ban_day DATE NOT NULL,
55 expire_day DATE NOT NULL
56)
57-------------------------------------------------------------------
58-- Player
59CREATE TABLE Player(
60 idPlayer int identity(1,1) primary key,
61 nickname varchar(45) NOT NULL check (nickname NOT LIKE '%[^A-Z]%'),
62 account_id int,
63 level int NOT NULL default 1,
64 vocation varchar(45) NOT NULL check (vocation in ('warrior', 'mage', 'ranger')),
65 deaths int default null,
66 kills int default null,
67 balance int default 30000,
68 guild_id int
69)
70-------------------------------------------------------------------
71-- Guild
72CREATE TABLE Guild(
73 idGuild int identity(1,1) primary key,
74 name varchar(45) NOT NULL check (name NOT LIKE '%[^A-Z]%'),
75 points int default null,
76 owner_id int
77)
78-------------------------------------------------------------------
79-- Player_Fight
80CREATE TABLE Player_Fight(
81 idPlayer_Fight int identity(1,1) primary key,
82 first_id int,
83 second_id int,
84 result int NOT NULL,
85 date DATE NOT NULL default getdate()
86)
87-------------------------------------------------------------------
88-- Player_Items
89CREATE TABLE Player_Items(
90 idPlayer_Items int identity(1,1) primary key,
91 player_id int,
92 item_id int
93)
94-------------------------------------------------------------------
95-- Item_List
96CREATE TABLE Item_List(
97 idItem_List int identity(1,1) primary key,
98 item_name varchar(45) NOT NULL check (item_name NOT LIKE '%[^A-Z]%'),
99 price int NOT NULL,
100 bonus_level int NOT NULL
101)
102-------------------------------------------------------------------
103-- ALTER TABLES --
104-------------------------------------------------------------------
105ALTER TABLE Account_Ban
106ADD CONSTRAINT add_fk_account_ban_id foreign key (account_ban_id ) references Account(idAccount)
107
108ALTER TABLE Player
109ADD CONSTRAINT add_fk_account_id foreign key (account_id) references Account(idAccount)
110
111ALTER TABLE Player
112ADD CONSTRAINT add_fk_guild_id foreign key (guild_id) references Guild (idGuild)
113
114ALTER TABLE Guild
115ADD CONSTRAINT add_fk_owner_id foreign key (owner_id) references Player(idPlayer)
116
117ALTER TABLE Player_Fight
118ADD CONSTRAINT add_fk_first_id foreign key (first_id) references Player(idPlayer)
119
120ALTER TABLE Player_Fight
121ADD CONSTRAINT add_fk_second_id foreign key (second_id) references Player(idPlayer)
122
123ALTER TABLE Player_Items
124ADD CONSTRAINT add_fk_player_id foreign key (player_id) references Player(idPlayer)
125
126ALTER TABLE Player_Items
127ADD CONSTRAINT add_fk_item_id foreign key (item_id) references Item_List(idItem_List)
128-------------------------------------------------------------------
129-- SHOW DATABASE --
130-------------------------------------------------------------------
131select * from Account
132select * from Account_Ban
133select * from Player
134select * from Guild
135select * from Player_Fight
136select * from Player_Items
137select * from Item_List
138-------------------------------------------------------------------
139-- PROCEDURES --
140-------------------------------------------------------------------
141-- Dodawanie i modyfikacja danych
142-------------------------------------------------------------------
143-- Dodanie konta - Admin
144create procedure addAccountAdmin
145 @username varchar(45),
146 @password varchar(45),
147 @group_id varchar(10)
148 as
149 begin
150 begin try
151 if exists (select username from Account where username=@username)
152 begin
153 raiserror ('Konto o podanej nazwie już istnieje', 11, 1)
154 end
155 else
156 begin
157 insert into Account(username, password, group_id)
158 values(@username, @password, @group_id);
159 end
160 end try
161 begin catch
162 select error_number() as 'number bledu', error_message() as 'komunikat'
163 end catch
164 end
165
166-- Dodanie konta - User
167create procedure addAccountUser
168 @username varchar(45),
169 @password varchar(45)
170 as
171 begin
172 begin try
173 if exists (select username from Account where username=@username)
174 begin
175 raiserror ('Konto o podanej nazwie już istnieje', 11, 1)
176 end
177 else
178 begin
179 insert into Account(username, password)
180 values(@username, @password);
181 end
182 end try
183 begin catch
184 select error_number() as 'number bledu', error_message() as 'komunikat'
185 end catch
186 end
187
188-- Modyfikacja konta - Admin
189create procedure modifyAccountAdmin
190 @idAccount int,
191 @username varchar(45),
192 @password varchar(45),
193 @group_id varchar(45)
194 as
195 begin
196 begin try
197 if not exists (select @idAccount from Account where idAccount=@idAccount)
198 begin
199 raiserror ('Nie istnieje takie konto', 11, 1)
200 end
201 else if exists (select username from Account where username=@username)
202 begin
203 raiserror ('Konto o podanej nazwie już istnieje', 11, 1)
204 end
205 else
206 update Account
207 set
208 username=isnull(@username, username),
209 password=isnull(@password,password),
210 group_id=isnull(@group_id,group_id)
211 where idAccount=@idAccount
212 end
213 try
214 begin catch
215 select error_number() as 'number bledu', error_message() as 'komunikat'
216 end catch
217 end
218
219-- Modyfikacja konta - User
220create procedure modifyAccountUser
221 @username varchar(45),
222 @password varchar(45)
223 as
224 begin
225 begin try
226 if not exists (select @username from Account where username=@username)
227 begin
228 raiserror ('Nie istnieje takie konto', 11, 1)
229 end
230 else
231 update Account
232 set
233 password=isnull(@password,password)
234 where username=@username
235 end
236 try
237 begin catch
238 select error_number() as 'number bledu', error_message() as 'komunikat'
239 end catch
240 end
241
242-- Dodanie postaci - Admin
243create procedure addPlayerAdmin
244 @nickname varchar(45),
245 @account_id int,
246 @level int = 1,
247 @vocation varchar(45),
248 @deaths int = 0,
249 @kills int = 0,
250 @balance int = 30000
251 as
252 begin
253 begin try
254 if exists (select nickname from Player where nickname=@nickname)
255 begin
256 raiserror ('Postac o takim nicku juz istnieje', 11, 1)
257 end
258 else
259 begin
260 insert into Player(nickname, account_id, level, vocation, deaths, kills, balance)
261 values(@nickname, @account_id, @level, @vocation, @deaths, @kills, @balance);
262 end
263 end try
264 begin catch
265 select error_number() as 'number bledu', error_message() as 'komunikat'
266 end catch
267 end
268
269-- Dodanie postaci - User
270create procedure addPlayerUser
271 @nickname varchar(45),
272 @account_id int,
273 @vocation varchar(45)
274 as
275 begin
276 begin try
277 if exists (select nickname from Player where nickname=@nickname)
278 begin
279 raiserror ('Postac o takim nicku juz istnieje', 11, 1)
280 end
281 else
282 begin
283 insert into Player(nickname, account_id, vocation)
284 values(@nickname, @account_id, @vocation);
285 end
286 end try
287 begin catch
288 select error_number() as 'number bledu', error_message() as 'komunikat'
289 end catch
290 end
291
292-- Usuwanie postaci
293create procedure deletePlayer
294 @nickname varchar(45),
295 @account_id int
296 as
297 begin
298 begin try
299 if not exists (select @nickname from Player where nickname=@nickname)
300 begin
301 raiserror ('Nie istnieje taka postać', 11, 1)
302 end
303 else
304 begin
305 declare @acc_id int set @acc_id = (select account_id from Player where nickname=@nickname)
306 if @account_id = @acc_id
307 begin
308 delete from Player
309 where nickname=@nickname
310 end
311 else
312 begin
313 raiserror ('Na tym koncie nie istnieje taka postać', 11, 1)
314 end
315 end
316 end try
317 begin catch
318 select error_number() as 'numer bledu', error_message() as 'komunikat'
319 end catch
320 end
321
322-- Dodawanie śmierci postaci
323create procedure addDeath
324 @idPlayer int
325 as
326 begin
327 begin try
328 if not exists (select @idPlayer from Player where idPlayer=@idPlayer)
329 begin
330 raiserror ('Nie istnieje takie gracz', 11, 1)
331 end
332 else
333 declare @deaths int set @deaths = (select deaths from Player where idPlayer=@idPlayer)
334 if(@deaths IS NULL)
335 begin
336 update Player
337 set
338 deaths = 1
339 where idPlayer=@idPlayer
340 end
341 else
342 begin
343 update Player
344 set
345 deaths=isnull(deaths+1,deaths),
346 level=isnull(level-1,level)
347 where idPlayer=@idPlayer
348 end
349 end
350 try
351 begin catch
352 select error_number() as 'number bledu', error_message() as 'komunikat'
353 end catch
354 end
355
356-- Czasowa blokada konta
357create procedure banAccout
358 @idPlayer int,
359 @reason varchar(255)
360 as
361 begin
362 begin try
363 if exists (select idPlayer from Player where idPlayer=@idPlayer)
364 begin
365 declare @idAccount int set @idAccount = (select account_id from Player where idPlayer = @idPlayer)
366 insert into Account_Ban(account_ban_id, reason, ban_day, expire_day)
367 values(@idAccount, @reason, GETDATE(), GETDATE()+3)
368 end
369 else
370 raiserror ('Nie ma takiej postaci', 11, 1)
371 end try
372 begin catch
373 select error_number() as 'number bledu', error_message() as 'komunikat'
374 end catch
375 end
376
377-- Dodawanie nowych przedmiotów do spisu
378create procedure addItemList
379 @item_name varchar(45),
380 @price int,
381 @bonus_level int
382 as
383 begin
384 begin try
385 if exists (select item_name from Item_List where item_name=@item_name)
386 begin
387 raiserror ('Przedmiot o danej nazwie juz istnieje', 11, 1)
388 end
389 else
390 begin
391 insert into Item_List(item_name, price, bonus_level)
392 values(@item_name, @price, @bonus_level);
393 end
394 end try
395 begin catch
396 select error_number() as 'number bledu', error_message() as 'komunikat'
397 end catch
398 end
399
400-- Kupno przedmiotów
401create procedure addPlayerItems
402 @player_id int,
403 @item_id int
404 as
405 begin
406 begin try
407 declare @itemPrice int set @itemPrice = (select price from Item_List where idItem_List=@item_id)
408 declare @playerBalance int set @playerBalance = (select balance from Player where idPlayer=@player_id)
409 if(@playerBalance<@itemPrice)
410 begin
411 raiserror ('Gracz nie posiada wystarczajacej ilosci zlota', 11, 1)
412 end
413 else
414 begin
415 insert into Player_Items(player_id, item_id)
416 values(@player_id, @item_id);
417 update Player
418 set
419 balance=isnull(balance-@itemPrice, balance)
420 where idPlayer=@player_id
421 end
422 end try
423 begin catch
424 select error_number() as 'number bledu', error_message() as 'komunikat'
425 end catch
426 end
427
428-- Dodawanie gildii
429create procedure addGuild
430 @name varchar(45),
431 @owner_id int
432 as
433 begin
434 begin try
435 if exists (select idPlayer from Player where idPlayer=@owner_id)
436 begin
437 if exists (select name from Guild where name=@name)
438 begin
439 raiserror ('Gildia o podanej nazwie juz istnieje', 11, 1)
440 end
441 else
442 begin
443 insert into Guild(name, owner_id)
444 values(@name, @owner_id);
445 end
446 end
447 else
448 raiserror ('Nie ma takiego gracza', 11, 1)
449 end try
450 begin catch
451 select error_number() as 'number bledu', error_message() as 'komunikat'
452 end catch
453 end
454
455-- Przypisanie gildii do postaci
456create procedure joinGuild
457 @id_player int,
458 @id_guild int
459 as
460 begin
461 begin try
462 if not exists (select idPlayer from Player where idPlayer=@id_player)
463 begin
464 raiserror ('Nie ma takiego gracza', 11, 1)
465 end
466 else if not exists (select idGuild from Guild where idGuild=@id_guild)
467 begin
468 raiserror ('Nie ma takiej gildii', 11, 1)
469 end
470 else
471 begin
472 update Player
473 set
474 guild_id=isnull(@id_guild, guild_id)
475 where idPlayer=@id_player
476 end
477 end try
478 begin catch
479 select error_number() as 'number bledu', error_message() as 'komunikat'
480 end catch
481 end
482
483-- Zwiększenie poziomu
484create procedure levelUp
485 @id_player int
486 as
487 begin
488 begin try
489 if not exists (select idPlayer from Player where idPlayer=@id_player)
490 begin
491 raiserror ('Nie ma takiego gracza', 11, 1)
492 end
493 else
494 begin
495 update Player
496 set
497 level=isnull(level+1,level),
498 balance=isnull(balance+10000,balance)
499 where idPlayer=@id_player
500 end
501 end try
502 begin catch
503 select error_number() as 'number bledu', error_message() as 'komunikat'
504 end catch
505 end
506
507-- Dodanie punkt gildii
508create procedure addGuildPoints
509 @id_guild int
510 as
511 begin
512 begin try
513 if not exists (select idGuild from Guild where idGuild=@id_guild)
514 begin
515 raiserror ('Nie ma takiej gildii', 11, 1)
516 end
517 else
518 begin
519 declare @points int set @points = (select points from Guild where idGuild=@id_guild)
520 if(@points IS NULL)
521 begin
522 update Guild
523 set
524 points = 1
525 where idGuild=@id_guild
526 end
527 else
528 begin
529 update Guild
530 set
531 points =isnull(points+1, points)
532 where idGuild=@id_guild
533 end
534 end
535 end try
536 begin catch
537 select error_number() as 'number bledu', error_message() as 'komunikat'
538 end catch
539 end
540
541-- Dodawanie zabójstwa postaci
542create procedure addKill
543 @idPlayer int
544 as
545 begin
546 begin try
547 if not exists (select @idPlayer from Player where idPlayer=@idPlayer)
548 begin
549 raiserror ('Nie istnieje takie gracz', 11, 1)
550 end
551 else
552 declare @kills int set @kills = (select kills from Player where idPlayer=@idPlayer)
553 if(@kills IS NULL)
554 begin
555 update Player
556 set
557 kills = 1
558 where idPlayer=@idPlayer
559 end
560 else
561 begin
562 update Player
563 set
564 kills=isnull(kills+1,deaths)
565 where idPlayer=@idPlayer
566 end
567 end
568 try
569 begin catch
570 select error_number() as 'number bledu', error_message() as 'komunikat'
571 end catch
572 end
573
574-- Walka postaci
575create procedure playerFight
576 @first_id int,
577 @second_id int
578 as
579 begin
580 begin try
581 declare @first_level int set @first_level = (select level from Player where idPlayer=@first_id)
582 declare @second_level int set @second_level = (select level from Player where idPlayer=@second_id)
583 if not exists (select idPlayer from Player where idPlayer=@first_id)
584 begin
585 raiserror ('Nie ma pierwsego gracza', 11, 1)
586 end
587 else if not exists (select idPlayer from Player where idPlayer=@second_id)
588 begin
589 raiserror ('Nie ma drugiego gracza', 11, 1)
590 end
591 else if (@first_level < 10)
592 begin
593 raiserror ('Za niski poziom pierwszego gracza (min 10)', 11, 1)
594 end
595 else if (@second_level < 10)
596 begin
597 raiserror ('Za niski poziom drugiego gracza (min 10)', 11, 1)
598 end
599 else
600 begin
601 declare @randNumber int set @randNumber = (SELECT FLOOR(RAND()*(10-1)+1))
602 declare @first_guild_id int set @first_guild_id = (select guild_id from Player where idPlayer=@first_id)
603 declare @second_guild_id int set @second_guild_id = (select guild_id from Player where idPlayer=@second_id)
604
605 if (@randNumber <= 5)
606 begin
607 insert into Player_Fight(first_id, second_id, result, date)
608 values (@first_id, @second_id, @first_id, GETDATE())
609 exec addDeath @second_id
610 exec addKill @first_id
611 exec levelUp @first_id
612 if ((select guild_id from Player where idPlayer=@first_id) IS NOT NULL)
613 begin
614 exec addGuildPoints @first_guild_id
615 end
616 end
617 else
618 begin
619 insert into Player_Fight(first_id, second_id, result, date)
620 values (@first_id, @second_id, @second_id, GETDATE())
621 exec addDeath @first_id
622 exec levelUp @second_id
623 exec addKill @second_id
624 if ((select guild_id from Player where idPlayer=@first_id) IS NOT NULL)
625 begin
626 exec addGuildPoints @second_guild_id
627 end
628 end
629 end
630 end try
631 begin catch
632 select error_number() as 'number bledu', error_message() as 'komunikat'
633 end catch
634 end
635
636-------------------------------------------------------------------
637-- FUNKCJE
638-------------------------------------------------------------------
639-- zlicza przedmioty gracza
640create function itemCount
641 (@id_player int)
642 returns table
643as
644 return select * from Player_Items where player_id=@id_player
645
646-- zlicza członków gildii
647create function memberCount
648 (@id_guild int)
649 returns table
650as
651 return select * from Player where guild_id=@id_guild
652
653-- zlicza profesje w gildii
654create function memberVocationCount
655(
656 @id_guild int,
657 @vocation varchar(45)
658)
659 returns table
660as
661 return select * from player where vocation=@vocation and guild_id=@id_guild
662
663-------------------------------------------------------------------
664-- TRIGGERY
665-------------------------------------------------------------------
666-- trigger na limit postaci - user
667create trigger t_addPlayerUser
668on Player
669for insert
670as
671declare @account_id int set @account_id = (select account_id from inserted)
672if ((select count(*) from Player where account_id=@account_id) > 3)
673 begin
674 print 'Osiagnieto limit postaci na konto ( 3 )'
675 rollback
676 end
677
678-- trigger na dodanie śmierci
679create trigger t_addDeath
680on Player
681after update
682as
683if ((select level from inserted) < 1)
684 begin
685 print 'Postac ma minimalny poziom'
686 rollback
687 end
688
689-- trigger na dodanie poziomu i golda (przedmioty)
690create trigger t_addPlayerItems
691on Player_Items
692for insert
693as
694 declare @itemId int set @itemId = (select item_id from inserted)
695 declare @playerId int set @playerId = (select player_id from inserted)
696 declare @bonusLevel int set @bonusLevel = (select bonus_level from Item_List where idItem_List=@itemId)
697 update Player
698 set
699 level=isnull(level+@bonusLevel,level),
700 balance=isnull(balance + (@bonusLevel*10000), balance)
701 where(idPlayer=@playerId)
702
703-- trigger na limit przedmiotów ( max 5 )
704create trigger t_addPlayerItemsLimit
705on Player_Items
706for insert
707as
708 declare @idPlayer int set @idPlayer = (select player_id from inserted)
709 declare @itemCount int set @itemCount = (select COUNT(*) from itemCount(@idPlayer))
710 if(@itemCount>=5)
711 begin
712 print 'Osiagnieto limit przedmiotow na postac ( 5 )'
713 rollback
714 end
715
716-- trigger na limity w gildii
717create trigger t_joinGuild
718on Player
719after update
720as
721 declare @idGuild int set @idGuild = (select guild_id from inserted)
722 declare @memberCount int set @memberCount = (select COUNT(*) from memberCount(@idGuild))
723 declare @warriorCount int set @warriorCount = (select COUNT(*) from memberVocationCount(@idGuild,'warrior'))
724 declare @mageCount int set @mageCount = (select COUNT(*) from memberVocationCount(@idGuild,'mage'))
725 declare @rangerCount int set @rangerCount = (select COUNT(*) from memberVocationCount(@idGuild,'ranger'))
726 if (@memberCount > 9)
727 begin
728 print 'Osiagnieto limit postaci w gildii ( 9 )'
729 rollback
730 end
731 else if (@warriorCount > 3)
732 begin
733 print 'Osiagnieto limit warriorow w gildii ( 3 )'
734 rollback
735 end
736 else if (@mageCount > 3)
737 begin
738 print 'Osiagnieto limit magow w gildii ( 3 )'
739 rollback
740 end
741 else if (@rangerCount > 3)
742 begin
743 print 'Osiagnieto limit rangerow w gildii ( 3 )'
744 rollback
745 end
746
747-- trigger na dolaczenie lidera do gildii
748create trigger t_addGuild
749on Guild
750after insert
751as
752 declare @guild_id int set @guild_id = (select idGuild from inserted)
753 declare @player_id int set @player_id = (select owner_id from inserted)
754 update Player
755 set
756 guild_id=@guild_id
757 where idPlayer=@player_id
758
759-------------------------------------------------------------------
760-- WIDOKI
761-------------------------------------------------------------------
762-- najlepsi gracze wzgledem doswiadczenia
763create view topLevel
764as
765select top 5 nickname, level
766from Player
767order by level desc
768
769-- najlepsi gracze wzgledem majatku
770create view topBalance
771as
772select top 5 nickname, balance
773from Player
774order by balance desc
775
776-- najlepsi gracze wzgledem ilosci smierci
777create view topDeath
778as
779select top 5 nickname, deaths
780from Player
781order by deaths
782
783-- najlepsi gracze wzgledem zabojstw
784create view topKillers
785as
786select top 5 nickname, kills
787from Player
788order by kills desc
789
790-- najlepsze gildie
791create view topGuilds
792as
793select top 5 name, points
794from Guild
795order by points desc
796
797-------------------------------------------------------------------
798-- SCENARIUSZ --
799-------------------------------------------------------------------
800-- Tworzenie kont ( admin, user )
801exec addAccountAdmin 'admin','admin','admin' -- dodanie admin konta
802exec addAccountAdmin 'admin','admin','admin' -- login zajety
803exec addAccountAdmin 'reguser','reguser','moderator' -- group id check
804exec addAccountAdmin 'reguser123','reguser','user' -- login check
805exec addAccountAdmin 'reguser','reg','user' -- password check
806exec addAccountAdmin 'reguser', 'reguser', 'user' -- dodanie usera przez admina
807exec addAccountUser 'mateusz','mateusz'
808exec addAccountUser 'test','test'
809
810select * from Account
811
812-- Modyfkacja kont ( admin, user )
813exec modifyAccountAdmin 900, 'user', 'user', 'user' -- zle id
814exec modifyAccountAdmin 14, 'mateusz', 'mateusz', 'user' -- login zajety
815exec modifyAccountAdmin 14, 'adminuser', 'adminuser', 'admin' -- zmiana konta reguser na admina
816exec modifyAccountUser 'test', 'test123' -- zmiana hasla
817
818select * from Account
819
820-- Dodanie postaci ( Admin, User )
821exec addPlayerAdmin 'Admin', 1, 500, 'warrior', 10, 30, 100000 -- dodanie przez admina
822exec addPlayerUser 'mateusz', 15, 'mage' -- dodanie postaci przez usera
823exec addPlayerUser 'mateusz', 16, 'warrior' -- zajety nick
824exec addPlayerUser 'mateuszDwa', 15, 'mage' -- dodanie postaci przez usera
825exec addPlayerUser 'mateuszTrzy', 15, 'mage' -- dodanie postaci przez usera
826exec addPlayerUser 'mateuszCztery', 15, 'mage' -- dodanie postaci przez usera
827exec addPlayerUser 'Test', 16, 'warrior' -- dodanie postaci przez usera
828exec addPlayerUser 'TestDwa', 16, 'warrior' -- dodanie postaci przez usera
829exec addPlayerUser 'AdminUser', 14, 'warrior' -- dodanie postaci przez usera
830
831select * from Player
832
833-- Usuwanie postaci
834exec deletePlayer 'mateuszOsiem', 15 -- usuniecie postaci ktora nie istnieje
835exec deletePlayer 'mateuszTrzy', 600 -- usuniecie postaci z nie swojego konta
836exec deletePlayer 'mateuszTrzy', 15 -- poprawne usuniecie
837
838select * from Player
839
840-- Blokada Konta
841exec banAccout 500, 'Uzywanie nielegalnego oprogramowania' -- brak takiej postci
842exec banAccout 10, 'Uzywanie nielegalnego oprogramowania' -- poprawny ban
843
844select * from Account_Ban
845
846-- Dodanie przedmiotow do spisu
847exec addItemList 'sword', 50000, 3 -- poprawne dodanie
848exec addItemList 'sword', 70000, 5 -- duplikat nazwy
849exec addItemList 'club', 20000, 2 -- poprawne dodanie
850exec addItemList 'wand', 10000, 3 -- poprawne dodanie
851exec addItemList 'armor', 25000, 6 -- poprawne dodanie
852
853select * from Item_List
854
855-- Kupno przedmiotów
856exec levelUp 3 -- dodanie levelu postaci ( wieksza ilosc zlota )
857exec addPlayerItems 3, 5 -- poprawne dodanie (t_addPlayerItems), dodawanie do limitu
858exec addPlayerItems 8, 2 -- za malo pieniedzy
859
860select * from Player
861select * from Player_Items
862select * from Item_List
863
864-- Gildie
865exec addGuild 'FirstGuild', 3 -- poprawne dodanie, przypisanie ownera gildii, dodanie go do gildii
866 -- max czlonkow w gildii, max 3 dane profesje
867exec addGuild 'FirstGuild', 7 -- nazwa zajeta
868exec addGuild 'SecondGuild', 8 -- poprawne dodanie
869
870exec joinGuild 4, 1 -- przypisanie postaci do gildii
871exec joinGuild 500,1 -- gracz nie istnieje
872exec joinGuild 7,500 -- gildia nie istnieje
873exec joinGuild 7, 2 -- przypisanie postaci do gildii
874exec joinGuild 1, 2 -- limit warriorow
875exec joinGuild 10,2 -- limit warriorow
876
877select * from Player
878select * from Guild
879
880-- Walka postaci
881exec playerFight 3, 7 -- walka graczy, zwieksza poziom wygranemu, zmniejsza przegranemu,
882 -- dodaje pkt wygranej gildii jezeli gracz nalezy, dodanie smierci
883 -- i zabojstwa postaci
884exec playerFight 4, 8 -- jeden bez gildii
885exec playerFight 900,8 -- brak gracza
886exec addDeath 11 -- trigger na min level
887
888exec levelUp 8
889select * from Player
890select * from Guild
891select * from Player_Fight
892
893-- Widoki
894select * from topLevel
895select * from topBalance
896select * from topDeath
897select * from topKillers
898select * from topGuilds