pgsql.sql
5.24 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
START TRANSACTION;
SET standard_conforming_strings=off;
SET escape_string_warning=off;
SET CONSTRAINTS ALL DEFERRED;
CREATE TABLE "dnssec_keys" (
"id" integer NOT NULL,
"dszone" integer NOT NULL,
"filename" varchar(100) NOT NULL,
"fkey" text ,
"fprivate" text ,
"archive" varchar(6) DEFAULT NULL,
"refresh" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE ("id")
);
CREATE TABLE "dnssec_zones" (
"id" integer NOT NULL,
"zone" integer NOT NULL,
"krf" text NOT NULL,
"dsset" text NOT NULL,
UNIQUE ("id")
);
CREATE TABLE "options" (
"prefkey" varchar(40) NOT NULL,
"preftype" varchar(12) NOT NULL DEFAULT '',
"prefval" varchar(510) DEFAULT NULL,
UNIQUE ("prefkey")
);
INSERT INTO "options" VALUES ('A','record','on'),('A6','record','off'),('AAAA','record','off'),('AFSDB','record','off'),('APL','record','off'),('ATMA','record','off'),('AXFR','record','off'),('CAA','record','off'),('CERT','record','off'),('CNAME','record','on'),('DNAME','record','off'),('DNSKEY','record','off'),('DS','record','off'),('EID','record','off'),('GPOS','record','off'),('HINFO','record','off'),('hostmaster','normal','postmaster.your.ns'),('ISDN','record','off'),('IXFR','record','off'),('KEY','record','off'),('KX','record','off'),('LOC','record','off'),('MAILB','record','off'),('master','normal','0.0.0.0'),('MINFO','record','off'),('MX','record','on'),('NAPTR','record','off'),('NIMLOC','record','off'),('NS','record','on'),('NSAP','record','off'),('NSAP-PTR','record','off'),('NSEC','record','off'),('NXT','record','off'),('OPT','record','off'),('prins','normal','your.master.ns'),('PTR','record','off'),('PX','record','off'),('range','normal','10'),('RP','record','off'),('RRSIG','record','off'),('RT','record','off'),('secns','normal','your.sec.ns'),('SIG','record','off'),('SINK','record','off'),('SRV','record','on'),('SSHFP','record','off'),('TKEY','record','off'),('TSIG','record','off'),('TXT','record','on'),('WKS','record','off'),('X25','record','off');
CREATE TABLE "records" (
"id" integer NOT NULL,
"zone" integer NOT NULL DEFAULT '0',
"host" varchar(256) NOT NULL,
"ttl" integer DEFAULT NULL,
"type" varchar(16) NOT NULL,
"pri" integer NOT NULL DEFAULT '0',
"destination" varchar(8192) DEFAULT NULL,
UNIQUE ("id")
);
CREATE TABLE "slave_zones" (
"id" integer NOT NULL,
"name" varchar(256) NOT NULL,
"master" varchar(256) DEFAULT NULL,
"owner" integer NOT NULL DEFAULT '0',
"updated" varchar(6) NOT NULL DEFAULT 'yes',
"valid" varchar(6) NOT NULL DEFAULT 'may',
UNIQUE ("id"),
UNIQUE ("name")
);
CREATE TABLE "users" (
"id" integer NOT NULL,
"username" varchar(64) NOT NULL,
"realname" varchar(100) DEFAULT NULL,
"password" varchar(64) NOT NULL,
"admin" varchar(6) NOT NULL DEFAULT 'no',
UNIQUE ("id"),
UNIQUE ("username")
);
INSERT INTO "users" VALUES (1,'admin','Administrator','3c99cbdb5c15684e4fc190f4f17e443c','yes');
CREATE TABLE "zones" (
"id" integer NOT NULL,
"name" varchar(128) NOT NULL,
"pri_dns" varchar(256) DEFAULT NULL,
"sec_dns" varchar(256) DEFAULT NULL,
"serial" integer NOT NULL DEFAULT '0',
"refresh" integer NOT NULL DEFAULT '604800',
"retry" integer NOT NULL DEFAULT '86400',
"expire" integer NOT NULL DEFAULT '2419200',
"ttl" integer NOT NULL DEFAULT '604800',
"owner" integer NOT NULL DEFAULT '1',
"valid" varchar(6) NOT NULL DEFAULT 'may',
"updated" varchar(6) NOT NULL DEFAULT 'yes',
"secured" varchar(6) NOT NULL DEFAULT 'no',
UNIQUE ("id"),
UNIQUE ("name")
);
-- Post-data save --
COMMIT;
START TRANSACTION;
-- Typecasts --
-- Foreign keys --
ALTER TABLE "dnssec_keys" ADD CONSTRAINT "fkdskeys" FOREIGN KEY ("dszone") REFERENCES "dnssec_zones" ("id") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX ON "dnssec_keys" ("dszone");
ALTER TABLE "dnssec_zones" ADD CONSTRAINT "fkdszones" FOREIGN KEY ("zone") REFERENCES "zones" ("id") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX ON "dnssec_zones" ("zone");
ALTER TABLE "records" ADD CONSTRAINT "fkrecords" FOREIGN KEY ("zone") REFERENCES "zones" ("id") ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX ON "records" ("zone");
-- Sequences --
CREATE SEQUENCE dnssec_keys_id_seq;
SELECT setval('dnssec_keys_id_seq', max(id)) FROM dnssec_keys;
ALTER TABLE "dnssec_keys" ALTER COLUMN "id" SET DEFAULT nextval('dnssec_keys_id_seq');
CREATE SEQUENCE dnssec_zones_id_seq;
SELECT setval('dnssec_zones_id_seq', max(id)) FROM dnssec_zones;
ALTER TABLE "dnssec_zones" ALTER COLUMN "id" SET DEFAULT nextval('dnssec_zones_id_seq');
CREATE SEQUENCE records_id_seq;
SELECT setval('records_id_seq', max(id)) FROM records;
ALTER TABLE "records" ALTER COLUMN "id" SET DEFAULT nextval('records_id_seq');
CREATE SEQUENCE slave_zones_id_seq;
SELECT setval('slave_zones_id_seq', max(id)) FROM slave_zones;
ALTER TABLE "slave_zones" ALTER COLUMN "id" SET DEFAULT nextval('slave_zones_id_seq');
CREATE SEQUENCE users_id_seq;
SELECT setval('users_id_seq', max(id)) FROM users;
ALTER TABLE "users" ALTER COLUMN "id" SET DEFAULT nextval('users_id_seq');
CREATE SEQUENCE zones_id_seq;
SELECT setval('zones_id_seq', max(id)) FROM zones;
ALTER TABLE "zones" ALTER COLUMN "id" SET DEFAULT nextval('zones_id_seq');
-- Full Text keys --
COMMIT;