plainblack.com
Username Password
search
Bookmark and Share

Database Links

To fully understand WebGUI database links you must first understand what they are used for. There are a couple of assets in WebGUI that have the ability to interact with data sources. Those sources could be the WebGUI database it’s self, another database on the same server that is hosting your website, a database that is running on a server half way around the world, or perhaps even a text file. There is a great deal of power when you have a system that can process data from multiple sources to generate useful information. In WebGUI, database links are one component of the system that makes this possible.

You can think of database links as a way for WebGUI to use data that is stored in other places. In order for WebGUI to be able to access the information, you have to tell it where it is and what credentials are needed to get to it. The function of a database link is to facilitate this connection and store the information needed to use it.

To gain a bit of perspective, lets look at the big picture. The way this all works together is that an asset in WebGUI with the ability to process external data will allow you to specify a database link for it to use in order to access that data. The database link can be used by multiple assets at the same time and only has to be defined once. The database link in turn handles all of the data manipulation the asset requested using a method that the data source you’re using understands. The important thing to realize is that the asset using the link has absolutely no idea what type of database it is interacting with or what specific commands the platform requires. The database link facilitates this interaction in a generic way regardless of the data source.

 

There is one special database link with which you should be familiar. This is the default database link. The default database link is a link to the database that WebGUI itself uses in order to function. All of your users, groups, content, and many other things are stored here. You will see the default database link when you view the databases screen from the admin console. It is labeled “WebGUI Database” and differs from other database links in that it can never be edited, copied, or deleted.

 

Because of this, those control icons are not listed next to the database link on the database page. Just as assets use a database link to communicate with data sources, so to does the core source code of WebGUI and for the same reasons described in this chapter.

 

DBI and DSN

“Make everything as simple as possible, but not simpler” -- Albert Eienstein

That quote says a lot about many things but it is especially relevant to Perl DBI. Perl DBI is a programming library that allows code written in Perl to communicate in a consistent way with multiple data sources. With all of the various ways that exist for one to store data it would be incredibly difficult if not impossible to keep up with them all, not to mention that the authors of Perl have absolutely no control over the rules that various database designers may implement for their system. DBI was written to mitigate this problem by allowing perl programmers to use a standard set of commands to perform operations on a database, regardless of what type of database that is. To do this DBI uses drivers for various data sources. You can find these drivers on the CPAN website by searching for “DBD”.

 



DBD stands for DataBase Driver and can be thought of as a translator that understands two languages. The first language it understands is DBI and the second language is the language that the database server uses. This could be MySQL, Sybase, Oracle, ODBC or any number of other data storage platforms that exist today. So to expand on our earlier example of the “Big Picture”, we can now look a little deeper at the Database Link portion of our diagram.

A database link in WebGUI is really just a series of screens that allow you to interact with DBI and DBD in a user friendly way and save the settings you assign to each one.

Note: It is important to remember that in order to connect to an external data source using a WebGUI Database Link, you must have the appropriate DBD installed for the database
type you are trying to connect to.

Another thing you need to know about is called the DSN or Data Source Name. This is nothing more than a specially formatted string of characters that tells DBD how to connect to the data source you want to connect to. The format the DSN takes is dependent on the DBD driver being used. Some examples of the more common DSN formats are available by using the WebGUI hover help feature by holding your mouse over the DSN property field in the WebGUI Database Link screen. An example DSN is also used later in this chapter for your reference.

An example transaction may go like this:

WebGUI asset code asks for DBI to create a connection to the database defined in the database link that the asset is using. The database link the asset is using is selected by the content manager when the asset is added to the site.

DBI then looks to see which driver has been specified for the connection and gathers the location of the database server and the credentials necessary to authenticate to it. Additionally, it checks to see which database operations the WebGUI database link is configured to allow, such as reading, writing, updating, and deleting data from the database.

DBI connects to the data source and the DBI commands sent by the asset are given to the DBD driver which translates them into commands that the database understands. The commands are then executed if they are allowed by the database link.

