EventLogLoader/EventLogLoaderService/Service1.vb

Imports System.Threading
Imports System.Globalization
Imports System.Data.SqlClient
Imports System.IO
Imports MySql.Data.MySqlClient
Imports Nest
Imports Newtonsoft.Json

Public Class EventLogLoaderService

    Dim ListOfProcessors As List(Of EventLogProcessor) = New List(Of EventLogProcessor)
    Dim ArrayThread() As Threading.Thread
    Dim ConfigSettingObj As ConfigSetting = New ConfigSetting

    Dim ConnectionString As String
    Dim DBType As String
    Dim ItIsMSSQL As Boolean = False
    Dim ItIsMySQL As Boolean = False
    Dim ItIsES As Boolean = False

    Public SleepTime As Integer = 60 * 1000 '1 минута

    Function LoadConfigSetting()

        LoadConfigSetting = False

        Try

            Dim PathConfigFile = Path.Combine(My.Application.Info.DirectoryPath, "config.json")
            If My.Computer.FileSystem.FileExists(PathConfigFile) Then

                ConfigSettingObj = ConfigSettingsModule.LoadConfigSettingFromFile(PathConfigFile)

                ConnectionString = ConfigSettingObj.ConnectionString

                DBType = ConfigSettingObj.DBType
                If DBType = "MySQL" Then
                    ItIsMySQL = True
                ElseIf DBType = "MS SQL Server" Then
                    ItIsMSSQL = True
                ElseIf DBType = "ElasticSearch" Then
                    ItIsES = True
                End If

                Dim s = ConfigSettingObj.RepeatTime
                SleepTime = s * 1000

                For Each IBConfig In ConfigSettingObj.Infobases

                    Dim IB_ID = IBConfig.DatabaseID
                    Dim IB_Name = IBConfig.DatabaseName
                    Dim IB_Catalog = IBConfig.DatabaseCatalog

                    Dim EventLogProcessorObj = New EventLogProcessor
                    EventLogProcessorObj.Log = NLog.LogManager.GetLogger("CurrentThread")
                    EventLogProcessorObj.InfobaseGuid = IB_ID
                    EventLogProcessorObj.InfobaseName = IB_Name
                    EventLogProcessorObj.Catalog = IB_Catalog
                    EventLogProcessorObj.ConnectionString = ConnectionString
                    EventLogProcessorObj.SleepTime = SleepTime
                    EventLogProcessorObj.ItIsMSSQL = ItIsMSSQL
                    EventLogProcessorObj.ItIsMySQL = ItIsMySQL
                    EventLogProcessorObj.ItIsES = ItIsES
                    EventLogProcessorObj.ESIndexName = ConfigSettingObj.ESIndexName
                    EventLogProcessorObj.ESIndexPostfix = ConfigSettingObj.ESUseIndexPostfix
                    EventLogProcessorObj.ESServerName = IBConfig.ESServerName
                    EventLogProcessorObj.ESUseSynonymsForFieldsNames = ConfigSettingObj.ESUseSynonymsForFieldsNames
                    EventLogProcessorObj.ESFieldSynonyms = ConfigSettingObj.ESFieldSynonyms

                    Try
                        EventLogProcessorObj.LoadEventsStartingAt = Date.Parse(IBConfig.StartDate)
                    Catch ex As Exception
                        EventLogProcessorObj.LoadEventsStartingAt = New Date(1900, 1, 1)
                    End Try


                    ListOfProcessors.Add(EventLogProcessorObj)

                Next

                LoadConfigSetting = True
            Else
                Log.Error("File config.json was not found!")
            End If
        Catch ex As Exception
            Log.Error(ex, "Parameters cannot be loaded from config.json (it may be corrupted)")
        End Try


    End Function

    Private WorkerThread As Thread
    Private Log As NLog.Logger

    Protected Overrides Sub OnStart(ByVal args() As String)
        ' Добавьте здесь код запуска службы. Этот метод должен настроить все необходимое
        ' для успешной работы службы.
        SubStart()
    End Sub

    Protected Overrides Sub OnStop()
        ' Добавьте здесь код для завершающих операций перед остановкой службы.
        SubStop()
    End Sub

    Public Sub SubStart()
        WorkerThread.Start()
    End Sub

    Public Sub SubStop()
        WorkerThread.Abort()
    End Sub

    Public Sub New()

        ' Этот вызов является обязательным для конструктора.
        InitializeComponent()

        ' Добавьте все инициализирующие действия после вызова InitializeComponent().

        'Нужно для того, что служба создавала логи в своем каталоге
        Directory.SetCurrentDirectory(My.Application.Info.DirectoryPath)

        Log = NLog.LogManager.GetLogger("CurrentThread")
        'Log.Info("Создание объекта сервиса")

        WorkerThread = New Threading.Thread(AddressOf DoWork)
        WorkerThread.SetApartmentState(Threading.ApartmentState.STA)


    End Sub

    Private Sub DoWork()

        'Log.Info("Запуск основного функционала")

        If Not LoadConfigSetting() Then
            Log.Error("Error while working with config.json file in application folder")
            Environment.Exit(-1)
        End If

        CreateTables()

        Dim i = 0

        For Each IB In ListOfProcessors

            Try
                IB.GetInfobaseIDFromDatabase()
            Catch ex As Exception
                Log.Error(ex, "Error occurred while getting infobase ID from target database (" + IB.InfobaseName + ")")
                Continue For
            End Try

            Try
                Dim Thead = New Threading.Thread(New ParameterizedThreadStart(AddressOf IB.DoWork))
                Thead.IsBackground = True
                Thead.Start()

                ReDim Preserve ArrayThread(i)

                ArrayThread(i) = Thead

                i = i + 1
            Catch ex As Exception
                Log.Error(ex, "Error occurred while starting new thread (" + IB.InfobaseName + ")")
            End Try

        Next

    End Sub


    Sub CreateTables()

        Try
            If ItIsMSSQL Then

                Dim objConn As New SqlConnection(ConnectionString)
                objConn.Open()

                Dim command As New SqlCommand("IF NOT EXISTS (select * from sysobjects where id = object_id(N'Events'))
                                                BEGIN
                                                  CREATE TABLE [dbo].[Events]([InfobaseCode] int Not NULL, [DateTime] [datetime] Not NULL, 
                                                        [TransactionStatus] [varchar](1) NULL,	
                                                        [TransactionStartTime] [datetime] NULL, 
                                              		    [TransactionMark] bigint NULL,	
                                                        [Transaction] [varchar](100) NULL,	
                                              		    [UserName] int NULL,	
                                              	        [ComputerName] int NULL,	
                                              		    [AppName] Int NULL, 
                                                        [EventID] int NULL, 
                                              		    [EventType] [varchar](1) NULL,	
                                                        [Comment] [nvarchar](max) NULL,	
                                              		    [MetadataID] int NULL,	
                                              		    [DataStructure] [nvarchar](max) NULL, 
                                              		    [DataString] [nvarchar](max) NULL,	
                                                        [ServerID] int NULL, 
                                              		    [MainPortID] int NULL,	
                                                        [SecondPortID] int NULL, 
                                              		    [Seance] int NULL);	
                                                    CREATE CLUSTERED INDEX [CIX_Events] ON [dbo].[Events]([InfobaseCode], [DateTime])
                                                END", objConn)
                command.ExecuteNonQuery()

                '**********************************************************************************
                'command.CommandText = "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Params'))" + vbNewLine + _
                '                    "	CREATE TABLE [dbo].[Params] ([InfobaseCode] int NOT NULL, [Position] bigint, [Filename] [char](100), [LastEventID] int);" + vbNewLine + _
                '                    " IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Params') AND Name = 'ClusteredIndex')" + vbNewLine + _
                '                    " CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Params] ([InfobaseCode] ASC);"
                'command.ExecuteNonQuery()
                '**********************************************************************************
                command.CommandText = "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Infobases'))" + vbNewLine +
                                    "	CREATE TABLE [dbo].[Infobases] ([Guid] [char](40) NOT NULL, [Code] int NOT NULL, [Name] [char](100))" + vbNewLine +
                                    " IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Infobases') AND Name = 'ClusteredIndex')" + vbNewLine +
                                    " CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Infobases] ([Guid] ASC);"
                command.ExecuteNonQuery()
                '**********************************************************************************
                command.CommandText =
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Users'))" + vbNewLine +
                    "CREATE TABLE [dbo].[Users]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100), [Guid] [varchar](40));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Users') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Users] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Metadata'))" + vbNewLine +
                    "CREATE TABLE [dbo].[Metadata]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100), [Guid] [varchar](40));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Metadata') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Metadata] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Computers'))" + vbNewLine +
                    "CREATE TABLE [dbo].[Computers]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Computers') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Computers] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Applications'))" + vbNewLine +
                    "CREATE TABLE [dbo].[Applications]([InfobaseCode] int NOT NULL, [Code] int NOT NULL,[Name] [nvarchar](100));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Applications') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Applications] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'EventsType'))" + vbNewLine +
                    "CREATE TABLE [dbo].[EventsType]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](max));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'EventsType') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[EventsType] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'Servers'))" + vbNewLine +
                    "CREATE TABLE [dbo].[Servers]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'Servers') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Servers] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'MainPorts'))" + vbNewLine +
                    "CREATE TABLE [dbo].[MainPorts]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'MainPorts') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[MainPorts] ([InfobaseCode] ASC, [Code] ASC);" + vbNewLine +
                    "" +
                    "IF NOT EXISTS (select * from sysobjects where id = object_id(N'SecondPorts'))" + vbNewLine +
                    "CREATE TABLE [dbo].[SecondPorts]([InfobaseCode] int NOT NULL, [Code] int NOT NULL, [Name] [nvarchar](100));" + vbNewLine +
                    "IF NOT EXISTS (select * from sys.indexes where object_id = object_id(N'SecondPorts') AND Name = 'ClusteredIndex')" + vbNewLine +
                    "CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[SecondPorts] ([InfobaseCode] ASC, [Code] ASC);"

                command.ExecuteNonQuery()
                '**********************************************************************************

                command.CommandText = "SELECT TOP 1 * FROM Events"
                command.ExecuteReader()

                command.Dispose()
                objConn.Close()
                objConn.Dispose()

            ElseIf ItIsMySQL Then

                Dim objConn = New MySql.Data.MySqlClient.MySqlConnection(ConnectionString)
                objConn.Open()

                Dim DBName = objConn.Database

                Dim command = New MySql.Data.MySqlClient.MySqlCommand
                command.Connection = objConn

                command.CommandText = "CREATE TABLE IF NOT EXISTS `Events` (`InfobaseCode` int(11) NOT NULL, `DateTime` int(11) NOT NULL," +
                    "`TransactionStatus` varchar(1) NULL, `TransactionStartTime` datetime NULL,	" +
                    "`TransactionMark` bigint NULL, `Transaction` varchar(100) NULL,	`UserName` int(11) NULL, `ComputerName` int(11) NULL,	" +
                    "`AppName` int(11) NULL, `EventID` int(11) NULL, `EventType` varchar(1) NULL,	" +
                    "`Comment` text NULL, `MetadataID` int(11) NULL,	`DataStructure` text NULL, `DataString` text NULL,	" +
                    "`ServerID` int(11) NULL, `MainPortID` int(11) NULL,	`SecondPortID` int(11) NULL, `Seance` int(11) NULL" +
                    ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                command.ExecuteNonQuery()
                '**********************************************************************************
                'command.CommandText = "CREATE TABLE IF NOT EXISTS `Params` (`InfobaseCode` int(11) NOT NULL, `Position` bigint, `Filename` varchar(100)," + _
                '                        "`LastEventID` int(11), PRIMARY KEY `InfobaseCode` (`InfobaseCode`));"
                'command.ExecuteNonQuery()
                '**********************************************************************************
                command.CommandText = "CREATE TABLE IF NOT EXISTS `Infobases` (`Guid` varchar(40) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100)," +
                                        "PRIMARY KEY `Guid` (`Guid`));"
                command.ExecuteNonQuery()
                '**********************************************************************************
                command.CommandText =
                    "CREATE TABLE IF NOT EXISTS `Users`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), `Guid` varchar(40), PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `Metadata`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), `Guid` varchar(40), PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `Computers`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `Applications`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `EventsType`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` text, PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `Servers`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `MainPorts`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));" + vbNewLine +
                    "" +
                    "CREATE TABLE IF NOT EXISTS `SecondPorts`(`InfobaseCode` int(11) NOT NULL, `Code` int(11) NOT NULL, `Name` varchar(100), PRIMARY KEY (`InfobaseCode`, `Code`));"

                command.ExecuteNonQuery()
                '**********************************************************************************



                command.Dispose()
                objConn.Close()
                objConn.Dispose()

            End If

            Log.Info("Target database tables have been verified!")

        Catch ex As Exception

            Log.Error(ex, "Error occurred while during target database tables verification")

        End Try

    End Sub


End Class