Hi All,
I have migrated the following DB objects to work okay with Postgres:
./procs/create_first_org.sql
./tables/web_customer.sql
./tables/rhnUserGroupType.sql
./tables/rhnUserGroup.sql
./tables/rhnOrgQuota.sql
./tables/rhnUserGroup_sequences.sql
./tables/rhnServerGroupType.sql
./tables/rhnUserGroupType_data.sql
./tables/rhnServerGroup.sql
./triggers/rhnOrgQuota.sql
And follwing are the two new files:
./main.sql
./tables/dual.sql
Here I will list out the instructions and lessons learnt so that any of
you who wishes to contribute to this effort can start submitting patches.
Look at the above objects for refernce, anddo not hesitate to ask questions,
here or on IRC.
Create a local branch off of pgsql branch, this is where all the fun is.
Copy the files you want to work on from
schema/spacewalk/rhnsat/<objecttype>/fileame.sql to
schema/spacewalk/postgresql/<objecttype>/fileame.sql and then start working
on it.
We need to migrate objects in the following order of prefernce:
Tables, Triggers, Sequences, Views, Procedures.
We are leaving out the Objects, Packages, and Synonyms for now for various
reasons. If you feel comfortable doing these, you are welcome (synonyms are
not portable, so don;t bother with those).
=== Compacting DDL ===
While migrating these objects, we wish to compact the DDL to the point where
it looks sane and does not look like a bloat. For example,
1) Remove the constraint name from NOT NULL constraints. But keep the
Foreign Key, Primary Key and CHECK constraint names.
2) Try to club together the various declarations for the same column. For
example, there a re a few table DDLs where a column is declared NOT NULL and
(either in CREATE statememt, or using ALTER TABLE), a Primary Key is also
specified on the same. In such a case, get rid of the NOT NULL, and put the
PRIMARY KEY clause right next to the column.
3) If there's a multi-column primary key, specify that in the CREATE TABLE
command itself, instead of ALTER TABLE ADD CONSTRAINT...
4) If you see a VARCHAR(1) or a CHAR(1) column datatype, and if you think
the application might be using it as a boolean (Y/N), then just put a TODO
item on top of that column saying "TODO: Should this be a boolean?".
5) Convert all NUMBER datatypes to NUMERIC. Soon we will be identifying the
columns whose precision can be reduced to use just INT/BIGINT, because
Postgres' NUMERIC datatype does not perform all that well.
6) Convert all DATE datatype columns to TIMESTAMP.
7) Replace SYSDATE usage (in DEFAULTs etc. ) with CURRENT_TIMESTAMP.
8) Remember to keep the TABLESPACE clauses when you move a UNIQUE
INDEX/PRIMARY KEY from outside to inside the CREATE TABLE.
9) Sometimes there are TRIGGERS created in the same file as the table's. We
need to strip out those and create a file by the same name in triggers/
directory and create the trigger there.
10) Convert Oracle's sequence_name.nextval as nextval( 'sequence_name' ).
Same applies for curval and setval too.
11) Keep an eye open for possible caompatbility issues, you'll learn a lot.
If you find any, please make a note here, or document them in
PostgresWorklog (https://fedorahosted.org/spacewalk/wiki/PostgresWorklog)
and mark them OPEN if needed.
When done with porting an object, add that file to the main.sql and recreate
the scema as mentioned in README. You might be required to re-order a few
line isn main.sql to resolve dependency conflicts.
When you start porting, please let the list know hwere, so that we can
coordinate our efforts.
Any help provided is much appreciated.
Best regards,
--
gurjeet[[email protected]
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
