Documentdatabase over XWIKI?

Help / Discuss
#1

We have installed XWiki as platform for the company web, we want now publish the invoices from the company, that the bookkeeping can search this.

We have ifilter, that we can insert the Metadata and the document link into a MariaDB, can we easily list this data in a database-list and can we search for it and can link the document.

Can I do this with XWiki? Or should we write a webapp for this?

Thank you for your help.

#2

Hi @Brave007! Yes you can :slight_smile:

But the answer is not so simple. There are a lot of pieces involved in the puzzle. In a single sentence, my answer would be yes, you can use XWiki to build a web application to do what you want.

If your objective is to interact with a remote database (not the one installed to store XWiki objects), I’m looking for the same solution. Our final goal is to move as much information as possible to XWiki data model, but as a first step, I need to interact with a lot of existing sources of information. I think XWiki is a competitive framework in this arena.

XWiki team and contributors are constantly updating documentation. In particular, that related with accessing remote SQL databases is a bit messy. I propose you three starting points and wait for other answer you will for sure get in this thread!

https://snippets.xwiki.org/xwiki/bin/view/Extension/Execute%20SQL
https://www.xwiki.org/xwiki/bin/view/Documentation/DevGuide/FrontendResources/LiveTable/
https://extensions.xwiki.org/xwiki/bin/view/Extension/Live%20Data%20Macro/

A very, very simple example reading a remote MariaDB database is available now here:

https://portal.igfae.usc.es/xwiki/bin/view/Team/

image

It reads ten members of our team at random. You can filter and sort the results.

It will be great to have your feedback!

1 Like
#3

Hi @Ricardo_Rodriguez,

thank you so much for your great answer, that is exactly what I search for.

I will try this and give you a answer shortly.

I have another question can I than search for this data over the XWiki search field? So can I start the indexing over the Solr search engine?

#4

Hi @Ricardo_Rodriguez,

have now tested, the SQL Tool extension works fine, I did have problems with the live data macro.
Your sample with the “10 random team´s members” look great, is it possible, that I can get your code?

Thank you so much, you did a great work. :+1::grinning:

#5

I have never tried that so far! It may be possible, but it would require an excellent understanding of XWiki functioning! I’m far from that point currently! And about Solr, of course!

#6

My very simple example doesn’t use Live Data Macro. I’m still struggling to understand how to generate JSON data from our MySQL and MariaDB relational databases. I’m not sure yet about where I’m failing!

The example generates a table using a simple Groovy script. You can see it online. I’m also copying it here:

{{groovy}}
import groovy.sql.Sql
def sql = new Sql(services.component.getConnection())
  println "(% class='doOddEven filterable grid sortable' id='tableid' %)"
  println "(% class='sortHeader' %)|=n|=Pid|= Surname|= Name|= Category"
    sql.execute "SET @row_number = 0"
    sql.rows("SELECT (@row_number:=@row_number + 1) AS n,PersonnelID,Surname,Name,CASE WHEN Category LIKE '%management%' THEN 'Rm&T staff' ELSE Category END AS Category FROM IGFAE_Personnel ORDER BY RAND() LIMIT 10").each{
      println "|${it.n}|${it.PersonnelID}|${it.Surname}|${it.Name}|${it.Category}"
    }
  sql.close()
{{/groovy}}

I defined the connection by creating and registering a XWiki Script component

import javax.inject.Named;
import javax.inject.Singleton;

import org.xwiki.component.annotation.Component;
import org.xwiki.script.service.ScriptService;

import java.sql.*;
import java.util.*;

@Named("component")

public class ComponentScriptService implements ScriptService
{
  public String get()
  {
    return "Toto is my best friend!";
  }
  public String getlong()
  {
    return "Even with a minimum number of code lines!";
  }
  public Connection getConnection()
  {
    def conn = "dummy" // probably not what you want, but valid to avoid property not found error; note by rjr on December 2023
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://YourHostname/YourDatabase", "username", "password");
    return conn;
  }
}

Disclaimer: I am still far from understanding how all the pieces fit together. It works, but it’s mostly a matter of luck! :slight_smile: I will keep posting here and in related threads!

1 Like
#7

Thank you so much @Ricardo_Rodriguez this is great, this is exactly what I need. :+1: :grinning:

Once again thank you for your help. :grinning:

#8

I have tried and it does not work, but I search for a solution, I will give an update later…