Note to all who are porting the tables: Please remove any COMMIT commands you see in these sql files. I have encountered a few, like rhnActionStatus_data.sql where there's a COMMIT at the end of all the INSERTs. These commits are just going to mess-up the transaction we start in main.sql's.
Best regards, On Tue, Feb 3, 2009 at 12:47 PM, Gurjeet Singh < [email protected]> wrote: > 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 > > -- 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
