Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-20598

Adjust SAKAI_PERSON_T hibernate mapping for type="text" and larger size

    XMLWordPrintable

    Details

    • Type: Task
    • Status: CLOSED
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.7.1, 2.8.0
    • Fix Version/s: 2.8.1, 2.9.0
    • Component/s: Common
    • Labels:
      None
    • Test Plan:
      Hide

      To test:

      Firstly, load up your current trunk ,whatever revision it is, and go into the Profile tool and fill in some fields. Maybe do this for a few different users.

      Now, cd to core-deploy and run
      mvn clean install sakai:deploy -U

      This will redeploy the overlays and force an update from the repo. The common project is the one we are after.

      Then run the appropriate DB conversion as below. It should convert over the data to the new fields.

      Start Tomcat, load up Profile and check the data is there.

      – Oracle upgrade SQL:
      alter table SAKAI_PERSON_T add (TMP_NOTES clob);
      alter table SAKAI_PERSON_T add (TMP_FAVOURITE_BOOKS clob);
      alter table SAKAI_PERSON_T add (TMP_FAVOURITE_TV_SHOWS clob);
      alter table SAKAI_PERSON_T add (TMP_FAVOURITE_MOVIES clob);
      alter table SAKAI_PERSON_T add (TMP_FAVOURITE_QUOTES clob);
      alter table SAKAI_PERSON_T add (TMP_EDUCATION_COURSE clob);
      alter table SAKAI_PERSON_T add (TMP_EDUCATION_SUBJECTS clob);
      alter table SAKAI_PERSON_T add (TMP_STAFF_PROFILE clob);
      alter table SAKAI_PERSON_T add (TMP_UNIVERSITY_PROFILE_URL clob);
      alter table SAKAI_PERSON_T add (TMP_ACADEMIC_PROFILE_URL clob);
      alter table SAKAI_PERSON_T add (TMP_PUBLICATIONS clob);
      alter table SAKAI_PERSON_T add (TMP_BUSINESS_BIOGRAPHY clob);

      update SAKAI_PERSON_T set TMP_NOTES = NOTES;
      update SAKAI_PERSON_T set TMP_FAVOURITE_BOOKS = FAVOURITE_BOOKS;
      update SAKAI_PERSON_T set TMP_FAVOURITE_TV_SHOWS = FAVOURITE_TV_SHOWS;
      update SAKAI_PERSON_T set TMP_FAVOURITE_MOVIES = FAVOURITE_MOVIES;
      update SAKAI_PERSON_T set TMP_FAVOURITE_QUOTES = FAVOURITE_QUOTES;
      update SAKAI_PERSON_T set TMP_EDUCATION_COURSE = EDUCATION_COURSE;
      update SAKAI_PERSON_T set TMP_EDUCATION_SUBJECTS = EDUCATION_SUBJECTS;
      update SAKAI_PERSON_T set TMP_STAFF_PROFILE = STAFF_PROFILE;
      update SAKAI_PERSON_T set TMP_UNIVERSITY_PROFILE_URL = UNIVERSITY_PROFILE_URL;
      update SAKAI_PERSON_T set TMP_ACADEMIC_PROFILE_URL = ACADEMIC_PROFILE_URL;
      update SAKAI_PERSON_T set TMP_PUBLICATIONS = PUBLICATIONS;
      update SAKAI_PERSON_T set TMP_BUSINESS_BIOGRAPHY = BUSINESS_BIOGRAPHY;

      alter table SAKAI_PERSON_T drop column NOTES;
      alter table SAKAI_PERSON_T drop column FAVOURITE_BOOKS;
      alter table SAKAI_PERSON_T drop column FAVOURITE_TV_SHOWS;
      alter table SAKAI_PERSON_T drop column FAVOURITE_MOVIES;
      alter table SAKAI_PERSON_T drop column FAVOURITE_QUOTES;
      alter table SAKAI_PERSON_T drop column EDUCATION_COURSE;
      alter table SAKAI_PERSON_T drop column EDUCATION_SUBJECTS;
      alter table SAKAI_PERSON_T drop column STAFF_PROFILE;
      alter table SAKAI_PERSON_T drop column UNIVERSITY_PROFILE_URL;
      alter table SAKAI_PERSON_T drop column ACADEMIC_PROFILE_URL;
      alter table SAKAI_PERSON_T drop column PUBLICATIONS;
      alter table SAKAI_PERSON_T drop column BUSINESS_BIOGRAPHY;

      alter table SAKAI_PERSON_T rename column TMP_NOTES to NOTES;
      alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_BOOKS to FAVOURITE_BOOKS;
      alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_TV_SHOWS to FAVOURITE_TV_SHOWS;
      alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_MOVIES to FAVOURITE_MOVIES;
      alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_QUOTES to FAVOURITE_QUOTES;
      alter table SAKAI_PERSON_T rename column TMP_EDUCATION_COURSE to EDUCATION_COURSE;
      alter table SAKAI_PERSON_T rename column TMP_EDUCATION_SUBJECTS to EDUCATION_SUBJECTS;
      alter table SAKAI_PERSON_T rename column TMP_STAFF_PROFILE to STAFF_PROFILE;
      alter table SAKAI_PERSON_T rename column TMP_UNIVERSITY_PROFILE_URL to UNIVERSITY_PROFILE_URL;
      alter table SAKAI_PERSON_T rename column TMP_ACADEMIC_PROFILE_URL to ACADEMIC_PROFILE_URL;
      alter table SAKAI_PERSON_T rename column TMP_PUBLICATIONS to PUBLICATIONS;
      alter table SAKAI_PERSON_T rename column TMP_BUSINESS_BIOGRAPHY to BUSINESS_BIOGRAPHY;

      – MySQL upgrade SQL (verified)
      alter table SAKAI_PERSON_T change NOTES NOTES mediumtext null;
      alter table SAKAI_PERSON_T change FAVOURITE_BOOKS FAVOURITE_BOOKS mediumtext null;
      alter table SAKAI_PERSON_T change FAVOURITE_TV_SHOWS FAVOURITE_TV_SHOWS mediumtext null;
      alter table SAKAI_PERSON_T change FAVOURITE_MOVIES FAVOURITE_MOVIES mediumtext null;
      alter table SAKAI_PERSON_T change FAVOURITE_QUOTES FAVOURITE_QUOTES mediumtext null;
      alter table SAKAI_PERSON_T change EDUCATION_COURSE EDUCATION_COURSE mediumtext null;
      alter table SAKAI_PERSON_T change EDUCATION_SUBJECTS EDUCATION_SUBJECTS mediumtext null;
      alter table SAKAI_PERSON_T change STAFF_PROFILE STAFF_PROFILE mediumtext null;
      alter table SAKAI_PERSON_T change UNIVERSITY_PROFILE_URL UNIVERSITY_PROFILE_URL mediumtext null;
      alter table SAKAI_PERSON_T change ACADEMIC_PROFILE_URL ACADEMIC_PROFILE_URL mediumtext null;
      alter table SAKAI_PERSON_T change PUBLICATIONS PUBLICATIONS mediumtext null;
      alter table SAKAI_PERSON_T change BUSINESS_BIOGRAPHY BUSINESS_BIOGRAPHY mediumtext null;

      Show
      To test: Firstly, load up your current trunk ,whatever revision it is, and go into the Profile tool and fill in some fields. Maybe do this for a few different users. Now, cd to core-deploy and run mvn clean install sakai:deploy -U This will redeploy the overlays and force an update from the repo. The common project is the one we are after. Then run the appropriate DB conversion as below. It should convert over the data to the new fields. Start Tomcat, load up Profile and check the data is there. – Oracle upgrade SQL: alter table SAKAI_PERSON_T add (TMP_NOTES clob); alter table SAKAI_PERSON_T add (TMP_FAVOURITE_BOOKS clob); alter table SAKAI_PERSON_T add (TMP_FAVOURITE_TV_SHOWS clob); alter table SAKAI_PERSON_T add (TMP_FAVOURITE_MOVIES clob); alter table SAKAI_PERSON_T add (TMP_FAVOURITE_QUOTES clob); alter table SAKAI_PERSON_T add (TMP_EDUCATION_COURSE clob); alter table SAKAI_PERSON_T add (TMP_EDUCATION_SUBJECTS clob); alter table SAKAI_PERSON_T add (TMP_STAFF_PROFILE clob); alter table SAKAI_PERSON_T add (TMP_UNIVERSITY_PROFILE_URL clob); alter table SAKAI_PERSON_T add (TMP_ACADEMIC_PROFILE_URL clob); alter table SAKAI_PERSON_T add (TMP_PUBLICATIONS clob); alter table SAKAI_PERSON_T add (TMP_BUSINESS_BIOGRAPHY clob); update SAKAI_PERSON_T set TMP_NOTES = NOTES; update SAKAI_PERSON_T set TMP_FAVOURITE_BOOKS = FAVOURITE_BOOKS; update SAKAI_PERSON_T set TMP_FAVOURITE_TV_SHOWS = FAVOURITE_TV_SHOWS; update SAKAI_PERSON_T set TMP_FAVOURITE_MOVIES = FAVOURITE_MOVIES; update SAKAI_PERSON_T set TMP_FAVOURITE_QUOTES = FAVOURITE_QUOTES; update SAKAI_PERSON_T set TMP_EDUCATION_COURSE = EDUCATION_COURSE; update SAKAI_PERSON_T set TMP_EDUCATION_SUBJECTS = EDUCATION_SUBJECTS; update SAKAI_PERSON_T set TMP_STAFF_PROFILE = STAFF_PROFILE; update SAKAI_PERSON_T set TMP_UNIVERSITY_PROFILE_URL = UNIVERSITY_PROFILE_URL; update SAKAI_PERSON_T set TMP_ACADEMIC_PROFILE_URL = ACADEMIC_PROFILE_URL; update SAKAI_PERSON_T set TMP_PUBLICATIONS = PUBLICATIONS; update SAKAI_PERSON_T set TMP_BUSINESS_BIOGRAPHY = BUSINESS_BIOGRAPHY; alter table SAKAI_PERSON_T drop column NOTES; alter table SAKAI_PERSON_T drop column FAVOURITE_BOOKS; alter table SAKAI_PERSON_T drop column FAVOURITE_TV_SHOWS; alter table SAKAI_PERSON_T drop column FAVOURITE_MOVIES; alter table SAKAI_PERSON_T drop column FAVOURITE_QUOTES; alter table SAKAI_PERSON_T drop column EDUCATION_COURSE; alter table SAKAI_PERSON_T drop column EDUCATION_SUBJECTS; alter table SAKAI_PERSON_T drop column STAFF_PROFILE; alter table SAKAI_PERSON_T drop column UNIVERSITY_PROFILE_URL; alter table SAKAI_PERSON_T drop column ACADEMIC_PROFILE_URL; alter table SAKAI_PERSON_T drop column PUBLICATIONS; alter table SAKAI_PERSON_T drop column BUSINESS_BIOGRAPHY; alter table SAKAI_PERSON_T rename column TMP_NOTES to NOTES; alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_BOOKS to FAVOURITE_BOOKS; alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_TV_SHOWS to FAVOURITE_TV_SHOWS; alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_MOVIES to FAVOURITE_MOVIES; alter table SAKAI_PERSON_T rename column TMP_FAVOURITE_QUOTES to FAVOURITE_QUOTES; alter table SAKAI_PERSON_T rename column TMP_EDUCATION_COURSE to EDUCATION_COURSE; alter table SAKAI_PERSON_T rename column TMP_EDUCATION_SUBJECTS to EDUCATION_SUBJECTS; alter table SAKAI_PERSON_T rename column TMP_STAFF_PROFILE to STAFF_PROFILE; alter table SAKAI_PERSON_T rename column TMP_UNIVERSITY_PROFILE_URL to UNIVERSITY_PROFILE_URL; alter table SAKAI_PERSON_T rename column TMP_ACADEMIC_PROFILE_URL to ACADEMIC_PROFILE_URL; alter table SAKAI_PERSON_T rename column TMP_PUBLICATIONS to PUBLICATIONS; alter table SAKAI_PERSON_T rename column TMP_BUSINESS_BIOGRAPHY to BUSINESS_BIOGRAPHY; – MySQL upgrade SQL (verified) alter table SAKAI_PERSON_T change NOTES NOTES mediumtext null; alter table SAKAI_PERSON_T change FAVOURITE_BOOKS FAVOURITE_BOOKS mediumtext null; alter table SAKAI_PERSON_T change FAVOURITE_TV_SHOWS FAVOURITE_TV_SHOWS mediumtext null; alter table SAKAI_PERSON_T change FAVOURITE_MOVIES FAVOURITE_MOVIES mediumtext null; alter table SAKAI_PERSON_T change FAVOURITE_QUOTES FAVOURITE_QUOTES mediumtext null; alter table SAKAI_PERSON_T change EDUCATION_COURSE EDUCATION_COURSE mediumtext null; alter table SAKAI_PERSON_T change EDUCATION_SUBJECTS EDUCATION_SUBJECTS mediumtext null; alter table SAKAI_PERSON_T change STAFF_PROFILE STAFF_PROFILE mediumtext null; alter table SAKAI_PERSON_T change UNIVERSITY_PROFILE_URL UNIVERSITY_PROFILE_URL mediumtext null; alter table SAKAI_PERSON_T change ACADEMIC_PROFILE_URL ACADEMIC_PROFILE_URL mediumtext null; alter table SAKAI_PERSON_T change PUBLICATIONS PUBLICATIONS mediumtext null; alter table SAKAI_PERSON_T change BUSINESS_BIOGRAPHY BUSINESS_BIOGRAPHY mediumtext null;
    • Conversion Script Required:
      Yes

      Description

      From recent discussions, the SAKAI_PERSON_T table is not created on MySQL5InnoDBDialect.
      Hibernate is not setting the column types as text, even though:
      <property name="notes" column="NOTES" type="string" length="4000" />

      It was proposed that the type be changed to text and length increased to 75000. This will force a mediumtext on MySQL and clob on Oracle. From the list:

      On 19/05/2011, at 12:11 PM, John Bush wrote:
      increasing the length while using type of string breaks oracle, increasing length while using type of text works universally. current settings break mysql when using the innodb5 dialect. im suggesting type=text length=75000 or more, which requires oracle start using clobs but works for mysql all dialects, db2 , and oracle

      Need some investigation into the feasibility of using clobs directly. And also a conversion for both MySQL and Oracle.

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  steve.swinsburg Steve Swinsburg
                  Reporter:
                  steve.swinsburg Steve Swinsburg
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration