Changeset 189

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:

• trunk/vega-import.pl (modified) (9 diffs)

trunk/vega-import.pl

@@ -59 +59 @@
     $newdbh->do("SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('permissions', 'permission_id'),".
         "1, true)") or warn "couldn't set permission sequence: ".$newdbh->errstr."\n";
+    # set seq id on default_records since we're inserting more than we retrieved
+    $newdbh->do("SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('default_records', 'record_id'),".
+        "1, true)") or warn "couldn't set defrec sequence: ".$newdbh->errstr."\n";
   }
 }
@@ -65 +68 @@
         PrintError => 1,
         PrintWarn => 1,
-        AutoCommit => 1
+        AutoCommit => 0
   });
 
@@ -83 +86 @@
 my $defrecget = $vegadbh->prepare("SELECT group_id, host, type, val, distance, weight, port, ttl, ".
         "description FROM default_records WHERE group_id=?");
+my $logget = $vegadbh->prepare("SELECT domain_id, user_id, group_id, email, name, entry, time FROM log ".
+        "WHERE group_id=?");
 
 my $newgrp = $newdbh->prepare("INSERT INTO groups (group_id,parent_group_id,group_name,permission_id) ".
@@ -103 +108 @@
 my $newdefrec = $newdbh->prepare("INSERT INTO default_records (group_id,host,type,val,distance,".
         "weight,port,ttl,description) VALUES (?,?,?,?,?,?,?,?,?)");
+my $logput = $newdbh->prepare("INSERT INTO log (domain_id, user_id, group_id, email, name, entry, stamp) ".
+        "VALUES (?,?,?,?,?,?,?)");
+
+my $newgrpid = $newdbh->prepare("SELECT currval('groups_group_id_seq')");
+my $newpgidup = $newdbh->prepare("UPDATE groups SET permission_id=? WHERE group_id=?");
+my $newuserid = $newdbh->prepare("SELECT currval('users_user_id_seq')");
+my $newpuidup = $newdbh->prepare("UPDATE users SET permission_id=? WHERE user_id=?");
+my $newdomid = $newdbh->prepare("SELECT currval('domains_domain_id_seq')");
+my $newrecid = $newdbh->prepare("SELECT currval('records_record_id_seq')");
+
+my $newgrp_add = $newdbh->prepare("INSERT INTO groups (parent_group_id,group_name) VALUES (?,?)");
+my $newuser_add = $newdbh->prepare("INSERT INTO users (group_id, username, password, firstname, ".
+        "lastname, phone, type, status, inherit_perm) VALUES (?,?,?,?,?,?,?,?,'f')");
+my $newdom_add = $newdbh->prepare("INSERT INTO domains (domain, group_id, description, status) VALUES ".
+        "(?,?,?,?)");
+my $newrec_add = $newdbh->prepare("INSERT INTO records (domain_id,host,type,val,distance,weight,port,".
+        "ttl,description) VALUES (?,?,?,?,?,?,?,?,?)");
 
 my $foo = qq(
@@ -118 +140 @@
 );
 
-my $stage = 'start';
-$grpsubs->execute;
-my $grp = 0;
-while (($grp) = $grpsubs->fetchrow_array()) {
-  dogroup($grp);
-}
-# set group seq to $grp+1
+my $maxgrp = 0;
+my %pcmap;  # used to map parent/child relations
+
+if ($mode eq 'replace') {
+
+  my $stage = 'start';
+  $grpsubs->execute;
+  my $grp = 0;
+  while (($grp) = $grpsubs->fetchrow_array()) {
+    $maxgrp = $grp if $grp > $maxgrp;
+    dogroup_replace($grp);
+  }
+
+  # Fix up sequence numbers to prevent insert problems
+  my %idmap = ('groups' => 'group_id', 'users' => 'user_id', 'domains' => 'domain_id',
+        'records' => 'record_id');
+  for my $table ('groups','users','domains','records') {
+    $newdbh->do("SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('$table','$idmap{$table}'),".
+        "(SELECT max($idmap{$table}) FROM $table),true)");
+  }
+
+} else {
+
+  $grpsubs->execute;
+  my $grp = 0;
+  while (($grp) = $grpsubs->fetchrow_array()) {
+    $maxgrp = $grp if $grp > $maxgrp;
+    dogroup_add($grp);
+  }
+
+}
+
+# and done.
 exit;
 
-sub dogroup {
+sub dogroup_replace {
   my $grpid = shift;
   $grpdata->execute($grpid) or warn $grpdata->errstr;
@@ -152 +200 @@
 
  ## Users
-  print " users:\n";
+  print " users: ";
   $userget->execute($grpid);
   while (my $user = $userget->fetchrow_hashref) {
@@ -178 +226 @@
         $user->{first_name}, $user->{last_name}, $user->{phone}, $user->{account_type}, $user->{status}, $pid) 
         or warn "  new user ".$user->{email}." (".$user->{user_id}.") failed: ".$newuser->errstr."\n";
-    print "  user ".$user->{email}." (".$user->{user_id}.")\n";
-  }
-  print " done\n";
+    print $user->{email}." (".$user->{user_id}."), ";
+  }
 
  ## Domains
-  print " domains: \n";
+  print "\n domains: ";
   $domget->execute($grpid);
   while (my ($id,$dom,$desc,$status) = $domget->fetchrow_array) {
     $status = ($status eq 'active' ? 1 : 0);
     $newdom->execute($id, $dom, $grpid, $desc, $status);
-    print "  domain $dom ($id)\n";
+    print "$dom ($id), ";
     $recget->execute($id);
     while (my @rec = $recget->fetchrow_array) {
@@ -198 +245 @@
     }
   }
-  print " done\n";
 
  ## Default records
-  print " default records: ";
+  print "\n default records: ";
   $defrecget->execute(1);       # Vega 1.1.5/1.1.6 do not have default records for all groups;
                                 # there is only support for one set of default records coded.
@@ -212 +258 @@
     $newdefrec->execute(@rec);
   }
