[RUNTIME-3169] All SQL requests on Login and Population_id should be case sensitive

Type: Bug
Resolution: Fixed
Priority: Major
Fix Version/s: 4.1.15, 4.2.1, 4.3.0
Affects Version/s: None
Component/s: None
Labels:
None

Sprint:
4.2.1

by default a varchar column is insensitive at least in mysql

- - Sort By Name
  - Sort By Date
  - Ascending
  - Descending
  - Thumbnails
  - List
  - Download All

sql.jcr
28/May/19 17:35
18 kB
Raphaël Franchet

Bloque

RUNTIME-3093 Uppercase login in a group

Closed

Est composé de

CMS-9862 The SQL temp table for password has a wrong primary key

Closed

RUNTIME-3170 In SQL tables, the login column should be harmonized

Closed

Raphaël Franchet added a comment - 14/May/19 17:19 - edited

Derby, hsqldb, oracle and postgres are case insensitive by default

Raphaël Franchet added a comment - 14/May/19 17:19 - edited Derby, hsqldb, oracle and postgres are case insensitive by default

Raphaël Franchet added a comment - 14/May/19 15:54 - edited

Migration (derby,mysql,hsqldb, oracle and postgres OK)

	var Config = Java.type("org.ametys.runtime.config.Config");
  	var SQLDataSourceManager = serviceManager.lookup("org.ametys.core.datasource.SQLDataSourceManager");
	var UserPopulationDAO = serviceManager.lookup("org.ametys.core.user.population.UserPopulationDAO");
	var GroupDirectoryDAO = serviceManager.lookup("org.ametys.core.group.GroupDirectoryDAO");
  
  	updateRightsTables();
  	updateAuhenticationTokenTables();
  	updateUserPreferencesTables();
  	updateUserSignupTables();
  	updateSurveyTables();
	updateUserPopulations();
	updateGroupDirectories();

	function updateGroupDirectories()
	{
		print("updateGroupDirectories");
		
		var groupDirectories = GroupDirectoryDAO.getGroupDirectories();
		for (var i = 0; i < groupDirectories.size(); i++)
		{
			var groupDirectory = groupDirectories.get(i);
			_updateGroupDirectory(groupDirectory);
		}
	}
	
	function _updateGroupDirectory(groupDirectory)
	{
		print("* groupDirectory " + groupDirectory.getId() + " *");
		if (groupDirectory.getGroupDirectoryModelId() == "org.ametys.plugins.core.group.directory.Jdbc")
		{
			var datasourceId = groupDirectory.getParameterValues().get("runtime.groups.jdbc.datasource");
			var tableName = groupDirectory.getParameterValues().get("runtime.groups.jdbc.composition.table");
	    	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
			var requests = [];
		
			switch (dbType)
			{
			  case "derby":
					requests.push("ALTER TABLE " + tableName + " ADD COLUMN Login2 varchar(64) NOT NULL DEFAULT 'nonnull'");
					requests.push("UPDATE " + tableName + " SET Login2=Login");
					requests.push("ALTER TABLE " + tableName + " DROP COLUMN Login");
					requests.push("RENAME COLUMN " + tableName + ".Login2 TO Login");
					requests.push("ALTER TABLE " + tableName + " ADD PRIMARY KEY (Group_Id, Login, UserPopulation_Id)");
					break;
			  case "mysql":
					requests.push("ALTER TABLE " + tableName + " MODIFY Login VARCHAR(64) BINARY");
					requests.push("ALTER TABLE " + tableName + " MODIFY UserPopulation_Id VARCHAR(200) BINARY");
					break;
			  case "oracle":
					requests.push("ALTER TABLE " + tableName + " MODIFY Login VARCHAR(64)");
					break;
			  case "postgresql":
					requests.push("ALTER TABLE " + tableName + " ALTER COLUMN Login TYPE VARCHAR(64)");
					break;
			  case "hsqldb":
					requests.push("ALTER TABLE " + tableName + " ALTER COLUMN Login VARCHAR(64)");
					break;
			  default:
					throw new Error("Unknown database type " + dbType)
			}
			
			_execute("_updateGroupDirectory", dbType, requests, datasourceId);
		}
	}

	function updateUserPopulations()
	{
		print("updateUserPopulations");
	
		var userPopulations = UserPopulationDAO.getUserPopulations(true);
		for (var i = 0; i < userPopulations.size(); i++)
		{
			var userPopulation = userPopulations.get(i);
			_updateUserPopulation(userPopulation);
		}
	}

	function _updateUserPopulation(userPopulation)
	{
		print("* userPopulation " + userPopulation.getId() + " *");

		var userDirectories = userPopulation.getUserDirectories();
		for (var i = 0; i < userDirectories.size(); i++)
		{
			var userDirectory = userDirectories.get(i);
			_updateUserDirectory(userPopulation, i, userDirectory);
		}

		var credentialProviders = userPopulation.getCredentialProviders();
		for (var i = 0; i < credentialProviders.size(); i++)
		{
			var credentialProvider = credentialProviders.get(i);
			_updateCredentialProvider(userPopulation, i, credentialProvider);
		}
	}
	
	function _updateUserDirectory(userPopulation, index, userDirectory)
	{
		print("** userDirectory[" + index + "] " + userDirectory.getId() + " " + userDirectory.getUserDirectoryModelId() + " *");
		if (userDirectory.getUserDirectoryModelId() == "org.ametys.plugins.core.user.directory.Jdbc")
		{			
			var datasourceId = userDirectory.getParameterValues().get("runtime.users.jdbc.datasource");
			var tableName = userDirectory.getParameterValues().get("runtime.users.jdbc.table");
	    	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
			var requests = [];
			
			switch (dbType)
			{
			  case "derby":
					// Nothing to do
					break;
			  case "mysql":
					requests.push("ALTER TABLE " + tableName + " MODIFY login varchar(64) BINARY");
					break;
			  case "oracle":
					// Nothing to do
					break;
			  case "postgresql":
					// Nothing to do
					break;
			  case "hsqldb":
					// Nothing to do
					break;
			  default:
					throw new Error("Unknown database type " + dbType)
			}
			
			_execute("_updateUserDirectory", dbType, requests, datasourceId);
		}
	}

	function _updateCredentialProvider(userPopulation, index, credentialProvider)
	{
		print("** credentialProvider[" + index + "] " + credentialProvider.getId() + " " + credentialProvider.getCredentialProviderModelId() + " *");
		if (credentialProvider.getCredentialProviderModelId() == "org.ametys.core.authentication.FormBased"
			&& credentialProvider.getParameterValues().get("runtime.authentication.form.captcha") == true)
		{			
			var datasourceId = credentialProvider.getParameterValues().get("runtime.authentication.form.security.storage");
	    	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
			var requests = [];
			
			switch (dbType)
			{
			  case "derby":
					requests.push("ALTER TABLE Users_FormConnectionFailed ADD COLUMN login2 varchar(64) NOT NULL DEFAULT 'nonnull'");
					requests.push("UPDATE Users_FormConnectionFailed SET login2=login");
					requests.push("ALTER TABLE Users_FormConnectionFailed DROP COLUMN login");
					requests.push("RENAME COLUMN Users_FormConnectionFailed.login2 TO login");
					requests.push("ALTER TABLE Users_FormConnectionFailed ADD PRIMARY KEY(login)");
					break;
			  case "mysql":
					requests.push("ALTER TABLE Users_FormConnectionFailed MODIFY login VARCHAR(64) BINARY");
					requests.push("ALTER TABLE Users_FormConnectionFailed MODIFY population_id VARCHAR(200) BINARY");
					break;
			  case "oracle":
					requests.push("ALTER TABLE Users_FormConnectionFailed MODIFY (login VARCHAR(64))");
					break;
			  case "postgresql":
					requests.push("ALTER TABLE Users_FormConnectionFailed ALTER COLUMN login TYPE VARCHAR(64)");
					break;
			  case "hsqldb":
					requests.push("ALTER TABLE Users_FormConnectionFailed ALTER COLUMN login VARCHAR(64)");
					break;
			  default:
					throw new Error("Unknown database type " + dbType)
			}
			
			_execute("_updateCredentialProvider", dbType, requests, datasourceId);
		}
	}

  	function updateUserSignupTables()
  	{
  	 	var datasourceId = Config.getInstance().getValue("web.usersignup.datasource");
		if (datasourceId == null)
		{
			print("...No web signup plugin...");
			return;
		}
      	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
		var requests = [];
		
      	switch (dbType)
        {
          case "derby":
				requests.push("ALTER TABLE Users_Temp ADD COLUMN userDirectory2 varchar(200) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Users_Temp SET userDirectory2=userDirectory");
				requests.push("ALTER TABLE Users_Temp DROP COLUMN userDirectory");
				requests.push("RENAME COLUMN Users_Temp.userDirectory2 TO userDirectory");
				requests.push("ALTER TABLE Users_Temp ADD PRIMARY KEY(userDirectory)");

				requests.push("ALTER TABLE Users_Temp ADD COLUMN population2 varchar(200) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Users_Temp SET population2=population");
				requests.push("ALTER TABLE Users_Temp DROP COLUMN population");
				requests.push("RENAME COLUMN Users_Temp.population2 TO population");
				requests.push("ALTER TABLE Users_Temp ADD PRIMARY KEY(population)");

				requests.push("ALTER TABLE Users_PasswordChange ADD COLUMN population2 varchar(200) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Users_PasswordChange SET population2=population");
				requests.push("ALTER TABLE Users_PasswordChange DROP COLUMN population");
				requests.push("RENAME COLUMN Users_PasswordChange.population2 TO population");

				requests.push("ALTER TABLE Users_PasswordChange ADD COLUMN login2 varchar(64) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Users_PasswordChange SET login2=login");
				requests.push("ALTER TABLE Users_PasswordChange DROP COLUMN login");
				requests.push("RENAME COLUMN Users_PasswordChange.login2 TO login");
				requests.push("ALTER TABLE Users_PasswordChange ADD PRIMARY KEY(site, login, population)");
            	break;
          case "mysql":
				requests.push("ALTER TABLE Users_Temp MODIFY userDirectory VARCHAR(200) BINARY");
				requests.push("ALTER TABLE Users_Temp MODIFY population VARCHAR(200) BINARY");

				requests.push("ALTER TABLE Users_PasswordChange MODIFY login VARCHAR(64) BINARY");
				requests.push("ALTER TABLE Users_PasswordChange MODIFY population VARCHAR(200) BINARY");
				requests.push("ALTER TABLE Users_PasswordChange DROP PRIMARY KEY, ADD PRIMARY KEY(site, login, population)");
            	break;
          case "oracle":
				requests.push("ALTER TABLE Users_Temp MODIFY (userDirectory VARCHAR(200))");
				requests.push("ALTER TABLE Users_Temp MODIFY (population VARCHAR(200))");

				requests.push("ALTER TABLE Users_PasswordChange MODIFY (login VARCHAR(64))");
				requests.push("ALTER TABLE Users_PasswordChange MODIFY (population VARCHAR(200))");
				
				requests.push("ALTER TABLE Users_PasswordChange DROP CONSTRAINT pk_users_passwordchange");
				requests.push("ALTER TABLE Users_PasswordChange ADD CONSTRAINT pk_users_passwordchange PRIMARY KEY (site, login, population)");
            	break;
          case "postgresql":
				requests.push("ALTER TABLE Users_Temp ALTER COLUMN userDirectory TYPE VARCHAR(200)");
				requests.push("ALTER TABLE Users_Temp ALTER COLUMN population TYPE VARCHAR(200)");

				requests.push("ALTER TABLE Users_PasswordChange ALTER COLUMN login TYPE VARCHAR(64)");
				requests.push("ALTER TABLE Users_PasswordChange ALTER COLUMN population TYPE VARCHAR(200)");
				requests.push("ALTER TABLE Users_PasswordChange DROP CONSTRAINT Users_PasswordChange_pkey, ADD PRIMARY KEY(site, login, population)");
            	break;
          case "hsqldb":
				requests.push("ALTER TABLE Users_Temp ALTER COLUMN userDirectory VARCHAR(200)");
				requests.push("ALTER TABLE Users_Temp ALTER COLUMN population VARCHAR(200)");

				requests.push("ALTER TABLE Users_PasswordChange ALTER COLUMN login VARCHAR(64)");
				requests.push("ALTER TABLE Users_PasswordChange ALTER COLUMN population VARCHAR(200)");
				var rs = sqlQuery("SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE LCASE(TABLE_NAME) = LCASE('Users_PasswordChange') and CONSTRAINT_NAME like 'SYS_PK_%'", datasourceId);
				rs.next();
				var constraint = rs.getString(1);
				requests.push("ALTER TABLE Users_PasswordChange DROP CONSTRAINT " + constraint);
				requests.push("ALTER TABLE Users_PasswordChange ADD PRIMARY KEY(site, login, population)");
            	break;
          default:
            	throw new Error("Unknown database type " + dbType)
        }
      	
		_execute("updateUserSignupTables", dbType, requests, datasourceId);
    }

  	function updateSurveyTables()
  	{
  	 	var datasourceId = Config.getInstance().getValue("plugins.survey.datasource");
		if (datasourceId == null)
		{
			print("...No survey plugin...");
			return;
		}
      	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
		var requests = [];
		
      	switch (dbType)
        {
          case "derby":
				requests.push("ALTER TABLE Survey_Session ADD COLUMN population2 varchar(200) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Survey_Session SET population2=population");
				requests.push("ALTER TABLE Survey_Session DROP COLUMN population");
				requests.push("RENAME COLUMN Survey_Session.population2 TO population");
            	break;
          case "mysql":
				requests.push("ALTER TABLE Survey_Session MODIFY login VARCHAR(64) BINARY");
				requests.push("ALTER TABLE Survey_Session MODIFY population VARCHAR(200) BINARY");
            	break;
          case "oracle":
				requests.push("ALTER TABLE Survey_Session MODIFY population VARCHAR(200)");
            	break;
          case "postgresql":
				requests.push("ALTER TABLE Survey_Session ALTER COLUMN population TYPE VARCHAR(200)");
            	break;
          /* HSQLDB is not supported in survey */
          default:
            	throw new Error("Unknown database type " + dbType)
        }
      	
		_execute("updateSurveyTables", dbType, requests, datasourceId);
    }

  	function updateUserPreferencesTables()
  	{
  	 	var datasourceId = Config.getInstance().getValue("runtime.usersprefs.datasource");
      	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
		var requests = [];
		
      	switch (dbType)
        {
          case "derby":
				requests.push("ALTER TABLE UserPreferences ADD COLUMN login2 varchar(64) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE UserPreferences SET login2=login");
				requests.push("ALTER TABLE UserPreferences DROP COLUMN login");
				requests.push("RENAME COLUMN UserPreferences.login2 TO login");
				requests.push("ALTER TABLE UserPreferences ADD PRIMARY KEY(login, population, context)");

            	break;
          case "mysql":
            	requests.push("ALTER TABLE UserPreferences MODIFY login varchar(64) BINARY");
				requests.push("ALTER TABLE UserPreferences MODIFY population varchar(200) BINARY");

            	break;
          case "oracle":
            	requests.push("ALTER TABLE UserPreferences MODIFY (login varchar(64))");
            	break;
          case "postgresql":
            	requests.push("ALTER TABLE UserPreferences ALTER COLUMN login TYPE varchar(64)");
            	break;
          case "hsqldb":
            	requests.push("ALTER TABLE UserPreferences ALTER COLUMN login varchar(64)");
            	break;
          default:
            	throw new Error("Unknown database type " + dbType)
        }
      	
		_execute("updateUserPreferencesTables", dbType, requests, datasourceId);
    }

  	function updateAuhenticationTokenTables()
  	{
  	 	var datasourceId = Config.getInstance().getValue("runtime.assignments.authenticationtokens");
      	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
		var requests = [];
		
      	switch (dbType)
        {
          case "derby":
				// Nothing
            	break;
          case "mysql":
            	requests.push("ALTER TABLE Authentication_Token MODIFY login varchar(64) BINARY");
				requests.push("ALTER TABLE Authentication_Token MODIFY population_id varchar(200) BINARY");

            	break;
          case "oracle":
				// Nothing to do
            	break;
          case "postgresql":
				// Nothing
            	break;
          case "hsqldb":
				// Nothing
            	break;
          default:
            	throw new Error("Unknown database type " + dbType)
        }
      	
		_execute("updateAuhenticationTokenTables", dbType, requests, datasourceId);
    }

  	function updateRightsTables()
  	{
  	 	var datasourceId = Config.getInstance().getValue("runtime.rights.datasource");
      	var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get("dbtype");
		var requests = [];
		
      	switch (dbType)
        {
          case "derby":
				requests.push("ALTER TABLE Rights_AllowedUsers ADD COLUMN Login2 varchar(64) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Rights_AllowedUsers SET Login2=Login");
				requests.push("ALTER TABLE Rights_AllowedUsers DROP COLUMN Login");
				requests.push("RENAME COLUMN Rights_AllowedUsers.Login2 TO Login");
				requests.push("ALTER TABLE Rights_AllowedUsers ADD PRIMARY KEY(Profile_Id, Login, UserPopulation_Id, Context)");

				requests.push("ALTER TABLE Rights_DeniedUsers ADD COLUMN Login2 varchar(64) NOT NULL DEFAULT 'nonnull'");
				requests.push("UPDATE Rights_DeniedUsers SET Login2=Login");
				requests.push("ALTER TABLE Rights_DeniedUsers DROP COLUMN Login");
				requests.push("RENAME COLUMN Rights_DeniedUsers.Login2 TO Login");
				requests.push("ALTER TABLE Rights_DeniedUsers ADD PRIMARY KEY(Profile_Id, Login, UserPopulation_Id, Context)");
            	break;
          case "mysql":
            	requests.push("ALTER TABLE Rights_AllowedUsers MODIFY Login varchar(64) BINARY");
				requests.push("ALTER TABLE Rights_AllowedUsers MODIFY UserPopulation_Id varchar(200) BINARY");
				requests.push("ALTER TABLE Rights_DeniedUsers MODIFY Login varchar(64) BINARY");
				requests.push("ALTER TABLE Rights_DeniedUsers MODIFY UserPopulation_Id varchar(200) BINARY");

            	break;
          case "oracle":
            	requests.push("ALTER TABLE Rights_AllowedUsers MODIFY Login varchar(64)");
				requests.push("ALTER TABLE Rights_DeniedUsers MODIFY Login varchar(64)");
            	break;
          case "postgresql":
            	requests.push("ALTER TABLE Rights_AllowedUsers ALTER COLUMN Login TYPE varchar(64)");
				requests.push("ALTER TABLE Rights_DeniedUsers ALTER COLUMN Login TYPE varchar(64)");
            	break;
          case "hsqldb":
            	requests.push("ALTER TABLE Rights_AllowedUsers ALTER COLUMN Login varchar(64)");
				requests.push("ALTER TABLE Rights_DeniedUsers ALTER COLUMN Login varchar(64)");
            	break;
          default:
            	throw new Error("Unknown database type " + dbType)
        }
      	
		_execute("updateRightsTables", dbType, requests, datasourceId);
    }

  	function _execute(label, dbType, requests, datasourceId)
  	{
		print("*********************************************************")
		print("* " + label + " on " + datasourceId + " (" + dbType + ") *");
		print("*********************************************************")
		
		for (var i=0; i < requests.length; i++)
		{
			print("        " + requests[i]);
			sqlUpdate(requests[i],datasourceId)
		}
		
		print("        OK")
	}

