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