Current development on JAMWiki is primarily focused on maintenance rather than new features due to a lack of developer availability. If you are interested in working on JAMWiki please join the jamwiki-devel mailing list.

Image comments:sql.mssql.properties

MS SQL properties file[edit]

With this file it is possible to start working with mssql database engine.

This is not only modification needed. I don't know why (I have no time to debug it today), but there is a problem with functions insertWikiUser and insertTopic. It looks like insertions are not commited, so after adding


	public void insertWikiUser(WikiUser user, Connection conn) throws Exception {            
		super.insertWikiUser(user, conn);
                conn.commit();
	}

	public void insertTopic(Topic topic, int virtualWikiId, Connection conn) throws Exception {
		super.insertTopic(topic, virtualWikiId, conn);
                conn.commit();
	}

to MSSqlQueryHandler.java everything works fine. Maybe this is a problem with DefaultQueryHandler implementation?

Test environment:

  • MSSQL 2000 database engine
  • jTDS driver [1]
  • jdbc:jtds:sqlserver://localhost:1433/jamwiki;tds=8.0;lastupdatecount=true;prepareSQL=0

The most important is prepareSQL parameter set to 0 to make pages with pagination work properly. dlpa 08-Oct-2006 10:52 PDT

Thanks! I've added the updated SQL properties file to the source code repository, but I want to take a closer look at the changes to the MSSqlQueryHandler class before adding that change as well - committing a transaction that is only partially complete will cause problems should a transaction need to be rolled back, so I'd like to first try to understand why the change above would be needed. It should be at least another week before a final 0.4.0 release is ready, so hopefully there will be time to better understand this problem. Since you've submitted code I've also added you under the "Developers" section of the CREDITS.txt file - hopefully that's OK. Thanks again! -- Ryan 08-Oct-2006 18:13 PDT

I think the problem is with function setupSpecialPage. It calls function exists(virtualWiki, topicName, true) that uses another Connection object and tries to read uncommited data, so it's blocked. It can be resolved by:
  • change exists function to use the same transaction setupSpecialPage uses, or
  • set transaction isolation level in getConnection function to Connection.TRANSACTION_READ_UNCOMMITTED
File: DatabaseHandler.java
...
private Connection getConnection() throws Exception {		
		Connection conn = DatabaseConnection.getConnection();
		conn.setAutoCommit(false);
                conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
		return conn;
}
...
Changes in MSSqlQueryHandler are not necessary. dlpa 09-Oct-2006 02:35 PDT
Good catch - changing all transactions to TRANSACTION_READ_UNCOMMITTED could potentially lead to a transaction returning an invalid row (process A adds some data, process B retrieves that data, process A then fails and rolls back, leaving process B with invalid data), so I think it's better to just fix setupSpecialPage. I've changed the current Subversion code to use lookupTopic(conn), so there should no longer be any problem with transactions being blocked. Longer term the DatabaseHandler code will need to be simplified a bit, since it's currently getting messy. Thanks for debugging! -- Ryan 09-Oct-2006 09:36 PDT
I've checkout new version from repository. Now, initialization procedure works just perfect! Good job. dlpa 09-Oct-2006 11:46 PDT

I've tested for a week MSSql support. Works good, but I had problem with pagination feature. The last page always displays maximum row count. There is a need to calculate "select top" parameter in case the last page is displayed. It could be done in MSSqlQueryHandler class functions, but I moved the logic to sql.mssql.properties file. With this modification of sql.mssql.properties file, functions:

getCategories(int virtualWikiId, Pagination pagination) 
getRecentChanges(String virtualWiki, Pagination pagination, boolean descending)
getRecentChanges(int topicId, Pagination pagination, boolean descending) 
getUserContributions(String virtualWiki, String userString, Pagination pagination, boolean descending) 
lookupTopicByType(int virtualWikiId, int topicType, Pagination pagination) 

should be removed from MSSqlQueryHandler class.

Any better ideas? -- dlpa 20-Oct-2006 01:34 PDT

I've added the changes you suggested to the source repository, so they'll be included in the final release of JAMWiki 0.4.0. I'm not exactly sure I understand the bug you're running into though - there is a known problem right now where if you (for example) display ten results per page, and there are exactly ten results, that the pagination will still show a "display next 10 results" link - is that the issue? Or is the problem that MS SQL to know that the last page only displays (for example) 7 results when displaying ten results per page and 17 total results? We will probably need to modify the pagination code to determine the total number of results available (select count(*) from ...) so perhaps that will help in addressing the MS SQL issue. -- Ryan 20-Oct-2006 11:33 PDT
See the example:
  • 17 records in the database,
  • first 10 (1-10) records displayed OK,
  • "next 10" displays 10 records (8-17) - should be 7 records (11-17),
  • "next 10" displays 10 records again (8-17)... and so on.
Changes I've made eliminate that issue. -- dlpa 20-Oct-2006 14:04 PDT
OK, I get it now. Provided the code works I'm completely happy to use your solution, and I've already committed it to the source code repository. If you or someone else comes up with a simpler way to perform this query we can always change it in the future, but for now I think "working" is very much preferable to "broken" :) -- Ryan 20-Oct-2006 14:07 PDT

Table creation[edit]

After last changes jam_topic table references jam_topic_version by current_version_id field. It makes error during setup fresh JamWiki installation. STATEMENT_CREATE_TOPIC_TABLE creates a table references to table does not exist yet (jam_topic_version). I've moved creation a reference to STATEMENT_CREATE_TOPIC_VERSION_TABLE. It makes that it just works, but maybe we need something smarter? -- dlpa 24-Oct-2006 14:18 PDT

Thanks, I haven't done any testing with a clean install yet. The constraint can be moved into a separate SQL statement as you've done, which should hopefully fix the problem. I'll get something done shortly. -- Ryan 24-Oct-2006 15:09 PDT