[Tynstep-svn] r80 - in trunk/step-web-app/src/main/java/com/tyndalehouse/step/web: client/event client/eventhandler client/presenter client/toolkit/timeline/helpers client/view client/view/widgets server/db server/db/framework server/db/timeline
ChrisBurrell at crosswire.org
ChrisBurrell at crosswire.org
Tue Feb 9 14:46:06 MST 2010
Author: ChrisBurrell
Date: 2010-02-09 14:46:06 -0700 (Tue, 09 Feb 2010)
New Revision: 80
Added:
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/TimebandListUpdateRequiredEvent.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/UserInterestInBandEvent.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/TimebandListUpdateRequiredEventHandler.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/UserInterestInBandEventHandler.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/presenter/TimebandListPresenter.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/toolkit/timeline/helpers/CurrentBandStats.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/TimebandListView.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/widgets/
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/widgets/ScriptureWord.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/MalformedStepQueryException.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/Query.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/ResultSetProcessor.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQuery.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunner.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunnerImpl.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/UnableToRunQueryException.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimeBandVisibleDateProcessor.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginDbBean.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginProcessor.java
trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineSetupDataProcessor.java
Log:
db framework
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/TimebandListUpdateRequiredEvent.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/TimebandListUpdateRequiredEvent.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/TimebandListUpdateRequiredEvent.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,42 @@
+package com.tyndalehouse.step.web.client.event;
+
+import java.util.List;
+
+import com.google.gwt.event.shared.GwtEvent;
+import com.tyndalehouse.step.web.client.eventhandler.TimebandListUpdateRequiredEventHandler;
+import com.tyndalehouse.step.web.client.toolkit.timeline.helpers.CurrentBandStats;
+
+public class TimebandListUpdateRequiredEvent extends GwtEvent<TimebandListUpdateRequiredEventHandler> {
+
+ public static Type<TimebandListUpdateRequiredEventHandler> TYPE = new Type<TimebandListUpdateRequiredEventHandler>();
+ private final List<CurrentBandStats> stats;
+
+ /**
+ * @return the stats
+ */
+ public List<CurrentBandStats> getTimelineStats() {
+ return stats;
+ }
+
+ /**
+ * The timeline is passed in, so that the timeband list can know what the new values are
+ * @param stats timeline to be passed in
+ */
+ public TimebandListUpdateRequiredEvent(List<CurrentBandStats> stats) {
+ this.stats = stats;
+
+ }
+
+ @Override
+ protected void dispatch(TimebandListUpdateRequiredEventHandler handler) {
+ handler.onTimebandListRequiringUpdate(this);
+
+ }
+
+ @Override
+ public Type<TimebandListUpdateRequiredEventHandler> getAssociatedType() {
+ // TODO Auto-generated method stub
+ return TYPE;
+ }
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/UserInterestInBandEvent.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/UserInterestInBandEvent.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/event/UserInterestInBandEvent.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,40 @@
+package com.tyndalehouse.step.web.client.event;
+
+import com.google.gwt.event.shared.GwtEvent;
+import com.tyndalehouse.step.web.client.eventhandler.UserInterestInBandEventHandler;
+
+public class UserInterestInBandEvent extends GwtEvent<UserInterestInBandEventHandler> {
+ public static Type<UserInterestInBandEventHandler> TYPE = new Type<UserInterestInBandEventHandler>();
+ private final int bandId;
+ private final Boolean isOfInterest;
+
+ /**
+ * @return the bandId
+ */
+ public int getBandId() {
+ return bandId;
+ }
+
+ public UserInterestInBandEvent(int bandId, Boolean isOfInterest) {
+ this.bandId = bandId;
+ this.isOfInterest = isOfInterest;
+ }
+
+ /**
+ * @return the isOfInterest
+ */
+ public Boolean isOfInterest() {
+ return isOfInterest;
+ }
+
+ @Override
+ protected void dispatch(UserInterestInBandEventHandler handler) {
+ handler.onUserInterestedInBand(this);
+ }
+
+ @Override
+ public Type<UserInterestInBandEventHandler> getAssociatedType() {
+ return TYPE;
+ }
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/TimebandListUpdateRequiredEventHandler.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/TimebandListUpdateRequiredEventHandler.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/TimebandListUpdateRequiredEventHandler.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,8 @@
+package com.tyndalehouse.step.web.client.eventhandler;
+
+import com.google.gwt.event.shared.EventHandler;
+import com.tyndalehouse.step.web.client.event.TimebandListUpdateRequiredEvent;
+
+public interface TimebandListUpdateRequiredEventHandler extends EventHandler {
+ public void onTimebandListRequiringUpdate(TimebandListUpdateRequiredEvent timebandListUpdateRequiredEvent);
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/UserInterestInBandEventHandler.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/UserInterestInBandEventHandler.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/eventhandler/UserInterestInBandEventHandler.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,10 @@
+package com.tyndalehouse.step.web.client.eventhandler;
+
+import com.google.gwt.event.shared.EventHandler;
+import com.tyndalehouse.step.web.client.event.UserInterestInBandEvent;
+
+public interface UserInterestInBandEventHandler extends EventHandler {
+
+ void onUserInterestedInBand(UserInterestInBandEvent userInterestInBandEvent);
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/presenter/TimebandListPresenter.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/presenter/TimebandListPresenter.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/presenter/TimebandListPresenter.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,71 @@
+package com.tyndalehouse.step.web.client.presenter;
+
+import java.util.List;
+
+import net.customware.gwt.presenter.client.EventBus;
+import net.customware.gwt.presenter.client.place.Place;
+import net.customware.gwt.presenter.client.place.PlaceRequest;
+import net.customware.gwt.presenter.client.widget.WidgetDisplay;
+import net.customware.gwt.presenter.client.widget.WidgetPresenter;
+
+import com.google.inject.Inject;
+import com.tyndalehouse.step.web.client.event.TimebandListUpdateRequiredEvent;
+import com.tyndalehouse.step.web.client.eventhandler.TimebandListUpdateRequiredEventHandler;
+import com.tyndalehouse.step.web.client.toolkit.timeline.helpers.CurrentBandStats;
+
+public class TimebandListPresenter extends WidgetPresenter<TimebandListPresenter.Display> {
+
+ @Inject
+ public TimebandListPresenter(Display display, EventBus eventBus) {
+ super(display, eventBus);
+ bind();
+ }
+
+ public interface Display extends WidgetDisplay {
+ //TODO: probably don't want to pass the whole of the timeline
+ //perhaps only the stats out of it, decoupling the counter a little?
+ public void updateList(List<CurrentBandStats> stats);
+ }
+
+ @Override
+ public Place getPlace() {
+ // TODO Auto-generated method stub
+ return null;
+ }
+
+ @Override
+ protected void onBind() {
+ eventBus.addHandler(TimebandListUpdateRequiredEvent.TYPE, new TimebandListUpdateRequiredEventHandler() {
+
+ @Override
+ public void onTimebandListRequiringUpdate(TimebandListUpdateRequiredEvent timebandListUpdateRequiredEvent) {
+ List<CurrentBandStats> stats = timebandListUpdateRequiredEvent.getTimelineStats();
+ display.updateList(stats);
+ }
+ });
+ }
+
+ @Override
+ protected void onPlaceRequest(PlaceRequest request) {
+ // TODO Auto-generated method stub
+
+ }
+
+ @Override
+ protected void onUnbind() {
+ // TODO Auto-generated method stub
+
+ }
+
+ @Override
+ public void refreshDisplay() {
+ // TODO Auto-generated method stub
+
+ }
+
+ @Override
+ public void revealDisplay() {
+ // TODO Auto-generated method stub
+
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/toolkit/timeline/helpers/CurrentBandStats.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/toolkit/timeline/helpers/CurrentBandStats.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/toolkit/timeline/helpers/CurrentBandStats.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,59 @@
+package com.tyndalehouse.step.web.client.toolkit.timeline.helpers;
+
+public class CurrentBandStats {
+ private final int bandId;
+ private final int numElements;
+ private final int numVisibleElements;
+ private final String bandDescription;
+ private final boolean isUserInterested;
+
+ /**
+ *
+ * @param bandId the id of the band
+ * @param numElements the number of events on the band
+ * @param numVisibleElements the number of visible events in the frame viewed on the scren
+ */
+ public CurrentBandStats(final int bandId, final String bandDescription,
+ int numElements, final int numVisibleElements, final boolean isUserInterested) {
+ this.bandId = bandId;
+ this.bandDescription = bandDescription;
+ this.numElements = numElements;
+ this.numVisibleElements = numVisibleElements;
+ this.isUserInterested = isUserInterested;
+ }
+
+ /**
+ * @return the isUserInterested
+ */
+ public boolean isUserInterested() {
+ return isUserInterested;
+ }
+
+ /**
+ * @return the bandDescription
+ */
+ public String getBandDescription() {
+ return bandDescription;
+ }
+
+ /**
+ * @return the bandId
+ */
+ public int getBandId() {
+ return bandId;
+ }
+
+ /**
+ * @return the numElements
+ */
+ public int getNumElements() {
+ return numElements;
+ }
+
+ /**
+ * @return the numVisibleElements
+ */
+ public int getNumVisibleElements() {
+ return numVisibleElements;
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/TimebandListView.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/TimebandListView.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/TimebandListView.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,70 @@
+package com.tyndalehouse.step.web.client.view;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import net.customware.gwt.presenter.client.EventBus;
+
+import com.google.gwt.event.logical.shared.ValueChangeEvent;
+import com.google.gwt.event.logical.shared.ValueChangeHandler;
+import com.google.gwt.user.client.ui.CheckBox;
+import com.google.gwt.user.client.ui.Composite;
+import com.google.gwt.user.client.ui.VerticalPanel;
+import com.google.gwt.user.client.ui.Widget;
+import com.google.inject.Inject;
+import com.tyndalehouse.step.web.client.event.UserInterestInBandEvent;
+import com.tyndalehouse.step.web.client.presenter.TimebandListPresenter;
+import com.tyndalehouse.step.web.client.toolkit.timeline.helpers.CurrentBandStats;
+
+public class TimebandListView extends Composite implements TimebandListPresenter.Display {
+ private Map<Integer, CheckBox> checkboxes = new HashMap<Integer, CheckBox>();
+ private VerticalPanel fp = new VerticalPanel();
+ private final EventBus eventBus;
+
+ @Inject
+ public TimebandListView(EventBus eventBus) {
+ this.eventBus = eventBus;
+ initWidget(fp);
+ }
+
+ public void updateList(final List<CurrentBandStats> stats) {
+ for(final CurrentBandStats band : stats) {
+ CheckBox cb = checkboxes.get(band.getBandId());
+ if(cb == null) {
+ cb = new CheckBox();
+ fp.add(cb);
+ cb.addValueChangeHandler(new ValueChangeHandler<Boolean>() {
+ @Override
+ public void onValueChange(ValueChangeEvent<Boolean> event) {
+ eventBus.fireEvent(new UserInterestInBandEvent(band.getBandId(), event.getValue()));
+ }
+
+ });
+ }
+
+ int numEventsInBand = band.getNumVisibleElements();
+ cb.setText(band.getBandDescription() + " (" + numEventsInBand + ")");
+ cb.setTitle("Click here to show " + numEventsInBand + " more events.");
+ cb.setValue(band.isUserInterested(), false);
+ checkboxes.put(band.getBandId(), cb);
+ }
+ }
+
+ @Override
+ public Widget asWidget() {
+ return this;
+ }
+
+ @Override
+ public void startProcessing() {
+ // TODO Auto-generated method stub
+
+ }
+
+ @Override
+ public void stopProcessing() {
+ // TODO Auto-generated method stub
+
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/widgets/ScriptureWord.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/widgets/ScriptureWord.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/client/view/widgets/ScriptureWord.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,34 @@
+package com.tyndalehouse.step.web.client.view.widgets;
+
+import net.customware.gwt.presenter.client.widget.WidgetDisplay;
+
+import com.google.gwt.user.client.ui.Button;
+import com.google.gwt.user.client.ui.Composite;
+import com.google.gwt.user.client.ui.Panel;
+import com.google.gwt.user.client.ui.SimplePanel;
+import com.google.gwt.user.client.ui.Widget;
+
+public class ScriptureWord extends Composite implements WidgetDisplay {
+ public ScriptureWord() {
+ Panel p = new SimplePanel();
+ p.add(new Button("1 "));
+ initWidget(p);
+ }
+
+ @Override
+ public Widget asWidget() {
+ return this;
+ }
+
+
+ @Override
+ public void startProcessing() {
+ // TODO Auto-generated method stub
+
+ }
+
+ @Override
+ public void stopProcessing() {
+ ;
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/MalformedStepQueryException.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/MalformedStepQueryException.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/MalformedStepQueryException.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,21 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+public class MalformedStepQueryException extends Exception {
+
+ /**
+ * serial id
+ */
+ private static final long serialVersionUID = -3607780460262284122L;
+
+ public MalformedStepQueryException(final String paramName, final String sql) {
+ super(String.format("The argument %s was not recognised in query:\n %s", paramName, sql));
+ }
+
+ public MalformedStepQueryException(final String message, final Exception ex) {
+ super(message, ex);
+ }
+
+ public MalformedStepQueryException(final String message) {
+ super(message);
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/Query.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/Query.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/Query.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,24 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+public enum Query {
+ LOOKUP_TIMELINE_ORIGIN("timeline"),
+ GET_TIMELINE_SETUP_DATA("timeline"),
+ GET_EVENTS_FOR_DATE_RANGE("timeline");
+
+
+ private final String component;
+
+
+ Query(final String component) {
+ this.component = component;
+
+ }
+
+ /**
+ * @return the component
+ */
+ public String getComponent() {
+ return component;
+ }
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/ResultSetProcessor.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/ResultSetProcessor.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/ResultSetProcessor.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,19 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.List;
+import java.util.Map;
+
+import com.tyndalehouse.step.web.server.common.ConfigNotLoadedException;
+
+/**
+ * @author CJBurrell
+ * This is used by the query runner to process result sets
+ * @param <T> the type of bean to be returned
+ */
+public interface ResultSetProcessor<T> {
+ public List<T> process(ResultSet rs) throws MalformedStepQueryException, ConfigNotLoadedException, SQLException;
+ public Map<String, Object> getParameters();
+ public Query getQuery();
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQuery.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQuery.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQuery.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,92 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class StepQuery {
+ private List<String> arguments;
+ private Map<String, Integer> paramTypes;
+ private String sql;
+
+ public StepQuery() {
+ arguments = new ArrayList<String>();
+ paramTypes = new HashMap<String, Integer>();
+ }
+
+ public void addArgument(final String paramName) {
+ arguments.add(paramName);
+ }
+
+ public void addParamTypeMapping(final String paramName, final Integer type) {
+ paramTypes.put(paramName, type);
+ }
+
+ public void validate() throws MalformedStepQueryException {
+ for(String arg : arguments) {
+ if(!paramTypes.containsKey(arg)) {
+ throw new MalformedStepQueryException(arg, sql);
+ }
+ }
+
+ //TODO: optimize by compiling prepared statement here!
+ }
+
+ /**
+ * Sets the sql for the step query to be run
+ * @param sql sql representing the query to be run
+ */
+ public void setSQLText(final String sql) {
+ this.sql = sql;
+ }
+
+ public String getSQLText() {
+ return sql;
+ }
+
+
+ /**
+ * Sets up the prepared statement arguments in the correct order.
+ * @param ps the prepared statement
+ * @param passedArguments the map of named arguments
+ * @throws SQLException
+ */
+ public void setupParameters(PreparedStatement ps, Map<String, Object> passedArguments) throws SQLException {
+ int argIndex = 1;
+ for(String argName : arguments) {
+ setArgument(ps, paramTypes.get(argName), passedArguments.get(argName), argIndex++);
+ }
+ }
+
+ /**
+ * Sets up the parameter into the prepared statement
+ * @param ps prepared statement
+ * @param argName the name of the parameter
+ * @param value the value of the parameter
+ * @param argIndex the position at which the parameter is to be placed in the prepared statement
+ * @throws SQLException an exception thrown in case something goes wrong
+ */
+ private void setArgument(final PreparedStatement ps, final int sqlType, final Object value, final int argIndex) throws SQLException {
+ //check for null first
+ if(value == null) {
+ ps.setNull(argIndex, sqlType);
+ return;
+ }
+
+ //at this stage, we do not need to worry about null variables
+ switch(sqlType) {
+ case Types.INTEGER : ps.setInt(argIndex, (Integer) value); break;
+ case Types.VARCHAR : ps.setString(argIndex, (String) value); break;
+ case Types.BIGINT : ps.setLong(argIndex, (Long) value); break;
+ case Types.SMALLINT : ps.setShort(argIndex, (Short) value); break;
+ case Types.LONGVARCHAR : ps.setString(argIndex, (String) value); break;
+ default: throw new SQLException(
+ String.format("The STEP query framework does not yet support this type/value (%d, %s)",
+ sqlType, value.toString()));
+ }
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunner.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunner.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunner.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,28 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+import java.sql.SQLException;
+import java.util.List;
+
+import com.tyndalehouse.step.web.server.common.ConfigNotLoadedException;
+
+public interface StepQueryRunner {
+
+ /**
+ * To run a query in the database
+ * @throws ConfigNotLoadedException
+ * @throws SQLException
+ */
+ public <T> List<T> run(final ResultSetProcessor<T> processor) throws UnableToRunQueryException;
+
+ /**
+ * Clears the cache of parsed queries that are currently in the query runner
+ */
+ public void clearCache();
+
+ /**
+ * Tears down the database pool and starts it up again
+ */
+ public void restartDatasourcePool();
+
+
+}
\ No newline at end of file
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunnerImpl.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunnerImpl.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/StepQueryRunnerImpl.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,286 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+import java.io.File;
+import java.io.IOException;
+import java.net.URL;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.util.HashMap;
+import java.util.List;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import javax.sql.DataSource;
+
+import org.apache.commons.dbcp.BasicDataSource;
+import org.apache.commons.io.FileUtils;
+import org.apache.commons.logging.Log;
+
+import com.google.inject.Inject;
+import com.google.inject.Singleton;
+import com.tyndalehouse.step.web.server.common.ConfigNotLoadedException;
+import com.tyndalehouse.step.web.server.common.ConfigProvider;
+
+/**
+ * This class is the crux of the database access. An enum identifies the SQL
+ * file to be loaded. Arguments get defined in the query file as define
+ * variableName as sqlType A map can be passed in that ensures all arguments are
+ * mapped to the correct location in the prepared statement
+ *
+ * @author CJBurrell
+ */
+ at Singleton
+public class StepQueryRunnerImpl implements StepQueryRunner {
+ private static final String DEFINITION_PATTERN = "define (\\w+) as (\\w+)";
+ private static final String HASH = "#";
+ private static final String QUESTION_MARK = "?";
+ private static final Object CACHE_QUERY_PROPERTY = "query.runner.cache";
+ private static HashMap<Query, StepQuery> queries = new HashMap<Query, StepQuery>();
+ private static DataSource datasource = null;
+ private final Log log;
+
+ /**
+ * prevent instantiation
+ */
+ @Inject
+ public StepQueryRunnerImpl(final Log log) {
+ this.log = log;
+ }
+
+ /*
+ * (non-Javadoc)
+ *
+ * @see
+ * com.tyndalehouse.step.web.server.db.StepQueryRunner#run(com.tyndalehouse
+ * .step.web.server.db.ResultSetProcessor)
+ */
+ public <T> List<T> run(final ResultSetProcessor<T> processor) throws UnableToRunQueryException {
+ ResultSet rs;
+ Connection connection = null;
+ PreparedStatement ps = null;
+
+ final long tInit = System.currentTimeMillis();
+ StepQuery stepQuery;
+ try {
+ stepQuery = prepareStepQuery(processor.getQuery());
+
+ final long tAfterLoad = System.currentTimeMillis();
+ connection = datasource.getConnection();
+ ps = connection.prepareStatement(stepQuery.getSQLText());
+
+ final long tAfterParse = System.currentTimeMillis();
+ stepQuery.setupParameters(ps, processor.getParameters());
+
+ logStepQuery(processor, stepQuery);
+ final long tAfterLog = System.currentTimeMillis();
+
+ // TODO: not returning anything!
+ rs = ps.executeQuery();
+ final long tAfterExecute = System.currentTimeMillis();
+
+ final List<T> results = processor.process(rs);
+ final long tAfterParseResults = System.currentTimeMillis();
+
+ if (log.isInfoEnabled()) {
+ log.info(String.format("StepQueryRunner (load, parse, execute, return) = (%d, %d, %d, %d)", tAfterLoad - tInit, tAfterParse
+ - tAfterLoad, tAfterExecute - tAfterLog, tAfterParseResults - tAfterExecute));
+ }
+
+ return results;
+ } catch (ConfigNotLoadedException e) {
+ throw new UnableToRunQueryException(e);
+ } catch (MalformedStepQueryException e) {
+ throw new UnableToRunQueryException(e);
+ } catch (SQLException e) {
+ throw new UnableToRunQueryException(e);
+ } finally {
+ // close the statement
+ if (ps != null) {
+ try {
+ ps.close();
+ } catch (final SQLException exception) {
+ log.error("Error finalising connection", exception);
+ }
+ }
+
+ // close the connection
+ if (connection != null) {
+ try {
+ connection.close();
+ } catch (final SQLException exception) {
+ log.error("Error finalising connection", exception);
+ }
+ }
+ }
+ }
+
+ private void logStepQuery(final ResultSetProcessor<?> processor, final StepQuery stepQuery) {
+ if (log.isTraceEnabled()) {
+ StringBuffer logOutput = new StringBuffer();
+ logOutput.append("Query is: ");
+ logOutput.append(stepQuery.getSQLText());
+
+ for (String paramName : processor.getParameters().keySet()) {
+ logOutput.append(' ');
+ logOutput.append(paramName);
+ logOutput.append('=');
+ logOutput.append(processor.getParameters().get(paramName));
+ logOutput.append(", ");
+ }
+ log.trace(logOutput.toString());
+ }
+ }
+
+ /**
+ * @param query
+ * @return
+ * @throws ConfigNotLoadedException
+ * @throws MalformedStepQueryException
+ */
+ private StepQuery prepareStepQuery(final Query query) throws ConfigNotLoadedException, MalformedStepQueryException {
+ StepQuery stepQuery = queries.get(query);
+ // check whether query is in cache
+ if (stepQuery == null) {
+ stepQuery = readQueryFromFile(query);
+ }
+
+ // now check if datasource is null
+ if (datasource == null) {
+ datasource = setupDatasource();
+ }
+ return stepQuery;
+ }
+
+ private DataSource setupDatasource() throws ConfigNotLoadedException {
+ final BasicDataSource ds = new BasicDataSource();
+ ds.setDriverClassName(ConfigProvider.get("db.driver"));
+ ds.setUsername(ConfigProvider.get("db.user"));
+ ds.setPassword(ConfigProvider.get("db.password"));
+ ds.setUrl(ConfigProvider.get("connection.string"));
+ ds.setInitialSize(ConfigProvider.getInt("db.pool.initialSize"));
+ ds.setMaxActive(ConfigProvider.getInt("db.pool.maxSize"));
+ // ds.setValidationQuery(ConfigProvider.get("db.pool.validation.query"));
+ // ds.setPoolPreparedStatements(true); //NOT Supported by java DB :(
+ ds.setMaxOpenPreparedStatements(ConfigProvider.getInt("db.pool.prepared.size"));
+ ds.setDefaultAutoCommit(false);
+
+ return ds;
+ }
+
+ /**
+ * REads a query from the file
+ *
+ * @param filename
+ * @throws IOException
+ * IOException thrown if failed to load the query from disk
+ * @throws ConfigNotLoadedException
+ * failed to load the configuration
+ * @throws MalformedStepQueryException
+ * thrown if arguments found don't match a declaration
+ * @throws NoSuchFieldException
+ * @throws IllegalAccessException
+ * @throws SecurityException
+ * @throws IllegalArgumentException
+ */
+ private StepQuery readQueryFromFile(final Query queryName) throws ConfigNotLoadedException, MalformedStepQueryException {
+ // get base path from file
+ final String basePath = ConfigProvider.get("query.repository.path");
+ final String baseComponent = queryName.getComponent();
+ final String filenameBase = queryName.name();
+
+ // read query from file
+ final ClassLoader loader = Thread.currentThread().getContextClassLoader();
+ final String filename = String.format("%s%s/%s.sql", basePath, baseComponent, filenameBase);
+ final URL fileLocation = loader.getResource(filename);
+ StepQuery stepQuery = null;
+ StringBuffer q;
+
+ //check file location is non null:
+ if(fileLocation == null) {
+ throw new MalformedStepQueryException(String.format("The filename %s does not exist.", filename));
+ }
+
+ try {
+ q = new StringBuffer(FileUtils.readFileToString(new File(fileLocation.getFile())));
+ stepQuery = parseDefinitions(q, parseArguments(q));
+ stepQuery.validate();
+
+ // check whether or not to cache the query:
+ if (ConfigProvider.get("query.runner.cache").equals(CACHE_QUERY_PROPERTY)) {
+ queries.put(queryName, stepQuery);
+ }
+ } catch (final IOException e) {
+ throw new MalformedStepQueryException(String.format("Error reading query file: ", filenameBase), e);
+ }
+
+ return stepQuery;
+ }
+
+ /**
+ * Look for all lines containing the pattern define variable_name as type
+ *
+ * @param q
+ * query text to look through
+ */
+ private StepQuery parseDefinitions(final StringBuffer queryText, final StepQuery q) throws MalformedStepQueryException {
+ // all sql files with parameters will have definitions for their
+ // parameters at the top.
+ final Pattern p = Pattern.compile(DEFINITION_PATTERN);
+ final Matcher m = p.matcher(queryText);
+
+ // iterate through definitions that have been found
+ while (m.find()) {
+ final String paramName = m.group(1);
+ final String paramType = m.group(2);
+
+ try {
+ final Integer type = Types.class.getField(paramType.toUpperCase()).getInt(null);
+ q.addParamTypeMapping(paramName, type);
+ } catch (final IllegalArgumentException e) {
+ throw new MalformedStepQueryException(String.format("Could not parse argument type: %s", paramType), e);
+ } catch (final SecurityException e) {
+ throw new MalformedStepQueryException(String.format("Could not parse argument type: %s", paramType), e);
+ } catch (final IllegalAccessException e) {
+ throw new MalformedStepQueryException(String.format("Could not parse argument type: %s", paramType), e);
+ } catch (final NoSuchFieldException e) {
+ throw new MalformedStepQueryException(String.format("Could not parse argument type: %s", paramType), e);
+ }
+ }
+
+ q.setSQLText(m.replaceAll(""));
+ return q;
+ }
+
+ /**
+ * Faster than a regexp, so use this
+ *
+ * @param q
+ */
+ private StepQuery parseArguments(final StringBuffer q) {
+ final StepQuery query = new StepQuery();
+
+ int openingHash = 0;
+ while ((openingHash = q.indexOf(HASH)) != -1) {
+ final int endingHash = q.indexOf(HASH, openingHash + 1);
+ final String paramName = q.substring(openingHash + 1, endingHash);
+ query.addArgument(paramName);
+ q.replace(openingHash, endingHash + 1, QUESTION_MARK);
+ }
+
+ return query;
+ }
+
+ @Override
+ public void clearCache() {
+ queries.clear();
+ }
+
+ @Override
+ public void restartDatasourcePool() {
+ // TODO: feature is to kill off all connections, and reload it.
+
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/UnableToRunQueryException.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/UnableToRunQueryException.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/framework/UnableToRunQueryException.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,16 @@
+package com.tyndalehouse.step.web.server.db.framework;
+
+
+public class UnableToRunQueryException extends Exception {
+
+ public UnableToRunQueryException(Throwable t) {
+ super(t);
+ }
+
+ /**
+ *
+ */
+ private static final long serialVersionUID = -7810594097160119720L;
+
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimeBandVisibleDateProcessor.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimeBandVisibleDateProcessor.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimeBandVisibleDateProcessor.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,74 @@
+package com.tyndalehouse.step.web.server.db.timeline;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import com.tyndalehouse.step.web.server.common.ConfigNotLoadedException;
+import com.tyndalehouse.step.web.server.db.framework.MalformedStepQueryException;
+import com.tyndalehouse.step.web.server.db.framework.Query;
+import com.tyndalehouse.step.web.server.db.framework.ResultSetProcessor;
+import com.tyndalehouse.step.web.shared.beans.TimelineEventBean;
+
+public class TimeBandVisibleDateProcessor implements ResultSetProcessor<TimelineEventBean> {
+ private static final String EVENT_TYPE_ID = "event_type_id";
+ private static final String CERTAINTY = "certainty";
+ private static final String IMPORTANCE_ID = "importance_id";
+ private static final String TIMEBAND_ID = "timeband_id";
+ private static final String NAME = "name";
+ private static final String TO_PRECISION = "to_precision";
+ private static final String FROM_PRECISION = "from_precision";
+ private static final String TO_DATE = "to_date";
+ private static final String FROM_DATE = "from_date";
+ private static final String EVENT_ID = "event_id";
+ private static final String MIN_DATE = "min_date";
+ private static final String MAX_DATE = "max_date";
+ /**
+ * params to be passed in to the query
+ */
+ private Map<String, Object> params;
+
+
+ public TimeBandVisibleDateProcessor(long minDate, long maxDate, int timebandId) {
+ params = new HashMap<String, Object>();
+ params.put(MIN_DATE, minDate);
+ params.put(MAX_DATE, maxDate);
+ params.put(TIMEBAND_ID, timebandId);
+ }
+
+ @Override
+ public Map<String, Object> getParameters() {
+ return params;
+ }
+
+ @Override
+ public Query getQuery() {
+ return Query.GET_EVENTS_FOR_DATE_RANGE;
+ }
+
+ @Override
+ public List<TimelineEventBean> process(ResultSet rs) throws MalformedStepQueryException, ConfigNotLoadedException, SQLException {
+ //TODO: parse the event in some object, as opposed to just fields like that!
+ //in particular the precision type
+ List<TimelineEventBean> events = new ArrayList<TimelineEventBean>();
+ while(rs.next()) {
+ TimelineEventBean teb = new TimelineEventBean(
+ rs.getInt(EVENT_ID),
+ rs.getLong(FROM_DATE),
+ rs.getObject(TO_DATE) == null ? null : rs.getLong(TO_DATE),
+ rs.getString(FROM_PRECISION),
+ rs.getString(TO_PRECISION),
+ rs.getString(NAME),
+ rs.getInt(TIMEBAND_ID),
+ rs.getInt(IMPORTANCE_ID),
+ rs.getString(CERTAINTY),
+ rs.getInt(EVENT_TYPE_ID)
+ );
+ events.add(teb);
+ }
+ return events;
+ }
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginDbBean.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginDbBean.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginDbBean.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,52 @@
+package com.tyndalehouse.step.web.server.db.timeline;
+
+import com.tyndalehouse.step.web.shared.timeline.Unit;
+
+public class TimelineOriginDbBean {
+
+ private long origin;
+ private Unit unit;
+ private int timebandId;
+
+ /**
+ * @return the origin
+ */
+ public long getOrigin() {
+ return origin;
+ }
+
+ /**
+ * @return the unit
+ */
+ public Unit getUnit() {
+ return unit;
+ }
+
+ /**
+ * Sets the origin field
+ * @param origin value in ms for oring
+ */
+ public void setOrigin(long origin) {
+ this.origin = origin;
+ }
+
+ /**
+ * Sets the unit for the bean
+ * @param unit unit that should be shown on screen
+ */
+ public void setUnit(final String unit) {
+ this.unit = Unit.valueOf(unit);
+ }
+
+ public int getTimebandId() {
+ return timebandId;
+ }
+
+ /**
+ * @param timebandId the timebandId to set
+ */
+ public void setTimebandId(int timebandId) {
+ this.timebandId = timebandId;
+ }
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginProcessor.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginProcessor.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineOriginProcessor.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,60 @@
+package com.tyndalehouse.step.web.server.db.timeline;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import com.tyndalehouse.step.web.server.common.ConfigNotLoadedException;
+import com.tyndalehouse.step.web.server.db.framework.MalformedStepQueryException;
+import com.tyndalehouse.step.web.server.db.framework.Query;
+import com.tyndalehouse.step.web.server.db.framework.ResultSetProcessor;
+
+public class TimelineOriginProcessor implements ResultSetProcessor<TimelineOriginDbBean> {
+ private static final String UNIT = "unit";
+ private static final String ORIGIN = "origin";
+ private static final String TIMEBAND = "timeband_id";
+ private static final String QRY_END = "qry_end";
+ private static final String QRY_START = "qry_start";
+ private Map<String, Object> params;
+
+ /**
+ * Given a scripture range, it
+ * @param qryStart
+ * @param qryEnd
+ */
+ public TimelineOriginProcessor(final int qryStart, final int qryEnd) {
+ params = new HashMap<String, Object>();
+ params.put(QRY_START, qryStart);
+ params.put(QRY_END, qryEnd);
+ }
+
+ @Override
+ public List<TimelineOriginDbBean> process(final ResultSet rs) throws MalformedStepQueryException, ConfigNotLoadedException, SQLException {
+ List<TimelineOriginDbBean> origins = new ArrayList<TimelineOriginDbBean>();
+
+ while(rs.next()) {
+ TimelineOriginDbBean bean = new TimelineOriginDbBean();
+ bean.setOrigin(rs.getLong(ORIGIN));
+ bean.setUnit(rs.getString(UNIT));
+ bean.setTimebandId(rs.getInt(TIMEBAND));
+ origins.add(bean);
+ }
+ return origins;
+ }
+
+ @Override
+ public Map<String, Object> getParameters() {
+ return params;
+
+ }
+
+ @Override
+ public Query getQuery() {
+ return Query.LOOKUP_TIMELINE_ORIGIN;
+ }
+
+
+}
Added: trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineSetupDataProcessor.java
===================================================================
--- trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineSetupDataProcessor.java (rev 0)
+++ trunk/step-web-app/src/main/java/com/tyndalehouse/step/web/server/db/timeline/TimelineSetupDataProcessor.java 2010-02-09 21:46:06 UTC (rev 80)
@@ -0,0 +1,58 @@
+package com.tyndalehouse.step.web.server.db.timeline;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+import com.tyndalehouse.step.web.server.common.ConfigNotLoadedException;
+import com.tyndalehouse.step.web.server.db.framework.MalformedStepQueryException;
+import com.tyndalehouse.step.web.server.db.framework.Query;
+import com.tyndalehouse.step.web.server.db.framework.ResultSetProcessor;
+import com.tyndalehouse.step.web.shared.beans.TimelineBean;
+
+public class TimelineSetupDataProcessor implements ResultSetProcessor<TimelineBean> {
+ private static final String TIMEBAND_ID = "timeband_id";
+ private static final String TIMEBAND_DESCRIPTION = "timeband_description";
+ private static final String TIMEBAND_UNIT = "timeband_unit";
+
+ private Map<String, Object> params;
+
+ /**
+ * Given a scripture range, it
+ * @param qryStart
+ * @param qryEnd
+ */
+ public TimelineSetupDataProcessor() {
+ params = new HashMap<String, Object>();
+ }
+
+ @Override
+ public List<TimelineBean> process(final ResultSet rs) throws MalformedStepQueryException, ConfigNotLoadedException, SQLException {
+ List<TimelineBean> timelines = new ArrayList<TimelineBean>();
+
+ while(rs.next()) {
+ TimelineBean bean = new TimelineBean();
+ bean.setTimelineId(rs.getInt(TIMEBAND_ID));
+ bean.setTimelineDescription(rs.getString(TIMEBAND_DESCRIPTION));
+ bean.setUnit(rs.getString(TIMEBAND_UNIT));
+ timelines.add(bean);
+ }
+ return timelines;
+ }
+
+ @Override
+ public Map<String, Object> getParameters() {
+ return params;
+
+ }
+
+ @Override
+ public Query getQuery() {
+ return Query.GET_TIMELINE_SETUP_DATA;
+ }
+
+
+}
More information about the Tynstep-svn
mailing list