[Tynstep-svn] r81 - in trunk/step-web-app/src/main/resources: . com/tyndalehouse/step/web/server com/tyndalehouse/step/web/server/config com/tyndalehouse/step/web/server/db com/tyndalehouse/step/web/server/db/queries com/tyndalehouse/step/web/server/db/queries/timeline

[ChrisBurrell at crosswire.org]

Author: ChrisBurrell
Date: 2010-02-09 14:54:01 -0700 (Tue, 09 Feb 2010)
New Revision: 81

Added:
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/conditions/
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_events_for_date_range.sql
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_timeline_setup_data.sql
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/lookup_timeline_origin.sql
Modified:
   trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/config/environment.properties
   trunk/step-web-app/src/main/resources/log4j.properties
Log:


Modified: trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/config/environment.properties
===================================================================
--- trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/config/environment.properties	2010-02-09 21:46:06 UTC (rev 80)
+++ trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/config/environment.properties	2010-02-09 21:54:01 UTC (rev 81)
@@ -1,4 +1,12 @@
 #db.driver=org.apache.derby.jdbc.ClientDriver
 #connection.string=jdbc:derby://localhost:1999/d:\\STEPSVN\\step-web-app\\war\\StepDB
 db.driver=org.apache.derby.jdbc.ClientDriver
+db.user=step
+db.password=step
+db.pool.initialSize=10
+db.pool.maxSize=20
+db.pool.validation.query=SELECT * FROM STEP.EVENT_TYPE
+db.pool.prepared.size=10
 connection.string=jdbc:derby://localhost:1999/d:\\STEPSVN\\step-web-app\\war\\StepDB
+query.repository.path=com/tyndalehouse/step/web/server/db/queries/
+query.runner.cache=false

Added: trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_events_for_date_range.sql
===================================================================
--- trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_events_for_date_range.sql	                        (rev 0)
+++ trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_events_for_date_range.sql	2010-02-09 21:54:01 UTC (rev 81)
@@ -0,0 +1,31 @@
+--------------------------------------------------------
+-- Query get_events_for_date_range.sql
+-- This looks up the events that should be displayed
+-- in a certain time period 
+--------------------------------------------------------
+define min_date as bigint
+define max_date as bigint
+define timeband_id as integer
+
+select 
+	event_id, 
+	from_date, 
+	to_date, 
+	from_precision, 
+	to_precision, 
+	name, 
+	timeband_id, 
+	importance_id,
+	certainty, 
+	event_type_id 
+from 
+	step.event 
+where 
+	(
+			from_date between #min_date# and #max_date#
+		or 	to_date between #min_date# and #max_date#
+		or 	from_date < #min_date# and to_date > #max_date#
+	)
+and 
+	timeband_id = #timeband_id#
+order by from_date

Added: trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_timeline_setup_data.sql
===================================================================
--- trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_timeline_setup_data.sql	                        (rev 0)
+++ trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/get_timeline_setup_data.sql	2010-02-09 21:54:01 UTC (rev 81)
@@ -0,0 +1,12 @@
+--------------------------------------------------------
+-- Query get_timeline_setup_data
+-- This looks up the main definitions for the timebands, 
+-- to initialise the timeline component
+--------------------------------------------------------
+
+select 
+	t.timeband_id, 
+	t.timeband_description,
+	t.timeband_unit
+from 
+	step.timeband t

Added: trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/lookup_timeline_origin.sql
===================================================================
--- trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/lookup_timeline_origin.sql	                        (rev 0)
+++ trunk/step-web-app/src/main/resources/com/tyndalehouse/step/web/server/db/queries/timeline/lookup_timeline_origin.sql	2010-02-09 21:54:01 UTC (rev 81)
@@ -0,0 +1,26 @@
+--------------------------------------------------------
+-- Query lookupTimelineOrigin.sql
+-- This lookups up the origin given a particular passage
+-- then joins on the importance sub_timeband_id field
+-- to work out what the best scale is going to be
+--------------------------------------------------------
+define qry_start as integer
+define qry_end as integer
+
+select 
+    ev.from_date as origin, 
+    ev.TIMEBAND_ID,  
+    case when ev.IMPORTANCE_ID is NULL then st.sub_timeband_unit else imp.sub_timeband_unit end as unit
+from 
+                   step.scripture_reference sr 
+        inner join step.event ev on sr.TARGET_ID = ev.EVENT_ID
+        left join sub_timeband imp on imp.sub_timeband_id = ev.SUB_TIMEBAND_ID
+        left join sub_timeband st on st.SUB_TIMEBAND_ID = ev.SUB_TIMEBAND_ID
+where target_type = 1 
+  and 
+		((#qry_start# between start_verse_id and end_verse_id)    -- the query start is between the event range
+		or (#qry_end# between start_verse_id and end_verse_id)  -- the query end is between the event range
+		or (#qry_start# <= start_verse_id and #qry_end# >= start_verse_id)  -- the query start is before the event start, but the end is after the event start
+		or (#qry_end# >= end_verse_id and #qry_start# <= end_verse_id))     -- the end of the range is greater than event end, but the start is before the end
+  order by (end_verse_id - start_verse_id) asc
+ fetch first row only

Modified: trunk/step-web-app/src/main/resources/log4j.properties
===================================================================
--- trunk/step-web-app/src/main/resources/log4j.properties	2010-02-09 21:46:06 UTC (rev 80)
+++ trunk/step-web-app/src/main/resources/log4j.properties	2010-02-09 21:54:01 UTC (rev 81)
@@ -1,5 +1,5 @@
 # Set root logger level to DEBUG and its only appender to A1.
-log4j.rootLogger=DEBUG, A1, A2
+log4j.rootLogger=INFO, A1, A2
 
 # A1 is set to be a ConsoleAppender.
 # A1 uses PatternLayout.