Raphaël Franchet added a comment - 14/May/19 15:54 - edited Migration (derby,mysql,hsqldb, oracle and postgres OK) var Config = Java.type( "org.ametys.runtime.config.Config" ); var SQLDataSourceManager = serviceManager.lookup( "org.ametys.core.datasource.SQLDataSourceManager" ); var UserPopulationDAO = serviceManager.lookup( "org.ametys.core.user.population.UserPopulationDAO" ); var GroupDirectoryDAO = serviceManager.lookup( "org.ametys.core.group.GroupDirectoryDAO" ); updateRightsTables(); updateAuhenticationTokenTables(); updateUserPreferencesTables(); updateUserSignupTables(); updateSurveyTables(); updateUserPopulations(); updateGroupDirectories(); function updateGroupDirectories() { print( "updateGroupDirectories" ); var groupDirectories = GroupDirectoryDAO.getGroupDirectories(); for ( var i = 0; i < groupDirectories.size(); i++) { var groupDirectory = groupDirectories.get(i); _updateGroupDirectory(groupDirectory); } } function _updateGroupDirectory(groupDirectory) { print( "* groupDirectory " + groupDirectory.getId() + " *" ); if (groupDirectory.getGroupDirectoryModelId() == "org.ametys.plugins.core.group.directory.Jdbc" ) { var datasourceId = groupDirectory.getParameterValues().get( "runtime.groups.jdbc.datasource" ); var tableName = groupDirectory.getParameterValues().get( "runtime.groups.jdbc.composition.table" ); var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : requests.push( "ALTER TABLE " + tableName + " ADD COLUMN Login2 varchar(64) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE " + tableName + " SET Login2=Login" ); requests.push( "ALTER TABLE " + tableName + " DROP COLUMN Login" ); requests.push( "RENAME COLUMN " + tableName + ".Login2 TO Login" ); requests.push( "ALTER TABLE " + tableName + " ADD PRIMARY KEY (Group_Id, Login, UserPopulation_Id)" ); break ; case "mysql" : requests.push( "ALTER TABLE " + tableName + " MODIFY Login VARCHAR(64) BINARY" ); requests.push( "ALTER TABLE " + tableName + " MODIFY UserPopulation_Id VARCHAR(200) BINARY" ); break ; case "oracle" : requests.push( "ALTER TABLE " + tableName + " MODIFY Login VARCHAR(64)" ); break ; case "postgresql" : requests.push( "ALTER TABLE " + tableName + " ALTER COLUMN Login TYPE VARCHAR(64)" ); break ; case "hsqldb" : requests.push( "ALTER TABLE " + tableName + " ALTER COLUMN Login VARCHAR(64)" ); break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "_updateGroupDirectory" , dbType, requests, datasourceId); } } function updateUserPopulations() { print( "updateUserPopulations" ); var userPopulations = UserPopulationDAO.getUserPopulations( true ); for ( var i = 0; i < userPopulations.size(); i++) { var userPopulation = userPopulations.get(i); _updateUserPopulation(userPopulation); } } function _updateUserPopulation(userPopulation) { print( "* userPopulation " + userPopulation.getId() + " *" ); var userDirectories = userPopulation.getUserDirectories(); for ( var i = 0; i < userDirectories.size(); i++) { var userDirectory = userDirectories.get(i); _updateUserDirectory(userPopulation, i, userDirectory); } var credentialProviders = userPopulation.getCredentialProviders(); for ( var i = 0; i < credentialProviders.size(); i++) { var credentialProvider = credentialProviders.get(i); _updateCredentialProvider(userPopulation, i, credentialProvider); } } function _updateUserDirectory(userPopulation, index, userDirectory) { print( "** userDirectory[" + index + "] " + userDirectory.getId() + " " + userDirectory.getUserDirectoryModelId() + " *" ); if (userDirectory.getUserDirectoryModelId() == "org.ametys.plugins.core.user.directory.Jdbc" ) { var datasourceId = userDirectory.getParameterValues().get( "runtime.users.jdbc.datasource" ); var tableName = userDirectory.getParameterValues().get( "runtime.users.jdbc.table" ); var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : // Nothing to do break ; case "mysql" : requests.push( "ALTER TABLE " + tableName + " MODIFY login varchar(64) BINARY" ); break ; case "oracle" : // Nothing to do break ; case "postgresql" : // Nothing to do break ; case "hsqldb" : // Nothing to do break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "_updateUserDirectory" , dbType, requests, datasourceId); } } function _updateCredentialProvider(userPopulation, index, credentialProvider) { print( "** credentialProvider[" + index + "] " + credentialProvider.getId() + " " + credentialProvider.getCredentialProviderModelId() + " *" ); if (credentialProvider.getCredentialProviderModelId() == "org.ametys.core.authentication.FormBased" && credentialProvider.getParameterValues().get( "runtime.authentication.form.captcha" ) == true ) { var datasourceId = credentialProvider.getParameterValues().get( "runtime.authentication.form.security.storage" ); var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : requests.push( "ALTER TABLE Users_FormConnectionFailed ADD COLUMN login2 varchar(64) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Users_FormConnectionFailed SET login2=login" ); requests.push( "ALTER TABLE Users_FormConnectionFailed DROP COLUMN login" ); requests.push( "RENAME COLUMN Users_FormConnectionFailed.login2 TO login" ); requests.push( "ALTER TABLE Users_FormConnectionFailed ADD PRIMARY KEY(login)" ); break ; case "mysql" : requests.push( "ALTER TABLE Users_FormConnectionFailed MODIFY login VARCHAR(64) BINARY" ); requests.push( "ALTER TABLE Users_FormConnectionFailed MODIFY population_id VARCHAR(200) BINARY" ); break ; case "oracle" : requests.push( "ALTER TABLE Users_FormConnectionFailed MODIFY (login VARCHAR(64))" ); break ; case "postgresql" : requests.push( "ALTER TABLE Users_FormConnectionFailed ALTER COLUMN login TYPE VARCHAR(64)" ); break ; case "hsqldb" : requests.push( "ALTER TABLE Users_FormConnectionFailed ALTER COLUMN login VARCHAR(64)" ); break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "_updateCredentialProvider" , dbType, requests, datasourceId); } } function updateUserSignupTables() { var datasourceId = Config.getInstance().getValue( "web.usersignup.datasource" ); if (datasourceId == null ) { print( "...No web signup plugin..." ); return ; } var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : requests.push( "ALTER TABLE Users_Temp ADD COLUMN userDirectory2 varchar(200) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Users_Temp SET userDirectory2=userDirectory" ); requests.push( "ALTER TABLE Users_Temp DROP COLUMN userDirectory" ); requests.push( "RENAME COLUMN Users_Temp.userDirectory2 TO userDirectory" ); requests.push( "ALTER TABLE Users_Temp ADD PRIMARY KEY(userDirectory)" ); requests.push( "ALTER TABLE Users_Temp ADD COLUMN population2 varchar(200) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Users_Temp SET population2=population" ); requests.push( "ALTER TABLE Users_Temp DROP COLUMN population" ); requests.push( "RENAME COLUMN Users_Temp.population2 TO population" ); requests.push( "ALTER TABLE Users_Temp ADD PRIMARY KEY(population)" ); requests.push( "ALTER TABLE Users_PasswordChange ADD COLUMN population2 varchar(200) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Users_PasswordChange SET population2=population" ); requests.push( "ALTER TABLE Users_PasswordChange DROP COLUMN population" ); requests.push( "RENAME COLUMN Users_PasswordChange.population2 TO population" ); requests.push( "ALTER TABLE Users_PasswordChange ADD COLUMN login2 varchar(64) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Users_PasswordChange SET login2=login" ); requests.push( "ALTER TABLE Users_PasswordChange DROP COLUMN login" ); requests.push( "RENAME COLUMN Users_PasswordChange.login2 TO login" ); requests.push( "ALTER TABLE Users_PasswordChange ADD PRIMARY KEY(site, login, population)" ); break ; case "mysql" : requests.push( "ALTER TABLE Users_Temp MODIFY userDirectory VARCHAR(200) BINARY" ); requests.push( "ALTER TABLE Users_Temp MODIFY population VARCHAR(200) BINARY" ); requests.push( "ALTER TABLE Users_PasswordChange MODIFY login VARCHAR(64) BINARY" ); requests.push( "ALTER TABLE Users_PasswordChange MODIFY population VARCHAR(200) BINARY" ); requests.push( "ALTER TABLE Users_PasswordChange DROP PRIMARY KEY, ADD PRIMARY KEY(site, login, population)" ); break ; case "oracle" : requests.push( "ALTER TABLE Users_Temp MODIFY (userDirectory VARCHAR(200))" ); requests.push( "ALTER TABLE Users_Temp MODIFY (population VARCHAR(200))" ); requests.push( "ALTER TABLE Users_PasswordChange MODIFY (login VARCHAR(64))" ); requests.push( "ALTER TABLE Users_PasswordChange MODIFY (population VARCHAR(200))" ); requests.push( "ALTER TABLE Users_PasswordChange DROP CONSTRAINT pk_users_passwordchange" ); requests.push( "ALTER TABLE Users_PasswordChange ADD CONSTRAINT pk_users_passwordchange PRIMARY KEY (site, login, population)" ); break ; case "postgresql" : requests.push( "ALTER TABLE Users_Temp ALTER COLUMN userDirectory TYPE VARCHAR(200)" ); requests.push( "ALTER TABLE Users_Temp ALTER COLUMN population TYPE VARCHAR(200)" ); requests.push( "ALTER TABLE Users_PasswordChange ALTER COLUMN login TYPE VARCHAR(64)" ); requests.push( "ALTER TABLE Users_PasswordChange ALTER COLUMN population TYPE VARCHAR(200)" ); requests.push( "ALTER TABLE Users_PasswordChange DROP CONSTRAINT Users_PasswordChange_pkey, ADD PRIMARY KEY(site, login, population)" ); break ; case "hsqldb" : requests.push( "ALTER TABLE Users_Temp ALTER COLUMN userDirectory VARCHAR(200)" ); requests.push( "ALTER TABLE Users_Temp ALTER COLUMN population VARCHAR(200)" ); requests.push( "ALTER TABLE Users_PasswordChange ALTER COLUMN login VARCHAR(64)" ); requests.push( "ALTER TABLE Users_PasswordChange ALTER COLUMN population VARCHAR(200)" ); var rs = sqlQuery( "SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE LCASE(TABLE_NAME) = LCASE( 'Users_PasswordChange' ) and CONSTRAINT_NAME like 'SYS_PK_%' " , datasourceId); rs.next(); var constraint = rs.getString(1); requests.push( "ALTER TABLE Users_PasswordChange DROP CONSTRAINT " + constraint); requests.push( "ALTER TABLE Users_PasswordChange ADD PRIMARY KEY(site, login, population)" ); break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "updateUserSignupTables" , dbType, requests, datasourceId); } function updateSurveyTables() { var datasourceId = Config.getInstance().getValue( "plugins.survey.datasource" ); if (datasourceId == null ) { print( "...No survey plugin..." ); return ; } var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : requests.push( "ALTER TABLE Survey_Session ADD COLUMN population2 varchar(200) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Survey_Session SET population2=population" ); requests.push( "ALTER TABLE Survey_Session DROP COLUMN population" ); requests.push( "RENAME COLUMN Survey_Session.population2 TO population" ); break ; case "mysql" : requests.push( "ALTER TABLE Survey_Session MODIFY login VARCHAR(64) BINARY" ); requests.push( "ALTER TABLE Survey_Session MODIFY population VARCHAR(200) BINARY" ); break ; case "oracle" : requests.push( "ALTER TABLE Survey_Session MODIFY population VARCHAR(200)" ); break ; case "postgresql" : requests.push( "ALTER TABLE Survey_Session ALTER COLUMN population TYPE VARCHAR(200)" ); break ; /* HSQLDB is not supported in survey */ default : throw new Error( "Unknown database type " + dbType) } _execute( "updateSurveyTables" , dbType, requests, datasourceId); } function updateUserPreferencesTables() { var datasourceId = Config.getInstance().getValue( "runtime.usersprefs.datasource" ); var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : requests.push( "ALTER TABLE UserPreferences ADD COLUMN login2 varchar(64) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE UserPreferences SET login2=login" ); requests.push( "ALTER TABLE UserPreferences DROP COLUMN login" ); requests.push( "RENAME COLUMN UserPreferences.login2 TO login" ); requests.push( "ALTER TABLE UserPreferences ADD PRIMARY KEY(login, population, context)" ); break ; case "mysql" : requests.push( "ALTER TABLE UserPreferences MODIFY login varchar(64) BINARY" ); requests.push( "ALTER TABLE UserPreferences MODIFY population varchar(200) BINARY" ); break ; case "oracle" : requests.push( "ALTER TABLE UserPreferences MODIFY (login varchar(64))" ); break ; case "postgresql" : requests.push( "ALTER TABLE UserPreferences ALTER COLUMN login TYPE varchar(64)" ); break ; case "hsqldb" : requests.push( "ALTER TABLE UserPreferences ALTER COLUMN login varchar(64)" ); break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "updateUserPreferencesTables" , dbType, requests, datasourceId); } function updateAuhenticationTokenTables() { var datasourceId = Config.getInstance().getValue( "runtime.assignments.authenticationtokens" ); var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : // Nothing break ; case "mysql" : requests.push( "ALTER TABLE Authentication_Token MODIFY login varchar(64) BINARY" ); requests.push( "ALTER TABLE Authentication_Token MODIFY population_id varchar(200) BINARY" ); break ; case "oracle" : // Nothing to do break ; case "postgresql" : // Nothing break ; case "hsqldb" : // Nothing break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "updateAuhenticationTokenTables" , dbType, requests, datasourceId); } function updateRightsTables() { var datasourceId = Config.getInstance().getValue( "runtime.rights.datasource" ); var dbType = SQLDataSourceManager.getDataSourceDefinition(datasourceId).getParameters().get( "dbtype" ); var requests = []; switch (dbType) { case "derby" : requests.push( "ALTER TABLE Rights_AllowedUsers ADD COLUMN Login2 varchar(64) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Rights_AllowedUsers SET Login2=Login" ); requests.push( "ALTER TABLE Rights_AllowedUsers DROP COLUMN Login" ); requests.push( "RENAME COLUMN Rights_AllowedUsers.Login2 TO Login" ); requests.push( "ALTER TABLE Rights_AllowedUsers ADD PRIMARY KEY(Profile_Id, Login, UserPopulation_Id, Context)" ); requests.push( "ALTER TABLE Rights_DeniedUsers ADD COLUMN Login2 varchar(64) NOT NULL DEFAULT 'nonnull' " ); requests.push( "UPDATE Rights_DeniedUsers SET Login2=Login" ); requests.push( "ALTER TABLE Rights_DeniedUsers DROP COLUMN Login" ); requests.push( "RENAME COLUMN Rights_DeniedUsers.Login2 TO Login" ); requests.push( "ALTER TABLE Rights_DeniedUsers ADD PRIMARY KEY(Profile_Id, Login, UserPopulation_Id, Context)" ); break ; case "mysql" : requests.push( "ALTER TABLE Rights_AllowedUsers MODIFY Login varchar(64) BINARY" ); requests.push( "ALTER TABLE Rights_AllowedUsers MODIFY UserPopulation_Id varchar(200) BINARY" ); requests.push( "ALTER TABLE Rights_DeniedUsers MODIFY Login varchar(64) BINARY" ); requests.push( "ALTER TABLE Rights_DeniedUsers MODIFY UserPopulation_Id varchar(200) BINARY" ); break ; case "oracle" : requests.push( "ALTER TABLE Rights_AllowedUsers MODIFY Login varchar(64)" ); requests.push( "ALTER TABLE Rights_DeniedUsers MODIFY Login varchar(64)" ); break ; case "postgresql" : requests.push( "ALTER TABLE Rights_AllowedUsers ALTER COLUMN Login TYPE varchar(64)" ); requests.push( "ALTER TABLE Rights_DeniedUsers ALTER COLUMN Login TYPE varchar(64)" ); break ; case "hsqldb" : requests.push( "ALTER TABLE Rights_AllowedUsers ALTER COLUMN Login varchar(64)" ); requests.push( "ALTER TABLE Rights_DeniedUsers ALTER COLUMN Login varchar(64)" ); break ; default : throw new Error( "Unknown database type " + dbType) } _execute( "updateRightsTables" , dbType, requests, datasourceId); } function _execute(label, dbType, requests, datasourceId) { print( "*********************************************************" ) print( "* " + label + " on " + datasourceId + " (" + dbType + ") *" ); print( "*********************************************************" ) for ( var i=0; i < requests.length; i++) { print( " " + requests[i]); sqlUpdate(requests[i],datasourceId) } print( " OK" ) }

Details

Description

Attachments

Attachments

Issue Links

Activity

Collapse comment: Raphaël Franchet added a comment - 14/May/19 17:19, Edited by Raphaël Franchet - 16/May/19 12:52

Expand comment: Raphaël Franchet added a comment - 14/May/19 17:19, Edited by Raphaël Franchet - 16/May/19 12:52

Collapse comment: Raphaël Franchet added a comment - 14/May/19 15:54, Edited by Raphaël Franchet - 16/May/19 13:08

Expand comment: Raphaël Franchet added a comment - 14/May/19 15:54, Edited by Raphaël Franchet - 16/May/19 13:08

People

Dates

Contact Product Support