Author: rwatler
Date: Thu Sep 23 05:29:47 2010
New Revision: 1000323
URL: http://svn.apache.org/viewvc?rev=1000323&view=rev
Log:
JS2-1217: clean up schema primary keys and foreign key indexes
Modified:
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase1-schema.xml
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase2-schema.xml
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/registry-schema.xml
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/security-schema.xml
Modified:
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase1-schema.xml
URL:
http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase1-schema.xml?rev=1000323&r1=1000322&r2=1000323&view=diff
==============================================================================
---
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase1-schema.xml
(original)
+++
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase1-schema.xml
Thu Sep 23 05:29:47 2010
@@ -72,13 +72,13 @@ limitations under the License.
-->
<table name="CLIENT_TO_CAPABILITY">
- <column name="CLIENT_ID" required="true" type="INTEGER"/>
- <column name="CAPABILITY_ID" required="true" type="INTEGER"/>
+ <column name="CLIENT_ID" primaryKey="true" required="true"
type="INTEGER"/>
+ <column name="CAPABILITY_ID" primaryKey="true" required="true"
type="INTEGER"/>
</table>
<table name="CLIENT_TO_MIMETYPE">
- <column name="CLIENT_ID" required="true" type="INTEGER"/>
- <column name="MIMETYPE_ID" required="true" type="INTEGER"/>
+ <column name="CLIENT_ID" primaryKey="true" required="true"
type="INTEGER"/>
+ <column name="MIMETYPE_ID" primaryKey="true" required="true"
type="INTEGER"/>
</table>
<!--
@@ -86,15 +86,21 @@ limitations under the License.
-->
<table name="MEDIATYPE_TO_CAPABILITY">
- <column name="MEDIATYPE_ID" required="true" type="INTEGER"/>
- <column name="CAPABILITY_ID" required="true" type="INTEGER"/>
+ <column name="MEDIATYPE_ID" primaryKey="true" required="true"
type="INTEGER"/>
+ <column name="CAPABILITY_ID" primaryKey="true" required="true"
type="INTEGER"/>
</table>
<table name="MEDIATYPE_TO_MIMETYPE">
- <column name="MEDIATYPE_ID" required="true" type="INTEGER"/>
- <column name="MIMETYPE_ID" required="true" type="INTEGER"/>
+ <column name="MEDIATYPE_ID" primaryKey="true" required="true"
type="INTEGER"/>
+ <column name="MIMETYPE_ID" primaryKey="true" required="true"
type="INTEGER"/>
</table>
+ <!--
+ Statistics - no primary keys declared: these
+ tables should not be replicated since they are
+ simply logging user activity
+ -->
+
<table name='PORTLET_STATISTICS'>
<column name='IPADDRESS' type='VARCHAR' size="80"/>
<column name='USER_NAME' type='VARCHAR' size="80"/>
Modified:
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase2-schema.xml
URL:
http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase2-schema.xml?rev=1000323&r1=1000322&r2=1000323&view=diff
==============================================================================
---
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase2-schema.xml
(original)
+++
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/phase2-schema.xml
Thu Sep 23 05:29:47 2010
@@ -151,6 +151,9 @@ limitations under the License.
<index name="IX_FOLDER_MENU_1">
<index-column name="PARENT_ID"/>
</index>
+ <index name="IX_FOLDER_MENU_2">
+ <index-column name="FOLDER_ID"/>
+ </index>
<index name="UN_FOLDER_MENU_1">
<index-column name="FOLDER_ID"/>
<index-column name="NAME"/>
@@ -290,12 +293,15 @@ limitations under the License.
<foreign-key foreignTable="PAGE_MENU" name="FK_PAGE_MENU_1"
onDelete="cascade">
<reference foreign="MENU_ID" local="PARENT_ID"/>
</foreign-key>
- <foreign-key foreignTable="PAGE" name="PM_M_FK_PAGE_ID_PAGE"
onDelete="cascade">
+ <foreign-key foreignTable="PAGE" name="FK_PAGE_MENU_2"
onDelete="cascade">
<reference foreign="PAGE_ID" local="PAGE_ID"/>
</foreign-key>
<index name="IX_PAGE_MENU_1">
<index-column name="PARENT_ID"/>
</index>
+ <index name="IX_PAGE_MENU_2">
+ <index-column name="PAGE_ID"/>
+ </index>
<index name="UN_PAGE_MENU_1">
<index-column name="PAGE_ID"/>
<index-column name="NAME"/>
@@ -641,13 +647,16 @@ limitations under the License.
<column name="NAME" required="true" size="80" type="VARCHAR"/>
<column name="COLUMN_VALUE" size="128" type="VARCHAR"/>
<column default="1" name="FALLBACK_TYPE" type="INTEGER"/>
+ <foreign-key foreignTable="PROFILING_RULE" name="FK_RULE_CRITERION_1"
onDelete="cascade">
+ <reference foreign="RULE_ID" local="RULE_ID"/>
+ </foreign-key>
<index name="IX_RULE_CRITERION_1">
+ <index-column name="RULE_ID"/>
+ </index>
+ <index name="IX_RULE_CRITERION_2">
<index-column name="RULE_ID" />
<index-column name="FALLBACK_ORDER" />
</index>
- <foreign-key foreignTable="PROFILING_RULE" name="FK_RULE_CRITERION_1"
onDelete="cascade">
- <reference foreign="RULE_ID" local="RULE_ID"/>
- </foreign-key>
</table>
<table name="PRINCIPAL_RULE_ASSOC">
@@ -657,12 +666,8 @@ limitations under the License.
</table>
<table name="PROFILE_PAGE_ASSOC">
- <column name="LOCATOR_HASH" required="true" size="40" type="VARCHAR"/>
- <column name="PAGE_ID" required="true" size="80" type="VARCHAR"/>
- <unique name="UN_PROFILE_PAGE_1">
- <unique-column name="LOCATOR_HASH" />
- <unique-column name="PAGE_ID" />
- </unique>
+ <column name="LOCATOR_HASH" primaryKey="true" required="true"
size="40" type="VARCHAR"/>
+ <column name="PAGE_ID" primaryKey="true" required="true" size="80"
type="VARCHAR"/>
</table>
<table name="CLUBS">
Modified:
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/registry-schema.xml
URL:
http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/registry-schema.xml?rev=1000323&r1=1000322&r2=1000323&view=diff
==============================================================================
---
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/registry-schema.xml
(original)
+++
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/registry-schema.xml
Thu Sep 23 05:29:47 2010
@@ -20,6 +20,16 @@ limitations under the License.
<database name="j2">
<!--
+ Registry - this schema is not in normal form
+ and thus relies on OJB to perform propagated
+ delete and update operations: foreign keys and
+ foreign key indexes are not declared here as
+ a matter of consistency and because schema
+ level cascade operations cannot ensure data
+ consistency as is done elsewhere.
+ -->
+
+ <!--
Create Portlet Definition Table
-->
<table name="PORTLET_DEFINITION">
@@ -62,9 +72,6 @@ limitations under the License.
<column name="COLUMN_VALUE" required="true" type="LONGVARCHAR"/>
<column name="NAME" required="true" size="100" type="VARCHAR"/>
<column name="LOCALE_STRING" required="true" size="50" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_PA_METADATA_FIELDS_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="OBJECT_ID"/>
- </foreign-key>
</table>
<!--
@@ -76,9 +83,6 @@ limitations under the License.
<column name="COLUMN_VALUE" required="true" type="LONGVARCHAR"/>
<column name="NAME" required="true" size="100" type="VARCHAR"/>
<column name="LOCALE_STRING" required="true" size="50" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_DEFINITION"
name="FK_PD_METADATA_FIELDS_1" onDelete="cascade">
- <reference foreign="ID" local="OBJECT_ID"/>
- </foreign-key>
</table>
<!--
@@ -137,44 +141,6 @@ limitations under the License.
</table>
-->
- <!--
- Preferences
- -->
- <table name="PORTLET_PREFERENCE">
- <column name="ID" primaryKey="true" required="true" type="INTEGER"/>
- <column name="DTYPE" required="true" size="10" type="VARCHAR"/>
- <column name="APPLICATION_NAME" required="true" size="80"
type="VARCHAR"/>
- <column name="PORTLET_NAME" required="true" size="80" type="VARCHAR"/>
- <column name="ENTITY_ID" type="VARCHAR" size="80"/>
- <column name="USER_NAME" size="80" type="VARCHAR"/>
- <column name="NAME" required="true" size="254" type="VARCHAR"/>
- <column name="READONLY" required="true" type="BOOLEANINT"/>
- <unique name="UIX_PORTLET_PREFERENCE">
- <unique-column name="DTYPE"/>
- <unique-column name="APPLICATION_NAME"/>
- <unique-column name="PORTLET_NAME"/>
- <unique-column name="ENTITY_ID"/>
- <unique-column name="USER_NAME"/>
- <unique-column name="NAME"/>
- </unique>
-
- </table>
-
- <table name="PORTLET_PREFERENCE_VALUE">
- <column name="ID" primaryKey="true" required="true" type="INTEGER"/>
- <column name="PREF_ID" primaryKey="true" required="true"
type="INTEGER"/>
- <column name="IDX" primaryKey="true" required="true" type="SMALLINT"/>
- <column name="PREF_VALUE" size="4000" type="VARCHAR"/>
-
- <index name="IX_PREFS_PREF_ID">
- <index-column name="PREF_ID"/>
- </index>
-
- <foreign-key foreignTable="PORTLET_PREFERENCE"
name="FK_PORTLET_PREFERENCE" onDelete="cascade">
- <reference foreign="ID" local="PREF_ID"/>
- </foreign-key>
- </table>
-
<!--
Security Role Reference
-->
@@ -192,9 +158,6 @@ limitations under the License.
<column name="ID" primaryKey="true" required="true" type="INTEGER"/>
<column name="APPLICATION_ID" required="true" type="INTEGER"/>
<column name="NAME" required="true" size="150" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_SECURITY_ROLE_REF_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="APPLICATION_ID"/>
- </foreign-key>
</table>
<!--
@@ -205,9 +168,6 @@ limitations under the License.
<column name="APPLICATION_ID" required="true" type="INTEGER"/>
<column name="NAME" size="150" type="VARCHAR"/>
<column name="NAME_LINK" size="150" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_USER_ATTRIBUTE_REF_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="APPLICATION_ID"/>
- </foreign-key>
</table>
<!--
@@ -217,9 +177,6 @@ limitations under the License.
<column name="ID" primaryKey="true" required="true" type="INTEGER"/>
<column name="APPLICATION_ID" required="true" type="INTEGER"/>
<column name="NAME" size="150" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_USER_ATTRIBUTE_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="APPLICATION_ID"/>
- </foreign-key>
</table>
<!--
@@ -263,9 +220,6 @@ limitations under the License.
<column name="CUSTOM_NAME" required="true" size="150" type="VARCHAR"/>
<column name="MAPPED_NAME" size="150" type="VARCHAR"/>
<column name="PORTAL_MANAGED" required="true" type="BOOLEANINT"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_CUSTOM_PORTLET_MODE_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="APPLICATION_ID"/>
- </foreign-key>
</table>
<!--
@@ -276,9 +230,6 @@ limitations under the License.
<column name="APPLICATION_ID" required="true" type="INTEGER"/>
<column name="CUSTOM_NAME" required="true" size="150" type="VARCHAR"/>
<column name="MAPPED_NAME" size="150" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_CUSTOM_WINDOW_STATE_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="APPLICATION_ID"/>
- </foreign-key>
</table>
<table name="EVENT_DEFINITION">
@@ -288,9 +239,6 @@ limitations under the License.
<column name="NAMESPACE" required="false" size="80" type="VARCHAR"/>
<column name="PREFIX" required="false" size="20" type="VARCHAR"/>
<column name="VALUE_TYPE" required="false" size="255" type="VARCHAR"/>
- <foreign-key foreignTable="PORTLET_APPLICATION"
name="FK_EVENT_DEFINITION_1" onDelete="cascade">
- <reference foreign="APPLICATION_ID" local="APPLICATION_ID"/>
- </foreign-key>
</table>
<!--
@@ -448,4 +396,41 @@ limitations under the License.
<column name="ENCODING" required="true" size="50" type="VARCHAR"/>
</table>
+ <!--
+ Preferences
+ -->
+
+ <table name="PORTLET_PREFERENCE">
+ <column name="ID" primaryKey="true" required="true" type="INTEGER"/>
+ <column name="DTYPE" required="true" size="10" type="VARCHAR"/>
+ <column name="APPLICATION_NAME" required="true" size="80"
type="VARCHAR"/>
+ <column name="PORTLET_NAME" required="true" size="80" type="VARCHAR"/>
+ <column name="ENTITY_ID" type="VARCHAR" size="80"/>
+ <column name="USER_NAME" size="80" type="VARCHAR"/>
+ <column name="NAME" required="true" size="254" type="VARCHAR"/>
+ <column name="READONLY" required="true" type="BOOLEANINT"/>
+ <unique name="UIX_PORTLET_PREFERENCE">
+ <unique-column name="DTYPE"/>
+ <unique-column name="APPLICATION_NAME"/>
+ <unique-column name="PORTLET_NAME"/>
+ <unique-column name="ENTITY_ID"/>
+ <unique-column name="USER_NAME"/>
+ <unique-column name="NAME"/>
+ </unique>
+
+ </table>
+
+ <table name="PORTLET_PREFERENCE_VALUE">
+ <column name="ID" primaryKey="true" required="true" type="INTEGER"/>
+ <column name="PREF_ID" primaryKey="true" required="true"
type="INTEGER"/>
+ <column name="IDX" primaryKey="true" required="true" type="SMALLINT"/>
+ <column name="PREF_VALUE" size="4000" type="VARCHAR"/>
+ <foreign-key foreignTable="PORTLET_PREFERENCE"
name="FK_PORTLET_PREFERENCE" onDelete="cascade">
+ <reference foreign="ID" local="PREF_ID"/>
+ </foreign-key>
+ <index name="IX_PORTLET_PREFERENCE">
+ <index-column name="PREF_ID"/>
+ </index>
+ </table>
+
</database>
Modified:
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/security-schema.xml
URL:
http://svn.apache.org/viewvc/portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/security-schema.xml?rev=1000323&r1=1000322&r2=1000323&view=diff
==============================================================================
---
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/security-schema.xml
(original)
+++
portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema/security-schema.xml
Thu Sep 23 05:29:47 2010
@@ -55,7 +55,10 @@ limitations under the License.
</unique>
<foreign-key foreignTable="SECURITY_DOMAIN"
name="FK_SECURITY_DOMAIN_1" onDelete="cascade">
<reference foreign="DOMAIN_ID" local="DOMAIN_ID"/>
- </foreign-key>
+ </foreign-key>
+ <index name="IX_SECURITY_DOMAIN_1">
+ <index-column name="DOMAIN_ID"/>
+ </index>
</table>
<!--
@@ -72,13 +75,15 @@ limitations under the License.
<column name="PRINCIPAL_ID" primaryKey="true" required="true"
type="INTEGER"/>
<column name="ATTR_NAME" primaryKey="true" required="true" size="200"
type="VARCHAR"/>
<column name="ATTR_VALUE" size="1000" type="VARCHAR"/>
- <index name="IX_NAME_LOOKUP">
- <index-column name="ATTR_NAME"/>
- </index>
+ <index name="IX_NAME_LOOKUP">
+ <index-column name="ATTR_NAME"/>
+ </index>
<foreign-key foreignTable="SECURITY_PRINCIPAL"
name="FK_PRINCIPAL_ATTR" onDelete="cascade">
<reference foreign="PRINCIPAL_ID" local="PRINCIPAL_ID"/>
</foreign-key>
-
+ <index name="IX_PRINCIPAL_ATTR">
+ <index-column name="PRINCIPAL_ID"/>
+ </index>
</table>
<!--
@@ -98,6 +103,12 @@ limitations under the License.
<foreign-key foreignTable="SECURITY_PRINCIPAL"
name="FK_TO_PRINCIPAL_ASSOC" onDelete="cascade">
<reference foreign="PRINCIPAL_ID" local="TO_PRINCIPAL_ID"/>
</foreign-key>
+ <index name="IX_FROM_PRINCIPAL_ASSOC">
+ <index-column name="FROM_PRINCIPAL_ID"/>
+ </index>
+ <index name="IX_TO_PRINCIPAL_ASSOC">
+ <index-column name="TO_PRINCIPAL_ID"/>
+ </index>
</table>
<!--
@@ -127,6 +138,12 @@ limitations under the License.
<foreign-key foreignTable="SECURITY_PRINCIPAL"
name="FK_PRINCIPAL_PERMISSION_2" onDelete="cascade">
<reference foreign="PRINCIPAL_ID" local="PRINCIPAL_ID"/>
</foreign-key>
+ <index name="IX_PRINCIPAL_PERMISSION_1">
+ <index-column name="PERMISSION_ID"/>
+ </index>
+ <index name="IX_PRINCIPAL_PERMISSION_2">
+ <index-column name="PRINCIPAL_ID"/>
+ </index>
</table>
<!--
@@ -152,6 +169,9 @@ limitations under the License.
<foreign-key foreignTable="SECURITY_PRINCIPAL"
name="FK_SECURITY_CREDENTIAL_1" onDelete="cascade">
<reference foreign="PRINCIPAL_ID" local="PRINCIPAL_ID"/>
</foreign-key>
+ <index name="IX_SECURITY_CREDENTIAL_1">
+ <index-column name="PRINCIPAL_ID"/>
+ </index>
</table>
@@ -173,6 +193,9 @@ limitations under the License.
<foreign-key foreignTable="SECURITY_DOMAIN"
name="FK_SECURITY_DOMAIN_2" onDelete="cascade">
<reference foreign="DOMAIN_ID" local="DOMAIN_ID"/>
</foreign-key>
+ <index name="IX_SECURITY_DOMAIN_2">
+ <index-column name="DOMAIN_ID"/>
+ </index>
<unique name="UIX_SITE_NAME">
<unique-column name="NAME" />
</unique>
@@ -192,7 +215,10 @@ limitations under the License.
</unique>
<!--<foreign-key foreignTable="SECURITY_DOMAIN"
name="FK_OWNER_DOMAIN_ID" onDelete="none">
<reference foreign="DOMAIN_ID" local="OWNER_DOMAIN_ID"/>
- </foreign-key>-->
+ </foreign-key>
+ <index name="IX_OWNER_DOMAIN_ID">
+ <index-column name="OWNER_DOMAIN_ID"/>
+ </index>-->
</table>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
