[jira] [Commented] (CONNECTORS-887) Database schema not updated properly

["Karl Wright (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CONNECTORS-887?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13896759#comment-13896759 ] 

Karl Wright commented on CONNECTORS-887:
----------------------------------------

Actually, the upgrade first adds the column, then modifies it after creating the corresponding authority group:

{code}
        cd = (ColumnDescription)existing.get(groupNameField);
        if (cd == null)
        {
          Map addMap = new HashMap();
          addMap.put(groupNameField,new ColumnDescription("VARCHAR(32)",false,true,
            authMgr.getTableName(),authMgr.getGroupNameColumn(),false));
          performAlter(addMap,null,null,null);
          boolean revert = true;
          try
          {
            ArrayList params = new ArrayList();
            IResultSet set = performQuery("SELECT "+nameField+","+descriptionField+" FROM "+getTableName(),null,null,null);
            for (int i = 0 ; i < set.getRowCount() ; i++)
            {
              IResultRow row = set.getRow(i);
              String authName = (String)row.getValue(nameField);
              String authDescription = (String)row.getValue(descriptionField);
              // Attempt to create a matching auth group.  This will fail if the group
              // already exists
              IAuthorityGroup grp = authMgr.create();
              grp.setName(authName);
              grp.setDescription(authDescription);
              try
              {
                authMgr.save(grp);
              }
              catch (ManifoldCFException e)
              {
                if (e.getErrorCode() == ManifoldCFException.INTERRUPTED)
                  throw e;
                // Fall through; the row exists already
              }
              Map<String,String> map = new HashMap<String,String>();
              map.put(groupNameField,authName);
              params.clear();
              String query = buildConjunctionClause(params,new ClauseDescription[]{
                new UnitaryClause(nameField,authName)});
              performUpdate(map," WHERE "+query,params,null);
            }
            Map modifyMap = new HashMap();
            modifyMap.put(groupNameField,new ColumnDescription("VARCHAR(32)",false,false,
              authMgr.getTableName(),authMgr.getGroupNameColumn(),false));
            performAlter(null,modifyMap,null,null);
            revert = false;
          }
          finally
          {
            if (revert)
            {
              // Upgrade failed; back out our changes
              List<String> deleteList = new ArrayList<String>();
              deleteList.add(groupNameField);
              performAlter(null,null,deleteList,null);
            }
          }
        }
{code}

I tried this on a PostgreSQL instance to be sure it worked; it did.  Is there anything unique about your setup that would prevent the above code from working in your case?


> Database schema not updated properly
> ------------------------------------
>
>                 Key: CONNECTORS-887
>                 URL: https://issues.apache.org/jira/browse/CONNECTORS-887
>             Project: ManifoldCF
>          Issue Type: Bug
>          Components: Framework core
>    Affects Versions: ManifoldCF 1.6
>         Environment: MySQL database
>            Reporter: Florian Schmedding
>            Priority: Minor
>              Labels: database, easyfix
>
> When running Manifold 1.6 the first time with an database schema from Manifold 1.3 the schema is not updated properly. The SQL-command 
> "ALTER TABLE authconnections MODIFY groupname VARCHAR(32) NOT NULL REFERENCES authgroups(groupname) ON DELETE RESTRICT"
> fails. It should instead add the column:
> "ALTER TABLE authconnections ADD groupname VARCHAR(32) NOT NULL REFERENCES authgroups(groupname) ON DELETE RESTRICT"
> The next startup after executing the corrected SQL-statement succeeds.



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)