Re: from mssql7.0 and ASP to postgres and PHP

Luis,

> PS: I beg you pardon for my English.

So? I speak Spanish like a 4-year-old. Don't apologize!

> Im porting 3 sites developed in ASP and MSQSLserver 7.0 to Linux.
> We selected posgrest due mainly to views, triggers and so on.
> 1) How to import tables.?
> I did the scripting of my MS dbs and after that tryed to convert them
> to
> text and so, but when therre are lots of tables this system is really
> ugly. Maybe an ODBC solution?.

I'd recommend using Perl::DBD::Pg, on the command-line, for converting
your data. It can pull data directly from MSSQL using FreeTDS (see
www.freetds.org) and feed it to PostgreSQL.

For the table definitions, I would recommend doing them by hand,
painful as it is. There are too many small differences (like
auto-increment columns) between Postgres and MSSQL to make it any
easier to script DDL statements.

Alternately, I believe that the PGAdminII project
(pgadmin.postgresql.org) as an automatic database migrator, but that
you will need to do some hand-adjustment after it is migrated, as the
automatic script does not create perfect table defs.

Finally, see some of the docs at: http://techdocs.postgresql.org which
talk about porting from MSSQL to Postgres.

> 2) Is PHP the one and only best solution.?
> Maybe ASP::Perl is better, or Python or ....etc, really I dont know
> and
> I am lost in a jungle of docs.

<grin> I know how you feel. I do SQL, PL/pgSQL, Perl, shell scripts
and VB myself and sometimes can't decide what to use.

The lowdown, is there are 4 good solutions for server-side scripting:

PHP: popular, fast, and very easy to learn, especially for former ASP
programmers. Many, many code snippets and whole projects available
for download.

Python: more of a full programming language, and not nearly as many
resources available as PHP. However, it does have 2 advantages: a)
Zope, a terrific open source content-management tool, and b) Python can
also be used as a command-line programming language for Postgres
trhough PL/python.

Perl: somewhat harder to learn for someone from an MS programming
background, but Perl has thousands of online resources including whole
site-management projects like PHP. Also useful as a PostgreSQL
programming language trhough PL/Perl.

Java: The heavyweight. Not recommended for small sites ... Java is
usually the best choice for a large, distributed application but
carries too much programming overhead and too steep of a learning curve
for the single-database-and-web-server setup. On the other hand, Java
is the only language of the above that lets you run embedded programs
(applets) in your web application which run in user space and don't
require page reloads.

Hope that helps!

-Josh Berkus