/**
name:SQLX_Insert
parm:string/array table
parm:array Row
parm:bool Rewrite_Skey
parm:bool Clip_Fields
returns:int
In its most basic form, this routine accepts a [[Row Array]]
and attempts to insert it into a table. Upon success, the routine
returns the skey value of the new row.
The first entry can be either a [[Table Reference]] or the name of
a table. The second entry is always a [[Row Array]]. This function
makes use of the dictionary to determine the correct formatting of all
columns, and ignores any column in the [[Row Array]] that is not
in the table.
The third parameter is used by the framework, and should always be
false. If the third parameter is set to true, then this routine
executes a [[gpSet]] with the value of skey for the new row, making
it look like this row came from the browser.
If the fourth parameter is true, values are clipped to column width
to prevent overflows. This almost guarantees the insert will succeed,
but should only be done if it is acceptable to throw away the ends of
columns.
*/
function SQLX_Insert($table, $colvals, $rewrite_skey = true, $clip = false)
{
# KFD 6/12/08, use new and improved
errorsClear();
if (!is_array($table)) {
$table = DD_TableRef($table);
}
$table_id = $table["table_id"];
$view_id = ddTable_idResolve($table_id);
$tabflat =& $table["flat"];
$new_cols = "";
$new_vals = "";
foreach ($tabflat as $colname => $colinfo) {
if (isset($colvals[$colname])) {
//if($colvals[$colname]<>'') {
if (DD_ColInsertsOK($colinfo, 'db')) {
# KFD 6/18/08, % signs really mess things up
#if(strpos($colvals[$colname],'%')!==false) {
# ErrorAdd("The % sign may not be in a saved value");
# vgfSet('ErrorRow_'.$table_id,$colvals);
# return 0;
#}
$cliplen = $clip ? $colinfo['colprec'] : 0;
$new_cols .= ListDelim($new_cols) . " " . $colname;
$new_vals .= ListDelim($new_vals) . " " . SQL_FORMAT($colinfo["type_id"], $colvals[$colname], $cliplen);
}
//}
}
}
if (!Errors()) {
$sql = "INSERT INTO " . $view_id . " ({$new_cols}) VALUES ({$new_vals})";
}
x4Debug($sql);
x4Debug(SessionGet('UID'));
// ERRORROW CHANGE 5/30/07, big change, SQLX_* routines now save
// the row for the table if there was an error
$errflag = false;
SQL($sql, $errflag);
if ($errflag) {
vgfSet('ErrorRow_' . $table_id, $colvals);
}
$notices = pg_last_notice($GLOBALS["dbconn"]);
$retval = 0;
$matches = array();
# KFD 10/18/08. This venerable line has been quietly working forever,
# until today! The problem turned out to be the table
# name had a number in it, which screwed it up! So
# I've changed one line here.
#preg_match_all("/SKEY(\D*)(\d*);/",$notices,$matches);
preg_match_all("/SKEY(.*\\s)(\\d*);/iu", $notices, $matches);
if (isset($matches[2][0])) {
$retval = $matches[2][0];
if ($rewrite_skey) {
gpSet("gp_skey", $matches[2][0]);
gpSet("gp_action", "edit");
}
}
// Possibly cache the row
$cache_pkey0 = vgfget('cache_pkey', array());
$cache_pkey = array_flip($cache_pkey0);
if (isset($cache_pkey[$table_id])) {
CacheRowPut($table, $colvals);
}
return $retval;
}
function mainDoit()
{
# Take the list of group assignments and reslot
# them into kills and changes.
$graw = aFromgp('grp_');
$gsame = array();
$gchg = array();
foreach ($graw as $from => $to) {
if ($from == $to) {
$gsame[] = "'{$to}'";
} else {
$gchg[$from] = $to;
}
}
# Step 1, make sure all users exist. Pull the ones
# that don't and create them
$users = SQL_AllRows("\n select user_id,member_password from users\n where COALESCE(member_password,'') <> ''\n AND not exists (\n select rolname from pg_roles\n where rolname = users.user_id::name\n )");
echo "<br/>Re-creating " . count($users) . " users.";
foreach ($users as $user) {
$pwd = $user['member_password'];
SQL("create role {$user['user_id']} login password '{$pwd}'");
}
# Step 1.5 set passwords and let them login
$users = SQL_AllRows("select user_id,member_password from users");
foreach ($users as $user) {
$pwd = $user['member_password'];
SQL("alter role {$user['user_id']} login password '{$pwd}'");
}
echo "<br/>Setting passwords for " . count($users) . " users.";
# Step 2, for all assignments that do not change,
# explicitly grant the role
$slist = implode(',', $gsame);
$assigns = SQL_AllRows("Select user_id,group_id \n from usersxgroups\n WHERE group_id in ({$slist})");
$count = 0;
foreach ($assigns as $assign) {
$count++;
SQL("grant {$assign['group_id']} to {$assign['user_id']}");
}
echo "<br/>{$count} users had existing permissions re-established";
errorsClear();
# Step 3, for all assignments that change,
# copy rows in usersxgroups, which also
# creates the role assignment
foreach ($gchg as $from => $to) {
$sql = "insert into usersxgroups (user_id,group_id)\n select user_id,'{$to}' FROM usersxgroups x\n where group_id = '{$from}'\n AND user_id in (Select rolname::varchar from pg_roles)\n and not exists (\n select * from usersxgroups x \n where user_id = x.user_id\n AND group_id= '{$to}'\n )";
SQL($sql);
}
echo "<br/>Migrated permissions for " . count($gchg) . " groups";
# Step 4, Delete all defunct user-group assignments
foreach ($gchg as $from => $to) {
SQL("Delete from usersxgroups where group_id = '{$from}'");
}
echo "<br/>Deleted old user-group rows for " . count($gchg) . " groups";
# Step 5, delete all defunct groups
echo "<br/>Deleted " . count($gchg) . " groups from old database";
foreach ($gchg as $from => $to) {
SQL("Delete from permxtables where group_id = '{$from}'");
SQL("Delete from uimenugroups where group_id = '{$from}'");
SQL("Delete from permxmodules where group_id = '{$from}'");
SQL("Delete from groups where group_id = '{$from}'");
}
}
