pg2tsql is a set of translation scripts written in perl that translates most openACS plpgsql and SQL-92 statements to the Microsoft SQL Server 2000's Transact SQL dialect. I've only tested it on the acs-kernel package, but I expect the results from this work to be extensible to the remaining application packages.

For the terminally curious, here's a background white paper I wrote on the project.

For the just-show-me-the-sql set, here's the sql output by the code generator

• all.sql - all sql statements combined into a single file.

• comparison a document comparing plpgsql and translated tsql. It also includes the error messages for statements which didn't validate when executed in isql.

The error messages contained in these files are produced by a validation routine that runs each statement against a SQL 7.0 test database using ADO.

Release Notes

Although I haven't officially released pg2tsql (as of 2/02), I thought I'd post a list of what you can expect to see when perusing the output:

• I haven't implemented any transaction handling or error handling semantics in the stored procedure.

• The overloaded functions are not fully debugged. For example, if an overloaded function is defined before the function it delegates to (to fill in missing args), it won't translate correctly. Since TSQL supports optional arguments, they should ideally go away but this would be a significant branching that would stymie future auto-porting efforts.

• Triggers are not supported. They'll have to be hand ported.

• Dynamic SQL is minimally supported. Since the parser can't 'look inside' the string concatenations to see what needs to be ported, it's not an easily solvable problem.

• I skipped the 'lobs' file because it's a lot of platform specific stuff and will likely need to be hand ported.

• Package xql files haven't been ported. It would be difficult to test transformation validity which request a processor port and a compiled ODBC driver, so I'm going to hold off.

February, 2002

John Sequeira - [email protected]