PIbd-21_MasenkinMS_SUBD_RouteGuide/RouteGuide/RouteGuideMongoDBImplement/DataTransfer.cs

using MongoDB.Bson;
using MongoDB.Driver;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RouteGuideMongoDBImplement
{
	/// <summary>
	/// Класс для переноса данных между БД
	/// </summary>
	public class DataTransfer
	{
		/// <summary>
		/// Параметры подключения к базе данных PostgreSQL
		/// </summary>
		private readonly string _pgsqlDBConnectionString = "Host=192.168.0.108;Port=5432;Database=RouteGuide;Username=postgres;Password=2004";

		/// <summary>
		/// Параметры подключения к базе данных MongoDB
		/// </summary>
		private readonly string _mongoDBConnectionString = "mongodb://localhost:27017";
		
		/// <summary>
		/// Название базы данных
		/// </summary>
		private readonly string _databaseName = "RouteGuideDatabase";

		/// <summary>
		/// Клиент MongoDB
		/// </summary>
		public readonly MongoClient _client;

		/// <summary>
		/// База данных MongoDB
		/// </summary>
		private readonly IMongoDatabase _database;

		/// <summary>
		/// Коллекция "Водители"
		/// </summary>
		private IMongoCollection<BsonDocument> Drivers => _database.GetCollection<BsonDocument>("Drivers");

		/// <summary>
		/// Коллекция "Транспорт"
		/// </summary>
		private IMongoCollection<BsonDocument> Transport => _database.GetCollection<BsonDocument>("Transport");

		/// <summary>
		/// Коллекция "Маршруты"
		/// </summary>
		private IMongoCollection<BsonDocument> Routes => _database.GetCollection<BsonDocument>("Routes");

		/// <summary>
		/// Коллекция "Остановки"
		/// </summary>
		private IMongoCollection<BsonDocument> Stops => _database.GetCollection<BsonDocument>("Stops");

		/// <summary>
		/// Связь для сущностей "Маршруты" и "Остановки"
		/// </summary>
		private IMongoCollection<BsonDocument> RouteStops => _database.GetCollection<BsonDocument>("RouteStops");

		/// <summary>
		/// Коллекция "Расписания"
		/// </summary>
		private IMongoCollection<BsonDocument> Schedules => _database.GetCollection<BsonDocument>("Schedules");

		/// <summary>
		/// Конструктор
		/// </summary>
		public DataTransfer()
		{
			_client = new MongoClient(_mongoDBConnectionString);
			_database = _client.GetDatabase(_databaseName);
		}

		/// <summary>
		/// Начать перенос данных
		/// </summary>
		public void SyncData()
		{
			// Переносим данные
			Transfer("Drivers");
			Transfer("Transport");
			Transfer("Routes");
			Transfer("Stops");
			Transfer("RouteStops");
			Transfer("Schedules");

			// Настраиваем корректные связи
			var transportCollection = Transport.Find(Builders<BsonDocument>.Filter.Empty).ToList();
			var routesCollection = Routes.Find(Builders<BsonDocument>.Filter.Empty).ToList();
			var schedulesCollection = Schedules.Find(Builders<BsonDocument>.Filter.Empty).ToList();

			// Связь между сущностями "Транспорт" и "Водитель"
			transportCollection.ForEach(t =>
			{
				int tempDriverId = t.GetValue("DriverId").ToInt32();
				var driverId = Drivers
					.Find(Builders<BsonDocument>.Filter.Eq("Id", tempDriverId))
					.FirstOrDefaultAsync()
					.Result.GetValue("_id")
					.ToString();
				var driver = Drivers
					.Find(Builders<BsonDocument>.Filter.Eq("Id", tempDriverId))
					.Project(Builders<BsonDocument>.Projection.Exclude("Id"))
					.FirstOrDefault();

				var filter = Builders<BsonDocument>.Filter.Eq("_id", t.GetValue("_id"));
				var updateDriverId = Builders<BsonDocument>.Update.Set("DriverId", driverId);
				var updateDriver = Builders<BsonDocument>.Update.Set("Driver", driver);
				Transport.UpdateOneAsync(filter, updateDriverId);
				Transport.UpdateOneAsync(filter, updateDriver);
			});

			// Связь между сущностями "Маршрут" и "Транспорт"
			routesCollection.ForEach(r =>
			{
				int tempTransportId = r.GetValue("TransportId").ToInt32();
				var transportId = Transport
					.Find(Builders<BsonDocument>.Filter.Eq("Id", tempTransportId))
					.FirstOrDefaultAsync()
					.Result.GetValue("_id")
					.ToString();
				var transport = Transport
					.Find(Builders<BsonDocument>.Filter.Eq("Id", tempTransportId))
					.Project(Builders<BsonDocument>.Projection.Exclude("Id"))
					.FirstOrDefault();

				var filter = Builders<BsonDocument>.Filter.Eq("_id", r.GetValue("_id"));
				var updateTransportId = Builders<BsonDocument>.Update.Set("TransportId", transportId);
				var updateTransport = Builders<BsonDocument>.Update.Set("Transport", transport);
				Routes.UpdateOneAsync(filter, updateTransportId);
				Routes.UpdateOneAsync(filter, updateTransport);
			});

			// Связь между сущностями "Маршруты" и "Остановки"
			routesCollection.ForEach(r =>
			{
				var tempRouteId = r.GetValue("Id").ToInt32();
				var routeStops = RouteStops
					.Find(Builders<BsonDocument>.Filter.Eq("RouteId", tempRouteId))
					.ToList();

				var updateStops = new Dictionary<string, (BsonDocument, int)>();
				routeStops.ForEach(rs =>
				{
					var tempStopId = rs.GetValue("StopId").ToInt32();
					var stop = Stops
						.Find(Builders<BsonDocument>.Filter.Eq("Id", tempStopId))
						.Project(Builders<BsonDocument>.Projection.Exclude("Id"))
						.FirstOrDefault();

					if (stop != null)
					{
						var stopId = stop.GetValue("_id").ToString();
						var stopNumber = rs.GetValue("Number").ToInt32();
						updateStops.Add(stopId!, (stop, stopNumber));
					}
				});

				var filter = Builders<BsonDocument>.Filter.Eq("_id", r.GetValue("_id"));
				var update = Builders<BsonDocument>.Update.Set("Stops", updateStops);
				Routes.UpdateOneAsync(filter, update);
			});

			// Связь между сущностями "Расписание" и "Маршрут"
			schedulesCollection.ForEach(s =>
			{
				int tempRouteId = s.GetValue("RouteId").ToInt32();
				var routeId = Routes
					.Find(Builders<BsonDocument>.Filter.Eq("Id", tempRouteId))
					.FirstOrDefaultAsync()
					.Result.GetValue("_id")
					.ToString();
				var route = Routes
					.Find(Builders<BsonDocument>.Filter.Eq("Id", tempRouteId))
					.Project(Builders<BsonDocument>.Projection.Exclude("Id"))
					.FirstOrDefault();

				var filter = Builders<BsonDocument>.Filter.Eq("_id", s.GetValue("_id"));
				var updateRouteId = Builders<BsonDocument>.Update.Set("RouteId", routeId);
				var updateRoute = Builders<BsonDocument>.Update.Set("Route", route);
				Schedules.UpdateOneAsync(filter, updateRouteId);
				Schedules.UpdateOneAsync(filter, updateRoute);
			});

			// Убираем поле "Id"
			var update = Builders<BsonDocument>.Update.Unset("Id");
			Drivers.UpdateMany(new BsonDocument(), update);
			Transport.UpdateMany(new BsonDocument(), update);
			Routes.UpdateMany(new BsonDocument(), update);
			Stops.UpdateMany(new BsonDocument(), update);
			Schedules.UpdateMany(new BsonDocument(), update);

			// Удаление коллекции для связи сущностей "Маршруты" и "Остановки"
			RouteStops.Database.DropCollection("RouteStops");
		}

		/// <summary>
		/// Перенос данных из таблицы в коллекцию
		/// </summary>
		/// <param name="collectionName"></param>
		private void Transfer(string collectionName)
		{
			using var connection = new NpgsqlConnection(_pgsqlDBConnectionString);
			connection.Open();
			using var command = new NpgsqlCommand($"SELECT * FROM \"{collectionName}\"", connection);
			using var reader = command.ExecuteReader();

			var collection = _database.GetCollection<BsonDocument>(collectionName);

			while (reader.Read())
			{
				var document = new BsonDocument();
				for (int i = 0; i < reader.FieldCount; i++)
				{
					string fieldName = reader.GetName(i);
					object value = reader.GetValue(i);
					document.Add(fieldName, BsonValue.Create(value));
				}
				collection.InsertOne(document);
			}

			reader.Close();
			command.Cancel();
			connection.Close();
		}
	}
}