A Look at Valentina

Valentina is a set of tools including: Valentina DB (a new SQL database server), Valentina Studio (a database management tool), Valentina Report (a GUI to create reports to be used in an application like PHP) and a related development toolkit (called ADK).

In this article, we will take a look at:

• How to use Valentina Studio to manage our MySQL database;
• How to use Valentina Report to create a presentable report.

We will not, however, discuss the the Valentina Database as it is impossible to grasp a new database server and cover its underlying mechanisms in such a short article.

Valentina Studio

Valentina Studio, the database management tool, has two versions. One is free and can be downloaded here. Another is the Pro version, with more features, priced at $200 per unit. Both versions support Windows, Mac and Linux platforms, making it a cross-platform tool.

In this article, I will use Valentina Studio Pro. Thanks to Valentina for providing me with a key for my installation and evaluation.

The startup speed of Valentina Studio is fast, faster than another tool that I am using. Its main interface has 3 panes:

Fig. 1 The welcoming window

• Servers: Provides CS based database management. It supports four types of “servers”: MySQL, PostgreSQL, Valentina and ODBC. It supports both local server and remote server connections. In my case, we can see there are two remote MySQL connections and one local connection. A red dot before the connection (or “Bookmark” in Valentina’s term) means the server is currently down. A green dot means it is up and running.
• Database: Supports files based database management. Currently it supports Valentina DB and SQLite.
• Projects: This is mainly used in report generation. A “report” generated by Valentina can reside locally and remotely. But it must have Valentina Report Server support (bundled with Valentina Server) to be called from an application. The report project manages the source, query and design of a report. We will cover this later.

After selecting a server, the databases in that server will be displayed in the following cascading column view:

This is my favorite view in Valentina Studio. In this view, different levels of entities (database, table, fields, links, indexes, etc) are displayed in columns in a cascading style. Selecting a database in column one, we can choose to display tables, links, views in column two; and for tables, view its fields, indexes, etc in column three. And the final level of details will be displayed in the right most pane. We can also create and modify an entity accordingly.

Another view, less used in my case, is the tree view:

When a table is selected, it displays the table data in a grid view; when a field is selected, only the column data is displayed. In the grid, we can right click on a record to export that record into CSV or SQL.

We can double click a table in tree view or column view to bring up another view to see the master-child relationship.

It also provides an SQL dialog, where we can input SQL statements. It has code insight and code completion features, which is quite handy.

Generally speaking, Valentina Studio provides rich, but still in-much-need-of-improvement features. For example, it lacks filters and navigation whether you are in a grid view and/or SQL output view. This makes our work harder. Also, unlike PHPMyAdmin, after an operation (like inserting a field), it won’t provide an SQL statement for that operation. In most cases, this is not convenient.

Likewise, when creating a table, it takes a longer process to define all the basics: fields, index, PK, etc with the UI. The definition of PK/FK is also less intuitive than in alternative apps.

The UI behavior is not that consistent. For example, when doing some tasks (say specifying the field type), we need to double click an arrow to bring up a pop up dialog; while doing some other tasks (say changing the table’s DB engine, or its charset), we only need to single click. This inconsistency can confuse and annoy the end user.

Finally, it is understandable to have features disabled in a free version. But the functionality difference between Valentina Studio Free and Pro is too big and somehow not really justified.

I give Valentina Studio (Free and Pro) a score of 3.5 out of 5. They’re usable, but need work – especially to justify the $200 bill. Beginners will find the Free version good enough and can enjoy it at zero cost. The decision to upgrade to Pro or not can be made after some time of evaluation.

Next, we will take a look at the Report module.

Valentina Report

This is probably the feature I consider the most interesting in Valentina Studio. Reporting has always been missing from my current book collection app so I will gladly demonstrate how to create a report of my book collections within PHP.

With Valentina Studio (and Valentina Report, Valentina Server), the task will be quite simple.

