C# automatically upgrade backup database when using FreeSQL production environment

Time:2021-11-29
catalogue
  • Project scenario:
  • Solution:
    • Summary description:
  • detailed description:
    • Summary:

      Project scenario:

      Using FreeSQL, including all ORM databases, will have such problems. In the codefirst mode, the database is automatically updated according to the code. It is recommended not to use it in the production environment. Why?
      In fact, it is not recommended. It is mainly because when automatically generating data according to the code, it is very likely to cause data loss. For example, if you modify the field type, the result of automatic update may not be what you want.
      However, some usage scenarios need to be upgraded automatically in the production environment, such as
      We have a CS client product, which is used locally offline and deployed locally. The database is also a local database. The version has iterated from 1000 to 1100, and 100 versions have been released in the middle. There may be multiple database changes in between. Our customers may also be all over the country, and the versions are different. If customers do not have new requirements, they may always use the current old version. They will upgrade the version only when they have new requirements or want to use new functions. Therefore, the upgrade time is uncertain. The upgrade requires customers to install a new version of software and automatically upgrade after running.
      Can it really not be used in the production environment?

      Solution:

      Summary description:

      In fact, the solution is to upgrade automatically, but only when you judge that you need to upgrade, and back up the database before upgrading.

      Specific process
      Every time there is a database change in the program, the corresponding version in the program shall be modified when the version is released. For example, the first is 1000 and the latest is 1100
      Add the sysconfig table in the database. The field contains dbver, indicating the current database version number
      Add the dblog table in the database to record the database upgrade log. This option is optional
      During the first installation, check that the database file does not exist, indicating the first installation. During the first installation, create the sysconfig table and dblog table, and update the sysconfig table dbver to record the version number in the program. Add dblog table log
      When running again later, first obtain the sysconfig table dbver to judge whether it is consistent with the program,
      If the database is larger than that in the program, it means that the program with lower version is running, and it can be prohibited according to the situation. You can also not synchronize the database and continue to run, which is determined according to the actual situation. If the requirements for program and database consistency are relatively high, it can be prohibited to run.
      If the database is smaller than the program, it indicates that the database needs to be upgraded. At this time, first back up the existing database, and then synchronize the database.

      detailed description:

      Directly on the code, better than anything

      Program.cs file code

      using Bonn.Helper;
      using System;
      using System.Collections.Generic;
      using System.IO;
      using System.Linq;
      using System.Threading.Tasks;
      using System.Windows.Forms;
      using FreeSql.DataAnnotations;
      using WindowsClient.Model;
      using System.Reflection;
      
      namespace WindowsClient
      {
          static class Program
          {
              /// <summary>
              ///Customer database path
              /// </summary>
              Private static string custdbpath = application. Startuppath + $"\ \ database \ \ cust. DB";
      
              /// <summary>
              ///Database ORM
              /// </summary>
              public static IFreeSql fsql;
      
              /// <summary>
              ///Server database version
              /// </summary>
              private static int ServerDbVer = 1000;
      
      
              /// <summary>
              ///The main entry point for the application.
              /// </summary>
              [STAThread]
              static void Main()
              {
                  try
                  {
      
                      //Whether the database exists for inserting initial data must be determined before FreeSQL instantiation, because the database will be created automatically during instantiation
                      var custDbPathExists = File.Exists(CustDbPath);
      
                      //Deebug automatically synchronizes the entity structure to the database and release manually
                      bool syncDbStructure = false;
      #if DEBUG
                      syncDbStructure = true;
      #endif
      
                      fsql = new FreeSql.FreeSqlBuilder()
                          .UseConnectionString(FreeSql.DataType.Sqlite, [email protected]"Data Source={CustDbPath}; Pooling=true;Min Pool Size=1")
                          .UseAutoSyncStructure(syncDbStructure) //Deebug automatically synchronizes the entity structure to the database and release manually
                          . usemonitorcommand (CMD = > console. Writeline ($"Thread: {CMD. Commandtext} \ R \ n"))
                          .Build(); // Be sure to define singleton singleton mode
      
                      if(syncDbStructure)
                      {
                          //It is mainly used in the development mode to make database modifications take effect quickly. If this sentence is not added, it will be synchronized only when the table is used
                          fsql.CodeFirst.SyncStructure(GetTypesByTableAttribute());
                      }
      
                      if (custDbPathExists == false)
                      {
                          //The database file does not exist, indicating that it is the first installation
                          fsql.CodeFirst.SyncStructure(GetTypesByTableAttribute());
                          var sysConfig = new SysConfig();
                          sysConfig.DbVer = ServerDbVer;
                          var dbResult = fsql.Insert(sysConfig).ExecuteAffrows();
                          if (dbResult <= 0)
                              Throw new exception ("initial database failed");
      
                          var row = new DbLog();
                          row.DbVer = ServerDbVer;
                          fsql.Insert(row).ExecuteAffrows();
                      }
                      int localDbVer = fsql.Select<SysConfig>().First().DbVer;
                      if (localDbVer != ServerDbVer)
                      {
                          //The database version is different and needs to be upgraded
                          //Backup database
                          File. Copy (custdbpath, application. Startuppath + $"\ \ database \ \ cust{datetime. Now: yyyymmddhhmmss}. DB");
                          //Upgrade database
                          fsql.CodeFirst.SyncStructure(GetTypesByTableAttribute());
                          var row = new DbLog();
                          row.DbVer = ServerDbVer;
                          fsql.Insert(row).ExecuteAffrows();
                      }
      
                      Application.EnableVisualStyles();
                      Application.SetCompatibleTextRenderingDefault(false);
                      Application.Run(new FrmMain());
                  }
                  catch (Exception e)
                  {
                      MessageBox. Show (e.tostring(), "error", messageboxbuttons. OK, messageboxicon. Error);
                  }
              }
      
              public static Type[] GetTypesByTableAttribute()
              {
                  List<Type> tableAssembies = new List<Type>();
                  foreach (Type type in Assembly.GetAssembly(typeof(IEntity)).GetExportedTypes())
                  {
                      foreach (Attribute attribute in type.GetCustomAttributes())
                      {
                          if (attribute is TableAttribute tableAttribute)
                          {
                              if (tableAttribute.DisableSyncStructure == false)
                              {
                                  tableAssembies.Add(type);
                              }
                          }
                      }
                  };
                  return tableAssembies.ToArray();
              }
          }
      }

      SysConfig.cs

      using FreeSql.DataAnnotations;
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
      
      namespace WindowsClient.Model
      {
          /// <summary>
          /// 
          /// </summary>
          [Table(Name = "sys_config")]
          public class SysConfig : IEntity
          {
              /// <summary>
              ///Primary key
              /// </summary>
              [Column(Name = "id", IsIdentity = true, IsPrimary = true)]
              public int Id { get; set; }
      
              /// <summary>
              ///Primary key
              /// </summary>
              [Column(Name = "dbVer")]
              public int DbVer { get; set; }
      
              /// <summary>
              ///Creation time
              /// </summary>
              [Column(ServerTime = DateTimeKind.Local, CanUpdate = false)]
              public DateTime CreateTime { get; set; }
      
              /// <summary>
              ///Modification time
              /// </summary>
              [Column(ServerTime = DateTimeKind.Local, CanUpdate = true)]
              public DateTime UpdateTime { get; set; }
      
          }
      }

      DbLog.cs

      using FreeSql.DataAnnotations;
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
      
      namespace WindowsClient.Model
      {
          /// <summary>
          /// 
          /// </summary>
          [Table(Name = "db_log")]
          public class DbLog : IEntity
          {
              /// <summary>
              ///Primary key
              /// </summary>
              [Column(Name = "id", IsIdentity = true, IsPrimary = true)]
              public int Id { get; set; }
      
              /// <summary>
              ///Primary key
              /// </summary>
              [Column(Name = "dbVer")]
              public int DbVer { get; set; }
      
              /// <summary>
              ///Creation time
              /// </summary>
              [Column(ServerTime = DateTimeKind.Local, CanUpdate = false)]
              public DateTime CreateTime { get; set; }
      
              /// <summary>
              ///Modification time
              /// </summary>
              [Column(ServerTime = DateTimeKind.Local, CanUpdate = true)]
              public DateTime UpdateTime { get; set; }
          }
      }

      Summary:

      It used to be handwritten SQL statements, but now it’s much more convenient to use FreeSQL.

      The above is c# the details of using FreeSQL production environment to automatically upgrade the backup database. For more information about c# using FreeSQL to automatically upgrade the backup database, please pay attention to other related articles of developeppaer!

      Recommended Today

      On the mutation mechanism of Clickhouse (with source code analysis)

      Recently studied a bit of CH code.I found an interesting word, mutation.The word Google has the meaning of mutation, but more relevant articles translate this as “revision”. The previous article analyzed background_ pool_ Size parameter.This parameter is related to the background asynchronous worker pool merge.The asynchronous merge and mutation work in Clickhouse kernel is completed […]