Read and write data using Sitemagic CMS

Sitemagic CMS supports two diffent types of data storage; XML files (which is default) and the MySQL database server (for big and really performance demanding websites). Since an XML file does not qualify as a real DataBase Management System (DBMS) like MySQL, we use the general term Data Source to describe both types of data storage.
A very unique feature to Sitemagic CMS and its Data Source mechanism, is its ability to use the SQL language on top of both MySQL (obviously) and XML files. This greately simplifies writing an extension since the developer does not need to worry about the type of Data Source being used.
Browse to the CMS Guide for information on how to configure Sitemagic CMS for MySQL, if you intend to use Sitemagic CMS with a real (and faster) database engine.
It is recommended to use the Data Source class that comes with Sitemagic CMS to perform data manipulation, since this will allow extensions to work on all Sitemagic CMS installations, no matter whether they run on XML files or the MySQL database. The following examples demonstrate how easy it is to perform basic operations such as inserting data, selecting data, updating data, and removing data.
Example
// Access Data Source named Persons

$ds = new SMDataSource("Persons");

// Insert data (add Casper)

$data = new SMKeyValueCollection();
$data["name"] = "Casper";
$data["age"] = "29";
$data["sex"] = "Male";

$ds->Insert($data);

// Select data (* = get data from all columns)

$persons = $ds->Select("*");

foreach ($persons as $person)
    echo $person["name"] . " is " . $person["age"] . " years old";

// Update data (change age for Casper)

$updates = new SMKeyValueCollection();
$updates["age"] = "30";

$ds->Update($updates, "name = 'Casper'");

// Delete data (remove all males)

$ds->Delete("sex = 'Male'");
The examples above will work out-of-the-box on a Sitemagic CMS installation using the XML based Data Source layer (which is default). A table must be created on the MySQL database server when using the MySQL Data Source layer. The following snippet is the table definition for the Persons table used in the example above.
Persons.sql
CREATE TABLE IF NOT EXISTS Persons
(
    `name`        varchar(250)            DEFAULT NULL,
    `age`         integer                 DEFAULT NULL,
    `sex`         varchar(6)              DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Being able to use the SQL language on both the MySQL database and XML files is quite unique to Sitemagic CMS. It has the obvious benefit of allowing people to use Sitemagic CMS without configuring a database, while not taking away their freedom to use MySQL if they should choose so.

Consistency
Using the Data Source class will automatically ensure data consistency. The system goes from one consistent state to another. If an extension fails, all changes to the Data Sources are rolled back, unless a manual commit has been performed.
The Data Source mechanism is fully transaction based. Transactions can be manually committed (which will start a new transaction the next time data is accessed), rolled back, and even locked, allowing only one extension at a time to modify data. This also applies to the XML based Data Source.

Security
When using the Data Source mechanism, be aware that data is automatically escaped by default (which can be turned off if required) to increase security - that way users won't be able to inject SQL code into the data set.
WHERE statements on the other hand is not escaped, since it wouldn't be possible for Sitemagic CMS to determine what code was generated by an extension, and what code was injected. On the positive side this only poses a problem when using the SQL based Data Source, since the XML based Data Source is not vulnerable to injection attacks.
Good practice, never the less, is to always escape data that goes into WHERE statements:
$ds = new SMDataSource("Users");

$searchUser = $_POST["SearchUsername"]; // Could potentially contain harmfull SQL code
$where = "Username LIKE '%" . $ds->Escape($searchUser) . "%'";

$users = $ds->Select("*", $where);

Limitations
The Data Source class is intended for simple data processing (SELECT, INSERT, UPDATE, DELETE, AND COUNT with WHERE and ORDER BY statements, as well as LIMIT for limiting the amount of affected data entries). 
Naturally the XML data files are the reason why only a subset of the SQL language is implemented. Handling data using the SQL language on top of XML files is fairly complex and not as fast as a real SQL engine (although fast enough for average websites). It is therefore not suitable for advanced features such as JOIN. If more advanced features are required, the built-in MySQL API (part of PHP) might be a better choice (more information below). Mixing the Data Source mechanism and the native MySQL API works fine, so one doesn't rule out the other.
WHERE conditions may be combined by AND or OR, and supports the following comparison operators:
  • =
  • !=
  • <
  • >
  • >=
  • <=
  • LIKE
  • NOT LIKE
  • IS NULL
  • IS NOT NULL
ORDER BY statements may be composed by up to 10 order by operations - example:
Income DESC, Time DESC, Username ASC, ....

Using the native PHP MySQL API for full SQL support
The code sample below may be used to extract database login information from Settings, which are stored in config.xml.php in the root of the Sitemagic CMS installation. This information can be used to connect to MySQL using the native MySQL API in PHP.
$cfg = new SMConfiguration("config.xml.php");
$dbEntry = $cfg->GetEntry("DatabaseConnection");

if ($dbEntry !== null) // Database login details exist
{
    $dbInfo = explode(";", $dbEntry);

    $server = $dbInfo[0];
    $db = $dbInfo[1];
    $user = $dbInfo[2];
    $pass = $dbInfo[3];

    // Connect and do what you need to do here..
}