From f99b37f56dad8e3a1f844f8d3b33ee1ba4b86d62 Mon Sep 17 00:00:00 2001 From: latham <latham@ispconfig3> Date: Wed, 25 May 2011 19:11:40 -0400 Subject: [PATCH] the altertable for the groups --- install/sql/ispconfig3.sql | 256 ++++++++++++++++++++++++++++++++++++++++++++++---- 1 files changed, 233 insertions(+), 23 deletions(-) diff --git a/install/sql/ispconfig3.sql b/install/sql/ispconfig3.sql index 07919e3..07d0510 100644 --- a/install/sql/ispconfig3.sql +++ b/install/sql/ispconfig3.sql @@ -1,6 +1,6 @@ /* -Copyright (c) 2007-2009, Till Brehm, projektfarm Gmbh +Copyright (c) 2007-2010, Till Brehm, projektfarm Gmbh All rights reserved. Redistribution and use in source and binary forms, with or without modification, @@ -53,6 +53,8 @@ `sys_perm_other` varchar(5) default NULL, `company_name` varchar(64) default NULL, `contact_name` varchar(64) default NULL, + `customer_no` varchar(64) default NULL, + `vat_id` varchar(64) default NULL, `street` varchar(255) default NULL, `zip` varchar(32) default NULL, `city` varchar(64) default NULL, @@ -89,8 +91,10 @@ `limit_ftp_user` int(11) NOT NULL default '-1', `limit_shell_user` int(11) NOT NULL default '0', `ssh_chroot` varchar(255) NOT NULL DEFAULT 'no,jailkit,ssh-chroot', + `limit_webdav_user` int(11) NOT NULL default '0', `default_dnsserver` int(11) unsigned NOT NULL default '1', `limit_dns_zone` int(11) NOT NULL default '-1', + `limit_dns_slave_zone` int(11) NOT NULL default '-1', `limit_dns_record` int(11) NOT NULL default '-1', `default_dbserver` int(11) NOT NULL default '1', `limit_database` int(11) NOT NULL default '-1', @@ -99,6 +103,7 @@ `limit_cron_frequency` int(11) NOT NULL default '5', `limit_traffic_quota` int(11) NOT NULL default '-1', `limit_client` int(11) NOT NULL default '0', + `limit_mailmailinglist` int(11) NOT NULL default '-1', `parent_client_id` int(11) unsigned NOT NULL default '0', `username` varchar(64) default NULL, `password` varchar(64) default NULL, @@ -107,6 +112,8 @@ `template_master` int(11) unsigned NOT NULL default '0', `template_additional` varchar(255) NOT NULL default '', `created_at` bigint(20) DEFAULT NULL, + `id_rsa` VARCHAR( 2000 ) NOT NULL default '', + `ssh_rsa` VARCHAR( 600 ) NOT NULL default '', PRIMARY KEY (`client_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; @@ -145,7 +152,9 @@ `limit_web_aliasdomain` int(11) NOT NULL default '-1', `limit_ftp_user` int(11) NOT NULL default '-1', `limit_shell_user` int(11) NOT NULL default '0', + `limit_webdav_user` int(11) NOT NULL default '0', `limit_dns_zone` int(11) NOT NULL default '-1', + `limit_dns_slave_zone` int(11) NOT NULL default '-1', `limit_dns_record` int(11) NOT NULL default '-1', `limit_database` int(11) NOT NULL default '-1', `limit_cron` int(11) NOT NULL default '0', @@ -153,6 +162,7 @@ `limit_cron_frequency` int(11) NOT NULL default '5', `limit_traffic_quota` int(11) NOT NULL default '-1', `limit_client` int(11) NOT NULL default '0', + `limit_mailmailinglist` int(11) NOT NULL default '-1', PRIMARY KEY (`template_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; @@ -160,7 +170,7 @@ -- -------------------------------------------------------- -- --- Table structure for table `dns_rr` +-- Table structure for table `cron` -- CREATE TABLE `cron` ( `id` int(11) unsigned NOT NULL auto_increment, @@ -244,6 +254,29 @@ -- -------------------------------------------------------- +-- +-- Table structure for table `dns_slave` +-- + +CREATE TABLE `dns_slave` ( + `id` int(10) unsigned NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL, + `sys_groupid` int(11) unsigned NOT NULL, + `sys_perm_user` varchar(5) NOT NULL, + `sys_perm_group` varchar(5) NOT NULL, + `sys_perm_other` varchar(5) NOT NULL, + `server_id` int(11) NOT NULL default '1', + `origin` varchar(255) NOT NULL, + `ns` varchar(255) NOT NULL, + `active` enum('N','Y') NOT NULL, + `xfer` varchar(255) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `origin` (`origin`), + KEY `active` (`active`) +) ENGINE=MyISAM AUTO_INCREMENT=1; + +-- -------------------------------------------------------- + -- -- Table structure for table `dns_template` -- @@ -261,6 +294,22 @@ `visible` enum('N','Y') NOT NULL default 'Y', PRIMARY KEY (`template_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; + +-- +-- Tabellenstruktur für Tabelle `domain` +-- + +CREATE TABLE `domain` ( + `domain_id` int(11) unsigned NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL default '0', + `sys_groupid` int(11) unsigned NOT NULL default '0', + `sys_perm_user` varchar(5) NOT NULL default '', + `sys_perm_group` varchar(5) NOT NULL default '', + `sys_perm_other` varchar(5) NOT NULL default '', + `domain` varchar(255) NOT NULL default '', + PRIMARY KEY (`domain_id`), + UNIQUE KEY `domain` (`domain`) +) ENGINE=MyISAM; -- -------------------------------------------------------- @@ -396,7 +445,7 @@ `sys_perm_other` varchar(5) NOT NULL default '', `server_id` int(11) unsigned NOT NULL default '0', `source` varchar(255) NOT NULL, - `destination` text NOT NULL default '', + `destination` text, `type` enum('alias','aliasdomain','forward','catchall') NOT NULL default 'alias', `active` enum('n','y') NOT NULL, PRIMARY KEY (`forwarding_id`), @@ -447,19 +496,23 @@ -- -------------------------------------------------------- --- --- Table structure for table `mail_mailman_domain` --- +-- +-- Tabellenstruktur für Tabelle `mail_mailinglist` +-- -CREATE TABLE `mail_mailman_domain` ( - `mailman_id` int(11) unsigned NOT NULL auto_increment, +CREATE TABLE `mail_mailinglist` ( + `mailinglist_id` int(11) unsigned NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL default '0', + `sys_groupid` int(11) unsigned NOT NULL default '0', + `sys_perm_user` varchar(5) NOT NULL, + `sys_perm_group` varchar(5) NOT NULL, + `sys_perm_other` varchar(5) NOT NULL, `server_id` int(11) unsigned NOT NULL default '0', - `domain` varchar(255) NOT NULL default '', - `mm_home` varchar(255) NOT NULL default '', - `mm_wrap` varchar(255) NOT NULL default '', - `mm_user` varchar(50) NOT NULL default '', - `mm_group` varchar(50) NOT NULL default '', - PRIMARY KEY (`mailman_id`,`server_id`,`domain`) + `domain` varchar(255) NOT NULL, + `listname` varchar(255) NOT NULL, + `email` varchar(255) NOT NULL, + `password` varchar(255) NOT NULL, + PRIMARY KEY (`mailinglist_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; -- -------------------------------------------------------- @@ -536,12 +589,14 @@ `sys_perm_other` varchar(5) NOT NULL default '', `server_id` int(11) unsigned NOT NULL default '0', `email` varchar(255) NOT NULL default '', + `login` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `name` varchar(255) NOT NULL default '', `uid` int(11) unsigned NOT NULL default '5000', `gid` int(11) unsigned NOT NULL default '5000', `maildir` varchar(255) NOT NULL default '', `quota` bigint(20) NOT NULL default '-1', + `cc` varchar(255) NOT NULL default '', `homedir` varchar(255) NOT NULL, `autoresponder` enum('n','y') NOT NULL default 'n', `autoresponder_start_date` datetime NOT NULL default '0000-00-00 00:00:00', @@ -637,9 +692,12 @@ `file_server` tinyint(1) NOT NULL default '0', `db_server` tinyint(1) NOT NULL default '0', `vserver_server` tinyint(1) NOT NULL default '0', + `proxy_server` tinyint(1) NOT NULL default '0', + `firewall_server` tinyint(1) NOT NULL default '0', `config` text NOT NULL, `updated` bigint(20) unsigned NOT NULL default '0', `mirror_server_id` int(11) unsigned NOT NULL default '0', + `dbversion` int(11) unsigned NOT NULL default '1', `active` tinyint(1) NOT NULL default '1', PRIMARY KEY (`server_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; @@ -683,6 +741,7 @@ `shell` varchar(255) NOT NULL default '/bin/bash', `dir` varchar(255) default NULL, `chroot` varchar(255) NOT NULL, + `ssh_rsa` VARCHAR( 600 ) NOT NULL default '', PRIMARY KEY (`shell_user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; @@ -700,6 +759,11 @@ `package_description` text, `package_version` varchar(8) default NULL, `package_type` enum('ispconfig','app','web') NOT NULL default 'app', + `package_installable` enum('yes','no','key') NOT NULL default 'yes', + `package_requires_db` enum('no','mysql') NOT NULL default 'no', + `package_remote_functions` text, + `package_key` varchar(255) NOT NULL, + `package_config` text, PRIMARY KEY (`package_id`), UNIQUE KEY `package_name` (`package_name`) ) ENGINE=MyISAM AUTO_INCREMENT=1; @@ -896,8 +960,8 @@ `action` char(1) NOT NULL default '', `tstamp` int(11) NOT NULL default '0', `user` varchar(255) NOT NULL default '', - `data` text NOT NULL, - `status` set('pending','ok','warning','error') NOT NULL default 'pending', + `data` longtext NOT NULL, + `status` set('pending','ok','warning','error') NOT NULL default 'ok', PRIMARY KEY (`datalog_id`), KEY `server_id` (`server_id`,`status`) ) ENGINE=MyISAM AUTO_INCREMENT=1; @@ -1008,12 +1072,29 @@ `app_theme` varchar(32) NOT NULL default 'default', `typ` varchar(16) NOT NULL default 'user', `active` tinyint(1) NOT NULL default '1', - `language` varchar(2) NOT NULL default 'de', - `groups` varchar(255) NOT NULL default '', + `language` varchar(2) NOT NULL default 'en', + `groups` TEXT NOT NULL default '', `default_group` int(11) unsigned NOT NULL default '0', `client_id` int(11) unsigned NOT NULL default '0', + `id_rsa` VARCHAR( 2000 ) NOT NULL default '', + `ssh_rsa` VARCHAR( 600 ) NOT NULL default '', PRIMARY KEY (`userid`) ) ENGINE=MyISAM AUTO_INCREMENT=1; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `sys_session` +-- + +CREATE TABLE `sys_session` ( + `session_id` varchar(32) NOT NULL default '', + `date_created` datetime NOT NULL default '0000-00-00 00:00:00', + `last_updated` datetime NOT NULL default '0000-00-00 00:00:00', + `session_data` longtext, + PRIMARY KEY (`session_id`), + KEY `last_updated` (`last_updated`) +) ENGINE=MyISAM; -- -------------------------------------------------------- @@ -1046,6 +1127,7 @@ `is_subdomainwww` tinyint(1) NOT NULL default '1', `subdomain` enum('none','www','*') NOT NULL default 'none', `php` varchar(32) NOT NULL default 'y', + `ruby` enum('n','y') NOT NULL default 'n', `redirect_type` varchar(255) default NULL, `redirect_path` varchar(255) default NULL, `ssl` enum('n','y') NOT NULL default 'n', @@ -1060,13 +1142,20 @@ `ssl_bundle` mediumtext NULL, `ssl_action` varchar(16) NULL, `stats_password` varchar(255) default NULL, + `stats_type` varchar(255) default 'webalizer', `allow_override` varchar(255) NOT NULL default 'All', - `apache_directives` text, - `php_open_basedir` text, + `apache_directives` mediumtext, + `nginx_directives` mediumtext, + `php_open_basedir` mediumtext, + `custom_php_ini` mediumtext, + `backup_interval` VARCHAR( 255 ) NOT NULL DEFAULT 'none', + `backup_copies` INT NOT NULL DEFAULT '1', `active` enum('n','y') NOT NULL default 'y', `traffic_quota_lock` enum('n','y') NOT NULL default 'n', PRIMARY KEY (`domain_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1; + + -- -------------------------------------------------------- @@ -1105,6 +1194,29 @@ `traffic_date` date NOT NULL, `traffic_bytes` bigint(32) unsigned NOT NULL default '0', PRIMARY KEY (`hostname`,`traffic_date`) +) ENGINE=MyISAM; + + +-- -------------------------------------------------------- + +-- +-- Table structure for table `webdav_user` +-- + +CREATE TABLE `webdav_user` ( + `webdav_user_id` int(11) unsigned NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL default '0', + `sys_groupid` int(11) unsigned NOT NULL default '0', + `sys_perm_user` varchar(5) default NULL, + `sys_perm_group` varchar(5) default NULL, + `sys_perm_other` varchar(5) default NULL, + `server_id` int(11) unsigned NOT NULL default '0', + `parent_domain_id` int(11) unsigned NOT NULL default '0', + `username` varchar(64) default NULL, + `password` varchar(64) default NULL, + `active` enum('n','y') NOT NULL default 'y', + `dir` varchar(255) default NULL, + PRIMARY KEY (`webdav_user_id`) ) ENGINE=MyISAM; @@ -1204,7 +1316,7 @@ -- Dumping data for table `dns_template` -- -INSERT INTO `dns_template` (`template_id`, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `name`, `fields`, `template`, `visible`) VALUES (1, 1, 1, 'riud', 'riud', '', 'Default', 'DOMAIN,IP,NS1,NS2,EMAIL', '[ZONE]\norigin={DOMAIN}.\nns={NS1}.\nmbox={EMAIL}.\nrefresh=28800\nretry=7200\nexpire=604800\nminimum=86400\nttl=86400\n\n[DNS_RECORDS]\nA|{DOMAIN}.|{IP}|0|86400\nA|www|{IP}|0|86400\nA|mail|{IP}|0|86400\nNS|{DOMAIN}.|{NS1}.|0|86400\nNS|{DOMAIN}.|{NS2}.|0|86400\nMX|{DOMAIN}.|mail.{DOMAIN}.|10|86400', 'y'); +INSERT INTO `dns_template` (`template_id`, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `name`, `fields`, `template`, `visible`) VALUES (1, 1, 1, 'riud', 'riud', '', 'Default', 'DOMAIN,IP,NS1,NS2,EMAIL', '[ZONE]\norigin={DOMAIN}.\nns={NS1}.\nmbox={EMAIL}.\nrefresh=7200\nretry=540\nexpire=604800\nminimum=86400\nttl=3600\n\n[DNS_RECORDS]\nA|{DOMAIN}.|{IP}|0|3600\nA|www|{IP}|0|3600\nA|mail|{IP}|0|3600\nNS|{DOMAIN}.|{NS1}.|0|3600\nNS|{DOMAIN}.|{NS2}.|0|3600\nMX|{DOMAIN}.|mail.{DOMAIN}.|10|3600', 'y'); -- -------------------------------------------------------- @@ -1250,7 +1362,7 @@ -- Dumping data for table `sys_user` -- -INSERT INTO `sys_user` (`userid`, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `username`, `passwort`, `modules`, `startmodule`, `app_theme`, `typ`, `active`, `language`, `groups`, `default_group`, `client_id`) VALUES (1, 1, 0, 'riud', 'riud', '', 'admin', '21232f297a57a5a743894a0e4a801fc3', 'admin,client,mail,monitor,sites,dns,tools,help', 'mail', 'default', 'admin', 1, 'en', '1,2', 1, 0); +INSERT INTO `sys_user` (`userid`, `sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `username`, `passwort`, `modules`, `startmodule`, `app_theme`, `typ`, `active`, `language`, `groups`, `default_group`, `client_id`) VALUES (1, 1, 0, 'riud', 'riud', '', 'admin', '21232f297a57a5a743894a0e4a801fc3', 'dashboard,admin,client,mail,monitor,sites,dns,tools,help', 'dashboard', 'default', 'admin', 1, 'en', '1,2', 1, 0); -- -------------------------------------------------------- @@ -1258,7 +1370,7 @@ -- Dumping data for table `sys_config` -- -INSERT INTO sys_config VALUES ('1','db','db_version','3.0.2.1'); +INSERT INTO sys_config VALUES ('1','db','db_version','3.0.3.3'); -- -------------------------------------------------------- @@ -1521,6 +1633,104 @@ ('ZW', 'ZIMBABWE', 'Zimbabwe', 'ZWE', 716); -- -------------------------------------------------------- +-- tables for the FAQ module + +CREATE TABLE `help_faq_sections` ( + `hfs_id` int(11) NOT NULL AUTO_INCREMENT, + `hfs_name` varchar(255) DEFAULT NULL, + `hfs_order` int(11) DEFAULT '0', + `sys_userid` int(11) DEFAULT NULL, + `sys_groupid` int(11) DEFAULT NULL, + `sys_perm_user` varchar(5) DEFAULT NULL, + `sys_perm_group` varchar(5) DEFAULT NULL, + `sys_perm_other` varchar(5) DEFAULT NULL, + PRIMARY KEY (`hfs_id`) +) ENGINE=MyISAM AUTO_INCREMENT=1; + +INSERT INTO `help_faq_sections` VALUES (1,'General',0,NULL,NULL,NULL,NULL,NULL); + +CREATE TABLE `help_faq` ( + `hf_id` int(11) NOT NULL AUTO_INCREMENT, + `hf_section` int(11) DEFAULT NULL, + `hf_order` int(11) DEFAULT '0', + `hf_question` text, + `hf_answer` text, + `sys_userid` int(11) DEFAULT NULL, + `sys_groupid` int(11) DEFAULT NULL, + `sys_perm_user` varchar(5) DEFAULT NULL, + `sys_perm_group` varchar(5) DEFAULT NULL, + `sys_perm_other` varchar(5) DEFAULT NULL, + PRIMARY KEY (`hf_id`) +) ENGINE=MyISAM AUTO_INCREMENT=1; + +INSERT INTO `help_faq` VALUES (1,1,0,'I\'d like to know ...','Yes, of course.',1,1,'riud','riud','r'); + +ALTER TABLE client ADD COLUMN company_id varchar(30); + + +CREATE TABLE `proxy_reverse` ( + `rewrite_id` int(11) NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL default '0', + `sys_groupid` int(11) unsigned NOT NULL default '0', + `sys_perm_user` varchar(5) default NULL, + `sys_perm_group` varchar(5) default NULL, + `sys_perm_other` varchar(5) default NULL, + `server_id` int(11) unsigned NOT NULL default '0', + `rewrite_url_src` varchar(100) NOT NULL, + `rewrite_url_dst` varchar(100) NOT NULL, + `active` enum('n','y') NOT NULL default 'y', + PRIMARY KEY (`rewrite_id`) +) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; + + +CREATE TABLE `firewall_filter` ( + `firewall_id` int(11) unsigned NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL default '0', + `domain_id` int(11) NOT NULL, + `sys_groupid` int(11) unsigned NOT NULL default '0', + `sys_perm_user` varchar(5) default NULL, + `sys_perm_group` varchar(5) default NULL, + `sys_perm_other` varchar(5) default NULL, + `server_id` int(11) unsigned NOT NULL default '0', + `rule_name` varchar(100) default NULL, + `rule_id` int(11) default 1, + `src_ip` varchar(20) NOT NULL, + `src_netmask` varchar(20) NOT NULL, + `dst_ip` varchar(20) NOT NULL, + `dst_netmask` varchar(20) NOT NULL, + `src_from_port` varchar(10) NOT NULL, + `src_to_port` varchar(10) NOT NULL, + `dst_to_port` varchar(10) NOT NULL, + `dst_from_port` varchar(10) NOT NULL, + `protocol` varchar(10) default 'tcp', + `inbound_policy` enum('allow','deny','reject','limit') default 'allow', + `outbound_policy` enum('allow','deny','reject','limit') default 'allow', + `active` enum('n','y') NOT NULL default 'y', + `client_id` int(11) NOT NULL, + PRIMARY KEY (`firewall_id`) +) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; + +CREATE TABLE `firewall_forward` ( + `firewall_id` int(11) unsigned NOT NULL auto_increment, + `sys_userid` int(11) unsigned NOT NULL default '0', + `domain_id` int(11) NOT NULL, + `sys_groupid` int(11) unsigned NOT NULL default '0', + `sys_perm_user` varchar(5) default NULL, + `sys_perm_group` varchar(5) default NULL, + `sys_perm_other` varchar(5) default NULL, + `server_id` int(11) unsigned NOT NULL default '0', + `application_name` varchar(100) default NULL, + `dst_ip` varchar(20) NOT NULL, + `src_from_port` varchar(10) NOT NULL, + `src_to_port` varchar(10) NOT NULL, + `dst_to_port` varchar(10) NOT NULL, + `dst_from_port` varchar(10) NOT NULL, + `protocol` int(3) default 0, + `active` enum('n','y') NOT NULL default 'y', + `client_id` int(11) NOT NULL, + PRIMARY KEY (`firewall_id`) +) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; + -- -------------------------------------------------------- SET FOREIGN_KEY_CHECKS = 1; -- Gitblit v1.9.1