-  print " done\n";
-}
+
+ ## Log entries
+  print "\n log entries: ";
+  $logget->execute($grpid);
+  while (my ($did,$uid,$gid,$email,$name,$entry,$stamp) = $logget->fetchrow_array) {
+    $stamp = localtime($stamp).'';
+    $logput->execute($did,$uid,$gid,$email,$name,'[V] '.$entry,$stamp);
+  }
+
+  print "\n done\n";
+}
+
+sub dogroup_add {
+  my $oldgrpid = shift;
+
+  $grpdata->execute($oldgrpid) or warn $grpdata->errstr;
+  my $oldgrp = $grpdata->fetchrow_hashref() or warn $grpdata->errstr;
+  print "group id $oldgrpid, name $oldgrp->{name}\n";
+
+  my $newgrpparent = $pcmap{g}{$oldgrp->{parent_group_id}};
+  $newgrpparent = 1 if $oldgrpid == 1;
+
+  # do in the same order as dnsdb
+  $newgrp_add->execute($newgrpparent, $oldgrp->{name});
+  $newgrpid->execute;
+  my ($newgid) = $newgrpid->fetchrow_array;
+  $pcmap{g}{$oldgrpid} = $newgid;
+
+  $grppget->execute($oldgrpid) or warn $grppget->errstr;
+  my $oldp = $grppget->fetchrow_hashref() or warn $grppget->errstr;
+
+  # de-nullify
+  foreach my $permtype ('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') {
+    $oldp->{$permtype} = 'f' if !defined($oldp->{$permtype});
+  }
+  $newgrppset->execute($oldp->{self_edit}, $oldp->{group_create}, $oldp->{group_edit}, $oldp->{group_delete},
+        $oldp->{user_create}, $oldp->{user_edit}, $oldp->{user_delete},
+        $oldp->{domain_create}, $oldp->{domain_edit}, $oldp->{domain_delete},
+        $oldp->{record_create}, $oldp->{record_edit}, $oldp->{record_delete},
+        $newgid) or warn $newgrppset->errstr;
+  $newpid->execute;
+  my ($pid) = $newpid->fetchrow_array;
+  $newpgidup->execute($pid,$newgid);
+
+ ## Users
+  print " users: ";
+  $userget->execute($oldgrpid);
+  while (my $user = $userget->fetchrow_hashref) {
+    # fiddle user data
+    $user->{account_type} = 'S' if $user->{account_type} eq 'senior_admin';
+    $user->{account_type} = 'u' if $user->{account_type} eq 'user';
+    $user->{status} = ($user->{status} eq 'active' ? 1 : 0);
+    $userpget->execute($user->{user_id}) or warn "failed permission get on ".$user->{user_id}."\n";
+
+    $newuser_add->execute($newgid, $user->{email}, $user->{password}, $user->{first_name},
+        $user->{last_name}, $user->{phone}, $user->{account_type}, $user->{status}) 
+        or warn "  new user ".$user->{email}." (".$user->{user_id}.") failed: ".$newuser->errstr."\n";
+    print " user ".$user->{email}." (".$user->{user_id}."), ";
+    $newuserid->execute;
+    my ($newuid) = $newuserid->fetchrow_array;
+    $pcmap{u}{$user->{user_id}} = $newuid;
+
+    my $oldp = $userpget->fetchrow_hashref;
+    # de-nullify
+    foreach my $permtype ('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') {
+      $oldp->{$permtype} = 'f' if !defined($oldp->{$permtype});
+    }
+    $newuserpset->execute(($user->{account_type} eq 'S' ? 't' : 'f'), $oldp->{self_edit},
+        $oldp->{group_create}, $oldp->{group_edit}, $oldp->{group_delete},
+        $oldp->{user_create}, $oldp->{user_edit}, $oldp->{user_delete},
+        $oldp->{domain_create}, $oldp->{domain_edit}, $oldp->{domain_delete},
+        $oldp->{record_create}, $oldp->{record_edit}, $oldp->{record_delete},
+        $newuid) or warn $newgrppset->errstr;
+    $newpid->execute;
+    my ($pid) = $newpid->fetchrow_array;
+    $newpuidup->execute($pid,$newuid);
+  }
+
+ ## Domains
+  print "\n domains: ";
+  $domget->execute($oldgrpid);
+  while (my ($id,$dom,$desc,$status) = $domget->fetchrow_array) {
+    $status = ($status eq 'active' ? 1 : 0);
+    $newdom_add->execute($dom, $newgid, $desc, $status);
+    print "$dom ($id), ";
+    $newdomid->execute;
+    my ($newdid) = $newdomid->fetchrow_array;
+    $pcmap{d}{$id} = $newdid;
+    $recget->execute($id);
+    while (my @rec = $recget->fetchrow_array) {
+      $rec[0] = $newdid;
+      $rec[2] = $reverse_typemap{$vegatypes{$rec[2]}};
+      $rec[4] = 0 if !$rec[4];
+      $rec[5] = 0 if !$rec[5];
+      $rec[6] = 0 if !$rec[6];
+      $newrec_add->execute(@rec);
+    }
+  }
+
+ ## Default records
+  print "\n default records: ";
+  $defrecget->execute(1);       # Vega 1.1.5/1.1.6 do not have default records for all groups;
+                                # there is only support for one set of default records coded.
+  while (my @rec = $defrecget->fetchrow_array) {
+    $rec[0] = $newgid;
+    $rec[2] = $reverse_typemap{$vegatypes{$rec[2]}};
+    $rec[4] = 0 if !$rec[4];
+    $rec[5] = 0 if !$rec[5];
+    $rec[6] = 0 if !$rec[6];
+    $newdefrec->execute(@rec);
+  }
+
+ ## Log entries
+  print "\n log entries: ";
+  $logget->execute($oldgrpid);
+  while (my ($did,$uid,$gid,$email,$name,$entry,$stamp) = $logget->fetchrow_array) {
+    $did = $pcmap{d}{$did};
+    $uid = $pcmap{u}{$uid};
+    $gid = $pcmap{g}{$gid};
+    $stamp = localtime($stamp).'';
+    $logput->execute($did,$uid,$gid,$email,$name,'[V] '.$entry,$stamp);
+  }
+
+  print "\n done\n";
+}