[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