# some very odd value managed to sneak in
$cbgroups = Sql_Query("select id from {$tables["attribute"]} where type = \"checkboxgroup\"");
while ($row = Sql_Fetch_Row($cbgroups)) {
Sql_Query("update {$tables["user_attribute"]} set value = \"\" where attributeid = {$row['0']} and value=\"Empty\"");
}
case "2.6.0":
case "2.6.1":
case "2.6.2":
case "2.6.3":
case "2.6.4":
case "2.6.5":
Sql_Verbose_Query("alter table {$tables["message"]} add column embargo datetime");
Sql_Verbose_Query("alter table {$tables["message"]} add column repeat integer default 0");
Sql_Verbose_Query("alter table {$tables["message"]} add column repeatuntil datetime");
# make sure that current queued messages are sent
Sql_Verbose_Query("update {$tables["message"]} set embargo = now() where status = \"submitted\"");
Sql_Query("alter table {$tables["message"]} change column status status enum('submitted','inprocess','sent','cancelled','prepared','draft')");
case "2.6.6":
case "2.7.0":
case "2.7.1":
case "2.7.2":
Sql_Create_Table($tables["user_history"], $DBstruct["user_history"]);
case "2.8.0":
Sql_Query("alter table {$tables["message"]} add column textmessage text");
case "2.8.1":
case "2.8.2":
case "2.8.3":
case "2.8.4":
case "2.8.5":
case "2.8.6":
Sql_Query("alter table {$tables["user"]} add index index_uniqid (uniqid)");
<?php
require_once dirname(__FILE__) . '/accesscheck.php';
# convert the database to add the prefix
# dangerous, should not be used, particularly not if the prefix already exists
if (!$GLOBALS["table_prefix"]) {
print "No prefix defined, nothing to do";
return;
}
include "structure.php";
while (list($table, $value) = each($DBstruct)) {
if ($table != $tables[$table]) {
Sql_Drop_Table($tables[$table]);
Sql_Verbose_Query("alter table {$table} rename {$tables[$table]}", 0);
}
}
$req = Sql_Verbose_Query("select tablename from " . $tables["attribute"]);
while ($row = Sql_Fetch_Row($req)) {
set_time_limit(500);
if (Sql_Table_Exists("listattr_" . $row[0]) && $table_prefix) {
Sql_Verbose_Query("alter table listattr_{$row['0']} rename {$table_prefix}" . "listattr_" . $row[0]);
}
if (Sql_Table_Exists($table_prefix . "listattr_" . $row[0])) {
Sql_Query("alter table {$table_prefix}" . "listattr_" . $row[0] . " add column listorder integer default 0", 0);
}
}
flush();
$req = Sql_Verbose_Query("select {$tables['user_attribute']}.userid\n from {$tables['user_attribute']} left join {$tables['user']} on {$tables['user_attribute']}.userid = {$tables['user']}.id\n where {$tables['user']}.id IS NULL group by {$tables['user_attribute']}.userid");
print Sql_Affected_Rows() . ' ' . $GLOBALS['I18N']->get('entries apply') . '<br/>';
while ($row = Sql_Fetch_Row($req)) {
Sql_Query("delete from {$tables['user_attribute']} where userid = {$row['0']}");
}
$req = Sql_Verbose_Query("select {$tables['listuser']}.userid\n from {$tables['listuser']} left join {$tables['user']} on {$tables['listuser']}.userid = {$tables['user']}.id\n where {$tables['user']}.id IS NULL group by {$tables['listuser']}.userid");
print Sql_Affected_Rows() . ' ' . $GLOBALS['I18N']->get('entries apply') . '<br/>';
while ($row = Sql_Fetch_Row($req)) {
Sql_Query("delete from {$tables['listuser']} where userid = {$row['0']}");
}
$req = Sql_Verbose_Query("select {$tables['usermessage']}.userid\n from {$tables['usermessage']} left join {$tables['user']} on {$tables['usermessage']}.userid = {$tables['user']}.id\n where {$tables['user']}.id IS NULL group by {$tables['usermessage']}.userid");
print Sql_Affected_Rows() . ' ' . $GLOBALS['I18N']->get('entries apply') . '<br/>';
while ($row = Sql_Fetch_Row($req)) {
Sql_Query("delete from {$tables['usermessage']} where userid = {$row['0']}");
}
$req = Sql_Verbose_Query("select {$tables['user_message_bounce']}.user\n from {$tables['user_message_bounce']} left join {$tables['user']} on {$tables['user_message_bounce']}.user = {$tables['user']}.id\n where {$tables['user']}.id IS NULL group by {$tables['user_message_bounce']}.user");
print Sql_Affected_Rows() . ' ' . $GLOBALS['I18N']->get('entries apply') . '<br/>';
while ($row = Sql_Fetch_Row($req)) {
Sql_Query("delete from {$tables['user_message_bounce']} where user = {$row['0']}");
}
}
$table_list = $tables['user'] . $findtables;
if ($find) {
$listquery = "select {$tables['user']}.id,{$findfield},{$tables['user']}.confirmed from " . $table_list . " where {$findbyselect}";
$count = Sql_query('SELECT count(*) FROM ' . $table_list . " where {$findbyselect}");
$unconfirmedcount = Sql_query('SELECT count(*) FROM ' . $table_list . " where !confirmed && {$findbyselect}");
if ($_GET['unconfirmed']) {
$listquery .= ' and !confirmed';
}
} else {
$listquery = "select {$tables['user']}.id,{$findfield},{$tables['user']}.confirmed from " . $table_list;
$req = Sql_Query("select id,$item from ".$tables["user"]);
while ($user = Sql_Fetch_Row($req))
Sql_Query(sprintf('insert into %s values(%d,%d,"%s")',$tables["user_attribute"],$attribute_id,$user[0],addslashes($user[1])));
Sql_Verbose_Query("alter table {$tables["user"]} drop column $item");
flush();
}
}
$success = $success && upgradeTable($tables[$table],$DBstruct[$table]);
flush();
}
Sql_Verbose_Query("update {$tables["user"]} set confirmed = 1");
$req = Sql_Query("select tablename from ".$tables["attribute"]);
while ($row = Sql_Fetch_Row($req)) {
set_time_limit(500);
if (Sql_Table_Exists("listattr_".$row[0]) && $table_prefix)
Sql_Verbose_Query("alter table listattr_$row[0] rename $table_prefix"."listattr_".$row[0]);
if (Sql_Table_Exists($table_prefix."listattr_".$row[0]))
Sql_Query("alter table $table_prefix"."listattr_".$row[0]." add column listorder integer default 0",0);
}
flush();
# something we should have done ages ago. make checkboxes save "on" value in user_attribute
$req = Sql_Query("select * from {$tables["attribute"]} where type = \"checkbox\"");
while ($row = Sql_Fetch_Array($req)) {
$req2 = Sql_Query("select * from $table_prefix"."listattr_$row[tablename]");
while ($row2 = Sql_Fetch_array($req2)) {
if ($row2["name"] == "Checked")
Sql_Query(sprintf('update %s set value = "on" where attributeid = %d and value = %d',
$tables["user_attribute"],$row["id"],$row2["id"]));
}
Sql_Query(sprintf('update %s set value = "" where attributeid = %d and value != "on"',
# insert javascript to require them, except for hidden ones :-)
if ($_POST["type"]["id"] == "hidden") {
Sql_Query("update {$tables['attribute']} set required = 0 where id = {$insertid}");
}
}
if ($_POST["type"][$id] == "checkbox") {
# with a checkbox we know the values
Sql_Query('insert into ' . $table_prefix . 'adminattr_' . $lc_name . ' (name) values("Checked")');
Sql_Query('insert into ' . $table_prefix . 'adminattr_' . $lc_name . ' (name) values("Unchecked")');
# we cannot "require" checkboxes, that does not make sense
Sql_Query("update {$tables['adminattribute']} set required = 0 where id = {$insertid}");
}
} elseif ($_POST["name"][$id] != "") {
# it is a change
$query = sprintf('update %s set name = "%s" ,listorder = %d,default_value = "%s" ,required = %d where id = %d', $tables["adminattribute"], addslashes($_POST["name"][$id]), $_POST["listorder"][$id], $_POST["default"][$id], $_POST["required"][$id], $id);
Sql_Verbose_Query($query);
}
}
if (isset($_POST["delete"])) {
while (list($id, $val) = each($_POST["delete"])) {
$res = Sql_Query("select tablename,type from {$tables['adminattribute']} where id = {$id}");
$row = Sql_Fetch_Row($res);
if ($row[1] != "hidden" && $row[1] != "textline") {
Sql_Query("drop table {$table_prefix}" . "adminattr_{$row['0']}");
}
Sql_Query("delete from {$tables['adminattribute']} where id = {$id}");
# delete all admin attributes as well
Sql_Query("delete from {$tables['admin_attribute']} where adminattributeid = {$id}");
}
}
}
print '<h2>Add HEX Test column</h2>';
## add another column for the test value
Sql_Query('alter table phplist_user_user add column hexemailtest varchar(255) not null default ""');
Sql_Query('alter table phplist_user_user add index hexemailtestidx (hexemailtest)');
Sql_Query('update phplist_user_user set hexemailtest = concat(hexemail,"0D")');
## this should render no results
#select u1.id,u2.id,hex(u1.email),hex(u2.email) from phplist_user_user u1 left join phplist_user_user u2 on u1.hexemail = u2.hexemail where u1.id != u2.id;
# find the ones that match on email with 0D added (\r -> carriage return)
print '<h2>Merge on CR</h2>';
$req = Sql_Verbose_Query('select u1.id,u2.id from phplist_user_user u1
left join phplist_user_user u2 on u1.hexemailtest = u2.hexemail where u1.id != u2.id');
while ($row = Sql_Fetch_Row($req)) {
mergeUsers($row[0], $row[1]);
}
print '<h2>Merge on NL</h2>';
# find the ones that match on email with 0A added (\n -> newline)
Sql_Query('update phplist_user_user set hexemailtest = concat(hexemail,"0A")');
$req = Sql_Verbose_Query('select u1.id,u2.id from phplist_user_user u1
left join phplist_user_user u2 on u1.hexemailtest = u2.hexemail where u1.id != u2.id');
while ($row = Sql_Fetch_Row($req)) {
mergeUsers($row[0], $row[1]);
}
print '<h2>Merge on TAB</h2>';
# find the ones that match on email with 09 added (\t -> tab)
Sql_Query('update phplist_user_user set hexemailtest = concat(hexemail,"09")');
$req = Sql_Verbose_Query('select u1.id,u2.id from phplist_user_user u1
left join phplist_user_user u2 on u1.hexemailtest = u2.hexemail where u1.id != u2.id');
while ($row = Sql_Fetch_Row($req)) {
mergeUsers($row[0], $row[1]);
}
Sql_Query('alter table phplist_user_user drop index hexemailidx, drop index hexemailtestidx, drop column hexemail, drop column hexemailtest');
} else {
$listorder = $maxitem[0] + 1;
# One more than the maximun
}
while (list($key, $val) = each($items)) {
$val = clean($val);
if ($val != "") {
$query = sprintf('INSERT into %s (name,listorder) values("%s","%s")', $table, $val, $listorder);
$result = Sql_query($query);
}
$listorder++;
}
}
if (isset($_POST["listorder"]) && is_array($_POST["listorder"])) {
foreach ($_POST["listorder"] as $key => $val) {
Sql_Verbose_Query("update {$table} set listorder = {$val} where id = {$key}");
}
}
function giveAlternative($table, $delete, $attributeid)
{
print $GLOBALS['I18N']->get('ReplaceAllWith') . formStart();
print '<select name=replace><option value="0">-- ' . $GLOBALS['I18N']->get('ReplaceWith') . '</option>';
$req = Sql_Query("select * from {$table} order by listorder,name");
while ($row = Sql_Fetch_array($req)) {
if ($row["id"] != $delete) {
printf('<option value="%d">%s</option>', $row["id"], $row["name"]);
}
}
print "</select>";
printf('<input type=hidden name="delete" value="%d">', $delete);
printf('<input type=hidden name="id" value="%d">', $attributeid);
function upgradeTable2($table, $tablestructure)
{
global $tmpdir;
$filename = "phpupgrade.tmp";
if (is_file("{$tmpdir}/{$filename}")) {
unlink("{$tmpdir}/{$filename}");
}
if (Sql_Verbose_Query("select * from {$table} into outfile '{$tmpdir}/{$filename}'")) {
Sql_Query("drop table {$table}");
Sql_Create_table($table, $tablestructure);
Sql_Query("load data infile '{$tmpdir}/{$filename}' into {$table} IGNORE");
} else {
print '<p>Error: Cannot dump old database tables. Please make sure you have the correct Database and File permissions.</p>
<p>Required permissions are:
<ul>
<li>Mysql "alter" and "file" permission on your database</li>
<li>Write permission in ' . $tmpdir . '</li>
</ul>';
}
if (is_file("{$tmpdir}/{$filename}")) {
unlink("{$tmpdir}/{$filename}");
}
}
# some very odd value managed to sneak in
$cbgroups = Sql_Query("select id from {$tables["attribute"]} where type = \"checkboxgroup\"");
while ($row = Sql_Fetch_Row($cbgroups)) {
Sql_Query("update {$tables["user_attribute"]} set value = \"\" where attributeid = {$row['0']} and value=\"Empty\"");
}
case "2.6.0":
case "2.6.1":
case "2.6.2":
case "2.6.3":
case "2.6.4":
case "2.6.5":
Sql_Verbose_Query("alter table {$tables["message"]} add column embargo datetime");
Sql_Verbose_Query("alter table {$tables["message"]} add column repeat integer default 0");
Sql_Verbose_Query("alter table {$tables["message"]} add column repeatuntil datetime");
# make sure that current queued messages are sent
Sql_Verbose_Query("update {$tables["message"]} set embargo = current_timestamp where status = \"submitted\"");
Sql_Query("alter table {$tables["message"]} change column status status enum('submitted','inprocess','sent','cancelled','prepared','draft')");
case "2.6.6":
case "2.7.0":
case "2.7.1":
case "2.7.2":
Sql_Create_Table($tables["user_history"], $DBstruct["user_history"]);
case "2.8.0":
Sql_Query("alter table {$tables["message"]} add column textmessage text");
case "2.8.1":
case "2.8.2":
case "2.8.3":
case "2.8.4":
case "2.8.5":
case "2.8.6":
Sql_Query("alter table {$tables["user"]} add index index_uniqid (uniqid)");