Index: lams_webct_integration/.classpath =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/.classpath,v diff -u -r1.2 -r1.3 --- lams_webct_integration/.classpath 19 Nov 2007 22:57:42 -0000 1.2 +++ lams_webct_integration/.classpath 5 Dec 2007 04:30:46 -0000 1.3 @@ -12,5 +12,6 @@ + Index: lams_webct_integration/DeployableComponentConfig.xml =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/DeployableComponentConfig.xml,v diff -u -r1.3 -r1.4 --- lams_webct_integration/DeployableComponentConfig.xml 27 Nov 2007 23:13:09 -0000 1.3 +++ lams_webct_integration/DeployableComponentConfig.xml 5 Dec 2007 04:30:46 -0000 1.4 @@ -4,7 +4,7 @@ - 0.0.13 + 0.0.28 1.0 lams.integration @@ -61,6 +61,7 @@ inheritable="true" optional="false" read-only="false" + not-applicable-lc-types="Domain;Institution;Division;Campus;Course;Section" default-value="10"> @@ -108,34 +109,45 @@ - - - + default-value="@dbUrl@" /> - + read-only="true" + default-value="@dbName@" /> + --> - + read-only="true" + default-value="@dbDriver@" /> + + + - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: lams_webct_integration/build.properties =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/build.properties,v diff -u -r1.2 -r1.3 --- lams_webct_integration/build.properties 27 Nov 2007 23:13:09 -0000 1.2 +++ lams_webct_integration/build.properties 5 Dec 2007 04:30:46 -0000 1.3 @@ -3,16 +3,20 @@ # DATABASE CONFIGURATION (cant be changed after installation) ############################# -# The host of the SQL Server -dbHost=localhost +# The type of database used tested for MsSql and MySql +dbType=MySql -# The port SQL Server is running through -dbPort=1433 +# The host of the database +dbHost=192.168.111.30 +# The port database is running through +dbPort=3306 + # Database name user and password with permision to webct database -dbName=webctdatabase -dbUser=sa -dbPass=hernamewaslola +dbName=lams +dbTable=webct_lams_lesson +dbUser=webct_lams_user +dbPass=webctpass ############################# Index: lams_webct_integration/build.xml =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/build.xml,v diff -u -r1.3 -r1.4 --- lams_webct_integration/build.xml 27 Nov 2007 23:13:09 -0000 1.3 +++ lams_webct_integration/build.xml 5 Dec 2007 04:30:46 -0000 1.4 @@ -2,6 +2,7 @@ + @@ -12,11 +13,13 @@ - + + + @@ -70,10 +73,12 @@ - + + + @@ -117,7 +122,8 @@ - + + @@ -126,6 +132,8 @@ + ${dbUrl} + - + @@ -147,7 +155,7 @@ password="${dbPass}" encoding="utf8" > - + Index: lams_webct_integration/db/conf/MsSql.properties =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/db/conf/MsSql.properties,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_webct_integration/db/conf/MsSql.properties 5 Dec 2007 04:30:46 -0000 1.1 @@ -0,0 +1,3 @@ +dbUrl=jdbc:sqlserver://${dbHost}:${dbPort} +dbDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver +dbDriverClassPath=lib/sqljdbc.jar \ No newline at end of file Index: lams_webct_integration/db/conf/MySql.properties =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/db/conf/MySql.properties,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_webct_integration/db/conf/MySql.properties 5 Dec 2007 04:30:46 -0000 1.1 @@ -0,0 +1,3 @@ +dbUrl=jdbc:mysql://${dbHost}:${dbPort}/${dbName}?characterEncoding=utf8 +dbDriver=com.mysql.jdbc.Driver +dbDriverClassPath=lib/mysql-connector-java-3.1.12-bin.jar \ No newline at end of file Index: lams_webct_integration/db/sql/createTable_MsSql.sql =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/db/sql/createTable_MsSql.sql,v diff -u Binary files differ Index: lams_webct_integration/db/sql/createTable_MySql.sql =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/db/sql/createTable_MySql.sql,v diff -u Binary files differ Index: lams_webct_integration/db/sql/deleteTable_MsSql.sql =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/db/sql/deleteTable_MsSql.sql,v diff -u Binary files differ Index: lams_webct_integration/db/sql/deleteTable_MySql.sql =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/db/sql/deleteTable_MySql.sql,v diff -u Binary files differ Index: lams_webct_integration/lib/mysql-connector-java-3.1.12-bin.jar =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/lib/mysql-connector-java-3.1.12-bin.jar,v diff -u Binary files differ Index: lams_webct_integration/src/org/lamsfoundation/integration/dao/ILamsLessonDao.java =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/src/org/lamsfoundation/integration/dao/ILamsLessonDao.java,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_webct_integration/src/org/lamsfoundation/integration/dao/ILamsLessonDao.java 5 Dec 2007 04:30:46 -0000 1.1 @@ -0,0 +1,68 @@ +package org.lamsfoundation.integration.dao; + +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Timestamp; +import java.util.ArrayList; +import org.lamsfoundation.integration.webct.LamsLesson; + + +public interface ILamsLessonDao +{ + + /** + * Getting the connection to the database + * @return Connection to database + * @throws SQLException + */ + Connection getConnection() throws SQLException; + + /** + * Getting a list of lessons + * @param learningContextId the learning context id + * @param ptId the powerlink instance id + * @return a list of LAMS lessons objects + * @throws Exception + */ + ArrayList getDBLessons(long learningContextId, long ptId) throws Exception; + + /** + * Gets a shortened list of "running" lessons for learner + * @param learningContextId the learning context id + * @param ptId the powerlink instance id + * @param now an sql timestamp for the current or desired timeframe + * @return A list of running lessons + * @throws Exception + */ + ArrayList getDBLessonsForLearner(long learningContextId, long ptId, Timestamp now) throws Exception; + + /** + * Create a new lesson in the db + * @param lesson the lesson to be created + * @return true for succes + * @throws Exception + */ + boolean createDbLesson(LamsLesson lesson) throws Exception; + + /** + * Update the given lesson + * @param lesson the lesson to be updated + * @return true for success + */ + boolean updateLesson(LamsLesson lesson); + + /** + * delete a lesson from the db + * @param lsId the learning session id of the lesson + * @return true if success + */ + boolean deleteDbLesson(long lsId); + + /** + * Gets a specific lams lesson from the db + * @param lsId the unique learning session id + * @return the result lams lesson + * @throws Exception + */ + LamsLesson getDBLesson(String lsId) throws Exception; +} Fisheye: Tag 1.5 refers to a dead (removed) revision in file `lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoJDBC.java'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoMsSqlJDBC.java =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoMsSqlJDBC.java,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoMsSqlJDBC.java 5 Dec 2007 04:30:46 -0000 1.1 @@ -0,0 +1,537 @@ +package org.lamsfoundation.integration.dao; + +import java.util.Map; +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.ResultSet; +import java.sql.Timestamp; +import java.util.ArrayList; + +import org.apache.log4j.Logger; +import com.microsoft.sqlserver.jdbc.SQLServerDataSource; + +import org.lamsfoundation.integration.util.Constants; +import org.lamsfoundation.integration.webct.LamsLesson; + + + +/** + * Database connector for webct/lams integration + * @author luke foxton + * + */ +public class LamsLessonDaoMsSqlJDBC implements ILamsLessonDao{ + + private Map settings; + + private String dbUrl; + private String dbHost; + private String dbPort; + private String dbName; + private String dbUser; + private String dbPass; + + private static final Logger log = Logger.getLogger(LamsLessonDaoMsSqlJDBC.class); + + + /** + * Empty constructor + */ + public LamsLessonDaoMsSqlJDBC() { + } + + /** + * Constructor using the lamswebct Powerlink settings hashmap + * @param settings lamswebct Powerlink settings hashmap + */ + public LamsLessonDaoMsSqlJDBC(Map settings) { + this.settings = settings; + + dbHost = (String)settings.get(Constants.SETTING_DB_HOST); + dbPort = (String)settings.get(Constants.SETTING_DB_PORT); + dbName = (String)settings.get(Constants.SETTING_DB_NAME); + dbUser = (String)settings.get(Constants.SETTING_DB_USER); + dbPass = (String)settings.get(Constants.SETTING_DB_PASS); + + this.dbUrl = "jdbc:microsoft:sqlserver://" +dbHost+ + ":" +dbPort+ "/" +dbName+ "/"; + } + + /** + * Full constructor + */ + public LamsLessonDaoMsSqlJDBC(String dbHost, String dbPort, + String dbName, String dbUser, String dbPass) { + this.dbHost = dbHost; + this.dbPort = dbPort; + this.dbName = dbName; + this.dbUser = dbUser; + this.dbPass = dbPass; + + this.dbUrl = "jdbc:microsoft:sqlserver://" +dbHost+ + ":" +dbPort+ "/" +dbName+ "/"; + } + + public Connection getConnection() throws SQLException + { + + //DriverManager.registerDriver(new SQLServerDriver()); + //DriverManager.registerDriver(new Driver()); + //Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass); + //return connection; + + SQLServerDataSource ds = new SQLServerDataSource(); + ds.setServerName(dbHost); + ds.setUser(dbUser); + ds.setPassword(dbPass); + ds.setPortNumber(Integer.parseInt(dbPort)); + ds.setDatabaseName(dbName); + return ds.getConnection(); + + + } + + public ArrayList getDBLessons(long learningContextId, long ptId) throws Exception + { + ArrayList lessons = new ArrayList(); + + Connection connection; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String query = "SELECT lesson_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time " + + "FROM webctdatabase.dbo.LAMS_LESSON " + + "WHERE (learning_context_id=" +learningContextId+ ") " + + "AND (pt_id=" +ptId+ ")"; + + System.out.println("SQL INSERT: " +query); + + ResultSet rs = stmt.executeQuery(query); + + while (rs.next()) + { + LamsLesson lesson = new LamsLesson(); + lesson.setLessonId(rs.getLong("lesson_id")); + lesson.setLearningContextId(rs.getLong("learning_context_id")); + lesson.setSequenceId(rs.getLong("sequence_id")); + lesson.setTitle(rs.getString("title")); + lesson.setDescription(rs.getString("description")); + lesson.setOwnerId(rs.getString("owner_id")); + lesson.setOwnerFirstName(rs.getString("owner_first_name")); + lesson.setOwnerLastName(rs.getString("owner_last_name")); + lesson.setHidden(rs.getBoolean("hidden")); + lesson.setSchedule(rs.getBoolean("schedule")); + lesson.setStartTimestamp(rs.getTimestamp("start_date_time")); + lesson.setEndTimestamp(rs.getTimestamp("end_date_time")); + + lessons.add(lesson); + } + + stmt.close(); + connection.close(); + } + catch (SQLException e) + { + log.error("Failed to get a list of LAMS lessons.", e); + throw new Exception ("Failed to get a list of LAMS lessons."); + } + return lessons; + } + + public ArrayList getDBLessonsForLearner(long learningContextId, + long ptId, + Timestamp now) throws Exception + { + ArrayList lessons = new ArrayList(); + Connection connection; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String query = "SELECT lesson_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time " + + "FROM webctdatabase.dbo.LAMS_LESSON " + + "WHERE " + + "(" + + "(learning_context_id=" +learningContextId+ ") " + + "AND (pt_id=" +ptId+ ") " + + "AND (hidden='false') " + + "AND (" + + "(schedule='false') " + + "OR (" + + "(start_date_time <= '" +now.toString()+ "') " + + "AND (end_date_time >='" +now.toString()+ "\')" + + ")" + + "OR (" + + "(start_date_time <= '" +now.toString()+ "') " + + "AND (end_date_time IS null)" + + ")" + + ")" + + ")"; + + + System.out.println("SQL INSERT: " +query); + + ResultSet rs = stmt.executeQuery(query); + + while (rs.next()) + { + LamsLesson lesson = new LamsLesson(); + lesson.setLessonId(rs.getLong("lesson_id")); + lesson.setLearningContextId(rs.getLong("learning_context_id")); + lesson.setSequenceId(rs.getLong("sequence_id")); + lesson.setTitle(rs.getString("title")); + lesson.setDescription(rs.getString("description")); + lesson.setOwnerId(rs.getString("owner_id")); + lesson.setOwnerFirstName(rs.getString("owner_first_name")); + lesson.setOwnerLastName(rs.getString("owner_last_name")); + lesson.setHidden(rs.getBoolean("hidden")); + lesson.setSchedule(rs.getBoolean("schedule")); + lesson.setStartTimestamp(rs.getTimestamp("start_date_time")); + lesson.setEndTimestamp(rs.getTimestamp("end_date_time")); + + lessons.add(lesson); + } + + stmt.close(); + connection.close(); + } + catch (SQLException e) + { + log.error("Failed to get a list of LAMS lessons.", e); + throw new Exception ("Failed to get a list of LAMS lessons."); + } + return lessons; + } + + public boolean createDbLesson(LamsLesson lesson) throws Exception + { + Connection connection; + + System.out.println(lesson.toString()); + + int hidden=0; + int schedule=0; + + if (lesson.getHidden()) {hidden=1;} + if (lesson.getSchedule()) {schedule=1;} + + String startTimeStamp = "null"; + if (lesson.getStartTimestamp()!=null) + { + startTimeStamp = "\'" +lesson.getStartTimestamp(); + startTimeStamp = startTimeStamp.replaceAll("-", "") + "\'"; + } + + String endTimeStamp = "null"; + if (lesson.getEndTimestamp()!=null) + { + endTimeStamp = "\'" + lesson.getEndTimestamp(); + endTimeStamp = endTimeStamp.replaceAll("-", "") + "\'"; + } + + System.out.println("START: " +startTimeStamp); + System.out.println("END: " +endTimeStamp); + + + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + + String insert = "INSERT INTO webctdatabase.dbo.LAMS_LESSON" + + "(lesson_id," + + "pt_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time)" + + "VALUES" + + "(" +lesson.getLessonId()+ "," + + "" +lesson.getPtId()+ "," + + "" +lesson.getLearningContextId()+ "," + + "" +lesson.getSequenceId()+ "," + + "\'" +lesson.getOwnerId()+ "\'," + + "\'" +lesson.getOwnerFirstName()+ "\'," + + "\'" +lesson.getOwnerLastName()+ "\'," + + "\'" +lesson.getTitle()+ "\'," + + "\'" +lesson.getDescription()+ "\'," + + "" +hidden+ ","+ + "" +schedule+ "," + + "" +startTimeStamp+ "," + + "" +endTimeStamp+ ")"; + + System.out.println("SQL INSERT: " +insert); + + int rows = stmt.executeUpdate(insert); + connection.commit(); + stmt.close(); + connection.close(); + + return rows>0; + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Error inserting LAMS lesson into database.", e); + throw new Exception ("Error inserting LAMS lesson into database. " + e); + } + + } + + public boolean updateLesson(LamsLesson lesson) + { + Connection connection; + int rows = 0; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + int hidden=0; + int schedule=0; + + if (lesson.getHidden()) {hidden=1;} + if (lesson.getSchedule()) {schedule=1;} + + String startTimeStamp = "null"; + if (lesson.getStartTimestamp()!=null) + { + startTimeStamp = "\'" +lesson.getStartTimestamp(); + startTimeStamp = startTimeStamp.replaceAll("-", "") + "\'"; + } + + String endTimeStamp = "null"; + if (lesson.getEndTimestamp()!=null) + { + endTimeStamp = "\'" + lesson.getEndTimestamp(); + endTimeStamp = endTimeStamp.replaceAll("-", "") + "\'"; + } + + String update="UPDATE webctdatabase.dbo.LAMS_LESSON" + + " SET pt_id = " +lesson.getPtId()+ + ",learning_context_id = " +lesson.getLearningContextId()+ + ",sequence_id = " + lesson.getSequenceId()+ + ",owner_id = \'" + lesson.getOwnerId()+ "\'" + + ",owner_first_name = \'" +lesson.getOwnerFirstName()+ "\'" + + ",owner_last_name = \'" + lesson.getOwnerLastName()+ "\'" + + ",title = \'" +lesson.getTitle()+ "\'" + + ",description = \'" +lesson.getDescription()+ "\'" + + ",hidden = " +hidden+ + ",schedule = " +schedule+ + ",start_date_time = " +startTimeStamp+ + ",end_date_time = " +endTimeStamp+ " " + + "WHERE lesson_id = " + lesson.getLessonId(); + + System.out.println("UPDATE: " + update); + + rows = stmt.executeUpdate(update); + stmt.close(); + + connection.commit(); + connection.close(); + + + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Error updating LAMS lesson to database.", e); + } + catch (Exception e) + { + e.printStackTrace(); + log.error("Error updating LAMS lesson to database.", e); + } + + return rows>0; + + } + + + public boolean deleteDbLesson(long lsId) + { + Connection connection; + int rows = 0; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String delete="DELETE FROM webctdatabase.dbo.LAMS_LESSON WHERE lesson_id=" +lsId; + System.out.println("DELETE: " + delete); + + rows = stmt.executeUpdate(delete); + stmt.close(); + + connection.commit(); + connection.close(); + + + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Error deleting LAMS lesson from database.", e); + } + + return rows>0; + + } + + public LamsLesson getDBLesson(String lsId) throws Exception + { + LamsLesson lesson = new LamsLesson();; + Connection connection; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String query = "SELECT lesson_id," + + "pt_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time " + + "FROM webctdatabase.dbo.LAMS_LESSON " + + "WHERE (lesson_id=" +lsId+ ")"; + + System.out.println("GET LESSON: " +query); + + ResultSet rs = stmt.executeQuery(query); + + rs.next(); + + lesson.setLessonId(rs.getLong("lesson_id")); + lesson.setPtId(rs.getLong("pt_id")); + lesson.setLearningContextId(rs.getLong("learning_context_id")); + lesson.setSequenceId(rs.getLong("sequence_id")); + lesson.setTitle(rs.getString("title")); + lesson.setDescription(rs.getString("description")); + lesson.setOwnerId(rs.getString("owner_id")); + lesson.setOwnerFirstName(rs.getString("owner_first_name")); + lesson.setOwnerLastName(rs.getString("owner_last_name")); + lesson.setHidden(rs.getBoolean("hidden")); + lesson.setSchedule(rs.getBoolean("schedule")); + lesson.setStartTimestamp(rs.getTimestamp("start_date_time")); + lesson.setEndTimestamp(rs.getTimestamp("end_date_time")); + + stmt.close(); + connection.close(); + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Failed to get LAMS lesson.", e); + throw new Exception ("Failed to get LAMS lesson."); + } + catch (Exception e) + { + throw new Exception ("Failed to get LAMS lesson."); + } + return lesson; + } + + + public String getDbUrl() { + return dbUrl; + } + + public void setDbUrl(String dbUrl) { + this.dbUrl = dbUrl; + } + + public String getDbHost() { + return dbHost; + } + + public void setDbHost(String dbHost) { + this.dbHost = dbHost; + } + + public String getDbPort() { + return dbPort; + } + + public void setDbPort(String dbPort) { + this.dbPort = dbPort; + } + + public String getDbName() { + return dbName; + } + + public void setDbName(String dbName) { + this.dbName = dbName; + } + + public String getDbUser() { + return dbUser; + } + + public void setDbUser(String dbUser) { + this.dbUser = dbUser; + } + + public String getDbPass() { + return dbPass; + } + + public void setDbPass(String dbPass) { + this.dbPass = dbPass; + } + + + public Map getSettings() { + return settings; + } + + + public void setSettings(Map settings) { + this.settings = settings; + } + + + +} Index: lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoMySqlJDBC.java =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoMySqlJDBC.java,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ lams_webct_integration/src/org/lamsfoundation/integration/dao/LamsLessonDaoMySqlJDBC.java 5 Dec 2007 04:30:46 -0000 1.1 @@ -0,0 +1,545 @@ +package org.lamsfoundation.integration.dao; + +import java.util.Map; +import java.sql.Connection; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.ResultSet; +import java.sql.Timestamp; +import java.sql.DriverManager; +import java.util.ArrayList; + +import org.apache.log4j.Logger; +//import com.microsoft.sqlserver.jdbc.SQLServerDataSource; + + +import org.lamsfoundation.integration.util.Constants; +import org.lamsfoundation.integration.webct.LamsLesson; + + + +/** + * Database connector for webct/lams integration + * @author luke foxton + * + */ +public class LamsLessonDaoMySqlJDBC implements ILamsLessonDao{ + + private Map settings; + + private String dbUrl; + private String dbUser; + private String dbPass; + private String dbDriver; + private String dbTable; + + private static final Logger log = Logger.getLogger(LamsLessonDaoMySqlJDBC.class); + + + /** + * Empty constructor + */ + public LamsLessonDaoMySqlJDBC() { + } + + /** + * Constructor using the lamswebct Powerlink settings hashmap + * @param settings lamswebct Powerlink settings hashmap + */ + public LamsLessonDaoMySqlJDBC(Map settings) { + this.settings = settings; + + this.dbUrl = (String)settings.get(Constants.SETTING_DB_URL); + this.dbUser = (String)settings.get(Constants.SETTING_DB_USER); + this.dbPass = (String)settings.get(Constants.SETTING_DB_PASS); + this.dbDriver = (String)settings.get(Constants.SETTING_DB_DRIVER); + this.dbTable = (String)settings.get(Constants.SETTING_DB_TABLE); + } + + /** + * Full constructor + */ + public LamsLessonDaoMySqlJDBC(String dbUrl, String dbDriver, String dbUser, String dbPass, String dbTable) { + + this.dbDriver = dbDriver; + this.dbUser = dbUser; + this.dbPass = dbPass; + this.dbUrl = dbUrl; + this.dbTable = dbTable; + + } + + public Connection getConnection() + { + + //DriverManager.registerDriver(new SQLServerDriver()); + //DriverManager.registerDriver(new Driver()); + //Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass); + //return connection; + + Connection conn = null; + try{ + Class.forName(dbDriver).newInstance(); + conn = DriverManager.getConnection(dbUrl,dbUser,dbPass); + } + catch (Exception e) + { + e.printStackTrace(); + } + + + return conn; + /* + Driver driver = new Driver(); + driver..setURL(dbUrl); + driver.setUser(dbUser); + driver.setPassword(dbPass); + //ds.setPortNumber(Integer.parseInt(dbPort)); + //ds.setDatabaseName(dbName); + return ds.getConnection(); + */ + + } + + public ArrayList getDBLessons(long learningContextId, long ptId) throws Exception + { + ArrayList lessons = new ArrayList(); + + Connection connection; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String query = "SELECT lesson_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time " + + "FROM " +dbTable+ + " WHERE (learning_context_id=" +learningContextId+ ") " + + "AND (pt_id=" +ptId+ ")"; + + System.out.println("SQL INSERT: " +query); + + ResultSet rs = stmt.executeQuery(query); + + while (rs.next()) + { + LamsLesson lesson = new LamsLesson(); + lesson.setLessonId(rs.getLong("lesson_id")); + lesson.setLearningContextId(rs.getLong("learning_context_id")); + lesson.setSequenceId(rs.getLong("sequence_id")); + lesson.setTitle(rs.getString("title")); + lesson.setDescription(rs.getString("description")); + lesson.setOwnerId(rs.getString("owner_id")); + lesson.setOwnerFirstName(rs.getString("owner_first_name")); + lesson.setOwnerLastName(rs.getString("owner_last_name")); + lesson.setHidden(rs.getBoolean("hidden")); + lesson.setSchedule(rs.getBoolean("schedule")); + lesson.setStartTimestamp(rs.getTimestamp("start_date_time")); + lesson.setEndTimestamp(rs.getTimestamp("end_date_time")); + + lessons.add(lesson); + } + + stmt.close(); + connection.close(); + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Failed to get a list of LAMS lessons.", e); + throw new Exception ("Failed to get a list of LAMS lessons."); + } + return lessons; + } + + public ArrayList getDBLessonsForLearner(long learningContextId, + long ptId, + Timestamp now) throws Exception + { + ArrayList lessons = new ArrayList(); + Connection connection; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String query = "SELECT lesson_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time " + + "FROM " +dbTable+ + " WHERE " + + "(" + + "(learning_context_id=" +learningContextId+ ") " + + "AND (pt_id=" +ptId+ ") " + + "AND (hidden='false') " + + "AND (" + + "(schedule='false') " + + "OR (" + + "(start_date_time <= '" +now.toString()+ "') " + + "AND (end_date_time >='" +now.toString()+ "\')" + + ")" + + "OR (" + + "(start_date_time <= '" +now.toString()+ "') " + + "AND (end_date_time IS null)" + + ")" + + ")" + + ")"; + + + System.out.println("SQL INSERT: " +query); + + ResultSet rs = stmt.executeQuery(query); + + while (rs.next()) + { + LamsLesson lesson = new LamsLesson(); + lesson.setLessonId(rs.getLong("lesson_id")); + lesson.setLearningContextId(rs.getLong("learning_context_id")); + lesson.setSequenceId(rs.getLong("sequence_id")); + lesson.setTitle(rs.getString("title")); + lesson.setDescription(rs.getString("description")); + lesson.setOwnerId(rs.getString("owner_id")); + lesson.setOwnerFirstName(rs.getString("owner_first_name")); + lesson.setOwnerLastName(rs.getString("owner_last_name")); + lesson.setHidden(rs.getBoolean("hidden")); + lesson.setSchedule(rs.getBoolean("schedule")); + lesson.setStartTimestamp(rs.getTimestamp("start_date_time")); + lesson.setEndTimestamp(rs.getTimestamp("end_date_time")); + + lessons.add(lesson); + } + + stmt.close(); + connection.close(); + } + catch (SQLException e) + { + log.error("Failed to get a list of LAMS lessons.", e); + throw new Exception ("Failed to get a list of LAMS lessons."); + } + return lessons; + } + + public boolean createDbLesson(LamsLesson lesson) throws Exception + { + Connection connection; + + System.out.println(lesson.toString()); + + int hidden=0; + int schedule=0; + + if (lesson.getHidden()) {hidden=1;} + if (lesson.getSchedule()) {schedule=1;} + + String startTimeStamp = convertTimestamp(lesson.getStartTimestamp()); + String endTimeStamp = convertTimestamp(lesson.getEndTimestamp()); + /* + String startTimeStamp = "null"; + if (lesson.getStartTimestamp()!=null) + { + startTimeStamp = "\'" +lesson.getStartTimestamp(); + startTimeStamp = startTimeStamp.replaceAll("-", "") + "\'"; + } + + String endTimeStamp = "null"; + if (lesson.getEndTimestamp()!=null) + { + endTimeStamp = "\'" + lesson.getEndTimestamp(); + endTimeStamp = endTimeStamp.replaceAll("-", "") + "\'"; + } + ( + */ + + System.out.println("START: " +startTimeStamp); + System.out.println("END: " +endTimeStamp); + + + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + + String insert = "INSERT INTO " +dbTable+ + " (lesson_id," + + "pt_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time)" + + "VALUES" + + "(" +lesson.getLessonId()+ "," + + "" +lesson.getPtId()+ "," + + "" +lesson.getLearningContextId()+ "," + + "" +lesson.getSequenceId()+ "," + + "\'" +lesson.getOwnerId()+ "\'," + + "\'" +lesson.getOwnerFirstName()+ "\'," + + "\'" +lesson.getOwnerLastName()+ "\'," + + "\'" +lesson.getTitle()+ "\'," + + "\'" +lesson.getDescription()+ "\'," + + "" +hidden+ ","+ + "" +schedule+ "," + + "" +startTimeStamp+ "," + + "" +endTimeStamp+ ")"; + + System.out.println("SQL INSERT: " +insert); + + int rows = stmt.executeUpdate(insert); + + if (!connection.getAutoCommit()) + connection.commit(); + stmt.close(); + connection.close(); + + return rows>0; + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Error inserting LAMS lesson into database.", e); + throw new Exception ("Error inserting LAMS lesson into database. " + e); + } + + } + + public boolean updateLesson(LamsLesson lesson) + { + Connection connection; + int rows = 0; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + int hidden=0; + int schedule=0; + + if (lesson.getHidden()) {hidden=1;} + if (lesson.getSchedule()) {schedule=1;} + + String startTimeStamp = convertTimestamp(lesson.getStartTimestamp()); + String endTimeStamp = convertTimestamp(lesson.getEndTimestamp()); + /* + String startTimeStamp = "null"; + if (lesson.getStartTimestamp()!=null) + { + startTimeStamp = "\'" +lesson.getStartTimestamp(); + startTimeStamp = startTimeStamp.replaceAll("-", "") + "\'"; + } + + String endTimeStamp = "null"; + if (lesson.getEndTimestamp()!=null) + { + endTimeStamp = "\'" + lesson.getEndTimestamp(); + endTimeStamp = endTimeStamp.replaceAll("-", "") + "\'"; + } + ( + */ + + String update="UPDATE " +dbTable+ + " SET pt_id = " +lesson.getPtId()+ + ",learning_context_id = " +lesson.getLearningContextId()+ + ",sequence_id = " + lesson.getSequenceId()+ + ",owner_id = \'" + lesson.getOwnerId()+ "\'" + + ",owner_first_name = \'" +lesson.getOwnerFirstName()+ "\'" + + ",owner_last_name = \'" + lesson.getOwnerLastName()+ "\'" + + ",title = \'" +lesson.getTitle()+ "\'" + + ",description = \'" +lesson.getDescription()+ "\'" + + ",hidden = " +hidden+ + ",schedule = " +schedule+ + ",start_date_time = " +startTimeStamp+ + ",end_date_time = " +endTimeStamp+ " " + + "WHERE lesson_id = " + lesson.getLessonId(); + + System.out.println("UPDATE: " + update); + + rows = stmt.executeUpdate(update); + stmt.close(); + + if (!connection.getAutoCommit()) + connection.commit(); + connection.close(); + + + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Error updating LAMS lesson to database.", e); + } + catch (Exception e) + { + e.printStackTrace(); + log.error("Error updating LAMS lesson to database.", e); + } + + return rows>0; + + } + + + public boolean deleteDbLesson(long lsId) + { + Connection connection; + int rows = 0; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String delete="DELETE FROM "+dbTable+" WHERE lesson_id=" +lsId; + System.out.println("DELETE: " + delete); + + rows = stmt.executeUpdate(delete); + stmt.close(); + + if (!connection.getAutoCommit()) + connection.commit(); + connection.close(); + + + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Error deleting LAMS lesson from database.", e); + } + + return rows>0; + + } + + public LamsLesson getDBLesson(String lsId) throws Exception + { + LamsLesson lesson = new LamsLesson();; + Connection connection; + try + { + connection = getConnection(); + Statement stmt = connection.createStatement(); + + String query = "SELECT lesson_id," + + "pt_id," + + "learning_context_id," + + "sequence_id," + + "owner_id," + + "owner_first_name," + + "owner_last_name," + + "title," + + "description," + + "hidden," + + "schedule," + + "start_date_time," + + "end_date_time " + + "FROM " +dbTable+ + " WHERE (lesson_id=" +lsId+ ")"; + + System.out.println("GET LESSON: " +query); + + ResultSet rs = stmt.executeQuery(query); + + rs.next(); + + lesson.setLessonId(rs.getLong("lesson_id")); + lesson.setPtId(rs.getLong("pt_id")); + lesson.setLearningContextId(rs.getLong("learning_context_id")); + lesson.setSequenceId(rs.getLong("sequence_id")); + lesson.setTitle(rs.getString("title")); + lesson.setDescription(rs.getString("description")); + lesson.setOwnerId(rs.getString("owner_id")); + lesson.setOwnerFirstName(rs.getString("owner_first_name")); + lesson.setOwnerLastName(rs.getString("owner_last_name")); + lesson.setHidden(rs.getBoolean("hidden")); + lesson.setSchedule(rs.getBoolean("schedule")); + lesson.setStartTimestamp(rs.getTimestamp("start_date_time")); + lesson.setEndTimestamp(rs.getTimestamp("end_date_time")); + + stmt.close(); + connection.close(); + } + catch (SQLException e) + { + e.printStackTrace(); + log.error("Failed to get LAMS lesson.", e); + throw new Exception ("Failed to get LAMS lesson."); + } + catch (Exception e) + { + throw new Exception ("Failed to get LAMS lesson."); + } + return lesson; + } + + public String convertTimestamp(Timestamp t) + { + String timestampStr = "null"; + if (t!=null) + { + timestampStr = "\'" + t + "\'"; + //timestampStr = timestampStr.replaceAll("-", "") + "\'"; + } + return timestampStr; + } + + + public String getDbUrl() { + return dbUrl; + } + + public void setDbUrl(String dbUrl) { + this.dbUrl = dbUrl; + } + + public String getDbUser() { + return dbUser; + } + + public void setDbUser(String dbUser) { + this.dbUser = dbUser; + } + + public String getDbPass() { + return dbPass; + } + + public void setDbPass(String dbPass) { + this.dbPass = dbPass; + } + + + public Map getSettings() { + return settings; + } + + + public void setSettings(Map settings) { + this.settings = settings; + } +} Index: lams_webct_integration/src/org/lamsfoundation/integration/util/Constants.java =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/src/org/lamsfoundation/integration/util/Constants.java,v diff -u -r1.3 -r1.4 --- lams_webct_integration/src/org/lamsfoundation/integration/util/Constants.java 27 Nov 2007 23:13:10 -0000 1.3 +++ lams_webct_integration/src/org/lamsfoundation/integration/util/Constants.java 5 Dec 2007 04:30:46 -0000 1.4 @@ -67,5 +67,8 @@ public static final String SETTING_DB_PORT = "dbPort"; public static final String SETTING_DB_USER = "dbUser"; public static final String SETTING_DB_PASS = "dbPass"; + public static final String SETTING_DB_URL = "dbUrl"; + public static final String SETTING_DB_DRIVER = "dbDriver"; + public static final String SETTING_DB_TABLE = "dbTable"; } Index: lams_webct_integration/src/org/lamsfoundation/integration/webct/LamsModule.java =================================================================== RCS file: /usr/local/cvsroot/lams_webct_integration/src/org/lamsfoundation/integration/webct/LamsModule.java,v diff -u -r1.5 -r1.6 --- lams_webct_integration/src/org/lamsfoundation/integration/webct/LamsModule.java 28 Nov 2007 23:40:04 -0000 1.5 +++ lams_webct_integration/src/org/lamsfoundation/integration/webct/LamsModule.java 5 Dec 2007 04:30:46 -0000 1.6 @@ -1,8 +1,6 @@ package org.lamsfoundation.integration.webct; import java.io.StringWriter; -import java.net.URLEncoder; -import java.net.URL; import java.util.Hashtable; import java.util.HashMap; import java.util.Iterator; @@ -29,7 +27,9 @@ import org.apache.log4j.Logger; import org.lamsfoundation.integration.webct.LamsSecurityUtil; -import org.lamsfoundation.integration.dao.LamsLessonDaoJDBC; +import org.lamsfoundation.integration.dao.ILamsLessonDao; +import org.lamsfoundation.integration.dao.LamsLessonDaoMySqlJDBC; + import org.lamsfoundation.integration.util.Constants; @@ -48,17 +48,12 @@ private String lamsServerId; private String lamsServerSecretKey; private String webctRequestSource; + private Long lcID; + UserVO user; - - - private static final Logger log = Logger.getLogger(LamsModule.class); - - private static final String GUTENBERG_QUERY_URL = "http://www.gutenberg.org/catalog/world/results"; - - public LamsModule() { super(); @@ -99,18 +94,16 @@ - Map settings = super.getSettings(); + this.settings = super.getSettings(); //user = super.getUserId(); lamsServerUrl = (String)settings.get(Constants.SETTING_LAMS_SERVER_URL); lamsServerId = (String)settings.get(Constants.SETTING_SERVER_ID); lamsServerSecretKey = (String)settings.get(Constants.SETTING_SECRET_KEY); webctRequestSource = (String)settings.get(Constants.SETTING_REQUEST_SRC); - - - UserVO user = null; - List roles; - Long lcID = super.getCurrentLearningContextId(); + List roles; + this.lcID = super.getCurrentLearningContextId(); + request = super.getRequest(); Map params = new HashMap(); String html=null; @@ -134,41 +127,29 @@ params.put("pt_id", ptid); params.put("page_id", request.getParameter("page_id")); - - boolean isTeacher = false; - boolean isStudent = false; - try { - user = UserService.getInstance().getUser(super.getUserId(), lcID); - roles = user.getUserRoles(); - Iterator it = roles.iterator(); - while (it.hasNext()) - { - String role = it.next().toString().trim(); - - System.out.println("ROLE: " + role); - if (role.equals(UserRole.COURSE_INSTRUCTOR_ROLE.toString()) || role.equals(UserRole.SECTION_INSTRUCTOR_ROLE.toString()) ) - { - isTeacher=true; - } - else if (role.equals(UserRole.STUDENT_ROLE.toString())) - { - isStudent=true; - } - } - if (!isStudent && !isTeacher) - { - throw new LoginException("User's role does not have access to these pages."); - } - } - catch (VistaDataException e) + + try{ + this.user = UserService.getInstance().getUser(super.getUserId(), lcID); + } + catch (VistaDataException e) + { + e.printStackTrace(); + throw new LoginException("Problem getting user details from WebCt: " + e.getMessage()); + } + + boolean canAuthor = hasLamsRole("authorRoles"); + boolean canMonitor = hasLamsRole("monitorRoles"); + boolean canLearner = hasLamsRole("learnerRoles") || canMonitor; + if (!canAuthor && !canLearner && !canMonitor) { - log.error("Problem getting user info:", e); - e.printStackTrace(); - return false; + throw new LoginException("User's role does not have access to these pages."); } + params.put("canAuthor", new Boolean(canAuthor)); + params.put("canMonitor", new Boolean(canMonitor)); + params.put("canLearner", new Boolean(canLearner)); + boolean isTeacher = canAuthor || canMonitor; - String action = request.getParameter("form_action"); if(action==null || action.trim().length()==0) @@ -187,7 +168,7 @@ // generate teacher page // ie list of running lessons, and a create new lesson button try{ - LamsLessonDaoJDBC lessonDao = new LamsLessonDaoJDBC(settings); + ILamsLessonDao lessonDao = new LamsLessonDaoMySqlJDBC(settings); List lessons = lessonDao.getDBLessons(lcID.longValue(), Long.parseLong(ptid)); monitorUrl = generateRequestURL(user, lcID, "monitor"); @@ -201,6 +182,7 @@ } catch (Exception e) { + e.printStackTrace(); log.error("Error creating LAMS teach page", e); throw new LoginException("Error creating LAMS teach page"); } @@ -210,7 +192,7 @@ // generate student page // ie list of running lessons try{ - LamsLessonDaoJDBC lessonDao = new LamsLessonDaoJDBC(settings); + ILamsLessonDao lessonDao = new LamsLessonDaoMySqlJDBC(settings); // test //List lessons = lessonDao.getDBLessons(1); @@ -259,7 +241,7 @@ String successMessage="LAMS lesson deleted."; String lsID = request.getParameter("lsID"); - LamsLessonDaoJDBC lessonDao = new LamsLessonDaoJDBC(settings); + ILamsLessonDao lessonDao = new LamsLessonDaoMySqlJDBC(settings); boolean success = lessonDao.deleteDbLesson(Long.parseLong(lsID)); @@ -335,7 +317,7 @@ } } - LamsLessonDaoJDBC lessonDao = new LamsLessonDaoJDBC(settings); + ILamsLessonDao lessonDao = new LamsLessonDaoMySqlJDBC(settings); LamsLesson lesson = new LamsLesson( lsID, Long.parseLong(ptid), @@ -424,7 +406,7 @@ else if(action.equals("modify_lesson")) { try{ - LamsLessonDaoJDBC lessonDao = new LamsLessonDaoJDBC(settings); + ILamsLessonDao lessonDao = new LamsLessonDaoMySqlJDBC(settings); LamsLesson modLesson = lessonDao.getDBLesson(request.getParameter("lsID")); @@ -497,7 +479,7 @@ } else if(action.equals("modify_proc")) { - LamsLessonDaoJDBC lessonDao = new LamsLessonDaoJDBC(settings); + ILamsLessonDao lessonDao = new LamsLessonDaoMySqlJDBC(settings); try{ @@ -688,6 +670,80 @@ return t; } + + /** + * Checks if the user has the lams role + * @param roleType + * @return + */ + public boolean hasLamsRole(String lamsRoleType) + { + boolean hasLameRole = false; + String learnerRoles[] = (String[])settings.get(lamsRoleType); + + System.out.print("ROLES: "); + for(int i = 0; i