/** Note that you should call the first instance of AbstractDb as soon as possible to get reliable SQL vs PHP statistics.*/
public static function &getObject()
{
if (self::$object == null) {
self::$object = new self();
}
return self::$object;
}
/**
* Constructor
*
* @param string $content_id Content id */
protected function __construct($content_id)
{
$db = AbstractDb::getObject();
// Init values
$row = null;
parent::__construct($content_id);
$content_id = $db->escapeString($content_id);
$sql = "SELECT * FROM content_file_image WHERE pictures_id='{$content_id}'";
$db->execSqlUniqueRes($sql, $row, false);
if ($row == null) {
/*
* Since the parent Content exists, the necessary data in
* content_group had not yet been created
*/
$sql = "INSERT INTO content_file_image (pictures_id) VALUES ('{$content_id}')";
$db->execSqlUpdate($sql, false);
$sql = "SELECT * FROM content_file_image WHERE pictures_id='{$content_id}'";
$db->execSqlUniqueRes($sql, $row, false);
if ($row == null) {
throw new Exception(_("The content with the following id could not be found in the database: ") . $content_id);
}
}
$this->mBd =& $db;
$this->pictures_row = $row;
$this->configEnableEditFilename(false);
$this->configEnableEditMimeType(false);
}
/**
* Constructor
*
* @return void
*/
public function __construct(&$network)
{
$db = AbstractDb::getObject();
// Init network
$this->_network = $network;
$this->_csv_document = "";
// Query the database, sorting by node name
$db->execSql("SELECT *, (CURRENT_TIMESTAMP-last_heartbeat_timestamp) AS since_last_heartbeat, EXTRACT(epoch FROM creation_date) as creation_date_epoch, CASE WHEN ((CURRENT_TIMESTAMP-last_heartbeat_timestamp) < interval '5 minutes') THEN true ELSE false END AS is_up FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE') ORDER BY lower(name)", $this->_nodes, false);
}
function PhpCIdb() {
parent::AbstractDb();
//load the database settings from the config file
//uses $this->db for convenience
$CI =& get_instance();
$CI->load->database();
$this->db = &$CI->db;
$this->base_path = $CI->config->item('absolute_path');
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$node_usage_stats = null;
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT connections.{$distinguish_users_by}, connections.node_id, nodes.name, date_trunc('day', timestamp_in) as rounded_date");
//$db->execSql($candidate_connections_sql, $tmp, true);
$daily_visit_table_name = "daily_visit_table_name_" . session_id();
$daily_visit_table_sql = "CREATE TEMP TABLE {$daily_visit_table_name} AS ({$candidate_connections_sql});\n \n";
$daily_visit_table_sql .= "CREATE INDEX {$daily_visit_table_name}_idx ON {$daily_visit_table_name} (node_id)";
$db->execSqlUpdate($daily_visit_table_sql, false);
$daily_visit_table_sql = "SELECT COUNT ({$distinguish_users_by}) AS total_visits, node_id, name FROM {$daily_visit_table_name} GROUP BY node_id, name ORDER BY total_visits DESC;";
$db->execSql($daily_visit_table_sql, $node_usage_stats, false);
$daily_visit_table_sql = "DROP TABLE {$daily_visit_table_name};";
$db->execSqlUpdate($daily_visit_table_sql, false);
// $candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("nodes.name,connections.node_id,COUNT(connections.node_id) AS connections ");
// $sql = "$candidate_connections_sql GROUP BY connections.node_id,nodes.name ORDER BY connections DESC";
// $db->execSql($sql, $node_usage_stats, false);
if ($node_usage_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= " <th>" . _("Node") . "</th>";
$html .= " <th>" . _("Visits") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$total = 0;
$even = 0;
foreach ($node_usage_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['name']}</td>\n";
$html .= " <td>" . $row['total_visits'] . "</td>";
$html .= "</tr>";
$total += $row['total_visits'];
}
$html .= "<tfoot>";
$html .= "<tr>";
$html .= " <th>" . _("Total") . ":</th>";
$html .= " <th>" . $total . "</th>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <td colspan=2>" . _("Note: A visit is like counting connections, but only counting one connection per day for each user at a single node") . ":</td>";
$html .= "</tr>";
$html .= "</tfoot>";
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
function CompostDb() {
parent::AbstractDb();
$CI =& get_instance();
if($CI->config->item('use_txt_db')){
$CI->load->library('phptxtdb');
$this->db = &$CI->phptxtdb;
} else {
$CI->load->library('phpcidb');
$this->db = &$CI->phpcidb;
}
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$graph = StatisticGraph::getObject('ConnectionsPerHour');
$html .= $graph->getReportUI($this->stats);
$graph = StatisticGraph::getObject('VisitsPerWeekday');
$html .= $graph->getReportUI($this->stats);
$graph = StatisticGraph::getObject('VisitsPerMonth');
$html .= $graph->getReportUI($this->stats);
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery(" connections.{$distinguish_users_by}, SUM(incoming+outgoing) AS total, SUM(incoming) AS total_incoming, SUM(outgoing) AS total_outgoing ", false);
$sql = "{$candidate_connections_sql} GROUP BY connections.{$distinguish_users_by} ORDER BY total DESC LIMIT " . self::NUM_USERS_TO_DISPLAY . "";
$db->execSql($sql, $frequent_users_stats, false);
if ($frequent_users_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$caption = _("User (username)");
} else {
$caption = _("User (MAC address)");
}
$html .= " <th>{$caption}</th>";
$html .= " <th>" . _("Incoming") . "</th>";
$html .= " <th>" . _("Outgoing") . "</th>";
$html .= " <th>" . _("Total") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
foreach ($frequent_users_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
if (!empty($row['user_id'])) {
$user = User::getObject($row['user_id']);
$display_id = $user->getUsername();
} else {
//We only have a MAC address
$display_id = $row['user_mac'];
}
$html .= " <td>{$display_id}</a></td>\n";
$html .= " <td>" . Utils::convertBytesToWords($row['total_incoming']) . "</td>";
$html .= " <td>" . Utils::convertBytesToWords($row['total_outgoing']) . "</td>";
$html .= " <td>" . Utils::convertBytesToWords($row['total']) . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT {$distinguish_users_by}, date_trunc('day', timestamp_in) AS date");
$sql = "SELECT COUNT(*) AS active_days, {$distinguish_users_by} FROM ({$candidate_connections_sql} GROUP BY date,{$distinguish_users_by}) AS user_active_days GROUP BY {$distinguish_users_by} ORDER BY active_days DESC LIMIT " . self::NUM_USERS_TO_DISPLAY . "";
$db->execSql($sql, $frequent_users_stats, false);
if ($frequent_users_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$caption = _("User (username)");
} else {
$caption = _("User (MAC address)");
}
$html .= " <th>{$caption}</th>";
$html .= " <th>" . _("Different days connected") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
foreach ($frequent_users_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
if (!empty($row['user_id'])) {
$user = User::getObject($row['user_id']);
$display_id = $user->getUsername();
} else {
//We only have a MAC address
$display_id = $row['user_mac'];
}
$html .= " <td>{$display_id}</a></td>\n";
//$html .= " <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
$html .= " <td>" . $row['active_days'] . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("COUNT(DISTINCT connections.node_id) AS num_hotspots_visited, {$distinguish_users_by}");
$sql = "{$candidate_connections_sql} GROUP BY {$distinguish_users_by} ORDER BY num_hotspots_visited DESC LIMIT " . self::NUM_USERS_TO_DISPLAY . "";
$db->execSql($sql, $mobile_users_stats, false);
if ($mobile_users_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
if ($distinguish_users_by == 'user_id') {
$caption = _("User (username)");
} else {
$caption = _("User (MAC address)");
}
$html .= " <th>{$caption}</th>";
$html .= " <th>" . _("Nodes visited") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$even = 0;
foreach ($mobile_users_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
if (!empty($row['user_id'])) {
$user = User::getObject($row['user_id']);
$display_id = $user->getUsername();
} else {
//We only have a MAC address
$display_id = $row['user_mac'];
}
$html .= " <td>{$display_id}</a></td>\n";
//$html .= " <td><a href='?date_from={$_REQUEST['date_from']}&date_to={$_REQUEST['date_to']}&user_id={$row['user_id']}'>{$row['username']}</a></td>\n";
$html .= " <td>" . $row['num_hotspots_visited'] . "</td>";
$html .= "</tr>";
}
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
return parent::getReportUI($html);
}
/** Return the actual Image data
* Classes must override this.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function showImageData()
{
require_once "Image/Graph.php";
$db = AbstractDb::getObject();
$Graph =& Image_Graph::factory("Image_Graph", array(600, 200));
$Plotarea =& $Graph->add(Image_Graph::factory("Image_Graph_Plotarea"));
$Dataset =& Image_Graph::factory("Image_Graph_Dataset_Trivial");
$Bar =& Image_Graph::factory("Image_Graph_Plot_Bar", $Dataset);
$Bar->setFillColor("#9db8d2");
$Plot =& $Plotarea->add($Bar);
$candidate_connections_sql = self::$stats->getSqlCandidateConnectionsQuery("COUNT(distinct user_mac) AS connections, extract('hour' from timestamp_in) AS hour");
$db->execSql("{$candidate_connections_sql} GROUP BY hour ORDER BY hour", $results, false);
if ($results) {
foreach ($results as $row) {
$Dataset->addPoint($row['hour'] . "h", $row['connections']);
}
}
$Graph->done();
}
/** Return the actual Image data
* Classes must override this.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function showImageData()
{
require_once "Image/Graph.php";
$db = AbstractDb::getObject();
$Graph =& Image_Graph::factory("Image_Graph", array(600, 200));
$Plotarea =& $Graph->add(Image_Graph::factory("Image_Graph_Plotarea"));
$Dataset =& Image_Graph::factory("Image_Graph_Dataset_Trivial");
$Bar =& Image_Graph::factory("Image_Graph_Plot_Bar", $Dataset);
$Bar->setFillColor("#9db8d2");
$Plot =& $Plotarea->add($Bar);
$total = 0;
$network_constraint = self::$stats->getSqlNetworkConstraint('account_origin');
$date_constraint = self::$stats->getSqlDateConstraint('reg_date');
$db->execSql("SELECT COUNT(users) AS num_users, date_trunc('month', reg_date) AS month FROM users WHERE account_status = " . ACCOUNT_STATUS_ALLOWED . " {$date_constraint} {$network_constraint} GROUP BY date_trunc('month', reg_date) ORDER BY month", $registration_stats, false);
if ($registration_stats) {
foreach ($registration_stats as $row) {
$Dataset->addPoint(substr($row['month'], 0, 7), $row['num_users']);
}
}
$Graph->done();
}
function page_if_down_since($nodeObject, $minutes)
{
$db = AbstractDb::getObject();
$last_heartbeat = strtotime($nodeObject->getLastHeartbeatTimestamp());
$time = time();
$downtime = round((time() - $last_heartbeat) / 60, 0);
$paged_addresses = "";
if ($time - $last_heartbeat > 60 * $minutes) {
//If hostpot is down for longuer than the requested average interval
$lastPaged = strtotime($nodeObject->getLastPaged());
//echo sprintf("Node down for %f minutes, Last paged: %f minutes ago, difference between last page and last heartbeat: %s, difference must be less than %d minutes to page again <br/>\n", ($time-$last_heartbeat)/60, ($time-$lastPaged)/60, ($lastPaged - $last_heartbeat)/60, $minutes);
if (!$nodeObject->getLastPaged() || !$lastPaged) {
//If we can't retrieve or parse, pretend we last paged right before the hostpot went down
$lastPaged = $last_heartbeat - 1;
}
if ($lastPaged < $last_heartbeat + 60 * $minutes) {
//If we haven't paged since the downtime reached the threshold
$network = $nodeObject->getNetwork();
$nodeObject->setLastPaged(time());
$usersToPage = $nodeObject->DEPRECATEDgetTechnicalOfficers();
$usersMsg = null;
foreach ($usersToPage as $officer) {
# Doesn't work if called from cron
#Locale :: setCurrentLocale(Locale::getObject($officer->getPreferedLocale()));
$mail = new Mail();
$mail->setSenderName(_("Monitoring system"));
$mail->setSenderEmail($network->getTechSupportEmail());
$mail->setRecipientEmail($officer->getEmail());
$mail->setMessageSubject($minutes . " - " . $network->getName() . " " . _("node") . " " . $nodeObject->getName());
$mail->setHighPriority(true);
$mail->setMessageBody(sprintf(_("Node %s (%s) has been down for %d minutes (since %s)"), $nodeObject->getName(), $nodeObject->getId(), $minutes, date("r", $last_heartbeat)));
$mailRetval = $mail->send();
$usersMsg .= sprintf("%s: %s", $officer->getUsername(), $mailRetval ? _("Success") : _("Failed sending mail")) . "\n";
}
$msg = sprintf("Node %s has been DOWN for %d minutes, we mailed the following %d user(s):\n%s", $nodeObject->getName(), ($time - $last_heartbeat) / 60, count($usersToPage), $usersMsg);
throw new exception($msg);
}
throw new exception(sprintf("Node %s has been DOWN for %d minutes (more than %d minutes), but we already notified everyone after %d minutes." . "\n", $nodeObject->getName(), ($time - $last_heartbeat) / 60, $minutes, ($lastPaged - $last_heartbeat) / 60));
}
}
/**
* Try to bring the database schema up to date.
* @param $schema_version The target schema
* @return void
*/
function real_update_schema($targetSchema)
{
$sql = '';
$db = AbstractDb::getObject();
$db->execSqlUniqueRes("SELECT * FROM schema_info WHERE tag='schema_version'", $row, false);
//Re-check the schema version, it could have been updated by another thread
$schema_version = $row['value'];
$new_schema_version = 2;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE users ADD COLUMN username text;\n";
$sql .= "ALTER TABLE users ADD COLUMN account_origin text;\n";
$db->execSql("SELECT user_id FROM users", $results, false);
foreach ($results as $row) {
$user_id = $db->escapeString($row['user_id']);
$sql .= "UPDATE users SET username='******', user_id='" . get_guid() . "', account_origin='LOCAL_USER' WHERE user_id='{$user_id}';\n";
}
$sql .= "CREATE UNIQUE INDEX idx_unique_username_and_account_origin ON users (username, account_origin);\n";
$sql .= "CREATE UNIQUE INDEX idx_unique_email_and_account_origin ON users USING btree (email, account_origin);\n";
}
$new_schema_version = 3;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "DROP INDEX idx_unique_email_and_account_origin;\n";
$sql .= "ALTER TABLE users DROP CONSTRAINT check_email_not_empty;\n";
}
$new_schema_version = 4;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE users ALTER COLUMN account_origin SET NOT NULL;\n";
$sql .= "ALTER TABLE users ADD CONSTRAINT check_account_origin_not_empty CHECK (account_origin::text <> ''::text);\n";
// We must skip all other updates because schema 5 must be manually updated:
$schema_version = 1000000;
}
$new_schema_version = 5;
if ($schema_version == $new_schema_version - 1) {
echo "<h1>Recoding database from ISO-8859-1 to UTF-8</h1>";
echo "<h1>YOU MUST EXECUTE THESE COMMANDS FROM THE COMMAND_LINE:</h1>";
echo "pg_dump wifidog -U wifidog > wifidog_dump.sql;<br>";
echo "dropdb wifidog -U wifidog; <br>";
echo "createdb --encoding=UNICODE --template=template0 -U wifidog wifidog;<br>";
echo "psql wifidog -U wifidog < wifidog_dump.sql;<br>\n";
echo " (Note: You may ignore the following errors: \"ERROR: permission denied to set session authorization\" and \"ERROR: must be owner of schema public\")<br>";
echo "psql wifidog -U wifidog -c \"UPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version'\";<br>";
exit;
}
$new_schema_version = 6;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE locales ( \n";
$sql .= " locales_id text PRIMARY KEY \n";
$sql .= " ); \n";
$sql .= " INSERT INTO locales VALUES ('fr'); \n";
$sql .= " INSERT INTO locales VALUES ('en'); \n";
$sql .= "ALTER TABLE users ADD COLUMN never_show_username bool;\n";
$sql .= "ALTER TABLE users ALTER COLUMN never_show_username SET DEFAULT FALSE;\n";
$sql .= "ALTER TABLE users ADD COLUMN real_name text;\n";
$sql .= "ALTER TABLE users ALTER COLUMN real_name SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE users ADD COLUMN website text;\n";
$sql .= "ALTER TABLE users ALTER COLUMN website SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE users ADD COLUMN prefered_locale text REFERENCES locales ON DELETE SET NULL ON UPDATE CASCADE;\n";
$sql .= "\n CREATE TABLE content\n (\n content_id text NOT NULL PRIMARY KEY,\n content_type text NOT NULL CONSTRAINT content_type_not_empty_string CHECK (content_type != ''),\n title text REFERENCES content ON DELETE RESTRICT ON UPDATE CASCADE,\n description text REFERENCES content ON DELETE RESTRICT ON UPDATE CASCADE,\n project_info text REFERENCES content ON DELETE RESTRICT ON UPDATE CASCADE,\n sponsor_info text REFERENCES content ON DELETE RESTRICT ON UPDATE CASCADE,\n creation_timestamp timestamp DEFAULT now()\n );\n \n CREATE TABLE content_has_owners\n (\n content_id text NOT NULL REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n user_id text NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,\n is_author bool NOT NULL,\n owner_since timestamp DEFAULT now(),\n PRIMARY KEY (content_id, user_id)\n );\n \n CREATE TABLE langstring_entries (\n langstring_entries_id text NOT NULL PRIMARY KEY,\n langstrings_id text REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n locales_id text REFERENCES locales ON DELETE RESTRICT ON UPDATE CASCADE,\n value text DEFAULT ''\n );\n \n CREATE TABLE content_group (\n content_group_id text NOT NULL PRIMARY KEY REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n is_artistic_content bool NOT NULL DEFAULT FALSE,\n is_locative_content bool NOT NULL DEFAULT FALSE,\n content_selection_mode text\n );\n \n CREATE TABLE content_group_element (\n content_group_element_id text NOT NULL PRIMARY KEY REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n content_group_id text NOT NULL REFERENCES content_group ON DELETE CASCADE ON UPDATE CASCADE,\n display_order integer DEFAULT '1',\n displayed_content_id text REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n force_only_allowed_node bool\n );\n CREATE INDEX idx_content_group_element_content_group_id ON content_group_element (content_group_id);\n \n CREATE TABLE content_group_element_has_allowed_nodes\n (\n content_group_element_id text NOT NULL REFERENCES content_group_element ON DELETE CASCADE ON UPDATE CASCADE,\n node_id text NOT NULL REFERENCES nodes ON DELETE CASCADE ON UPDATE CASCADE,\n allowed_since timestamp DEFAULT now(),\n PRIMARY KEY (content_group_element_id, node_id)\n );\n \n CREATE TABLE content_group_element_portal_display_log (\n user_id text NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,\n content_group_element_id text NOT NULL REFERENCES content_group_element ON DELETE CASCADE ON UPDATE CASCADE,\n display_timestamp timestamp NOT NULL DEFAULT now(),\n node_id text REFERENCES nodes ON DELETE CASCADE ON UPDATE CASCADE,\n PRIMARY KEY (user_id,content_group_element_id, display_timestamp)\n );\n \n CREATE TABLE user_has_content (\n user_id text NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,\n content_id text NOT NULL REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n subscribe_timestamp timestamp NOT NULL DEFAULT now(),\n PRIMARY KEY (user_id,content_id)\n );\n \n CREATE TABLE node_has_content (\n node_id text NOT NULL REFERENCES nodes ON DELETE CASCADE ON UPDATE CASCADE,\n content_id text NOT NULL REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n subscribe_timestamp timestamp NOT NULL DEFAULT now(),\n PRIMARY KEY (node_id,content_id)\n );\n \n CREATE TABLE network_has_content (\n network_id text NOT NULL,\n content_id text NOT NULL REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,\n subscribe_timestamp timestamp NOT NULL DEFAULT now(),\n PRIMARY KEY (network_id,content_id)\n );";
}
$new_schema_version = 7;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content ADD COLUMN is_persistent bool;\n";
$sql .= "ALTER TABLE content ALTER COLUMN is_persistent SET DEFAULT FALSE;\n";
}
$new_schema_version = 8;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE flickr_photostream\n (\n flickr_photostream_id text NOT NULL,\n api_key text,\n photo_selection_mode text NOT NULL DEFAULT 'PSM_GROUP'::text,\n user_id text,\n user_name text,\n tags text,\n tag_mode varchar(10) DEFAULT 'any'::character varying,\n group_id text,\n random bool NOT NULL DEFAULT true,\n min_taken_date timestamp,\n max_taken_date timestamp,\n photo_batch_size int4 DEFAULT 10,\n photo_count int4 DEFAULT 1,\n display_title bool NOT NULL DEFAULT true,\n display_description bool NOT NULL DEFAULT false,\n display_tags bool NOT NULL DEFAULT false,\n CONSTRAINT flickr_photostream_pkey PRIMARY KEY (flickr_photostream_id),\n CONSTRAINT flickr_photostream_content_group_fkey FOREIGN KEY (flickr_photostream_id) REFERENCES content_group (content_group_id) ON UPDATE CASCADE ON DELETE CASCADE\n );";
}
$new_schema_version = 9;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE files\n (\n files_id text NOT NULL,\n filename text,\n mime_type text,\n binary_data bytea,\n remote_size bigint,\n CONSTRAINT files_pkey PRIMARY KEY (files_id)\n );";
}
$new_schema_version = 10;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE files ADD COLUMN url text;";
$sql .= "ALTER TABLE flickr_photostream ADD COLUMN preferred_size text;";
$sql .= "CREATE TABLE embedded_content (\n embedded_content_id text NOT NULL,\n embedded_file_id text,\n fallback_content_id text,\n parameters text,\n attributes text\n );";
}
$new_schema_version = 11;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "DROP TABLE content_group_element_portal_display_log;\n";
$sql .= "CREATE TABLE content_display_log\n (\n user_id text NOT NULL REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,\n content_id text NOT NULL REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE,\n first_display_timestamp timestamp NOT NULL DEFAULT now(),\n node_id text NOT NULL REFERENCES nodes ON UPDATE CASCADE ON DELETE CASCADE,\n last_display_timestamp timestamp NOT NULL DEFAULT now(),\n CONSTRAINT content_group_element_portal_display_log_pkey PRIMARY KEY (user_id, content_id, node_id)\n ); \n";
}
$new_schema_version = 12;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE flickr_photostream DROP CONSTRAINT flickr_photostream_content_group_fkey;";
$sql .= "ALTER TABLE flickr_photostream ADD CONSTRAINT flickr_photostream_content_fkey FOREIGN KEY (flickr_photostream_id) REFERENCES content (content_id) ON UPDATE CASCADE ON DELETE CASCADE;";
}
$new_schema_version = 13;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content_group DROP COLUMN content_selection_mode;\n";
$sql .= "ALTER TABLE content_group ADD COLUMN content_changes_on_mode text;\n";
$sql .= "UPDATE content_group SET content_changes_on_mode='ALWAYS';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN content_changes_on_mode SET DEFAULT 'ALWAYS';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN content_changes_on_mode SET NOT NULL;\n";
$sql .= "ALTER TABLE content_group ADD COLUMN content_ordering_mode text;\n";
$sql .= "UPDATE content_group SET content_ordering_mode='RANDOM';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN content_ordering_mode SET DEFAULT 'RANDOM';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN content_ordering_mode SET NOT NULL;\n";
$sql .= "ALTER TABLE content_group ADD COLUMN display_num_elements int;\n";
$sql .= "UPDATE content_group SET display_num_elements=1;\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN display_num_elements SET DEFAULT '1';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN display_num_elements SET NOT NULL;\n";
$sql .= "ALTER TABLE content_group ADD CONSTRAINT display_at_least_one_element CHECK (display_num_elements > 0);\n";
$sql .= "ALTER TABLE content_group ADD COLUMN allow_repeat text;\n";
$sql .= "UPDATE content_group SET allow_repeat='YES';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN allow_repeat SET DEFAULT 'YES';\n";
$sql .= "ALTER TABLE content_group ALTER COLUMN allow_repeat SET NOT NULL;\n";
}
$new_schema_version = 14;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE pictures " . "( " . "pictures_id text NOT NULL PRIMARY KEY REFERENCES files ON DELETE CASCADE ON UPDATE CASCADE, " . "width int4, " . "height int4" . ");\n";
}
$new_schema_version = 15;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE files ADD COLUMN data_blob oid;\n ALTER TABLE files ADD COLUMN local_binary_size int8;\n ALTER TABLE files DROP COLUMN binary_data;\n";
}
$new_schema_version = 16;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE flickr_photostream ADD COLUMN requests_cache text; \n";
$sql .= "ALTER TABLE flickr_photostream ADD COLUMN cache_update_timestamp timestamp; \n";
}
$new_schema_version = 17;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN max_monthly_incoming int8; \n";
$sql .= "ALTER TABLE nodes ADD COLUMN max_monthly_outgoing int8; \n";
$sql .= "ALTER TABLE nodes ADD COLUMN quota_reset_day_of_month int4; \n";
}
$new_schema_version = 18;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content ADD COLUMN long_description text REFERENCES content ON DELETE RESTRICT ON UPDATE CASCADE;\n";
}
$new_schema_version = 19;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE iframes (";
$sql .= "iframes_id text NOT NULL PRIMARY KEY REFERENCES content ON DELETE CASCADE ON UPDATE CASCADE,";
$sql .= "url text,";
$sql .= "width int4,";
$sql .= "height int4";
$sql .= ");\n";
}
$new_schema_version = 20;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN latitude NUMERIC(16, 6);\n";
$sql .= "ALTER TABLE nodes ADD COLUMN longitude NUMERIC(16, 6);\n";
}
$new_schema_version = 21;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE content_rss_aggregator \n";
$sql .= "( \n";
$sql .= "content_id text NOT NULL PRIMARY KEY REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "number_of_display_items integer NOT NULL DEFAULT 10, \n";
$sql .= "algorithm_strength real NOT NULL DEFAULT 0.75, \n";
$sql .= "max_item_age interval DEFAULT NULL \n";
$sql .= "); \n";
$sql .= "CREATE TABLE content_rss_aggregator_feeds \n";
$sql .= "( ";
$sql .= "content_id text NOT NULL REFERENCES content_rss_aggregator ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "url text, \n";
$sql .= "bias real NOT NULL DEFAULT 1, \n";
$sql .= "default_publication_interval int DEFAULT NULL, \n";
$sql .= "PRIMARY KEY(content_id, url) \n";
$sql .= "); \n";
$sql .= "ALTER TABLE content_has_owners ALTER COLUMN is_author SET DEFAULT 'f';\n";
$results = null;
$db->execSql("SELECT node_id, rss_url FROM nodes", $results, false);
foreach ($results as $row) {
if (!empty($row['rss_url'])) {
$content_id = get_guid();
$sql .= "\nINSERT INTO content (content_id, content_type) VALUES ('{$content_id}', 'RssAggregator');\n";
$sql .= "INSERT INTO content_rss_aggregator (content_id) VALUES ('{$content_id}');\n";
$sql .= "INSERT INTO content_rss_aggregator_feeds (content_id, url) VALUES ('{$content_id}', '" . $row['rss_url'] . "');\n";
$node = Node::getObject($row['node_id']);
$db->execSql("SELECT user_id FROM node_stakeholders WHERE is_owner = true AND node_id='" . $node->getId() . "'", $ownersRow, false);
if ($ownersRow != null) {
foreach ($ownersRow as $owner_row) {
$sql .= "INSERT INTO content_has_owners (content_id, user_id) VALUES ('{$content_id}', '" . $owner_row['user_id'] . "');\n";
}
}
$sql .= "INSERT INTO node_has_content (content_id, node_id) VALUES ('{$content_id}', '" . $row['node_id'] . "');\n";
}
}
$sql .= "\nALTER TABLE nodes DROP COLUMN rss_url;\n";
$sql .= "\nDELETE FROM content WHERE content_type='HotspotRss';\n";
}
$new_schema_version = 22;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN civic_number text;\n";
// Dropping street_address and copying data to street_name for the sake of backward compatibility
$sql .= "ALTER TABLE nodes ADD COLUMN street_name text;\n";
$sql .= "UPDATE nodes SET street_name = street_address;\n";
$sql .= "ALTER TABLE nodes DROP COLUMN street_address;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN city text;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN province text;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN country text;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN postal_code text;\n";
}
$new_schema_version = 23;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE node_tech_officers (\n";
$sql .= " node_id VARCHAR(32) REFERENCES nodes (node_id),\n";
$sql .= " user_id VARCHAR(45) REFERENCES users (user_id),\n";
$sql .= "PRIMARY KEY (node_id, user_id)\n";
$sql .= ");\n";
}
$new_schema_version = 24;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content_rss_aggregator_feeds ADD COLUMN title text; \n";
}
$new_schema_version = 25;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE node_stakeholders ( \n";
$sql .= " node_id VARCHAR(32) REFERENCES nodes (node_id),\n";
$sql .= " user_id VARCHAR(45) REFERENCES users (user_id),\n";
$sql .= " is_owner BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= " is_tech_officer BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= "PRIMARY KEY (node_id, user_id)\n";
$sql .= ");\n";
$sql .= "INSERT INTO node_stakeholders (node_id, user_id) SELECT node_id, user_id FROM node_owners UNION SELECT node_id, user_id FROM node_tech_officers;\n";
$sql .= "UPDATE node_stakeholders SET is_owner = true FROM node_owners WHERE node_stakeholders.node_id = node_owners.node_id AND node_stakeholders.user_id = node_owners.user_id;\n";
$sql .= "UPDATE node_stakeholders SET is_tech_officer = true FROM node_tech_officers WHERE node_stakeholders.node_id = node_tech_officers.node_id AND node_stakeholders.user_id = node_tech_officers.user_id;";
$sql .= "DROP TABLE node_owners;\n";
$sql .= "DROP TABLE node_tech_officers;\n";
}
$new_schema_version = 26;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE networks ( \n";
$sql .= " network_id text NOT NULL PRIMARY KEY,\n";
$sql .= " network_authenticator_class text NOT NULL CHECK (network_authenticator_class<>''),\n";
$sql .= " network_authenticator_params text,\n";
$sql .= " is_default_network boolean NOT NULL DEFAULT FALSE,\n";
$sql .= " name text NOT NULL DEFAULT 'Unnamed network' CHECK (name<>''),\n";
$sql .= " creation_date date NOT NULL DEFAULT now(),\n";
$sql .= " homepage_url text,\n";
$sql .= " tech_support_email text,\n";
$sql .= " validation_grace_time interval NOT NULL DEFAULT '1200 seconds',\n";
$sql .= " validation_email_from_address text NOT NULL CHECK (validation_email_from_address<>'') DEFAULT 'validation@wifidognetwork',\n";
$sql .= " allow_multiple_login BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= " allow_splash_only_nodes BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= " allow_custom_portal_redirect BOOLEAN NOT NULL DEFAULT FALSE\n";
$sql .= ");\n";
$sql .= "INSERT INTO networks (network_id, network_authenticator_class, network_authenticator_params) SELECT account_origin, COALESCE('AuthenticatorLocalUser') as network_authenticator_class, '\\'' || account_origin || '\\'' FROM users GROUP BY (account_origin) ORDER BY min(reg_date);\n";
$sql .= "UPDATE networks SET is_default_network=TRUE WHERE network_id=(SELECT account_origin FROM users GROUP BY (account_origin) ORDER BY min(reg_date) LIMIT 1);\n";
$sql .= "ALTER TABLE users ADD CONSTRAINT account_origin_fkey FOREIGN KEY (account_origin) REFERENCES networks (network_id) ON UPDATE CASCADE ON DELETE RESTRICT;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN network_id text; \n";
$sql .= "UPDATE nodes SET network_id=(SELECT account_origin FROM users GROUP BY (account_origin) ORDER BY min(reg_date) LIMIT 1);\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN network_id SET NOT NULL; \n";
$sql .= "ALTER TABLE nodes ADD CONSTRAINT network_id_fkey FOREIGN KEY (network_id) REFERENCES networks ON UPDATE CASCADE ON DELETE RESTRICT;\n";
$sql .= "ALTER TABLE network_has_content ADD CONSTRAINT network_id_fkey FOREIGN KEY (network_id) REFERENCES networks ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "CREATE TABLE network_stakeholders ( \n";
$sql .= " network_id text REFERENCES networks,\n";
$sql .= " user_id VARCHAR(45) REFERENCES users,\n";
$sql .= " is_admin BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= " is_stat_viewer BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= "PRIMARY KEY (network_id, user_id)\n";
$sql .= ");\n";
}
$new_schema_version = 27;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN last_paged timestamp;\n";
}
$new_schema_version = 28;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN is_splash_only_node boolean;\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN is_splash_only_node SET DEFAULT FALSE;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN custom_portal_redirect_url text;\n";
}
$new_schema_version = 29;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE flickr_photostream ADD COLUMN api_shared_secret text;\n";
}
$new_schema_version = 30;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE INDEX idx_connections_user_id ON connections (user_id);\n";
$sql .= "CREATE INDEX idx_connections_user_mac ON connections (user_mac);\n";
$sql .= "CREATE INDEX idx_connections_node_id ON connections (node_id);\n";
}
$new_schema_version = 31;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE content_display_location ( \n";
$sql .= " display_location text NOT NULL PRIMARY KEY\n";
$sql .= ");\n";
$sql .= "INSERT INTO content_display_location (display_location) VALUES ('portal_page');\n";
$sql .= "INSERT INTO content_display_location (display_location) VALUES ('login_page');\n";
$sql .= "ALTER TABLE network_has_content ADD COLUMN display_location text;\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_location SET DEFAULT 'portal_page';\n";
$sql .= "UPDATE network_has_content SET display_location='portal_page';\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_location SET NOT NULL;\n";
$sql .= "ALTER TABLE network_has_content ADD CONSTRAINT display_location_fkey FOREIGN KEY (display_location) REFERENCES content_display_location ON UPDATE CASCADE ON DELETE RESTRICT;\n";
$sql .= "ALTER TABLE node_has_content ADD COLUMN display_location text;\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_location SET DEFAULT 'portal_page';\n";
$sql .= "UPDATE node_has_content SET display_location='portal_page';\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_location SET NOT NULL;\n";
$sql .= "ALTER TABLE node_has_content ADD CONSTRAINT display_location_fkey FOREIGN KEY (display_location) REFERENCES content_display_location ON UPDATE CASCADE ON DELETE RESTRICT;\n";
/* Convert the existing node logos */
$results = null;
$db->execSql("SELECT node_id FROM nodes", $results, false);
define('HOTSPOT_LOGO_NAME', 'hotspot_logo.jpg');
foreach ($results as $row) {
$php_logo_path = WIFIDOG_ABS_FILE_PATH . LOCAL_CONTENT_REL_PATH . $row['node_id'] . '/' . HOTSPOT_LOGO_NAME;
if (file_exists($php_logo_path)) {
$node_logo_abs_url = $db->escapeString(BASE_URL_PATH . LOCAL_CONTENT_REL_PATH . $row['node_id'] . '/' . HOTSPOT_LOGO_NAME);
$content_id = get_guid();
$sql .= "\nINSERT INTO content (content_id, content_type) VALUES ('{$content_id}', 'Picture');\n";
$sql .= "INSERT INTO files (files_id, url) VALUES ('{$content_id}', '{$node_logo_abs_url}');\n";
$sql .= "INSERT INTO pictures (pictures_id) VALUES ('{$content_id}');\n";
$node = Node::getObject($row['node_id']);
$db->execSql("SELECT user_id FROM node_stakeholders WHERE is_owner = true AND node_id='" . $node->getId() . "'", $ownersRow, false);
if ($ownersRow != null) {
foreach ($ownersRow as $owner_row) {
$sql .= "INSERT INTO content_has_owners (content_id, user_id) VALUES ('{$content_id}', '" . $owner_row['user_id'] . "');\n";
}
}
$sql .= "INSERT INTO node_has_content (content_id, node_id, display_location) VALUES ('{$content_id}', '" . $row['node_id'] . "', 'login_page');\n";
}
}
}
$new_schema_version = 32;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "INSERT INTO locales VALUES ('de');\n";
}
$new_schema_version = 33;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE flickr_photostream ADD COLUMN photo_display_mode text;\n";
$sql .= "ALTER TABLE flickr_photostream ALTER COLUMN photo_display_mode SET DEFAULT 'PDM_GRID'::text;\n";
$sql .= "UPDATE flickr_photostream SET photo_display_mode = 'PDM_GRID';\n";
$sql .= "ALTER TABLE flickr_photostream ALTER COLUMN photo_display_mode SET NOT NULL;\n";
}
$new_schema_version = 34;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE node_stakeholders DROP CONSTRAINT \"\$1\";\n";
$sql .= "ALTER TABLE node_stakeholders ADD CONSTRAINT nodes_fkey FOREIGN KEY (node_id) REFERENCES nodes(node_id) ON UPDATE CASCADE ON DELETE CASCADE;";
}
$new_schema_version = 35;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE servers ( \n";
$sql .= " server_id text NOT NULL PRIMARY KEY,\n";
$sql .= " is_default_server boolean NOT NULL DEFAULT FALSE,\n";
$sql .= " name text NOT NULL DEFAULT 'Unnamed server' CHECK (name<>''),\n";
$sql .= " creation_date date NOT NULL DEFAULT now(),\n";
$sql .= " hostname text NOT NULL DEFAULT 'localhost' CHECK (name<>''),\n";
$sql .= " ssl_available BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= " gmaps_api_key text\n";
$sql .= ");\n";
$sql .= "INSERT INTO servers (server_id, is_default_server, name, creation_date, hostname, ssl_available, gmaps_api_key) VALUES ('" . str_replace(".", "-", $_SERVER['SERVER_NAME']) . "', TRUE, 'Unnamed server', (SELECT creation_date FROM networks GROUP BY (creation_date) ORDER BY min(creation_date) LIMIT 1), '{$_SERVER['SERVER_NAME']}', " . (defined("SSL_AVAILABLE") ? SSL_AVAILABLE ? "TRUE" : "FALSE" : "FALSE") . ", " . (defined("GMAPS_PUBLIC_API_KEY") ? "'" . GMAPS_PUBLIC_API_KEY . "'" : "''") . ");\n";
$sql .= "ALTER TABLE networks ADD COLUMN gmaps_initial_latitude NUMERIC(16, 6);\n";
$sql .= "ALTER TABLE networks ADD COLUMN gmaps_initial_longitude NUMERIC(16, 6);\n";
$sql .= "ALTER TABLE networks ADD COLUMN gmaps_initial_zoom_level integer;\n";
$sql .= "ALTER TABLE networks ADD COLUMN gmaps_map_type text CHECK (gmaps_map_type<>'');\n";
$sql .= "ALTER TABLE networks ALTER COLUMN gmaps_map_type SET DEFAULT 'G_MAP_TYPE';\n";
$sql .= "UPDATE networks SET gmaps_map_type = 'G_MAP_TYPE';\n";
$sql .= "ALTER TABLE networks ALTER COLUMN gmaps_map_type SET NOT NULL;\n";
$sql .= "UPDATE networks SET gmaps_initial_latitude = " . (defined("GMAPS_INITIAL_LATITUDE") ? "'" . GMAPS_INITIAL_LATITUDE . "'" : "'45.494511'") . ", gmaps_initial_longitude = " . (defined("GMAPS_INITIAL_LONGITUDE") ? "'" . GMAPS_INITIAL_LONGITUDE . "'" : "'-73.560285'") . ", gmaps_initial_zoom_level = " . (defined("GMAPS_INITIAL_ZOOM_LEVEL") ? "'" . GMAPS_INITIAL_ZOOM_LEVEL . "'" : "'5'") . ";\n";
}
$new_schema_version = 36;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "INSERT INTO locales VALUES ('pt');\n";
}
$new_schema_version = 37;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE flickr_photostream RENAME TO content_flickr_photostream;\n";
$sql .= "ALTER TABLE embedded_content RENAME TO content_embedded_content;\n";
$sql .= "ALTER TABLE files RENAME TO content_file;\n";
$sql .= "ALTER TABLE iframes RENAME TO content_iframe;\n";
$sql .= "ALTER TABLE langstring_entries RENAME TO content_langstring_entries;\n";
$sql .= "ALTER TABLE pictures RENAME TO content_file_image;\n";
$sql .= "ALTER TABLE content_display_location RENAME TO content_available_display_pages; \n";
$sql .= "ALTER TABLE content_available_display_pages RENAME COLUMN display_location TO display_page; \n";
$sql .= "UPDATE content_available_display_pages SET display_page = 'login' WHERE display_page = 'login_page';\n";
$sql .= "UPDATE content_available_display_pages SET display_page = 'portal' WHERE display_page = 'portal_page';\n";
$sql .= "INSERT INTO content_available_display_pages (display_page) VALUES ('everywhere');\n";
$sql .= "CREATE TABLE content_available_display_areas ( display_area text PRIMARY KEY);\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('page_header');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('page_footer');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('left_area-top');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('left_area_middle');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('left_area_bottom');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('main_area_top');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('main_area_middle');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('main_area_bottom');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('right_area_top');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('right_area_middle');\n";
$sql .= "INSERT INTO content_available_display_areas (display_area) VALUES ('right_area_bottom');\n";
$sql .= "ALTER TABLE network_has_content RENAME COLUMN display_location TO display_page;\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_page SET DEFAULT 'portal'::text;\n";
$sql .= "ALTER TABLE network_has_content ADD COLUMN display_area text REFERENCES content_available_display_areas ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "UPDATE network_has_content SET display_area = 'main_area_middle';\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_area SET DEFAULT 'main_area_middle'::text;\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_area SET NOT NULL;\n";
$sql .= "ALTER TABLE network_has_content ADD COLUMN display_order integer;\n";
$sql .= "UPDATE network_has_content SET display_order = 1;\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_order SET DEFAULT 1;\n";
$sql .= "ALTER TABLE network_has_content ALTER COLUMN display_order SET NOT NULL;\n";
$sql .= "ALTER TABLE node_has_content RENAME COLUMN display_location TO display_page;\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_page SET DEFAULT 'portal'::text;\n";
$sql .= "ALTER TABLE node_has_content ADD COLUMN display_area text REFERENCES content_available_display_areas ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "UPDATE node_has_content SET display_area = 'main_area_middle';\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_area SET DEFAULT 'main_area_middle'::text;\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_area SET NOT NULL;ALTER TABLE node_has_content ADD COLUMN display_order integer;\n";
$sql .= "UPDATE node_has_content SET display_order = 1;\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_order SET DEFAULT 1;\n";
$sql .= "ALTER TABLE node_has_content ALTER COLUMN display_order SET NOT NULL;\n";
}
$new_schema_version = 38;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "UPDATE content_available_display_areas SET display_area='left_area_top' WHERE display_area='left_area-top';\n";
}
$new_schema_version = 39;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
// Update to new Gmaps v2 constants
$sql .= "ALTER TABLE networks ALTER COLUMN gmaps_map_type SET DEFAULT 'G_NORMAL_MAP'::text;\n";
$sql .= "UPDATE networks SET gmaps_map_type='G_NORMAL_MAP' WHERE gmaps_map_type = 'G_MAP_TYPE'; \n";
$sql .= "UPDATE networks SET gmaps_map_type='G_HYBRID_MAP' WHERE gmaps_map_type = 'G_HYBRID_TYPE'; \n";
$sql .= "UPDATE networks SET gmaps_map_type='G_SATELLITE_MAP' WHERE gmaps_map_type = 'G_SATELLITE_TYPE'; \n";
// Use formula given here : http://www.google.com/apis/maps/documentation/upgrade.html#Upgrade
$sql .= "UPDATE networks SET gmaps_initial_zoom_level = 17 - gmaps_initial_zoom_level; \n";
}
$new_schema_version = 40;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE networks ADD COLUMN theme_pack text;\n";
$sql .= "ALTER TABLE networks ALTER COLUMN theme_pack SET DEFAULT NULL;\n";
}
$new_schema_version = 41;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "INSERT INTO locales (locales_id) VALUES('ja');\n";
}
$new_schema_version = 42;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content_file ADD COLUMN creation_date TIMESTAMP;\n";
$sql .= "ALTER TABLE content_file ALTER COLUMN creation_date SET DEFAULT NOW();\n";
$sql .= "ALTER TABLE content_file ADD COLUMN last_update_date TIMESTAMP;\n";
$sql .= "ALTER TABLE content_file ALTER COLUMN last_update_date SET DEFAULT NOW();\n";
$sql .= "UPDATE content_file SET creation_date = NOW(), last_update_date = NOW();\n";
$sql .= "ALTER TABLE content_file_image ADD COLUMN hyperlink_url TEXT;\n";
}
$new_schema_version = 43;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE content_clickthrough_log ( \n";
$sql .= " user_id text REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= " content_id text NOT NULL REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= " clickthrough_timestamp timestamp NOT NULL DEFAULT now(),\n";
$sql .= " node_id text NOT NULL REFERENCES nodes ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= " destination_url text NOT NULL CHECK (destination_url<>'')\n";
$sql .= ");\n";
}
$new_schema_version = 44;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "INSERT INTO locales (locales_id) VALUES('es');\n";
}
$new_schema_version = 45;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content DROP COLUMN sponsor_info;\n";
$sql .= "ALTER TABLE users DROP CONSTRAINT account_origin_fkey;\n";
$sql .= "ALTER TABLE users ADD CONSTRAINT account_origin_fkey FOREIGN KEY (account_origin) REFERENCES networks (network_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE nodes DROP CONSTRAINT network_id_fkey;\n";
$sql .= "ALTER TABLE nodes ADD CONSTRAINT network_id_fkey FOREIGN KEY (network_id) REFERENCES networks (network_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE users DROP CONSTRAINT check_account_origin_not_empty;\n";
$sql .= "ALTER TABLE connections DROP CONSTRAINT fk_nodes;\n";
$sql .= "ALTER TABLE connections ADD CONSTRAINT fk_nodes FOREIGN KEY (node_id) REFERENCES nodes (node_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE connections DROP CONSTRAINT fk_users;\n";
$sql .= "ALTER TABLE connections ADD CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE node_stakeholders DROP CONSTRAINT \"\$2\";\n";
$sql .= "ALTER TABLE node_stakeholders ADD CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE network_stakeholders DROP CONSTRAINT \"\$1\";\n";
$sql .= "ALTER TABLE network_stakeholders ADD CONSTRAINT fk_network FOREIGN KEY (network_id) REFERENCES networks (network_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE network_stakeholders DROP CONSTRAINT \"\$2\";\n";
$sql .= "ALTER TABLE network_stakeholders ADD CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE content ADD COLUMN title_is_displayed bool;\n";
$sql .= "ALTER TABLE content ALTER COLUMN title_is_displayed SET DEFAULT true;\n";
$sql .= "UPDATE content SET title_is_displayed=true;\n";
$sql .= "ALTER TABLE content ALTER COLUMN title_is_displayed SET NOT NULL;\n\n";
}
$new_schema_version = 46;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content_group_element ADD COLUMN valid_from_timestamp timestamp;\n";
$sql .= "ALTER TABLE content_group_element ADD COLUMN valid_until_timestamp timestamp;\n";
$sql .= "CREATE INDEX idx_content_group_element_valid_from_timestamp ON content_group_element (valid_from_timestamp);\n";
$sql .= "CREATE INDEX idx_content_group_element_valid_until_timestamp ON content_group_element (valid_until_timestamp);\n";
}
$new_schema_version = 47;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE users DROP COLUMN real_name;\n";
$sql .= "ALTER TABLE users DROP COLUMN website;\n";
$sql .= "ALTER TABLE content_display_log ADD COLUMN num_display integer;\n";
$sql .= "UPDATE content_display_log SET num_display=1;\n";
$sql .= "ALTER TABLE content_display_log ALTER COLUMN num_display SET NOT NULL;\n";
$sql .= "ALTER TABLE content_display_log ALTER COLUMN num_display SET DEFAULT 1;\n";
$sql .= "ALTER TABLE content_clickthrough_log ADD COLUMN num_clickthrough integer;\n";
$sql .= "UPDATE content_clickthrough_log SET num_clickthrough=1;\n";
$sql .= "ALTER TABLE content_clickthrough_log ALTER COLUMN num_clickthrough SET NOT NULL;\n";
$sql .= "ALTER TABLE content_clickthrough_log ALTER COLUMN num_clickthrough SET DEFAULT 1;\n";
$sql .= "ALTER TABLE content_clickthrough_log RENAME COLUMN clickthrough_timestamp TO first_clickthrough_timestamp;\n";
$sql .= "ALTER TABLE content_clickthrough_log ADD COLUMN last_clickthrough_timestamp timestamp;\n";
$sql .= "UPDATE content_clickthrough_log SET last_clickthrough_timestamp=first_clickthrough_timestamp;\n";
$sql .= "ALTER TABLE content_clickthrough_log ALTER COLUMN last_clickthrough_timestamp SET NOT NULL;\n";
$sql .= "ALTER TABLE content_clickthrough_log ALTER COLUMN last_clickthrough_timestamp SET DEFAULT NOW();\n";
}
$new_schema_version = 48;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "DELETE FROM content_clickthrough_log WHERE user_id IS NULL;\n";
$sql .= "ALTER TABLE content_clickthrough_log ALTER COLUMN user_id SET NOT NULL;\n";
$results = null;
$db->execSql("SELECT COUNT(*) as num_clickthrough, MIN(first_clickthrough_timestamp) as first_clickthrough_timestamp, MAX(last_clickthrough_timestamp) as last_clickthrough_timestamp, user_id, content_id, node_id, destination_url FROM content_clickthrough_log GROUP BY user_id, content_id, node_id, destination_url HAVING COUNT(*) > 1", $results, false);
if ($results) {
foreach ($results as $row) {
$sql .= "DELETE FROM content_clickthrough_log WHERE user_id='{$row['user_id']}' AND content_id='{$row['content_id']}' AND node_id='{$row['node_id']}' AND destination_url='{$row['destination_url']}';\n";
if (!empty($row['user_id'])) {
$sql .= "INSERT INTO content_clickthrough_log (num_clickthrough, first_clickthrough_timestamp, last_clickthrough_timestamp, user_id, content_id, node_id, destination_url) VALUES ({$row['num_clickthrough']}, '{$row['first_clickthrough_timestamp']}', '{$row['last_clickthrough_timestamp']}', '{$row['user_id']}', '{$row['content_id']}', '{$row['node_id']}', '{$row['destination_url']}');\n";
}
}
}
$sql .= "ALTER TABLE content_clickthrough_log ADD CONSTRAINT content_clickthrough_log_pkey PRIMARY KEY(content_id, user_id, node_id, destination_url);\n";
$sql .= "ALTER TABLE content_display_log DROP CONSTRAINT content_group_element_portal_display_log_pkey;\n";
$sql .= "ALTER TABLE content_display_log ADD CONSTRAINT content_display_log_pkey PRIMARY KEY(content_id, user_id, node_id);\n";
}
$new_schema_version = 49;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE content_rss_aggregator ADD COLUMN feed_expansion text;\n";
$sql .= "ALTER TABLE content_rss_aggregator ALTER COLUMN feed_expansion SET DEFAULT 'FIRST';\n";
$sql .= "UPDATE content_rss_aggregator SET feed_expansion='FIRST';\n";
$sql .= "ALTER TABLE content_rss_aggregator ALTER COLUMN feed_expansion SET NOT NULL;\n";
$sql .= "ALTER TABLE content_rss_aggregator ADD COLUMN feed_ordering text;\n";
$sql .= "ALTER TABLE content_rss_aggregator ALTER COLUMN feed_ordering SET DEFAULT 'REVERSE_DATE';\n";
$sql .= "UPDATE content_rss_aggregator SET feed_ordering='REVERSE_DATE';\n";
$sql .= "ALTER TABLE content_rss_aggregator ALTER COLUMN feed_ordering SET NOT NULL;\n";
$sql .= "ALTER TABLE content_rss_aggregator ADD COLUMN display_empty_feeds boolean;\n";
$sql .= "ALTER TABLE content_rss_aggregator ALTER COLUMN display_empty_feeds SET DEFAULT TRUE;\n";
$sql .= "UPDATE content_rss_aggregator SET display_empty_feeds=TRUE;\n";
$sql .= "ALTER TABLE content_rss_aggregator ALTER COLUMN display_empty_feeds SET NOT NULL;\n";
}
$new_schema_version = 50;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN gw_id text;\n";
$sql .= "UPDATE nodes SET gw_id=node_id;\n";
$db->execSql("SELECT node_id FROM nodes", $results, false);
foreach ($results as $row) {
$sql .= "UPDATE nodes SET node_id='" . get_guid() . "' WHERE node_id='{$row['node_id']}';\n";
}
$sql .= "ALTER TABLE nodes ALTER COLUMN gw_id SET NOT NULL;\n";
$sql .= "CREATE UNIQUE INDEX idx_gw_id ON nodes (gw_id);\n";
}
$new_schema_version = 51;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE content_key_value_pairs \n";
$sql .= "( ";
$sql .= "content_id text NOT NULL REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "key text NOT NULL, \n";
$sql .= "value text, \n";
$sql .= "PRIMARY KEY(content_id, key) \n";
$sql .= "); \n";
$sql .= "ALTER TABLE content ADD COLUMN last_update_timestamp timestamp;\n";
$sql .= "ALTER TABLE content ALTER COLUMN last_update_timestamp SET DEFAULT now();\n";
$sql .= "UPDATE content SET last_update_timestamp=CURRENT_TIMESTAMP;\n";
$sql .= "UPDATE content SET last_update_timestamp=last_update_date FROM content_file WHERE content_id=files_id;\n";
$sql .= "ALTER TABLE content ALTER COLUMN last_update_timestamp SET NOT NULL;\n";
$sql .= "ALTER TABLE content_file DROP COLUMN last_update_date;\n";
$sql .= "ALTER TABLE content_file DROP COLUMN creation_date;\n";
$sql .= "ALTER TABLE content_group DROP COLUMN is_artistic_content;\n";
$sql .= "ALTER TABLE content_group DROP COLUMN is_locative_content;\n";
}
$new_schema_version = 52;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE content_shoutbox_messages \n";
$sql .= "( \n";
$sql .= "message_content_id text PRIMARY KEY REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "shoutbox_id text NOT NULL REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "origin_node_id text NOT NULL REFERENCES nodes ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "author_user_id text NOT NULL REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "creation_date timestamp DEFAULT now() \n";
$sql .= "); \n";
}
$new_schema_version = 53;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE content_type_filters \n";
$sql .= "( \n";
$sql .= "content_type_filter_id text NOT NULL PRIMARY KEY,\n";
$sql .= "content_type_filter_label text,\n";
$sql .= "content_type_filter_rules text NOT NULL CONSTRAINT content_type_filter_rules_not_empty_string CHECK (content_type_filter_rules != '')\n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE profile_templates \n";
$sql .= "( \n";
$sql .= "profile_template_id text NOT NULL PRIMARY KEY, \n";
$sql .= "profile_template_label text,\n";
$sql .= "creation_date timestamp DEFAULT now()\n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE profile_template_fields \n";
$sql .= "( \n";
$sql .= "profile_template_field_id text NOT NULL PRIMARY KEY,\n";
$sql .= "profile_template_id text NOT NULL REFERENCES profile_templates ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "display_label_content_id text REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "admin_label_content_id text REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "content_type_filter_id text REFERENCES content_type_filters ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "display_order integer DEFAULT '1',\n";
$sql .= "semantic_id text\n";
$sql .= ");\n";
$sql .= "CREATE INDEX profile_template_fields_semantic_id ON profile_template_fields(semantic_id);\n\n";
$sql .= "CREATE TABLE network_has_profile_templates \n";
$sql .= "( \n";
$sql .= "network_id text REFERENCES networks ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "profile_template_id text REFERENCES profile_templates ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "PRIMARY KEY(network_id, profile_template_id) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE profiles \n";
$sql .= "( \n";
$sql .= "profile_id text NOT NULL PRIMARY KEY, \n";
$sql .= "profile_template_id text REFERENCES profile_templates ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "creation_date timestamp DEFAULT now(),\n";
$sql .= "is_visible boolean DEFAULT TRUE\n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE user_has_profiles \n";
$sql .= "( \n";
$sql .= "user_id text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "profile_id text REFERENCES profiles ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "PRIMARY KEY(user_id, profile_id) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE profile_fields \n";
$sql .= "( \n";
$sql .= "profile_field_id text NOT NULL PRIMARY KEY,\n";
$sql .= "profile_id text REFERENCES profiles ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "profile_template_field_id text REFERENCES profile_template_fields ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "content_id text REFERENCES content ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "last_modified timestamp DEFAULT now()\n";
$sql .= ");\n";
}
$new_schema_version = 54;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE stakeholder_types \n";
$sql .= "( \n";
$sql .= "stakeholder_type_id text NOT NULL PRIMARY KEY CONSTRAINT stakeholder_types_id_not_empty_string CHECK (stakeholder_type_id != '')\n";
$sql .= ");\n\n";
$sql .= "INSERT into stakeholder_types (stakeholder_type_id) VALUES ('Node');\n\n";
$sql .= "INSERT into stakeholder_types (stakeholder_type_id) VALUES ('Network');\n\n";
$sql .= "INSERT into stakeholder_types (stakeholder_type_id) VALUES ('Server');\n\n";
$sql .= "INSERT into stakeholder_types (stakeholder_type_id) VALUES ('Content');\n\n";
$sql .= "CREATE TABLE permissions \n";
$sql .= "( \n";
$sql .= "permission_id text NOT NULL PRIMARY KEY CONSTRAINT permission_rules_id_not_empty_string CHECK (permission_id != ''),\n";
$sql .= "stakeholder_type_id text NOT NULL REFERENCES stakeholder_types ON UPDATE CASCADE ON DELETE CASCADE\n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE roles \n";
$sql .= "( \n";
$sql .= "role_id text NOT NULL PRIMARY KEY CONSTRAINT roles_rules_id_not_empty_string CHECK (role_id != ''), \n";
$sql .= "role_description_content_id text REFERENCES content ON UPDATE CASCADE ON DELETE SET NULL,\n";
$sql .= "is_system_role boolean NOT NULL DEFAULT FALSE,\n";
$sql .= "stakeholder_type_id text NOT NULL REFERENCES stakeholder_types ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "role_creation_date timestamp DEFAULT now()\n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE role_has_permissions \n";
$sql .= "( \n";
$sql .= "role_id text REFERENCES roles ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "permission_id text REFERENCES permissions ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "PRIMARY KEY(role_id, permission_id) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE stakeholders \n";
$sql .= "( \n";
$sql .= "user_id text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "role_id text REFERENCES roles ON UPDATE CASCADE ON DELETE CASCADE,\n";
$sql .= "object_id text NOT NULL CONSTRAINT user_has_roles_objct_id_not_empty_string CHECK (object_id != ''),\n";
$sql .= "PRIMARY KEY(user_id, role_id, object_id) \n";
$sql .= ");\n\n";
/* Fix the Virtual Host/Server confusion... */
$sql .= "CREATE TABLE virtual_hosts ( \n";
$sql .= " virtual_host_id text NOT NULL PRIMARY KEY,\n";
$sql .= " hostname text NOT NULL UNIQUE CHECK (hostname<>''),\n";
$sql .= " creation_date date NOT NULL DEFAULT now(),\n";
$sql .= " ssl_available BOOLEAN NOT NULL DEFAULT FALSE,\n";
$sql .= " gmaps_api_key text,\n";
$sql .= " default_network text NOT NULL REFERENCES networks ON UPDATE cascade ON DELETE RESTRICT\n";
$sql .= ");\n";
$sql .= "INSERT INTO virtual_hosts (virtual_host_id, hostname, creation_date, ssl_available, gmaps_api_key, default_network) (SELECT server_id, hostname, creation_date, ssl_available, gmaps_api_key, (SELECT network_id FROM networks WHERE is_default_network = true LIMIT 1) FROM servers);\n";
$sql .= "ALTER TABLE networks DROP column is_default_network;\n";
$sql .= "CREATE TABLE server ( \n";
$sql .= " server_id text NOT NULL PRIMARY KEY,\n";
$sql .= " creation_date date NOT NULL DEFAULT now(),\n";
$sql .= " default_virtual_host text NOT NULL REFERENCES virtual_hosts ON UPDATE cascade ON DELETE RESTRICT\n";
$sql .= ");\n";
$sql .= "INSERT INTO server (server_id, default_virtual_host) VALUES ('SERVER_ID', (SELECT server_id FROM servers WHERE is_default_server = true LIMIT 1));\n";
$sql .= "DROP TABLE servers;\n";
$sql .= "DROP TABLE network_stakeholders; \n";
$sql .= "CREATE TABLE network_stakeholders \n";
$sql .= "( \n";
$sql .= " CONSTRAINT fk_network FOREIGN KEY (object_id) REFERENCES networks (network_id) ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "PRIMARY KEY(user_id, role_id, object_id) \n";
$sql .= ") inherits(stakeholders);\n\n";
/* Convert superusers */
$sql .= "DROP TABLE administrators; \n";
$sql .= "CREATE TABLE server_stakeholders \n";
$sql .= "( \n";
$sql .= " CONSTRAINT fk_network FOREIGN KEY (object_id) REFERENCES server (server_id) ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "PRIMARY KEY(user_id, role_id, object_id) \n";
$sql .= ") inherits(stakeholders);\n\n";
$sql .= "INSERT into roles (role_id, stakeholder_type_id) VALUES ('SERVER_SYSADMIN', 'Server');\n";
$sql .= "INSERT into roles (role_id, stakeholder_type_id) VALUES ('NETWORK_SYSADMIN', 'Network');\n";
$db->execSql("SELECT * FROM administrators", $results, false);
foreach ($results as $row) {
$sql .= "INSERT into server_stakeholders (user_id, role_id, object_id) VALUES ('{$row['user_id']}', 'SERVER_SYSADMIN', (SELECT server_id FROM server LIMIT 1));\n";
$db->execSql("SELECT network_id FROM networks", $networkRows, false);
foreach ($networkRows as $networkRow) {
$sql .= "INSERT into network_stakeholders (user_id, role_id, object_id) VALUES ('{$row['user_id']}', 'NETWORK_SYSADMIN', '{$networkRow['network_id']}');\n";
}
}
$sql .= "DROP TABLE node_stakeholders; \n";
$sql .= "CREATE TABLE node_stakeholders \n";
$sql .= "( \n";
$sql .= " CONSTRAINT fk_nodes FOREIGN KEY (object_id) REFERENCES nodes (node_id) ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "PRIMARY KEY(user_id, role_id, object_id) \n";
$sql .= ") inherits(stakeholders);\n\n";
/* Convert node owners and tech officer */
$sql .= "INSERT into roles (role_id, stakeholder_type_id) VALUES ('NODE_OWNER', 'Node');\n";
$db->execSql("SELECT * FROM node_stakeholders WHERE is_owner = true", $results, false);
if ($results) {
foreach ($results as $row) {
$sql .= "INSERT into node_stakeholders (user_id, role_id, object_id) VALUES ('{$row['user_id']}', 'NODE_OWNER', '{$row['node_id']}');\n";
}
}
$sql .= "INSERT into roles (role_id, stakeholder_type_id) VALUES ('NODE_TECH_OFFICER', 'Node');\n";
$db->execSql("SELECT * FROM node_stakeholders WHERE is_tech_officer = true", $results, false);
if ($results) {
foreach ($results as $row) {
$sql .= "INSERT into node_stakeholders (user_id, role_id, object_id) VALUES ('{$row['user_id']}', 'NODE_TECH_OFFICER', '{$row['node_id']}');\n";
}
}
}
$new_schema_version = 55;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN last_heartbeat_sys_uptime INTEGER;\n";
//68 years of uptime should be enough for anybody ;)
$sql .= "ALTER TABLE nodes ALTER COLUMN last_heartbeat_sys_uptime SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN last_heartbeat_wifidog_uptime INTEGER;\n";
//68 years of uptime should be enough for anybody ;)
$sql .= "ALTER TABLE nodes ALTER COLUMN last_heartbeat_wifidog_uptime SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN last_heartbeat_sys_memfree INTEGER;\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN last_heartbeat_sys_memfree SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN last_heartbeat_sys_load real;\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN last_heartbeat_sys_load SET DEFAULT NULL;\n";
}
$new_schema_version = 56;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE INDEX idx_connections_timestamp_in ON connections (timestamp_in);\n";
}
$new_schema_version = 57;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE users ADD COLUMN open_id_url text;\n";
$sql .= "CREATE INDEX idx_users_topen_id_url ON users (open_id_url);\n";
}
$new_schema_version = 58;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE network_stakeholders ADD CONSTRAINT fk_roles FOREIGN KEY (role_id) REFERENCES roles (role_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE node_stakeholders ADD CONSTRAINT fk_roles FOREIGN KEY (role_id) REFERENCES roles (role_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "ALTER TABLE server_stakeholders ADD CONSTRAINT fk_roles FOREIGN KEY (role_id) REFERENCES roles (role_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
$sql .= "UPDATE roles SET role_id='SERVER_OWNER', is_system_role=true WHERE role_id='SERVER_SYSADMIN';\n";
$sql .= "UPDATE roles SET role_id='NETWORK_OWNER', is_system_role=true WHERE role_id='NETWORK_SYSADMIN';\n";
$sql .= "UPDATE roles SET is_system_role=true WHERE role_id='NODE_OWNER';\n";
$sql .= "INSERT into roles (role_id, stakeholder_type_id, is_system_role) VALUES ('CONTENT_OWNER', 'Content', true);\n";
}
$new_schema_version = 59;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE INDEX idx_content_display_log ON content_display_log (last_display_timestamp);\n";
$sql .= "CREATE INDEX idx_nodes_node_deployment_status ON nodes (node_deployment_status);\n";
}
$new_schema_version = 60;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE token_templates \n";
$sql .= "( \n";
$sql .= "token_template_id text PRIMARY KEY, \n";
$sql .= "token_template_network text REFERENCES networks (network_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, -- (Note: Server-wide tokens aren't supported, but the code will look up the tokens of networks you peer with) \n";
$sql .= "token_template_creation_date timestamp NOT NULL DEFAULT now(),\n";
$sql .= "token_max_incoming_data integer, -- Ex: Allows capping bandwidth \n";
$sql .= "token_max_outgoing_data integer, -- Ex: Allows capping bandwidth \n";
$sql .= "token_max_total_data integer, -- Ex: Allows capping bandwidth \n";
$sql .= "token_max_connection_duration interval, -- Ex: Allows limiting the length of a single connection \n";
$sql .= "token_max_usage_duration interval, -- Ex: Allows selling access by the hour (counting only when in use) \n";
$sql .= "token_max_wall_clock_duration interval, -- Ex: Allows selling daily, weekly or monthly passes (starting the count as soon as the token is first used) \n";
$sql .= "token_max_age interval, -- Ex: Allow setting a maximum time before expiration (starting the count as soon as the token is issued) \n";
$sql .= "token_is_reusable boolean DEFAULT true -- Can a user connect again using this token? (normally, yes) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE tokens_template_valid_nodes -- (Unfortunately, for hotels selling 24h access to their clients, we have to consider that their network may consist of more than one node. If the token has no entry in this table, it's considered valid everywhere on the Network (and it's peers)) \n";
$sql .= "( \n";
$sql .= "token_template_id text REFERENCES token_templates (token_template_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, \n";
$sql .= "token_valid_at_node text REFERENCES nodes (node_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, \n";
$sql .= "PRIMARY KEY (token_template_id, token_valid_at_node) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE token_lots \n";
$sql .= "( \n";
$sql .= "token_lot_id text PRIMARY KEY, \n";
$sql .= "token_lot_comment text, -- A free-form comment about the lot text \n";
$sql .= "token_lot_creation_date timestamp NOT NULL DEFAULT now()\n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE tokens \n";
$sql .= "( \n";
$sql .= "token_id text PRIMARY KEY, \n";
$sql .= "token_template_id text REFERENCES token_templates (token_template_id) ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "token_status text REFERENCES token_status (token_status) ON UPDATE CASCADE ON DELETE RESTRICT, \n";
$sql .= "token_lot_id text REFERENCES token_lots (token_lot_id) ON UPDATE CASCADE ON DELETE CASCADE, \n";
$sql .= "token_creation_date timestamp NOT NULL DEFAULT now(), -- (not the same as connection start time) \n";
$sql .= "token_issuer text REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, -- A user in the system. User responsible for the creation of the token (not necessarily the same as the one using it), \n";
$sql .= "token_owner text REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE -- The user that can USE the token, anyone if empty.\n";
$sql .= ");\n\n";
$sql .= "INSERT INTO tokens (token_id, token_status, token_creation_date, token_issuer, token_owner) SELECT token AS token_id, token_status, timestamp_in AS token_creation_date, user_id AS token_issuer, user_id AS token_owner FROM connections; \n";
$sql .= "CREATE INDEX idx_token_status ON tokens (token_status);\n";
$sql .= "ALTER TABLE connections ADD CONSTRAINT fk_tokens FOREIGN KEY (token) REFERENCES tokens (token_id) ON UPDATE CASCADE ON DELETE RESTRICT; \n";
$sql .= "ALTER TABLE connections DROP column token_status; \n";
$sql .= "ALTER TABLE connections ADD COLUMN max_total_bytes integer;\n";
$sql .= "ALTER TABLE connections ALTER COLUMN max_total_bytes SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE connections ADD COLUMN max_incoming_bytes integer;\n";
$sql .= "ALTER TABLE connections ALTER COLUMN max_incoming_bytes SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE connections ADD COLUMN max_outgoing_bytes integer;\n";
$sql .= "ALTER TABLE connections ALTER COLUMN max_outgoing_bytes SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE connections ADD COLUMN expiration_date timestamp;\n";
$sql .= "ALTER TABLE connections ALTER COLUMN expiration_date SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE connections ADD COLUMN logout_reason integer;\n";
$sql .= "ALTER TABLE connections ALTER COLUMN logout_reason SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE connections RENAME COLUMN token TO token_id;\n";
}
$new_schema_version = 61;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE networks ADD column connection_limit_window interval; \n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_window SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE networks ADD COLUMN connection_limit_network_max_total_bytes integer;\n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_network_max_total_bytes SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE networks ADD COLUMN connection_limit_network_max_usage_duration interval;\n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_network_max_usage_duration SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE networks ADD COLUMN connection_limit_node_max_total_bytes integer;\n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_node_max_total_bytes SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE networks ADD COLUMN connection_limit_node_max_usage_duration interval;\n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_node_max_usage_duration SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN connection_limit_node_max_total_bytes_override integer;\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN connection_limit_node_max_total_bytes_override SET DEFAULT NULL;\n";
$sql .= "ALTER TABLE nodes ADD COLUMN connection_limit_node_max_usage_duration_override interval;\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN connection_limit_node_max_usage_duration_override SET DEFAULT NULL;\n";
}
$new_schema_version = 62;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_network_max_total_bytes TYPE bigint;\n";
$sql .= "ALTER TABLE networks ALTER COLUMN connection_limit_node_max_total_bytes TYPE bigint;\n";
$sql .= "ALTER TABLE nodes ALTER COLUMN connection_limit_node_max_total_bytes_override TYPE bigint;\n";
}
$new_schema_version = 63;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE networks ADD column allow_original_url_redirect bool; \n";
$sql .= "ALTER TABLE networks ALTER COLUMN allow_original_url_redirect SET DEFAULT FALSE;\n";
$sql .= "ALTER TABLE nodes ADD column allow_original_URL_redirect bool; \n";
$sql .= "ALTER TABLE nodes ALTER COLUMN allow_original_URL_redirect SET DEFAULT FALSE;\n";
}
$new_schema_version = 64;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE server ADD use_global_auth bool NOT NULL DEFAULT FALSE;\n";
}
$new_schema_version = 65;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "ALTER TABLE nodes ADD COLUMN allows_public_stats boolean NOT NULL DEFAULT false;\n";
}
$new_schema_version = 66;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "UPDATE connections SET logout_reason=0, timestamp_out=timestamp_in FROM tokens WHERE connections.token_id=tokens.token_id AND timestamp_out IS NULL AND (token_status = 'USED' OR token_status = 'UNUSED');\n";
}
$new_schema_version = 67;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "\n\nALTER TABLE networks ADD COLUMN usernames_case_sensitive boolean NOT NULL DEFAULT true;\n";
}
$new_schema_version = 68;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "CREATE TABLE node_groups \n";
$sql .= "( \n";
$sql .= "node_group_id character varying(32) PRIMARY KEY, \n";
$sql .= "name text, \n";
$sql .= "description text , \n";
$sql .= "group_creation_date timestamp NOT NULL DEFAULT now() \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE hotspot_graph_elements \n";
$sql .= "( \n";
$sql .= "hotspot_graph_element_id character varying(32) PRIMARY KEY, \n";
$sql .= "element_id text, \n";
$sql .= "element_type character varying(16) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE hotspot_graph \n";
$sql .= "( \n";
$sql .= "child_element_id character varying(32) REFERENCES hotspot_graph_elements (hotspot_graph_element_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL , \n";
$sql .= "parent_element_id character varying(32) REFERENCES hotspot_graph_elements (hotspot_graph_element_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL , \n";
$sql .= "link_order integer DEFAULT 1 , \n";
$sql .= "PRIMARY KEY(child_element_id, parent_element_id) \n";
$sql .= ");\n\n";
$sql .= "CREATE TABLE hotspot_graph_element_has_content (\n";
$sql .= "hotspot_graph_element_id character varying(32) REFERENCES hotspot_graph_elements (hotspot_graph_element_id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL ,\n";
$sql .= "content_id text NOT NULL,\n";
$sql .= "subscribe_timestamp timestamp without time zone DEFAULT now() NOT NULL,\n";
$sql .= "display_page text DEFAULT 'portal'::text NOT NULL,\n";
$sql .= "display_area text DEFAULT 'main_area_middle'::text NOT NULL,\n";
$sql .= "display_order integer DEFAULT 1 NOT NULL\n";
$sql .= ");\n\n";
$sql .= "INSERT INTO stakeholder_types (stakeholder_type_id) VALUES ('NodeGroup');\n";
//$networks = Network::getAllNetworks();
$networks = array();
$db->execSql("Select network_id from networks", $networks, false);
foreach ($networks as $network) {
$new_guid = get_guid();
$sql .= "INSERT INTO hotspot_graph_elements values('{$new_guid}', '{$network['network_id']}', 'Network' );\n ";
$nodes = array();
$db->execSql("SELECT node_id FROM nodes where network_id = '{$network['network_id']}'", $nodes, false);
if ($nodes) {
foreach ($nodes as $node) {
$node_guid = get_guid();
$sql .= "INSERT INTO hotspot_graph_elements values('{$node_guid}', '{$node['node_id']}', 'Node' );\n ";
$sql .= "INSERT INTO hotspot_graph(child_element_id, parent_element_id) VALUES ('{$node_guid}', '{$new_guid}');\n";
}
}
}
$sql .= "INSERT INTO hotspot_graph_element_has_content (\n";
$sql .= "SELECT hge.hotspot_graph_element_id, content_id, subscribe_timestamp, display_page, display_area, display_order \n";
$sql .= "FROM hotspot_graph_elements hge inner join network_has_content nhc on nhc.network_id = hge.element_id \n";
$sql .= "WHERE hge.element_type = 'Network');\n\n";
$sql .= "INSERT INTO hotspot_graph_element_has_content (\n";
$sql .= "SELECT hge.hotspot_graph_element_id, content_id, subscribe_timestamp, display_page, display_area, display_order \n";
$sql .= "FROM hotspot_graph_elements hge inner join node_has_content nhc on nhc.node_id = hge.element_id \n";
$sql .= "WHERE hge.element_type = 'Node');\n\n";
$sql .= "CREATE TABLE nodegroup_stakeholders (\n";
$sql .= ")\n";
$sql .= "INHERITS (stakeholders);\n\n";
}
$new_schema_version = 69;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "\n\nDELETE FROM hotspot_graph_element_has_content WHERE content_id NOT IN (SELECT content_id from content);\n";
$sql .= "\n\nALTER TABLE hotspot_graph_element_has_content ADD CONSTRAINT contentfk FOREIGN KEY (content_id) REFERENCES content(content_id) ON UPDATE CASCADE ON DELETE CASCADE;\n";
}
$new_schema_version = 70;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "\nALTER TABLE nodes ADD COLUMN show_node_on_map boolean NOT NULL DEFAULT true;";
}
$new_schema_version = 71;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='{$new_schema_version}' WHERE tag='schema_version';\n";
$sql .= "\nCREATE INDEX lower_username ON users USING btree (lower(username));";
$sql .= "\nCREATE INDEX lower_email ON users USING btree (lower(email));";
}
/*
$new_schema_version = ;
if ($schema_version < $new_schema_version && $new_schema_version <= $targetSchema) {
printUpdateVersion($new_schema_version);
$sql .= "\n\nUPDATE schema_info SET value='$new_schema_version' WHERE tag='schema_version';\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
$sql .= "\n";
}
*/
if (SCHEMA_UPDATE_TEST_MODE) {
$retval = $db->execSqlUpdate("BEGIN;\n{$sql}\nROLLBACK;\n", true);
} else {
$retval = $db->execSqlUpdate("BEGIN;\n{$sql}\nCOMMIT;\n", true);
}
@ob_flush();
flush();
return $retval;
}
/**
* Feed-specific section of the admin interface
*
* @param array $feed_row The database row of the content_rss_aggregator_feeds table
*
* @return void
*/
private function processFeedAdminUI($feed_row)
{
$db = AbstractDb::getObject();
$original_url = $db->escapeString($feed_row['url']);
/*
* bias
*/
$name = "rss_aggregator_" . $this->id . "_feed_" . md5($feed_row['url']) . "_bias";
$original_bias = $db->escapeString($feed_row['bias']);
$bias = $db->escapeString($_REQUEST[$name]);
if (is_numeric($bias) && $bias > 0 && $bias != $original_bias) {
/*
* Only update database if the mode is valid and there is an actual change
*/
$db->execSqlUpdate("UPDATE content_rss_aggregator_feeds SET bias = '{$bias}' WHERE content_id = '{$this->id}' AND url='{$original_url}'", false);
$this->refresh();
} elseif (!is_numeric($bias) || $bias <= 0) {
echo _("The bias must be a positive real number");
} else {
/*
* Successfull, but nothing modified
*/
}
/*
* default_publication_interval
*/
$name = "rss_aggregator_" . $this->id . "_feed_" . md5($feed_row['url']) . "_default_publication_interval";
if (isset($_REQUEST[$name])) {
$original_default_publication_interval = $db->escapeString($feed_row['default_publication_interval']);
$default_publication_interval = $db->escapeString($_REQUEST[$name] * (60 * 60 * 24));
if ((empty($default_publication_interval) || is_numeric($default_publication_interval) && $default_publication_interval > 0) && $default_publication_interval != $original_default_publication_interval) {
/*
* Only update database if the mode is valid and there is an actual change
*/
if (empty($default_publication_interval)) {
$default_publication_interval = 'NULL';
}
$db->execSqlUpdate("UPDATE content_rss_aggregator_feeds SET default_publication_interval = {$default_publication_interval} WHERE content_id = '{$this->id}' AND url='{$original_url}'", false);
$this->refresh();
} elseif (!is_numeric($bias) || $bias <= 0) {
echo _("The default publication must must be a positive integer or empty");
} else {
/*
* Successfull, but nothing modified
*/
}
}
/*
* URL, we must change it last or we won't find the row again
*/
$name = "rss_aggregator_" . $this->id . "_feed_" . md5($feed_row['url']) . "_url";
$url = $db->escapeString($_REQUEST[$name]);
if (!empty($url) && $url != $feed_row['url']) {
/*
* Only update database if the mode is valid and there is an actual change
*/
$db->execSqlUpdate("UPDATE content_rss_aggregator_feeds SET url = '{$url}' WHERE content_id = '{$this->id}' AND url='{$original_url}'", false);
$this->refresh();
} elseif (empty($url)) {
echo _("The URL cannot be empty!");
} else {
/*
* Successfull, but nothing modified
*/
}
}
/**
* Returns if this this Content element is displayable at this hotspot
*
* @param string $node Node Id
*
* @return bool True if it is displayable
*/
public function isDisplayableAt($node)
{
$db = AbstractDb::getObject();
// Init values
$retval = false;
$allowed_node_rows = null;
$sql = "SELECT * FROM content_group_element_has_allowed_nodes WHERE content_group_element_id='{$this->id}'";
$db->execSql($sql, $allowed_node_rows, false);
if ($allowed_node_rows != null) {
if ($node) {
$node_id = $node->getId();
/**
* @todo Proper algorithm, this is a dirty and slow hack
*/
foreach ($allowed_node_rows as $allowed_node_row) {
if ($allowed_node_row['node_id'] == $node_id) {
$retval = true;
}
}
} else {
/* There are allowed nodes, but we don't know at which node we want to display */
$retval = false;
}
} else {
/* No allowed node means all nodes are allowed */
$retval = true;
}
return $retval;
}
/**
* Function called by the children of this class to delete the parent element form the graph
* @param unknown_type $errmsg
* @return unknown_type
*/
protected function _delete(&$errmsg)
{
// Init values
$retval = false;
$db = AbstractDb::getObject();
$id = $db->escapeString($this->getHgeId());
if (!$db->execSqlUpdate("DELETE FROM hotspot_graph_elements WHERE hotspot_graph_element_id='{$id}'", false)) {
$errmsg = _('Could not delete graph element!');
} else {
$retval = true;
}
return $retval;
}
/**
* Deletes a Langstring object
*
* @param string $errmsg Reference to error message
*
* @return bool True if deletion was successful
* @internal Persistent content will not be deleted
*/
public function delete(&$errmsg)
{
// Init values.
$_retval = false;
if ($this->isPersistent()) {
$errmsg = _("Content is persistent (you must make it non persistent before you can delete it)");
} else {
$db = AbstractDb::getObject();
if ($this->DEPRECATEDisOwner(User::getCurrentUser()) || User::getCurrentUser()->DEPRECATEDisSuperAdmin()) {
$sql = "DELETE FROM content WHERE content_id='{$this->id}'";
$db->execSqlUpdate($sql, false);
$_retval = true;
// Create new cache object.
$_cache = new Cache('all', $this->id);
// Check if caching has been enabled.
if ($_cache->isCachingEnabled) {
// Remove old cached data.
$_cache->eraseCachedGroupData();
}
} else {
$errmsg = _("Access denied (not owner of content)");
}
}
return $_retval;
}
/** Retreives the user interface of this object. Anything that overrides this method should call the parent method with it's output at the END of processing.
* @return The HTML fragment for this interface */
public function getUserUI()
{
$real_node = Node::getCurrentRealNode();
//For production
//$real_node = Node::getCurrentNode();//For testing
$node = Node::getCurrentNode();
$formHtml = null;
if ($real_node) {
$formHtml .= "<form action='" . BASE_URL_PATH . "content/ShoutBox/add_message.php'>\n";
$formHtml .= "<input type='hidden' name='shoutbox_id' value='" . $this->getId() . "'/>\n";
//$html .= "destination_url: ";pretty_print_r($_SERVER);
$maxShoutChars = $this->getMaxShoutChars();
$shoutFieldSize = $this->getShoutFieldSize();
if ($maxShoutChars > 0) {
$max_size = "maxlength='{$maxShoutChars}'";
$maxShoutChars <= $shoutFieldSize ? $size = "size='{$maxShoutChars}'" : ($size = "size='{$shoutFieldSize}'");
} else {
$max_size = null;
$size = "size='{$shoutFieldSize}'";
}
$formHtml .= "<input type='hidden' name='node_id' value='" . $node->getId() . "'/>\n";
$formHtml .= "<input type='text' name='shout_text' id='shout_text' {$size} {$max_size} value=''/>\n";
$onclick_content = $this->getOnClickContent();
if ($onclick_content) {
$onclick = "onclick=\"" . $onclick_content->getString() . "\"";
} else {
$onclick = null;
}
$formHtml .= "<input type='submit' name='shout_submit' {$onclick} value='" . _("Shout!") . "'>\n";
$formHtml .= "</form>\n";
} else {
$formHtml .= "<p>" . _("Sorry, you must be at a hotspot to use the shoutbox") . "</p>\n";
}
$html_main = '';
$displayNumItems = $this->getDisplayNumItems();
$db = AbstractDb::getObject();
if ($node) {
$node_id = $db->escapeString($node->getId());
if ($displayNumItems > 0) {
$limit = "LIMIT {$displayNumItems}";
$heading = "<em>" . sprintf(_("Last %d messages:"), $displayNumItems) . "</em>";
} else {
$limit = null;
$heading = null;
}
$sql = "SELECT *, EXTRACT(EPOCH FROM creation_date) as creation_date_php FROM content_shoutbox_messages WHERE origin_node_id='{$node_id}' ORDER BY creation_date DESC {$limit}\n";
$db->execSql($sql, $rows, false);
$html_main .= "<ul>";
$html_main .= "<li>{$formHtml}</li>";
if ($rows) {
//$html_main .= $heading;
foreach ($rows as $row) {
$user = User::getObject($row['author_user_id']);
$content = Content::getObject($row['message_content_id']);
$html_main .= "<li>";
$dateStr = "<span class='date'>" . strftime('%x', $row['creation_date_php']) . "</span>\n";
$html_main .= $dateStr . ' ' . $user->getListUI() . ": \n";
$html_main .= "<div class='message'>" . $content->getListUI() . "</div>\n";
$html_main .= "</li>";
}
}
$html_main .= "</ul>";
} else {
$html_main .= "<p>" . _("Sorry, I am unable to determine your current node") . "</p>\n";
}
$this->setUserUIMainDisplayContent($html_main);
//$this->setUserUIMainInteractionArea($formHtml);
return Content::getUserUI();
}
/**
* Get the list of all narratives
*
* @return the archive page HTML
*/
public function getNarrativeList()
{
$db = AbstractDb::getObject();
// Init values
$narratives = array();
$rows = null;
$sql = "SELECT DISTINCT user_id FROM content_display_log AS cdl JOIN content_group_element AS cge ON (cdl.content_id = cge.content_group_element_id) JOIN content ON (content.content_id = cge.content_group_id) WHERE content_type = 'PatternLanguage'";
$db->execSql($sql, $rows, false);
if ($rows) {
foreach ($rows as $row) {
$narratives[] = User::getObject($row['user_id']);
}
}
return $narratives;
}
/**
* Delete this Object form the it's storage mechanism
*
* @param string &$errmsg Returns an explanation of the error on failure
*
* @return bool true on success, false on failure or access denied
*
* @access public
*/
public function delete(&$errmsg)
{
// Init values
$retval = false;
Security::requirePermission(Permission::P('NETWORK_PERM_DELETE_NETWORK'), $this);
if ($this->isDefaultNetwork() === true) {
$errmsg = _('Cannot delete default network, create another one and select it before you remove this one.');
} else {
$db = AbstractDb::getObject();
$id = $db->escapeString($this->getId());
if (!$db->execSqlUpdate("DELETE FROM networks WHERE network_id='{$id}'", false)) {
$errmsg = _('Could not delete network!');
} else {
parent::_delete($errmsg);
$retval = true;
}
}
return $retval;
}
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
/* User visits */
// Only Super admin
if (!User::getCurrentUser()->DEPRECATEDisSuperAdmin()) {
$html .= "<p class='error'>" . _("Access denied") . "</p>";
} else {
/** Starting sql file with geolocation data */
$tmpdir = sys_get_temp_dir();
$nodefile = tempnam($tmpdir, 'wd');
$nfilehndl = fopen($nodefile, 'w');
$datafile = tempnam($tmpdir, 'wd');
$datahndl = fopen($datafile, 'w');
if (!$nfilehndl || !$datahndl) {
$html .= "<p class='error'>" . _("Could not create files for anonymised data") . "</p>";
} else {
/* header('Content-Type: application/octet-stream');
header('Content-Disposition: inline; filename="anonymised_nodes.sql"');
header("Content-Transfer-Encoding: binary"); */
$text = <<<EOT
CREATE TABLE nodes_anonymised
(
node_id text NOT NULL,
latitude NUMERIC(16, 6),
longitude NUMERIC(16, 6)
);
EOT;
$text .= "\n";
fwrite($nfilehndl, $text);
$node_constraint = $this->stats->getSqlNodeConstraint('nodes.node_id');
$network_constraint = $this->stats->getSqlNetworkConstraint('nodes.network_id');
$sql = "SELECT node_id, latitude, longitude \n";
$sql .= "FROM nodes \n";
$sql .= "WHERE 1=1 {$node_constraint} {$network_constraint}";
$db->execSql($sql, $nodes);
if ($nodes) {
foreach ($nodes as $row) {
$keys = null;
$values = null;
$first = true;
foreach ($row as $key => $value) {
if ($key == 'user_id' || $key == 'node_id' || $key == 'conn_id' || $key == 'user_mac') {
$value = "'" . $this->getNonRepeatableHash($value) . "'";
} else {
if ($key == 'latitude' && empty($value)) {
$value = 'NULL';
} else {
if ($key == 'longitude' && empty($value)) {
$value = 'NULL';
} else {
$value = "'{$value}'";
}
}
}
if (!$first) {
$keys .= ', ';
$values .= ', ';
} else {
$first = false;
}
$keys .= $key;
$values .= $value;
}
//fwrite($temp, "INSERT INTO connections_anonymised ($keys) VALUES ($values);\n");
fwrite($nfilehndl, "INSERT INTO nodes_anonymised ({$keys}) VALUES ({$values});\n");
}
}
/** End sql file with node data */
/** Get the sql file with anonymised connection data */
/* header('Content-Type: application/octet-stream');
header('Content-Disposition: inline; filename="anonymised_data.sql"');
header("Content-Transfer-Encoding: binary");*/
$text = <<<EOT
CREATE TABLE connections_anonymised
(
conn_id text NOT NULL,
timestamp_in timestamp,
node_id text,
timestamp_out timestamp,
user_id text NOT NULL DEFAULT '',
user_mac text,
incoming int8,
outgoing int8
);
EOT;
$text .= "\n";
fwrite($datahndl, $text);
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("conn_id, users.user_id, nodes.node_id, connections.user_id, user_mac, timestamp_in, timestamp_out, incoming, outgoing ", true);
$sql = "{$candidate_connections_sql} ORDER BY timestamp_in DESC";
$db->execSqlRaw($sql, $resultHandle, false);
if ($resultHandle) {
while ($row = pg_fetch_array($resultHandle, null, PGSQL_ASSOC)) {
$keys = null;
$values = null;
$first = true;
foreach ($row as $key => $value) {
if ($key == 'user_id' || $key == 'node_id' || $key == 'conn_id' || $key == 'user_mac') {
$value = "'" . $this->getNonRepeatableHash($value) . "'";
} else {
if ($key == 'timestamp_out' && empty($value)) {
$value = 'NULL';
} else {
$value = "'{$value}'";
}
}
if (!$first) {
$keys .= ', ';
$values .= ', ';
} else {
$first = false;
}
$keys .= $key;
$values .= $value;
}
//fwrite($temp, "INSERT INTO connections_anonymised ($keys) VALUES ($values);\n");
fwrite($datahndl, "INSERT INTO connections_anonymised ({$keys}) VALUES ({$values});\n");
}
}
fclose($datahndl);
fclose($nfilehndl);
$html .= <<<EOS
<script type="text/javascript">
\t\twindow.open('/admin/stats.php?file={$nodefile}&type=node', 'Node File');
\t\twindow.open('/admin/stats.php?file={$datafile}&type=data', 'Data file');
\t\t\t\t\t\t\t\t</script>
EOS;
}
}
return $html;
}
* @author Benoit Grégoire <*****@*****.**>
* @author Max Horváth <*****@*****.**>
* @copyright 2004-2006 Philippe April
* @copyright 2004-2006 Benoit Grégoire, Technologies Coeus inc.
* @copyright 2006 Max Horváth, Horvath Web Consulting
* @version Subversion $Id$
* @link http://www.wifidog.org/
*/
/**
* Load required files
*/
require_once dirname(__FILE__) . '/include/common.php';
require_once 'classes/User.php';
require_once 'classes/MainUI.php';
$smarty = SmartyWifidog::getObject();
$db = AbstractDb::getObject();
/**
* Process recovering username
*/
// Init ALL smarty SWITCH values
$smarty->assign('sectionTOOLCONTENT', false);
$smarty->assign('sectionMAINCONTENT', false);
// Init ALL smarty values
$smarty->assign('message', "");
$smarty->assign('error', "");
$smarty->assign('auth_sources', "");
$smarty->assign('selected_auth_source', "");
$smarty->assign('SelectNetworkUI', "");
$smarty->assign('username', "");
$smarty->assign('email', "");
if (isset($_REQUEST['form_request'])) {
/** Get the actual report.
* Classes must override this, but must call the parent's method with what
* would otherwise be their return value and return that instead.
* @param $child_html The child method's return value
* @return A html fragment
*/
public function getReportUI($child_html = null)
{
$db = AbstractDb::getObject();
$html = '';
/* Monthly registration graph */
$graph = StatisticGraph::getObject('RegistrationsPerMonth');
$html .= $graph->getReportUI($this->stats);
/* End Monthly registration graph */
/* Cumulative registration graph */
$graph = StatisticGraph::getObject('RegistrationsCumulative');
$html .= $graph->getReportUI($this->stats);
/* End cumulative registration graph */
/* First connection per node */
$html .= "<fieldset>";
$html .= "<legend>" . _("First connection per node") . "</legend>";
$node_usage_stats = null;
$distinguish_users_by = $this->stats->getDistinguishUsersBy();
/* The following query will retreive the list of the REAL first connection of each user, no matter where or when.*/
$sql_real_first_connections = $this->stats->getSqlRealFirstConnectionsQuery('connections.conn_id', false);
//$db->execSql($sql_real_first_connections, $tmp, true);
$real_first_connections_table_name = "real_first_conn_table_name_" . session_id();
$real_first_connections_table_sql = "CREATE TABLE {$real_first_connections_table_name} AS ({$sql_real_first_connections});\n";
//$real_first_connections_table_sql .= "CREATE INDEX {$real_first_connections_table_name}_idx ON $real_first_connections_table_name (conn_id); \n";
$db->execSqlUpdate($real_first_connections_table_sql, false);
/* Now retrieves the oldest connection matching the report restriction, and only keep it if it's really the user's first connection */
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("DISTINCT ON(connections.{$distinguish_users_by}) connections.{$distinguish_users_by}, conn_id, connections.node_id, nodes.name,timestamp_in ");
//$db->execSql($candidate_connections_sql, $tmp, true);
$first_connection_table_sql = "{$candidate_connections_sql} ORDER BY connections.{$distinguish_users_by}, timestamp_in\n";
//$db->execSql($first_connection_table_sql, $node_usage_stats, true);
$first_connection_table_name = "first_connection_table_name_" . session_id();
$registration_node_table_sql = "CREATE TEMP TABLE {$first_connection_table_name} AS ({$first_connection_table_sql});\n \n";
//$registration_node_table_sql .= "CREATE INDEX {$first_connection_table_name}_idx ON $first_connection_table_name (node_id)";
$db->execSqlUpdate($registration_node_table_sql, false);
$registration_node_table_sql = "SELECT COUNT ({$first_connection_table_name}.{$distinguish_users_by}) AS total_first_connections, node_id, name FROM {$first_connection_table_name} JOIN {$real_first_connections_table_name} ON ({$first_connection_table_name}.conn_id={$real_first_connections_table_name}.conn_id) GROUP BY node_id, name ORDER BY total_first_connections DESC;";
$db->execSql($registration_node_table_sql, $node_usage_stats, false);
$registration_node_table_sql = "DROP TABLE {$first_connection_table_name};";
$db->execSqlUpdate($registration_node_table_sql, false);
$real_first_connections_table_sql = "DROP TABLE {$real_first_connections_table_name};";
$db->execSqlUpdate($real_first_connections_table_sql, false);
if ($node_usage_stats) {
$html .= "<table>";
$html .= "<thead>";
$html .= "<tr>";
$html .= " <th>" . _("Node") . "</th>";
$html .= " <th>" . _("# of new user first connection") . "</th>";
$html .= "</tr>";
$html .= "</thead>";
$total = 0;
$even = 0;
foreach ($node_usage_stats as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['name']}</td>\n";
$html .= " <td>" . $row['total_first_connections'] . "</td>";
$html .= "</tr>";
$total += $row['total_first_connections'];
}
$html .= "<tfoot>";
$html .= "<tr>";
$html .= " <th>" . _("Total") . ":</th>";
$html .= " <th>" . $total . "</th>";
$html .= "</tr>";
$html .= "<tr>";
$html .= " <td colspan=2>" . _("Note: This is actually a list of how many new user's first connection occured at each hotspot, taking report restrictions into account. It includes non-validated users who successfully connected.") . "</td>";
$html .= "</tr>";
$html .= "</tfoot>";
$html .= "</table>";
} else {
$html .= _("No information found matching the report configuration");
}
/* End first connection per node */
return parent::getReportUI($html);
}
public function getAdminUI()
{
$db = AbstractDb::getObject();
$html = '';
$html .= "<fieldset class='admin_container'>\n";
$html .= "<legend>" . _("Report configuration") . "</legend>\n";
$html .= "<ul class='admin_element_list'>\n";
// Network
$html .= "<li class='admin_element_item_container'>\n";
$html .= "<div class='admin_element_data'>\n";
$networks = Security::getObjectsWithPermission(Permission::P('NETWORK_PERM_VIEW_STATISTICS'));
$additionalWhere = " AND (FALSE\n";
foreach ($networks as $network) {
$idStr = $db->escapeString($network->getId());
$additionalWhere .= " OR network_id='{$idStr}'\n";
}
$additionalWhere .= ")\n";
$networkUserData['preSelectedObject'] = reset($this->report_selected_networks);
$networkUserData['allowEmpty'] = true;
$networkUserData['additionalWhere'] = $additionalWhere;
$html .= Network::getSelectUI('Statistics', $networkUserData);
$html .= "</div>\n";
$html .= "</li>\n";
// Date range
$html .= "<li class='admin_element_item_container'>\n";
$html .= "<fieldset class='admin_element_group'>\n";
$html .= "<legend>" . _("Restrict the time range for which statistics will be computed") . " : </legend>\n";
$html .= $this->getDateRangeUI();
$html .= "</fieldset>\n";
$html .= "</li>\n";
// Selected nodes
$html .= "<li class='admin_element_item_container'>\n";
$html .= "<fieldset class='admin_element_group'>\n";
$html .= "<legend>" . _("Restrict stats to the following nodes") . " : </legend>\n";
$html .= "<div class='admin_element_data'>\n";
$html .= $this->getSelectedNodesUI();
$html .= "</div>\n";
$html .= "</fieldset>\n";
$html .= "</li>\n";
// Unique user criteria
$html .= "<li class='admin_element_item_container'>\n";
$html .= "<fieldset class='admin_element_group'>\n";
$html .= "<legend>" . _("Distinguish users by") . " : </legend>\n";
$html .= "<div class='admin_element_data'>\n";
$html .= $this->getDistinguishUsersByUI();
$html .= "</div>\n";
$html .= "</fieldset>\n";
$html .= "</li>\n";
// Selected users
$html .= "<li class='admin_element_item_container'>\n";
$html .= "<fieldset class='admin_element_group'>\n";
$html .= "<legend>" . _("Restrict stats to the selected users") . " : </legend>\n";
$html .= "<div class='admin_element_data'>\n";
$html .= $this->getSelectedUsersUI();
$html .= "</div>\n";
$html .= "</fieldset class='admin_element_group'>\n";
$html .= "</li>\n";
// Reports
$html .= "<li class='admin_element_item_container'>\n";
$html .= "<fieldset>\n";
$html .= "<legend>" . _("Selected reports") . " : </legend>\n";
$html .= "<div class='admin_element_data'>\n";
$html .= $this->getSelectedReportsUI();
$html .= "</div>\n";
$html .= "</fieldset>\n";
$html .= "</ul>\n";
$html .= "</fieldset>\n";
$html .= "</li>\n";
return $html;
}
/**
* Attempts to login a user against the authentication source
*
* If successfull, returns a User object
*
* @param string $username A valid identifying token for the source. Not
* necessarily unique. For local user, bots username
* and email are valid.
* @param string $password Clear text password.
* @param string $errmsg Reference of error message
* @param int $errno Reference to error code
*
* @return object The actual User object if login was successfull, false
* otherwise.
*/
public function login($username, $password, &$errmsg = null, &$errno = 0)
{
//echo "DEBUG: login($username, $password, $errmsg)<br/>";
$db = AbstractDb::getObject();
// Init values
$retval = false;
$username = $db->escapeString($username);
if (empty($username)) {
$errmsg .= sprintf(getErrorText(ERR_NO_USERNAME));
$errno = ERR_NO_USERNAME;
$retval = false;
} else {
/* gbastien: this is not reusable!!, why not use password directly? */
//$password_hash = User::passwordHash($_REQUEST['password']);
$password_hash = User::passwordHash($password);
$password = $db->escapeString($password);
$username = $this->getNetwork()->getUsernamesCaseSensitive() ? $username : strtolower($username);
$compareto = $this->getNetwork()->getUsernamesCaseSensitive() ? 'username' : 'lower(username)';
$sql = "SELECT user_id FROM users WHERE ({$compareto} = '{$username}' OR lower(email) = '{$username}') AND account_origin='" . $this->getNetwork()->getId() . "' AND pass='******'";
$db->execSqlUniqueRes($sql, $user_info, false);
if ($user_info != null) {
$user = User::getObject($user_info['user_id']);
if ($user->isUserValid($errmsg, $errno)) {
$retval =& $user;
$errmsg = _("Login successfull");
} else {
$retval = false;
//Reason for refusal is already in $errmsg
}
} else {
/*
* This is only used to discriminate if the problem was a
* non-existent user or a wrong password.
*/
$user_info = null;
$db->execSqlUniqueRes("SELECT * FROM users WHERE ({$compareto} = '{$username}' OR lower(email) = '{$username}') AND account_origin='" . $this->getNetwork()->getId() . "'", $user_info, false);
if ($user_info == null) {
$errmsg = getErrorText(ERR_UNKNOWN_USERNAME);
$errno = ERR_UNKNOWN_USERNAME;
} else {
$errmsg = getErrorText(ERR_WRONG_PASSWORD);
$errno = ERR_WRONG_PASSWORD;
}
$retval = false;
}
}
User::setCurrentUser($retval);
return $retval;
}
/** Return the actual Image data
* Classes must override this.
* @param $child_html The child method's return value
* @param $param mixed: used for $Graph->done()
* @return A html fragment
*/
public function showImageData($child_html = '', $param = false)
{
require_once "Image/Graph.php";
$db = AbstractDb::getObject();
$Graph =& Image_Graph::factory("Image_Graph", array(600, 200));
$Plotarea =& $Graph->add(Image_Graph::factory("Image_Graph_Plotarea"));
$Dataset =& Image_Graph::factory("Image_Graph_Dataset_Trivial");
$Bar =& Image_Graph::factory("Image_Graph_Plot_Bar", $Dataset);
$Bar->setFillColor("#9db8d2");
$Plot =& $Plotarea->add($Bar);
$candidate_connections_sql = self::$stats->getSqlCandidateConnectionsQuery("COUNT(DISTINCT connections.user_id||connections.node_id) AS daily_connections, date_trunc('day', timestamp_in) AS date");
$db->execSql("SELECT SUM(daily_connections) AS connections, date_trunc('month', date) AS month FROM ({$candidate_connections_sql} GROUP BY date) AS daily_connections_table GROUP BY month ORDER BY month", $results, false);
if ($results != null) {
foreach ($results as $row) {
/* Cut xxxx-xx-xx xx:xx:Xx to yy-mm */
$Dataset->addPoint(substr($row['month'], 0, 7), $row['connections']);
}
}
$Graph->done($param);
unset($Graph, $Plot, $Bar, $Plotarea, $Dataset, $row, $results);
}
/**
* Deletes a ProfileTemplateField object
*
* @param string $errmsg Reference to error message
*
* @return bool True if deletion was successful
* @internal Persistent content will not be deleted
*
* @todo Implement proper access control
*/
public function delete(&$errmsg)
{
require_once 'classes/User.php';
$db = AbstractDb::getObject();
// Init values
$_retVal = false;
if (!User::getCurrentUser()->DEPRECATEDisSuperAdmin()) {
$errmsg = _('Access denied (must have super admin access)');
} else {
$_id = $db->escapeString($this->getId());
if (!$db->execSqlUpdate("DELETE FROM profile_template_fields WHERE profile_template_field_id = '{$_id}'", false)) {
$errmsg = _('Could not delete ProfileTemplateField!');
} else {
$_retVal = true;
}
}
return $_retVal;
}
/**
* @see GenericObject
* @internal Persistent content will not be deleted
*/
public function delete(&$errmsg)
{
$retval = false;
if ($this->isPersistent()) {
$errmsg = _("Content is persistent (you must make it non persistent before you can delete it)");
} else {
$db = AbstractDb::getObject();
if ($this->DEPRECATEDisOwner(User::getCurrentUser()) || User::getCurrentUser()->DEPRECATEDisSuperAdmin()) {
$sql = "DELETE FROM content WHERE content_id='{$this->id}'";
$db->execSqlUpdate($sql, false);
//Metadata mmust be deleted AFTER the main content.
$errmsgTmp = null;
$metadata = $this->getTitle();
if ($metadata) {
$metadata->delete($errmsgTmp);
}
$errmsg .= $errmsgTmp;
$errmsgTmp = null;
$metadata = $this->getDescription();
if ($metadata) {
$metadata->delete($errmsgTmp);
}
$errmsg .= $errmsgTmp;
$errmsgTmp = null;
$metadata = $this->getLongDescription();
if ($metadata) {
$metadata->delete($errmsgTmp);
}
$errmsg .= $errmsgTmp;
$errmsgTmp = null;
$metadata = $this->getProjectInfo();
if ($metadata) {
$metadata->delete($errmsgTmp);
}
$errmsg .= $errmsgTmp;
$retval = true;
} else {
$errmsg = _("Access denied (not owner of content)");
}
}
return $retval;
}
function Phptxtdb() {
parent::AbstractDb();
$CI =& get_instance();
$this->_base_path = $CI->config->item('absolute_path');
}
