[Tynstep-svn] r25 - in trunk/StepDataLoader: . lib sql/com/tyndalehouse/step/dataloader/sql sql/com/tyndalehouse/step/dataloader/sql/create src/com/tyndalehouse/step/dataloader src/com/tyndalehouse/step/dataloader/beans src/com/tyndalehouse/step/dataloader/common src/com/tyndalehouse/step/dataloader/loaders src/com/tyndalehouse/step/dataloader/utils

ChrisBurrell at crosswire.org ChrisBurrell at crosswire.org
Tue Nov 24 15:49:22 MST 2009


Author: ChrisBurrell
Date: 2009-11-24 15:49:22 -0700 (Tue, 24 Nov 2009)
New Revision: 25

Added:
   trunk/StepDataLoader/lib/commons-dbutils-1.3.jar
   trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/options for scripture.txt
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TargetTypeEnum.java
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/common/
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/common/DateParsingException.java
Removed:
   trunk/StepDataLoader/lib/commons-dbutils-1.2.jar
   trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/drop/
   trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/utils/
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java
Modified:
   trunk/StepDataLoader/.classpath
   trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/2.scripture_referencing.sql
   trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/3.timeline.sql
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/Dataloader.java
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/PartialDate.java
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineEventBean.java
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/loaders/TimelineLoader.java
   trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/utils/DerbyUtils.java
Log:


Modified: trunk/StepDataLoader/.classpath
===================================================================
--- trunk/StepDataLoader/.classpath	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/.classpath	2009-11-24 22:49:22 UTC (rev 25)
@@ -7,7 +7,8 @@
 	<classpathentry kind="lib" path="lib/SuperCSV-1.52.jar"/>
 	<classpathentry kind="lib" path="lib/spiffy-all-0.05.jar"/>
 	<classpathentry kind="lib" path="lib/commons-io-1.4.jar"/>
-	<classpathentry kind="lib" path="lib/commons-dbutils-1.2.jar"/>
 	<classpathentry kind="lib" path="lib/commons-lang-2.4.jar"/>
+	<classpathentry kind="lib" path="lib/commons-dbutils-1.3.jar"/>
+	<classpathentry combineaccessrules="false" kind="src" path="/jsword"/>
 	<classpathentry kind="output" path="bin"/>
 </classpath>

Deleted: trunk/StepDataLoader/lib/commons-dbutils-1.2.jar
===================================================================
(Binary files differ)

Added: trunk/StepDataLoader/lib/commons-dbutils-1.3.jar
===================================================================
(Binary files differ)


Property changes on: trunk/StepDataLoader/lib/commons-dbutils-1.3.jar
___________________________________________________________________
Added: svn:mime-type
   + application/octet-stream

Modified: trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/2.scripture_referencing.sql
===================================================================
--- trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/2.scripture_referencing.sql	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/2.scripture_referencing.sql	2009-11-24 22:49:22 UTC (rev 25)
@@ -1,25 +1,27 @@
 set schema step;
 
+
+call dropIfExists('scripture_reference');
+
+
+-- should probably add index on start_verse_id and end_verse_id
+
 /** need to define standards and whether we want to include seperate creates in each file
  * or one file per area
  */
