· 10 years ago · Apr 13, 2016, 02:15 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-- zlicza ilość morderstw danego gracza
636create procedure killCountByNickName @nickname varchar(45)
637 select kills from Player where nickname=@nickname
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 3 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-- najlepsi gracze wzgledem ilosci smierci danego dnia
799create view dateTopDeath
800as
801Select top 5 nickname, deaths
802from player
803where
804-- zestawienie morderstw wg gracza
805create view topKillers
806exec killCountByNickName 'mateusz'
807-------------------------------------------------------------------
808-- SCENARIUSZ --
809-------------------------------------------------------------------
810-- Tworzenie kont ( admin, user )
811exec addAccountAdmin 'admin','admin','admin' -- dodanie admin konta
812exec addAccountAdmin 'admin','admin','admin' -- login zajety
813exec addAccountAdmin 'reguser','reguser','moderator' -- group id check
814exec addAccountAdmin 'reguser123','reguser','user' -- login check
815exec addAccountAdmin 'reguser','reg','user' -- password check
816exec addAccountAdmin 'reguser', 'reguser', 'user' -- dodanie usera przez admina
817exec addAccountUser 'mateusz','mateusz'
818exec addAccountUser 'test','test'
819
820select * from Account
821
822-- Modyfkacja kont ( admin, user )
823exec modifyAccountAdmin 900, 'user', 'user', 'user' -- zle id
824exec modifyAccountAdmin 14, 'mateusz', 'mateusz', 'user' -- login zajety
825exec modifyAccountAdmin 14, 'adminuser', 'adminuser', 'admin' -- zmiana konta reguser na admina
826exec modifyAccountUser 'test', 'test123' -- zmiana hasla
827
828select * from Account
829
830-- Dodanie postaci ( Admin, User )
831exec addPlayerAdmin 'Admin', 1, 500, 'warrior', 10, 30, 100000 -- dodanie przez admina
832exec addPlayerUser 'mateusz', 15, 'mage' -- dodanie postaci przez usera
833exec addPlayerUser 'mateusz', 16, 'warrior' -- zajety nick
834exec addPlayerUser 'mateuszDwa', 15, 'mage' -- dodanie postaci przez usera
835exec addPlayerUser 'mateuszTrzy', 15, 'mage' -- dodanie postaci przez usera
836exec addPlayerUser 'mateuszCztery', 15, 'mage' -- dodanie postaci przez usera
837exec addPlayerUser 'Test', 16, 'warrior' -- dodanie postaci przez usera
838exec addPlayerUser 'TestDwa', 16, 'warrior' -- dodanie postaci przez usera
839exec addPlayerUser 'AdminUser', 14, 'warrior' -- dodanie postaci przez usera
840
841select * from Player
842
843-- Usuwanie postaci
844exec deletePlayer 'mateuszOsiem', 15 -- usuniecie postaci ktora nie istnieje
845exec deletePlayer 'mateuszTrzy', 600 -- usuniecie postaci z nie swojego konta
846exec deletePlayer 'mateuszTrzy', 15 -- poprawne usuniecie
847
848select * from Player
849
850-- Blokada Konta
851exec banAccout 500, 'Uzywanie nielegalnego oprogramowania' -- brak takiej postci
852exec banAccout 10, 'Uzywanie nielegalnego oprogramowania' -- poprawny ban
853
854select * from Account_Ban
855
856-- Dodanie przedmiotow do spisu
857exec addItemList 'sword', 50000, 3 -- poprawne dodanie
858exec addItemList 'sword', 70000, 5 -- duplikat nazwy
859exec addItemList 'club', 20000, 2 -- poprawne dodanie
860exec addItemList 'wand', 10000, 3 -- poprawne dodanie
861exec addItemList 'armor', 25000, 6 -- poprawne dodanie
862
863select * from Item_List
864
865-- Kupno przedmiotów
866exec levelUp 3 -- dodanie levelu postaci ( wieksza ilosc zlota )
867exec addPlayerItems 3, 5 -- poprawne dodanie (t_addPlayerItems), dodawanie do limitu
868exec addPlayerItems 8, 2 -- za malo pieniedzy
869
870select * from Player
871select * from Player_Items
872select * from Item_List
873
874-- Gildie
875exec addGuild 'FirstGuild', 3 -- poprawne dodanie, przypisanie ownera gildii, dodanie go do gildii
876 -- max czlonkow w gildii, max 3 dane profesje
877exec addGuild 'FirstGuild', 7 -- nazwa zajeta
878exec addGuild 'SecondGuild', 8 -- poprawne dodanie
879
880exec joinGuild 4, 1 -- przypisanie postaci do gildii
881exec joinGuild 500,1 -- gracz nie istnieje
882exec joinGuild 7,500 -- gildia nie istnieje
883exec joinGuild 7, 2 -- przypisanie postaci do gildii
884exec joinGuild 1, 2 -- limit warriorow
885exec joinGuild 10,2 -- limit warriorow
886
887select * from Player
888select * from Guild
889
890-- Walka postaci
891exec playerFight 3, 7 -- walka graczy, zwieksza poziom wygranemu, zmniejsza przegranemu,
892 -- dodaje pkt wygranej gildii jezeli gracz nalezy, dodanie smierci
893 -- i zabojstwa postaci
894exec playerFight 4, 8 -- jeden bez gildii
895exec playerFight 900,8 -- brak gracza
896exec addDeath 11 -- trigger na min level
897
898exec levelUp 8
899select * from Player
900select * from Guild
901select * from Player_Fight
902
903-- Widoki
904select * from topLevel
905select * from topBalance
906select * from topDeath
907select * from topKillers
908select * from topGuilds