From f2a5b46dd36fab1f4db439155b7ab3151fd2ac5c Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 23 Sep 2009 17:56:38 +0000
Subject: [PATCH] Create two new indexes on action.circulation &
 action.aged_circulation, and a virtual view for counting circulations by
 year.

M    Open-ILS/src/sql/Pg/002.schema.config.sql
A    Open-ILS/src/sql/Pg/upgrade/0017.schema.circ-target-copy-indexes.sql
M    Open-ILS/examples/fm_IDL.xml


git-svn-id: svn://svn.open-ils.org/ILS/trunk@14129 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/examples/fm_IDL.xml                       | 45 ++++++++++++++++++++++
 Open-ILS/src/sql/Pg/002.schema.config.sql          |  2 +-
 .../0017.schema.circ-target-copy-indexes.sql       | 11 ++++++
 3 files changed, 57 insertions(+), 1 deletion(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0017.schema.circ-target-copy-indexes.sql

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 66f45f2e0c..b9fa062e69 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -5132,6 +5132,51 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 		</links>
 	</class>
 
+    <class id="circbyyr" controller="open-ils.cstore" oils_obj:fieldmapper="action::circ_counts_by_year" oils_persist:tablename="action.circ_counts_by_year" oils_persist:readonly="true">
+		<oils_persist:source_definition>
+
+	SELECT id, SUM(count) AS count, year, is_renewal FROM (
+		SELECT
+			cp.id,
+			COUNT(circ.id),
+			EXTRACT(YEAR FROM circ.xact_start) AS year,
+			(phone_renewal OR desk_renewal OR opac_renewal) as is_renewal
+		FROM
+			asset.copy cp
+				JOIN action.circulation circ ON (cp.id = circ.target_copy)
+		GROUP BY 1, 3, 4
+		UNION
+		SELECT
+			cp.id,
+			COUNT(circ.id),
+			EXTRACT(YEAR FROM circ.xact_start) AS year,
+			(phone_renewal OR desk_renewal OR opac_renewal) as is_renewal
+		FROM
+			asset.copy cp
+				JOIN action.aged_circulation circ ON (cp.id = circ.target_copy)
+		GROUP BY 1, 3, 4
+		UNION
+		SELECT
+			id,
+			circ_count,
+			-1 AS year,
+			false as is_renewal
+		FROM
+			extend_reporter.legacy_circ_count
+	)x GROUP BY 1, 3, 4
+
+		</oils_persist:source_definition>
+        <fields>
+            <field reporter:label="Copy ID" name="id" reporter:datatype="link"/>
+            <field reporter:label="Count" name="count" reporter:datatype="int"/>
+            <field reporter:label="Year" name="year" reporter:datatype="int"/>
+            <field reporter:label="Renewal" name="is_renewal" reporter:datatype="bool"/>
+        </fields>
+		<links>
+			<link field="id" reltype="has_a" key="id" map="" class="acp"/>
+		</links>
+    </class>
+
 	<class id="rodcirc" controller="open-ils.reporter-store" oils_obj:fieldmapper="reporter::overdue_circs" oils_persist:tablename="reporter.overdue_circs" reporter:core="true" reporter:label="Overdue Circulation">
 		<fields oils_persist:primary="id" oils_persist:sequence="money.billable_xact_id_seq">
 			<field reporter:label="Check In Library" name="checkin_lib" reporter:datatype="org_unit"/>
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index a6481c37ff..341cea1efb 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log (
     install_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 );
 
-INSERT INTO config.upgrade_log (version) VALUES ('0016');
+INSERT INTO config.upgrade_log (version) VALUES ('0017');
 
 CREATE TABLE config.bib_source (
 	id		SERIAL	PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/upgrade/0017.schema.circ-target-copy-indexes.sql b/Open-ILS/src/sql/Pg/upgrade/0017.schema.circ-target-copy-indexes.sql
new file mode 100644
index 0000000000..d6bb92d4b5
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0017.schema.circ-target-copy-indexes.sql
@@ -0,0 +1,11 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0017');
+
+CREATE INDEX action_circulation_target_copy_idx
+ON action.circulation (target_copy);
+
+CREATE INDEX action_aged_circulation_target_copy_idx ON
+ON action.aged_circulation (target_copy);
+
+COMMIT;
-- 
2.11.0