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
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
drop table if exists net;
drop table if exists asn;
drop table if exists resource_class;
drop table if exists registrant;
CREATE TABLE asn (
asn_id SERIAL NOT NULL,
start_as BIGINT unsigned NOT NULL,
end_as BIGINT unsigned NOT NULL,
resource_class_id BIGINT unsigned NOT NULL
);
CREATE UNIQUE INDEX XPKasn ON asn
(
asn_id
);
ALTER TABLE asn
ADD PRIMARY KEY (asn_id);
CREATE TABLE net (
net_id SERIAL NOT NULL,
start_ip VARCHAR(40) NOT NULL,
end_ip VARCHAR(40) NOT NULL,
version TINYINT unsigned NOT NULL,
resource_class_id BIGINT unsigned NOT NULL
);
CREATE UNIQUE INDEX XPKnet ON net
(
net_id
);
ALTER TABLE net
ADD PRIMARY KEY (net_id);
CREATE TABLE registrant (
registrant_id SERIAL NOT NULL,
IRBE_mapped_id TEXT
);
CREATE UNIQUE INDEX XPKregistrant ON registrant
(
registrant_id
);
ALTER TABLE registrant
ADD PRIMARY KEY (registrant_id);
CREATE TABLE resource_class (
resource_class_id SERIAL NOT NULL,
subject_name TEXT,
valid_until DATETIME NOT NULL,
registrant_id BIGINT unsigned NOT NULL
);
CREATE UNIQUE INDEX XPKresource_class ON resource_class
(
resource_class_id
);
ALTER TABLE resource_class
ADD PRIMARY KEY (resource_class_id);
ALTER TABLE asn
ADD FOREIGN KEY (resource_class_id)
REFERENCES resource_class
ON DELETE SET NULL
ON UPDATE SET NULL;
ALTER TABLE net
ADD FOREIGN KEY (resource_class_id)
REFERENCES resource_class
ON DELETE SET NULL
ON UPDATE SET NULL;
ALTER TABLE resource_class
ADD FOREIGN KEY (registrant_id)
REFERENCES registrant
ON DELETE SET NULL
ON UPDATE SET NULL;
insert into registrant (IRBE_mapped_id) values ('ARIN');
insert into registrant (IRBE_mapped_id) values ('TIER1_ISP1');
insert into registrant (IRBE_mapped_id) values ('TIER1_ISP2');
insert into registrant (IRBE_mapped_id) values ('JOES_PIZZA');
insert into resource_class (subject_name, valid_until, registrant_id)
select 'All ARIN resources', '2099-12-31', registrant_id from registrant
where IRBE_mapped_id = 'ARIN';
insert into resource_class (subject_name, valid_until, registrant_id)
select 'Tier 1 ISP foo subject name', '2008-12-31', registrant_id from registrant
where IRBE_mapped_id = 'TIER1_ISP1';
insert into resource_class (subject_name, valid_until, registrant_id)
select 'Tier 1 ISP foo subject name', '2009-06-30', registrant_id from registrant
where IRBE_mapped_id = 'TIER1_ISP1';
insert into resource_class (subject_name, valid_until, registrant_id)
select 'Tier 1 ISP bar subject name', '2007-07-31', registrant_id from registrant
where IRBE_mapped_id = 'TIER1_ISP2';
insert into resource_class (subject_name, valid_until, registrant_id)
select 'arbitrary characters', '2007-12-31', registrant_id from registrant
where IRBE_mapped_id = 'JOES_PIZZA';
insert into net (start_ip, end_ip, version, resource_class_id)
select 'DEAD:BEEF:0000:0000:0000:0000:0000:0000','DFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF',6,resource_class_id from resource_class where subject_name = 'All ARIN resources';
insert into net (start_ip, end_ip, version, resource_class_id)
select 'DEAD:BEEF:FACE:0000:0000:0000:0000:0000','DEAD:BEEF:FACE:FFFF:FFFF:FFFF:FFFF:FFFF',6,resource_class_id from resource_class where subject_name = 'TIER 1 ISP foo subject name' and valid_until = '2009-06-30';
insert into net (start_ip, end_ip, version, resource_class_id)
select 'DEAD:BEEF:FACE:FADE:0000:0000:0000:0000','DEAD:BEEF:FACE:FADE:FFFF:FFFF:FFFF:FFFF',6,resource_class_id from resource_class where subject_name = 'arbitrary characters' and valid_until = '2007-12-31';
insert into net(start_ip, end_ip, version, resource_class_id)
select
'010.000.000.000','010.255.255.255',4,resource_class_id from resource_class where subject_name = 'All ARIN resources';
insert into net(start_ip, end_ip, version, resource_class_id)
select
'010.128.000.000','010.191.255.255',4,resource_class_id from resource_class where subject_name = 'Tier 1 ISP foo subject name' and valid_until = '2009-06-30';
insert into net(start_ip, end_ip, version, resource_class_id)
select
'010.000.000.000','010.063.255.255',4,resource_class_id from resource_class where subject_name = 'Tier 1 ISP foo subject name' and valid_until = '2009-06-30';
insert into net(start_ip, end_ip, version, resource_class_id)
select
'010.128.000.000','010.191.255.255',4,resource_class_id from resource_class where subject_name = 'arbitrary characters';
insert into asn(start_as, end_as, resource_class_id)
select 12345, 12345, resource_class_id from resource_class where subject_name = 'Tier 1 ISP foo subject name' and valid_until = '2009-06-30';
insert into asn(start_as, end_as, resource_class_id)
select 23456, 23457, resource_class_id from resource_class where subject_name = 'Tier 1 ISP foo subject name' and valid_until = '2009-06-30';
insert into asn(start_as, end_as, resource_class_id)
select 34567, 34567, resource_class_id from resource_class where subject_name = 'Tier 1 ISP foo subject name' and valid_until = '2008-12-31';
|
