Skrypty do SQL'a

peruzi

I. Ogólne:

1).Usuwanie czarów z danej postaci

UPDATE Character
SET MagicList = NULL where name = 'NAZWA_POSTACI'

2).Usuwanie Gildi

DELETE FROM Guild
DELETE FROM GuildMember

3).Opróżnianie skrzyni i inventorów graczy

* Wszystkie skrzynie i plecaki:

UPDATE Character SET Inventory = NULL
UPDATE Warehouse SET Items = NULL

* Wybranej postaci:

UPDATE Character SET Inventory = null WHERE Name = 'NAZWA_POSTACI'
UPDATE Warehouse SET Items = null WHERE AccountID = 'LOGIN'

4).Wyrzuca wszystkie konta ktore nie posiadaja postaci.

DELETE FROM MEMB_STAT WHERE memb___id NOT IN (SELECT AccountID FROM Character)
DELETE FROM MEMB_INFO WHERE memb___id NOT IN (SELECT AccountID FROM Character)
DELETE FROM VI_CURR_INFO WHERE memb___id NOT IN (SELECT AccountID FROM Character)
DELETE FROM warehouse WHERE AccountID NOT IN (SELECT AccountID FROM Character)
DELETE FROM AccountCharacter WHERE ID NOT IN (SELECT AccountID FROM Character)

5).Skrypt ktory usuwa nieuzywane konta od wybranego przez nas czasu. 28(4tygodnie) mozemy zmienic na wiecej albo mniej w zaleznosci od naszych potrzeb.

USE MUONLINE
DECLARE @Accid nvarchar(10)
DECLARE LISTA CURSOR LOCAL FOR
SELECT memb___id FROM MEMB_STAT
WHERE connectTM < getdate()-28
OPEN LISTA
FETCH NEXT FROM LISTA INTO @Accid
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM Character WHERE AccountId = @Accid
DELETE FROM AccountCharacter WHERE ID = @Accid
DELETE FROM VI_CURR_INFO WHERE memb___id = @Accid
DELETE FROM MEMB_STAT WHERE memb___id = @Accid
DELETE FROM warehouse WHERE AccountID = @Accid
--DELETE FROM WebUsers WHERE login = @Accid
DELETE FROM MEMB_INFO WHERE memb___id = @Accid
FETCH NEXT FROM LISTA INTO @Accid
END

6).Usuwanie wszystkich kont z danego adresu IP. Zamiast 127.0.0.1 podajemy adres IP gościa.

DECLARE @Acc varchar(10);
DECLARE LISTA CURSOR LOCAL FOR
(SELECT memb___id FROM MEMB_STAT WHERE (IP LIKE '127.0.0.1' + '%'))
OPEN LISTA
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM LISTA INTO @Acc;
DELETE FROM AccountCharacter WHERE ID = @Acc;
DELETE FROM Guild WHERE G_Master IN (SELECT Name FROM Character WHERE (AccountID = @Acc));
DELETE FROM GuildMember WHERE Name IN (SELECT Name FROM Character WHERE (AccountID = @Acc));
DELETE FROM Character WHERE AccountID = @Acc;
DELETE FROM MEMB_STAT WHERE memb___id = @Acc;
DELETE FROM MEMB_INFO WHERE memb___id = @Acc;
DELETE FROM VI_CURR_INFO WHERE memb___id = @Acc;
DELETE FROM warehouse WHERE AccountID = @Acc;
END
CLOSE LISTA;
DEALLOCATE LISTA;

7).Przypisuje wszystkiem GMom isAdmin=1 jednoczesnie usuwajach ich z rankingu na stronie.

USE MUONLINE

UPDATE Character
SET isAdmin = 1
WHERE CtlCode = 8

II. Do strony STMP2

1). Dodanie tabeli Reset

USE [MuOnline];
GO

ALTER TABLE [Character] ADD [Reset] [smallint] DEFAULT(0) NOT NULL;

2). Dodanie tabeli isAdmin

USE [MuOnline];
GO

ALTER TABLE [Character] ADD [isAdmin] [smallint] DEFAULT(0) NOT NULL;

3). Dodanie tabeli ST_WYMIANA

CREATE TABLE ST_WYMIANA (
Id int,
RenCount int,
Ren2Count int,
NewItem nvarchar(10),
ItemLevel nvarchar(5),
ItemOpt nvarchar(5),
ItemSL nvarchar(5),
ItemEopt1 nvarchar(5),
ItemEopt2 nvarchar(5),
ItemEopt3 nvarchar(5),
ItemEopt4 nvarchar(5),
ItemEopt5 nvarchar(5),
ItemEopt6 nvarchar(5),
ItemEoptMAX smallint,
Autoserial smallint,
Dur smallint,
PRIMARY KEY (Id)
)

