Changeset 189 for trunk


Ignore:
Timestamp:
12/08/11 16:42:42 (13 years ago)
Author:
Kris Deugau
Message:

/trunk

Complete VegaDNS import script

  • add segment
  • light tweaks in replace segment
File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/vega-import.pl

    r184 r189  
    5959    $newdbh->do("SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('permissions', 'permission_id'),".
    6060        "1, true)") or warn "couldn't set permission sequence: ".$newdbh->errstr."\n";
     61    # set seq id on default_records since we're inserting more than we retrieved
     62    $newdbh->do("SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('default_records', 'record_id'),".
     63        "1, true)") or warn "couldn't set defrec sequence: ".$newdbh->errstr."\n";
    6164  }
    6265}
     
    6568        PrintError => 1,
    6669        PrintWarn => 1,
    67         AutoCommit => 1
     70        AutoCommit => 0
    6871  });
    6972
     
    8386my $defrecget = $vegadbh->prepare("SELECT group_id, host, type, val, distance, weight, port, ttl, ".
    8487        "description FROM default_records WHERE group_id=?");
     88my $logget = $vegadbh->prepare("SELECT domain_id, user_id, group_id, email, name, entry, time FROM log ".
     89        "WHERE group_id=?");
    8590
    8691my $newgrp = $newdbh->prepare("INSERT INTO groups (group_id,parent_group_id,group_name,permission_id) ".
     
    103108my $newdefrec = $newdbh->prepare("INSERT INTO default_records (group_id,host,type,val,distance,".
    104109        "weight,port,ttl,description) VALUES (?,?,?,?,?,?,?,?,?)");
     110my $logput = $newdbh->prepare("INSERT INTO log (domain_id, user_id, group_id, email, name, entry, stamp) ".
     111        "VALUES (?,?,?,?,?,?,?)");
     112
     113my $newgrpid = $newdbh->prepare("SELECT currval('groups_group_id_seq')");
     114my $newpgidup = $newdbh->prepare("UPDATE groups SET permission_id=? WHERE group_id=?");
     115my $newuserid = $newdbh->prepare("SELECT currval('users_user_id_seq')");
     116my $newpuidup = $newdbh->prepare("UPDATE users SET permission_id=? WHERE user_id=?");
     117my $newdomid = $newdbh->prepare("SELECT currval('domains_domain_id_seq')");
     118my $newrecid = $newdbh->prepare("SELECT currval('records_record_id_seq')");
     119
     120my $newgrp_add = $newdbh->prepare("INSERT INTO groups (parent_group_id,group_name) VALUES (?,?)");
     121my $newuser_add = $newdbh->prepare("INSERT INTO users (group_id, username, password, firstname, ".
     122        "lastname, phone, type, status, inherit_perm) VALUES (?,?,?,?,?,?,?,?,'f')");
     123my $newdom_add = $newdbh->prepare("INSERT INTO domains (domain, group_id, description, status) VALUES ".
     124        "(?,?,?,?)");
     125my $newrec_add = $newdbh->prepare("INSERT INTO records (domain_id,host,type,val,distance,weight,port,".
     126        "ttl,description) VALUES (?,?,?,?,?,?,?,?,?)");
    105127
    106128my $foo = qq(
     
    118140);
    119141
    120 my $stage = 'start';
    121 $grpsubs->execute;
    122 my $grp = 0;
    123 while (($grp) = $grpsubs->fetchrow_array()) {
    124   dogroup($grp);
    125 }
    126 # set group seq to $grp+1
     142my $maxgrp = 0;
     143my %pcmap;  # used to map parent/child relations
     144
     145if ($mode eq 'replace') {
     146
     147  my $stage = 'start';
     148  $grpsubs->execute;
     149  my $grp = 0;
     150  while (($grp) = $grpsubs->fetchrow_array()) {
     151    $maxgrp = $grp if $grp > $maxgrp;
     152    dogroup_replace($grp);
     153  }
     154
     155  # Fix up sequence numbers to prevent insert problems
     156  my %idmap = ('groups' => 'group_id', 'users' => 'user_id', 'domains' => 'domain_id',
     157        'records' => 'record_id');
     158  for my $table ('groups','users','domains','records') {
     159    $newdbh->do("SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('$table','$idmap{$table}'),".
     160        "(SELECT max($idmap{$table}) FROM $table),true)");
     161  }
     162
     163} else {
     164
     165  $grpsubs->execute;
     166  my $grp = 0;
     167  while (($grp) = $grpsubs->fetchrow_array()) {
     168    $maxgrp = $grp if $grp > $maxgrp;
     169    dogroup_add($grp);
     170  }
     171
     172}
     173
     174# and done.
    127175exit;
    128176
    129 sub dogroup {
     177sub dogroup_replace {
    130178  my $grpid = shift;
    131179  $grpdata->execute($grpid) or warn $grpdata->errstr;
     
    152200
    153201 ## Users
    154   print " users:\n";
     202  print " users: ";
    155203  $userget->execute($grpid);
    156204  while (my $user = $userget->fetchrow_hashref) {
     
    178226        $user->{first_name}, $user->{last_name}, $user->{phone}, $user->{account_type}, $user->{status}, $pid)
    179227        or warn "  new user ".$user->{email}." (".$user->{user_id}.") failed: ".$newuser->errstr."\n";
    180     print "  user ".$user->{email}." (".$user->{user_id}.")\n";
    181   }
    182   print " done\n";
     228    print $user->{email}." (".$user->{user_id}."), ";
     229  }
    183230
    184231 ## Domains
    185   print " domains: \n";
     232  print "\n domains: ";
    186233  $domget->execute($grpid);
    187234  while (my ($id,$dom,$desc,$status) = $domget->fetchrow_array) {
    188235    $status = ($status eq 'active' ? 1 : 0);
    189236    $newdom->execute($id, $dom, $grpid, $desc, $status);
    190     print "  domain $dom ($id)\n";
     237    print "$dom ($id), ";
    191238    $recget->execute($id);
    192239    while (my @rec = $recget->fetchrow_array) {
     
    198245    }
    199246  }
    200   print " done\n";
    201247
    202248 ## Default records
    203   print " default records: ";
     249  print "\n default records: ";
    204250  $defrecget->execute(1);       # Vega 1.1.5/1.1.6 do not have default records for all groups;
    205251                                # there is only support for one set of default records coded.
     
    212258    $newdefrec->execute(@rec);
    213259  }
    214   print " done\n";
    215 }
     260
     261 ## Log entries
     262  print "\n log entries: ";
     263  $logget->execute($grpid);
     264  while (my ($did,$uid,$gid,$email,$name,$entry,$stamp) = $logget->fetchrow_array) {
     265    $stamp = localtime($stamp).'';
     266    $logput->execute($did,$uid,$gid,$email,$name,'[V] '.$entry,$stamp);
     267  }
     268
     269  print "\n done\n";
     270}
     271
     272sub dogroup_add {
     273  my $oldgrpid = shift;
     274
     275  $grpdata->execute($oldgrpid) or warn $grpdata->errstr;
     276  my $oldgrp = $grpdata->fetchrow_hashref() or warn $grpdata->errstr;
     277  print "group id $oldgrpid, name $oldgrp->{name}\n";
     278
     279  my $newgrpparent = $pcmap{g}{$oldgrp->{parent_group_id}};
     280  $newgrpparent = 1 if $oldgrpid == 1;
     281
     282  # do in the same order as dnsdb
     283  $newgrp_add->execute($newgrpparent, $oldgrp->{name});
     284  $newgrpid->execute;
     285  my ($newgid) = $newgrpid->fetchrow_array;
     286  $pcmap{g}{$oldgrpid} = $newgid;
     287
     288  $grppget->execute($oldgrpid) or warn $grppget->errstr;
     289  my $oldp = $grppget->fetchrow_hashref() or warn $grppget->errstr;
     290
     291  # de-nullify
     292  foreach my $permtype ('self_edit', 'group_create', 'group_edit', 'group_delete', 'user_create',
     293        'user_edit', 'user_delete', 'domain_create', 'domain_edit', 'domain_delete', 'record_create',
     294        'record_edit', 'record_delete') {
     295    $oldp->{$permtype} = 'f' if !defined($oldp->{$permtype});
     296  }
     297  $newgrppset->execute($oldp->{self_edit}, $oldp->{group_create}, $oldp->{group_edit}, $oldp->{group_delete},
     298        $oldp->{user_create}, $oldp->{user_edit}, $oldp->{user_delete},
     299        $oldp->{domain_create}, $oldp->{domain_edit}, $oldp->{domain_delete},
     300        $oldp->{record_create}, $oldp->{record_edit}, $oldp->{record_delete},
     301        $newgid) or warn $newgrppset->errstr;
     302  $newpid->execute;
     303  my ($pid) = $newpid->fetchrow_array;
     304  $newpgidup->execute($pid,$newgid);
     305
     306 ## Users
     307  print " users: ";
     308  $userget->execute($oldgrpid);
     309  while (my $user = $userget->fetchrow_hashref) {
     310    # fiddle user data
     311    $user->{account_type} = 'S' if $user->{account_type} eq 'senior_admin';
     312    $user->{account_type} = 'u' if $user->{account_type} eq 'user';
     313    $user->{status} = ($user->{status} eq 'active' ? 1 : 0);
     314    $userpget->execute($user->{user_id}) or warn "failed permission get on ".$user->{user_id}."\n";
     315
     316    $newuser_add->execute($newgid, $user->{email}, $user->{password}, $user->{first_name},
     317        $user->{last_name}, $user->{phone}, $user->{account_type}, $user->{status})
     318        or warn "  new user ".$user->{email}." (".$user->{user_id}.") failed: ".$newuser->errstr."\n";
     319    print " user ".$user->{email}." (".$user->{user_id}."), ";
     320    $newuserid->execute;
     321    my ($newuid) = $newuserid->fetchrow_array;
     322    $pcmap{u}{$user->{user_id}} = $newuid;
     323
     324    my $oldp = $userpget->fetchrow_hashref;
     325    # de-nullify
     326    foreach my $permtype ('admin', 'self_edit', 'group_create', 'group_edit', 'group_delete', 'user_create',
     327        'user_edit', 'user_delete', 'domain_create', 'domain_edit', 'domain_delete', 'record_create',
     328        'record_edit', 'record_delete') {
     329      $oldp->{$permtype} = 'f' if !defined($oldp->{$permtype});
     330    }
     331    $newuserpset->execute(($user->{account_type} eq 'S' ? 't' : 'f'), $oldp->{self_edit},
     332        $oldp->{group_create}, $oldp->{group_edit}, $oldp->{group_delete},
     333        $oldp->{user_create}, $oldp->{user_edit}, $oldp->{user_delete},
     334        $oldp->{domain_create}, $oldp->{domain_edit}, $oldp->{domain_delete},
     335        $oldp->{record_create}, $oldp->{record_edit}, $oldp->{record_delete},
     336        $newuid) or warn $newgrppset->errstr;
     337    $newpid->execute;
     338    my ($pid) = $newpid->fetchrow_array;
     339    $newpuidup->execute($pid,$newuid);
     340  }
     341
     342 ## Domains
     343  print "\n domains: ";
     344  $domget->execute($oldgrpid);
     345  while (my ($id,$dom,$desc,$status) = $domget->fetchrow_array) {
     346    $status = ($status eq 'active' ? 1 : 0);
     347    $newdom_add->execute($dom, $newgid, $desc, $status);
     348    print "$dom ($id), ";
     349    $newdomid->execute;
     350    my ($newdid) = $newdomid->fetchrow_array;
     351    $pcmap{d}{$id} = $newdid;
     352    $recget->execute($id);
     353    while (my @rec = $recget->fetchrow_array) {
     354      $rec[0] = $newdid;
     355      $rec[2] = $reverse_typemap{$vegatypes{$rec[2]}};
     356      $rec[4] = 0 if !$rec[4];
     357      $rec[5] = 0 if !$rec[5];
     358      $rec[6] = 0 if !$rec[6];
     359      $newrec_add->execute(@rec);
     360    }
     361  }
     362
     363 ## Default records
     364  print "\n default records: ";
     365  $defrecget->execute(1);       # Vega 1.1.5/1.1.6 do not have default records for all groups;
     366                                # there is only support for one set of default records coded.
     367  while (my @rec = $defrecget->fetchrow_array) {
     368    $rec[0] = $newgid;
     369    $rec[2] = $reverse_typemap{$vegatypes{$rec[2]}};
     370    $rec[4] = 0 if !$rec[4];
     371    $rec[5] = 0 if !$rec[5];
     372    $rec[6] = 0 if !$rec[6];
     373    $newdefrec->execute(@rec);
     374  }
     375
     376 ## Log entries
     377  print "\n log entries: ";
     378  $logget->execute($oldgrpid);
     379  while (my ($did,$uid,$gid,$email,$name,$entry,$stamp) = $logget->fetchrow_array) {
     380    $did = $pcmap{d}{$did};
     381    $uid = $pcmap{u}{$uid};
     382    $gid = $pcmap{g}{$gid};
     383    $stamp = localtime($stamp).'';
     384    $logput->execute($did,$uid,$gid,$email,$name,'[V] '.$entry,$stamp);
     385  }
     386
     387  print "\n done\n";
     388}
Note: See TracChangeset for help on using the changeset viewer.