From 0bb89778d382fc5758354c6986130ce1ddba7d4c Mon Sep 17 00:00:00 2001
From: friendica <info@friendica.com>
Date: Thu, 26 Mar 2015 19:55:52 -0700
Subject: add channel_lastpost timestamp to help optimise some outrageously
 expensive queries.

---
 install/schema_mysql.sql    |  2 ++
 install/schema_postgres.sql |  2 ++
 install/update.php          | 16 +++++++++++++++-
 3 files changed, 19 insertions(+), 1 deletion(-)

(limited to 'install')

diff --git a/install/schema_mysql.sql b/install/schema_mysql.sql
index b3f992500..c2cefd07d 100644
--- a/install/schema_mysql.sql
+++ b/install/schema_mysql.sql
@@ -219,6 +219,7 @@ CREATE TABLE IF NOT EXISTS `channel` (
   `channel_notifyflags` int(10) unsigned NOT NULL DEFAULT '65535',
   `channel_pageflags` int(10) unsigned NOT NULL DEFAULT '0',
   `channel_dirdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `channel_lastpost` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `channel_deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `channel_max_anon_mail` int(10) unsigned NOT NULL DEFAULT '10',
   `channel_max_friend_req` int(10) unsigned NOT NULL DEFAULT '10',
@@ -282,6 +283,7 @@ CREATE TABLE IF NOT EXISTS `channel` (
   KEY `channel_deleted` (`channel_deleted`),
   KEY `channel_a_republish` (`channel_a_republish`),
   KEY `channel_dirdate` (`channel_dirdate`),
+  KEY `channel_lastpost` (`channel_lastpost`),
   KEY `channel_w_like` (`channel_w_like`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
diff --git a/install/schema_postgres.sql b/install/schema_postgres.sql
index bc72be445..438b29d49 100644
--- a/install/schema_postgres.sql
+++ b/install/schema_postgres.sql
@@ -166,6 +166,7 @@ CREATE TABLE "channel" (
   "channel_notifyflags" bigint  NOT NULL DEFAULT '65535',
   "channel_pageflags" bigint  NOT NULL DEFAULT '0',
   "channel_dirdate" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
+  "channel_lastpost" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
   "channel_deleted" timestamp NOT NULL DEFAULT '0001-01-01 00:00:00',
   "channel_max_anon_mail" bigint  NOT NULL DEFAULT '10',
   "channel_max_friend_req" bigint  NOT NULL DEFAULT '10',
@@ -231,6 +232,7 @@ create index "channel_deleted" on channel ("channel_deleted");
 create index "channel_a_republish" on channel ("channel_a_republish");
 create index "channel_w_like" on channel ("channel_w_like");
 create index "channel_dirdate" on channel ("channel_dirdate");
+create index "channel_lastpost" on channel ("channel_lastpost");
 CREATE TABLE "chat" (
   "chat_id" serial  NOT NULL,
   "chat_room" bigint  NOT NULL DEFAULT '0',
diff --git a/install/update.php b/install/update.php
index 75805513d..36aa20373 100644
--- a/install/update.php
+++ b/install/update.php
@@ -1,6 +1,6 @@
 <?php
 
-define( 'UPDATE_VERSION' , 1139 );
+define( 'UPDATE_VERSION' , 1140 );
 
 /**
  *
@@ -1589,3 +1589,17 @@ function update_r1138() {
 		return UPDATE_SUCCESS;
 	return UPDATE_FAILED;
 }
+
+function update_r1139() {
+	if(ACTIVE_DBTYPE == DBTYPE_POSTGRES) { 
+		$r1 = q("ALTER TABLE channel ADD channel_lastpost timestamp NOT NULL DEFAULT '0001-01-01 00:00:00'");
+		$r2 = q("create index channel_lastpost on channel ( channel_lastpost ) ");
+		$r = $r1 && $r2;
+	}
+	else
+		$r = q("ALTER TABLE `channel` ADD `channel_lastpost` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER `channel_dirdate` , ADD INDEX ( `channel_lastpost` ) ");
+	if($r)
+		return UPDATE_SUCCESS;
+	return UPDATE_FAILED;
+
+}
\ No newline at end of file
-- 
cgit v1.2.3