Obliquid uses Metabase as database astraction layer. Metabase is better than many other database access layers, because it manages the database structure with XML files and is able to talk to many different vendors DBMS.
Despite this, Obliquid supports only MySQL, and even if the database structure could be easily installed on other DBMSs, the queries will simply not work. Sometimes in the future the team will probably decide to support a new database too. This will mean to check every query, and will also make releasing upgrade packages more difficult: that's why it will probably happen after version 1.0 is released.
A Metabase instance is already created for you by Obliquid framework in $_obweb->mb. Metabase has extensive documentation and a tutorial. Below you will find some examples for the most used operations.
Example 12.1. Metabase Query method
In this example, we are deleting a row from news_ctext, specified by cat GET parameter. The method Query is used because we don't have to fetch any result from this query. sprintf is used because a GET parameter can be easily manipulated by a user to contain anything.
$sql="DELETE FROM ".$_obweb->tprefix
.sprintf("news_ctext WHERE id_news_cat=%d", $_GET["cat"]);
$success=$_obweb->mb->Query($sql);
if (!$success) echo $_obweb->mb->Error();
Example 12.2. Metabase GetSequenceNextValue method
In this example, we call GetTextFieldValue method manually to prepare the text values. Then we check wether catid is set to new or to a numeric value. If catid is new we do an INSERT. To find the new id to be inserted, we call the method GetSequenceNextValue: the new id will be in $catid variable. For this method to work a sequence has to be defined on the table.
//Make sure all text is fit to be inserted
$lang = $_obweb->mb->GetTextFieldValue($_POST["lang"]);
$title = $_obweb->mb->GetTextFieldValue(htmlspecialchars($_POST["title"]));
$desc = $_obweb->mb->GetTextFieldValue(htmlspecialchars($_POST["desc"]));
$rstd = $_obweb->mb->GetTextFieldValue($_POST["rstd"]);
$aa = $_obweb->mb->GetTextFieldValue($_POST["aa"]);
$ob = $_obweb->mb->GetTextFieldValue($_POST["ob"]);
if ($_POST["catid"]=="new") { //Finally.. see if new or update
$_obweb->mb->GetSequenceNextValue($_obweb->tprefix."posts_cat", $catid);
$sql = sprintf("INSERT INTO %sposts_cat VALUES (%d, %s, %s, %s, %s, %s, %s, '', %d)",
$_obweb->tprefix, $catid, $lang, $title, $desc, $rstd, $aa, $ob, $ord);
} else {
$catid = $_POST["catid"];
$sql = sprintf("UPDATE %sposts_cat SET lang=%s, title=%s, description=%s, restricted=%s, autoapprove=%s, ordby=%s, ord=%d WHERE id_posts_cat=%d",
$_obweb->tprefix, $lang, $title, $desc, $rstd, $aa, $ob, $ord, $catid);
}
$_obweb->mb->Query($sql);
Example 12.3. Metabase QueryField method
Here we find the number of unsent events. This is a simple select query with just a field to retrieve, so we use the method QueryField. Variable $total will be filled with the query result.
//number of total unsent events
$sql="SELECT count(*) FROM ".$_obweb->tprefix."msg_queue WHERE sent='N'";
$success=$_obweb->mb->QueryField($sql, $total);
if (!$success) echo $_obweb->mb->Error();
Example 12.4. Metabase QueryRow method
Most of the times a query will fetch more then a field at a time. We can use QueryRow when only a row of results is expected.
//Get the title and description of the category with this language
$sql="SELECT title, body FROM ".$_obweb->tprefix
.sprintf("news_ctext WHERE id_news_cat=%d AND lang = '%s'",
$_GET["cat"], $catlang);
$_obweb->mb->QueryRow($sql, $catinfo);
//$catinfo[0] contains the title, and $catinfo[1] the description
Example 12.5. Metabase QueryAll method
This query gets a list of post objects. QueryAll method gets the whole result set in a matrix stored in $objs variable.
//Get the list of objects
$sql = "SELECT id_posts_object, object_name, object_type, ord, location, approved FROM "
.$_obweb->tprefix."posts_object WHERE id_posts_item='".$_GET["id"]
."' ORDER BY location, ord";
$_obweb->mb->QueryAll($sql, $objs);
This variable is then passed to a Smarty template that displays the results.
{{foreach from=$objs item=obj}}
<tr>
{{section loop=$obj name=i start=1}}
<td class="tablebody">{{$obj[i]}}</td>
{{/section}}
</tr>
{{/foreach}}
$_obweb->mb->GetBooleanFieldValue(true) Convert a boolean value into a DBMS specific format that is suitable to compose query statements.
![[Caution]](images/admonitions/caution.png) | Don't use your own database connections |
|---|---|
They will make your application a lot harder to maintain, and in core Obliquid programming it's wrong | |