}
/*
* Process action requests (saving, previewing and deleting)
*/
switch ($_REQUEST['action']) {
case "save":
$object->processAdminUI();
$_REQUEST['action'] = 'edit';
break;
case "preview":
if (empty($_REQUEST['node_id'])) {
$node_id = null;
$node = null;
} else {
$node_id = $_REQUEST['node_id'];
$node = Node::getObject($node_id);
Node::setCurrentNode($node);
$html .= "<h1>" . _("Showing preview as it would appear at ") . $node->getName() . "</h1><br><br>";
}
if (!empty($_REQUEST['debug'])) {
$common_input .= "<input type='hidden' name='debug' value='true'>";
}
$common_input .= "<input type='hidden' name='object_id' value='" . $object->GetId() . "'>";
$common_input .= "<input type='hidden' name='object_class' value='" . get_class($object) . "'>";
$common_input .= "<input type='hidden' name='node_id' value='" . $node_id . "'>";
$html .= "<form action='" . GENERIC_OBJECT_ADMIN_ABS_HREF . "' target='_top' method='post'>";
$html .= $common_input;
$name = "node_id";
$html .= _("Node");
$html .= ": ";
$html .= Node::getSelectUI($name);
/**
* Get the select node interface.
*/
private function processSelectedNodesUI()
{
$name = "selected_nodes";
//pretty_print_r($_REQUEST[$name]);
$this->report_selected_nodes = array();
if (!empty($_REQUEST[$name])) {
foreach ($_REQUEST[$name] as $value) {
if (!empty($value)) {
$this->report_selected_nodes[$value] = Node::getObject($value);
}
}
}
}
} else {
$auth_message .= "| Incoming or outgoing counter is missing; counters not updated. ";
}
if ($_REQUEST['stage'] == STAGE_LOGOUT) {
$authenticator->logout($info['conn_id']);
$auth_message .= "| User is now logged out. ";
}
if ($_REQUEST['stage'] == STAGE_COUNTERS) {
if ($info['token_status'] == TOKEN_INUSE) {
/* This is for the 15 minutes validation period, the exact same code is also present when the stage is login. If you update this one don't forget to update the other one! */
if ($info['account_status'] == ACCOUNT_STATUS_VALIDATION && $info['validation_grace_time_expired'] == 't') {
$auth_response = ACCOUNT_STATUS_VALIDATION_FAILED;
$auth_message .= "| The validation grace period which began at " . $info['reg_date'] . " has now expired. ";
} else {
/* TODO: This is a bit hackish, it's a shortcut untill the Token architecture uniform connection limit calculations are in place. */
$abuseControlFault = User::isAbuseControlViolated(User::getObject($info['user_id']), $info['user_mac'], Node::getObject($info['node_id']));
if ($abuseControlFault) {
$auth_response = ACCOUNT_STATUS_DENIED;
$auth_message .= "| {$abuseControlFault} ";
$authenticator->logout($info['conn_id']);
$auth_message .= "| User is now logged out. ";
} else {
$auth_response = $info['account_status'];
}
}
} else {
$auth_response = ACCOUNT_STATUS_DENIED;
$auth_message .= "| Invalid token status: " . $token_to_text[$info['token_status']] . ". ";
}
}
} else {
/**
* Processes the input of the administration interface for ContentGroupElement
*
* @return void
*/
public function processAdminUI()
{
$db = AbstractDb::getObject();
// Init values
$allowed_node_rows = null;
$errmsg = null;
parent::processAdminUI();
/* display_order */
$name = "content_group_element_" . $this->id . "_display_order";
$this->setDisplayOrder($_REQUEST[$name]);
// valid_from_timestamp
$name = "content_group_element_" . $this->id . "_valid_from";
$this->setValidFromDate(DateTimeWD::processSelectDateTimeUI($name, DateTimeWD::INTERFACE_DATETIME_FIELD)->getIso8601FormattedString());
// valid_until_timestamp
$name = "content_group_element_" . $this->id . "_valid_until";
$this->setValidUntilDate(DateTimeWD::processSelectDateTimeUI($name, DateTimeWD::INTERFACE_DATETIME_FIELD)->getIso8601FormattedString());
/* content_group_element_has_allowed_nodes */
$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) {
foreach ($allowed_node_rows as $allowed_node_row) {
$node = Node::getObject($allowed_node_row['node_id']);
$name = "content_group_element_" . $this->id . "_allowed_node_" . $node->GetId() . "_remove";
if (!empty($_REQUEST[$name]) && $_REQUEST[$name] == true) {
$sql = "DELETE FROM content_group_element_has_allowed_nodes WHERE content_group_element_id='{$this->id}' AND node_id='" . $node->GetId() . "'";
$db->execSqlUpdate($sql, false);
}
}
}
$name = "content_group_element_{$this->id}_new_allowed_node_submit";
if (!empty($_REQUEST[$name]) && $_REQUEST[$name] == true) {
$name = "content_group_element_{$this->id}_new_allowed_node";
$node = Node::processSelectUI($name);
$node_id = $node->GetId();
$db->execSqlUpdate("INSERT INTO content_group_element_has_allowed_nodes (content_group_element_id, node_id) VALUES ('{$this->id}', '{$node_id}')", FALSE);
}
/* displayed_content_id */
if (empty($this->content_group_element_row['displayed_content_id'])) {
// Could be either a new content or existing content ( try both successively )
$displayed_content = Content::processNewContentUI("content_group_element_{$this->id}_new_displayed_content");
if ($displayed_content == null) {
$displayed_content = Content::processSelectExistingContentUI("content_group_element_{$this->id}_new_displayed_existing_element");
}
if ($displayed_content != null) {
$displayed_content_id = $displayed_content->GetId();
$db->execSqlUpdate("UPDATE content_group_element SET displayed_content_id = '{$displayed_content_id}' WHERE content_group_element_id = '{$this->id}'", FALSE);
}
} else {
$displayed_content = self::getObject($this->content_group_element_row['displayed_content_id']);
$name = "content_group_element_{$this->id}_erase_displayed_content";
if (!empty($_REQUEST[$name]) && $_REQUEST[$name] == true) {
if ($displayed_content->delete($errmsg) != false) {
$db->execSqlUpdate("UPDATE content_group_element SET displayed_content_id = NULL WHERE content_group_element_id = '{$this->id}'", FALSE);
} else {
echo $errmsg;
}
} else {
$displayed_content->processAdminUI();
}
}
}
if ($nodes_results == null) {
throw new Exception(_("No deployed nodes could not be found in the database"));
}
echo "<html>\n<head>\n";
echo "<title>Node Monitoring System</title>\n</head>\n";
echo "<style>\n";
echo "table {border: 1px solid black;}\n";
echo "td {padding: 4px;}\n";
echo "tr {border: 1px solid black;}\n";
echo ".alert {background: #ffaaaa;}\n";
echo "</style>";
echo "<body>\n";
echo "<p>Current server time: " . date("r") . "</p>";
echo "<table>\n<tr><th>Node</th><th>Last Heartbeat</th><th>Last IP Address</th><th>Status Message</th></tr>\n";
foreach ($nodes_results as $node_row) {
$nodeObject = Node::getObject($node_row['node_id']);
echo "<tr><td>";
echo $nodeObject->getName();
echo "</td><td>";
echo $nodeObject->getLastHeartbeatTimestamp();
echo "</td><td>";
echo $nodeObject->getLastHeartbeatIP();
echo "</td>";
try {
page_if_down_since($nodeObject, 43200);
//A month
page_if_down_since($nodeObject, 10080);
//A week
page_if_down_since($nodeObject, 1440);
//A day
page_if_down_since($nodeObject, 120);
/**
* 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;
}
/** Generate a token in the connection table so the user can actually use the internet
@return true on success, false on failure
*/
function generateConnectionToken($mac = null)
{
if ($this->isUserValid()) {
$db = AbstractDb::getObject();
$session = Session::getObject();
$token = self::generateToken();
if ($_SERVER['REMOTE_ADDR']) {
$node_ip = $db->escapeString($_SERVER['REMOTE_ADDR']);
}
if ($session && $node_ip && $session->get(SESS_NODE_ID_VAR)) {
//echo "$session && $node_ip && {$session->get(SESS_NODE_ID_VAR)}";
$node_id = $db->escapeString($session->get(SESS_NODE_ID_VAR));
$abuseControlFault = User::isAbuseControlViolated($this, $mac, Node::getObject($node_id));
if ($abuseControlFault) {
throw new Exception($abuseControlFault);
}
$mac = is_null($mac) ? '' : $db->escapeString($mac);
/*
* Delete all unused tokens for this user, so we don't fill the database
* with them
*/
$sql = "DELETE FROM connections USING tokens " . "WHERE tokens.token_id=connections.token_id AND token_status='" . TOKEN_UNUSED . "' AND user_id = '" . $this->getId() . "';\n";
// TODO: Try to find a reusable token before creating a brand new one!
$sql .= "INSERT INTO tokens (token_owner, token_issuer, token_id, token_status) VALUES ('" . $this->getId() . "', '" . $this->getId() . "', '{$token}', '" . TOKEN_UNUSED . "');\n";
$sql .= "INSERT INTO connections (user_id, token_id, timestamp_in, node_id, node_ip, last_updated, user_mac) VALUES ('" . $this->getId() . "', '{$token}', CURRENT_TIMESTAMP, '{$node_id}', '{$node_ip}', CURRENT_TIMESTAMP, '{$mac}')";
$db->execSqlUpdate($sql, false);
$retval = $token;
} else {
$retval = false;
}
} else {
$retval = false;
}
return $retval;
}
* @version Subversion $Id$
* @link http://www.wifidog.org/
*/
/**
* Load common include file
*/
require_once 'admin_common.php';
require_once 'classes/Node.php';
require_once 'classes/Network.php';
require_once 'classes/AbstractGeocoder.php';
require_once 'classes/MainUI.php';
require_once 'classes/Server.php';
$ui = MainUI::getObject();
$ui->setTitle(_("Hotspot location map"));
if (!empty($_REQUEST['node_id'])) {
$node = Node::getObject($_REQUEST['node_id']);
// Add Google Maps JavaScript ( must set config values )
$html_headers = "<script src=\"http://maps.google.com/maps?file=api&v=1&key=" . VirtualHost::getCurrentVirtualHost()->getGoogleAPIKey() . "\" type=\"text/javascript\"></script>";
$ui->appendHtmlHeadContent($html_headers);
// Create HTML body
$html = _("Click anywhere on the map to extract the GIS location, then click on the button to save the data.") . "<br>";
$html .= "<div id=\"map_frame\"></div>\n";
$html .= "<input type='button' value='" . _("Use these coordinates") . "' onClick='setLocationInOriginalWindow();'>\n";
$ui->addContent('main_area_middle', $html);
if (($gisLocation = $node->getGisLocation()) !== null && $gisLocation->getLatitude() != null) {
} elseif (($gisLocation = $node->getNetwork()->getGisLocation()) !== null && $gisLocation->getLatitude() != null) {
} else {
$html .= "<div class='error'>" . _("Error: You need to set the GIS coordinates of the center of your network") . "</div\n";
$gisLocation = null;
}
if ($gisLocation !== null) {
} else {
$verbose = false;
}
if (!is_dir(WIFIDOG_ABS_FILE_PATH . NODE_PUBLIC_STATS_DIR)) {
mkdir(WIFIDOG_ABS_FILE_PATH . NODE_PUBLIC_STATS_DIR, 0775);
}
$db = AbstractDb::getObject();
$sql = "SELECT node_id, network_id, allows_public_stats FROM nodes WHERE node_deployment_status != 'PERMANENTLY_CLOSED'";
$result = null;
$db->execSql($sql, $result, false);
$session = Session::getObject();
//print_r($result);
$statistics_object = new YearlyStats();
$session->set('current_statistics_object', $statistics_object);
foreach ($result as $row) {
$node = Node::getObject($row['node_id']);
$network = $node->getNetwork();
if ($node->getAllowsPublicStats()) {
$statistics_object->setSelectedNodes(array($node->getId() => $node));
$statistics_object->setSelectedNetworks(array($network->getId() => $network));
if ($verbose) {
print "Processing node {$node->getId()} ... mem used: " . memory_get_usage() . "\n";
}
//Make sure folder exists
if (!is_dir($node->getPublicStatsDir())) {
mkdir($node->getPublicStatsDir(), 0775);
}
//Write the index file header
$index = fopen($node->getPublicStatsDir() . $node->getPublicStatsFile(), "w");
fwrite($index, "" . '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">' . "\n" . "<html>\n<head>\n" . '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">' . "\n" . '<meta http-equiv="Pragma" content="no-cache">' . "\n" . "<title>Statistics for {$node->getName()}</title>\n" . '<link rel="stylesheet" type="text/css" href="/media/public_stats/stylesheet.css">' . "\n" . '<link rel="stylesheet" type="text/css" media="print" href="/media/base_theme/printer.css">' . "\n" . "</head>\n<body>\n" . "<h1>Statistics for {$node->getName()}</h1>\n");
//Generate reports
/**
* Retreives the output of this object.
*
* @return void
*
* @author Benoit Grégoire <*****@*****.**>
* @author Francois Proulx <*****@*****.**>
* @author Max Horváth <*****@*****.**>
* @copyright 2004-2006 Benoit Grégoire, Technologies Coeus inc.
* @copyright 2004-2006 Francois Proulx, Technologies Coeus inc.
* @copyright 2006 Max Horváth, Horvath Web Consulting
*/
public function getOutput()
{
$db = AbstractDb::getObject();
// Root node
$_rss = $this->_xmldoc->createElement("rss");
$this->_xmldoc->appendChild($_rss);
$_rss->setAttribute('version', '2.0');
// channel
$_channel = $this->_xmldoc->createElement("channel");
$_rss->appendChild($_channel);
/*
* Required channel elements
*/
// title
$_title = $this->_xmldoc->createElement("title");
$_title = $_channel->appendChild($_title);
$_textNode = $this->_xmldoc->createTextNode($this->_network->getName() . ": " . _("Newest Hotspots"));
$_title->appendChild($_textNode);
// link
$_link = $this->_xmldoc->createElement("link");
$_channel->appendChild($_link);
$_textNode = $this->_xmldoc->createTextNode($this->_network->getWebSiteURL());
$_link->appendChild($_textNode);
// description
$_description = $this->_xmldoc->createElement("description");
$_channel->appendChild($_description);
$_textNode = $this->_xmldoc->createTextNode(_("List of the most recent Hotspots opened by the network: ") . $this->_network->getName());
$_description->appendChild($_textNode);
/*
* Optional channel elements
*/
// language
$_language = $this->_xmldoc->createElement("language");
$_channel->appendChild($_language);
if (User::getCurrentUser() != null) {
$_textNode = $this->_xmldoc->createTextNode(substr(User::getCurrentUser()->getPreferedLocale(), 0, 5));
} else {
$_textNode = $this->_xmldoc->createTextNode("en-US");
}
$_language->appendChild($_textNode);
// copyright
$_copyright = $this->_xmldoc->createElement("copyright");
$_channel->appendChild($_copyright);
$_textNode = $this->_xmldoc->createTextNode(_("Copyright ") . $this->_network->getName());
$_copyright->appendChild($_textNode);
// webMaster
if ($this->_network->getTechSupportEmail() != "") {
$_webMaster = $this->_xmldoc->createElement("webMaster");
$_channel->appendChild($_webMaster);
$_textNode = $this->_xmldoc->createTextNode($this->_network->getTechSupportEmail());
$_webMaster->appendChild($_textNode);
}
// pubDate
$_pubDate = $this->_xmldoc->createElement("pubDate");
$_channel->appendChild($_pubDate);
$_textNode = $this->_xmldoc->createTextNode(gmdate("D, d M Y H:i:s \\G\\M\\T", time()));
$_pubDate->appendChild($_textNode);
/**
* lastBuildDate
*
* <lastBuildDate> -- The date-time the last time the content of the
* channel changed.
*
* Make a request through the database for the latest modification date
* of an object.
*
* @todo The latest modification date of an object should be an
* object property
*/
$db->execSqlUniqueRes("SELECT EXTRACT(epoch FROM MAX(creation_date)) as date_last_hotspot_opened FROM nodes WHERE network_id = '" . $db->escapeString($this->_network->getId()) . "' AND (node_deployment_status = 'DEPLOYED' OR node_deployment_status = 'NON_WIFIDOG_NODE')", $_lastHotspotRow, false);
$_lastBuildDate = $this->_xmldoc->createElement("lastBuildDate");
$_channel->appendChild($_lastBuildDate);
$_textNode = $this->_xmldoc->createTextNode(gmdate("D, d M Y H:i:s \\G\\M\\T", $_lastHotspotRow['date_last_hotspot_opened']));
$_lastBuildDate->appendChild($_textNode);
// generator
$_generator = $this->_xmldoc->createElement("generator");
$_channel->appendChild($_generator);
$_textNode = $this->_xmldoc->createTextNode(WIFIDOG_NAME . " " . WIFIDOG_VERSION);
$_generator->appendChild($_textNode);
// docs
$_docs = $this->_xmldoc->createElement("docs");
$_channel->appendChild($_docs);
$_textNode = $this->_xmldoc->createTextNode("http://blogs.law.harvard.edu/tech/rss");
$_docs->appendChild($_textNode);
// image
/*if (defined('NETWORK_LOGO_NAME') && file_exists(WIFIDOG_ABS_FILE_PATH . "local_content/common/" . constant('NETWORK_LOGO_NAME'))) {
$_image = $this->_xmldoc->createElement("image");
$_channel->appendChild($_image);
// title
$_title = $this->_xmldoc->createElement("title");
$_image->appendChild($_title);
$_textNode = $this->_xmldoc->createTextNode($this->_network->getName() . ": " . _("Newest Hotspots"));
$_title->appendChild($_textNode);
// url
$_url = $this->_xmldoc->createElement("url");
$_image->appendChild($_url);
$_textNode = $this->_xmldoc->createTextNode(COMMON_CONTENT_URL . NETWORK_LOGO_NAME);
$_url->appendChild($_textNode);
// link
$_link = $this->_xmldoc->createElement("link");
$_image->appendChild($_link);
$_textNode = $this->_xmldoc->createTextNode($this->_network->getWebSiteURL());
$_link->appendChild($_textNode);
$_imageSize = @getimagesize(WIFIDOG_ABS_FILE_PATH . "local_content/common/" . NETWORK_LOGO_NAME);
if ($_imageSize) {
// width
$_width = $this->_xmldoc->createElement("width");
$_image->appendChild($_width);
$_textNode = $this->_xmldoc->createTextNode($_imageSize[0]);
$_width->appendChild($_textNode);
// height
$_height = $this->_xmldoc->createElement("height");
$_image->appendChild($_height);
$_textNode = $this->_xmldoc->createTextNode($_imageSize[1]);
$_height->appendChild($_textNode);
}
// description
$_description = $this->_xmldoc->createElement("description");
$_image->appendChild($_description);
$_textNode = $this->_xmldoc->createTextNode(_("List of the most recent Hotspots opened by the network: ") . $this->_network->getName());
$_description->appendChild($_textNode);
}*/
// Node details
if ($this->_nodes) {
foreach ($this->_nodes as $_nodeData) {
$_node = Node::getObject($_nodeData['node_id']);
$this->_network = $_node->getNetwork();
$_hotspot = $this->_xmldoc->createElement("item");
$_hotspot = $_channel->appendChild($_hotspot);
// Hotspot name
$_hotspotName = $this->_xmldoc->createElement("title", htmlspecialchars($_node->getName(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotName);
// Hotspot Website URL
if ($_node->getWebSiteURL() != "") {
$_hotspotUrl = $this->_xmldoc->createElement("link", htmlspecialchars($_node->getWebSiteURL(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotUrl);
}
// Hotspot name
$_hotspotDesc = $this->_xmldoc->createElement("description");
$_hotspot->appendChild($_hotspotDesc);
$_descriptionText = '<p>';
// Hotspot global status
if ($_node->getDeploymentStatus() != 'NON_WIFIDOG_NODE') {
if ($_nodeData['is_up'] == 't') {
$_descriptionText .= "<img src='" . COMMON_IMAGES_URL . "HotspotStatus/up.gif' alt='up' />";
} else {
$_descriptionText .= "<img src='" . COMMON_IMAGES_URL . "HotspotStatus/down.gif' alt='down' />";
}
}
// Description
if ($_node->getDescription() != "") {
$_descriptionText .= htmlspecialchars($_node->getDescription(), ENT_QUOTES);
}
$_descriptionText .= '</p>';
$_descriptionText .= '<p>';
$_descriptionText .= _("Address") . ": ";
// Civic number
if ($_node->getCivicNumber() != "") {
$_descriptionText .= $_node->getCivicNumber() . ", ";
}
// Street address
if ($_node->getStreetName() != "") {
$_descriptionText .= htmlspecialchars($_node->getStreetName(), ENT_QUOTES) . ", ";
}
// City
if ($_node->getCity() != "") {
$_descriptionText .= htmlspecialchars($_node->getCity(), ENT_QUOTES) . ", ";
}
// Province
if ($_node->getProvince() != "") {
$_descriptionText .= htmlspecialchars($_node->getProvince(), ENT_QUOTES) . ", ";
}
// Postal code
if ($_node->getPostalCode() != "") {
$_descriptionText .= $_node->getPostalCode() . ", ";
}
// Country
if ($_node->getCountry() != "") {
$_descriptionText .= htmlspecialchars($_node->getCountry(), ENT_QUOTES);
}
// Map Url
if ($_node->getMapURL() != "") {
$_descriptionText .= " <a href='" . $_node->getMapURL() . "'>" . _("See Map") . "</a>";
}
// Mass transit info
if ($_node->getTransitInfo() != "") {
$_descriptionText .= "<br />";
$_descriptionText .= htmlspecialchars($_node->getTransitInfo(), ENT_QUOTES);
}
$_descriptionText .= "</p>";
if ($_node->getEmail() != "" || $_node->getTelephone() != "") {
$_descriptionText .= "<p>";
$_descriptionText .= _("Contact") . ": ";
// Contact e-mail
if ($_node->getEmail() != "") {
$_descriptionText .= "<br /><a href='mailto:" . $_node->getEmail() . "'>" . $_node->getEmail() . "</a>";
}
// Contact phone
if ($_node->getTelephone() != "") {
$_descriptionText .= "<br />" . $_node->getTelephone();
}
$_descriptionText .= "</p>";
}
$_hotspotDesc->appendChild($this->_xmldoc->createTextNode($_descriptionText));
// guid
if ($_node->getWebSiteURL() != "") {
$_guid = $this->_xmldoc->createElement("guid");
$_guid->setAttribute('isPermaLink', 'false');
$_hotspot->appendChild($_guid);
$_textNode = $this->_xmldoc->createTextNode(htmlspecialchars($_node->getWebSiteURL(), ENT_QUOTES));
$_guid->appendChild($_textNode);
}
// pubDate
$_hotspotOpeningDate = $this->_xmldoc->createElement("pubDate", gmdate("D, d M Y H:i:s \\G\\M\\T", $_nodeData['creation_date_epoch']));
$_hotspot->appendChild($_hotspotOpeningDate);
}
}
echo $this->_xmldoc->saveXML();
}
/** 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();
global $account_status_to_text;
global $token_to_text;
// Init values
$html = "";
$_date_from = "";
$_date_to = "";
// Process input
if (isset($_REQUEST['date_from'])) {
$_date_from = $_REQUEST['date_from'];
}
if (isset($_REQUEST['date_to'])) {
$_date_to = $_REQUEST['date_to'];
}
$selected_users = $this->stats->getSelectedUsers();
if ($selected_users) {
foreach ($selected_users as $user_id => $userObject) {
if ($userObject) {
$userinfo = null;
$user_id = $db->escapeString($user_id);
$sql = "SELECT * FROM users WHERE user_id='{$user_id}'";
$db->execSqlUniqueRes($sql, $userinfo, false);
if ($userinfo == null) {
throw new Exception(sprintf(_("User id: %s could not be found in the database"), $user_id));
}
$userinfo['account_status_description'] = $account_status_to_text[$userinfo['account_status']];
$html .= "<fieldset>\n";
$html .= "<legend>" . _("Profile") . "</legend>\n";
$html .= "<table>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Username") . ":</th>\n";
$html .= " <td>" . $userinfo['username'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Email") . ":</th>\n";
$html .= " <td>" . $userinfo['email'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Network") . ":</th>\n";
$html .= " <td><a href='?date_from={$_date_from}&date_to={$_date_to}&network_id={$userinfo['account_origin']}'>{$userinfo['account_origin']}</a></td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Unique ID") . ":</th>\n";
$html .= " <td>" . $userinfo['user_id'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Member since") . ":</th>\n";
$html .= " <td>" . strftime("%c", strtotime($userinfo['reg_date'])) . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Account Status") . ":</th>\n";
$html .= " <td>" . $userinfo['account_status_description'] . "</td>\n";
$html .= "</tr>\n";
$html .= "<tr class='odd'>\n";
$html .= " <th>" . _("Prefered Locale") . ":</th>\n";
$html .= " <td>" . $userinfo['prefered_locale'] . "</td>\n";
$html .= "</tr>\n";
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
/******* Connections **********/
$html .= "<fieldset>\n";
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("*");
$sql = "{$candidate_connections_sql} ORDER BY timestamp_in DESC";
$db->execSql($sql, $connections, false);
$html .= "<legend>" . sprintf(_("%d Connections"), count($connections)) . "</legend>\n";
// Variables init
$even = 0;
$total = array();
$total['incoming'] = 0;
$total['outgoing'] = 0;
$total['time_spent'] = 0;
if (count($connections) == 0) {
$html .= _("No information found matching the report configuration");
} else {
$html .= "<table class='smaller'>\n";
$html .= "<thead>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Logged in") . "</th>\n";
$html .= " <th>" . _("Time spent") . "</th>\n";
$html .= " <th>" . _("Token status") . "</th>\n";
$html .= " <th>" . _("Node") . "</th>\n";
$html .= " <th>" . _("IP") . "</th>\n";
$html .= " <th>" . _("D") . "</th>\n";
$html .= " <th>" . _("U") . "</th>\n";
$html .= "</tr>\n";
$html .= "</thead>\n";
foreach ($connections as $connection) {
$timestamp_in = !empty($connection['timestamp_in']) ? strtotime($connection['timestamp_in']) : null;
$timestamp_out = !empty($connection['timestamp_out']) ? strtotime($connection['timestamp_out']) : null;
$nodeObject = Node::getObject($connection['node_id']);
$total['incoming'] += $connection['incoming'];
$total['outgoing'] += $connection['outgoing'];
$connection['token_status_description'] = $token_to_text[$connection['token_status']];
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>" . strftime("%c", $timestamp_in) . "</td>\n";
if (!empty($timestamp_in) && !empty($timestamp_out)) {
$total['time_spent'] += $timestamp_out - $timestamp_in;
$html .= "<td>" . Utils::convertSecondsToWords($timestamp_out - $timestamp_in) . "</td>\n";
} else {
$html .= "<td>" . _("N/A") . "</td>\n";
}
$html .= " <td>" . $connection['token_status'] . "</td>\n";
$html .= " <td><a href='?date_from={$_date_from}&date_to={$_date_to}&node_id={$nodeObject->getId()}'>{$nodeObject->getName()}</a></td>\n";
$html .= " <td>" . $connection['user_ip'] . "</td>\n";
$html .= " <td>" . Utils::convertBytesToWords($connection['incoming']) . "</td>\n";
$html .= " <td>" . Utils::convertBytesToWords($connection['outgoing']) . "</td>\n";
$html .= "</tr>\n";
}
$html .= "<tr>\n";
$html .= " <th>" . _("Total") . ":</th>\n";
$html .= " <th>" . Utils::convertSecondsToWords($total['time_spent']) . "</th>\n";
$html .= " <td></td>\n";
$html .= " <td></td>\n";
$html .= " <td></td>\n";
$html .= " <th>" . Utils::convertBytesToWords($total['incoming']) . "</th>\n";
$html .= " <th>" . Utils::convertBytesToWords($total['outgoing']) . "</th>\n";
$html .= "</tr>\n";
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
if ($this->stats->getDistinguishUsersBy() == 'user_id') {
/******* MAC addresses **********/
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("user_mac,count(user_mac) as nb ");
$sql = "{$candidate_connections_sql} group by user_mac order by nb desc";
$db->execSql($sql, $rows, false);
$html .= "<fieldset>\n";
$html .= "<legend>" . sprintf(_("%d MAC addresses"), count($rows)) . "</legend>\n";
$html .= "<table>\n";
$html .= "<thead>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("MAC") . "</th>\n";
$html .= " <th>" . _("Count") . "</th>\n";
$html .= "</tr>\n";
$html .= "</thead>\n";
$even = 0;
if ($rows) {
foreach ($rows as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['user_mac']}</td>\n";
$html .= " <td>" . $row['nb'] . "</td>\n";
$html .= "</tr>\n";
}
}
$html .= "</table>\n";
$html .= "</fieldset>\n";
} else {
/******* Usernames **********/
$candidate_connections_sql = $this->stats->getSqlCandidateConnectionsQuery("connections.user_id,username, count(connections.user_id) as nb ", true);
$sql = "{$candidate_connections_sql} group by connections.user_id, username order by nb desc, connections.user_id,username";
$db->execSql($sql, $rows, false);
$html .= "<fieldset>\n";
$html .= "<legend>" . sprintf(_("%d users"), count($rows)) . "</legend>\n";
$html .= "<table>\n";
$html .= "<thead>\n";
$html .= "<tr>\n";
$html .= " <th>" . _("Username") . "</th>\n";
$html .= " <th>" . _("Count") . "</th>\n";
$html .= "</tr>\n";
$html .= "</thead>\n";
$even = 0;
if ($rows) {
foreach ($rows as $row) {
$html .= $even ? "<tr>\n" : "<tr class='odd'>\n";
if ($even == 0) {
$even = 1;
} else {
$even = 0;
}
$html .= " <td>{$row['username']}</td>\n";
$html .= " <td>" . $row['nb'] . "</td>\n";
$html .= "</tr>\n";
}
}
$html .= "</table>\n";
$html .= "</fieldset>\n";
}
}
}
return parent::getReportUI($html);
}
/**
* Retreives the output of this object.
*
* @param bool $return_object If true this function only returns the DOM object
*
* @return string The XML output
*
* @author Benoit Grégoire <*****@*****.**>
* @author Francois Proulx <*****@*****.**>
* @author Max Horváth <*****@*****.**>
* @copyright 2004-2006 Benoit Grégoire, Technologies Coeus inc.
* @copyright 2004-2006 Francois Proulx, Technologies Coeus inc.
* @copyright 2006 Max Horváth, Horvath Web Consulting
*/
public function getOutput($return_object = false)
{
// Root node
$_hotspotStatusRootNode = $this->_xmldoc->createElement("wifidogHotspotsStatus");
$_hotspotStatusRootNode->setAttribute('version', '1.0');
$this->_xmldoc->appendChild($_hotspotStatusRootNode);
// Document metadata
$_documentGendateNode = $this->_xmldoc->createElement("generationDateTime", gmdate("Y-m-d\\Th:m:s\\Z"));
$_hotspotStatusRootNode->appendChild($_documentGendateNode);
// Network metadata
$_networkMetadataNode = $this->_xmldoc->createElement("networkMetadata");
$_networkMetadataNode = $_hotspotStatusRootNode->appendChild($_networkMetadataNode);
$_networkUriNode = $this->_xmldoc->createElement("networkUri", htmlspecialchars($this->_network->getWebSiteURL(), ENT_QUOTES));
$_networkMetadataNode->appendChild($_networkUriNode);
$_networkNameNode = $this->_xmldoc->createElement("name", htmlspecialchars($this->_network->getName(), ENT_QUOTES));
$_networkMetadataNode->appendChild($_networkNameNode);
$_networkUrlNode = $this->_xmldoc->createElement("websiteUrl", htmlspecialchars($this->_network->getWebSiteURL(), ENT_QUOTES));
$_networkMetadataNode->appendChild($_networkUrlNode);
$_email = $this->_network->getTechSupportEmail();
if (!empty($email)) {
$_networkEmailNode = $this->_xmldoc->createElement("techSupportEmail", $_email);
$_networkMetadataNode->appendChild($_networkEmailNode);
}
$_nodesCountNode = $this->_xmldoc->createElement("hotspotsCount", count($this->_nodes));
$_networkMetadataNode->appendChild($_nodesCountNode);
$_networkValidUsersNode = $this->_xmldoc->createElement("validSubscribedUsersCount", $this->_network->getNumValidUsers());
$_networkMetadataNode->appendChild($_networkValidUsersNode);
// Get number of online users
$_networkOnlineUsersNode = $this->_xmldoc->createElement("onlineUsersCount", $this->_network->getNumOnlineUsers());
$_networkMetadataNode->appendChild($_networkOnlineUsersNode);
// Node details
if ($this->_nodes) {
// Hotspots metadata
$_hotspotsMetadataNode = $this->_xmldoc->createElement("hotspots");
$_hotspotsMetadataNode = $_hotspotStatusRootNode->appendChild($_hotspotsMetadataNode);
foreach ($this->_nodes as $_nodeData) {
$_node = Node::getObject($_nodeData['node_id']);
$this->_network = $_node->getNetwork();
$_hotspot = $this->_xmldoc->createElement("hotspot");
$_hotspot = $_hotspotsMetadataNode->appendChild($_hotspot);
// Hotspot ID
$_hotspotId = $this->_xmldoc->createElement("hotspotId", $_node->getId());
$_hotspot->appendChild($_hotspotId);
// Hotspot name
$_hotspotName = $this->_xmldoc->createElement("name", htmlspecialchars($_node->getName(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotName);
/**
* (1..n) A Hotspot has many node
*
* WARNING For now, we are simply duplicating the hotspot data in node
* Until wifidog implements full abstractiong hotspot vs nodes.
*/
$_nodes = $this->_xmldoc->createElement("nodes");
$_hotspot->appendChild($_nodes);
$_nodeMetadataNode = $this->_xmldoc->createElement("node");
$_nodes->appendChild($_nodeMetadataNode);
// Node ID
$_nodeId = $this->_xmldoc->createElement("nodeId", $_node->getId());
$_nodeMetadataNode->appendChild($_nodeId);
// Online Users
$_nodeUserNum = $this->_xmldoc->createElement("numOnlineUsers", $_node->GetNumOnlineUsers());
$_nodeMetadataNode->appendChild($_nodeUserNum);
$_nodeCreationDate = $this->_xmldoc->createElement("creationDate", $_node->getCreationDate());
$_nodeMetadataNode->appendChild($_nodeCreationDate);
if ($_node->getDeploymentStatus() != 'NON_WIFIDOG_NODE') {
if ($_nodeData['is_up'] == 't') {
$_nodeStatus = $this->_xmldoc->createElement("status", "up");
} else {
$_nodeStatus = $this->_xmldoc->createElement("status", "down");
}
$_nodeMetadataNode->appendChild($_nodeStatus);
}
if (($_gisData = $_node->getGisLocation()) !== null) {
$_nodeGis = $this->_xmldoc->createElement("gisLatLong");
$_nodeGis->setAttribute("lat", $_gisData->getLatitude());
$_nodeGis->setAttribute("long", $_gisData->getLongitude());
$_nodeMetadataNode->appendChild($_nodeGis);
}
// Hotspot opening date ( for now it's called creation_date )
$_hotspotOpeningDate = $this->_xmldoc->createElement("openingDate", $_node->getCreationDate());
$_hotspot->appendChild($_hotspotOpeningDate);
// Hotspot Website URL
if ($_node->getWebSiteURL() != "") {
$_hotspotUrl = $this->_xmldoc->createElement("webSiteUrl", htmlspecialchars($_node->getWebSiteURL(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotUrl);
}
// Hotspot global status
if ($_node->getDeploymentStatus() != 'NON_WIFIDOG_NODE') {
if ($_nodeData['is_up'] == 't') {
$_hotspotStatus = $this->_xmldoc->createElement("globalStatus", "100");
} else {
$_hotspotStatus = $this->_xmldoc->createElement("globalStatus", "0");
}
$_hotspot->appendChild($_hotspotStatus);
}
// Description
if ($_node->getDescription() != "") {
$_hotspotDesc = $this->_xmldoc->createElement("description", htmlspecialchars($_node->getDescription(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotDesc);
}
// Map Url
if ($_node->getMapURL() != "") {
$_hotspotMapUrl = $this->_xmldoc->createElement("mapUrl", htmlspecialchars($_node->getMapURL(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotMapUrl);
}
// Mass transit info
if ($_node->getTransitInfo() != "") {
$_hotspotTransit = $this->_xmldoc->createElement("massTransitInfo", htmlspecialchars($_node->getTransitInfo(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotTransit);
}
// Contact e-mail
if ($_node->getEmail() != "") {
$_hotspotContactEmail = $this->_xmldoc->createElement("contactEmail", $_node->getEmail());
$_hotspot->appendChild($_hotspotContactEmail);
}
// Contact phone
if ($_node->getTelephone() != "") {
$_hotspotContactPhone = $this->_xmldoc->createElement("contactPhoneNumber", $_node->getTelephone());
$_hotspot->appendChild($_hotspotContactPhone);
}
// Civic number
if ($_node->getCivicNumber() != "") {
$_hotspotCivicNr = $this->_xmldoc->createElement("civicNumber", $_node->getCivicNumber());
$_hotspot->appendChild($_hotspotCivicNr);
}
// Street address
if ($_node->getStreetName() != "") {
$_hotspotStreet = $this->_xmldoc->createElement("streetAddress", htmlspecialchars($_node->getStreetName(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotStreet);
}
// City
if ($_node->getCity() != "") {
$_hotspotCity = $this->_xmldoc->createElement("city", htmlspecialchars($_node->getCity(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotCity);
}
// Province
if ($_node->getProvince() != "") {
$_hotspotProvince = $this->_xmldoc->createElement("province", htmlspecialchars($_node->getProvince(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotProvince);
}
// Postal code
if ($_node->getPostalCode() != "") {
$_hotspotPostalCode = $this->_xmldoc->createElement("postalCode", $_node->getPostalCode());
$_hotspot->appendChild($_hotspotPostalCode);
}
// Country
if ($_node->getCountry() != "") {
$_hotspotCountry = $this->_xmldoc->createElement("country", htmlspecialchars($_node->getCountry(), ENT_QUOTES));
$_hotspot->appendChild($_hotspotCountry);
}
// Long / Lat
if (($_gisData = $_node->getGisLocation()) !== null) {
$_hotspotGis = $this->_xmldoc->createElement("gisCenterLatLong");
$_hotspotGis->setAttribute("lat", $_gisData->getLatitude());
$_hotspotGis->setAttribute("long", $_gisData->getLongitude());
$_hotspotGis->setAttribute("show", $_node->showOnMap());
$_hotspot->appendChild($_hotspotGis);
}
}
}
if ($return_object) {
return $this->_xmldoc;
} else {
echo $this->_xmldoc->saveXML();
}
}
/**
* Retreives the output of this object.
*
* @return string The XML output
*
* @author Benoit Grégoire <*****@*****.**>
* @author Francois Proulx <*****@*****.**>
* @author Max Horváth <*****@*****.**>
* @copyright 2004-2006 Benoit Grégoire, Technologies Coeus inc.
* @copyright 2004-2006 Francois Proulx, Technologies Coeus inc.
* @copyright 2006 Max Horváth, Horvath Web Consulting
*/
public function getOutput()
{
$this->_csv_document = "jiwire_ref, status, provider_id, provider_name, address, address2, city, state, country, postal_code, website_url, email, phone, fax, location_name, location_type_id, field17, node_type, ssid, fee_comments, equipement, standard_802_11, MAC_address, network_drop, latitude, longitude\r\n";
if ($this->_nodes) {
foreach ($this->_nodes as $_nodeData) {
$_node = Node::getObject($_nodeData['node_id']);
// No JiWire ref. number, status NEW
$this->_csv_document .= "###,NEW,";
// Provider Name = Wifidog node ID
$this->_csv_document .= $this->quoteForCsv($_node->getId()) . ",";
// Provider Id = Wifidog Network name
$this->_csv_document .= $this->quoteForCsv($this->_network->getName()) . ",";
// Address
$this->_csv_document .= $this->quoteForCsv($_node->getCivicNumber() . ", " . $_node->getStreetName()) . ",";
// Address 2 (skipped)
$this->_csv_document .= ",";
// City
$this->_csv_document .= $this->quoteForCsv($_node->getCity()) . ",";
// State
$this->_csv_document .= $this->quoteForCsv($_node->getProvince()) . ",";
// Country
$this->_csv_document .= $this->quoteForCsv($_node->getCountry()) . ",";
// Postal code
$this->_csv_document .= $this->quoteForCsv($_node->getPostalCode()) . ",";
// Web Site URL
$this->_csv_document .= $this->quoteForCsv($_node->getWebSiteURL()) . ",";
// Email
$this->_csv_document .= $this->quoteForCsv($_node->getEmail()) . ",";
// Phone number
$this->_csv_document .= $this->quoteForCsv($_node->getTelephone()) . ",";
// Fax (skipped)
$this->_csv_document .= ",";
// Node name
$this->_csv_document .= $this->quoteForCsv($_node->getName()) . ",";
// Location type (JiWite Appendix A --> 5 = Café), field17 (skipped), node_type (2 = free), SSID (skipped), fee comment (skipped), 802.11 type (b or g, skipped),
$this->_csv_document .= "5,, 2,,,,,,,";
// Latitude + longitude
$this->_csv_document .= $_node->getGisLocation()->getLatitude() . "," . $_node->getGisLocation()->getLongitude();
$this->_csv_document .= "\r\n";
}
}
echo $this->_csv_document;
}
/**
* Displays the output of this node list.
*
* @return void
*
* @author Benoit Grégoire <*****@*****.**>
* @author Francois Proulx <*****@*****.**>
* @author Max Horváth <*****@*****.**>
* @copyright 2004-2006 Benoit Grégoire, Technologies Coeus inc.
* @copyright 2004-2006 Francois Proulx, Technologies Coeus inc.
* @copyright 2006 Max Horváth, Horvath Web Consulting
*/
public function getOutput()
{
// Init ALL smarty values
$this->_smarty->assign('DEPRECATEDisSuperAdmin', false);
$this->_smarty->assign('GMapsEnabled', false);
$this->_smarty->assign('nodes', array());
$this->_smarty->assign('num_deployed_nodes', 0);
$this->_smarty->assign('PdfSupported', false);
$userData['preSelectedObject'] = $this->_network;
$userData['allowEmpty'] = true;
$userData['nullCaptionString'] = _("All");
$userData['onChange'] = "submit.click();";
$this->_smarty->assign('selectNetworkUI', Network::getSelectUI('network_id', $userData) . (count(Network::getAllNetworks()) > 1 ? '<input class="submit" type="submit" name="submit" value="' . _("Change network") . '">' : ""));
$this->_smarty->assign('selectedNetworkName', $this->_network === null ? _("All networks") : $this->_network->getName());
/**
* Define user security levels for the template
*
* These values are used in the default template of WiFoDog but could be used
* in a customized template to restrict certain links to specific user
* access levels.
*/
$this->_smarty->assign('DEPRECATEDisSuperAdmin', $this->_currentUser && $this->_currentUser->DEPRECATEDisSuperAdmin());
if (defined('GMAPS_HOTSPOTS_MAP_ENABLED') && GMAPS_HOTSPOTS_MAP_ENABLED == true) {
$this->_smarty->assign('GMapsEnabled', true);
}
$_html = null;
/*
* Main content
*/
// Reset ALL smarty SWITCH values
$this->_smarty->assign('sectionMAINCONTENT', false);
// Set section of Smarty template
$this->_smarty->assign('sectionMAINCONTENT', true);
// Node details
if ($this->_nodes) {
foreach ($this->_nodes as $_nodeData) {
$_node = Node::getObject($_nodeData['node_id']);
$_nodeData['num_online_users'] = $_node->getNumOnlineUsers();
$this->_smarty->append("nodes", $_nodeData);
}
}
$this->_smarty->assign("num_deployed_nodes", count($this->_nodes));
// Compile HTML code
$_html_body = $this->_smarty->fetch("templates/sites/hotspot_status.tpl");
/*
* Compile HTML output
*/
$this->_mainUI->setTitle(_("Hotspot list"));
$this->_mainUI->appendHtmlHeadContent('<link rel="alternate" type="application/rss+xml" title="' . ($this->_network === null ? _("All networks") : $this->_network->getName()) . ": " . _("Newest Hotspots") . '" href="' . BASE_SSL_PATH . 'hotspot_status.php?format=RSS">');
$this->_mainUI->addContent('left_area_middle', $_html);
$this->_mainUI->addContent('main_area_middle', $_html_body);
$this->_mainUI->display();
}
/**
* Displays the output of this object.
*
* @return void
*
* @author Benoit Gregoire <*****@*****.**>
* @author Francois Proulx <*****@*****.**>
* @author Max Horváth <*****@*****.**>
* @author Joe Bowser <*****@*****.**>
* @copyright 2004-2006 Benoit Gregoire, Technologies Coeus inc.
* @copyright 2004-2006 Francois Proulx, Technologies Coeus inc.
* @copyright 2006 Max Horváth, Horvath Web Consulting
* @copyright 2006 Joe Bowser
*/
public function getOutput()
{
$_kml = $this->_xmldoc->createElement("kml");
$_kml->setAttribute('xmlns', 'http://earth.google.com/kml/2.0');
$this->_xmldoc->appendChild($_kml);
// Document
$_document = $this->_xmldoc->createElement("Document");
$_kml->appendChild($_document);
/*
* Style Elements (Up Nodes)
*/
$_style_up = $this->_xmldoc->createElement("Style");
$_style_up->setAttribute('id', 'node_up');
$_document->appendChild($_style_up);
$_iconStyle = $this->_xmldoc->createElement("IconStyle");
$_style_up->appendChild($_iconStyle);
/* Since scale is the same, we only have to define it once */
$_scale = $this->_xmldoc->createElement("scale");
$_iconStyle->appendChild($_scale);
$_textNode = $this->_xmldoc->createTextNode("0.5");
$_scale->appendChild($_textNode);
$_icon = $this->_xmldoc->createElement("Icon");
$_iconStyle->appendChild($_icon);
$_href = $this->_xmldoc->createElement("href");
$_icon->appendChild($_href);
$_textNode = $this->_xmldoc->createTextNode(BASE_URL_PATH . "images/HotspotStatusMap/up.png");
$_href->appendChild($_textNode);
/*
* Style Elements (Down Nodes)
*/
$_style_down = $this->_xmldoc->createElement("Style");
$_style_down->setAttribute('id', 'node_down');
$_document->appendChild($_style_down);
$_iconStyle = $this->_xmldoc->createElement("IconStyle");
$_style_down->appendChild($_iconStyle);
$_scale = $this->_xmldoc->createElement("scale");
$_iconStyle->appendChild($_scale);
$_textNode = $this->_xmldoc->createTextNode("0.5");
$_scale->appendChild($_textNode);
$_iconStyle->appendChild($_scale);
$_icon = $this->_xmldoc->createElement("Icon");
$_iconStyle->appendChild($_icon);
$_href = $this->_xmldoc->createElement("href");
$_icon->appendChild($_href);
$_textNode = $this->_xmldoc->createTextNode(BASE_URL_PATH . "images/HotspotStatusMap/down.png");
$_href->appendChild($_textNode);
/*
* Style Elements (Unknown Nodes)
*/
$_style_unknown = $this->_xmldoc->createElement("Style");
$_style_unknown->setAttribute('id', 'node_unknown');
$_document->appendChild($_style_unknown);
$_iconStyle = $this->_xmldoc->createElement("IconStyle");
$_style_unknown->appendChild($_iconStyle);
$_scale = $this->_xmldoc->createElement("scale");
$_iconStyle->appendChild($_scale);
$_textNode = $this->_xmldoc->createTextNode("0.5");
$_scale->appendChild($_textNode);
$_icon = $this->_xmldoc->createElement("Icon");
$_iconStyle->appendChild($_icon);
$_href = $this->_xmldoc->createElement("href");
$_icon->appendChild($_href);
$_textNode = $this->_xmldoc->createTextNode(BASE_URL_PATH . "images/HotspotStatusMap/unknown.png");
$_href->appendChild($_textNode);
/*
* Creating the Folder
*/
$_folder = $this->_xmldoc->createElement("Folder");
$_document->appendChild($_folder);
$_name = $this->_xmldoc->createElement("name");
$_folder->appendChild($_name);
$_textNode = $this->_xmldoc->createTextNode($this->_network->getName());
$_name->appendChild($_textNode);
/*
* Creating the Placemarks (Nodes)
*/
if ($this->_nodes) {
foreach ($this->_nodes as $_nodeData) {
$_node = Node::getObject($_nodeData['node_id']);
$this->_network = $_node->getNetwork();
$_placemark = $this->_xmldoc->createElement("Placemark");
$_folder->appendChild($_placemark);
// Hotspot name
$_hotspotName = $this->_xmldoc->createElement("name", htmlspecialchars($_node->getName(), ENT_QUOTES));
$_placemark->appendChild($_hotspotName);
$_html_data = "<b>" . _("Address") . ":</b><br />" . $_node->getCivicNumber() . " " . $_node->getStreetName() . "<br />" . $_node->getCity() . "," . $_node->getProvince() . "<br />" . $_node->getCountry() . "<br />" . $_node->getPostalCode() . "<br /><br /> <b>" . _("URL") . ":</b> <a href='" . $_node->getWebSiteURL() . "'>" . $_node->getWebSiteURL() . "</a> <br /> <b> " . _("Email") . ":</b> <a href='mailto:" . $_node->getEmail() . "'>" . $_node->getEmail() . "</a>";
// Creating the description node with the data from it
$_description = $this->_xmldoc->createElement("description");
$_placemark->appendChild($_description);
$_cdata = $this->_xmldoc->createCDATASection($_html_data);
$_description->appendChild($_cdata);
// Description data goes here
$_point = $this->_xmldoc->createElement("Point");
$_placemark->appendChild($_point);
// Get GIS Location
$_gis_loc = $_node->getGisLocation();
$_gis_string = $_gis_loc->getLongitude() . "," . $_gis_loc->getLatitude() . "," . $_gis_loc->getAltitude();
$_coordinates = $this->_xmldoc->createElement("coordinates", $_gis_string);
// Hotspot global status
if ($_node->getDeploymentStatus() != 'NON_WIFIDOG_NODE') {
if ($_nodeData['is_up'] == 't') {
$_styleURL = $this->_xmldoc->createElement("styleURL", "#node_up");
} else {
$_styleURL = $this->_xmldoc->createElement("styleURL", "#node_down");
}
} else {
$_styleURL = $this->_xmldoc->createElement("styleURL", "#node_unknown");
}
$_point->appendChild($_coordinates);
}
}
echo $this->_xmldoc->saveXML();
}