# Exporter class
exporter.class=com.atlassian.uwc.exporters.JiveExporter

### database connection information

## the name of the database
databaseName=wikidb
## dbUrl is the JDBC connection url. The following is an example mysql url
dbUrl=jdbc:mysql://localhost:3306
# The JDBC driver you wish to use
# Note: You will have to provide the JAR, unless you use MySQL. See:
# http://confluence.atlassian.com/display/CONFEXT/UWC+F.A.Q.#UWCF.A.Q.-HowdoIaddalibraryjartotheUWC%3F
# The Mysql driver has been provided. The following would be the class if you use MySQL.
jdbc.driver.class=com.mysql.jdbc.Driver

## Login info to connect to this database. You will need to replace this.
login=mylogin
password=mypassword

## This is the output directory. The export will send text files with your jive data to this directory.
output=/Users/laura/tmp
output.container=/Users/laura/tmp/exported_jive_containerdata.txt
output.hierarchy=/Users/laura/tmp/exported_jive_hierarchy.txt
output.attachments=/Users/laura/tmp/exported_jive_attachments.txt
output.titles=/Users/laura/tmp/exported_jive_titles.txt
## SQL Properties
## Should provide columns: internalDocID, versionID, containerType, containerID 
## for each document we are interested in exporting. 
## Results will be examined by column name 'internalDocID', etc.
## This example provides the documents that are not deleted and have state published
sql.doc=select d.internalDocID,v.versionID,d.containerType,d.containerID from jiveDocument d, jiveDocVersion v where d.internalDocID=v.internalDocID and v.status!=11 and v.state='published'; 
## Should provide title, userid, creationdate, modificationdate, summary, version id for each version of a document we're interested in. Use a ? in the SQL where you want the relevant internalDocID returned from 'sql.doc' above, to be inserted into this SQL.
## This example provides the latest version only for the above documents
sql.doc.versions=select v.title, v.userId, v.creationDate, v.modificationDate, v.summary, v.versionID from jiveDocVersion v, jiveDocument d where d.internalDocID=v.internalDocID and v.internalDocID=? and v.status!=11 and v.state='published';
## Gets body data
## We have to do this seperate from versions in case there isn't entry for 
## a particular document
## Provide bodyText column
## Use a ? in the SQL where you want the relevant internalDocID returned from
## sql.doc above.
sql.doc.body=select b.bodyText from jiveDocVersion v, jiveDocumentBody b, jiveDocBodyVersion bv, jiveDocument d where v.internalDocID=bv.internalDocID and v.versionID=bv.versionID and b.bodyID=bv.bodyID and d.internalDocID=v.internalDocID and v.state='published' and v.status!=11 and v.internalDocID=?;
## Gets username data
## Should provide username column. User a ? in the SQL where you want the relevant userid from the versions data to be used.
sql.doc.user=select username from jiveUser where userid=?;
## Provide a way to identify the parent containers you are allowing 
## the objects to be contained from. The number in the 4th spot of the key
## should correspond to the container type. So, for spaces/communities, the 
## key would be sql.container.filter.14.
## This sql should return a list of allowed container ids for the relevant
## container type.
## The comment .14 is an example of how to export a root community with id 1001
## and all its subspaces/subcommunities. (set the c2.communityId= bits to 
## that number). The uncommented .14 gets all communities. 
## Similarly, the commented 600 filter exports project whos parent is
## that root community or any of its sub branches. the 700 filter exports
## all social groups. The 2020 filter exports all user containers.
#sql.container.filter.14=select c1.communityID from jiveCommunity c1, jiveCommunity c2 where c1.lft > c2.lft and c1.rgt < c2.rgt and (c2.communityId=1001) union select communityId from jiveCommunity where (communityId=1001) order by communityId;
sql.container.filter.14=select communityID from jiveCommunity;
#sql.container.filter.600=select projectId from jiveProject where parentObjectType=14 and (parentObjectId=1001 or parentObjectId=any( select c1.communityID from jiveCommunity c1, jiveCommunity c2 where c1.lft > c2.lft and c1.rgt < c2.rgt and (c2.communityId=1001)));
sql.container.filter.600=select projectId from jiveProject; 
sql.container.filter.700=select groupId from jiveSGroup;
sql.container.filter.2020=select userContainerID from jiveUserContainer;
#
## Gets container (space, project, group) data for later use
## and outputs to output.container (see above)
## name should provide name and description  of a community. 
## The ? will come from the community results sql.doc calls
sql.container.name.14=select name,description from jiveCommunity where communityid=?;
sql.container.name.600=select name,description from jiveProject where projectId=?;
sql.container.name.700=select name,description from jiveSGroup where groupID=?;
sql.container.name.2020=select name,description from jiveUserContainer where userContainerId=?;
## sql.container.parent.X
## Provide a way to get parent id and type for the given container id
## The ? will come from the containerid sql.container.filter.X
## The X in the key should be the containerType for the container in question
## Column names should be 'id' and 'objtype'
sql.container.parent.14=select c1.communityID as id, '14' as objtype from jiveCommunity c1, jiveCommunity c2 where c1.lft < c2.lft and c1.rgt > c2.rgt and c2.communityId=? order by c1.lft desc;
sql.container.parent.600=select parentObjectId as id,parentObjectType as objtype from jiveProject where projectId=?;
sql.container.parent.700=select '1' as id, '14' as objtype from jiveDocument limit 1;
sql.container.parent.2020=select '1' as id, '14' as objtype from jiveDocument limit 1;
## typename should provide the name of (code) for a type: community, for ex. 
## The ? will come from the
## community results from sql.doc calls
sql.container.typename=select code from jiveObjectType where typeId=?;
## Blog data
## Provide all the blog data we need for each blog
## Columns should include: blogpostId, blogId, containerId, containerType, userId, subject, body, creationDate, and modificationDate
sql.blog=select bp.blogpostId, b.blogId, b.containerID, b.containerType, bp.userID, bp.subject, bp.body, bp.creationDate, bp.modificationDate from jiveBlogPost bp, jiveBlog b where bp.blogID=b.blogID and bp.status=2;
## Provides username for a user container. Column should be named 'username'.
## ? should be a containerId provided by an earlier call
sql.blog.usercontainer=select u.username as username from jiveUser u, jiveUserContainer uc where u.userId=uc.userId and uc.userContainerId=?;
## Provides comment sql.
## The ? should represent the object id. You should provide objecctype in the 
## where clause.
## columns should be commentid, parentcommentid, objecttype, objectid, userid, creationdate, creation modification date, and body
sql.comment.doc=select commentID, parentCommentID, objectType, objectID, userID, creationDate, modificationDate, body FROM jiveComment WHERE objectID=? and objecttype=102;
sql.comment.blog=select commentID, parentCommentID, objectType, objectID, userID, creationDate, modificationDate, body FROM jiveComment WHERE objectID=? and objecttype=38;
## Provides tag sql for retrieving tags for objects
## Put the string @objid in the sql where the object id should be used
## Put the string @objtype in the sql where the object type should be used
## Should return the tagname
sql.tag=select ot.tagID, t.tagName, t.creationDate, ot.objectID, ot.objectType from jiveObjectTag ot join jiveTag t on ot.tagID = t.tagID where ot.objectID=@objid and ot.objectType=@objtype order by t.creationDate;
## Attachments info output to a file for use by the UWC
## Presumes attachments are on the file system 
## Attachments whose file data lives in the database are not currently supported.
## .attachment - represents files in the jiveAttachment table
## .image - represents files in the jiveImage table
## .binaryBody - represents files referred to in jiveDocumentBody (files uploaded as documents)
##					@objid  - will be replaced with the internalDocId or blogpostid ## 							of one of the docs returned by sql.doc
##					@objtype - will be replaced with the associated type number for 
##								the objid
## 				each should return columns 
##						- attachment/image/bodyid field with the name 'id', 
##						- filename, 
##						- a timestamp field with the name 'date', 
sql.att.attachment= SELECT a.attachmentID as id, fileName, a.modificationDate as date FROM jiveAttachment a join jiveAttachVersion av ON a.attachmentID = av.attachmentID WHERE a.objectid=@objid and a.objectType=@objtype and av.VersionId=(select max(versionId) from jiveAttachVersion av2 where av2.internalDocId=av.internalDocId and a.attachmentId=av2.attachmentid);
sql.att.image=SELECT distinct i.imageID as id, i.fileName, i.modificationdate as date FROM jiveImage i where i.objectId=@objid and i.objectType=@objtype;
sql.att.binaryBody=select b.bodyID as id, b.fileName, b.creationDate as date from jiveDocVersion v, jiveDocumentBody b, jiveDocBodyVersion bv, jiveDocument d where v.internalDocID=bv.internalDocID and v.versionID=bv.versionID and b.bodyID=bv.bodyID and d.internalDocID=v.internalDocID and d.versionID=v.versionID and b.fileName is not null and v.internalDocID=@objid;

## Used for setting up unit tests. Can be ignored.
#debug=true
#debug.out=/Users/laura/tmp/jivetest.out.txt