Add additional table in database?

[nenad982]

Hi all,

I am currently using Alfresco Enterprise 4.2.2 and I would like to create an extension for Admin Console page. For that I need few additional tables in database and I would like to add these tables (if these do not already exist) to database on application start-up (after my custom repository amp installation which will include this sql script).

Does anybody know is it possible to do it and if yes some explanation would be nice?

I already tried to add my own sql script to the spring like this:

<bean id="patch.db-V4.2-add-new-table" class="org.alfresco.repo.admin.patch.impl.SchemaUpgradeScriptPatch" parent="basePatch">
        <property name="id"><value>patch.db-V4.2-add-new-table</value></property>
        <property name="description"><value>patch.db-V4.2-add-new-table.description</value></property>
        <property name="fixesFromSchema"><value>0</value></property>
        <property name="fixesToSchema"><value>6018</value></property>
        <property name="targetSchema"><value>6019</value></property>
        <property name="scriptUrl">
            <value>classpath:alfresco/extension/dbscripts/upgrade/4.2/${db.script.dialect}add-new-table.sql</value>
        </property>
    </bean>
    
    <bean id="schema.upgrade.sampleextension" class="org.alfresco.repo.domain.schema.SchemaBootstrapRegistration" init-method="register">
        <property name="schemaBootstrap" ref="schemaBootstrap" />
        <property name="postUpdateScriptPatches">
            <list>
                <ref bean="patch.db-V4.2-add-new-table" />
            </list>
        </property>
    </bean>

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

But on the next application start-up nothing happens. Does anybody has suggestion how to include this sql script in the amp and after amp installation this script to be automatically executed on the next application start-up?

Thanks…

[mrogers]

Yes its possible.   However discouraged since it shouldn't be needed. 

The patch should be O.K.   So I'd presume you have a problem with your schema numbers.     You may like to put your "fixesToSchema" to a high number such as 100000.

[nenad982]

Hi mrogers,

I would try this change.

Why you won't suggest including this sql script in the amp?

I can run it manually before starup but I would like to avoid that additional step. Please let me know if I missed something with this idea.

Thanks,
Nenad

[mrogers]

Yes you can put it into an amp.

[nenad982]

Hi mrogers,

But is it possible to create this customization more general so it not to be dependent of schema build number?

Also when I increase the number it complains like this:

17:37:23,022 ERROR [org.springframework.web.context.ContextLoader] [localhost-startStop-1] Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'patch.db-V4.2-add-new-table' defined in class path resource [alfresco/module/repoAmp/context/service-context.xml]: Error setting property values; nested exception is org.springframework.beans.PropertyBatchUpdateException; nested PropertyAccessExceptions (1) are:
PropertyAccessException 1: org.springframework.beans.MethodInvocationException: Property 'targetSchema' threw exception; nested exception is java.lang.IllegalArgumentException: 'targetSchema' must be greater than 'fixesToSchema'
‍‍‍‍‍

And when I increase targetSchema also I don't see anything in the logs and table is not created.

Do you see maybe where I made mistake?

Thanks

[mrogers]

This patch is an example that fires regardless of version.

   <bean id="patch.wcmFolders" class="org.alfresco.repo.admin.patch.impl.WCMFoldersPatch" parent="basePatch" >
        <property name="id"><value>patch.wcmFolders</value></property>
        <property name="description"><value>patch.wcmFolders.description</value></property>
        <property name="fixesFromSchema"><value>0</value></property>
        <property name="fixesToSchema"><value>${version.schema}</value></property>
        <property name="targetSchema"><value>10000</value></property>
        <!– helper beans for execution –>
        <property name="messageSource">
            <ref bean="bootstrapSpacesMessageSource" />
        </property>
        <property name="importerBootstrap">
            <ref bean="spacesBootstrap" />
        </property>
    </bean>