Hi!
I know there are many private server packages over there and they come with its database, but I designed this database thinking in understandment and expansibility. I know it might sound weird but (in my opinion) all private servers' database are poorly designed and don't follow a consistent standard, they're just made expecting to work.
This database is made following a standard (it's explained in the source) and can be easily expansable, so if you're making a private server from 0 (or you just realized your database sucks) and need help with making a new one, use this or take a look at it.
Here's the source:
0.1:
If you find something that can be changed or you don't know why it exists, post it here.
See you!
I know there are many private server packages over there and they come with its database, but I designed this database thinking in understandment and expansibility. I know it might sound weird but (in my opinion) all private servers' database are poorly designed and don't follow a consistent standard, they're just made expecting to work.
This database is made following a standard (it's explained in the source) and can be easily expansable, so if you're making a private server from 0 (or you just realized your database sucks) and need help with making a new one, use this or take a look at it.
Here's the source:
Code:
--
-- DarkOrbit general private server database
--
-- So I designed this database in order to offer a clean and easy to use
-- database for DarkOrbit private servers.
--
-- I was alone at home thinking about how to make a GOOD database.
-- Instead of making a table when it's needed I decided to study DarkOrbit and design
-- a database to have everything fixed.
-- The standard of this database is as follows:
--
-- -All table names are in plural. Why? A table contains various records so it's like
-- a collection of values. You don't say "An onion bag", instead you say "An onions bag"...
-- idk if this sounds good in English, but it does in Spanish :P
--
-- -All table identifiers are named "id" and are unsigned integers. Why? It makes more
-- readable the query, (I think) it's easier to read the query "SELECT * FROM `users` WHERE `users.id`=1"
-- than "SELECT * FROM `users` WHERE `users.usersID`=1"
--
-- -All table rows name are singular and grouped. Let me explain this...
-- The table `user` contains the values `register_ip` and `register_date`.
-- Those values are grouped by `register` because they're used when the user registers
-- Same applies to table names: `accounts_messages` and `accounts_profiles` are grouped by `accounts`
--
-- -Other table references are tableName_rowName, for example, table `accounts` contains the row
-- `users_id` you can read it like "From table `users` use row `id`"
--
-- If you find anything wrong here, or you want me to change something, post it in the thread.
-- I STRONGLY RECOMMEND YOU that if you extend this database to use the same standard as mine.
-- If you don't, change the whole database standards because it's going to be a mess.
--
-- See you!
--
-- @author Manulaiko
--
-- @version 0.2
--
-- Change the name of the database here
DROP DATABASE IF EXISTS `blackeye`;
CREATE DATABASE `blackeye`;
USE `blackeye`;
-- --------------------------------------------------------
--
-- Accounts
--
-- User's account, there can be more than one row with same `users_id`
--
-- @author Manulaiko
--
-- @version 0.2
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`users_id` int(10) UNSIGNED NOT NULL,
`register_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`register_ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',
`register_users_id` int(10) UNSIGNED NOT NULL,
`last_login_date` timestamp NULL DEFAULT NULL,
`last_login_ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',
`session_id` char(32) NOT NULL,
`accounts_equipment_hangars_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL,
`factions_id` tinyint(3) UNSIGNED NOT NULL,
`clans_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`uridium` int(11) NOT NULL DEFAULT '10000',
`credits` bigint(20) NOT NULL DEFAULT '10000',
`jackpot` decimal(5,2) NOT NULL DEFAULT '0.00',
`experience` bigint(20) NOT NULL DEFAULT '0',
`honor` int(11) NOT NULL DEFAULT '0',
`levels_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`is_premium` tinyint(1) NOT NULL DEFAULT '0',
`ranks_id` int(10) UNSIGNED NOT NULL DEFAULT '1',
`rank_points` int(10) UNSIGNED NOT NULL DEFAULT '0',
`quests` varchar(255) NOT NULL DEFAULT '[]',
PRIMARY KEY (`id`),
UNIQUE KEY `session_id` (`session_id`, `register_users_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Equipment
-- Configurations
--
-- Accounts configurations
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_equipment_configurations` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`accounts_equipment_ships_id` int(10) UNSIGNED NOT NULL,
`configuration` tinyint(3) UNSIGNED NOT NULL,
`lasers` varchar(1023) NOT NULL DEFAULT '[]',
`hellstorms` varchar(255) NOT NULL DEFAULT '[]',
`generators` varchar(1023) NOT NULL DEFAULT '[]',
`extras` varchar(1023) NOT NULL DEFAULT '[]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Equipment
-- Drones
--
-- Account's drones
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_equipment_drones` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`accounts_id` int(10) UNSIGNED NOT NULL,
`items_id` int(10) UNSIGNED NOT NULL,
`levels_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`experience` smallint(5) UNSIGNED NOT NULL,
`damage` decimal(3,2) NOT NULL,
`configurations` varchar(2047) NOT NULL DEFAULT '[{"hangarID":1,"EQ":[{"configID":1,"default":["69","68"],"design":[]},{"configID":2,"default":[],"design":[]}]}',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Equipment
-- Hangars
--
-- Account's hangars
--
-- @author Manulaiko
--
-- @version 0.2
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_equipment_hangars` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`accounts_id` int(10) UNSIGNED NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`name` varchar(255) NOT NULL DEFAULT 'HANGAR',
`resources` varchar(1023) NOT NULL DEFAULT '[0,0,0,0,0,0,0,0,0]',
`configurations` varchar(2047) NOT NULL DEFAULT '[{"configurationId":1,"lasers":[],"heavy_guns":[],"generators":[],"extras":[]},{"configurationId":2,"lasers":[],"heavy_guns":[],"generators":[],"extras":[]}]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Equipment
-- Items
--
-- Accounts items
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_equipment_items` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`items_id` int(10) UNSIGNED NOT NULL,
`levels_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`amount` int(10) UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Equipment
-- Ships
--
-- If `accounts_equipment_hangars_id` is 0 it means ship stills in shipsyard
--
-- @author Manulaiko
--
-- @version 0.2
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_equipment_ships` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`accounts_id` int(10) UNSIGNED NOT NULL,
`accounts_equipment_hangars_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`ships_id` smallint(5) UNSIGNED NOT NULL,
`maps_id` int(10) UNSIGNED NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`position` varchar(255) NOT NULL DEFAULT '[]',
`health` int(11) NOT NULL DEFAULT '0',
`nanohull` int(11) NOT NULL DEFAULT '0',
`shield` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `accounts_equipment_hangars_id` (`accounts_equipment_hangars_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
--
-- Accounts
-- GalaxyGates
--
-- Account's galaxy gates
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_galaxygates` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`galaxygates_id` tinyint(3) UNSIGNED NOT NULL,
`accounts_id` int(10) UNSIGNED NOT NULL,
`parts` tinyint(3) UNSIGNED NOT NULL,
`wave` smallint(6) NOT NULL DEFAULT '-1',
`lives` tinyint(4) NOT NULL DEFAULT '-1',
`amount` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Messages
--
-- `from_status` and `to_status` contains the current status of the
-- message for the author and the receiver.
-- Possible values:
-- -0: Message isn't read
-- -1: Message is read
-- -2: Message is deleted
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_messages` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`from_accounts_id` int(10) UNSIGNED NOT NULL,
`from_status` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`to_accounts_id` int(10) UNSIGNED NOT NULL,
`to_status` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`title` varchar(255) NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Accounts
-- Profiles
--
-- Account's profile
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `accounts_profiles` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`accounts_id` int(10) UNSIGNED NOT NULL,
`skin` varchar(15) NOT NULL DEFAULT 'blue',
`avatar` varchar(255) NOT NULL DEFAULT 'default.jpg',
`status` varchar(1023) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `accounts_id` (`accounts_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Collectables
--
-- A node from game.xml:
-- <collectable class="2" id="132" resKey="beacon_3_2" easterResKey="beacon_easter_3_2" soundID="3"/>
-- If you take a look you'll see there are different classes of
-- collectables:
-- -BonusBoxes (0)
-- -Resources (1)
-- -Beacons (2)
-- -Fireworks (3)
-- The row `gfx` is the attribute "id" and the row `name` is the attribute "resKey"
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.2
--
CREATE TABLE IF NOT EXISTS `collectables` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`gfx` tinyint(3) UNSIGNED NOT NULL DEFAULT '2',
`class` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`name` varchar(255) NOT NULL DEFAULT 'box2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Clan
--
-- Ranks JSON:
-- An array that contains objects that represents the rank.
-- Object variables:
-- -name: Rank name.
-- -rights: Array containing rank rights.
--
-- Example:
-- [{
-- "name": "Rank 1",
-- "rights": [
-- true, //Create Newsletters
-- false, //View and Edit applications
-- false, //Dismiss members
-- false, //Edit ranks
-- false, //Diplomacy: View requests, start diplomatic agreement
-- false, //Diplomacy: End alliances
-- false, //Diplomacy: Declare war on other clans
-- false, //Payment from clan treasury
-- false, //Change tax rate
-- false, //Create clan news
-- false, //Manage Battle Station Modules
-- false, //Activate/deactivate Battle Station Deflector
-- false, //Build Battle Station
-- ]
-- }, {
-- "name": "Rank 2",
-- "rights": [
-- true, //Create Newsletters
-- false, //View and Edit applications
-- false, //Dismiss members
-- false, //Edit ranks
-- false, //Diplomacy: View requests, start diplomatic agreement
-- false, //Diplomacy: End alliances
-- false, //Diplomacy: Declare war on other clans
-- false, //Payment from clan treasury
-- false, //Change tax rate
-- false, //Create clan news
-- true, //Manage Battle Station Modules
-- true, //Activate/deactivate Battle Station Deflector
-- true, //Build Battle Station
-- ]
-- }]
--
-- Members JSON:
-- An array that contains objects that represents members in the clan.
-- Object variables:
-- -accounts_id
-- -rank: Member's rank (name or JSON array index) (-1 = leader)
-- -date: Join date (timestamp or full date)
--
-- Example:
-- [{
-- "accounts_id": 1,
-- "rank": -1,
-- "date": 11992456,
-- }, {
-- "accounts_id": 2,
-- "rank": "Rank 2",
-- "date": "2015-12-25 00:00:00",
-- }]
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.2
--
CREATE TABLE IF NOT EXISTS `clans` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`tag` varchar(4) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`ranks` varchar(1023) NOT NULL DEFAULT '[]',
`members` varchar(1023) NOT NULL DEFAULT '[]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Factions
-- `is_public` means if user can join to the company (Saturn company isn't public)
--
-- @author Manulaiko
--
-- @version 0.2
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `factions` (
`id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`abbreviation` char(3) NOT NULL,
`color` char(6) NOT NULL,
`is_public` tinyint(1) NOT NULL DEFAULT '0',
`home_maps_id` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`home_position` varchar(255) NOT NULL DEFAULT '[1000,1000]',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`,`abbreviation`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `factions` (`id`, `name`, `abbreviation`, `color`, `is_public`, `home_maps_id`, `home_position`) VALUES
(1, 'Mars Mining Operations', 'mmo', 'FF8787', 1, 1, '[1000,1000]'),
(2, 'Earth Industries Corporations', 'eic', '87FFFF', 1, 5, '[18500,1000]'),
(3, 'Venus Resources Unlimited', 'vru', 'C3FF87', 1, 8, '[18500,12500]');
-- --------------------------------------------------------
--
-- GalaxyGates
-- Reward JSON:
-- An object that contains the reward that user receives once
-- the gate is completed
-- Variables:
-- -experience
-- -honor
-- -uridium
-- -credits
-- -resources: An array containing the amount of resources of the
-- cargobox (if any), each index is `collectables_id`
-- -others: An array containing an object that represents other items that
-- can be dropped when gate is completed.
-- Variables:
-- -items_id
-- -amount
-- -provability: A double that contains the provability of the gate
-- to drop "items_id" once it's completed
-- Example:
-- {
-- "experience": 1000000,
-- "honor": 100000,
-- "uridium": 10000,
-- "credits": 10000000,
-- "resources": [
-- 0, //prometium
-- 0, //Endurium
-- 0, //Terbium
-- 0, //Prometid
-- 0, //Duranium
-- 0, //Xenomit
-- 0, //Promerium
-- 0, //Seprom
-- 0, //Palladium
-- ],
-- "others": [{
-- "items_id": 243, //Let's assume it's LF4
-- "provability": 33.33
-- }]
-- }
--
-- Waves JSON:
-- An array that contains arrays for each step of the wave.
-- The wave step is a object that contains the "ship_id" and the "amount" of "ship_id"
-- Example:
-- [[ //Wave 1
-- { //step 1
-- "ships_id": 84, //Streuners
-- "amount": 10
-- }, { //step 2, once user has completed step 1
-- "ships_id": 84, //Streuners
-- "amount": 20
-- }, { //step 3, once user has completed step 2
-- "ships_id": 84, //Streuners
-- "amount": 10
-- }
-- ], [ //Wave 2
-- { //step 1
-- "ships_id": 85, //Lordakias
-- "amount": 20
-- }, { //step 2, once user has completed step 1
-- "ships_id": 85, //Lordakias
-- "amount": 40
-- }, { //step 3, once user has completed step 2
-- "ships_id": 85, //Lordakias
-- "amount": 20
-- }
-- ]]
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `galaxygates` (
`id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`maps_id` tinyint(3) UNSIGNED NOT NULL,
`parts` tinyint(3) UNSIGNED NOT NULL,
`reward` varchar(1023) NOT NULL DEFAULT '{"experience":0,"honor":0,"uridium":0,"credits":0,"resources":[0,0,0,0,0,0,0,0,0],"others":[]}',
`waves` varchar(2047) NOT NULL DEFAULT '[]',
PRIMARY KEY (`id`),
UNIQUE KEY `maps_id` (`maps_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Items
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `items` (
`id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`loot_id` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`category` varchar(31) NOT NULL,
`filter` tinyint(3) UNSIGNED NOT NULL,
`price_buy` int(10) UNSIGNED NOT NULL,
`price_sell` int(10) UNSIGNED NOT NULL,
`is_elite` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `loot_id` (`loot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Levels
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `levels` (
`id` tinyint(3) UNSIGNED NOT NULL,
`accounts` int(10) UNSIGNED NOT NULL,
`drones` smallint(6) NOT NULL DEFAULT '-1',
`pets` mediumint(9) NOT NULL DEFAULT '-1',
`upgrade_credits` mediumint(9) NOT NULL DEFAULT '-1',
`upgrade_uridium` smallint(6) NOT NULL DEFAULT '-1',
PRIMARY KEY (`id`),
UNIQUE KEY `accounts` (`accounts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Maps
-- Portals JSON:
-- An array containing objects that represents the portal.
-- Variables:
-- -level: Required level in order to use the portal
-- -position: An array containing X and Y position (0 = x, 1 = y)
-- -target_position: The position of the user once he has used the portal
-- -target_maps_id: The map of the user once he has used the portal
-- -is_visible
-- -is_working: Some pirate maps portals don't work
-- -faction_scrap: Pirate maps portals has a cartel with faction name in it
-- -gfx: Take a look at game.xml
--
-- Example:
-- [{
-- "level": 1,
-- "position": [
-- 18500, //X
-- 12000, //Y
-- ],
-- "target_position": [
-- 1000, //X
-- 1000, //Y
-- ],
-- "target_maps_id": 2,
-- "is_visible": true,
-- "is_working": true,
-- "faction_scrap": 0, //No faction scrap
-- "gfx": 1 //Default portal
-- }]
--
-- Stations JSON:
-- An array containing map's station.
-- Variables:
-- -position: Station position
-- -factions_id: Station's faction
--
-- Example:
-- [{
-- "position": [
-- 1000, //X
-- 1000, //Y
-- ],
-- "factions_id": 1
-- }]
--
-- NPCS JSON:
-- An array containing map's NPCs.
-- Variables:
-- -ships_id
-- -amount
--
-- Example:
-- [{
-- "ships_id": 84,
-- "amount": 50
-- }]
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `maps` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` char(3) NOT NULL,
`limits` point NOT NULL,
`portals` varchar(2047) NOT NULL DEFAULT '[]' COMMENT '[{"level":1,"position":[1000,1000],"target_position":[1000,1000],"target_maps_id":2,"is_visible":true,"is_working":true,"factions_scrap":0,"gfx":1}]',
`stations` varchar(1023) NOT NULL DEFAULT '[]' COMMENT '[{"position":[1000,1000],"factions_id":1}]',
`npcs` varchar(1023) NOT NULL DEFAULT '[]' COMMENT '[{"ships_id":1,"amount":100}]',
`is_pvp` tinyint(1) NOT NULL DEFAULT '0',
`is_starter` tinyint(1) NOT NULL DEFAULT '0',
`factions_id` tinyint(3) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- News
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `news` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`permalink` varchar(31) NOT NULL,
`title` varchar(255) NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `permalink` (`permalink`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Quests
-- Reward JSON:
-- An array that contains an object with 2 variables:
-- -items_id: The ID of the reward item (from `items`)
-- things like uridium, credits, experience or honor
-- don't have a row in `items` so just put the name in uppercase
-- ("URIDIUM", "CREDITS", "EXPERIENCE", "HONOR")
-- -amount: The amount of items to give
-- Example:
-- [{
-- "items_id": "CREDITS",
-- "amount": 10000
-- }, {
-- "items_id": 1,
-- "amount": 4
-- }]
--
-- Quest JSON:
-- An array that contains the things to do in order
-- to complete the quest.
-- Each index is an object that contains the thing to do
-- The content of the object can vary from each kind of thing
-- (Collect ore, kill enemies, stay alive...)
--
-- Common variables:
-- -type: The kind of type the user must complete.
-- it can be either a constant (uppercase string) or a integer
-- Examples:
-- - COLLECT (1)
-- - FLY_DISTANCE (2)
-- - KILL (3)
-- - STAY_ALIVE (4)
-- - VISIT_MAP (5)
-- - ANY_OF (6)
-- - IN_ORDER (7)
-- -condition: An array containing objects that represents the condition
-- to accomplish. Variables:
-- -type: Can be either a constant or an integer and it represents
-- the type of condition, Examples:
-- - ACCOMPLISH_BEFORE (1)
-- - ON_MAP (2)
-- - DO_NOT_DIE (3)
-- -value: An integer that contains the value of the condition.
-- Examples:
-- [{
-- "type": "ACCOMPLISH_BEFORE",
-- "value": 3600, //Number of seconds on which the condition should be accomplished
-- }, {
-- "type": 2,
-- "value": 1, //maps_id, if is string like home1 represents user's faction 1st map (1-1 MMO, 2-1 EIC, 3-1 VRU)
-- }, {
-- "type": "DO_NOT_DIE",
-- "value": 5,//Don't die more than 5 times (0=Must complete the condition without dying)
-- }]
-- -locked: A boolean that tells if the user needs to complete the condition asked before the current one
--
-- Variables for each type:
-- COLLECT:
-- Requires user to collect collectables.
-- Variables:
-- -collectables_id: Identifier of table `collectables` (0=any)
-- -amount: Amount of collectables to collect
--
-- FLY_DISTANCE:
-- Requires user to fly a distance.
-- Variables:
-- -distance: Amount of distance to travel
--
-- KILL:
-- Requires user to kill a ship (user or npc).
-- Variables:
-- -ships_id: Identifier of table `ships` (0=any)
-- -amount: Amount of ships to kill
--
-- STAY_ALIVE:
-- Requires the user to stay alive.
-- Variables:
-- -amount: Amount of seconds to stay alive
--
-- VISIT_MAP:
-- Requires the user to visit a map
-- -maps_id: Identifier of table `maps` (0=any)
--
-- ANY_OF:
-- An array containing different options that user can choose to complete.
-- The array is like the main quest array.
-- Variables:
-- -options: the array
--
-- IN_ORDER:
-- Same as above but user needs to complete the things in order.
-- Variables:
-- -options: the array
-- Example:
-- [{
-- "type": "COLLECT",
-- "collectables_id": 1, //Let's assume it's prometium
-- "amount": 10,
-- "condition": [{
-- "type": "ON_MAP",
-- "value": "home2" //MMO: 1-2, EIC: 2-2, VRU: 3-2
-- },{
-- "type": "ACCOMPLISH_BEFORE",
-- "value": 3600 //In less than an hour
-- }]
-- }, {
-- "type": "IN_ORDER",
-- "options": [{ //Accomplish the following things in order
-- "type": "KILL",
-- "ships_id": 84, //Streuners
-- "amount": 10,
-- "condition": [{
-- "type": "ON_MAP",
-- "value": "home-1" //MMO: 1-1, EIC: 2-1, VRU 3-1
-- }, {
-- "type": "DO_NOT_DIE",
-- "value": 0 //Complete it without dying
-- }]
-- }, {
-- "type": "STAY_ALIVE",
-- "amount": 300, //Stay alive 5 minutes
-- "condition": [{
-- "type": "ON_MAP",
-- "value": "enemy-5" //For MMO: 2-5 and 3-5, for EIC: 1-5 and 3-5, for VRU. 1-5 and 2-5
-- }]
-- }]
-- }]
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `quests` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`reward` varchar(1023) NOT NULL,
`quest` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Ranks
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `ranks` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(31) NOT NULL,
`percentage` decimal(3,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Ships
-- Reward JSON:
-- An object that contains the reward that user receives once
-- the ship is killed
-- Variables:
-- -experience
-- -honor
-- -uridium
-- -credits
-- -resources: An array containing the amount of resources of the
-- cargobox, each index is `collectables_id`
-- -others: An array containing an object that represents other items that
-- can be dropped when ship is kill.
-- Variables:
-- -items_id
-- -amount
-- -provability: A double that contains the provability of the ship
-- to drop "items_id" once it's kill
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `ships` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`items_id` int(10) UNSIGNED NOT NULL,
`health` int(10) UNSIGNED NOT NULL DEFAULT '1000',
`speed` smallint(5) UNSIGNED NOT NULL DEFAULT '100',
`cargo` smallint(5) UNSIGNED NOT NULL DEFAULT '100',
`reward` varchar(2047) NOT NULL DEFAULT '{"experience":0,"honor":0,"uridium":0,"credits":0,"resources":[0,0,0,0,0,0,0,0,0],"others":[]}',
`ai` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`damage` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `items_id` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Trade
-- All items that are available in the trade are stored here.
-- `accounts_id` is the highest bidder and `bid` is the highest bid
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `trade` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`items_id` int(10) UNSIGNED NOT NULL,
`accounts_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`bid` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `items_id` (`items_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Users
-- This tables represents user's details.
-- Instead of putting all account related things here I've made
-- another table so same user can have more than one account.
--
-- The account on which user logged in last time is stored in `last_login_accounts_id`
-- and the account that was created when the user registered is in `register_accounts_id`
--
-- About email verification, if `email_verification_date` is 0 it means user hasn't
-- verified its email
--
-- @author Manulaiko
--
-- @version 0.1
--
-- @since 0.1
--
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`invitation_code` char(32) NOT NULL DEFAULT '00000000000000000000000000000000',
`register_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`register_ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',
`register_accounts_id` int(10) UNSIGNED NOT NULL,
`last_login_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_login_ip` varchar(15) NOT NULL DEFAULT '0.0.0.0',
`last_login_accounts_id` int(10) UNSIGNED NOT NULL,
`session_id` char(32) NOT NULL,
`name` varchar(255) NOT NULL,
`password` char(32) NOT NULL,
`email` varchar(255) NOT NULL,
`email_verification_code` char(32) NOT NULL,
`email_verification_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
UNIQUE KEY `invitation_code` (`invitation_code`,`register_accounts_id`,`session_id`,`name`,`email`,`email_verification_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
If you find something that can be changed or you don't know why it exists, post it here.
See you!
