database.sql

   1 CREATE TABLE IF NOT EXISTS dns_soa (
   2         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   3         origin VARCHAR(255) NOT NULL,
   4         ns VARCHAR(255) NOT NULL,
   5         mbox VARCHAR(255) NOT NULL,
   6         serial INT(10) NOT NULL DEFAULT '1',
   7         refresh INT(10) NOT NULL DEFAULT '28800',
   8         retry INT(10) NOT NULL DEFAULT '7200',
   9         expire INT(10) NOT NULL DEFAULT '604800',
  10         minimum INT(10) NOT NULL DEFAULT '86400',
  11         ttl INT(10) NOT NULL DEFAULT '86400',
  12         active boolean NOT NULL DEFAULT 1,
  13         UNIQUE KEY origin (origin)
  14 ) ENGINE=InnoDB;
  15
  16 CREATE TABLE IF NOT EXISTS dns_rr (
  17         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  18         zone INT(10) NOT NULL,
  19         name VARCHAR(255) NOT NULL,
  20         data VARCHAR(255) NOT NULL,
  21         aux INT(10) NOT NULL,
  22         ttl INT(10) NOT NULL DEFAULT '86400',
  23         type enum('A', 'AAAA', 'CNAME', 'MX', 'PTR', 'SRV', 'TXT', 'TLSA', 'NS', 'DS') DEFAULT NULL,
  24         active boolean NOT NULL DEFAULT 1,
  25         UNIQUE KEY dns_rr (zone, name, type, data)
  26 ) ENGINE=InnoDB;
  27
  28 CREATE TABLE IF NOT EXISTS dns_sec (
  29         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  30         zone INT(10) NOT NULL,
  31         type enum('ZSK','KSK') DEFAULT NULL,
  32         algo VARCHAR(255) NOT NULL,
  33         public TEXT,
  34         private TEXT,
  35         dsset TEXT,
  36         active boolean NOT NULL DEFAULT 1
  37 ) ENGINE=InnoDB;
  38
  39 CREATE TABLE IF NOT EXISTS dns_user (
  40         userID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  41         username VARCHAR(255) NOT NULL,
  42         email VARCHAR(255) NOT NULL,
  43         password VARCHAR(255) NOT NULL,
  44         reseller INT(10) NOT NULL DEFAULT '0',
  45         status INT(10) NOT NULL DEFAULT '0',
  46         UNIQUE KEY dns_user (username, email, password)
  47 ) ENGINE=InnoDB;
  48
  49 CREATE TABLE IF NOT EXISTS dns_soa_to_user (
  50         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  51         userID INT(10) NOT NULL,
  52         soaID INT(10) NOT NULL,
  53         UNIQUE KEY dns_soa_to_user (userID, soaID)
  54 ) ENGINE=InnoDB;
  55
  56 CREATE TABLE IF NOT EXISTS dns_options (
  57         optionID int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  58         optionName VARCHAR(255) NOT NULL DEFAULT '',
  59         optionValue MEDIUMTEXT,
  60         UNIQUE KEY optionName (optionName)
  61 ) ENGINE=InnoDB;
  62
  63 CREATE TABLE IF NOT EXISTS dns_api (
  64         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  65         userID INT(10) NOT NULL,
  66         apiKey VARCHAR(255) NOT NULL
  67 ) ENGINE=InnoDB;
  68
  69 CREATE TABLE IF NOT EXISTS dns_template (
  70         templateID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  71         userID INT(10) NOT NULL,
  72         template TEXT
  73 ) ENGINE=InnoDB;
  74
  75 CREATE TABLE IF NOT EXISTS dns_session (
  76         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  77         sessionID VARCHAR(255) NOT NULL DEFAULT '',
  78         expire INT(10) NOT NULL,
  79         sessionData TEXT
  80 ) ENGINE=InnoDB;
  81
  82 CREATE TABLE IF NOT EXISTS dns_permissions (
  83         permissionID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  84         permission VARCHAR(255) NOT NULL,
  85 ) ENGINE=InnoDB;
  86
  87 CREATE TABLE IF NOT EXISTS dns_permissions_to_user (
  88         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  89         userID INT(10) NOT NULL,
  90         permissionID VARCHAR(255) NOT NULL,
  91 ) ENGINE=InnoDB;
  92
  93
  94
  95 ALTER TABLE dns_api ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
  96 ALTER TABLE dns_sec ADD FOREIGN KEY (zone) REFERENCES dns_soa (id) ON DELETE CASCADE;
  97 ALTER TABLE dns_rr ADD FOREIGN KEY (zone) REFERENCES dns_soa (id) ON DELETE CASCADE;
  98 ALTER TABLE dns_soa_to_user ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
  99 ALTER TABLE dns_soa_to_user ADD FOREIGN KEY (soaID) REFERENCES dns_soa (id) ON DELETE CASCADE;
 100 ALTER TABLE dns_template ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
 101 ALTER TABLE dns_permissions_to_user ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
 102 ALTER TABLE dns_permissions_to_user ADD FOREIGN KEY (permissionID) REFERENCES dns_permissions (id) ON DELETE CASCADE;
 103
 104 INSERT INTO dns_options VALUES (1, 'dns_api_key', '0E2372C5-E5A3-424B-82E5-75AD723A9447');
 105 INSERT INTO dns_options VALUES (2, 'offline', '0');
 106 INSERT INTO dns_options VALUES (3, 'enable_debug', '1');
 107 INSERT INTO dns_options VALUES (4, 'dns_default_records', '{domain}:NS:ns1.stricted.de.\n{domain}:NS:ns2.stricted.de.\n{domain}:NS:ns3.stricted.de.\n{domain}:NS:ns4.stricted.de.\n{domain}:NS:ns5.stricted.de.\n{domain}:MX:mail.{domain}\n{domain}:A:84.200.248.52\n{domain}:AAAA:2001:1608:12:1::def\n*.{domain}:A:84.200.248.52\n*.{domain}:AAAA:2001:1608:12:1::def\n{domain}:TXT:\"v=spf1 mx -all\"');
 108 INSERT INTO dns_options VALUES (5, 'dns_soa_mbox', 'info.stricted.de.');
 109 INSERT INTO dns_options VALUES (6, 'dns_soa_ns', 'ns1.stricted.de.');
 110 INSERT INTO dns_options VALUES (7, 'dns_soa_ttl', '86400');
 111 INSERT INTO dns_options VALUES (8, 'dns_soa_refresh', '28800');
 112 INSERT INTO dns_options VALUES (9, 'dns_soa_retry', '7200');
 113 INSERT INTO dns_options VALUES (10, 'dns_soa_expire', '604800');
 114 INSERT INTO dns_options VALUES (11, 'dns_soa_minimum_ttl', '60');
 115 INSERT INTO dns_options VALUES (12, 'enable_dnssec', '1');
 116
 117
 118 INSERT INTO `dns_user` VALUES (1, 'admin', 'example@example.net', '$2a$08$XfcfTGc1LlmOHWUt/2sfNeFLEwqESy6wmrIIJMyQS1j5pwembqiae', '0', '2');