Databases & XML

Storing historized data in a relational database

There are quite a few concepts that deal with storing historized data in a relational database; that means creating an archive copy or journal when data is updated or deleted. Spontaneously, these options¬†come into my mind: Using the built-in functionality¬†(for example, in the latest Oracle database). Using black-box technology is dangerous, if you don’t understand…
Read more

Using SQL fixtures for database related unit tests

This is a short tutorial to show how to use fixtures for database related unit tests. The inspiration for this post came from a conversation I had at the recent PHP Unconference in Hamburg, Germany. From my experience, the usual way to test database related classes is to use a special test database, that needs…
Read more

Updated version of Zend_Db_Adapter_Odbtp_Mssql

As people start asking me about that ODBTP adapter for Zend Framework, I will publish it as attachment to this post now. It should work with the latest version of Zend Framework (as reported by a developer). If you want to use it, just copy the files to your include path (make sure the path…
Read more

Switched from Drupal 5 to WordPress 2.5

After having trouble with the Drupal upgrade I decided to give WordPress a try. The funny thing is to copy the existing posts to the new tables. If you got the same problem, you can use this view: CREATE VIEW wp_import AS SELECT a.nid AS ID, 1 AS post_author, FROM_UNIXTIME(a.created) AS post_date, FROM_UNIXTIME(a.created) AS post_date_gmt,…
Read more

Comma separated list in SQL

Just a short example of how to write a stored function that returns a comma separated list of values (Microsoft Transact-SQL):


CREATE FUNCION get_authors (@pubId int)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @authorList nvarchar(max);

SELECT @authorList = @authorList + ', ' +
authorFirstName + ' ' + authorLastName
FROM pdb_authors a, pdb_publicationAuthors b
WHERE a.authorId = b.authorId AND b.pubId = @pubId;

RETURN(SUBSTRING(@authorList, 3, LEN(@authorList) - 2))
END

Optimizing SQL – Part 1

+++ Design approaches for multi-lingual data

In this example, we’re assuming a dataset consisting of 6 multi-lingual fields. Each field holds strings with an average length of 200 characters in the 4 locales en, de, zh and ru. A fallback function is implemented using the CASE expression (doesn’t add much overhead and is faster than fetching data in two locales and implementing the fallback at application level).

PHP Magazin: Parlez-vous Zend?

[[image php_mag.gif align=”right”]] Wer sich fuer **[http://phpmagazin.de/itr/online_artikel/psecom,id,916,nodeid,62,_language,de.html mehrsprachige Unternehmens-Anwendungen mit Zend Framework]** interessiert, findet im aktuellen [http://phpmagazin.de/ PHP Magazin] (5.07) einen Artikel von mir. Anhand eines Fallbeispiels wurde gezeigt, wie die Neuentwicklung einer PHP Anwendung mit modernen Technologien ablaufen kann und was es zu beachten gibt.

Fun with SQL

I had heaps of fun with SQL today (again). Ever heard of Common Table Expressions? Here is an example of a recursive query:


WITH
nodeCTE (nodeId, nodeName)
AS (
SELECT
a.nodeId,
nodeName = CONVERT(varchar(8000), nodeName)
FROM
nodeNames a JOIN nodes b ON a.nodeId = b.nodeId
AND a.locale = 'en' AND b.parentId = 0
UNION ALL SELECT y.nodeId,
x.nodeName + ' -> ' + CONVERT(varchar(8000),
z.nodeName)
FROM
nodeCTE x JOIN nodes y ON y.parentId = x.nodeId
JOIN nodeNames z ON z.nodeId = y.nodeId AND
z.locale = 'en'
)
SELECT * FROM nodeCTE;

Zend Framework: Unicode DB adapter for SQL Server

Yeah, I finally got it managed to publish my proposal for the Zend Framwork ODBTP database adapter component:

http://framework.zend.com/wiki/display/ZFPROP/Zend_Db_Adapter_Odbtp_Mssql

The default MS SQL database driver does not work with Unicode, as SQL Server expects Unicode strings in the UCS-2 encoding (UCS-2 contains null bytes and the driver is not binary-safe). ODBTP works as layer between PHP and SQL Server and accepts Unicode strings in the UTF-8 encoding.