portals-jetspeed-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rwat...@apache.org
Subject svn commit: r1000323 - in /portals/jetspeed-2/portal/trunk/jetspeed-portal-resources/src/main/ddl-schema: phase1-schema.xml phase2-schema.xml registry-schema.xml security-schema.xml
Date Thu, 23 Sep 2010 05:29:48 GMT
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: jetspeed-dev-unsubscribe@portals.apache.org
For additional commands, e-mail: jetspeed-dev-help@portals.apache.org


Mime
View raw message