full.sql

-- SWIFT Panel Database
-- Version 1.6.1
-- 8/17/2009d

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

--
-- Table structure for table `admin`
--

DROP TABLE IF EXISTS `admin`;
CREATE TABLE IF NOT EXISTS `admin` (
  `adminid` int(10) unsigned NOT NULL auto_increment,
  `username` text NOT NULL,
  `password` text NOT NULL,
  `firstname` text NOT NULL,
  `lastname` text NOT NULL,
  `email` text NOT NULL,
  `access` text NOT NULL,
  `notes` text NOT NULL,
  `status` text NOT NULL,
  `lastlogin` text NOT NULL,
  `lastip` text NOT NULL,
  `lasthost` text NOT NULL,
  `totp` varchar(64) NOT NULL default '',
  `totp_recovery` text NOT NULL,
  PRIMARY KEY  (`adminid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `admin`
--

INSERT INTO `admin` (`adminid`, `username`, `password`, `firstname`, `lastname`, `email`, `access`, `notes`, `status`, `lastlogin`, `lastip`, `lasthost`) VALUES
(1, 'admin', '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8', 'Admin', 'Account', 'user@example.com', 'Super', 'Check out the forums at www.SwiftPanel.com!\r\nRead manuals on how to setup remote boxes & install game servers in the forums.\r\nDon''t forget to setup the Cron Job. Go to Cron Settings under Configuration.', 'Active', '', '', '');

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

--
-- Table structure for table `box`
--

DROP TABLE IF EXISTS `box`;
CREATE TABLE IF NOT EXISTS `box` (
  `boxid` int(10) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  `location` text NOT NULL,
  `ip` text NOT NULL,
  `login` text NOT NULL,
  `password` text NOT NULL,
  `ftpport` text NOT NULL,
  `sshport` text NOT NULL,
  `ostype` text NOT NULL,
  `cost` text NOT NULL,
  `passive` text NOT NULL,
  `notes` text NOT NULL,
  `ftp` text NOT NULL,
  `ssh` text NOT NULL,
  `load` text NOT NULL,
  `idle` text NOT NULL,
  PRIMARY KEY  (`boxid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `box`
--

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

--
-- Table structure for table `client`
--

DROP TABLE IF EXISTS `client`;
CREATE TABLE IF NOT EXISTS `client` (
  `clientid` int(10) unsigned NOT NULL auto_increment,
  `email` text NOT NULL,
  `password` text NOT NULL,
  `firstname` text NOT NULL,
  `lastname` text NOT NULL,
  `company` text NOT NULL,
  `address1` text NOT NULL,
  `address2` text NOT NULL,
  `city` text NOT NULL,
  `state` text NOT NULL,
  `postcode` text NOT NULL,
  `country` text NOT NULL,
  `phone` text NOT NULL,
  `notes` text NOT NULL,
  `status` text NOT NULL,
  `lastlogin` datetime NOT NULL,
  `lastip` text NOT NULL,
  `lasthost` text NOT NULL,
  `totp` varchar(64) NOT NULL default '',
  `totp_recovery` text NOT NULL,
  `created` date NOT NULL,
  PRIMARY KEY  (`clientid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `client`
--

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

--
-- Table structure for table `config`
--

DROP TABLE IF EXISTS `config`;
CREATE TABLE IF NOT EXISTS `config` (
  `setting` varchar(255) NOT NULL,
  `value` text NOT NULL,
  KEY `setting` (`setting`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `clientdatabase`
--

CREATE TABLE IF NOT EXISTS `clientdatabase` (
  `dbid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `dbname` varchar(64) NOT NULL,
  `dbuser` varchar(32) NOT NULL,
  `dbpass` text NOT NULL,
  `dbhost` varchar(64) NOT NULL default '%',
  `maxsize` int(10) unsigned NOT NULL default '0',
  `disksize` decimal(12,2) NOT NULL default '0.00',
  `created` datetime default NULL,
  PRIMARY KEY  (`dbid`),
  UNIQUE KEY `dbname` (`dbname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `announcement`
--

CREATE TABLE IF NOT EXISTS `announcement` (
  `annid` int(10) unsigned NOT NULL auto_increment,
  `title` text NOT NULL,
  `body` text NOT NULL,
  `active` text NOT NULL,
  `adminname` text NOT NULL,
  `created` datetime default NULL,
  PRIMARY KEY  (`annid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `schedule`
--

CREATE TABLE IF NOT EXISTS `schedule` (
  `schedid` int(10) unsigned NOT NULL auto_increment,
  `serverid` int(10) unsigned NOT NULL,
  `clientid` int(10) unsigned NOT NULL,
  `label` text NOT NULL,
  `action` varchar(16) NOT NULL,
  `command` text NOT NULL,
  `freq` varchar(10) NOT NULL,
  `at_minute` tinyint(3) unsigned NOT NULL default '0',
  `at_hour` tinyint(3) unsigned NOT NULL default '0',
  `dow` tinyint(3) unsigned NOT NULL default '0',
  `enabled` char(1) NOT NULL default '1',
  `lastrun` datetime default NULL,
  `nextrun` datetime default NULL,
  PRIMARY KEY  (`schedid`),
  KEY `due` (`enabled`,`nextrun`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `backup`
--

CREATE TABLE IF NOT EXISTS `backup` (
  `backupid` int(10) unsigned NOT NULL auto_increment,
  `serverid` int(10) unsigned NOT NULL,
  `clientid` int(10) unsigned NOT NULL,
  `name` text NOT NULL,
  `filename` varchar(128) NOT NULL,
  `sizebytes` bigint(20) unsigned NOT NULL default '0',
  `status` varchar(12) NOT NULL default 'done',
  `created` datetime default NULL,
  PRIMARY KEY  (`backupid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for tables `ticket` / `ticketpost`
--

CREATE TABLE IF NOT EXISTS `ticket` (
  `ticketid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `subject` text NOT NULL,
  `status` varchar(10) NOT NULL default 'open',
  `priority` varchar(8) NOT NULL default 'normal',
  `created` datetime default NULL,
  `updated` datetime default NULL,
  PRIMARY KEY  (`ticketid`),
  KEY `clientid` (`clientid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `ticketpost` (
  `postid` int(10) unsigned NOT NULL auto_increment,
  `ticketid` int(10) unsigned NOT NULL,
  `author` varchar(8) NOT NULL default 'client',
  `name` text NOT NULL,
  `body` text NOT NULL,
  `created` datetime default NULL,
  PRIMARY KEY  (`postid`),
  KEY `ticketid` (`ticketid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `serverstat` (player-count history for sparklines)
--

CREATE TABLE IF NOT EXISTS `serverstat` (
  `statid` int(10) unsigned NOT NULL auto_increment,
  `serverid` int(10) unsigned NOT NULL,
  `ts` datetime NOT NULL,
  `players` smallint(5) unsigned NOT NULL default '0',
  `maxplayers` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`statid`),
  KEY `srv_ts` (`serverid`,`ts`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `apikey`
--

CREATE TABLE IF NOT EXISTS `apikey` (
  `keyid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `label` text NOT NULL,
  `tokenhash` char(64) NOT NULL,
  `prefix` varchar(12) NOT NULL,
  `readonly` char(1) NOT NULL default '1',
  `lastused` datetime default NULL,
  `created` datetime default NULL,
  PRIMARY KEY  (`keyid`),
  KEY `tokenhash` (`tokenhash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `subuser` (server sharing)
--

CREATE TABLE IF NOT EXISTS `subuser` (
  `subid` int(10) unsigned NOT NULL auto_increment,
  `serverid` int(10) unsigned NOT NULL,
  `ownerid` int(10) unsigned NOT NULL,
  `subclientid` int(10) unsigned NOT NULL default '0',
  `subemail` text NOT NULL,
  `created` datetime default NULL,
  PRIMARY KEY  (`subid`),
  KEY `serverid` (`serverid`),
  KEY `subclientid` (`subclientid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `loginlog`
--

CREATE TABLE IF NOT EXISTS `loginlog` (
  `logid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `ip` varchar(45) NOT NULL,
  `agent` varchar(255) NOT NULL,
  `method` varchar(10) NOT NULL default 'password',
  `ts` datetime NOT NULL,
  PRIMARY KEY  (`logid`),
  KEY `clientid` (`clientid`,`ts`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `notification`
--

CREATE TABLE IF NOT EXISTS `notification` (
  `notifid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `kind` varchar(12) NOT NULL default 'system',
  `title` text NOT NULL,
  `body` text NOT NULL,
  `url` varchar(255) NOT NULL default '',
  `seen` char(1) NOT NULL default '0',
  `created` datetime default NULL,
  PRIMARY KEY  (`notifid`),
  KEY `client_seen` (`clientid`,`seen`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `config`
--

INSERT INTO `config` (`setting`, `value`) VALUES
('lastcronrun', 'Never'),
('panelname', 'Swift Panel'),
('systemurl', 'http://www.example.com/'),
('key', ''),
('panelversion', '1.6.1'),
('template', 'bootstrap'),
('country', 'US'),
('clientdb_enabled', '0'),
('clientdb_max', '2'),
('clientdb_maxsize', '200'),
('clientdb_host', '%'),
('clientdb_pma', '');

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

--
-- Table structure for table `emailtemp`
--

DROP TABLE IF EXISTS `emailtemp`;
CREATE TABLE IF NOT EXISTS `emailtemp` (
  `emailtempid` int(10) NOT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `bcc` text NOT NULL,
  `subject` text NOT NULL,
  `template` text NOT NULL,
  PRIMARY KEY  (`emailtempid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `emailtemp`
--

INSERT INTO `emailtemp` (`emailtempid`, `name`, `email`, `bcc`, `subject`, `template`) VALUES
(1, 'Your Company', 'user@example.com', '', 'Game Panel Account Information', 'Dear {firstname} {lastname},\r\n\r\nHere is your account login details:\r\n\r\nEmail Address: {email}\r\nPassword: {password}\r\n\r\nGame Panel Link: {clientarealink}\r\n\r\nYour Company\r\nwww.changeme.com');

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

--
-- Table structure for table `game`
--

DROP TABLE IF EXISTS `game`;
CREATE TABLE IF NOT EXISTS `game` (
  `gameid` int(10) unsigned NOT NULL auto_increment,
  `name` text NOT NULL,
  `game` text NOT NULL,
  `query` text NOT NULL,
  `priority` text NOT NULL,
  `slots` text NOT NULL,
  `type` text NOT NULL,
  `cfg1name` text NOT NULL,
  `cfg1` text NOT NULL,
  `cfg1edit` text NOT NULL,
  `cfg2name` text NOT NULL,
  `cfg2` text NOT NULL,
  `cfg2edit` text NOT NULL,
  `cfg3name` text NOT NULL,
  `cfg3` text NOT NULL,
  `cfg3edit` text NOT NULL,
  `cfg4name` text NOT NULL,
  `cfg4` text NOT NULL,
  `cfg4edit` text NOT NULL,
  `cfg5name` text NOT NULL,
  `cfg5` text NOT NULL,
  `cfg5edit` text NOT NULL,
  `cfg6name` text NOT NULL,
  `cfg6` text NOT NULL,
  `cfg6edit` text NOT NULL,
  `cfg7name` text NOT NULL,
  `cfg7` text NOT NULL,
  `cfg7edit` text NOT NULL,
  `cfg8name` text NOT NULL,
  `cfg8` text NOT NULL,
  `cfg8edit` text NOT NULL,
  `startline` text NOT NULL,
  `gamedir` text NOT NULL,
  `port` text NOT NULL,
  `status` text NOT NULL,
  `qryport` text NOT NULL,
  PRIMARY KEY  (`gameid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `game`
--

INSERT INTO `game` (`gameid`, `name`, `game`, `query`, `priority`, `slots`, `type`, `cfg1name`, `cfg1`, `cfg1edit`, `cfg2name`, `cfg2`, `cfg2edit`, `cfg3name`, `cfg3`, `cfg3edit`, `cfg4name`, `cfg4`, `cfg4edit`, `cfg5name`, `cfg5`, `cfg5edit`, `cfg6name`, `cfg6`, `cfg6edit`, `cfg7name`, `cfg7`, `cfg7edit`, `cfg8name`, `cfg8`, `cfg8edit`, `startline`, `gamedir`, `port`, `status`) VALUES
(1, 'CSS Server', 'Counter-Strike: Source', 'valve', 'None', '16', 'Public', 'Tick Rate', '66', '', 'FPS Max', '300', '', 'Map', 'de_dust2', '', 'Server Config', 'server.cfg', '', 'TV Enable', '1', '', 'TV Max Clients', '15', '', 'TV Port', '27020', '', '', '', '', './srcds_run -game cstrike -ip {ip} -port {port} -maxplayers {slots} -tickrate {cfg1} +fps_max {cfg2} +map {cfg3} +servercfgfile {cfg4} +tv_enable {cfg5} +tv_maxclients {cfg6} +tv_port {cfg7} -autoupdate', '/home/gamefiles/css', '27015', 'Active'),
(2, 'CS Server', 'Counter-Strike 1.6', 'valve', 'None', '16', 'Public', 'FPS Max', '300', '', 'Ping Boost', '0', '', 'Map', 'de_dust2', '', 'Server Config', 'server.cfg', '', '', '', '', '', '', '', '', '', '', '', '', '', './hlds_run -game cstrike +ip {ip} +port {port} +maxplayers {slots} +sys_ticrate {cfg1} -pingboost {cfg2} +map {cfg3} +servercfgfile {cfg4} -autoupdate', '/home/gamefiles/cs', '27015', 'Active'),
(3, 'DODS Server', 'Day of Defeat: Source', 'valve', 'None', '16', 'Public', 'Tick Rate', '66', '', 'FPS Max', '300', '', 'Map', 'dod_anzio', '', 'Server Config', 'server.cfg', '', '', '', '', '', '', '', '', '', '', '', '', '', 'cd orangebox && {nice} ./srcds_run -game dod -ip {ip} -port {port} -maxplayers {slots} -tickrate {cfg1} +fps_max {cfg2} +map {cfg3} +servercfgfile {cfg4} -autoupdate', '/home/gamefiles/dods', '27015', 'Active'),
(4, 'TF2 Server', 'Team Fortress 2', 'valve', 'None', '16', 'Public', 'Tick Rate', '66', '', 'FPS Max', '300', '', 'Map', 'ctf_well', '', 'Server Config', 'server.cfg', '', '', '', '', '', '', '', '', '', '', '', '', '', 'cd orangebox && {nice} ./srcds_run -game tf -ip {ip} -port {port} -maxplayers {slots} -tickrate {cfg1} +fps_max {cfg2} +map {cfg3} +servercfgfile {cfg4} -autoupdate', '/home/gamefiles/tf2', '27015', 'Active'),
(5, 'CZ Server', 'Counter-Strike: Condition Zero', 'valve', 'None', '16', 'Public', 'FPS Max', '300', '', 'Ping Boost', '0', '', 'Map', 'de_dust', '', 'Server Config', 'server.cfg', '', '', '', '', '', '', '', '', '', '', '', '', '', './hlds_run -game czero +ip {ip} +port {port} +maxplayers {slots} +sys_ticrate {cfg1} -pingboost {cfg2} +map {cfg3} +servercfgfile {cfg4} -autoupdate', '/home/gamefiles/czero', '27015', 'Active'),
(6, 'DOD Server', 'Day of Defeat', 'valve', 'None', '16', 'Public', 'FPS Max', '300', '', 'Ping Boost', '0', '', 'Map', 'dod_anzio', '', 'Server Config', 'server.cfg', '', '', '', '', '', '', '', '', '', '', '', '', '', './hlds_run -game dod +ip {ip} +port {port} +maxplayers {slots} +sys_ticrate {cfg1} -pingboost {cfg2} +map {cfg3} +servercfgfile {cfg4} -autoupdate', '/home/gamefiles/dod', '27015', 'Active'),
(7, 'HLTV', 'Half Life TV', 'valve', 'None', '50', 'Public', 'FPS Max', '100', '', 'Server Config', 'hltv.cfg', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', './hltv -ip {ip} -port {port} +maxclients {slots} -maxfps {cfg1} +exec {cfg2} -autoupdate', '/home/gamefiles/hltv', '27020', 'Active'),
(8, 'COD4 Server', 'Call of Duty 4', 'quake3', 'None', '24', 'Public', 'Server Config', 'server.cfg', '', 'Map', 'mp_carentan', '', 'Punkbuster', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', './cod4_lnxded +set dedicated 2 +set net_ip {ip} +set net_port {port} +set sv_maxclients {slots} +exec {cfg1} +map {cfg2} +set sv_punkbuster {cfg3}', '/home/gamefiles/cod4', '28960', 'Active');

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

--
-- Table structure for table `ip`
--

DROP TABLE IF EXISTS `ip`;
CREATE TABLE IF NOT EXISTS `ip` (
  `ipid` int(10) unsigned NOT NULL auto_increment,
  `boxid` int(10) unsigned NOT NULL,
  `ip` text NOT NULL,
  `usage` text NOT NULL,
  PRIMARY KEY  (`ipid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `ip`
--

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

--
-- Table structure for table `log`
--

DROP TABLE IF EXISTS `log`;
CREATE TABLE IF NOT EXISTS `log` (
  `logid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `serverid` int(10) unsigned NOT NULL,
  `boxid` int(10) unsigned NOT NULL,
  `message` text NOT NULL,
  `name` text NOT NULL,
  `ip` text NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`logid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `log`
--

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

--
-- Table structure for table `server`
--

DROP TABLE IF EXISTS `server`;
CREATE TABLE IF NOT EXISTS `server` (
  `serverid` int(10) unsigned NOT NULL auto_increment,
  `clientid` int(10) unsigned NOT NULL,
  `boxid` int(10) unsigned NOT NULL,
  `ipid` int(10) unsigned NOT NULL,
  `name` text NOT NULL,
  `game` text NOT NULL,
  `status` text NOT NULL,
  `query` text NOT NULL,
  `priority` text NOT NULL,
  `slots` text NOT NULL,
  `type` text NOT NULL,
  `cfg1name` text NOT NULL,
  `cfg1` text NOT NULL,
  `cfg1edit` text NOT NULL,
  `cfg2name` text NOT NULL,
  `cfg2` text NOT NULL,
  `cfg2edit` text NOT NULL,
  `cfg3name` text NOT NULL,
  `cfg3` text NOT NULL,
  `cfg3edit` text NOT NULL,
  `cfg4name` text NOT NULL,
  `cfg4` text NOT NULL,
  `cfg4edit` text NOT NULL,
  `cfg5name` text NOT NULL,
  `cfg5` text NOT NULL,
  `cfg5edit` text NOT NULL,
  `cfg6name` text NOT NULL,
  `cfg6` text NOT NULL,
  `cfg6edit` text NOT NULL,
  `cfg7name` text NOT NULL,
  `cfg7` text NOT NULL,
  `cfg7edit` text NOT NULL,
  `cfg8name` text NOT NULL,
  `cfg8` text NOT NULL,
  `cfg8edit` text NOT NULL,
  `startline` text NOT NULL,
  `showftp` text NOT NULL,
  `webftp` text NOT NULL,
  `user` text NOT NULL,
  `password` text NOT NULL,
  `homedir` text NOT NULL,
  `installdir` text NOT NULL,
  `configdir` text NOT NULL,
  `port` text NOT NULL,
  `online` text NOT NULL,
  `qryport` text NOT NULL,
  `fastdl` text NOT NULL,
  `disksize` bigint(20) unsigned NOT NULL default '0',
  `disktime` datetime default NULL,
  `downcount` tinyint(3) unsigned NOT NULL default '0',
  `downalert` datetime default NULL,
  PRIMARY KEY  (`serverid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `server`
--

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

--
-- Repair activity-log rows written by an older build that HTML-escaped the
-- whole console-log message (the "#N" link showed as raw text). No-op on a
-- fresh database. Safe to re-run.
--

UPDATE `log` SET `message` =
  REPLACE(REPLACE(REPLACE(REPLACE(`message`,
    '&lt;a href=', '<a href='),
    '"&gt;&#35;', '">#'),
    '&lt;/a&gt;', '</a>'),
    '&#35;', '#')
WHERE `message` LIKE '%onsole %&lt;a href=%';