Changeset 74 for trunk/cgi-bin


Ignore:
Timestamp:
11/16/04 15:10:08 (20 years ago)
Author:
Kris Deugau
Message:

/trunk

Added a CircuitID field on both netblock and static IP
Updated SQL table defs
Fixed nitpicky display bug in delete confirmation HTML

Location:
trunk/cgi-bin
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/cgi-bin/ipdb.psql

    r57 r74  
    2323
    2424--
    25 -- TOC Entry ID 2 (OID 25848)
    26 --
    27 -- Name: allocations Type: TABLE Owner: ipdb
    28 --
    29 
    30 CREATE TABLE "allocations" (
    31         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
    32         "custid" character varying(16) DEFAULT '',
    33         "type" character(2) DEFAULT '',
    34         "city" character varying(30) DEFAULT '',
    35         "description" character varying(64) DEFAULT '',
    36         "notes" text,
    37         "maskbits" integer DEFAULT 128,
    38         Constraint "allocations_pkey" Primary Key ("cidr")
    39 );
    40 
    41 --
    42 -- TOC Entry ID 3 (OID 25848)
    43 --
    44 -- Name: allocations Type: ACL Owner:
    45 --
    46 
    47 REVOKE ALL on "allocations" from PUBLIC;
    48 GRANT ALL on "allocations" to "kdeugau";
    49 GRANT ALL on "allocations" to "ipdb";
    50 
    51 --
    52 -- TOC Entry ID 4 (OID 25854)
     25-- TOC Entry ID 2 (OID 25854)
    5326--
    5427-- Name: customers Type: TABLE Owner: ipdb
     
    7144
    7245--
    73 -- TOC Entry ID 5 (OID 25854)
     46-- TOC Entry ID 3 (OID 25854)
    7447--
    7548-- Name: customers Type: ACL Owner:
     
    8154
    8255--
    83 -- TOC Entry ID 6 (OID 25866)
     56-- TOC Entry ID 4 (OID 25872)
     57--
     58-- Name: masterblocks Type: TABLE Owner: ipdb
     59--
     60
     61CREATE TABLE "masterblocks" (
     62        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
     63        Constraint "masterblocks_pkey" Primary Key ("cidr")
     64);
     65
     66--
     67-- TOC Entry ID 5 (OID 25872)
     68--
     69-- Name: masterblocks Type: ACL Owner:
     70--
     71
     72REVOKE ALL on "masterblocks" from PUBLIC;
     73GRANT ALL on "masterblocks" to "kdeugau";
     74GRANT ALL on "masterblocks" to "ipdb";
     75
     76--
     77-- TOC Entry ID 6 (OID 25875)
     78--
     79-- Name: routed Type: TABLE Owner: ipdb
     80--
     81
     82CREATE TABLE "routed" (
     83        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
     84        "maskbits" integer DEFAULT 128,
     85        "city" character varying(30) DEFAULT '',
     86        Constraint "routed_pkey" Primary Key ("cidr")
     87);
     88
     89--
     90-- TOC Entry ID 7 (OID 25875)
     91--
     92-- Name: routed Type: ACL Owner:
     93--
     94
     95REVOKE ALL on "routed" from PUBLIC;
     96GRANT ALL on "routed" to "kdeugau";
     97GRANT ALL on "routed" to "ipdb";
     98
     99--
     100-- TOC Entry ID 8 (OID 31131)
     101--
     102-- Name: temp Type: TABLE Owner: ipdb
     103--
     104
     105CREATE TABLE "temp" (
     106        "ofs" integer
     107);
     108
     109--
     110-- TOC Entry ID 9 (OID 31131)
     111--
     112-- Name: temp Type: ACL Owner:
     113--
     114
     115REVOKE ALL on "temp" from PUBLIC;
     116GRANT ALL on "temp" to "kdeugau";
     117GRANT ALL on "temp" to "ipdb";
     118
     119--
     120-- TOC Entry ID 10 (OID 73917)
     121--
     122-- Name: searchme Type: VIEW Owner: ipdb
     123--
     124
     125CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.ptype, poolips.city, poolips.description FROM poolips;
     126
     127--
     128-- TOC Entry ID 11 (OID 73917)
     129--
     130-- Name: searchme Type: ACL Owner:
     131--
     132
     133REVOKE ALL on "searchme" from PUBLIC;
     134GRANT ALL on "searchme" to "kdeugau";
     135GRANT ALL on "searchme" to "ipdb";
     136
     137--
     138-- TOC Entry ID 12 (OID 91065)
     139--
     140-- Name: freeblocks Type: TABLE Owner: ipdb
     141--
     142
     143CREATE TABLE "freeblocks" (
     144        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
     145        "maskbits" integer DEFAULT 128,
     146        "city" character varying(30) DEFAULT '',
     147        "routed" character(1) DEFAULT 'n',
     148        Constraint "freeblocks_pkey" Primary Key ("cidr")
     149);
     150
     151--
     152-- TOC Entry ID 13 (OID 91065)
     153--
     154-- Name: freeblocks Type: ACL Owner:
     155--
     156
     157REVOKE ALL on "freeblocks" from PUBLIC;
     158GRANT ALL on "freeblocks" to "kdeugau";
     159GRANT ALL on "freeblocks" to "ipdb";
     160
     161--
     162-- TOC Entry ID 14 (OID 92444)
    84163--
    85164-- Name: poolips Type: TABLE Owner: ipdb
     
    93172        "ptype" character(1) DEFAULT 'c' NOT NULL,
    94173        "available" character(1) DEFAULT 'y',
    95         "notes" text,
     174        "notes" text DEFAULT '',
    96175        "description" character varying(64) DEFAULT '',
     176        "circuitid" character varying(128) DEFAULT '',
    97177        CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar))),
    98178        Constraint "poolips_pkey" Primary Key ("ip")
     
    100180
    101181--
    102 -- TOC Entry ID 7 (OID 25866)
     182-- TOC Entry ID 15 (OID 92444)
    103183--
    104184-- Name: poolips Type: ACL Owner:
     
    110190
    111191--
    112 -- TOC Entry ID 8 (OID 25872)
    113 --
    114 -- Name: masterblocks Type: TABLE Owner: ipdb
    115 --
    116 
    117 CREATE TABLE "masterblocks" (
    118         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
    119         Constraint "masterblocks_pkey" Primary Key ("cidr")
    120 );
    121 
    122 --
    123 -- TOC Entry ID 9 (OID 25872)
    124 --
    125 -- Name: masterblocks Type: ACL Owner:
    126 --
    127 
    128 REVOKE ALL on "masterblocks" from PUBLIC;
    129 GRANT ALL on "masterblocks" to "kdeugau";
    130 GRANT ALL on "masterblocks" to "ipdb";
    131 
    132 --
    133 -- TOC Entry ID 10 (OID 25875)
    134 --
    135 -- Name: routed Type: TABLE Owner: ipdb
    136 --
    137 
    138 CREATE TABLE "routed" (
    139         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
     192-- TOC Entry ID 16 (OID 92725)
     193--
     194-- Name: allocations Type: TABLE Owner: ipdb
     195--
     196
     197CREATE TABLE "allocations" (
     198        "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
     199        "custid" character varying(16) DEFAULT '',
     200        "type" character(2) DEFAULT '',
     201        "city" character varying(30) DEFAULT '',
     202        "description" character varying(64) DEFAULT '',
     203        "notes" text DEFAULT '',
    140204        "maskbits" integer DEFAULT 128,
    141         "city" character varying(30) DEFAULT '',
    142         Constraint "routed_pkey" Primary Key ("cidr")
    143 );
    144 
    145 --
    146 -- TOC Entry ID 11 (OID 25875)
    147 --
    148 -- Name: routed Type: ACL Owner:
    149 --
    150 
    151 REVOKE ALL on "routed" from PUBLIC;
    152 GRANT ALL on "routed" to "kdeugau";
    153 GRANT ALL on "routed" to "ipdb";
    154 
    155 --
    156 -- TOC Entry ID 12 (OID 31131)
    157 --
    158 -- Name: temp Type: TABLE Owner: ipdb
    159 --
    160 
    161 CREATE TABLE "temp" (
    162         "ofs" integer
    163 );
    164 
    165 --
    166 -- TOC Entry ID 13 (OID 31131)
    167 --
    168 -- Name: temp Type: ACL Owner:
    169 --
    170 
    171 REVOKE ALL on "temp" from PUBLIC;
    172 GRANT ALL on "temp" to "kdeugau";
    173 GRANT ALL on "temp" to "ipdb";
    174 
    175 --
    176 -- TOC Entry ID 14 (OID 73917)
    177 --
    178 -- Name: searchme Type: VIEW Owner: ipdb
    179 --
    180 
    181 CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.ptype, poolips.city, poolips.description FROM poolips;
    182 
    183 --
    184 -- TOC Entry ID 15 (OID 73917)
    185 --
    186 -- Name: searchme Type: ACL Owner:
    187 --
    188 
    189 REVOKE ALL on "searchme" from PUBLIC;
    190 GRANT ALL on "searchme" to "kdeugau";
    191 GRANT ALL on "searchme" to "ipdb";
    192 
    193 --
    194 -- TOC Entry ID 16 (OID 91065)
    195 --
    196 -- Name: freeblocks Type: TABLE Owner: ipdb
    197 --
    198 
    199 CREATE TABLE "freeblocks" (
    200         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL,
    201         "maskbits" integer DEFAULT 128,
    202         "city" character varying(30) DEFAULT '',
    203         "routed" character(1) DEFAULT 'n',
    204         Constraint "freeblocks_pkey" Primary Key ("cidr")
    205 );
    206 
    207 --
    208 -- TOC Entry ID 17 (OID 91065)
    209 --
    210 -- Name: freeblocks Type: ACL Owner:
    211 --
    212 
    213 REVOKE ALL on "freeblocks" from PUBLIC;
    214 GRANT ALL on "freeblocks" to "kdeugau";
    215 GRANT ALL on "freeblocks" to "ipdb";
    216 
     205        "circuitid" character varying(128) DEFAULT '',
     206        Constraint "allocations_pkey" Primary Key ("cidr")
     207);
     208
     209--
     210-- TOC Entry ID 17 (OID 92725)
     211--
     212-- Name: allocations Type: ACL Owner:
     213--
     214
     215REVOKE ALL on "allocations" from PUBLIC;
     216GRANT ALL on "allocations" to "kdeugau";
     217GRANT ALL on "allocations" to "ipdb";
     218
  • trunk/cgi-bin/main.cgi

    r73 r74  
    734734  my $count = 0;
    735735  while (my @data = $sth->fetchrow_array) {
    736     # pool,ip,custid,city,ptype,available,notes,description
     736    # pool,ip,custid,city,ptype,available,notes,description,circuitid
    737737    # If desc is null, make it not null.  <g>
    738738    if ($data[7] eq '') {
     
    933933  $html =~ s|\$\$CITY\$\$|$webvar{city}|g;
    934934  $html =~ s|\$\$CUSTID\$\$|$webvar{custid}|g;
     935  $html =~ s|\$\$CIRCID\$\$|$webvar{circid}|g;
    935936  $webvar{desc} = desanitize($webvar{desc});
    936937  $webvar{notes} = desanitize($webvar{notes});
     
    976977
    977978    $sth = $ip_dbh->prepare("update poolips set custid='$webvar{custid}',".
    978         "city='$webvar{city}',available='n',description='$webvar{desc}'".
     979        "city='$webvar{city}',available='n',description='$webvar{desc}',".
     980        "circuitid='$webvar{circid}'".
    979981        " where ip='$cidr'");
    980982    $sth->execute;
     
    10281030          $sth = $ip_dbh->prepare("insert into allocations values ('$webvar{fullcidr}',".
    10291031            "'$webvar{custid}','$webvar{alloctype}','$webvar{city}','$webvar{desc}',".
    1030             "'$webvar{notes}',".$cidr->masklen.")");
     1032            "'$webvar{notes}',".$cidr->masklen.",'$webvar{circid}')");
    10311033          $sth->execute;
    10321034        } # routing vs non-routing netblock
     
    10941096          $sth = $ip_dbh->prepare("insert into allocations values ('$webvar{fullcidr}',".
    10951097            "'$webvar{custid}','$webvar{alloctype}','$webvar{city}',".
    1096             "'$webvar{desc}','$webvar{notes}',".$cidr->masklen.")");
     1098            "'$webvar{desc}','$webvar{notes}',".$cidr->masklen.",'$webvar{circid}')");
    10971099          $sth->execute;
    10981100        } # done with netblock alloctype != rr
     
    11171119        # have to insert all pool IPs into poolips table as "unallocated".
    11181120        $sth = $ip_dbh->prepare("insert into poolips values ('$webvar{fullcidr}',".
    1119           " ?, '6750400', '$webvar{city}', '$pooltype', 'y', '')");
     1121          " ?, '6750400', '$webvar{city}', '$pooltype', 'y', '', '', '')");
    11201122        my @poolip_list = $cidr->hostenum;
    11211123        for (my $i=1; $i<=$#poolip_list; $i++) {
     
    11901192  # because I'm lazy, we'll try to make the SELECT's bring out identical)ish) data
    11911193  if ($webvar{block} =~ /\/32$/) {
    1192     $sql = "select ip,custid,ptype,city,description,notes from poolips where ip='$webvar{block}'";
     1194    $sql = "select ip,custid,ptype,city,circuitid,description,notes from poolips where ip='$webvar{block}'";
    11931195  } else {
    1194     $sql = "select cidr,custid,type,city,description,notes from allocations where cidr='$webvar{block}'"
     1196    $sql = "select cidr,custid,type,city,circuitid,description,notes from allocations where cidr='$webvar{block}'"
    11951197  }
    11961198
     
    12431245  # These can be modified, although CustID changes may get ignored.
    12441246  $html =~ s/\$\$CUSTID\$\$/$data[1]/g;
    1245   $html =~ s/\$\$DESC\$\$/$data[4]/g;
    1246   $html =~ s/\$\$NOTES\$\$/$data[5]/g;
     1247  $html =~ s/\$\$TYPE\$\$/$data[2]/g;
     1248  $html =~ s/\$\$CIRCID\$\$/$data[4]/g;
     1249  $html =~ s/\$\$DESC\$\$/$data[5]/g;
     1250  $html =~ s/\$\$NOTES\$\$/$data[6]/g;
    12471251
    12481252  print $html;
     
    12671271      # Note the hack ( available='n' ) to work around "update" additions
    12681272      # to static IP space.  Eww.
    1269       $sql = "update poolips set custid='$webvar{custid}',".
    1270         "notes='$webvar{notes}',description='$webvar{desc}',available='n' ".
     1273      $sql = "update poolips set custid='$webvar{custid}',notes='$webvar{notes}',".
     1274        "circuitid='$webvar{circid}',description='$webvar{desc}',available='n' ".
    12711275        "where ip='$webvar{block}'";
    12721276    } else {
    12731277      $sql = "update allocations set custid='$webvar{custid}',".
    12741278        "description='$webvar{desc}',notes='$webvar{notes}',city='$webvar{city}',".
    1275         "type='$webvar{alloctype}' where cidr='$webvar{block}'";
     1279        "type='$webvar{alloctype}',circuitid='$webvar{circid}' where cidr='$webvar{block}'";
    12761280    }
    12771281syslog "debug", $sql;
     
    12981302  $html =~ s/\$\$TYPEFULL\$\$/$full_alloc_types{$webvar{alloctype}}/g;
    12991303  $html =~ s/\$\$CUSTID\$\$/$webvar{custid}/g;
     1304  $html =~ s/\$\$CIRCID\$\$/$webvar{circid}/g;
    13001305  $html =~ s/\$\$DESC\$\$/$webvar{desc}/g;
    13011306  $html =~ s/\$\$NOTES\$\$/$webvar{notes}/g;
     
    13221327  }
    13231328
    1324   my ($cidr, $custid, $type, $city, $desc, $notes, $alloctype);
     1329  my ($cidr, $custid, $type, $city, $circid, $desc, $notes, $alloctype);
    13251330
    13261331  if ($webvar{alloctype} eq 'rr') {
     
    13361341    $custid = "N/A";
    13371342    $alloctype = $webvar{alloctype};
     1343    $circid = "N/A";
    13381344    $desc = "N/A";
    13391345    $notes = "N/A";
     
    13441350    $custid = "N/A";
    13451351    $alloctype = $webvar{alloctype};
     1352    $circid = "N/A";
    13461353    $desc = "N/A";
    13471354    $notes = "N/A";
     
    13491356
    13501357    # Unassigning a static IP
    1351     my $sth = $ip_dbh->prepare("select ip,custid,city,ptype,notes from poolips".
     1358    my $sth = $ip_dbh->prepare("select ip,custid,city,ptype,notes,circuitid from poolips".
    13521359        " where ip='$webvar{block}'");
    13531360    $sth->execute();
    13541361#  croak $sth->errstr() if($sth->errstr());
    13551362
    1356     $sth->bind_columns(\$cidr, \$custid, \$city, \$alloctype, \$notes);
     1363    $sth->bind_columns(\$cidr, \$custid, \$city, \$alloctype, \$notes, \$circid);
    13571364    $sth->fetch() || croak $sth->errstr;
    13581365
     
    13611368  } else { # done with alloctype=[cdsmw]i
    13621369
    1363     my $sth = $ip_dbh->prepare("select cidr,custid,type,city,description,notes from ".
     1370    my $sth = $ip_dbh->prepare("select cidr,custid,type,city,circuitid,description,notes from ".
    13641371        "allocations where cidr='$webvar{block}'");
    13651372    $sth->execute();
    13661373#       croak $sth->errstr() if($sth->errstr());
    13671374
    1368     $sth->bind_columns(\$cidr, \$custid, \$alloctype, \$city, \$desc, \$notes);
    1369     $sth->fetch() || croak $sth->errstr;
     1375    $sth->bind_columns(\$cidr, \$custid, \$alloctype, \$city, \$circid, \$desc, \$notes);
     1376    $sth->fetch() || carp $sth->errstr;
    13701377  } # end cases for different alloctypes
    13711378
     
    13771384  $html =~ s|\$\$CITY\$\$|$city|g;
    13781385  $html =~ s|\$\$CUSTID\$\$|$custid|g;
     1386  $html =~ s|\$\$CIRCID\$\$|$circid|g;
    13791387  $html =~ s|\$\$DESC\$\$|$desc|g;
    13801388  $html =~ s|\$\$NOTES\$\$|$notes|g;
Note: See TracChangeset for help on using the changeset viewer.