#!/usr/bin/tclsh 

#
# pg_group v0.30
# A quick hack pg_group admin command line utility.
# I know it needs a lot of clean up. 
# Send any bugs, patches or total rewrites to
# jscottb@infoave.com
#

load libpgtcl[info sharedlibextension]

proc do_usage {} {
   puts "usage: pg_group options -- dB group \[user ...\] || \[table ...\]"
   puts "where options is one of:\n\
          -c create group\n\
          -d delete group\n\
          -a add user(s) to group\n\
          -r remove user(s) to group\n\
          +g give group access to tables\n\
          -g revoke group access to tables\n\
          -p privlages to grant/revoke.\
          This is only used with the +g and -g options.\n\
          -- end of switches.\n\n\
	 examples:\n\
         pg_group -c -- guestbook grp_gstbook_usr nobody tux\n\
         pg_group -a -- guestbook grp_gstbook_usr webuser\n\
         pg_group -d -- guestbook grp_gstbook_usr\n\
         pg_group -r -- guestbook grp_gstbook_usr nobody\n\
         pg_group +g -p \"insert,select\" -- guestbook grp_gstbook_usr gstbook\n\
         pg_group -g -p \"insert\" -- guestbook grp_gstbook_usr gstbook\n"
}

# main
   #
   # I need to Clean up here and do better on argv and argc
   # checking on each switch type.
   #
   
   if {$argc < 4} {
      do_usage
      exit 1
   }

   set option ""
   set privswt 0
   set optend 0   
   set cnt 0
   while {1} {
      if {$cnt > $argc} {
         break
      }
        
      set swtch [lindex $argv $cnt]
      incr cnt
      
      switch -- $swtch {
         "-c" {
	    set option "c"
	 }
	 
         "-a" {
	    set option "a"
	 }

         "-r" {
	    set option "r"
	 }

         "+g" {
	    set option "+"
	 }

         "-g" {
	    set option "-"
	 }

         "-p" {
	    set privswt 1
	    set privs [lindex $argv $cnt]
	    incr cnt
	 }

         "-d" {
	    set option "d"
	 }

         "--" {
            set optend 1
            break	    
	 }
      }
   }

   #
   # Yes, I took the easy way out.
   #

   if {!$optend} {
      puts "error '--' missing."
      do_usage
      exit 1
   }

   # Get the parms that we know should be there.
   set db [lindex $argv $cnt]
   incr cnt
   set group [lindex $argv $cnt]
   incr cnt
   
   set uids {}
   set dbhnd [pg_connect $db]
   
   #
   # From here on down needs to be switched out and
   # call a proc for each option.
   #
   
   # Create a group.
   if {$option == "c"} {
      # See if the group already exsists first.
      set Sql "select grosysid \
               from pg_group \
               where groname='$group'"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }
   
      if {[pg_result $ret -numTuples] >= 1} {
         puts "group $group already exsists, try -a option to add users."
	 exit 1
      }

      # Figure out what the next free group id is.
      set Sql "select max(grosysid) from pg_group"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }

      set gid [pg_result $ret -getTuple 0]
      
      # If we get a "{}" then that means there is no one in the group already.
      if {$gid == "{}" } {
         set gid -1
      }
      
      incr gid

      # Add the group to the dB.
      set Sql "insert into pg_group \
               values('$group', $gid, '{}')"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }
   }

   # Delete a group.
   if {$option == "d"} {
      # See if the group already exsists first.
      set Sql "select grosysid \
               from pg_group \
               where groname='$group'"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }
   
      if {[pg_result $ret -numTuples] == 0} {
         puts "group $group does not exsist in dB $db."
	 exit 1
      }

      pg_result $ret -clear
      
      # Revoke all to clean up the pg_class entry for each table.
      set Sql "select relname from pg_class"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }

      set numoftables [pg_result $ret -numTuples]
      set rowcnt 0
      while {1} {
         # Get table name.
         set tablename [pg_result $ret -getTuple $rowcnt]

         # Revoke all to clean up the pg_class entry.
         set Sql "revoke all on $tablename from group $group"
         set ret2 [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret2 -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }

         pg_result $ret2 -clear
	 
	 incr rowcnt
	 if {$rowcnt >= $numoftables} {
            break	 
	 }
      }

      pg_result $ret -clear
      
      # Delete the group from the dB.
      set Sql "delete from pg_group \
               where groname='$group'"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }
   }

   # Add to a group.
   if {$option == "a" || $option == "r"} {
      set Sql "select grolist \
               from pg_group \
               where groname='$group'"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
	 exit 1
      }
      
      set uids [pg_result $ret -getTuple 0]
      pg_result $ret -clear
      
      set uids [string trim $uids "{}"]
      if {$uids != ""} {
         set uids "$uids,"         
      }
   }

   if {$option != "+" && $option != "-" && $option != "r"} {
      # Process the user list.
      set new_uids {}
      while {1} {
         if {$cnt > $argc} {
            break
         }
        
         set user [lindex $argv $cnt]
         if {[string trim $user] == ""} {
            incr cnt
            continue      
         }
      
         incr cnt

         # Get the user id of the user to added.
         set Sql "select usesysid \
                  from pg_user \
                  where usename='$user'"
         set ret [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }

         # See if the user exsists.
         if {[pg_result $ret -numTuples] < 1} {
            puts "user $user does not exsits in $db."
            continue
         }

         set uid [pg_result $ret -getTuple 0]
         set noadd 0
       
         # See if the user is already in the group.
         set guids [split $uids ","]
         foreach guid $guids {
            set guid [string trim $guid]
            if {$guid == $uid} {
               puts "user $user already in group $group."
               set noadd 1
               break
            }
         }

         if {!$noadd} {
            append new_uids $uid ","
         }
       
         pg_result $ret -clear
      }

      # Remove the extra coma.
      set new_uids [string trimright $new_uids ", "]
      set uids [string trimright $uids ", "]
 
      set uids_forgrp ""
      if {$uids != ""} {
         set uids_forgrp $uids
      }
   
      if {$new_uids != ""} {
         append uids_forgrp "," $new_uids
      }

      set uids_forgrp [string trimleft $uids_forgrp ", "]
   
      # Update the group with the users.
      set Sql "update pg_group \
               set grolist='{$uids_forgrp}' \
               where groname='$group'"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
         exit 1
      }
   
      pg_result $ret -clear
   }

   # Revoke privs from a list of users.
   if {$option == "r"} {
      # Process the user list.
      set new_uids {}
      while {1} {
         if {$cnt > $argc} {
            break
         }
        
         set user [lindex $argv $cnt]
         if {[string trim $user] == ""} {
            incr cnt
            continue      
         }
      
         incr cnt

         # Get the user id of the user to added.
         set Sql "select usesysid \
                  from pg_user \
                  where usename='$user'"
         set ret [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }

         # See if the user exsists.
         if {[pg_result $ret -numTuples] < 1} {
            puts "user $user does not exsits in $db."
            continue
         }

         set uid [pg_result $ret -getTuple 0]
         pg_result $ret -clear

         # Remove the user from the list.
         regsub -all "$uid\,|$uid" $uids {} uids
      }	 

      # Remove the extra coma.
      set uids_forgrp [string trimright $uids ", "]
   
      # Update the group with the users.
      set Sql "update pg_group \
               set grolist='{$uids_forgrp}' \
               where groname='$group'"
      set ret [pg_exec $dbhnd $Sql]
 
      # Get the error result if any.
      set DbRet [pg_result $ret -error]
      if {$DbRet != ""} {
         puts $DbRet
         exit 1
      }
   
      pg_result $ret -clear
   }

   # Handle group granting.
   if {$option == "+"} {
      # Process the user list.
      set tables {}
      while {1} {
         if {$cnt > $argc} {
            break
         }
        
         set table [lindex $argv $cnt]
         if {[string trim $table] == ""} {
            incr cnt
            continue      
         }
      
         incr cnt

         # See if the table exsists.
         set Sql "select tablename \
                  from pg_tables \
                  where tablename='$table'"
         set ret [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }

         if {[pg_result $ret -numTuples] < 1} {
            puts "table $table does not exsist in $db."
            continue
         }

         # Get the user id of the user to added.
         set Sql "grant $privs on $table to group $group"
         set ret [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }
       
         pg_result $ret -clear
      }
   }

   # Handle group revoking.
   if {$option == "-"} {
      # Process the user list.
      set tables {}
      while {1} {
         if {$cnt > $argc} {
            break
         }
        
         set table [lindex $argv $cnt]
         if {[string trim $table] == ""} {
            incr cnt
            continue      
         }
      
         incr cnt

         # See if the table exsists.
         set Sql "select tablename \
                  from pg_tables \
                  where tablename='$table'"
         set ret [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }

         if {[pg_result $ret -numTuples] < 1} {
            puts "table $table does not exsist in $db."
            continue
         }

         # Get the user id of the user to added.
         set Sql "revoke $privs on $table from group $group"
         set ret [pg_exec $dbhnd $Sql]
 
         # Get the error result if any.
         set DbRet [pg_result $ret -error]
         if {$DbRet != ""} {
            puts $DbRet
            exit 1
         }
       
         pg_result $ret -clear
      }
   }

   exit 0
# end main