RE: [uportal-dev] SQLServer uPortal 3.1 RC1 Tables.xml
Dear uPortal Developers, I've raised a JIRA (2351): http://www.ja-sig.org/issues/browse/UP-2351 Thanks, Conan Cook Senior Applications Developer Academic Technologies Group (ATG) University of the West of England Frenchay Campus Bristol BS16 1QY email: conan.c...@uwe.ac.uk telephone: 01173 281 053 -Original Message- From: bounce-6917425-15797...@lists.wisc.edu [mailto:bounce-6917425-15797...@lists.wisc.edu] On Behalf Of Conan Cook Sent: 10 March 2009 12:09 To: uportal-dev@lists.ja-sig.org Subject: [uportal-dev] SQLServer uPortal 3.1 RC1 Tables.xml Dear uPortal Developers, I've successfully installed uPortal 3.1 RC1 using MySQL and HSQL, but when I tried to run the initdb ANT target against an SQL Server database, it failed complaining that primary keys cannot be created on nullable columns (SQL Server can't handle primary keys being set to nullable columns). We fixed the issue by adding the appropriate not-null elements to the three tables.xml files immediately after all the primary-key elements using a simple find and replace. Conan Cook Senior Applications Developer Academic Technologies Group (ATG) University of the West of England Frenchay Campus Bristol BS16 1QY email: conan.c...@uwe.ac.uk telephone: 01173 281 053 This email was independently scanned for viruses by McAfee anti-virus software and none were found -- You are currently subscribed to uportal-dev@lists.ja-sig.org as: conan.c...@uwe.ac.uk To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/uportal-dev This incoming email to UWE has been independently scanned for viruses by McAfee anti-virus software and none were detected This email was independently scanned for viruses by McAfee anti-virus software and none were found -- You are currently subscribed to uportal-dev@lists.ja-sig.org as: arch...@mail-archive.com To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/uportal-dev
Re: [uportal-dev] SQLServer uPortal 3.1 RC1 Tables.xml
Thanks for the report Conan, this had already been found and fixed: http://www.ja-sig.org/issues/browse/UP-2313 Ther RC2 release announcement will be coming out shortly. -Eric Conan Cook wrote: Dear uPortal Developers, I've raised a JIRA (2351): http://www.ja-sig.org/issues/browse/UP-2351 Thanks, Conan Cook Senior Applications Developer Academic Technologies Group (ATG) University of the West of England Frenchay Campus Bristol BS16 1QY email: conan.c...@uwe.ac.uk telephone: 01173 281 053 -Original Message- From: bounce-6917425-15797...@lists.wisc.edu [mailto:bounce-6917425-15797...@lists.wisc.edu] On Behalf Of Conan Cook Sent: 10 March 2009 12:09 To: uportal-dev@lists.ja-sig.org Subject: [uportal-dev] SQLServer uPortal 3.1 RC1 Tables.xml Dear uPortal Developers, I've successfully installed uPortal 3.1 RC1 using MySQL and HSQL, but when I tried to run the initdb ANT target against an SQL Server database, it failed complaining that primary keys cannot be created on nullable columns (SQL Server can't handle primary keys being set to nullable columns). We fixed the issue by adding the appropriate not-null elements to the three tables.xml files immediately after all the primary-key elements using a simple find and replace. Conan Cook Senior Applications Developer Academic Technologies Group (ATG) University of the West of England Frenchay Campus Bristol BS16 1QY email: conan.c...@uwe.ac.uk telephone: 01173 281 053 This email was independently scanned for viruses by McAfee anti-virus software and none were found -- You are currently subscribed to uportal-dev@lists.ja-sig.org as: conan.c...@uwe.ac.uk To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/uportal-dev This incoming email to UWE has been independently scanned for viruses by McAfee anti-virus software and none were detected This email was independently scanned for viruses by McAfee anti-virus software and none were found smime.p7s Description: S/MIME Cryptographic Signature
Re:[uportal-dev] SQLServer uPortal 3.1 RC1 Tables.xml
While we're on the subject, I was curious if it might be useful to apply a primary key definition to the UP_PERMISSION table? I see that there is an index defined (UPPERM_IDX), but didn't know if there was a good reason for not defining a primary key. The only condition I can think of is if someone wanted to define a series of timed permissions via the EFFECTIVE/EXPIRES columns. However, if there is no reason, might we consider the following? 1) Add not-null to the following columns: OWNER PRINCIPAL_TYPE PRINCIPAL_KEY ACTIVITY TARGET PERMISSION_TYPE 2) Resize the following columns: (*** SEE NOTE BELOW ***) ACTIVITY - VARCHAR(64) PERMISSION_TYPE - VARCHAR(64) 3) Apply the following as the primary-key definition: OWNER PRINCIPAL_TYPE PRINCIPAL_KEY ACTIVITY TARGET PERMISSION_TYPE *** NOTE *** There is a size restriction of 900 bytes for the definition of a primary key in MS SQL Server. Though it allows for a primary key definition greater than 900 bytes, it will return the following warning: Warning! The maximum key length is 900 bytes. The index 'PK_UP_PERMISSION' has maximum length of 1279 bytes. For some combination of large values, the insert/update operation will fail. Since both the ACTIVITY and PERMISSION_TYPE values are typically smaller, could we safely resize them down to VARCHAR(64), which would bring the primary key size down to just under 900 bytes? I'm not sure if other databases have similar restrictions, so it might be worth a little research. If applying the primary key does not make sense, then does it at least make sense to apply the not-null restrictions? Are there real cases where NULL values should be allowed in the aforementioned columns? Thoughts? --Chris -- You are currently subscribed to uportal-dev@lists.ja-sig.org as: arch...@mail-archive.com To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/uportal-dev
Re: [uportal-dev] SQLServer uPortal 3.1 RC1 Tables.xml
The size is much lower for MySQL keys. MySQL (as I've been told) can only have 1000 bytes in a primary key. The harder part is with UTF-8 MySQL uses 3 bytes per character which means we would have to significantly reduce the column widths in the table to only fit about 333 characters total. Chris Doyle wrote: While we're on the subject, I was curious if it might be useful to apply a primary key definition to the UP_PERMISSION table? I see that there is an index defined (UPPERM_IDX), but didn't know if there was a good reason for not defining a primary key. The only condition I can think of is if someone wanted to define a series of timed permissions via the EFFECTIVE/EXPIRES columns. However, if there is no reason, might we consider the following? 1) Add not-null to the following columns: OWNER PRINCIPAL_TYPE PRINCIPAL_KEY ACTIVITY TARGET PERMISSION_TYPE 2) Resize the following columns: (*** SEE NOTE BELOW ***) ACTIVITY - VARCHAR(64) PERMISSION_TYPE - VARCHAR(64) 3) Apply the following as the primary-key definition: OWNER PRINCIPAL_TYPE PRINCIPAL_KEY ACTIVITY TARGET PERMISSION_TYPE *** NOTE *** There is a size restriction of 900 bytes for the definition of a primary key in MS SQL Server. Though it allows for a primary key definition greater than 900 bytes, it will return the following warning: Warning! The maximum key length is 900 bytes. The index 'PK_UP_PERMISSION' has maximum length of 1279 bytes. For some combination of large values, the insert/update operation will fail. Since both the ACTIVITY and PERMISSION_TYPE values are typically smaller, could we safely resize them down to VARCHAR(64), which would bring the primary key size down to just under 900 bytes? I'm not sure if other databases have similar restrictions, so it might be worth a little research. If applying the primary key does not make sense, then does it at least make sense to apply the not-null restrictions? Are there real cases where NULL values should be allowed in the aforementioned columns? Thoughts? --Chris smime.p7s Description: S/MIME Cryptographic Signature
