Converting phpNuke to MS SQL Server

Darn, I just spent most of the day writing notes about converting phpNuke to MS SQL Server and now I’ve lost my notes.

Anyhow:

Keyword conflicts: especially with keyword TOP and VIEW– they are used as column names in phpNuke and have to be surrounded by [ ] both in the SQL intialization script and in the code.
Datatype conflicts: mediumint, unsigned, binary, and many others, had to be replaced.
Identity: mySQL allows you to insert a number in an autonumber/identity column, or insert a null to get the next number. T-SQL wants you to list all the column names or call SET IDENTITY_INSERT tablename ON and doesn’t seem to think it’s okay to use NULL as a place marker. This was a problem in the code and the sql initialization script.

PHP config- need to turn off the errors, make sure that PHP.exe can see the database extension
SQL Server Network Config– for some reason I had the wrong TCP IP port , should have been 1433.
The code tries to change LIMIT to TOP but the regular expression should test for [Ll][Ii][Mm][Ii][Tt] since the code isn’t consistent with capitals.

I finally gave up, the PHP code gets to a point where it just fails to sent SQL to the database and it doesn’t get anything back. This happens right at a priv’s check, so I’m left without priv’s.

Oh well, I did learn about SQL2000 some and now I’m going to try again with mySQL. PHPNuke should take out the part that says it works with other databases, or at lease mitigate the claims.

Objects and Rows, Classes and Tables

There is obviouly a close relationship between object oriented programming and relational databases.

An object is a table, roughly. A column is an object’s field, roughly.

An interface is a subset of columns that two or more tables have in common, such as Customer.FirstName, Customer.LastName vs. Employee.FirstName, Employee.LastName

For example, a has-a relationship is recorded in a database as a one-to-many table and in objects as a method that returns another object.

A one-to-one relationship (sometimes modeled with disjoint tables with similar columns) corresponds to inheritence, or the is-a relationship. In this case, one entity comes in many varieties. To avoid lots of blank columns in our database, we put different, entities in separate tables, or alternatively, the common attributes in a single table and the category specific attributes in a separate table.

Category codes roughly correspond to constants.

Can SQL be run in reverse?

Can SQL be reversed, say by looking at a trace of SQL and undoing each statement?

SELECT -> no operation
INSERT -> DELETE row
UPDATE -> no can do. We don’t know which subset was updated, and we don’t know what values used to be there.
UPDATE (assume 1 row, primary key and the value being updated is also with the WHERE clause) -> UPDATE using appropriate values in WHERE clause
CREATE TABLE -> DROP TABLE Works for similar DDL
DROP TABLE -> no can do. Don’t know what we dropped. Similar problem for other DDL

Seems like it would be easier to reverse engineer the transaction logs, since they have entire row before and after snapshots of each row that changes.