Creating Reports From The Vibe SQL Database
Before I start: You must not change the sitescape database. Never! So do not change any columns, fields or tables!
Creating Reports From The Vibe SQL Database
Microfocus description for Vibe tells us the “Vibe brings people, projects, and processes together in one secure place to enhance team productivity - no matter where the team is or what devices they use”.
To handle all this information and these requests a SQL database is used in the background to store values, states and much more:
Structural information about workspaces, folders, and entries (for example, their location in the workspace tree)
Identification information about workspaces, folders, and entries (for example, titles, descriptions, dates of creation/modification, and users associated with creation/modification)
User profile information (for example, full name, phone number, and e-mail address)
So the next lines describe how to access the database – there is no support for what I am doing! You must not change the sitescape database. Never! So do not change any columns, fields or tables!
Why access this database directly? I see two main reasons: customers have asked me to present values, information or properties of their Vibe environment which Vibe does not deliver by itself, and the second reason is a little bit more selfish – it’s my academic approach. I want to know more about what’s happening in the background and how the pieces fit and work together.
What is my request now? My Vibe server has several teamwork spaces for different purposes. I want to get a list of folders and subfolders of one of these workspaces.
Environment & Tools
My description and experience is based on Vibe 4.0.x and MySQL 5.5. I know Vibe well but I am not an experienced SQL programmer or specialist. So please do not argue with me that my SQL statements are not efficient enough or there is a better way to do it. The main purpose for this article is to show how easy it is to get to and read additional information out of Vibe.
For my showcase I use MySQL Workbench 6.0 on Microsoft Windows to find the right relationships, values and entries. But there are other tools too with a similar purpose (e.g. HeidiSQL - which is not an Austrian or Swiss product!). A “Read Only” mode is preferable for any tool.
After connecting successfully you will see at least two databases. Sitescape is the database we need. A bundle of tables will be listed on the left side after opening Sitescape. It seems that only tables starting with “SS_” are relevant for our task.
Selecting a table
There are so many tables, so many fields and different id fields. After digging around I have found a table SS_Forums which seems to contain what I need. Therefore I start my first selection:
and receive what I need:
Figure 2: SELECT result
Now I have to work on my SELECT statement to improve my result. I want to see only one of my team workspaces and only a few columns.
Here is my new statement:
SELECT id, pathname, creation_date as Created, modification_date as Modified, deleted, preDeleted
WHERE pathname like '/Home Workspace/Team Workspaces/Summit%';
The table will columns for id, pathname, creation date and modification date. There are two additional items “deleted” and “preDeleted” – I will explain these columns later. Within my WHERE clause I limit what I want to view. My limitation will select one defined workspace.
My result will be:
Figure 3: Adjusted result
A yellow bubble shows you that I can export this information into a csv file for further use. So you see it was really easy to get the information I was searching for.
Now I have to explain those strange columns “deleted” and “preDeleted”. If you delete an entry then you can decide if this entry will be moved to the trash where you can restore it again and bring back to life; this activity is called “preDeleted”. However if you purge an item – now restore is not possible – this entry will be marked “deleted”.
My example is simple, easy and fast to do. It will be more difficult if you have to use JOINs because these tables are linked together via id fields to store diversified information. There is an interesting example in Open Horizons magazine Issue 38 (2017/3), p15-18.
However I will came back with this remark: You must not change the sitescape database. Never! So do not change any columns, fields or tables!