The database sends information back based on the command executed. This information is given to the DBD driver which translates it back into a DBI compatible format.

DBI hands the information off to the asset.

As you can see, the end result is that the asset code never has to change regardless of the location, format, or type of storage system being used. This allows WebGUI to access vast amounts of data in many different formats out of the box.

 

Database Link Usage

So now that you have a general idea of how a database link works, lets explore some of the places you can use one in WebGUI.

SQL Report Asset: The SQL Report asset is used to pull data from a database for reporting purposes. The data is returned and processed through the WebGUI templating system so that reports can be presented however the content manager would like. In order for a report to be generated however, the SQL Report needs to know which database link to use. Unlike the SQL Form asset, the SQL Report asset can access the default database link but only in a read-only fashion. The database link is set for an SQL Report on the properties tab of the asset at the bottom of the screen. By default, the default database link is selected.

 

WebGUI Groups: A WebGUI group can be configured to include users based on the result of a database query. More information on this feature can be found in the groups chapter. The database link property is defined on the edit group screen towards the bottom of the page.

 

See the Special Inclusion section of the Groups chapter for more information.

Custom Code: If a developer has written a plugin for WebGUI such as an authentication module, asset, macro, etc. that you have installed on your site, it may have a database link property as well. Consult the documentation and help files supplied with the code for more information.

 

Configuring a Database

Now that we know where you can use a database link and how they work, let's talk about how you actually create one.

The first step is getting to the Databases management screen in the WebGUI admin console. This can be accomplished using the WebGUI Admin Panel, or the WebGUI Admin Console by clicking on the “Databases” icon.

Clicking this icon will allow you to manage the database links for your WebGUI site. The default view lists all of the current links that are defined. Beside each link you will see three icons. The red “X” deletes the link, The “edit” icon allows you to modify the links properties, and the “copy” icon allows you to make a replica of the link with a new name. The copy functionality creates a copy of the connection information only, not the data source its self. This feature is useful if you are connecting to another database and the properties are very similar.

 

The default database link is listed in the center of the screen. On the right hand column you will see links to “Show Admin Console”, “Add a database link”, “Back to site”, “Turn Admin Off”, and “Logout”. All of these function exactly as they do in other admin console screens. The one link we’re interested in is the “Add a database link.” property. This link allows us to create a link to another data source.

In this example we’re going to connect to a MySQL database that exists on the same server as our WebGUI installation. The database name is “financials” and the mysql username and password with access rights to this database are “remote-user” and “money!” respectively. For this link we only wish to allow wobjects to use the MySQL commands select, show, and describe (read only access). Let’s go through the fields necessary to complete this example.

 

Database LinkId - This field is auto-generated by WebGUI. When you first start editing it will be “New” and after saving the details WebGUI will assign a unique id to the link. This is a read only field that you don’t need to worry about.

Title - Specify any name you want here that is descriptive of the connection to be made. This is the name that your content managers will see when selecting a database link from the drop down list using the asset property screen.

DSN - This is the Data Source Name property and specifes which DBD driver to use, the name of the database to use and various other properties depending on the DBD driver.

Generally the format is:

DBI:driver:database-name;property=value;property=value.

In our example we are connecting to a MySQL database called financials that exists on the local server so our DSN is:

DBI:mysql:financials;host=localhost

Database User - This is the user name that is sent to authenticate to the database server when attempting to make a connection.

Database Password - This is the password that is sent to authenticate to the database server when attempting to make a connection.

Allowed Keywords - These are the commands this database will allow to be sent to the database server. This feature will allow you to limit the set of commands that a database user can execute, even if the user has privileges to do so at the database level. This feature will not allow you to execute any commands to which the authenticating user does not have permission to do at the database level.

Now when we click the Save button, we return to the manage database links page and our new database link is there.

Keywords: database database link sql

Search | Most Popular | Recent Changes | Wiki Home
© 2018 Plain Black Corporation | All Rights Reserved