-- ------------------------------------------------------------------------------
-- This file is part of Spheres.
-- Copyright (C) 2019 Jesusalva
-- This library is free software; you can redistribute it and/or
-- modify it under the terms of the GNU Lesser General Public
-- License as published by the Free Software Foundation; either
-- version 2.1 of the License, or (at your option) any later version.
-- This library is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- Lesser General Public License for more details.
-- You should have received a copy of the GNU Lesser General Public
-- License along with this library; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
-- ------------------------------------------------------------------------------
-- Table structure for database
CREATE TABLE IF NOT EXISTS `login` (
`userid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`userpw` VARCHAR(32) NOT NULL DEFAULT '',
`email` VARCHAR(255) NOT NULL DEFAULT '',
--`guild_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`lastlogin` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`userid`),
KEY `name` (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=2000000;
CREATE TABLE IF NOT EXISTS `player` (
`userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`status` INT(11) NOT NULL DEFAULT 0,
`quest` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`gp` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`crystals` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`exp` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`level` SMALLINT(6) UNSIGNED NOT NULL DEFAULT 1,
`ap` SMALLINT(11) UNSIGNED NOT NULL DEFAULT 8,
`max_ap` INT(11) NOT NULL DEFAULT 8,
`aptime` INT(11) NOT NULL DEFAULT 0,
`max_inv` INT(11) UNSIGNED NOT NULL DEFAULT 25,
`lastlogin` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS `world` (
`userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`world` VARCHAR(32) NOT NULL DEFAULT '',
`status` INT(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`userid`,`world`)
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS `party` (
`userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`party_id` TINYINT(2) NOT NULL DEFAULT 0,
`member1_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`member1_ix` INT(11) NOT NULL DEFAULT -1,
`member2_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`member2_ix` INT(11) NOT NULL DEFAULT -1,
`member3_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`member3_ix` INT(11) NOT NULL DEFAULT -1,
`member4_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`member4_ix` INT(11) NOT NULL DEFAULT -1,
PRIMARY KEY (`userid`,`party_id`)
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS `inventory` (
`userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`index` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`unit_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`unit_lv` INT(11) UNSIGNED NOT NULL DEFAULT 1,
`unit_xp` INT(11) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`userid`,`index`)
) ENGINE=MyISAM;
-- .:: CURRENCY ::.
-- medals: Bounty currency (WIP)
-- miniap: Mini AP Elixirs (WIP)
-- maxiap: AP Elixirs (WIP)
-- ticketX: ticket for tavern ID X (WIP)
-- Standard_D3_v2
-- Add more stuff as desired.
CREATE TABLE IF NOT EXISTS `currency` (
`userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`medals` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`miniap` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`maxiap` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`ticket3` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`ticket4` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`ticket5` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`ticket6` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`ticket7` INT(11) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;
-- ---------------------------------- The event progress control table
-- .:: BOUNTY BOSS EVENTS ::.
-- bounty_medals: Medals accumulated
-- bounty_eventid: Entry from bounty.json to use (for reset and name etc.)
-- .:: SPONSOR EVENTS ::.
-- sponsor_gift: How many extra crystals to receive
-- sponsor_left: How many times you'll receive sponsor_gift
-- sponsor_last: Like login_date (DEPRECATED)
-- .:: RAID EVENTS ::.
-- Raids might need raid.json, defining:
-- Boss name, item names, shop worth, etc.
-- Keep in mind raid boss must be kept on a global array (GA)
-- raid_itemX: Amount of item X you have, where X is index 1~4.
-- raid_level: Maximum level you've reached on the raid. (auto-update by GA)
-- raid_eventid: Which entry from raid.json to use. Also determines data reset
-- .:: LOGIN EVENTS ::.
-- login_memo: Tracks total event login count
-- login_eventid: The event ID to which login_memo refers to
-- .:: GIFTING EVENTS ::.
-- You receive X normal gifts (special class, like crystals) from quests/daily
-- gifting_normal: Amount of normal gifts
-- gifting_premium: Amount of platinum gifts
-- gifting_recv_normal: Amount of RECEIVED normal gifts
-- gifting_recv_premium: Amount of RECEIVED platinum gifts
-- WIP: https://grandsphere.fandom.com/wiki/Summon_Wars_(Clokka)//
-- That's mostly server infrastructure and less heavy on SQL
CREATE TABLE IF NOT EXISTS `event` (
`userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`bounty_medals` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`bounty_eventid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`sponsor_gift` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`sponsor_left` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`sponsor_last` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`raid_item1` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`raid_item2` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`raid_item3` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`raid_item4` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`raid_level` INT(11) UNSIGNED NOT NULL DEFAULT 1,
`raid_eventid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`login_main` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`login_memo` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`login_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gifting_normal` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`gifting_premium` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`gifting_recv_normal` INT(11) UNSIGNED NOT NULL DEFAULT 0,
`gifting_recv_premium` INT(11) UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM;
