Schema Analysis of Jama Contour - Constraints

Contour Data Schema

This document outlines the data type conventions and tables in the Contour database schema.Some information like column indexes and foreign keys are omitted.Data types may differ slightly depending on which database is being used.

Tips

1.1 Boolean Values

Boolean Values are always represented by a T for true and F for false.

1.2 RefID & Scope Columns

You will notice that several columns include a RefId and Scope column. For certain tables the records can be configured to correspond to the project, organization or system.

The scope field may have the following values:

  1. System
  2. Organization Level
  3. Project
  4. Document Group (Not currently used)
  5. Item (Not currently used)
  6. User (Not currently used)

When writing queries, keep the scope in mind as this will indicate which table to join the ID to obtain the information.For example,if a lookup type has a scope of 2 that means the lookup is used across the entire organization. If the lookup type has a scope of 3 it's unique to each project.

1.3 Naming Conventions

Tables with an underscore such as are link tables that join data together.

 Generated by
SchemaSpy
184 Foreign Key Constraints:
SourceForge.net

Constraint Name Child Column Parent Column
FK121CEB461958AE78 jamacomment_userlikes.userId userbase.id
FK121CEB466A869C32 jamacomment_userlikes.commentId jamacomment.id
FK143BF46A1403590E user_role.roleId role.id
FK143BF46A1958AE78 user_role.userId userbase.id
FK14F51CD86915E1C2 version.originDocumentId document.id
FK14F51CD8DA506A1C version.documentId document.id
FK2941B4523C551562 review_author.reviewId review.id
FK2941B452966B3BC3 review_author.authorUserId userbase.id
FK335CD11B4267BDB3 document.user1Id userbase.id
FK335CD11B4267C174 document.user2Id userbase.id
FK335CD11B6080E200 document.priorityId lookup.id
FK335CD11B62A5139B document.projectId project.id
FK335CD11B6AC02593 document.lookup1Id lookup.id
FK335CD11B6AC02954 document.lookup2Id lookup.id
FK335CD11B6AC02D15 document.lookup3Id lookup.id
FK335CD11B6AC030D6 document.lookup4Id lookup.id
FK335CD11B6AC03497 document.lookup5Id lookup.id
FK335CD11B6AC03858 document.lookup6Id lookup.id
FK335CD11B6AC03C19 document.lookup7Id lookup.id
FK335CD11B6AC03FDA document.lookup8Id lookup.id
FK335CD11B6AC0439B document.lookup9Id lookup.id
FK335CD11B718FC1C5 document.release1Id projectrelease.id
FK335CD11B718FC586 document.release2Id projectrelease.id
FK335CD11B739D9835 document.lookup10Id lookup.id
FK335CD11B74C9DCCE document.statusId lookup.id
FK335CD11B884663C1 document.documentGroup documentgroup.id
FK335CD11B9E94C348 document.childDocumentTypeId documenttype.id
FK335CD11BBD032133 document.lockedBy userbase.id
FK335CD11BD1554582 document.releaseId projectrelease.id
FK335CD11BE181EF2C document.documentTypeId documenttype.id
FK3622D271958AE78 user_organization.userId userbase.id
FK3622D27B2808048 user_organization.organizationId organization.id
FK3AFE26183006F3ED evententry.organization organization.id
FK3AFE26183E4037C1 evententry.document document.id
FK3AFE26184D1BFDB7 evententry.eventUser userbase.id
FK3AFE2618858B68C0 evententry.project project.id
FK3AFE2618884663C1 evententry.documentGroup documentgroup.id
FK3AFE2618F3ED4351 evententry.documentType documenttype.id
FK3C591A7B62A5139B distributiongroup.projectId project.id
FK3C591A7BB2808048 distributiongroup.organizationId organization.id
FK3F0778A41A80FDAC fieldlayout.viewId jamaview.id
FK3F0778A4E181EF2C fieldlayout.documentTypeId documenttype.id
FK3FFD98B81958AE78 signature.userId userbase.id
FK3FFD98B8674BC58C signature.baseLineId baseline.id
FK46B8C0791958AE78 userlogentry.userId userbase.id
FK4FA204991403590E perspective_role.roleId role.id
FK4FA2049999AAFC2C perspective_role.perspectiveId perspective.id
FK4FA3BEC81A80FDAC perspective_view.viewId jamaview.id
FK4FA3BEC899AAFC2C perspective_view.perspectiveId perspective.id
FK5C24B9C1958AE78 email.userId userbase.id
FK5EEEBF56166CC54A report_reportformat.reportId report.id
FK5EEEBF56604E6058 report_reportformat.reportFormatId reportformat.id
FK5EF19E4462A5139B documentgroup.projectId project.id
FK5EF19E44E181EF2C documentgroup.documentTypeId documenttype.id
FK5F1F94DE4C133EE4 baseline_version.versionId version.id
FK5F1F94DE674BC58C baseline_version.baselineId baseline.id
FK767C093166CC54A reportcriteria.reportId report.id
FK7FB5A2687EF490F0 integrationfieldmapping.integrationTypeId integrationtype.id
FK7FB5A268917BEB17 integrationfieldmapping.updateDirectionTypeId integrationdirectiontype.id
FK7FB5A268BF0C02E5 integrationfieldmapping.setMappingId integrationcomponentmapping.id
FK7FB5A268F5ECC78D integrationfieldmapping.fieldId documentfield.id
FK8659CBA39587419 revision_user_item.revisionUserId revision_user.id
FK8659CBA3ED279D54 revision_user_item.status revisionuseritemstatus.id
FK8659CBA3F4E6E0A9 revision_user_item.revisionItemId revision_item.id
FK8AF7592362A5139B attachment.projectId project.id
FK8AF75923B2808048 attachment.organizationId organization.id
FK8B302528AC877456 estimate.lookupId lookup.id
FK8C592429A27E04F changerequestassociation.versionEndId version.id
FK8C59242A9096BB3 changerequestassociation.changeRequestItemId document.id
FK8C59242D32E6C16 changerequestassociation.versionStartId version.id
FK8C59242EC05EE9A changerequestassociation.associatedItemId document.id
FK996EE64562A5139B baseline.projectId project.id
FK9B0ED72F8C20F203 testresult.testCaseId document.id
FK9B0ED72FDAF8E1E5 testresult.testConfiguration lookup.id
FK9F2566584D729212 documenttypefielddefinition.documentFieldId documentfield.id
FK9F256658E181EF2C documenttypefielddefinition.documentTypeId documenttype.id
FK_ATTRIBUTE_TOKEN tokenattributes.tokenId jamatoken.id
FK_DASHBOARD_LAYOUT dashboard.layoutId dashboardlayout.id
FK_DOCDOC_ITEMSYNCSTATUS document_document.itemSyncStatus itemsyncstatus.id
FK_dockeylookup_doctype documentkeylookup.documentTypeId documenttype.id
FK_dockeylookup_project documentkeylookup.projectId project.id
FK_DOCUMENTNODE_BASELINE documentnode.baseLineId baseline.id
FK_DOCUMENTNODE_PARENT documentnode.parentNodeId documentnode.id
FK_fieldlayout_type fieldlayout.fieldLayoutTypeId fieldlayouttype.id
FK_MUTEDSUB_SUBSCRIPTION mutedsubscription.subscriptionId subscription.id
FK_MUTEDSUB_USER mutedsubscription.subscriberId userbase.id
FK_PORTLETINSTANCE_DASHBOARD portletinstance.dashboardId dashboard.id
FK_PORTLETINSTANCE_PORTLET portletinstance.portletId portlet.id
FK_PROPERTY_PORTLETINSTANCE portletinstanceproperties.portletInstanceId portletinstance.id
FK_SUBSCRIPTION_ROLE subscription.groupId role.id
FK_SUBSCRIPTION_SUBSCRIBEDBY subscription.subscribedBy userbase.id
FK_SUBSCRIPTION_USER subscription.userId userbase.id
FK_TESTCASE_TCSTATUS document.testCaseStatusId testcasestatus.id
FK_TESTCASESTEP_TESTCASE stepitem.testCaseId document.id
FK_TESTCYCLE_TESTPLAN document.testPlanId document.id
FK_TESTRUN_TESTCASE document.testCaseId document.id
FK_TESTRUN_TESTRUNSET document.testRunSetId testrunset.id
FK_TESTRUN_TESTRUNSTATUS document.testRunStatusId testrunstatus.id
FK_TESTRUN_USER document.assignedToId userbase.id
FK_TESTRUNSET_TESTCYCLE testrunset.testCycleId document.id
FK_TESTRUNSET_USER testrunset.assignedToId userbase.id
FK_TESTRUNSTEP_TESTRUN stepitem.testRunId document.id
FK_TESTSET_ASSIGNEDTO testset.assignedToId userbase.id
FK_TESTSET_TESTPLAN testset.testPlanId document.id
FK_TESTSETCASE_TESTCASE testsetcase.testCaseId document.id
FK_TESTSETCASE_TESTSET testsetcase.testSetId testset.id
FK_TRSTEP_TESTRUNSTATUS stepitem.testRunStatusId testrunstatus.id
FK_USER_AVATAR userbase.avatarId attachment.id
FKA835BA763E4037C1 document_tag.document document.id
FKA835BA76868B9321 document_tag.tag tag.id
FKA835C04BDA506A1C document_url.documentId document.id
FKA83F5AD5672FE4C8 documenttype.documentTypeCategoryId documenttypecategory.id
FKAB47207E62A5139B perspectiveconfig.projectId project.id
FKAB47207E99AAFC2C perspectiveconfig.perspectiveId perspective.id
FKAC076E271A80FDAC viewconfig.viewId jamaview.id
FKAC076E2762A5139B viewconfig.projectId project.id
FKAF999D91403590E aclpermission.roleId role.id
FKAF999D91958AE78 aclpermission.userId userbase.id
FKAF999D96BDCCF66 aclpermission.aclObjectId aclobject.id
FKB04369271A80FDAC view_itemtype.viewId jamaview.id
FKB2C6BBB27EF490F0 integrationconnection.integrationTypeId integrationtype.id
FKB2C6BBB2845B1931 integrationconnection.defaultContourUserId userbase.id
FKB408CB78884663C1 filter.documentGroup documentgroup.id
FKB408CB78F3ED4351 filter.documentType documenttype.id
FKBCFD230525D539E9 integrationcomponentmapping.targetDocumentId document.id
FKBCFD23057EF490F0 integrationcomponentmapping.integrationTypeId integrationtype.id
FKBCFD23059A05EA8C integrationcomponentmapping.commentsDirectionTypeId integrationdirectiontype.id
FKBCFD2305D766C998 integrationcomponentmapping.documentId document.id
FKBE9BA97A8CB30AEA lookup.lookupTypeId lookuptype.id
FKC53D6D56166CC54A report_criterion.reportId report.id
FKC5FF3FBF39339B3C document_document.toDocument document.id
FKC5FF3FBF45B2C50B document_document.relationshipType relationshiptype.id
FKC5FF3FBFA07B777C document_document.associationType lookup.id
FKC5FF3FBFA9DB056B document_document.fromDocument document.id
FKC5FF3FBFF0FE0433 document_document.status lookup.id
FKC653A3C1E06C7A2 perspective.stereotypeId perspective.id
FKC653A3CAA82B7C0 perspective.perspectiveTypeId perspectivetype.id
FKC84C553456B0D1E8 report.attachmentId attachment.id
FKC84C55347CDA49B0 report.previewAttachmentId attachment.id
FKC84C5534F0DEC1DE report.reportTypeId reporttype.id
FKC84EF75862A5139B review.projectId project.id
FKC89CDF0F1958AE78 distributiongroup_user.userId userbase.id
FKC89CDF0F573B620A distributiongroup_user.distributionGroupId distributiongroup.id
FKCE07663057893EF9 jamaview.stereotypeId jamaview.id
FKCE076630E57BF940 jamaview.viewTypeId viewtype.id
FKD01BA671958AE78 userfavorite.userId userbase.id
FKD312A70764B111EF projecttypefield.projectFieldId projectfield.id
FKD312A707B0B57FAF projecttypefield.projectTypeId projecttype.id
FKD8310F5993BB117A document_docattach.docattachId document.id
FKD8310F59DA506A1C document_docattach.documentId document.id
FKE13A6A03DA506A1C documentcustomfieldvalue.documentId document.id
FKE93DECE69C6616 projectrelease.releaseTypeId releasetype.id
FKECA112F417F9EB jamacomment.commenttype commenttype.id
FKECA112F41958AE78 jamacomment.userId userbase.id
FKECA112F440920E7C jamacomment.status commentstatus.id
FKECA112F4DA506A1C jamacomment.documentId document.id
FKED904B194267BDB3 project.user1Id userbase.id
FKED904B194267C174 project.user2Id userbase.id
FKED904B194A8942C6 project.projectManager userbase.id
FKED904B196AC02593 project.lookup1Id lookup.id
FKED904B196AC02954 project.lookup2Id lookup.id
FKED904B196AC02D15 project.lookup3Id lookup.id
FKED904B196AC030D6 project.lookup4Id lookup.id
FKED904B1974C9DCCE project.statusId lookup.id
FKED904B19B0B57FAF project.projectTypeId projecttype.id
FKED904B19B2808048 project.organizationId organization.id
FKED904B19C82FC15C project.projectGroupLookupId lookup.id
FKEE51863162A5139B glossaryitem.projectId project.id
FKF022D9491958AE78 user_job.userId userbase.id
FKF022D94934453ACA user_job.job jobschedule.id
FKF024151C3006F3ED userbase.organization organization.id
FKF074B7DB3C551562 revision.reviewId review.id
FKF074B7DB674BC58C revision.baselineId baseline.id
FKF074B7DBA8BABC36 revision.status revisionstatus.id
FKF081564E7C13C8FE fieldlayoutentry.documentTypeFieldId documenttypefielddefinition.id
FKF081564EA8BE6B1C fieldlayoutentry.fieldLayoutId fieldlayout.id
FKF377ABC2928CF7E8 revisionpropertyentry.revisionId revision.id
FKF9A8E6311958AE78 useritemviewhistory.userId userbase.id
FKFA072CF74C133EE4 revision_item.versionId version.id
FKFA072CF7928CF7E8 revision_item.revisionId revision.id
FKFA0C9DAF1958AE78 revision_user.userId userbase.id
FKFA0C9DAF928CF7E8 revision_user.revisionId revision.id
FKFEB8AEC756B0D1E8 document_attachment.attachmentId attachment.id
FKFEB8AEC7DA506A1C document_attachment.documentId document.id

Check Constraints:

Table Constraint Name Constraint
None detected