-call dropIfExists('scripture_reference_map');
-create table scripture_reference_map (
-	scripture_reference_id		int NOT NULL,
+create table scripture_reference (
+	scripture_reference_id		int NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
 	target_id  					int NOT NULL, -- could be for eg. a timeline event, or an article, etc. no referential integrity here
-    target_type					int  NOT NULL -- description of the event, defined in enum in java code...
+    target_type					int  NOT NULL, -- description of the event, defined in enum in java code...
+	start_verse_id				int NOT NULL,	
+	end_verse_id				int	NOT NULL -- this may be a single verse so can be null here 
 );
 
-/**
- * This tables defines the reference. An event may have several references
- * For eg. Exodus 3:1-5,9-10 would be represented twice here...
- */
-call dropIfExists('scripture_reference');
-create table scripture_reference (
-	scripture_reference_id		int PRIMARY KEY,
-	book_id						int NOT NULL,
-	start_verse_number			int NOT NULL,
-	end_verse_number			int				-- this may be a single verse so can be null here 
-												-- or should we put the start verse and have a neat non-nullable field? 
-);
+create index target_id_target_type_id_ind 
+on scripture_reference(target_id, target_type);
 
+create index start_verse_id_ind
+on scripture_reference(start_verse_id);
+
+create index end_verse_id_ind
+on scripture_reference(end_verse_id);

Modified: trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/3.timeline.sql
===================================================================
--- trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/3.timeline.sql	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/3.timeline.sql	2009-11-24 22:49:22 UTC (rev 25)
@@ -1,35 +1,13 @@
 set schema step;
 
--- TODO: add foreign key constraints...
--- timeline_id and importance_id should be tied to the the timeline table
-
-/**
- * the main event table that holds all the events of the timeline widget
- *  
- */
+call dropIfExists('alternative_date');
 call dropIfExists('event');
-create table event (
-	event_id 					int PRIMARY KEY generated by default as IDENTITY,
-	event_text_id 				varchar(20) 	NOT NULL,	
-	name 						varchar(100) 	NOT NULL,	
-	from_date 					bigint 			,				
-	to_date 					bigint,			-- nullable, may a point in time.
-	from_precision 				char(1), 		-- D/M/Y:: D=> take the whole date, M=>only month and year, Y=>only year is relevant
-	to_precision				char(1),
-	timeline_id 				int,	
-	importance_id 				int,
-	certainty 					varchar(25),	
-	flags 						varchar(50),
-	event_type_id				int,
-	description 				varchar(512),	
-	source 						varchar(512),
-	notes 						varchar(512)
-);
+call dropIfExists('timeline');
+call dropIfExists('event_type');
 
 /**
  * Timeline table indicating different types of timelines.
  */
-call dropIfExists('timeline');
 create table timeline (
 	timeline_id 				int PRIMARY KEY,
 	timeline_description		varchar(50),			-- TODO: this should probably be made an ID to timeline_description
@@ -41,29 +19,47 @@
 /**
  * Timeline table indicating different types of timelines.
  */
-call dropIfExists('event_type');
 create table event_type (
 	event_type_id 			int PRIMARY KEY,
 	event_description		varchar(50),			
 	event_code				varchar(50)
 );
 
-
-/** events may have alternative dates associated to them...
- * 
+/**
+ * the main event table that holds all the events of the timeline widget
+ *  
  */
-call dropIfExists('alternative_event_date');
-create table alternative_event_date (
-	event_id					int,
-	alternative_date_id			int
+create table event (
+	event_id 					int PRIMARY KEY generated by default as IDENTITY,
+	event_text_id 				varchar(20) 	NOT NULL,	
+	name 						varchar(100) 	NOT NULL,	
+	from_date 					bigint 			,				
+	to_date 					bigint,			-- nullable, may a point in time.
+	from_precision 				char(1), 		-- D/M/Y:: D=> take the whole date, M=>only month and year, Y=>only year is relevant
+	to_precision				char(1),
+	timeline_id 				int CONSTRAINT timeline_id_fk REFERENCES timeline (timeline_id),	
+	importance_id 				int CONSTRAINT importance_id_fk REFERENCES timeline (timeline_id),
+	certainty 					varchar(25),	
+	flags 						varchar(50),
+	event_type_id				int CONSTRAINT event_type_id_fk REFERENCES event_type(event_type_id),
+	description 				varchar(512),	
+	source 						varchar(512),
+	notes 						varchar(512)
 );
 
-call dropIfExists('alternative_date');
 create table alternative_date (
-    alternative_date_id int PRIMARY KEY generated by default as identity ,
-	from_date 					date 	NOT NULL,
-	to_date 					date,
-	from_date_precision 		date,
-	to_date_precision			date
+	alternative_date_id			int PRIMARY KEY GENERATED BY DEFAULT as IDENTITY,
+    event_id 					int CONSTRAINT event_id_fk REFERENCES event (event_id),
+	from_date 					bigint,
+	to_date 					bigint,
+	from_date_precision 		char(1),
+	to_date_precision			char(1)
 );
 
+
+-- finally create indexes:
+-- do some performance tests - it may be that full table scans might be better.
+-- TODO: these are just guesses at the moment!
+create index timeline_id_ind on event(timeline_id);
+create index from_date_from_precision_ind on event(from_date, from_precision);
+create index to_date_to_precision_ind on event(from_date, from_precision);

Added: trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/options for scripture.txt
===================================================================
--- trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/options for scripture.txt	                        (rev 0)
+++ trunk/StepDataLoader/sql/com/tyndalehouse/step/dataloader/sql/create/options for scripture.txt	2009-11-24 22:49:22 UTC (rev 25)
@@ -0,0 +1,79 @@
+set schema step;
+
+call dropIfExists('scripture_reference_map');
+call dropIfExists('scripture_reference');
+
+--/** This table contains all the verse numbers in a passage...
+-- * If a passage contains 10 verses, there will 10 rows in the database representing
+-- * this.
+-- * If a passage contains 100 verses (fairly unlikely) then it will contain
+-- * 100 rows in the database...
+-- * TODO: write performance tests for querying...
+-- */
+--create table scripture_reference (
+--	target_id						int,
+--	target_type						int,
+--	verse_id						int
+--
+--)
+--
+---- finally create indexes
+--create unique index target_type_target_id_verse_id_uind 
+--on scripture_reference (target_id, target_type, verse_id)
+--
+--
+
+/*
+ * we have several options here, 1st to basically store each verse references as a single
+ * row and then when doing a query we can either
+ *    A- lookup 
+ * 	            select * from scripture where verse_id in (a,b,c,d,e,f,g,h)
+ *    B- given we are probably only looking at one passage at a time, we can lookup as follows:
+ *              select * from scripture where verse_id between a and h
+ * 
+ * Second option is store ranges, and do more complicated logic around the beginning of verses
+ * and end of verses.
+ *             select * from scripture where a between (verse_start_id and verse_end_id)
+ *                                       or b between (verse_start_id and verse_end_id)
+  
+ *   but of course, it is not quite so simple as this, since we d be wanting to key by chapter too
+ *   so in the end we'd be looking at verse/chapter overlaps.
+ * 
+ * Third option is to store with verse_numbers and verse ranges...
+ *            so as above but without the book/chapter mapping which makes it much easier, or does it?
+ * we'd probaly have to end up doing the same thing several times, if the passage that is looked at
+ * is non contiguous... 
+ *
+ * 
+ */
+
+/**
+ * This tables defines the reference. An event may have several references
+ * For eg. Exodus 3:1-5,9-10 would be represented twice here...
+ TODO: check the performance of this
+ */
+create table scripture_reference (
+	scripture_reference_id		int PRIMARY KEY,
+	book_id						int NOT NULL,
+	start_chapter				int NOT NULL,		-- for philemon and short books, we can store this as -1? 
+	end_chapter					int NOT NULL,       -- so we can benefit from index or make nullable.
+	start_verse					int NOT NULL,
+	end_verse					int				-- this may be a single verse so can be null here 
+												-- or should we put the start verse and have a neat non-nullable field? 
+);
+
+/** need to define standards and whether we want to include seperate creates in each file
+ * or one file per area
+ */
+create table scripture_reference_map (
+	scripture_reference_id		int NOT NULL 
+								CONSTRAINT scripture_reference_id_fk REFERENCES scripture_reference (scripture_reference_id),
+	target_id  					int NOT NULL, -- could be for eg. a timeline event, or an article, etc. no referential integrity here
+    target_type					int  NOT NULL -- description of the event, defined in enum in java code...
+);
+
+
+---- finally create indexes
+--create index target_type_target_id_scripture_reference_id_ind 
+--on scripture_reference_map (target_id, target_type, scripture_reference_id);
+

Modified: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/Dataloader.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/Dataloader.java	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/Dataloader.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -2,37 +2,115 @@
 
 import java.io.IOException;
 import java.sql.SQLException;
-import java.text.ParseException;
+import java.util.Calendar;
 
 import org.apache.commons.dbutils.DbUtils;
+import org.crosswire.jsword.passage.NoSuchKeyException;
+import org.crosswire.jsword.passage.Passage;
+import org.crosswire.jsword.passage.PassageKeyFactory;
+import org.crosswire.jsword.passage.RestrictionType;
+import org.crosswire.jsword.passage.RocketPassage;
+import org.crosswire.jsword.passage.VerseRange;
 
+import com.tyndalehouse.step.dataloader.common.DateParsingException;
 import com.tyndalehouse.step.dataloader.loaders.SchemaLoader;
 import com.tyndalehouse.step.dataloader.loaders.TimelineLoader;
 
 public class Dataloader {
-	public static void main(String args[]) throws SQLException, ClassNotFoundException, IOException, ParseException {
+	
+	//check whether the .40 at the end means verse or chapter
+	//String reference = "2Sam.5.1-5; 1Chr.11.1-3; 1Chr.11.10-12.40";
+	// 2nd example: Matt.4.18-24;Matt.8.2-4;Matt.8.14-17;Matt.9.1-17;Mark.1.16-2.22;Luke.4.31-5.39
+	//could do a regular expression maybe? but would have to name the groups,
+	//and how do names work if they are repeated etc...
+	//performance comparison would be necessary.
+	
+	//TODO: assess performance, and perhaps need to change data tier
+	//to reflect this. 
+	//choice is to input each verse number according to what JSword
+	//returns to us...
+	private static void parseReference(final String reference) throws NoSuchKeyException {
+		//TODO: assess performance of this function...
+		
+		
+		
+		
+		
+		
+		//		RocketPassage p1 = new RocketPassage();
+//		VerseRange r1 = p1.getRangeAt(0, RestrictionType.NONE);
+//		
+		
+		
+		String[] multiRef = reference.replace(" ", "").split(";");
+		
+		
+//		
+//		String[] ss = p.split(reference);
+//		for(String s : ss) {
+//			System.out.println("s:" + s);
+//		}
+//		
+//		
+		//[A-Za-z]+\.[0-9]+(\.[0-9])?-([0-9]+(\.[0-9])?
+		
+//		
+//		//each multiRef item contains one scripture reference...
+//		for(String r : multiRef) {
+//			//the first dot will be the end of the book
+//			int indexOfFirstDot = r.indexOf('.');
+//			int indexOfDash = r.indexOf('-');
+//			String bookName = r.substring(0, indexOfFirstDot);
+//			
+//			//2ndly we are concerned with the -, since as far as I can see there should
+//			//only be 1 -
+//			//TODO: check against spec!
+//			String refStart = r.substring(indexOfFirstDot + 1, indexOfDash);
+//			String refEnd =   r.substring(indexOfDash + 1);
+//			
+//			//now we have refStart and refEnd in the following format:
+//			// 12(.34)?
+//			//if we have a dot, we assume we have chapter and verse,
+//			//if not, we assume we have 
+//			
+//			
+//			System.out.println("b: " + bookName);
+//			System.out.println("s: " + refStart);
+//			System.out.println("e: " + refEnd);
+//			
+//		}
+		
+	}
+	
+	public static void main(String args[]) throws SQLException, ClassNotFoundException, IOException, DateParsingException, NoSuchKeyException {
 		if(System.getProperty("driver") != null) {
 			DbUtils.loadDriver(System.getProperty("org.apache.derby.jdbc.ClientDriver"));
 		} else {
 			DbUtils.loadDriver("org.apache.derby.jdbc.EmbeddedDriver");	
 		}
 		
+		String reference = "2Sam.5.1-5; 1Chr.11.1-3; 1Chr.11.10-12.40";
+//		parseReference(reference);
 		
 		
+		
+	//	if(true) return;
 	
-//		long l = -156805891185278L;
-//		Calendar c = Calendar.getInstance();
-//		c.setTimeInMillis(l);
-//		
-//		System.out.println(c.get(Calendar.ERA));
-//		System.out.println(c.get(Calendar.YEAR));
-//		System.out.println(c.get(Calendar.MONTH));
-//		System.out.println(c.get(Calendar.DAY_OF_MONTH));
-//		System.out.println(c.get(Calendar.HOUR_OF_DAY));
-//		
+		SchemaLoader sl = new SchemaLoader();
+		TimelineLoader tl = new TimelineLoader();
 		
-		new SchemaLoader().recreateAll();
-		TimelineLoader tl = new TimelineLoader();
+		long startTime= Calendar.getInstance().getTimeInMillis();
+
+		sl.recreateAll();
+		long currentTime = Calendar.getInstance().getTimeInMillis();
+		System.out.println("Took " + (currentTime-startTime ) + "ms to create schema");
+		startTime = currentTime;
 		tl.loadData("data/timeline");
+		
+		currentTime = Calendar.getInstance().getTimeInMillis();
+		System.out.println("Took " + (currentTime-startTime ) + "ms to load data");
+			
 	}
+
+	
 }

Modified: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/PartialDate.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/PartialDate.java	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/PartialDate.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -6,6 +6,8 @@
 
 import org.apache.commons.lang.StringUtils;
 
+import com.tyndalehouse.step.dataloader.common.DateParsingException;
+
 /**
  * This class is the way dates are represented in the databased
  * and they should be parsed back into this object on their way out!
@@ -42,7 +44,6 @@
 		this.precision = precision;
 	}
 	
-	//TODO: change the ParseException to something slightly better.
 	/**
 	 * Date is specified in yy-mm-dd or yyyy-mm-dd and gets parsed in to a date.
 	 * the mm and dd are optional which is what determines the precision of the date.
@@ -51,7 +52,7 @@
 	 * @return a PartialDate
 	 * @throws ParseException an error during the parsing of the date
 	 */
-	public static PartialDate parseDate(final String date, char delimiter) throws ParseException {
+	public static PartialDate parseDate(final String date, char delimiter) throws DateParsingException {
 		String[] parts;
 		Calendar c = Calendar.getInstance();
 		PrecisionType p;
@@ -68,7 +69,7 @@
 			
 			//check we have more than one character left after substring:
 			if(date.length() < 2) {
-				throw new ParseException("The date " + date + " was not long enough.", 0);
+				throw new DateParsingException("The date " + date + " was not long enough.");
 			}
 		
 			//split the remainder of the date into parts
@@ -80,7 +81,7 @@
 		try {
 			//length of field determines how much of the date has been specified
 			switch(parts.length) {
-				case 0: throw new ParseException("The date " + date + " could not be parsed.", 0);
+				case 0: throw new DateParsingException("The date " + date + " could not be parsed.");
 				case 1:
 					//only the year is specified, so use 1st of Jan Year
 					c.set(Integer.parseInt(parts[0]), 1, 1);
@@ -97,11 +98,11 @@
 					c.set(Integer.parseInt(parts[0]), Integer.parseInt(parts[1]), Integer.parseInt(parts[2]));
 					p = PrecisionType.DAY;
 					break;
-				default: throw new ParseException("Too many parts to the date: ", 0);
+				default: throw new DateParsingException("Too many parts to the date: " + date);
 					
 			}
 		} catch(NumberFormatException nfe) {
-			throw new ParseException("Could not parse date into year, month or day.", 0);
+			throw new DateParsingException("Could not parse date into year, month or day.");
 		}
 
 		c.set(Calendar.HOUR_OF_DAY, 0);

Added: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TargetTypeEnum.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TargetTypeEnum.java	                        (rev 0)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TargetTypeEnum.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -0,0 +1,17 @@
+package com.tyndalehouse.step.dataloader.beans;
+
+public enum TargetTypeEnum {
+	TIMELINE_EVENT(1);
+	
+	private final int id;
+
+	TargetTypeEnum(int id) {
+		this.id = id;
+		
+	}
+	
+	public int getId() {
+		return id;
+	}
+	
+}

Deleted: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -1,54 +0,0 @@
-package com.tyndalehouse.step.dataloader.beans;
-
-/**
- * Timeline bean represents the different timelines on which events can be found...
- * @author CJBurrell
- *
- */
-public class TimelineBean extends DbBean {
-	private final int timelineId;
-	private final String timelineDescription;
-	private final String timelineCode;
-	
-	public TimelineBean(int timelineId, String timelineDescription, String timelineCode) {
-		this.timelineId = timelineId;
-		this.timelineDescription = timelineDescription;
-		this.timelineCode = timelineCode;
-	}
-	
-	/**
-	 * @return the timelineId
-	 */
-	public int getTimelineId() {
-		return timelineId;
-	}
-
-	/**
-	 * @return the timelineDescription
-	 */
-	public String getTimelineDescription() {
-		return timelineDescription;
-	}
-
-	/**
-	 * @return the timelineCode
-	 */
-	public String getTimelineCode() {
-		return timelineCode;
-	}
-
-	public String getInsertStatement() {
-		StringBuffer statement = new StringBuffer();
-		statement.append("insert into step.timeline(timeline_id, timeline_code, timeline_description) values(")
-			.append(getTimelineId())
-			.append(", ")
-			.append(getDbString(getTimelineCode()))			
-			.append(", ")
-			.append(getDbString(getTimelineDescription()))
-			.append(");");
-		
-		return statement.toString();
-	}
-}
-
-

Added: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java	                        (rev 0)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -0,0 +1,54 @@
+package com.tyndalehouse.step.dataloader.beans;
+
+/**
+ * Timeline bean represents the different timelines on which events can be found...
+ * @author CJBurrell
+ *
+ */
+public class TimelineBean extends DbBean {
+	private final int timelineId;
+	private final String timelineDescription;
+	private final String timelineCode;
+	
+	public TimelineBean(int timelineId, String timelineDescription, String timelineCode) {
+		this.timelineId = timelineId;
+		this.timelineDescription = timelineDescription;
+		this.timelineCode = timelineCode;
+	}
+	
+	/**
+	 * @return the timelineId
+	 */
+	public int getTimelineId() {
+		return timelineId;
+	}
+
+	/**
+	 * @return the timelineDescription
+	 */
+	public String getTimelineDescription() {
+		return timelineDescription;
+	}
+
+	/**
+	 * @return the timelineCode
+	 */
+	public String getTimelineCode() {
+		return timelineCode;
+	}
+
+	public String getInsertStatement() {
+		StringBuffer statement = new StringBuffer();
+		statement.append("insert into step.timeline(timeline_id, timeline_code, timeline_description) values(")
+			.append(getTimelineId())
+			.append(", ")
+			.append(getDbString(getTimelineCode()))			
+			.append(", ")
+			.append(getDbString(getTimelineDescription()))
+			.append(");");
+		
+		return statement.toString();
+	}
+}
+
+


Property changes on: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineBean.java
___________________________________________________________________
Added: svn:mergeinfo
   + 

Modified: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineEventBean.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineEventBean.java	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/beans/TimelineEventBean.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -19,66 +19,27 @@
 	private String source;
 	private String notes;
 
+	private static final String alternativeDateInsertStatement = "insert into step.alternative_date" +
+			"(event_id, from_date, to_date, from_date_precision, to_date_precision) values" +
+			"(?,?,?,?,?)";
+	
 	private static final String eventInsert = "insert into step.event(" +
-		"event_text_id, name, from_date, to_date, from_precision, to_precision, timeline_id, importance_id, certainty, " +
+		"event_id, event_text_id, name, from_date, to_date, from_precision, to_precision, timeline_id, importance_id, certainty, " +
 		"flags, event_type_id, description, source, notes) values(" +
-		"?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+		"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+	
+	private static final String scriptureReferenceInsertStatement = "insert into scripture_reference(" +
+			"target_id, target_type, start_verse_id, end_verse_id) values (?, ?, ?, ?)";
+	
+	/*create table scripture_reference (
+	target_id						int,
+	target_type						int,
+	verse_id						int
 
+);*/
 	
 	public static String getInsertStatement() {
 		return eventInsert;
-		
-		
-//			HashMap<String, TimelineBean> timelines, 
-//			HashMap<String, EventTypeBean> eventTypes) throws ParseException {
-//		StringBuffer event = new StringBuffer(eventInsert);
-//		
-//		PartialDate from = PartialDate.parseDate(getFrom(), '-');
-//		PartialDate to = PartialDate.parseDate(getTo(), '-');
-//
-//		//this will be interesting: TODO: add alternative dates
-//		PartialDate from2 = PartialDate.parseDate(getFrom2(), '-');
-//		PartialDate to2 = PartialDate.parseDate(getTo2(), '-');
-//		
-//		//TODO: refactor so that this is way less verbose!
-//		appendString(event, getID());
-//		appendComma(event);
-//		appendString(event, getName());
-//		appendComma(event);
-//		
-//		appendString(event, from.getDate());
-//		appendComma(event);
-//		appendString(event, to.getDate());
-//		appendComma(event);
-//		appendString(event, from.getPrecision().getShortCode());
-//		appendComma(event);
-//		appendString(event, to.getPrecision().getShortCode());
-//		appendComma(event);
-//		
-//		//importance_id refers to a table that may not be in the timeline table, but let's assume
-//		//for now, and refactor later by setting a flag...
-//		event.append(timelines.get(getTimeline()).getTimelineId());
-//		appendComma(event);
-//		event.append(timelines.get(getImportance()).getTimelineId());
-//		appendComma(event);
-//
-//		appendString(event, getCertainty());
-//		appendComma(event);
-//		appendString(event, getFlags());
-//		appendComma(event);
-//		
-//		event.append(eventTypes.get(getType()).getEventTypeId());
-//		appendComma(event);
-//		
-//		appendString(event, getDescription());
-//		appendComma(event);
-//		appendString(event, getSource());
-//		appendComma(event);
-//		appendString(event, getNotes());
-//		
-//		event.append(");");
-//		
-//		return event.toString();
 	}
 
 
@@ -265,5 +226,21 @@
 	public void setSource(String source) {
 		this.source = source;
 	}
+
+
+
+
+
+	public static String getAlternativeDateStatement() {
+		return alternativeDateInsertStatement;
+	}
+
+
+
+
+	//TODO: move this out to a seperate logical unit
+	public static String getScriptureReferenceStatement() {
+		return scriptureReferenceInsertStatement;
+	}
 	
 }

Added: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/common/DateParsingException.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/common/DateParsingException.java	                        (rev 0)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/common/DateParsingException.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -0,0 +1,23 @@
+package com.tyndalehouse.step.dataloader.common;
+
+public class DateParsingException extends Exception {
+
+	/**
+	 * generated serial version UID
+	 */
+	private static final long serialVersionUID = -5685008606295145634L;
+	private final String message;
+
+	public DateParsingException(final String message) {
+		this.message = message;
+	}
+
+	/**
+	 * @return the message
+	 */
+	public String getMessage() {
+		return message;
+	}
+	
+	
+}

Modified: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/loaders/TimelineLoader.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/loaders/TimelineLoader.java	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/loaders/TimelineLoader.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -13,206 +13,271 @@
 import java.util.HashMap;
 import java.util.List;
 
+import org.crosswire.jsword.passage.KeyFactory;
+import org.crosswire.jsword.passage.NoSuchKeyException;
+import org.crosswire.jsword.passage.NoSuchVerseException;
+import org.crosswire.jsword.passage.PassageKeyFactory;
+import org.crosswire.jsword.passage.RestrictionType;
+import org.crosswire.jsword.passage.RocketPassage;
+import org.crosswire.jsword.passage.Verse;
+import org.crosswire.jsword.passage.VerseRange;
 import org.supercsv.io.CsvBeanReader;
 import org.supercsv.io.ICsvBeanReader;
 import org.supercsv.prefs.CsvPreference;
 
 import com.tyndalehouse.step.dataloader.beans.EventTypeBean;
 import com.tyndalehouse.step.dataloader.beans.PartialDate;
+import com.tyndalehouse.step.dataloader.beans.TargetTypeEnum;
 import com.tyndalehouse.step.dataloader.beans.TimelineBean;
 import com.tyndalehouse.step.dataloader.beans.TimelineEventBean;
+import com.tyndalehouse.step.dataloader.common.DateParsingException;
 
-
 public class TimelineLoader extends AbstractLoader {
 	private HashMap<String, TimelineBean> timelines;
 	private HashMap<String, EventTypeBean> eventTypes;
-	
+
 	public TimelineLoader() throws SQLException {
 		super();
 	}
-	
+
 	/**
 	 * Reads the timeline directory into beans...
-	 * @param directoryPath directory path
-	 * @throws FileNotFoundException the directory specified was not found
-	 * @throws IOException an error occured while reading the data
+	 * 
+	 * @param directoryPath
+	 *            directory path
+	 * @throws FileNotFoundException
+	 *             the directory specified was not found
+	 * @throws IOException
+	 *             an error occured while reading the data
 	 */
-	private List<TimelineEventBean> readDataFromTimelineDirectory(String directoryPath)
-			throws FileNotFoundException, IOException {
+	private List<TimelineEventBean> readDataFromTimelineDirectory(String directoryPath) throws FileNotFoundException, IOException {
 		File directory = new File(directoryPath);
-		
-		if(!directory.isDirectory()) {
+
+		if (!directory.isDirectory()) {
 			error("The directory specified is not a valid directory");
 		}
-		
-		//then get list of files in directory
+
+		// then get list of files in directory
 		File[] listOfFiles = directory.listFiles(new FileFilter() {
 
 			@Override
-			public boolean accept(File file) { 
-				if(!file.getName().endsWith(".csv")) {
+			public boolean accept(File file) {
+				if (!file.getName().endsWith(".csv")) {
 					return false;
 				}
 				return true;
 			}
-			
 		});
+
 		List<TimelineEventBean> events = new ArrayList<TimelineEventBean>();
 		TimelineEventBean tb;
-		
-		//each file should be its own CSVObject
-		for(File f : listOfFiles) {
-			System.out.print(String.format("%s", f.getName()));
+		int count = 0;
+
+		// each file should be its own CSVObject
+		for (File f : listOfFiles) {
+			// TODO: use log4j
+			// System.out.print(String.format("%s", f.getName()));
 			ICsvBeanReader inFile = new CsvBeanReader(new FileReader(f), CsvPreference.EXCEL_PREFERENCE);
 			final String[] header = inFile.getCSVHeader(true);
-			int count = 0;
-			
-			//add processors to aid conversion?
-			while( (tb = inFile.read(TimelineEventBean.class, header)) != null) {
-		        events.add(tb);
-		        count++;
-		      }
-		
-			System.out.println(String.format("\t\t\t %d events - READ", count));
+			// add processors to aid conversion?
+			while ((tb = inFile.read(TimelineEventBean.class, header)) != null) {
+				events.add(tb);
+				count++;
+			}
 		}
-		
+		System.out.println(String.format("Read %d events", count));
+
 		return events;
 	}
 
 	/**
-	 * Adds the timeline to the hashmap passed in, assuming it is not already in there
-	 * This is just a helper function really
+	 * Adds the timeline to the hashmap passed in, assuming it is not already in
+	 * there This is just a helper function really
+	 * 
 	 * @param timelineCode
 	 */
 	public void addTimelineIfNotExists(final String timelineCode) {
-		if(!timelines.containsKey(timelineCode)) {
-			//debatable as to whether or not we want to set the id here...
+		if (!timelines.containsKey(timelineCode)) {
+			// debatable as to whether or not we want to set the id here...
 			TimelineBean tb = new TimelineBean(timelines.size() + 1, timelineCode, timelineCode);
 			timelines.put(timelineCode, tb);
-		} 
+		}
 	}
 
-	
 	private void addEventTypeIfNotExists(final String eventType) {
-		if(!eventTypes.containsKey(eventType)) {
-			//debatable as to whether or not we want to set the id here...
+		if (!eventTypes.containsKey(eventType)) {
+			// debatable as to whether or not we want to set the id here...
 			EventTypeBean tb = new EventTypeBean(eventTypes.size() + 1, eventType, eventType);
 			eventTypes.put(eventType, tb);
-		} 
+		}
 	}
-	
+
 	/**
 	 * main entry function to read in the timeline data into the database
-	 * @param directoryPath path to the timeline directory
-	 * @throws SQLException sql exception occuring when trying to load the data 
-	 * @throws IOException unable to read the timeline from the disk
-	 * @throws ParseException unable to parse dates contained in timeline source files
+	 * 
+	 * @param directoryPath
+	 *            path to the timeline directory
+	 * @throws SQLException
+	 *             sql exception occuring when trying to load the data
+	 * @throws IOException
+	 *             unable to read the timeline from the disk
+	 * @throws NoSuchKeyException
+	 * @throws ParseException
+	 *             unable to parse dates contained in timeline source files
 	 */
-	public void loadData(String directoryPath) throws SQLException, IOException, ParseException {
+	public void loadData(String directoryPath) throws SQLException, IOException, DateParsingException, NoSuchKeyException {
 		List<TimelineEventBean> events = readDataFromTimelineDirectory(directoryPath);
 
-		//first parse - get all the different timeline and populate timeline table
-		//also add what's in the importance field in case it's not there already
+		// first parse - get all the different timeline and populate timeline
+		// table
+		// also add what's in the importance field in case it's not there
+		// already
 		populateTimelineTable(events);
 		populateEventTypeTable(events);
 		populateEventsTable(events);
 	}
 
 	private void setDate(PreparedStatement ps, int index, PartialDate d) throws SQLException {
-		if(d == null || d.getDate() == null) {
+		if (d == null || d.getDate() == null) {
 			ps.setNull(index, Types.BIGINT);
 		} else {
 			ps.setLong(index, d.getDate().getTimeInMillis());
 		}
 	}
-	
+
 	private void setString(PreparedStatement ps, int index, String value) throws SQLException {
-		if(value == null || value.length() == 0) {
+		if (value == null || value.length() == 0) {
 			ps.setNull(index, Types.VARCHAR);
 		} else {
 			ps.setString(index, value);
 		}
 	}
-	
-	//TODO: rewrite to use prepared statements instead!
-	private void populateEventsTable(List<TimelineEventBean> events) throws SQLException, ParseException {
-		PreparedStatement ps = c.prepareStatement(TimelineEventBean.getInsertStatement());
-		
-		try {				
-		for(TimelineEventBean event : events) {
-				System.out.println(event.getName() + " " + event.getID() + " " + event.getTimeline());
-			
+
+	// TODO: all foreign key constraint, indexes and checks
+	// TODO: Rewrite to get rid of all the parameter indexes. It should be
+	// feasible to give a bunch
+	// of things to a function, and it add it to the right function.
+	private void populateEventsTable(List<TimelineEventBean> events) throws SQLException, DateParsingException, NoSuchKeyException {
+		PreparedStatement timelineInsert = c.prepareStatement(TimelineEventBean.getInsertStatement());
+		PreparedStatement alternativeDates = c.prepareStatement(TimelineEventBean.getAlternativeDateStatement());
+		PreparedStatement scriptureReferences = c.prepareStatement(TimelineEventBean.getScriptureReferenceStatement());
+		RocketPassage rp = null;
+		int currentId = 1;
+
+		// set up the key factory once
+		KeyFactory keyFactory = PassageKeyFactory.instance();
+
+		try {
+			for (TimelineEventBean event : events) {
+				// System.out.println(event.getName() + " " + event.getID() +
+				// " " + event.getTimeline());
+				int timelineParameterIndex = 1;
+				int alternativeDateParameterIndex = 1;
+
 				PartialDate from = PartialDate.parseDate(event.getFrom(), '-');
 				PartialDate to = PartialDate.parseDate(event.getTo(), '-');
-		
-				//this will be interesting: TODO: add alternative dates
+
+				timelineInsert.setInt(timelineParameterIndex++, currentId);
+				setString(timelineInsert, timelineParameterIndex++, event.getID()); // event_text_id
+				setString(timelineInsert, timelineParameterIndex++, event.getName()); // name
+				setDate(timelineInsert, timelineParameterIndex++, from); // from_date
+				setDate(timelineInsert, timelineParameterIndex++, to); // to_date
+				setString(timelineInsert, timelineParameterIndex++, "" + from.getPrecision().getShortCode()); // from_precision
+				setString(timelineInsert, timelineParameterIndex++, "" + to.getPrecision().getShortCode()); // to_precision
+				timelineInsert.setInt(timelineParameterIndex++, timelines.get(event.getTimeline()).getTimelineId()); // timeline_id
+				timelineInsert.setInt(timelineParameterIndex++, timelines.get(event.getImportance()).getTimelineId()); // importance_id
+				setString(timelineInsert, timelineParameterIndex++, event.getCertainty()); // certainty
+				setString(timelineInsert, timelineParameterIndex++, event.getFlags()); // flags
+				timelineInsert.setInt(timelineParameterIndex++, eventTypes.get(event.getType()).getEventTypeId()); // event_type_id
+				setString(timelineInsert, timelineParameterIndex++, event.getDescription()); // description
+				setString(timelineInsert, timelineParameterIndex++, event.getSource()); // source
+				setString(timelineInsert, timelineParameterIndex++, event.getNotes()); // notes
+
+				// Now do the alternative dates bit
 				PartialDate from2 = PartialDate.parseDate(event.getFrom2(), '-');
 				PartialDate to2 = PartialDate.parseDate(event.getTo2(), '-');
+				timelineInsert.addBatch();
 
-				setString(ps, 1, event.getID()); //	event_text_id 
-				setString(ps, 2, event.getName()); //	name 
-				setDate(ps, 3, from); //	from_date 
-				setDate(ps, 4, to); //	to_date
-				setString(ps, 5, ""+from.getPrecision().getShortCode()); //	from_precision 
-				setString(ps, 6, ""+to.getPrecision().getShortCode()); //	to_precision
-				ps.setInt(7, timelines.get(event.getTimeline()).getTimelineId()); //	timeline_id 
-				ps.setInt(8, timelines.get(event.getImportance()).getTimelineId()); //	importance_id 
-				setString(ps, 9, event.getCertainty()); //	certainty
-				setString(ps, 10, event.getFlags()); //	flags
-				ps.setInt(11, eventTypes.get(event.getType()).getEventTypeId()); //	event_type_id 
-				setString(ps, 12, event.getDescription()); //	description
-				setString(ps, 13, event.getSource()); //	source 
-				setString(ps, 14, event.getNotes()); //	notes
-					
-				ps.addBatch();
-				
-				try {
-					ps.executeBatch();
-				} catch(SQLException ex) {
-					System.err.println(ex.getMessage());
-					ex.printStackTrace();
+				if (from2.getDate() == null && to2.getDate() == null) {
+					// set event_id from previous timeline insert
+					alternativeDates.setInt(alternativeDateParameterIndex++, currentId);
+					setDate(alternativeDates, alternativeDateParameterIndex++, from2); // from_date
+					setDate(alternativeDates, alternativeDateParameterIndex++, to2); // to_date
+					setString(alternativeDates, alternativeDateParameterIndex++, "" + from2.getPrecision().getShortCode()); // from_precision
+					setString(alternativeDates, alternativeDateParameterIndex++, "" + to2.getPrecision().getShortCode()); // to_precision
+					alternativeDates.addBatch();
 				}
+
+				// now do the insert into scripture bit!
+				if (event.getRefs() != null && event.getRefs().length() != 0) {
+					try {
+						rp = (RocketPassage) keyFactory.getKey(event.getRefs());
+						for (int ii = 0; ii < rp.countRanges(RestrictionType.NONE); ii++) {
+							VerseRange vr = rp.getRangeAt(ii, RestrictionType.NONE);
+							Verse start = vr.getStart();
+							Verse end = vr.getEnd();
+
+							int startVerseId = start.getOrdinal();
+							int endVerseId = end.getOrdinal();
+
+							scriptureReferences.setInt(1, currentId);
+							scriptureReferences.setInt(2, TargetTypeEnum.TIMELINE_EVENT.getId());
+							scriptureReferences.setInt(3, startVerseId);
+							scriptureReferences.setInt(4, endVerseId);
+							scriptureReferences.addBatch();
+						}
+					} catch(NoSuchVerseException nsve) {
+						//Nehemiah 6.20 might not exist for example...
+						//TODO: proper logging
+						System.err.println(event.getRefs() + " => " + nsve.getMessage());
+						}
+				}
+				currentId++;
 			}
-		
-			//int[] results = ps.executeBatch();
-			
-		} catch(SQLException ex) {
-			//TODO: get some proper logging framework in there...
-			//output warnings here...
+
+			timelineInsert.executeBatch();
+			alternativeDates.executeBatch();
+			scriptureReferences.executeBatch();
+		} catch (SQLException ex) {
+			// TODO: get some proper logging framework in there...
+			// output warnings here...
 			System.err.println(ex.getMessage());
 			ex.printStackTrace();
-			
+
 		}
 	}
 
 	/**
 	 * populates the timeline table, ie. the different timelines
-	 * @param events the list of all the events to be uploaded.
-	 * @throws SQLException error running the sql statement 
+	 * 
+	 * @param events
+	 *            the list of all the events to be uploaded.
+	 * @throws SQLException
+	 *             error running the sql statement
 	 */
 	private void populateTimelineTable(final List<TimelineEventBean> events) throws SQLException {
 		timelines = new HashMap<String, TimelineBean>();
-		
-		for(TimelineEventBean event : events) {
+
+		for (TimelineEventBean event : events) {
 			addTimelineIfNotExists(event.getTimeline());
 			addTimelineIfNotExists(event.getImportance());
 		}
-		
-		for(TimelineBean timeline : timelines.values()) {
+
+		for (TimelineBean timeline : timelines.values()) {
 			executeSQLText(timeline.getInsertStatement());
 		}
 	}
+
 	private void populateEventTypeTable(List<TimelineEventBean> events) throws SQLException {
 		eventTypes = new HashMap<String, EventTypeBean>();
-		
-		for(TimelineEventBean event : events) {
+
+		for (TimelineEventBean event : events) {
 			addEventTypeIfNotExists(event.getType());
 		}
-		
-		for(EventTypeBean eventType: eventTypes.values()) {
+
+		for (EventTypeBean eventType : eventTypes.values()) {
 			executeSQLText(eventType.getInsertStatement());
-		}		
+		}
 	}
 
 }
-

Modified: trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/utils/DerbyUtils.java
===================================================================
--- trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/utils/DerbyUtils.java	2009-11-24 22:45:54 UTC (rev 24)
+++ trunk/StepDataLoader/src/com/tyndalehouse/step/dataloader/utils/DerbyUtils.java	2009-11-24 22:49:22 UTC (rev 25)
@@ -21,7 +21,7 @@
 		//get count out:
 		int count = exRs.getInt(1);
 		if(count > 0) {
-			System.out.println("Dropping table " + tableName);
+			//System.out.println("Dropping table " + tableName);
 			PreparedStatement s = conn.prepareStatement("drop table " + tableName);	
 			s.executeUpdate();
 		}		




More information about the Tynstep-svn mailing list