Jan Pazdziora wrote:
On Thu, Jan 15, 2009 at 03:36:55PM +0530, Gurjeet Singh wrote:
I am attaching the text file here. Lets discuss the issues here, and
finalize them, before going ahead with the actual modifications.
Thank you for the writeup. Here are my observations.
== Open questions relevant to porting efforts ==
1. Which versions of Oracle are to be supported by Spacewalk?
10g and later.
2. Which versions of Postgres are to be supported by Spacewalk?
8.1.11 (the one in RHEL 5) and later.
Yes, this was confirmed by Satellite Roadmap alignment.
It has been mentioned that there are advantages to using 8.2. Are there
differences from a porting standpoint? Are there features that would
make the porting easier/different? Can you summarize the differences
between 8.1 and 8.1 as it applies to the spacewalk schema/queries and
our porting effort?
6. Oracle treats {quote}{quote} (empty string) as null, but PG does not.
So either make the application aware of it, or attach a BEFORE EACH ROW
trigger on every table which has a char/varchar column, and set 'if
new.char_col = {quote}{quote}then new.char_col = null'.
Triggers will not help in cases where app uses results of dynamically computed
strings.
occurrences in schema: 17; find ./ -name *.sql | xargs grep \'\' | grep -iv
values | grep -v rhnFAQ_satdata.sql
Wouldn't it be better to create check that no varchar column in
PostgreSQL has value ''? That way both the application would not be
able to insert those.
Yeah, a trigger to correct this seems too heavy handed.
After running the above find, it seems that most of these (17) really
don't seem to be affected by different handling of ('') by oracle and pg.
1) ./views/rhnChannelTreeView.sql: '' || c.name
padded_name,
2) ./views/rhnUserChannelTreeView.sql: '' || c.name
padded_name,
3) ./views/rhnHistoryView.sql: '') ||
4) ./views/rhnHistoryView.sql: '' details,
5) ./views/rhnHistoryView.sql: '') ||
6) ./views/rhnHistoryView.sql: '' details,
7) ./views/rhnHistoryView.sql: '') ||
8) ./views/rhnHistoryView.sql: '' details,
9) ./views/rhnSharedChannelTreeView.sql:''||C.NAME AS PADDED_NAME,
10) ./views/rhnOrgChannelTreeView.sql: '' || c.name
padded_name,
(1-10) - contrived values and would be the same regardless of which DB,
right?
11) ./views/rhnActionOverview.sql:(SELECT COUNT(*) FROM
rhnServerAction WHERE action_id = A.id AND status = 2), -- XXX: don''t
hard code status here :)
13) /tables/rhnChannel_triggers.sql: -- this is a really bad way
of saying if all we''re
14) ./tables/rhnPackage.sql:--
possible'' easier
(11-14) - This is just in a comment, doesn't matter.
15) ./procs/label_join.sql: ret := '';
16) ./procs/name_join.sql: ret := '';
17) ./procs/id_join.sql:ret := '';
18) ./procs/channel_name_join.sql: ret := '';
May need to look into these further but doesn't seem to be affected by
pg having different handling of ''.
7. Convert DECODE calls to CASE expressions.
Although Orafce provides DECODE support, suggestion here is again to modify
the Oracle code to use CASE expressions for the reason mentioned in point 2.
-1. If Orafce supports this, let's not do more changes than necessary.
+1, let's not do more changes than necessary.
11. ROWID is not supported by Postgres.
Fortunately, ROWID keyword is used in very few places across the code, so it
can be easily ported to Postgres by using the result of expression
'tableoid::varchar || ctid::varchar' as a row identifier. Better still, using a
Primary or Unique Key instead is recommended.
+1 for the primary / unique keys instead of using rowid.
+1.
12. ROWNUM is not supported by Postgres.
But LIMIT ... OFFSET clause can be used to achieve the same result.
That however will not work on Oracle. How do you propose handling
these situations where no common syntax exists?
13. Subquery in SET clause of UPDATE command which updates multiple columns is
not supported in Postgres.
Syntax supported:
{{{
UPDATE tbl SET col = (SELECT col from tbl2);
}}}
Syntax not supported:
{{{
UPDATE tbl SET ( col1, col2 ) = (SELECT col1, col2 from tbl2);
}}}
How many occurences of these do we have in the schema?
14. TODO : Mention port from sysdate to CURRENT_TIMESTAMP, and research their
differences (formatti etc.).
Well, sysdate and current_timestamp are completely different -- they
are of different types. Could you elaborate on the port from
suggestion? Do you want to remove the use of sysdate in the Oracle
code? Can't we just emulate sysdate on PostgreSQL?
The plan is to port the queries to be more ANSI SQL compliant so they
will work on both databases. We don't want to have a separate set of
queries for each database. However, for queries that can't be modified
to work for either, we'll need to have a one off but we want these to
be kept to a
