Changeset 370 for trunk


Ignore:
Timestamp:
07/29/12 22:02:48 (12 years ago)
Author:
Kris Deugau
Message:

/trunk

Checkpoint; adding location/view support. See #10.

  • add location to menu
  • add table and link fields to initial tabledef and upgrade SQL
  • add listing subs and list page
  • update permissions list and subpage template with new permissions
Location:
trunk
Files:
1 added
6 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r368 r370  
    4747        &addUser &updateUser &delUser &userFullName &userStatus &getUserData
    4848        &getUserCount &getUserList &getUserDropdown
     49        &addLoc &updateLoc &delLoc
     50        &getLocCount &getLocList &getLocDropdown
    4951        &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    5052        &addRec &updateRec &delRec
    … …  
    7274                &addUser &updateUser &delUser &userFullName &userStatus &getUserData
    7375                &getUserCount &getUserList &getUserDropdown
     76                &addLoc &updateLoc &delLoc
     77                &getLocCount &getLocList &getLocDropdown
    7478                &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    7579                &addRec &updateRec &delRec
    … …  
    106110
    107111# Arguably defined wholly in the db, but little reason to change without supporting code changes
     112# group_view, user_view permissions? separate rDNS permission(s)?
    108113our @permtypes = qw (
    109114        group_edit      group_create    group_delete
    … …  
    111116        domain_edit     domain_create   domain_delete
    112117        record_edit     record_create   record_delete
     118        location_edit   location_create location_delete location_view
    113119        self_edit       admin
    114120);
    … …  
    28912897
    28922898} # end getUserData()
     2899
     2900
     2901## DNSDB::addLoc()
     2902sub addLoc {}
     2903
     2904## DNSDB::updateLoc()
     2905sub updateLoc {}
     2906
     2907## DNSDB::delLoc()
     2908sub delLoc {}
     2909
     2910
     2911## DNSDB::getLocCount()
     2912# Get count of locations/views
     2913# Takes a database handle and hash containing at least the current group, and optionally:
     2914# - a reference list of secondary groups
     2915# - a filter string
     2916# - a "Starts with" string
     2917sub getLocCount {
     2918  my $dbh = shift;
     2919
     2920  my %args = @_;
     2921
     2922  my @filterargs;
     2923
     2924  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
     2925  push @filterargs, "^$args{startwith}" if $args{startwith};
     2926  push @filterargs, $args{filter} if $args{filter};
     2927
     2928
     2929  my $sql = "SELECT count(*) FROM locations ".
     2930        "WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2931        ($args{startwith} ? " AND description ~* ?" : '').
     2932        ($args{filter} ? " AND description ~* ?" : '');
     2933  my ($count) = $dbh->selectrow_array($sql, undef, (@filterargs) );
     2934  $errstr = $dbh->errstr if !$count;
     2935  return $count;
     2936} # end getLocCount()
     2937
     2938
     2939## DNSDB::getLocList()
     2940sub getLocList {
     2941  my $dbh = shift;
     2942
     2943  my %args = @_;
     2944
     2945  my @filterargs;
     2946
     2947  $args{startwith} = undef if $args{startwith} && $args{startwith} !~ /^(?:[a-z]|0-9)$/;
     2948  push @filterargs, "^$args{startwith}" if $args{startwith};
     2949  push @filterargs, $args{filter} if $args{filter};
     2950
     2951  # better to request sorts on "simple" names, but it means we need to map it to real columns
     2952#  my %sortmap = (user => 'u.username', type => 'u.type', group => 'g.group_name', status => 'u.status',
     2953#       fname => 'fname');
     2954#  $args{sortby} = $sortmap{$args{sortby}};
     2955
     2956  # protection against bad or missing arguments
     2957  $args{sortorder} = 'ASC' if !$args{sortorder};
     2958  $args{sortby} = 'l.description' if !$args{sortby};
     2959  $args{offset} = 0 if !$args{offset};
     2960
     2961  my $sql = "SELECT l.location, l.description, l.iplist, g.group_name ".
     2962        "FROM locations l ".
     2963        "INNER JOIN groups g ON l.group_id=g.group_id ".
     2964        "WHERE l.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2965        ($args{startwith} ? " AND l.description ~* ?" : '').
     2966        ($args{filter} ? " AND l.description ~* ?" : '').
     2967        " ORDER BY $args{sortby} $args{sortorder} ".
     2968        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
     2969  my $ulist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
     2970  $errstr = $dbh->errstr if !$ulist;
     2971  return $ulist;
     2972} # end getLocList()
     2973
     2974
     2975## DNSDB::getLocDropdown()
    28932976
    28942977

  • trunk/dns-1.0-1.2.sql

    r369 r370  
    11-- SQL table/record type upgrade file for dnsadmin 1.0 to 1.2 migration
     2
     3-- need this before we add any other bits
     4CREATE TABLE locations (
     5    loc character varying (4) PRIMARY KEY,
     6    group_id integer NOT NULL DEFAULT 1,
     7    iplist text NOT NULL DEFAULT '',
     8    description text NOT NULL DEFAULT ''
     9);
     10
     11ALTER TABLE ONLY locations
     12    ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
     13
     14ALTER TABLE permissions ADD COLUMN location_create boolean DEFAULT false NOT NULL;
     15ALTER TABLE permissions ADD COLUMN location_edit boolean DEFAULT false NOT NULL;
     16ALTER TABLE permissions ADD COLUMN location_delete boolean DEFAULT false NOT NULL;
     17ALTER TABLE permissions ADD COLUMN location_view boolean DEFAULT false NOT NULL;
    218
    319-- Minor buglet;  domains must be unique
    … …  
    2339SELECT pg_catalog.setval('default_rev_records_record_id_seq', 5, false);
    2440
    25 ALTER TABLE domains ADD COLUMN changed boolean;
    26 UPDATE domains SET changed=false;
    27 ALTER TABLE domains ALTER COLUMN changed SET DEFAULT true;
    28 ALTER TABLE domains ALTER COLUMN changed SET NOT NULL;
     41ALTER TABLE domains ADD COLUMN changed boolean DEFAULT true NOT NULL;
     42ALTER TABLE domains ADD COLUMN default_location character varying (4) DEFAULT '' NOT NULL;
    2943-- ~2x performance boost iff most zones are fed to output from the cache
    3044CREATE INDEX dom_status_index ON domains (status);
    … …  
    3852    zserial integer,
    3953    sertype character(1) DEFAULT 'D'::bpchar,
    40     changed boolean DEFAULT true NOT NULL
     54    changed boolean DEFAULT true NOT NULL,
     55    default_location character varying (4) DEFAULT '' NOT NULL
    4156);
    4257CREATE INDEX rev_status_index ON revzones (status);
    … …  
    5267ALTER TABLE records DROP CONSTRAINT "$1";
    5368ALTER TABLE records ALTER COLUMN domain_id SET DEFAULT 0;
    54 ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0;
    55 UPDATE records SET rdns_id=0;
    56 ALTER TABLE records ALTER COLUMN rdns_id SET NOT NULL;
     69ALTER TABLE records ADD COLUMN rdns_id INTEGER DEFAULT 0 NOT NULL;
     70ALTER TABLE records ADD COLUMN location character varying (4) DEFAULT '' NOT NULL;
     71
    5772-- ~120s -> 75s performance boost on 100K records when always exporting all records
    5873CREATE INDEX rec_types_index ON records (type);

  • trunk/dns.cgi

    r338 r370  
    13421342  }
    13431343
     1344} elsif ($webvar{page} eq 'loclist') {
     1345
     1346#  changepage(page => "domlist", errmsg => "You are not allowed access to this function")
     1347#       unless $permissions{admin} || $permissions{ foo? };
     1348
     1349  # security check - does the user have permission to access this entity?
     1350#  if (!check_scope(id => $webvar{id}, type => 'loc')) {
     1351#    changepage(page => "loclist", errmsg => "You are not permitted to <foo> the requested location/view");
     1352#  }
     1353  list_locations();
     1354
     1355# Permissions!
     1356  $page->param(addloc => $permissions{admin} || $permissions{loc_create});
     1357  $page->param(delloc => $permissions{admin} || $permissions{loc_delete});
     1358
     1359} elsif ($webvar{page} eq 'location') {
     1360
     1361
    13441362} elsif ($webvar{page} eq 'dnsq') {
    13451363
    … …  
    15861604##fixme
    15871605  $page->param(mayrdns => 1);
     1606  $page->param(mayloc => 1);
    15881607
    15891608  $page->param(maydefrec => $permissions{admin});
    … …  
    20792098
    20802099
     2100sub list_locations {
     2101
     2102  my @childgroups;
     2103  getChildren($dbh, $curgroup, \@childgroups, 'all') if $searchsubs;
     2104  my $childlist = join(',',@childgroups);
     2105
     2106  my $count = getLocCount($dbh, (childlist => $childlist, curgroup => $curgroup,
     2107        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef) ) );
     2108
     2109# fill page count and first-previous-next-last-all bits
     2110  fill_pgcount($count,"locations/views",'');
     2111  fill_fpnla($count);
     2112
     2113  $sortby = 'user';
     2114# sort/order
     2115  $session->param($webvar{page}.'sortby', $webvar{sortby}) if $webvar{sortby};
     2116  $session->param($webvar{page}.'order', $webvar{order}) if $webvar{order};
     2117
     2118  $sortby = $session->param($webvar{page}.'sortby') if $session->param($webvar{page}.'sortby');
     2119  $sortorder = $session->param($webvar{page}.'order') if $session->param($webvar{page}.'order');
     2120
     2121# set up the headers
     2122  my @cols = ('description', 'iplist', 'group');
     2123  my %colnames = (description => 'Location/View Name', iplist => 'Permitted IPs/Ranges', group => 'Group');
     2124  fill_colheads($sortby, $sortorder, \@cols, \%colnames);
     2125
     2126# waffle, waffle - keep state on these as well as sortby, sortorder?
     2127  $page->param("start$startwith" => 1) if $startwith && $startwith =~ /^(?:[a-z]|0-9)$/;
     2128
     2129  $page->param(filter => $filter) if $filter;
     2130  $page->param(searchsubs => $searchsubs) if $searchsubs;
     2131
     2132  my $loclist = getLocList($dbh, (childlist => $childlist, curgroup => $curgroup,
     2133        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef),
     2134        offset => $webvar{offset}, sortby => $sortby, sortorder => $sortorder) );
     2135  # Some UI things need to be done to the list
     2136  foreach my $l (@{$loclist}) {
     2137    $l->{edloc} = ($permissions{admin} || $permissions{loc_edit});
     2138    $l->{delloc} = ($permissions{admin} || $permissions{loc_delete});
     2139  }
     2140  $page->param(loctable => $loclist);
     2141} # end list_locations()
     2142
     2143
    20812144# Generate all of the glop necessary to add or not the appropriate marker/flag for
    20822145# the sort order and column in domain, user, group, and record lists

  • trunk/dns.sql

    r369 r370  
    16161       dbversion       1.2
    1717\.
     18
     19CREATE TABLE locations (
     20    location character varying (4) PRIMARY KEY,
     21    group_id integer NOT NULL DEFAULT 1,
     22    iplist text NOT NULL DEFAULT '',
     23    description text NOT NULL DEFAULT ''
     24);
    1825
    1926CREATE TABLE default_records (
    … …  
    6673    zserial integer,
    6774    sertype character(1) DEFAULT 'D'::bpchar,
    68     changed boolean DEFAULT true NOT NULL
     75    changed boolean DEFAULT true NOT NULL,
     76    default_location character varying (4) DEFAULT '' NOT NULL
    6977);
    7078
    … …  
    7785    zserial integer,
    7886    sertype character(1) DEFAULT 'D'::bpchar,
    79     changed boolean DEFAULT true NOT NULL
     87    changed boolean DEFAULT true NOT NULL,
     88    default_location character varying (4) DEFAULT '' NOT NULL
    8089);
    8190
    … …  
    123132    record_edit boolean DEFAULT false NOT NULL,
    124133    record_delete boolean DEFAULT false NOT NULL,
     134    location_create boolean DEFAULT false NOT NULL,
     135    location_edit boolean DEFAULT false NOT NULL,
     136    location_delete boolean DEFAULT false NOT NULL,
     137    location_view boolean DEFAULT false NOT NULL,
    125138    user_id integer UNIQUE,
    126139    group_id integer UNIQUE
    … …  
    129142-- Need *two* basic permissions;  one for the initial group, one for the default admin user
    130143COPY permissions (permission_id, admin, self_edit, group_create, group_edit, group_delete, user_create, user_edit, user_delete, domain_create, domain_edit, domain_delete, record_create, record_edit, record_delete, user_id, group_id) FROM stdin;
    131 1       f       f       f       f       f       f       f       f       t       t       t       t       t       t       \N      1
    132 2       t       f       f       f       f       f       f       f       f       f       f       f       f       f       1       \N
     1441       f       f       f       f       f       f       f       f       t       t       t       t       t       t       f       f       f       f       \N      1
     1452       t       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       f       1       \N
    133146\.
    134147
    … …  
    145158    port integer DEFAULT 0 NOT NULL,
    146159    ttl integer DEFAULT 7200 NOT NULL,
    147     description text
     160    description text,
     161    default_location character varying (4) DEFAULT '' NOT NULL
    148162);
    149163
    … …  
    289303-- foreign keys
    290304-- fixme: permissions FK refs
     305ALTER TABLE ONLY locations
     306    ADD CONSTRAINT "locations_group_id_fkey" FOREIGN KEY (group_id) REFERENCES groups(group_id);
     307
    291308ALTER TABLE ONLY domains
    292309    ADD CONSTRAINT "$1" FOREIGN KEY (group_id) REFERENCES groups(group_id);

  • trunk/templates/menu.tmpl

    r238 r370  
    99<TMPL_IF maydefrec><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=reclist&amp;id=<TMPL_VAR NAME=group>&amp;defrec=y">Default Records</a><br />
    1010<TMPL_IF mayrdns><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=reclist&amp;id=<TMPL_VAR NAME=group>&amp;defrec=y&amp;revrec=y">Default Reverse Records</a><br /></TMPL_IF></TMPL_IF>
     11<TMPL_IF mayloc><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=loclist&id=<TMPL_VAR NAME=group>">Locations/Views</a><br /></TMPL_IF>
    1112<TMPL_IF mayimport><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=axfr">AXFR Import</a><br /></TMPL_IF>
    1213<TMPL_IF maybulk><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=bulkdomain">Bulk Domain Operations</a><br /></TMPL_IF>

  • trunk/templates/permlist.tmpl

    r67 r370  
    2525</tr>
    2626<tr>
     27        <td align="right">Location/View:</td>
     28        <td<TMPL_UNLESS may_location_edit> class="<TMPL_UNLESS info>noaccess<TMPL_ELSE>info</TMPL_UNLESS>"</TMPL_UNLESS>><input type="checkbox"<TMPL_UNLESS info> name="location_edit"</TMPL_UNLESS><TMPL_IF location_edit> checked="checked"</TMPL_IF><TMPL_UNLESS may_location_edit>disabled="disabled"</TMPL_UNLESS> /> Edit</td>
     29        <td<TMPL_UNLESS may_location_create> class="<TMPL_UNLESS info>noaccess<TMPL_ELSE>info</TMPL_UNLESS>"</TMPL_UNLESS>><input type="checkbox"<TMPL_UNLESS info> name="location_create"</TMPL_UNLESS><TMPL_IF location_create> checked="checked"</TMPL_IF><TMPL_UNLESS may_location_create> disabled="disabled"</TMPL_UNLESS> /> Create</td>
     30        <td<TMPL_UNLESS may_location_delete> class="<TMPL_UNLESS info>noaccess<TMPL_ELSE>info</TMPL_UNLESS>"</TMPL_UNLESS>><input type="checkbox"<TMPL_UNLESS info> name="location_delete"</TMPL_UNLESS><TMPL_IF location_delete> checked="checked"</TMPL_IF><TMPL_UNLESS may_location_delete> disabled="disabled"</TMPL_UNLESS> /> Delete</td>
     31        <td<TMPL_UNLESS may_location_view> class="<TMPL_UNLESS info>noaccess<TMPL_ELSE>info</TMPL_UNLESS>"</TMPL_UNLESS>><input type="checkbox"<TMPL_UNLESS info> name="location_view"</TMPL_UNLESS><TMPL_IF location_view> checked="checked"</TMPL_IF><TMPL_UNLESS may_location_view> disabled="disabled"</TMPL_UNLESS> /> View</td>
     32</tr>
     33<tr>
    2734        <td align="right">Self:</td>
    2835        <td<TMPL_UNLESS may_self_edit> class="<TMPL_UNLESS info>noaccess<TMPL_ELSE>info</TMPL_UNLESS>"</TMPL_UNLESS>><input type="checkbox"<TMPL_UNLESS info> name="self_edit"</TMPL_UNLESS><TMPL_IF self_edit> checked="checked"</TMPL_IF><TMPL_UNLESS may_self_edit> disabled="disabled"</TMPL_UNLESS> /> Edit</td>
Note: See TracChangeset for help on using the changeset viewer.