aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorRob Austein <sra@hactrn.net>2007-08-23 20:37:11 +0000
committerRob Austein <sra@hactrn.net>2007-08-23 20:37:11 +0000
commite03d348bdc8e095f3fe5c406c73650d817d68be8 (patch)
treec2f4db2a08e2ecf492f6af5316473d350f4604e0
parent1e4974c0191563b9ffd306446983edf84da9558b (diff)
SQL for a demo IRDB, from Tim (thanks!)
svn path=/docs/README; revision=901
Diffstat
-rw-r--r--docs/README35
-rw-r--r--docs/sample-irbe.pdf111
-rw-r--r--docs/sample-irbe.sql141
3 files changed, 287 insertions, 0 deletions
diff --git a/docs/README b/docs/README
new file mode 100644
index 00000000..646076aa
--- /dev/null
+++ b/docs/README
@@ -0,0 +1,35 @@
+$Id$
+
+Ok, there's getting to be enough stuff in this directory to be
+confusing.
+
+README This file
+
+entity-decompose.pdf An overview presentation
+
+images Some old pictures
+
+left-right-protocol Pseudo-code for left-right protocol
+
+left-right-xml XML samples of left-right protocol
+
+presentations More presentations
+
+publication-protocol Pseudo-code for publication protocol
+
+repository-engine-objects.dot Objects in the RPKI engine (PDF)
+repository-engine-objects.pdf Graphviz source for "
+
+repository-structure.txt Old notes on repository structure
+
+rpki-db-schema.hacked.sql Fork of rpki-db-schema, to be deleted RSN
+
+rpki-db-schema.pdf Current working draft of SQL objects
+rpki-db-schema.sql in the RPKI engine
+
+sample-irbe.pdf A toy database representing the IR back end
+sample-irbe.sql database
+
+signed-manifests ASN.1 for signed manifests
+
+up-down-protocol Pointer to up-down protocol specification
diff --git a/docs/sample-irbe.pdf b/docs/sample-irbe.pdf
new file mode 100644
index 00000000..ab86a43a
--- /dev/null
+++ b/docs/sample-irbe.pdf
@@ -0,0 +1,111 @@
+%PDF-1.3
+%쏢
+5 0 obj
+<</Length 6 0 R/Filter /FlateDecode>>
+stream
+xXmo7 ~B"]0 ]nE:1tH!úMa~t'ɱ&^\`-l<!Exxq?Es5K%^'fTh69mxF50!eo NڰEsAݎo'ݎ쥳B2d{d.Q>͎L[#
+ER'ٟx\-$QvP}KHKK\pU\Vn]BxdӜȧJ4{t6:]z;y,۱ $͍j2>N2dIf*.W2kf\d;M9PaMW 6ǛEaH8kNQ~^ $h0p95ڒ&`g|(K_V#vUKFdG1ћDŽE?ŹA"
++#B,=rL^4M+V;r-r%Ոә篑[!"skЉbLqfG<*tҭjwhut"1ѣ4)z‡ ʈ~:*eJ<WƝhe?6SkUfkl (%x|d AX˰02l6aۮh<T5' A-x<AaB&_QJQŕ!>kJqiܕV؞plXjfv[$s{@Sm%0+xgwB6UcOZj>{[̉nA\0";y0.L30uN,9wh]2;N/ 1zdq\cl/?KZ[ugH:[<-K}R('&Q4?x]:o=|H98 i&Etuf(|+}@Ě'/Jӥ+:?蔶I.G/Ut)0ޛuTtU$TMYd QR!q"dnjb:h]B.Є\4Rox!.Ř"iSr֙G+sv+uQ]}H
+.{~SHe4^q+¢/<1cVxf"$RS:"Mj˄LQ%t{Q%@/.荶dT hxD6Mr> MUZz.Dǵt/W0*=hN:AY()5iA5u=MlQO/׬bwRמtXYUTa*gT*9Ouiޜ'E T
+bt)d$FO٘#iYj-<a(8n7EL$GQ!3 l̉_^%ְM[Җ<7]).}nkSY9yQIW8WI_7jendstream
+endobj
+6 0 obj
+1519
+endobj
+4 0 obj
+<</Type/Page/MediaBox [0 0 612 792]
+/Rotate 0/Parent 3 0 R
+/Resources<</ProcSet[/PDF /Text]
+/ColorSpace 11 0 R
+/Pattern 12 0 R
+/Font 13 0 R
+>>
+/Contents 5 0 R
+>>
+endobj
+3 0 obj
+<< /Type /Pages /Kids [
+4 0 R
+] /Count 1
+/Rotate 0>>
+endobj
+1 0 obj
+<</Type /Catalog /Pages 3 0 R
+>>
+endobj
+10 0 obj
+[/Pattern]
+endobj
+11 0 obj
+<</R10
+10 0 R>>
+endobj
+12 0 obj
+<</R9
+9 0 R>>
+endobj
+9 0 obj
+<</Filter/FlateDecode
+/Type/Pattern
+/PatternType 1
+/PaintType 1
+/TilingType 1
+/BBox [0 0 1 1]
+/Matrix[1.91953
+0
+0
+-1.91953
+0.03
+0.03]
+/XStep 1
+/YStep 1
+/Resources<</ProcSet [/PDF/ImageB]>>/Length 99>>stream
+x-M@0sog]$b
+rJ<2ɗK+}`঍p6p`6Qf<oxg/\[PK'T:ܲ
+endstream
+endobj
+13 0 obj
+<</R8
+8 0 R/R7
+7 0 R>>
+endobj
+8 0 obj
+<</BaseFont/Courier/Type/Font
+/Subtype/Type1>>
+endobj
+7 0 obj
+<</BaseFont/Helvetica/Type/Font
+/Subtype/Type1>>
+endobj
+2 0 obj
+<</Producer(AFPL Ghostscript 8.53)
+/CreationDate(D:20070823150926-04'00')
+/ModDate(D:20070823150926-04'00')
+/Title(irbe)
+/Creator(PDFCreator Version 0.9.0)
+/Author(timc)
+/Keywords()
+/Subject()>>endobj
+xref
+0 14
+0000000000 65535 f
+0000001869 00000 n
+0000002503 00000 n
+0000001801 00000 n
+0000001624 00000 n
+0000000015 00000 n
+0000001604 00000 n
+0000002439 00000 n
+0000002377 00000 n
+0000002006 00000 n
+0000001917 00000 n
+0000001944 00000 n
+0000001976 00000 n
+0000002338 00000 n
+trailer
+<< /Size 14 /Root 1 0 R /Info 2 0 R
+/ID [<1E39B40BCE32F837B778887451D281AB><1E39B40BCE32F837B778887451D281AB>]
+>>
+startxref
+2712
+%%EOF
diff --git a/docs/sample-irbe.sql b/docs/sample-irbe.sql
new file mode 100644
index 00000000..7cc75d30
--- /dev/null
+++ b/docs/sample-irbe.sql
@@ -0,0 +1,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';
+
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-16 11:47:51 +0000