source: trunk/scripts/textblock-add-missing-security @ 1184

#!/usr/bin/env php
<?php
require_once(dirname($argv[0]) . "/utilities.php");
require_once(IA_ROOT_DIR . "www/format/format.php");

db_connect();

$invalid_security = "
    security NOT IN ('public', 'private', 'protected') AND
    security NOT LIKE 'task: %' AND
    security NOT LIKE 'round: %'
";
$query = "
SELECT name, timestamp, security FROM ia_textblock_revision WHERE
    ($invalid_security)
";
$textblocks = db_fetch_all($query);

foreach ($textblocks as $textblock) {
    if ($textblock["security"] !== "") {
        log_warn("Invalid textblock security for " . $textblock["name"]);
        continue;
    }

    # Fetch the security of the first revision which has security set
    $result = db_fetch(sprintf("
        SELECT security FROM ia_textblock_revision
        WHERE name = %s AND NOT (%s)
        ORDER BY timestamp ASC LIMIT 1
    ", db_quote($textblock["name"]), $invalid_security));

    if ($result === NULL) {
        # Fetch the security of the latest revision for the current page
        $result = db_fetch(sprintf(
            "SELECT security FROM ia_textblock WHERE name = %s",
            db_quote($textblock["name"])
        ));
        $new_security = array_pop($result);
    } else {
        $new_security = array_pop($result);
    }

    log_print(sprintf(
        "Changing '%s' revision from '%s' security to '%s'",
        $textblock["name"], format_date($textblock["timestamp"]),
        $new_security
    ));

    db_query(sprintf(
        "UPDATE ia_textblock_revision SET security = %s
         WHERE name = %s AND timestamp = %s",
        db_quote($new_security), db_quote($textblock["name"]),
        db_quote($textblock["timestamp"])
    ));
}

db_query("
ALTER TABLE `ia_textblock`
    MODIFY COLUMN `security` VARCHAR(64) NOT NULL
");

db_query("
ALTER TABLE `ia_textblock_revision`
    MODIFY COLUMN `name` VARCHAR(64) NOT NULL,
    MODIFY COLUMN `security` VARCHAR(64) NOT NULL
");
?>