function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $presentation_type = SummitEventType::get("SummitEventType", "Type = 'Presentation' AND SummitID = 5")->first(); if ($presentation_type) { $presentation_type_id = $presentation_type->ID; } else { $presentation_type = new SummitEventType(); $presentation_type->Type = 'Presentation'; $presentation_type->SummitID = 5; $presentation_type->Color = '#D0A9F5'; $presentation_type_id = $presentation_type->Write(); } $SQL = <<<SQL UPDATE SummitEvent SET TypeID = {$presentation_type_id} WHERE ClassName = 'Presentation' AND SummitID = 5; SQL; DB::query($SQL); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { DB::getConn()->transactionStart(); try { // migrate records from Live to Stage table DB::query("DELETE FROM JobPage"); DB::query("\nINSERT INTO JobPage\n\t\t(ID,\n\t\tJobPostedDate,\n\t\tJobCompany,\n\t\tJobMoreInfoLink,\n\t\tJobLocation,\n\t\tFoundationJob,\n\t\tExpirationDate,\n\t\tActive,\n\t\tJobInstructions2Apply,\n\t\tLocationType,\n\t\tTitle,\n\t\tContent)\nSELECT \tJobPage_Live.ID,\n\t\tJobPostedDate,\n\t\tJobCompany,\n\t\tJobMoreInfoLink,\n\t\tJobLocation,\n\t\tFoundationJob,\n\t\tExpirationDate,\n\t\tActive,\n\t\tJobInstructions2Apply,\n\t\tLocationType,\n\t\tTitle,\n\t\tContent\nFROM \tJobPage_Live\nINNER JOIN Page_Live on Page_Live.Id = JobPage_Live.Id\nINNER JOIN SiteTree_Live on SiteTree_Live.Id = JobPage_Live.Id"); DB::query("DROP TABLE JobPage_Live"); DB::query("DROP TABLE JobPage_versions"); // delete orphan records DB::query("delete from Page where Id in (select Id from SiteTree where ClassName = 'JobPage')"); DB::query("delete from SiteTree where ClassName = 'JobPage'"); DB::query("delete from Page_Live where Id in (select Id from SiteTree_Live where ClassName = 'JobPage')"); DB::query("delete from SiteTree_Live where ClassName = 'JobPage'"); DB::query("delete from Page_versions where Id in (select Id from SiteTree_versions where ClassName = 'JobPage')"); DB::query("delete from SiteTree_versions where ClassName = 'JobPage'"); DB::getConn()->transactionEnd(); } catch (Exception $e) { DB::getConn()->transactionRollback(); return; } $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $requests = JobRegistrationRequest::get(); foreach ($requests as $request) { if (!empty($request->City)) { //create locations $location = new JobLocation(); $location->City = $request->City; $location->State = $request->State; $location->Country = $request->Country; $location->RequestID = $request->ID; $location->Write(); $request->LocationType = 'Various'; $request->Write(); } } $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $SQL = <<<SQL INSERT INTO `VideoPresentation` ( `ClassName`, `Created`, `LastEdited`, `Name`, `DisplayOnSite`, `Featured`, `City`, `Country`, `Description`, `YouTubeID`, `URLSegment`, `StartTime`, `EndTime`, `Location`, `Type`, `Day`, `Speakers`, `SlidesLink`, `event_key`, `IsKeynote`, `PresentationCategoryPageID`, `SummitID`, `MemberID`) SELECT 'VideoPresentation', NOW(),NOW(),Name, `DisplayOnSite`, `Featured`, `City`, `Country`, `Description`, `YouTubeID`, `URLSegment`, `StartTime`, `EndTime`, `Location`, `Type`, `Day`, `Speakers`, `SlidesLink`, `event_key`, `IsKeynote`, `PresentationCategoryPageID`, `SummitID`, `MemberID` FROM Presentation; SQL; DB::query($SQL); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $marketplace_types = array('Private Cloud'); $factory = new MarketplaceFactory(); $service = new MarketplaceTypeManager(new SapphireMarketPlaceTypeRepository(), new SapphireSecurityGroupRepository(), SapphireTransactionManager::getInstance()); foreach ($marketplace_types as $marketplace_type) { try { $service->store($factory->buildMarketplaceType($marketplace_type)); } catch (Exception $ex) { } } DB::query("INSERT INTO CloudServiceOffered_PricingSchemas (CloudServiceOfferedID, PricingSchemaTypeID) SELECT PublicCloudServiceOfferedID, PricingSchemaTypeID FROM PublicCloudServiceOffered_PricingSchemas;"); DB::query("UPDATE OpenStackImplementationApiCoverage SET ClassName='CloudServiceOffered'\nWHERE ImplementationID IN (SELECT ID FROM CompanyService where ClassName='PublicCloudService');"); DB::query("UPDATE DataCenterRegion SET CloudServiceID = PublicCloudID;"); DB::query("ALTER TABLE `CloudServiceOffered_PricingSchemas`\n\t\t\tADD UNIQUE INDEX `CloudServiceOffered_PricingSchemaType` (`CloudServiceOfferedID` ASC, `PricingSchemaTypeID` ASC);"); DB::query("ALTER TABLE `DataCenterRegion` DROP INDEX `Name_PublicCloud`;"); DB::query("ALTER TABLE `DataCenterRegion`\n\t\t\tADD UNIQUE INDEX `Name_CloudService` (`Name` ASC, `CloudServiceID` ASC);"); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Proc ...<BR>"; $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); //run migration $sql = <<<SQL \t\tupdate DeploymentSurvey set InformationSources = REPLACE(InformationSources,'ask.openstack.org','Ask OpenStack (ask.openstack.org)') where InformationSources like '%ask.openstack.org%'; SQL; DB::query($sql); $sql = <<<SQL update Deployment set DeploymentStage = REPLACE(DeploymentStage,'Dev/QA','Under development/in testing') where DeploymentStage like '%Dev/QA%'; SQL; DB::query($sql); $sql = <<<SQL update Deployment set OtherWhyNovaNetwork = WhyNovaNetwork where WhyNovaNetwork is not null; SQL; DB::query($sql); $sql = <<<SQL update Deployment set WhyNovaNetwork = 'Other (please specify)' where OtherWhyNovaNetwork is not null; SQL; DB::query($sql); } else { echo "Migration Already Ran! <BR>"; } echo "Migration Done <BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { //first we add the platform InteropProgramType $platform = InteropProgramType::get("InteropProgramType", "ShortName = 'Platform'")->first(); if (!$platform) { $platform = new InteropProgramType(); $platform->Name = 'OpenStack Powered Platform'; $platform->ShortName = 'Platform'; $platform->HasCapabilities = true; $platform->Order = 1; $platform->write(); $platform = InteropProgramType::get("InteropProgramType", "ShortName = 'Platform'")->first(); } //then migrate the old InteropProgramType relations with capability and designated sections $relations = DB::query('SELECT cap.ID AS capID, pt.ID AS ptID, pv.ID AS pvID, cap.Order AS capOrder FROM InteropCapability AS cap LEFT JOIN InteropProgramType AS pt ON cap.ProgramID = pt.ID LEFT JOIN InteropProgramVersion AS pv ON cap.VersionID = pv.ID'); foreach ($relations as $relation) { $capability = InteropCapability::get_by_id('InteropCapability', $relation['capID']); $program = InteropProgramType::get_by_id('InteropProgramType', $relation['ptID']); $version = InteropProgramVersion::get_by_id('InteropProgramVersion', $relation['pvID']); $capability->Program()->add($program); $capability->Program()->add($platform); $capability->Version()->add($version, array('Order' => $relation['capOrder'])); $capability->write(); } $relations = DB::query('SELECT ds.ID AS dsID, pt.ID AS ptID, pv.ID AS pvID, ds.Order AS dsOrder FROM InteropDesignatedSection AS ds LEFT JOIN InteropProgramType AS pt ON ds.ProgramID = pt.ID LEFT JOIN InteropProgramVersion AS pv ON ds.VersionID = pv.ID'); foreach ($relations as $relation) { $dsection = InteropDesignatedSection::get_by_id('InteropDesignatedSection', $relation['dsID']); $program = InteropProgramType::get_by_id('InteropProgramType', $relation['ptID']); $version = InteropProgramVersion::get_by_id('InteropProgramVersion', $relation['pvID']); $dsection->Program()->add($program); $dsection->Program()->add($platform); $dsection->Version()->add($version, array('Order' => $relation['dsOrder'])); $dsection->write(); } //finally remove ProgramID and VersionID column from capability and designatedsection DB::query('ALTER TABLE InteropCapability DROP COLUMN ProgramID'); DB::query('ALTER TABLE InteropDesignatedSection DROP COLUMN ProgramID'); DB::query('ALTER TABLE InteropCapability DROP COLUMN VersionID'); DB::query('ALTER TABLE InteropDesignatedSection DROP COLUMN VersionID'); DB::query('ALTER TABLE InteropCapability DROP COLUMN `Order`'); DB::query('ALTER TABLE InteropDesignatedSection DROP COLUMN `Order`'); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { DB::query("update JobPage set active =1;"); DB::query("update JobPage set ExpirationDate = DATE_ADD(JobPostedDate, INTERVAL 2 MONTH)\nwhere JobPage.JobPostedDate IS NOT NULL;"); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo sprintf("starting migration # %s ...", $this->title) . PHP_EOL; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $this->doUp(); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); echo sprintf("ending migration # %s ...", $this->title) . PHP_EOL; } else { echo sprintf("migration # %s already ran !...", $this->title) . PHP_EOL; } }
function up() { echo "Starting Migration Proc ...<BR>"; $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); //run migration $query = "UPDATE Deployment, (\n SELECT Org.ID AS OrgID, Deployment.ID AS DepID\n FROM Org, Deployment, DeploymentSurvey, Member\n WHERE\n Member.ID = DeploymentSurvey.MemberID AND\n Org.ID = Member.OrgID AND\n Deployment.DeploymentSurveyID = DeploymentSurvey.ID\n ) AS t1\n SET Deployment.OrgID = t1.OrgID\n WHERE t1.DepID = Deployment.ID"; DB::query($query); } else { echo "Migration Already Ran! <BR>"; } echo "Migration Done <BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { $g = new Group(); $g->setTitle('Community Members'); $g->setDescription('Community Members'); $g->setSlug(IFoundationMember::CommunityMemberGroupSlug); $g->write(); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $g = new Group(); $g->setTitle('CCLA Admin'); $g->setDescription('Company CCLA Admin'); $g->setSlug(ICLAMemberDecorator::CCLAGroupSlug); $g->write(); Permission::grant($g->getIdentifier(), ICLAMemberDecorator::CCLAPermissionSlug); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { $result = News::get(); foreach ($result as $news) { if (empty($news->DateEmbargo)) { $news->DateEmbargo = $news->Date; } $news->Write(); } $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Proc ...<BR>"; $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); //run migration $surveys = DeploymentSurvey::get(); foreach ($surveys as $survey) { $survey->SendDigest = 1; $survey->write(); } } else { echo "Migration Already Ran! <BR>"; } echo "Migration Done <BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { //run migration $query = <<<SQL update SiteTree_Live SET ClassName='AnniversaryPage' where ID = 891; SQL; DB::query($query); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } else { echo "Migration Already Ran! <BR>"; } echo "Migration Done <BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { DB::getConn()->transactionStart(); try { DB::query("DELETE FROM EventPage"); DB::query("\nINSERT INTO EventPage\n\t\t(ID,\n\t\tEventStartDate,\n\t\tEventEndDate,\n\t\tEventLink,\n\t\tEventLinkLabel,\n\t\tEventLocation,\n\t\tEventSponsor,\n\t\tEventSponsorLogoUrl,\n\t\tIsSummit,\n\t\tEventCategory,\n\t\tExternalSourceId,\n\t\tClassName,\n\t\tCreated,\n\t\tLastEdited,\n\t\tTitle)\nSELECT \tEventPage_Live.ID,\n\t\tEventStartDate,\n\t\tEventEndDate,\n\t\tEventLink,\n\t\tEventLinkLabel,\n\t\tEventLocation,\n\t\tEventSponsor,\n\t\tEventSponsorLogoUrl,\n\t\tIsSummit,\n\t\tEventCategory,\n\t\tExternalSourceId,\n\t\tClassName,\n\t\tCreated,\n\t\tLastEdited,\n\t\tTitle\nFROM \tEventPage_Live\nINNER JOIN Page_Live on Page_Live.Id = EventPage_Live.Id\nINNER JOIN SiteTree_Live on SiteTree_Live.Id = EventPage_Live.Id\n "); DB::query("DROP TABLE EventPage_Live"); DB::getConn()->transactionEnd(); } catch (Exception $e) { DB::getConn()->transactionRollback(); } $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $programs = DB::query('SELECT P.*,C.CompanyID from TrainingProgram P INNER JOIN Contract C on C.ID = P.TrainingContractID;'); $service = new TrainingManager(new SapphireTrainingServiceRepository(), new SapphireMarketPlaceTypeRepository(), new MarketPlaceTypeAddPolicyStub(), null, new CacheServiceStub(), new MarketplaceFactory(), SapphireTransactionManager::getInstance()); $factory = new TrainingFactory(); foreach ($programs as $program) { $company_id = (int) $program['CompanyID']; $company = Company::get()->byID($company_id); $program_id = (int) $program['ID']; $training = $factory->buildTraining($program['Name'], $program['Description'], true, $company); $training->CompanyID = $company_id; $training->write(); //get former courses and associate it with new entity $courses = TrainingCourse::get()->filter('ProgramID', $program_id); if ($courses && count($courses) > 0) { foreach ($courses as $course) { $course->TrainingServiceID = $training->getIdentifier(); $course->write(); } } } //db alter DB::query('DROP TABLE Training;'); DB::query('ALTER TABLE `TrainingCourse` DROP COLUMN ProgramID;'); DB::query('DROP TABLE TrainingProgram;'); $new_training_group_slug = ITraining::MarketPlaceGroupSlug; DB::query("\n\t\t\tUPDATE `Company_Administrators`\n\t\t\tSET GroupID = (SELECT ID FROM `Group` WHERE code='{$new_training_group_slug}' LIMIT 1 )\n\t\t\tWHERE ID IN (\n\t\t\t\tSELECT tmp.id FROM\n\t\t\t\t(\n\t\t\t\t\tSELECT ID FROM `Company_Administrators`\n\t\t\t\t\tWHERE GroupID IN ( SELECT ID FROM `Group` where code='training-admins')\n\t\t\t\t) as tmp\n\t\t\t);\n\t\t\t"); DB::query("\n\t\t\tDELETE Permission\n\t\t\tFROM Permission\n\t\t\tWHERE Code='MANAGE_COMPANY_TRAINING';\n\t\t\t"); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = DataObject::get_one("Migration", "Name='{$this->title}'"); if (!$migration) { DB::getConn()->transactionStart(); try { DB::query("delete from Page where Id in (select Id from SiteTree where ClassName = 'EventPage')"); DB::query("delete from SiteTree where ClassName = 'EventPage'"); DB::query("delete from Page_Live where Id in (select Id from SiteTree_Live where ClassName = 'EventPage')"); DB::query("delete from SiteTree_Live where ClassName = 'EventPage'"); DB::getConn()->transactionEnd(); } catch (Exception $e) { DB::getConn()->transactionRollback(); } $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $marketplace_types = array('Training', 'Public Cloud', 'Appliance', 'Distribution', 'Consultant'); $factory = new MarketplaceFactory(); $service = new MarketplaceTypeManager(new SapphireMarketPlaceTypeRepository(), new SapphireSecurityGroupRepository(), SapphireTransactionManager::getInstance()); foreach ($marketplace_types as $marketplace_type) { try { $service->store($factory->buildMarketplaceType($marketplace_type)); } catch (Exception $ex) { } } DB::query("ALTER TABLE `OpenStackRelease_OpenStackComponents`\n\t\t\tADD UNIQUE INDEX `Release_Component` (`OpenStackReleaseID` ASC, `OpenStackComponentID` ASC);"); DB::query("ALTER TABLE `CloudServiceOffered_PricingSchemas`\n\t\t\tADD UNIQUE INDEX `CloudServiceOffered_PricingSchemaType` (`CloudServiceOfferedID` ASC, `PricingSchemaTypeID` ASC);"); $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); } echo "Ending Migration Proc ...<BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; $migration = Migration::get()->filter('Name', $this->title)->first(); if (!$migration) { $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); DB::query("DROP TABLE IF EXISTS `Continent_Countries`;"); DB::query("CREATE TABLE `Continent_Countries` (\n `ContinentID` int(11) NOT NULL,\n `CountryCode` varchar(4) NOT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;"); DB::query("LOCK TABLES `Continent_Countries` WRITE;"); DB::query("INSERT INTO `Continent_Countries` VALUES (3,'AD'),(5,'AE'),(5,'AF'),(1,'AI'),(3,'AL'),(5,'AM'),(1,'AN'),(4,'AO'),(7,'AQ'),(2,'AR'),(1,'AS'),(3,'AT'),(6,'AU'),(3,'AX'),(5,'AZ'),(1,'BB'),(3,'BA'),(5,'BD'),(3,'BE'),(4,'BF'),(3,'BG'),(5,'BH'),(2,'BO'),(2,'BR'),(5,'BT'),(4,'BW'),(4,'ZW'),(4,'ZM'),(4,'ZA'),(5,'VN'),(2,'VE'),(2,'UY'),(1,'US'),(1,'UM'),(4,'UG'),(3,'UA'),(4,'TZ'),(5,'TW'),(1,'TT'),(3,'TR'),(5,'TR'),(4,'TN'),(5,'TH'),(7,'TF'),(1,'TC'),(5,'SY'),(1,'SV'),(4,'SN'),(3,'SK'),(3,'SI'),(5,'SG'),(3,'SE'),(4,'SD'),(8,'SB'),(5,'SA'),(4,'RW'),(3,'RU'),(3,'RS'),(3,'RO'),(5,'QA'),(2,'PY'),(3,'PT'),(1,'PR'),(3,'PL'),(5,'PK'),(5,'PH'),(8,'PG'),(8,'PF'),(2,'PE'),(1,'PA'),(8,'NZ'),(5,'NP'),(3,'NO'),(3,'NL'),(4,'NG'),(4,'NE'),(4,'NA'),(5,'MY'),(1,'MX'),(4,'MU'),(3,'MT'),(5,'MO'),(5,'MN'),(5,'MM'),(3,'MK'),(3,'ME'),(3,'MD'),(3,'MC'),(4,'MA'),(4,'LY'),(3,'LV'),(3,'LU'),(3,'LT'),(5,'LK'),(1,'LC'),(5,'LB'),(5,'KZ'),(1,'KY'),(5,'KW'),(5,'KR'),(5,'KP'),(5,'KH'),(4,'KE'),(5,'JP'),(5,'JO'),(1,'JM'),(3,'IT'),(3,'IS'),(5,'IR'),(4,'IO'),(5,'IN'),(5,'IL'),(3,'IE'),(5,'ID'),(3,'HU'),(3,'HR'),(1,'HN'),(5,'HK'),(1,'GT'),(3,'GR'),(1,'GP'),(3,'BY'),(1,'CA'),(4,'CM'),(1,'CR'),(3,'CY'),(3,'DK'),(2,'EC'),(3,'ES'),(3,'FR'),(3,'GE'),(3,'CH'),(4,'CI'),(2,'CL'),(5,'CN'),(2,'CO'),(1,'CU'),(3,'CZ'),(3,'DE'),(3,'FI'),(3,'FX'),(4,'GH'),(8,'CX'),(1,'DO'),(4,'DZ'),(3,'EE'),(4,'EG'),(3,'GB');"); DB::query("UNLOCK TABLES;"); DB::query("DROP TABLE IF EXISTS `Continent`;"); DB::query("CREATE TABLE `Continent` (\n `ID` int(11) NOT NULL AUTO_INCREMENT,\n `Name` varchar(32) NOT NULL,\n PRIMARY KEY (`ID`)\n) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;"); DB::query("LOCK TABLES `Continent` WRITE;"); DB::query("INSERT INTO `Continent` VALUES (1,'North America'),(2,'South America'),(3,'Europe'),(4,'Africa'),(5,'Asia'),(6,'Australia'),(7,'Antarctica'),(8,'Oceania');"); DB::query("UNLOCK TABLES;"); } else { echo "Migration Already Ran! <BR>"; } echo "Migration Done <BR>"; }
function up() { echo "Starting Migration Proc ...<BR>"; //check if migration already had ran ... $migration = Migration::get()->filter('Name', $this->title)->First(); if (!$migration) { //if not create migration and run it... $migration = new Migration(); $migration->Name = $this->title; $migration->Description = $this->description; $migration->Write(); //run migration proc //get migration data from cvs file, //this cvs file contains all Orgs that should be splitted on several //current affiliations $ds = new CvsDataSourceReader("\t"); $cur_path = Director::baseFolder(); $ds->Open($cur_path . "/openstack/code/migrations/data/orgs.csv"); $headers = $ds->getFieldsInfo(); $split_organizations = array(); //and stored those ones on a hash try { while (($row = $ds->getNextRow()) !== FALSE) { $id = $row[$headers["ID"]]; $split = $row[$headers["Split"]]; $name = $row[$headers["Name"]]; if ($split == 'True' || $split == 'SEMICOLON') { $split_organizations[$id] = array("Split" => $split, "Name" => $name); } } } catch (Exception $e) { $status = 0; } echo sprintf("Multiple Organization Count %d </BR>", count($split_organizations)); echo 'Procesing Members.... </BR>'; $count = DB::query("SELECT COUNT(ID) From Member Where OrgID IS NOT NULL;")->value(); echo sprintf("%d Members to process </BR>", $count); $page = 0; $page_size = 1000; $page_count = ceil($count / $page_size); for ($page = 0; $page <= $page_count; $page++) { echo sprintf("Procesing Member Page %d Of %d ...</BR>", $page, $page_count); $start_from = $page * $page_size; //get current org $res = DB::query("SELECT Member.ID AS MemberID, Org.ID AS OrgID, Org.Name AS OrgName , Org.IsStandardizedOrg, Member.JobTitle, Member.Role From Member INNER JOIN Org ON Org.ID=Member.OrgID Where OrgID IS NOT NULL LIMIT {$start_from}, {$page_size};"); $multi_org_id = null; foreach ($res as $record) { $curOrgID = $record["OrgID"]; $curMemberID = $record["MemberID"]; $curRole = $record["Role"]; $curJobTitle = $record["JobTitle"]; //is multiple current affiliations? if (isset($split_organizations[$curOrgID])) { $multi_org_id = $curOrgID; $org_aux = $split_organizations[$curOrgID]; $org_name = $org_aux["Name"]; $mode = $org_aux["Split"]; $affiliations = array(); switch ($mode) { case "True": $affiliations = explode(",", $org_name); break; case "SEMICOLON": $affiliations = explode(";", $org_name); break; } foreach ($affiliations as $a) { $a = Convert::raw2sql(trim($a)); $org_id = DB::query("SELECT ID From Org WHERE Name = '{$a}' AND ID <> {$curOrgID} ORDER BY ID ASC LIMIT 1")->value(); if (is_null($org_id)) { $org_id = DB::query("SELECT ID From Org WHERE Name Like '%{$a}%' AND ID <> {$curOrgID} ORDER BY ID ASC LIMIT 1")->value(); } if (is_null($org_id)) { //create Org $org_id = $this->writeOrg($a); } //create affiliation $this->writeAffiliation(null, null, null, null, true, $curMemberID, $org_id); //check if we have it on history repeated to get the from date $res2 = DB::query("SELECT NewAffiliation,OldAffiliation,Created,LastEdited FROM AffiliationUpdate WHERE MemberID={$curMemberID} AND NewAffiliation=OldAffiliation AND OldAffiliation='{$curOrgID}' ORDER BY Created ASC;"); if ($res2) { foreach ($res2 as $record2) { $this->updateAffiliationStartDate($record2['Created'], $curMemberID, $org_id); break; } } } } else { //single current affiliation //create affiliation $this->writeAffiliation(null, null, $curJobTitle, $curRole, true, $curMemberID, $curOrgID); } //now build affiliation history ... if (!is_null($multi_org_id)) { $res3 = DB::query("SELECT NewAffiliation,OldAffiliation,Created,LastEdited FROM AffiliationUpdate WHERE MemberID={$curMemberID} AND NewAffiliation<>'{$multi_org_id}' AND OldAffiliation<>'{$multi_org_id}' ORDER BY Created ASC;"); } else { $res3 = DB::query("SELECT NewAffiliation,OldAffiliation,Created,LastEdited FROM AffiliationUpdate WHERE MemberID={$curMemberID} ORDER BY Created ASC;"); } foreach ($res3 as $record3) { $old_org = Convert::raw2sql(trim($record3["OldAffiliation"])); $new_org = Convert::raw2sql(trim($record3["NewAffiliation"])); $created = $record3["Created"]; $new_org_id = $this->getOrgId($new_org); $old_org_id = $this->getOrgId($old_org); if (is_null($new_org_id)) { continue; } //create affiliation if ($new_org_id == $old_org_id || is_null($old_org_id)) { //check if current $count = DB::query("SELECT COUNT(ID) FROM Affiliation WHERE MemberID={$curMemberID} AND OrganizationID={$new_org_id} AND Current=1;")->value(); if ($count > 0) { $this->updateAffiliationStartDate($created, $curMemberID, $new_org_id); continue; } //check if does not not exists already $count = DB::query("SELECT COUNT(ID) FROM Affiliation WHERE MemberID={$curMemberID} AND OrganizationID={$new_org_id}")->value(); if ($count > 0) { continue; } //new one $this->writeAffiliation($created, null, null, null, false, $curMemberID, $new_org_id); } else { //try to update old affiliation $this->updateAffiliationEndDate($created, $curMemberID, $old_org_id); //check if current $count = DB::query("SELECT COUNT(ID) FROM Affiliation WHERE MemberID={$curMemberID} AND OrganizationID={$new_org_id} AND Current=1;")->value(); if ($count > 0) { $this->updateAffiliationStartDate($created, $curMemberID, $new_org_id); continue; } $this->writeAffiliation($created, null, null, null, false, $curMemberID, $new_org_id); } } } } } else { echo "Migration Already Ran! <BR>"; } echo "Migration Done <BR>"; }