On 09/12/2011 11:00 AM, Jan Pazdziora wrote:
On Wed, Jul 27, 2011 at 03:47:39PM +0200, Ionuț Arțăriși wrote:
Adding a char to varchar2 definitions would then be step to, and piece
of cake.
Hm... writing another tool to do what chameleon currently does seems
like a massive undertaking.
Kudos to Michal Mráka who did just that.
More kudos from me as well!
The Spacewalk master/nightly build process is now chameleon-free, so
if you like, we can move forward with adding the CHAR declaration to
the schema definition -- I'll be happy to review a patch.
In the patch, you'll also need to amend spacewalk-oracle2postgresql to
remove the CHAR from the declaration as PostgreSQL does not support
it, and add schema upgrade scripts to modify existing installation.
I've attached the patches for the new regexp + migration and schema
change the way I've applied them in our repository.
Well, before working on the patch, we should probably decide if the
changes should only touch columns where you've already hit the
problem, or if you want to change all VARCHAR2 columns in the database.
We have multiple columns like LABEL where we only allow limited subset
of US-ASCII (typically a-zA-Z0-9_ or something similar), so there it
might actually be usefull to keep it BYTE (and explicitly declare it
as BYTE) and perhaps also add a CHECK constraint which will enforce
this limited subset on the column.
I wasn't aware of this specific case and since I don't know how many
other columns would be in this same situation I can't advocate changing
to CHAR for all of the VARCHAR2 columns. However I do think that this
specific constraint should only be a CHECK constraint and the storage
format should be irrelevant.
-Ionuț
>From a8f017ed753394d9fbd4a9cb2069f6cbb1294e60 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Ionu=C8=9B=20Ar=C8=9B=C4=83ri=C8=99i?= <iartar...@suse.cz>
Date: Thu, 22 Sep 2011 13:28:32 +0200
Subject: [PATCH 1/2] allow setting VARCHAR2 size in CHARs and BYTEs
explicitly
---
schema/spacewalk/spacewalk-oracle2postgresql | 3 ++-
1 files changed, 2 insertions(+), 1 deletions(-)
diff --git a/schema/spacewalk/spacewalk-oracle2postgresql b/schema/spacewalk/spacewalk-oracle2postgresql
index a11d6f4..eb0193e 100755
--- a/schema/spacewalk/spacewalk-oracle2postgresql
+++ b/schema/spacewalk/spacewalk-oracle2postgresql
@@ -1,10 +1,11 @@
#!/bin/sed -f
# data types
-s/\bVARCHAR2/VARCHAR/i;
s/\bDATE\b/TIMESTAMPTZ/i;
s/\bNUMBER\b/NUMERIC/i;
s/\bBLOB\b/BYTEA/i;
+# postgres doesn't have CHAR or BYTE
+s/\bVARCHAR2(\([0-9]\+\)[[:blank:]]*\(CHAR\|BYTE\)\?)/VARCHAR(\1)/i;
# functions
s/\bSYSDATE\b/CURRENT_TIMESTAMP/i;
--
1.7.6.1
>From 1d3466464e59da565f562816493764987532d521 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Ionu=C8=9B=20Ar=C8=9B=C4=83ri=C8=99i?= <iartar...@suse.cz>
Date: Thu, 22 Sep 2011 13:28:59 +0200
Subject: [PATCH 2/2] change rhnServerAction.result_msg column size from
default BYTEs to CHARs
---
schema/spacewalk/common/tables/rhnServerAction.sql | 2 +-
.../001-rhnServerAction-result_msg-size.sql | 3 +++
2 files changed, 4 insertions(+), 1 deletions(-)
create mode 100644 schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/001-rhnServerAction-result_msg-size.sql
diff --git a/schema/spacewalk/common/tables/rhnServerAction.sql b/schema/spacewalk/common/tables/rhnServerAction.sql
index 292c83e..8b15128 100644
--- a/schema/spacewalk/common/tables/rhnServerAction.sql
+++ b/schema/spacewalk/common/tables/rhnServerAction.sql
@@ -27,7 +27,7 @@ CREATE TABLE rhnServerAction
CONSTRAINT rhn_server_action_status_fk
REFERENCES rhnActionStatus (id),
result_code NUMBER,
- result_msg VARCHAR2(1024),
+ result_msg VARCHAR2(1024 CHAR),
pickup_time DATE,
remaining_tries NUMBER
DEFAULT (5) NOT NULL,
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/001-rhnServerAction-result_msg-size.sql b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/001-rhnServerAction-result_msg-size.sql
new file mode 100644
index 0000000..7b41ff3
--- /dev/null
+++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/001-rhnServerAction-result_msg-size.sql
@@ -0,0 +1,3 @@
+alter table rhnServerAction modify ( result_msg VARCHAR2(1024 CHAR) );
+
+commit;
--
1.7.6.1
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