A Valentina Report is a self-contained project file with the extension vsp. In the start-up window (as shown in Fig 1 above), we choose “New Project|Local“, select a name for our first Valentina Report project and then save the file to a location. The Report management window looks like this:

A Report project will have at least 3 items:

1. A data source (rsywx_test). We can select this from a dialog showing all the currently available connections and databases.
2. At least one query (q_book_by_location). A query is based on a data source and the SQL statement can be any arbitrary legal SQL statement. It does not support a visualized “drag-and-drop” way to design the query but supports code highlight and code completion (for SQL keywords and table/field names).
3. A report layout (rpt_location). It has a wizard to help us to design the layout (fields to display, header, footer, grouping, sorting, etc). This is quite an intuitive process.

As we will set up the grouping and sorting in the report design, it is suggested that during the query design, we only include the basic query (by just selecting the fields and omitting all the ORDER BY, GROUP BY statements).

The Report management window is well designed and provides design tools and preview capabilities:

Valentina Report designer is quite powerful and is able to change almost everything in a report: the styles (font family, size, color, shade, border, etc), layout (position, margin, size, alignment, etc), the aggregation (count, sum, etc) features, etc.

The preview feature is also very neat. We can have a visual of what will be rendered in the final output quickly. It has a navigation tool bar and page properties setup too.

NOTE: In the free version, “save” is disabled so it makes the report tool almost useless. I would suggest the Valentina team consider adding a save feature to their FREE version.

Note: As usual, displaying Chinese characters requires to set the font to a Chinese font family. Otherwise, the default font family (Arial) will just display the Chinese characters as a block.

Note: Though there are a few themes to choose from during the wizard process, it does not have a theme re-selection button to apply a new theme. This is a bit inconvenient.

After we finalized the design of the report, we can save it and leave the design interface. Next, we will see how to invoke that report in our PHP script and output it in different formats (HTML and PDF will be demonstrated).

Invoking a Valentina Report in PHP

To use Valentina Report in PHP, we need to put two Valentina extensions into our PHP environment. In my Windows setup, these two DLLs are: php5?_pdo_valentina.dll and php5?_valentina. It is a bit weird to notice that these two files are NOT located under the installation directory of Valentina Studio, but in <user>\Documents\Paradigma Software\VPHP_5.

Currently, Valentina Studio officially support PHP 5.3 and 5.4, a 5.5 lib (compiled under VC9) is available upon request, but a 5.5 lib compiled under VC11 is not there yet. This has forced me to downgrade my EasyPHP installation.

Also, we will need Valentina Report Server (bundled in Valentina Server). It can be downloaded here for Windows, Linux and Mac.

To use a report in PHP, we need to do some preparation:

1. Install the Valentina Server (and the Report Server). The server installation will have a default user ‘sa’ (password ‘sa’).
2. Copy the vsp report project file into the server directory under projects, like: c:\Program Files\Paradigma Software\VServer x64\projects\.
3. Launch Valentina Studio again and (if not already present), create a Bookmark to the Valentina Server we just installed.
4. In that local Valentina Server, there will be a node named “Projects”, showing the vsp file we just copied over. Click “Register” to register it.

We can now open that report in the “server” and preview it to make sure everything is still working.

So now we have a report server, a MySQL server and we will move on to the client side PHP programming. The overall structure of this 3-layers system is more or less like this:

It may be overkill as we are only doing a very simple demo but in larger applications and multi-user environments, this structure can have its advantages.

Invoking the report we designed earlier in PHP is straightforward. We will see how to make an HTML report first:

$host = 'localhost';
$username = 'sa';
$password = 'sa';
$conn_id = prepare($host, $username, $password);

$report_file = "V-rsywx.vsp";

// Create VProject, get report dumped with data

$project = vproject_create($conn_id, $report_file);
$report_id=vproject_make_new_report_with_datasource($project, 'rpt_location', "mysql://host='localhost', dbname='rsywx_test' user='root' password='xxxxxx' ", "select * from book_book");

