Amadis

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

It can be useful for some reasons (for instance when moving to an older 1.0.x version to a newer 1.1.x version) to manually migrate / copy the data from one database to the other. To do so only a few command lines are required.

Dumping the older database content

First connect to the older instance database container:

docker exec -it samdatabase_1.0.10 /bin/sh

Don’t forget to adapt the container name (samdatabase_1.0.10 here) to your actual setup.

Then dump the content of users, SMDevices and SMDeviceModels tables:

mysqldump -u root -p --single-transaction sambackend users SMDevices SMDeviceModels > dump.sql

You will then be prompter to enter the root password to access the database.

This will create an SQL file called dump.sql with the content of the tables.

If you added personal data into other tables simply add them at the end of the command, just before the '>' character.

Now to retrieve the file on you local machine, exit the container shell and use the following command:

docker cp database:dump.sql dump.sql

Change the table names and columns

Some tables have been renamed when moving to the new version of the product. In particular SMDevices changed into sm_devices and SMDeviceModels changed into sm_device_models. The dump.sql file need to be updated to reflect that change. This can be done either manually using a proper file editor or with some Linux-style commands such as:

sed -i 's/SMDevices/sm_devices/g' dump.sql
sed -i 's/SMDeviceModels/sm_device_models/g' dump.sql

The sm_devices table contains a new smrdSecurityId column that needs to be added to the file.

Update the table creation command to the following:

DROP TABLE IF EXISTS `sm_devices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sm_devices` (
  `smrdId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id of the corresponding Devices row.',
  `smrdTerminalId` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Terminal Id.  Used to identify the device within the API',
  `smrdIMEI` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'IMEI number of the device',
  `smrdModel` int(11) NOT NULL COMMENT 'Model of the device.',
  `smrdState` int(11) NOT NULL DEFAULT '1' COMMENT 'State of the device.',
  `smrdCreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time when the row was created.',
  `smrdUpdatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time when the row was last updated.',
  `smrdDeletedAt` datetime DEFAULT NULL COMMENT 'Date and time when the row was deleted.',
  `smrdSecurityId` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Security ID of the device, unique per hardware unit.',
  PRIMARY KEY (`smrdId`),
  KEY `IXFK_Devices_DeviceModels` (`smrdModel`),
  KEY `FK_sm_devices_SMDeviceStates` (`smrdState`),
  KEY `FK_SMDevices_SMDeviceSecurityIds` (`smrdSecurityId`),
  CONSTRAINT `FK_Devices_DeviceModels` FOREIGN KEY (`smrdModel`) REFERENCES `sm_device_models` (`smdmId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_sm_devices_SMDeviceStates` FOREIGN KEY (`smrdState`) REFERENCES `SMDeviceStates` (`smdsId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Registered devices';
/*!40101 SET character_set_client = @saved_cs_client */;

The modification only consists in the addition of lines 13 and 17.

Loading the old database content into the new one

First push the file into the newer instance of the database container:

docker cp dump.sql database:dump.sql

Then load the content into the database:

mysql -u root -p sambackend < dump.sql

  • No labels