3). Dodanie tabeli ST_WARNING

CREATE TABLE ST_WARNING (
Name nvarchar(10),
W_Level smallint,
W_1_kom nvarchar(50),
W_2_kom nvarchar(50),
W_3_kom nvarchar(50),
Banned_to int,
PRIMARY KEY (Name)
)

4). Dodanie tabeli vault

CREATE TABLE [dbo].[vault] (
[AccountID] [nvarchar] (10) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[vault] [varbinary] (5000) NULL ,
[money] [bigint] DEFAULT (0) NOT NULL ,
[vault_size] [int] DEFAULT (0) NOT NULL
)

5). Dodanie tabeli AucMain

CREATE TABLE AucMain (
AucID int,
Name nvarchar(10),
AccountId nvarchar(10),
AucItem varbinary(20),
StartDate int,
EndDate int,
MinPrice bigint,
CurPrice bigint
)

CREATE TABLE [dbo].[AucCURINFO] (
[AcID] [int] NOT NULL ,
[AccountID] [nvarchar] (10) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[Name] [nvarchar] (10) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[Price] [bigint] NOT NULL ,
[ADate] [bigint] NOT NULL ,
[isdef] [smallint] NOT NULL
)
CREATE TABLE [dbo].[AucMain] (
[AucID] [int] NOT NULL ,
[Name] [nvarchar] (10) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[AccountId] [nvarchar] (10) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[AucItem] [varbinary] (20) NOT NULL ,
[StartDate] [int] NOT NULL ,
[EndDate] [int] NOT NULL ,
[MinPrice] [bigint] NOT NULL ,
[CurPrice] [bigint] NULL
)

6). Dodanie tabeli ST_GATE

CREATE TABLE [dbo].[ST_GATE] (
[GateId] [int] NOT NULL ,
[GateItem] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[GateItemLevel] [int] NULL ,
[MapNumber] [smallint] NULL ,
[MapX] [int] NULL ,
[MapY] [int] NULL ,
[WarpMoney] [int] NULL ,
[InvitName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MapName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[GateImage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[MaxUsers] [int] NULL ,
[MinLevel] [int] NULL
) ON [PRIMARY]
GO

7). Dodanie tabeli MAILSYSTEM

CREATE TABLE [dbo].[MAILSYSTEM] (
[ID] [int] NOT NULL ,
[od] [nvarchar] (11) COLLATE Chinese_PRC_CS_AS NULL ,
[do] [nvarchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[kiedy] [bigint] NULL ,
[Temat] [text] COLLATE Chinese_PRC_CS_AS NULL ,
[Tekst] [text] COLLATE Chinese_PRC_CS_AS NULL ,
[Odczytana] [smallint] NULL
)

8 ). Dodanie tabeli ST_CHAOS

CREATE TABLE [dbo].[ST_CHAOS] (
[Id] [int] NOT NULL ,
[Request] [nvarchar] (255) COLLATE Chinese_PRC_CS_AS NULL ,
[Success_rate] [smallint] NULL ,
[Effect] [nvarchar] (255) COLLATE Chinese_PRC_CS_AS NULL ,
[Fail_Effect] [nvarchar] (255) COLLATE Chinese_PRC_CS_AS NULL ,
[Money] [int] NULL ,
[Deny_for] [nvarchar] (255) COLLATE Chinese_PRC_CS_AS NULL
)

9). Dodanie tabeli ST_ADMIN

CREATE TABLE [dbo].[ST_ADMIN] (
[AccountID] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[AuthLevel] [int] NOT NULL
) ON [PRIMARY]
GO

10). Dodanie tabeli ST_SHOP

CREATE TABLE [dbo].[ST_SHOP] (
[Id] [smallint] NOT NULL ,
[Item] [nvarchar] (10) COLLATE Chinese_PRC_CS_AS NULL ,
[Lvl] [smallint] NULL ,
[Dur] [smallint] NULL ,
[Skill] [tinyint] NULL ,
[Luck] [tinyint] NULL ,
[Opt] [tinyint] NULL ,
[Exc] [nvarchar] (2) COLLATE Chinese_PRC_CS_AS NULL ,
[Request] [nvarchar] (50) COLLATE Chinese_PRC_CS_AS NULL ,
[Money] [bigint] NULL ,
[Autoserial] [tinyint] NULL
)

11). Naliczanie resetow na stronie STMP2

UPDATE Character SET clevel=('1') , experience=('0'), Reset=Reset+1 WHERE clevel>LVL_PO_KTÓRYM_JEST_RESET

12). Naprawianie licznika Online gdy gwałtownie wyłączymy server

UPDATE MEMB_STAT SET ConnectStat = 0

--------------------

Stawianie servera by Bulka Tiger