Jivespace: Message List - Questions about DB schema for Clearspace version 2.5

Re: Questions about DB schema for Clearspace version 2.5

communities@jivesoftware.com — Fri, 07 Nov 2008 14:52:02 GMT

Hi Rick,

Indeed it is, nice to hear from you. Also nice to see that you take the time to help out in the forums .

What I'd like to achieve is add new tags to some documents. By some I mean a ton of documents, so doing it manually is out of the question. I'm hoping to be able to run some SQL to do this for me. I've done some research and I believe all I need to do is:

1. Stop Clearspace

2. For each document (with ID internalDocID):

(a). Pick a unique ID for each tag, let's call it X. This tagID must not currently exist in jiveTag. Insert a record into jiveTag for the new tag.

(b). Insert a record into jiveObjectTag. Record will have the following format:

102 (which is the code for a doc), internalDocID, X, creationDate.

3. Start Clearspace

I don't think I need to touch tables jiveTagSet and JiveTagSetMap because I understand them to be a grouping mechanism for tags which I don't need?

Do I need to do some kind of re-indexing because I have manually inserted records into the tables? Maybe I don't need to worry about re-indexing and I'll be good to go as soon as I restart Clearspace. Does my above plan seem sensible?

Thanks,

Ramsey

Re: Questions about DB schema for Clearspace version 2.5

rick@jivesoftware.com — Thu, 06 Nov 2008 00:07:10 GMT

Hi Ramsey (same Ramsey that I met over in London by any chance?)

Not sure if you've seen this, but there's a Database Schema guide that lists all of the tables, and down at the bottom of the doc there are ERD diagrams that I've found helpful in visualizing the relationships.

Re: Questions about DB schema for Clearspace version 2.5

communities@jivesoftware.com — Mon, 03 Nov 2008 13:59:07 GMT

Hi,

The key here is the jiveObjectTag table, this is the table that maps any object in the system to a given tag.

objectType and objectID will map to a specific document, blog, community, etc. in the system. Then the tagID maps to a tag in the jiveTags table. Thanks.

-Todd

Questions about DB schema for Clearspace version 2.5

communities@jivesoftware.com — Mon, 03 Nov 2008 11:47:46 GMT

Hi,

I'm using Clearspace version 2.5.2 and am trying to work out how tags are related to individual documents / other content in the DB schema. I've worked out how a tag maps to a community / space:

jiveTag (tagID) --> jiveTagSetMap (tagSetID) --> jiveTagSet (containerID) --> jiveCommunity

The above tells me that tags at their lowest level can be mapped to a communinty / space, and therefore by implication to every document within a space, but not to one specific document only. Can anyone explain how you map a record from the table jiveTag to an individual document in table jiveDocument?