// Get the output and render to HTML 
$html_data=vreport_print_to_buffer($report_id, VALENTINA_REPORT_PRINT_TYPE_TO_HTML, 1);
print($html_data);

// Clean up

vreport_close($report_id);
vproject_close($project);
valentina_close($conn_id);

// The helper function
function prepare($host, $login, $pass) {
    // For Standalone VPHP only
    $conn_id = valentina_connect($host, $login, $pass);

    if (!$conn_id)
        die("error connecting to VServer!");

    return $conn_id;
}

Valentina has a full API reference manual (and a PHP reference manual) so please take a look to understand the functions and related parameters.

The overall process is:

1. Make connections to the Valentina Server, NOT the MySQL server.
2. Get reference to a project and create a report with necessary data.
3. Render the report and print the HTML.
4. Clean up.

All the statements above are quite self explanatory, except one:

$report_id=vproject_make_new_report_with_datasource($project, 'rpt_location', "mysql://host='localhost', dbname='rsywx_test' user='root' password='xxxxxx' ", "select * from book_book");

This function does not have a reference entry in the manual (it has a similar function vproject_make_new_report) so I have to rely on the sample code that Valentina provides.

The first 2 parameters are simpler. One for the project resource ID ($project) and one for the report name (rpt_location), which can be an integer index (starting with 1) too.

The 3rd parameter is a connection string to the MySQL server where the data is actually stored. Why do we need to provide a new data source? The data source is used to design the report, so when we specify a report, the data source should already be fixed, right?

Well, Valentina’s answer to this is: in design stage, we may be using a test server and the configuration will be different than what we use in a production server. So by providing / overriding the connection, we can switch to the production server without re-doing the report file.

The 4th parameter is the query string to retrieve the data. Wait! We have built a query in the report design. Why do we need to provide a new query string?

From my correspondence with Valentina, I got the same answer as mentioned above.

But this does not make sense. The report design and layout relies solely on the underlying data. In my case, I am selecting my books information and group them by location, author and sort by title. All these “extra” features are specified in the report layout (not in the query). So if we provide a totally different query, say instead of selecting from book_book table, we select from book_review table, we lose control on what the selected data will be as we will use a totally different result set!

The flexibility to provide both a new connection string and a new query in this function only comes to help when the production server has a totally different set of table names as the development server. In my view, this is actually a rather bad design and we should do everything in our power to avoid it.

The output in the browser is good. It has the exact output as seen in the preview.

To output the report in PDF, only two slight changes are needed:

$html_data=vreport_print_to_buffer($report_id, VALENTINA_REPORT_PRINT_TYPE_TO_PDF, 1);
header('Content-Type: application/pdf');    

The header function is needed or the output will be distorted. The output will be shown in a browser and we can save it for further usage.

The output formats also include SVG, LaTex, PostScript, etc. It also has a similar output function to save the output to a disk file: vreport_print_to_disk.

Conclusion

In this article, we have introduced a “new kid on the block”: Valentina. We illustrated how to use its GUI database management tool (Valentina Studio) and its Report Server (bundled with Valentina Server).

In general, its GUI tool is usable but still needs a few GUI tweaks to make user experience more consistent and more efficient. The features difference between a FREE and PRO version may also need further tweaking.

Its Report server is fast and good for companies running an intranet site to generate data reports to be used by its employees. It can save a lot of time. It may also be attractive to those web apps which would like to provide dynamic report features to their visitors.

However, in my view, its API (ADK) has great room for improvement, and to be redesigned. I have quoted an example above (vproject_make_new_report_with_datasource) to show one direction.

Its installation directories in Windows also need to be more consistent:

• Valentina Studio 32 bit: Program Files (x86)
• Valentina Server 64 bit: Program Files
• PHP lib and sample files: <User>\Documents\

Regardless, I can recommend Valentina Studio and Valentina Server for personal and business use.

I am looking forward to it becoming more mature and more successful. Have you tried it? What did you think? Let us know in the comments below.

Frequently Asked Questions (FAQs) about Valentina