Alfresco Addon: Introducing MetaDBConnector
Hi Alfresco community!
Long time ago we contribute in Alfresco Addons website with “Alfresco MetaDBConnector” tool. But what is this exactly? How can be used? In which scenarios should you use it? If you have these questions and you want to learn how it works, this post is made for you!
What is it MetaDBConnector?
Simple: It is an Alfresco add-on which allows you to link database information with Alfresco content.
In which scenario can it be applied?
Mainly adds the capacity to Integrate other corporative systems with Alfresco (CRM, ERP, eCommerce..).
In short
- Link corporative databases with Alfresco content (jDBC compliant).
- Keep synchronized changes in these data with Alfresco related content.
- Search easily documents in Share by these data.
Which versions are supported?
We have two flavours:
- Community:
- Compatible till Alfresco 4.x. Community
- You can download and check the sources from our git repo here
- Enterprise:
- Compatible till Alfresco 5.1.x One.
- Share module mavenized
- Quick start support services.
- Synchronization functionality (changes in DB are replicated in Alfresco content).
- Warranty and maintenance (bugs, patches, customizations).
- Compatible till Alfresco 5.1.x One.
Okay, I’m ready for the rock & Roll!
We are going to learn how to install this addon in our Alfresco. First at all, be sure about Alfresco release version that you are using is supported by MetaDBConnnector. In my case I’ll do it with Addon community version and Alfresco 4.2.f
- Go into Alfresco Addons website (here)
- Download & compile in your local environment:
sudo git clone https://github.com/venziait/alfresco-metadbconnector.git
- To compile the Alfresco repo AMPs file:
-
cd alfresco-metadbconnector/Alfresco\ MetaDBConnector\ Amp\ Module/
-
sudo mvn clean install
- the amp will be located under:
alfresco-metadbconnector/Alfresco MetaDBConnector Amp Module/target/mdbc-1.0.amp
-
- To produce the Share jar file:
-
cd alfresco-metadbconnector/Alfresco Share MetaDBConnector Module/ sudo ant clean dist-jar
the jar file will be located under:
alfresco-metadbconnector/Alfresco Share MetaDBConnector Module/build/dist/share-mdbc-component-0.1.jar
-
- Deploy the amp and jar file in your Alfresco:
-
Stop Alfresco Server
-
Copy mdbc-1.0.amp file to $ALF_ROOT/amps
- Remove directories :
$ALF_ROOT/tomcat/webapps/alfresco $ALF_ROOT/tomcat/work/Catalina/localhost/alfresco $ALF_ROOT/tomcat/work/Catalina/localhost/share
- Install the amp in alfresco war file with the command :
cd $ALF_ROOT sudo java -jar bin/alfresco-mmt.jar install amps/mdbc-1.0.amp tomcat/webapps/alfresco.war
(note we use sudo because the installation path is /opt/alfresco and the command is launched for a non-root user)
To verify that the process was fine:sudo java -jar bin/alfresco-mmt.jar list tomcat/webapps/alfresco.war
You should be able to find the Module id ‘mdbc’
- Copy share-mdbc-component-0.1.jar to
$ALF_ROOT/tomcat/shared/lib
- If you are using other database, please be sure you have the jbdc driver is located under
$ALF_ROOT/tomcat/lib/
- Start the Server.
-
How to configure it
Inside the project there are XML files which allows to Alfresco has mapped the Alfresco objects with the database structure and properties. In order to achieve that, we need to have a minimun content model defined. So, we have three main elements playing in our project:
Mapping or connector files:
-
$ALF_ROOT/tomcat/webapps/alfresco/WEB-INF/classes/alfresco/module/mdbc/connectors/customer-connector.xml
-
$ALF_ROOT/tomcat/webapps/alfresco/WEB-INF/classes/alfresco/module/mdbc/connectors/country-connector.xml
Custom Model file:
-
$ALF_ROOT/tomcat/webapps/alfresco/WEB-INF/classes/alfresco/module/mdbc/model/mdbc-model.xml
Mapping files are composed by:
- Name, title, description of your configuration file
- Aspect: The Alfresco aspect used to store connector information in a XML structure.
- Database access and Query configuration:
driver-class-name, url, username and password to access to the DB.
column-detail : Column representing the record (for humans).
table : Table or View to query. It’s the From clause typically.
where : Filter to apply against the Table or View. The ‘%{0}%’ part is taken from the Share UI Component, when the user apply the filter to search for. - Synchronization configuration. (only available for enteprise version)
cron-expression : The scheduler to fire the synchronization process.
cron-mode : “test” to print on log the theoric changes (not performed) or “run” to perform the operation and apply changes to Alfresco contents (If any).
cron-enabled : true/false to enable/disable the cron. - Columns tag: Part used by Share UI component to select/display DB rows from document edit properties page.
Available properties per element inside columns block :
name : DB column name as part of Select clause.
primary-key : to identify the ID of the row. Mandatory at least in one column.
label : label to display column in UI component.
visible : to display (true by default) or not the column.order : to order the display of columns.
These connectors are configured by default for query a MySQL public database called “SakilaDB”. To install the SakilaDB sample database schema and introduce some data, you can download the examples from here:
mysql -u root -p path/to/sakila-schema.sql (Enter root password) mysql -u root -p path/to/sakila-data.sql (Enter root password)
Congratulations: You have this addon successfully installed! Now it’s time to start playing with it 🙂
Troubleshooting
Note: If you start the first time the connector, please be aware of this error:
-
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
Solution: We need to download the jdbc drive here and paste it on
$ALF_ROOT/tomcat/lib/
Roadmap
- Make available the source code on Alfresco 5.x comunnity version
- TODO: Deploy in a dynamic way the custom content model.
- Tests, tests & tests!
Demo
Tell me more!
If you already like the community part but you need more, please let us know and ping us!