(Terminal) Terminal (2010)

Remote SQL execute for PostgreSQL on GSM/GPRS channel with extreme compress and cryptography

PostgreSQL удобен для построения терминальных сетей по многим причинам, об одной из них я рассказал в топике - Мониторинг терминальной сети на PostgreSQL. PostgreSQL также имеет несколько механизмов репликации, Slony-I даже непосредственно вынесен в диалоги pgAdmin. В принципе репликационных решений для PostgreSQL существует много, но все они работают на хороших каналах связи и совсем не заточены на обрывы связи, встроенную криптографию и максимальную компрессию.

Поэтому я решился написать собственный модуль для собственного репликационного решения для PostgreSQL, тем более Network Application - я люблю писать не меньше, чем Desktop Application и Web Application. Самое защищенное мое решение подобного рода описано здесь WebActivator - клиент/сервер защиты от копирования для платных программ - грубо говоря это SSL на прикладном уровне со специальным механизмом квитирования и смены сессионных ключей). Там же лежат скрины, по которым видно, что это решение тоже не затачивалось на минимальный трафик и максимальную компрессию. Общий алгоритм моих защищенных сетевых драйверов - после первого обращения сервер выдает квитанцию и ничего более. Если квитанция принята, расшфрована и опять зашифрована клиентом верно, то сервер выполняет уже более существенные операции по запросу этого клиента. Это позволяет надежно защититься от DOS. На следующие шаги - более тяжелые для сервера и с возможными последствиями левому клиенту попать совершенно невозможно. В то время как мой драйвер защиты программ имеет 13 состояний протокола (те у первого IF в этом хандлере 13 больших веток), но в данной реализации для тонких GSM/GPRS каналов я публикую протокол без состояния - первый же POST от клиента считается осмысленной SQL-командой и подлежит исполнению на сервере (кстати это привело примерно к десятикратному уменьшения обьему кода относительно моих обычных защищенных драйверов с состоянием).

Также у меня на сайте выложено некоторые небольшие описания моих драйверов 2005-го года рождения, предназначенных для удаленных операций с MS SQL, есть описание драйвера оповещений об изменения в рекордсетах MS SQL, на которых работает система электронных магазинов http://digitalshop.ru/. Есть и еще более старые мои защищенные самописные протоколы для обмена данными в сетях, написанные например на ICSharpCode.SharpZipLib или решения на сборках MS SQL. В этом смысле - публикуемое ниже решение, хоть и простенькое, но хронологически последнее и вобравшее многолетний опыт написания моих самописных защищенных драйверов для обмена данными с сетях.

В качестве средства сжатия рекордсетов в этом решении я использовал 7zip. И результат превзошел ожидания - некоторые CSV c рекордсетами сжимаются в 10 раз и более. Поскольку я и так использую zip-компрессор, я решил не пользоваться дополнительными средствами шифрования результатов запросов, кроме парольной защиты Zip. Хотя ничего не стоит расширить этот протокол дополнительными шагами по выработке общего секрета - пароля zip-шифрования, но в данной публикуемой реализации для тонких GSM/GPRS каналов я максимально упростил протокол - общий симметричный секрет задается на сервере и клиентах статически. Это и позволило отказаться от наличия состояний в этом протоколе и предельно сократить служебный трафик для обслуживания протокола.

Второе направление расширения публикуемого ниже протокола, от которого я отказался в публикуемом ниже решении - докачка. Те передаваемый с сервера фрагмент можно разбить как разбиваются ZIP VOLUME, например по килобайту и менее. И продолжать передачу не с начала, а с зависшего фрагмента. Но для реализации этого придется перейти к полноценному протоколу с состоянием - те выдаче клиенту номера реквеста при первом обращении к серверу.

В данном решении кеши драйвером (как на клиенте, так и на сервере) не очищаются автоматически. Это можно сделать раз в месяц вручную или любой моей прогой, предназначенной для чистки кешей, например RemoveOldFile - утилита удаления устаревших файлов.

Работа любого моего защищенного протокола начинается с того, что сервер хранит RSA-ключи. Способ хранения RSA-ключей (и страничку для их менеджмента) я описал здесь - Этюды на ASP.NET. Пример сайта на СУБД PostgreSQL. Надеюсь вы понимаете, зачем я так сделал. Для чего мне пользоваться огромной пирамидой ПЛАТНОГО подписания открытых ключей шифрования, если я могу сгенерить их сам одной строчкой кода?

  14:              Dim RSA As New System.Security.Cryptography.RSACryptoServiceProvider()
Дьявольская надстройка в виде сертификатов, сделанная с целью заработать - ни имеет ни малейшего отношения собственно к защите данных. Кроме того, эти открытые/закрытые ключи шифрования ходят между драйверами - люди их вообще не видят. Поэтому применение для защищенного обмена данными SSL, основанного на платном подписании непонятно кем открытых ключей шифрования - это более чем моветон.

Откровенным маразмом также является криптозащита на российских криптоалгоритмах, я писал об этом здесь - Криптография по ГОСТ. Российское чудо-решение не только стоит от миллиона рублей и выше, не только требует лицензирования, не только требует подписания открытых ключей шифрования в ФСБ, но еще и абсолютно дырявое. Любой сотрудник ФСО за соответствующую мзду продаст вам маски мастер-ключа той или иной лицензированной им криптосистемы - именно таким образом на рынках оказываются криптографически защищенные базы о всех банковских перечислениях между юридическими лицами, обо всех остатках на корсчетах коммерческих банков в центробанке, о доходах юридических и физических лиц. Все эти базы по Закону защищаются российскими криптоалгоритмами и российскими чудо-ключами, сделанными на основе масок мастер-ключа, хранимого в ФСО. А ведь в чем математическая основа шифрования? Вы множите два простых числа друг на друга получаете результат, по которому невозможно определить что на что вы изначально множили. Но если вам выдали маску при лицензировании, те фактически один из множителей, то в чем смысл всей дальнейшей деятельности по шифрованию, когда один из множителей заведомо известен отдельным лицам - и заведомо известно что эти лица наладили заработки именно на продажах этой маски.

Именно поэтому в своих криптосистемах и криптопротоколах я не пользуюсь глупостями в виде платного подписания неизвестно кем моих открытых ключей шифрования, ни уж тем более российскими чудо-криптоалгоритмами и "закрытыми" чудо-ключами - за которые еще и заплатить государству надо от миллиона рублей и выше.

Итак, надеюсь я выложил достаточные обоснования публикуемого ниже решения - перейдем конкретнее к описанию кода.


Клиентская часть кода для обращения к удаленному PostgreSQL серверу состоят всего из двух модулей - RemoteExec - собственно драйвер защищенного компрессированного сетевого обмена с сервером и модуля InternetTransfer - низкоуровневые операции с сетью:


   1:      Dim RsaKey As String
   2:      Dim RSA As Security.Cryptography.RSACryptoServiceProvider
   3:      Dim RSAKeyInfo As Security.Cryptography.RSAParameters
   4:   
   5:      ''' <summary>
   6:      ''' Драйвер выполняет SQL-запрос на удаленном сервере PostgreSQL
   7:      ''' Возвращает сообщение об ошибке и (возможно) имя файла с рекордсетом в виде CSV (при ответе дравера 'OK')
   8:      ''' Помимо четырех параметров при вызове испозьзует пять параметров, заданных в конфигурации 
   9:      ''' My.Settings.ServerHandler = http://delmar.asp-net.ru/RemoteSQL.ashx = адрес конкретного хандлера сервера
  10:      ''' My.Settings.ZipPath = C:\Program Files (x86)\7-Zip\7z.exe = имя и патч зиппера  7-ZIP
  11:      ''' My.Settings.ZipCacheDir = E:\ = каталог с кешем работы по сети (каталог может быть почищен в любое время)
  12:      ''' My.Settings.ZipParm = e -pqwerty = режим работы 7zip и пароль, которым сервер шифрует передаваемых рекордсет
  13:      ''' My.Settings.ServerSecret = uiwhdihaciuay87ey3ho = для прохождения аутентификации на сервере
  14:      ''' </summary>
  15:      ''' <param name="SQL_CMD">SQL команда PostgreSQL, уделенно исполняемая на севрере</param>
  16:      ''' <param name="ServerRecordsetFileName">При ответе драйвера OK здесь отдается имя файла с рекордетом</param>
  17:      ''' ''' <remarks>Буфера работы по сети автоматически не удаляются</remarks>
  18:      Function RemoteSqlDrv(ByVal SQL_CMD As String, ByRef ServerRecordsetFileName As String, ByVal NewKey As Boolean) As String
  19:          '
  20:          'Первый реквест - просим симметричный ключ
  21:          Dim UTF8 = New System.Text.UTF8Encoding
  22:          '
  23:          If NewKey Then
  24:              Try
  25:                  RsaKey = GetContents1(My.Settings.ServerHandler)
  26:                  'читаем ассимметричный ключ, сформированный сервером 
  27:                  '<RSAKeyValue><Modulus>rkq0QoDbsMgT/dUgeTr05ex+W1UXjP59LhvFVpPMnN4V060ePlYPcy6kRKcbcehPvWtVKh2kYlJ7qpXwhSOmsVFCrK4iajVYf1btT8+1I44+9iI0vqLKxn2wlCMgR31IhYZDNochuRknx/1NGQibbbbHPNExw4BzS2vl9xyETfM=</Modulus><Exponent>AQAB</Exponent></RSAKeyValue>"
  28:              Catch ex As Exception
  29:                  Return ("Не  удалось прочитать открытый ключ шифрования сервера" & vbCrLf & My.Settings.ServerHandler & vbCrLf & ex.Message)
  30:              End Try
  31:              Try
  32:                  RSA = New Security.Cryptography.RSACryptoServiceProvider()
  33:                  RSAKeyInfo = New Security.Cryptography.RSAParameters()
  34:                  RSA.FromXmlString(RsaKey)
  35:              Catch ex As Exception
  36:                  Return ("Не работает криптопровайдер " & vbCrLf & ex.Message)
  37:              End Try
  38:          End If
  39:          '
  40:          'Отсылаем зашифрованный запрос методом пост
  41:          Dim ReqiestPacket As String = My.Settings.ServerSecret & vbCrLf & SQL_CMD
  42:          Dim Post_Data_Bytes() As Byte = UTF8.GetBytes(ReqiestPacket)
  43:          Dim Crypto_Post_Data_Bytes() As Byte
  44:          Try
  45:              Crypto_Post_Data_Bytes = RSA.Encrypt(Post_Data_Bytes, False)
  46:          Catch ex As Exception
  47:              Return ("Не удалось зашифровать запрос серверу." & vbCrLf & My.Settings.ServerHandler & vbCrLf & ReqiestPacket & vbCrLf & ex.Message)
  48:          End Try
  49:          '
  50:          'Получаем ответ и сохраняем его во временный файл
  51:          Dim NetTempFileName As String = Guid.NewGuid.ToString & ".zip"
  52:          Dim FullNetFileName As String = IO.Path.Combine(My.Settings.ZipCacheDir, NetTempFileName)
  53:          Dim OutTempFileName As String = Guid.NewGuid.ToString & ".log"
  54:          Try
  55:              PostRequest(My.Settings.ServerHandler, Crypto_Post_Data_Bytes, FullNetFileName)
  56:          Catch ex As Exception
  57:              Return ("Не удалось прочитать ответ от сервера." & vbCrLf & My.Settings.ServerHandler & vbCrLf & ReqiestPacket & vbCrLf & FullNetFileName & vbCrLf & ex.Message)
  58:          End Try
  59:          '
  60:          If Not My.Computer.FileSystem.FileExists(FullNetFileName) Then
  61:              'ничего не получено из сети
  62:              Return ("Server response empty")
  63:          Else
  64:              Dim TestResponse As New IO.FileStream(FullNetFileName, IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.Read)
  65:              Dim FirstByte As Integer = TestResponse.ReadByte() ' ANSI dec_55  = hex_37 = char_7
  66:              Dim SecondByte As Integer = TestResponse.ReadByte  ' ANSI dec_122 = hex_7A = char_z
  67:              TestResponse.Close()
  68:              'получен ZIP-файл с зашифрованным рекордсетом в виде CSV или код ошибки?
  69:              If FirstByte <> 80 Or SecondByte <> 75 Then   'UTF8
  70:                  'получен код возврата из хандлера
  71:                  Dim HaddlerError As String = My.Computer.FileSystem.ReadAllText(FullNetFileName)
  72:                  Return ("Server message: " & HaddlerError)
  73:              Else
  74:                  'определяем имя файла, который содержится в полученном зашифрованном архиве
  75:                  Dim ZipPath1 As String = My.Settings.ZipPath  'C:\Program Files (x86)\7-Zip\7z.exe 
  76:                  Dim ZipParm1 As String = " l " & FullNetFileName
  77:                  Dim ZipCacheDir1 As String = My.Settings.ZipCacheDir 'E:\
  78:                  Dim Zip1 As New System.Diagnostics.Process
  79:                  Zip1.StartInfo.RedirectStandardOutput = True
  80:                  Zip1.StartInfo.UseShellExecute = False
  81:                  Zip1.StartInfo.CreateNoWindow = True
  82:                  Dim ZipLog As String
  83:                  Try
  84:                      Zip1.StartInfo.FileName = ZipPath1
  85:                      Zip1.StartInfo.Arguments = ZipParm1
  86:                      Zip1.StartInfo.WorkingDirectory = ZipCacheDir1
  87:                      Zip1.Start()
  88:                      Zip1.WaitForExit()
  89:                      ZipLog = Zip1.StandardOutput.ReadToEnd()
  90:                      Zip1.Close()
  91:                  Catch ex As Exception
  92:                      Return ("ZIP не удалось определить содержимое ZIP-архива")
  93:                  End Try
  94:                  'парсим журнал, чтобы найти имя файла
  95:                  If Len(ZipLog) < 40 Then
  96:                      Return ("ZIP не удалось определить имя файла, содержащееся в ZIP-архиве")
  97:                  Else
  98:                      Dim Pos2 As Integer = ZipLog.IndexOf(".txt")
  99:                      If Pos2 = 0 Or Pos2 < 40 Then
 100:                          Return ("ZIP не удалось определить имя файла, содержащееся в ZIP-архиве.")
 101:                      Else
 102:                          Dim OutFileName As String = Mid(ZipLog, Pos2 - 35, 40)
 103:                          Dim FullOutFileName As String = IO.Path.Combine(My.Settings.ZipCacheDir, OutFileName)
 104:                          Dim ZipPath As String = My.Settings.ZipPath  'C:\Program Files (x86)\7-Zip\7z.exe 
 105:                          Dim ZipParm As String = My.Settings.ZipParm  'e -pqwerty
 106:                          Dim ZipCacheDir As String = My.Settings.ZipCacheDir 'E:\
 107:                          Dim Zip As New System.Diagnostics.Process
 108:                          Zip.StartInfo.UseShellExecute = False
 109:                          Zip.StartInfo.CreateNoWindow = True
 110:                          'Раззиповывываем и возвращаем имя раззипованного файла
 111:                          Try
 112:                              Zip.StartInfo.FileName = ZipPath
 113:                              Zip.StartInfo.Arguments = ZipParm & " " & FullNetFileName
 114:                              Zip.StartInfo.WorkingDirectory = ZipCacheDir
 115:                              Zip.Start()
 116:                              Zip.WaitForExit()
 117:                              Zip.Close()
 118:                              If My.Computer.FileSystem.FileExists(FullOutFileName) Then
 119:                                  ServerRecordsetFileName = FullOutFileName
 120:                                  Return ("OK")
 121:                              Else
 122:                                  'нет результата исполнения команды
 123:                                  Return ("ZIP output empty")
 124:                              End If
 125:                          Catch ex As Exception
 126:                              Return ("Не удалось раззиповать файл." & vbCrLf & My.Settings.ServerHandler & vbCrLf & ReqiestPacket & vbCrLf & FullNetFileName & vbCrLf & FullOutFileName & vbCrLf & ex.Message)
 127:                          End Try
 128:                      End If
 129:                  End If
 130:              End If
 131:          End If
 132:      End Function
 133:  End Module

   1:  Module InternetTransfer
   2:   
   3:      'считывает HTML-странички по HTTP без сообщений при ошибках - возвращает строку
   4:      Public Function GetContents1(ByVal URL As String) As String
   5:          Try
   6:              'запрос по HTTP
   7:              Dim PageRequest As System.Net.HttpWebRequest = CType(System.Net.WebRequest.Create(URL), System.Net.HttpWebRequest)
   8:              'Отправлен запрос
   9:              Dim PageResponse As System.Net.HttpWebResponse = PageRequest.GetResponse
  10:              'Получен ответ
  11:              Dim Reader As New System.IO.StreamReader(PageResponse.GetResponseStream(), System.Text.Encoding.Default)
  12:              Dim HTML As String = Reader.ReadToEnd
  13:              Reader.Close()
  14:              'Загружено в память
  15:              Return HTML
  16:          Catch x As System.Exception
  17:              'пусть молча идет дальше при ошибках
  18:              'MsgBox("Ошибка: " & x.Message & " " & x.Source) 
  19:          End Try
  20:      End Function
  21:   
  22:      'Запрос странички методом POST (молча, ошибки обрабатываются извне этого кода)
  23:      Public Sub PostRequest(ByVal URL As String, ByVal POST_Data() As Byte, ByVal OutFileName As String)
  24:          '========== System.NotSupportedException The URI prefix is not recognized.
  25:          Dim request As Net.HttpWebRequest = Net.HttpWebRequest.Create(URL)
  26:          request.Method = "POST"
  27:          request.ContentType = "application/x-www-form-urlencoded"
  28:          request.ContentLength = POST_Data.Length
  29:          request.Timeout = 100000
  30:          '========== System.Net.WebExceptionStatus.Timeout Unable to connect to the remote server
  31:          Dim POST_Stream As IO.Stream = request.GetRequestStream()
  32:          POST_Stream.Write(POST_Data, 0, POST_Data.Length)
  33:          POST_Stream.Close()
  34:          '
  35:          'ждем
  36:          '========== System.Net.WebException.Timeout
  37:          '========== System.Net.WebException = "The remote server returned an error: (404) Not Found."
  38:          Dim response As Net.HttpWebResponse = request.GetResponse()
  39:          Dim GET_Stream As IO.Stream = response.GetResponseStream()
  40:          Dim Reader = New IO.BinaryReader(GET_Stream)
  41:          Dim Buf1(1000) As Byte
  42:          While Reader.BaseStream.CanRead
  43:              Buf1 = Reader.ReadBytes(Buf1.Length)
  44:              If Buf1.Length > 0 Then
  45:                  My.Computer.FileSystem.WriteAllBytes(OutFileName, Buf1, True)
  46:              Else
  47:                  Exit Sub
  48:              End If
  49:          End While
  50:      End Sub
  51:   
  52:   
  53:  End Module

Протестировать этот драйвер можно так:




   1:      Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
   2:          TST1("select * from ""МаркаАвто""")
   3:      End Sub
   4:   
   5:      Sub TST1(ByVal RemoteCMD)
   6:          Dim CSVfileName As String = ExecRemoteSQL(RemoteCMD,  True)
   7:          If CSVfileName IsNot Nothing Then
   8:              Dim CSVstring As String = My.Computer.FileSystem.ReadAllText(CSVfileName)
   9:              MsgBox(CSVstring)
  10:          End If
  11:      End Sub

Как видите, я на клиенте получил рекордест с удаленного PostgreSQL сервера в виде CSV-файла. Далее этот рекордсет можно либо распотрошить собственным кодом, как строку, либо целиком загрузить в базу одним ПГ-оператором COPY. Это и есть основа репликационного решения один_ко_многим - клиенты вычитывают с сервера то, что им надо и пополняют свою базу данными, которые лежат на сервере.

Давайте еще раз поймем в чем смысл этого решения по сравнению с простейшей PosrgreSQL-процедурой на dblink, сетевой трафик при работе которой выглядит примерно вот так. Это криптографическая защищенность и уплотнение по трафику до 10 раз (в среднем не менее 3). А это как раз то, что нужно для защищенной передачи по тонким линиям связи.


Теперь посмотрим как устроен сервер. Еnrollment ключей шифрования является обязательным компонентом этого решения. Ну и естественно, этот хандлер использует ту же обертку вокруг postgresql, как и страничка энролмента ключей шифрования. Эта страничка описана здесь - Этюды на ASP.NET. Пример сайта на СУБД PostgreSQL.

И вот собственно код моего хандлера:


   1:  <%@ WebHandler Language="VB" Class="RemoteSQL" %>
   2:   
   3:  Imports System
   4:  Imports System.Web
   5:   
   6:  Public Class RemoteSQL : Implements IHttpHandler, IRequiresSessionState
   7:      
   8:      
   9:      Dim RequestType As ProtocolStep
  10:      Dim Symm_Key(32) As Byte, Symm_IV(16) As Byte
  11:      Enum ProtocolStep
  12:          SendAsymmentricKey = 1
  13:      End Enum
  14:      
  15:      Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
  16:         
  17:          PG_Safe_Connection(context)
  18:          
  19:          If context.Current.Request.RequestType = "GET" Then
  20:              'Это GET-запрос без параметров -  выдаем всем желающим только свой публичный ключ шифрования
  21:              If context.Current.Request.QueryString.Keys.Count = 0 Then
  22:                  RequestType = ProtocolStep.SendAsymmentricKey
  23:                  'вообще-то это лишь самый первый шаг длинного протокола
  24:                  Dim PublicKey As String = GetPublicKey()
  25:                  context.Response.ContentType = "text/Xml"
  26:                  context.Response.Write(PublicKey)
  27:                  Exit Sub
  28:              End If
  29:   
  30:          ElseIf context.Current.Request.RequestType = "POST" Then
  31:              'это POST-запрос
  32:              Dim UTF8 = New System.Text.UTF8Encoding
  33:              Dim PrivateKey As String
  34:              '
  35:              'прочитали из базы секретный ключ
  36:              Try
  37:                  PrivateKey = GetPrivateKey(context)
  38:              Catch ex As Exception
  39:                  context.Response.ContentType = "text/plain"
  40:                  context.Response.Write("BAD Secret key:" & ex.Message)
  41:              End Try
  42:              '
  43:              'теперь расшифруем данные в Post
  44:              Dim Decrypt_Post_Data_Bytes() As Byte
  45:              Try
  46:                  Decrypt_Post_Data_Bytes = ReadPostData_And_RSADecrypt(context, PrivateKey)
  47:              Catch ex As Exception
  48:                  context.Response.ContentType = "text/plain"
  49:                  context.Response.Write("BAD DATA:" & ex.Message)
  50:              End Try
  51:              '
  52:              'вынимаем из них ServerSecret и SQL-CMD
  53:              Dim SourceString As String = UTF8.GetString(Decrypt_Post_Data_Bytes)
  54:              Dim LineReader As New IO.StringReader(SourceString)
  55:              Dim ServerSecret As String = LineReader.ReadLine
  56:              Dim RemoteSQLCommand As String = LineReader.ReadLine                              'Select * from "МаркаАвто"
  57:   
  58:              'проверяем права доступа к серверу
  59:              If Not ServerSecretIsValid() Then
  60:                  context.Response.ContentType = "text/plain"
  61:                  context.Response.Write("BAD Server Secret")
  62:              Else
  63:                  'проверим перед исполнением корректность команды
  64:                  If RemoteSQLCommand.ToLower.Contains("delete") Or RemoteSQLCommand.ToLower.Contains("update") Then
  65:                      context.Response.ContentType = "text/plain"
  66:                      context.Response.Write("Permission denied")
  67:                  Else
  68:                      'предварительная проверка команды на корректность перед исполнением
  69:                      Try
  70:                          PG1.PG.ExecRDR("EXPLAIN " & RemoteSQLCommand)                        'EXPLAIN Select * from "МаркаАвто"
  71:                      Catch ex As Exception
  72:                          context.Response.ContentType = "text/plain"
  73:                          context.Response.Write("BAD SQL COMMAND:" & ex.Message)
  74:                          Exit Sub
  75:                      End Try
  76:                      '
  77:                      'исполняем полученную команду
  78:                      '
  79:                      Dim WorkingDir As String = context.Server.MapPath(ConfigurationManager.AppSettings("Cache").ToString)                   'G:\Projects\DelmarDisk\www\Temp
  80:                      Dim ZipOutFileName As String = Guid.NewGuid.ToString & ".zip"               '4d67eb4a-7f06-463b-b914-b51c7cb837d5.zip
  81:                      Dim FullZipFileName As String = IO.Path.Combine(WorkingDir, ZipOutFileName) 'G:\Projects\DelmarDisk\www\Temp\4d67eb4a-7f06-463b-b914-b51c7cb837d5.zip
  82:                      Dim CSVFileName As String = Guid.NewGuid.ToString & ".txt"                  '2acdd007-4879-419b-886d-ccb120a45e92.txt
  83:                      Dim FullCSVFileName As String = IO.Path.Combine(WorkingDir, CSVFileName)    'G:\Projects\DelmarDisk\www\Temp\2acdd007-4879-419b-886d-ccb120a45e92.txt
  84:                      Dim FullPostgresCMD As String = "COPY (" & RemoteSQLCommand & ") TO '" & FullCSVFileName & "' DELIMITER ';';"
  85:                      Try
  86:                          PG1.PG.ExecScalar(FullPostgresCMD.Replace("\", "\\"))                   'COPY (Select * from "МаркаАвто") TO 'G:\Projects\DelmarDisk\www\Temp\2acdd007-4879-419b-886d-ccb120a45e92.txt' DELIMITER ';';
  87:                      Catch ex As Exception
  88:                          context.Response.ContentType = "text/plain"
  89:                          context.Response.Write("SQL_ERROR:" & ex.Message)
  90:                          Exit Sub
  91:                      End Try
  92:                      '
  93:                      If Not My.Computer.FileSystem.FileExists(FullCSVFileName) Then
  94:                          context.Response.ContentType = "text/plain"
  95:                          context.Response.Write("SQL_OUT_FILE_NOTHING")
  96:                      Else
  97:                          'зашифровываем и зипуем ее результат
  98:                          Dim ZipPath As String = ConfigurationManager.AppSettings("ZipPath").ToString  'C:\Program Files (x86)\7-Zip\7z.exe 
  99:                          Dim ZipParm As String = ConfigurationManager.AppSettings("ZipParm").ToString  'a -pqwerty
 100:                          Dim Zip As New System.Diagnostics.Process
 101:                          Try
 102:                              Zip.StartInfo.FileName = ZipPath
 103:                              Zip.StartInfo.Arguments = ZipParm & " " & FullZipFileName & " " & FullCSVFileName
 104:                              Zip.StartInfo.WorkingDirectory = WorkingDir
 105:                              Zip.StartInfo.UseShellExecute = False
 106:                              Zip.Start()
 107:                              Zip.WaitForExit()
 108:                              Zip.Close()
 109:                          Catch ex As Exception
 110:                              context.Response.ContentType = "text/plain"
 111:                              context.Response.Write("ZIP_ERROR:" & ex.Message)
 112:                              Exit Sub
 113:                          End Try
 114:                          '
 115:                          If Not My.Computer.FileSystem.FileExists(FullZipFileName) Then
 116:                              context.Response.ContentType = "text/plain"
 117:                              context.Response.Write("ZIP_FILE_NOTHING")
 118:                          Else
 119:                              'пишем результат в поток браузера
 120:                              Try
 121:                                  context.Response.ContentType = "application/octet-stream"
 122:                                  context.Response.BinaryWrite(My.Computer.FileSystem.ReadAllBytes(FullZipFileName))
 123:                              Catch ex As Exception
 124:                                  'тут можно только в рельсу постучать, но на всякий случай
 125:                                  context.Response.ContentType = "text/plain"
 126:                                  context.Response.Write("WRITE_ERROR:" & ex.Message)
 127:                              End Try
 128:                          End If
 129:                      End If
 130:                  End If
 131:              End If
 132:          End If
 133:      End Sub
 134:       
 135:      Dim PG1 As PG1.SQL_Postgres
 136:      Sub PG_Safe_Connection(ByVal context As HttpContext)
 137:          If context.Current.Session("PG1") IsNot Nothing Then
 138:              PG1 = context.Current.Session("PG1")
 139:          Else
 140:              PG1 = New PG1.SQL_Postgres
 141:              context.Current.Session("PG1") = PG1
 142:          End If
 143:          PG1.CheckConnect()
 144:      End Sub
 145:   
 146:      Function CheckServerSecret() As Boolean
 147:          Return False
 148:      End Function
 149:      
 150:      'Достать из базы сеекретный ключ RSA
 151:      Private Function GetPrivateKey(ByVal context As HttpContext) As String
 152:          Dim PrivateKey As String
 153:          Dim RDR1 As Npgsql.NpgsqlDataReader = PG1.PG.ExecRDR("select ""PrivateKey"" from ""RSA_KEY"" order by i desc limit 1")
 154:          If RDR1.Read Then
 155:              PrivateKey = RDR1("PrivateKey")
 156:          End If
 157:          RDR1.Close()
 158:          Return PrivateKey
 159:      End Function
 160:      
 161:      'Достать из базы публичный ключ RSA
 162:      Private Function GetPublicKey() As String
 163:          Dim PublicKey As String
 164:          Dim RDR1 As Npgsql.NpgsqlDataReader = PG1.PG.ExecRDR("select ""PublicKey"" from ""RSA_KEY"" order by i desc limit 1")
 165:          If RDR1.Read Then
 166:              PublicKey = RDR1("PublicKey")
 167:          End If
 168:          RDR1.Close()
 169:          Return PublicKey
 170:      End Function
 171:   
 172:      
 173:      'RSA-расшифровка
 174:      Private Function ReadPostData_And_RSADecrypt(ByVal context As HttpContext, ByVal PrivateKey As String) As Byte()
 175:          '"The data to be decrypted exceeds the maximum for this modulus of 128 bytes."
 176:          Dim RSA As New System.Security.Cryptography.RSACryptoServiceProvider()
 177:          RSA.FromXmlString(PrivateKey)
 178:          Dim Post_Data_Bytes(context.Current.Request.InputStream.Length - 1) As Byte
 179:          context.Current.Request.InputStream.Read(Post_Data_Bytes, 0, context.Current.Request.InputStream.Length)
 180:          Return RSA.Decrypt(Post_Data_Bytes, False)
 181:      End Function
 182:          
 183:      Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
 184:          Get
 185:              Return False
 186:          End Get
 187:      End Property
 188:   
 189:      Function ServerSecretIsValid() As Boolean
 190:          Return True
 191:      End Function
 192:      
 193:  End Class

Этот хандлер предельно упрощен для публикации - тут например вместо проверки подлинности реквеста (ServerSecretIsValid) стоит просто заглушка. Хотя енролнмент этих ключей доступа к серверу - это тоже тема, как вы понимаете. Предположим, одностороння функция от времени и идентификационного номера терминала.

Для работы хандлера требуется правильный конфиг, ключевой фрагмент которого выглядит вот так:


   1:  .......
   2:      <appSettings>
   3:          <add key="Cache" value="Temp1" />
   4:          <add key="ZipPath" value="C:\Program Files (x86)\7-Zip\7z.exe" />
   5:          <add key="ZipParm" value=" a -pqwerty" />
   6:      </appSettings>
   7:      <connectionStrings>
   8:          <add name="SQLServer_ConnectionStrings" connectionString="HOST=10.10.10.2;PORT=5432;PROTOCOL=3;DATABASE=Disk;USER ID=postgres;POOLING=True;CONNECTIONLIFETIME=0;MINPOOLSIZE=1;MAXPOOLSIZE=1024;COMMANDTIMEOUT=200;"
               providerName="Npgsql"/>
   9:      </connectionStrings>
  10:      <system.web>
  11:          <compilation debug="true" strict="false" explicit="true">
  12:              <assemblies>
  13:                  <add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  14:                  <add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
  15:                  <add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  16:                  <add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
  17:                  <add assembly="Npgsql, Version=2.0.8.0, Culture=neutral, PublicKeyToken=5D8B90D52F46FDA7"/>
  18:              </assemblies>
  19:          </compilation>
  20:          <pages enableSessionState="true">
  21:  .........

Надеюсь, теперь понятно, как на этих низкоуровневых функциях, позволяющих выполнять запросы на удаленном PostgreSQL-сервере можно сделать репликацию один_сервер_много_терминалов. Терминалы должны вычитывать обновления серверной базы и дозаполнять свою локальную базу. В принципе можно и через этот же связной механизм прогонять обновления на сервере, но я намеренно ограничил в коде эту возможность. В моей ситуации это ограничение является элементом защиты - фактически серверная база доступна через этот связной механизм только для чтения.

Для того, чтобы надстроить над этим драйвером репликацию - надо всего лишь добавить такую вот функцию, которая можно вызывать для для каждой таблицы сервера, которую надо реплицировать на клиенте.


   1:      Public Delegate Sub PerformOneRow(ByVal column As String())
   2:   
   3:      Sub PerformEachRow(ByVal RemoteSQLCommand As String, ByVal ExecOneRow As PerformOneRow, ByVal NewKey As Boolean)
   4:          Dim CSVfileName As String = ExecRemoteSQL(RemoteSQLCommand, NewKey)
   5:          Dim CSVstring As String
   6:          If CSVfileName Is Nothing Then
   7:              MsgBox("Не прочитался " & vbCrLf & RemoteSQLCommand)
   8:              Exit Sub
   9:          End If
  10:          '
  11:          CSVstring = My.Computer.FileSystem.ReadAllText(CSVfileName)
  12:          If Len(CSVstring) = 0 Then
  13:              MsgBox("Нет данных в " & vbCrLf & RemoteSQLCommand)
  14:              Exit Sub
  15:          End If
  16:          '
  17:          Dim RDR2 As New IO.StringReader(CSVstring)
  18:          Dim Line As String
  19:          Dim Col As String()
  20:          While True
  21:              Line = RDR2.ReadLine()
  22:              If Len(Line) = 0 Then Exit While
  23:              Col = Line.Split(";")
  24:              For Each OneStr As String In Col
  25:                  If OneStr.Length = 0 Then OneStr = "''"
  26:              Next
  27:              ExecOneRow.Invoke(Col)
  28:          End While
  29:          RDR2.Close()
  30:      End Sub
  31:   
  32:      ''' <summary>
  33:      ''' Возвращает для обработки одну последнюю строку рекодсета
  34:      ''' </summary>
  35:      Function PerformLastRow(ByVal RemoteSQLCommand As String) As String()
  36:          Dim CSVfileName As String = ExecRemoteSQL(RemoteSQLCommand, True)
  37:          Dim CSVstring As String
  38:          If CSVfileName Is Nothing Then
  39:              MsgBox("Не прочитался " & vbCrLf & RemoteSQLCommand)
  40:              Exit Function
  41:          End If
  42:          '
  43:          CSVstring = My.Computer.FileSystem.ReadAllText(CSVfileName)
  44:          If Len(CSVstring) = 0 Then
  45:              MsgBox("Нет данных в " & vbCrLf & RemoteSQLCommand)
  46:              Exit Function
  47:          End If
  48:          '
  49:          Dim RDR2 As New IO.StringReader(CSVstring)
  50:          Dim Line As String
  51:          Dim Col As String()
  52:          While True
  53:              Line = RDR2.ReadLine()
  54:              If Len(Line) = 0 Then Exit While
  55:              Col = Line.Split(";")
  56:          End While
  57:          RDR2.Close()
  58:          Return Col
  59:      End Function

Как видите эта функция вызывает делегат вышележащего слоя софта, которым обрабатывается каждая строка серверного рекордсета (либо просто последняя строка серверного рекордсета). Соответственно на самом выскоком уровне софта - репликация выглядит буквально вызовом одной строчки и одной строчкой делегата.

Архитектура реплицируемых таблиц может быть весьма сложной, вложенной. В таком случае в калбеке надо делать новые и новые запросы, например вот так:


   1:  ....
   2:              PerformEachRow("select * from ""СвойстваТовара"" where toimport=" & ServerImportID.ToString, AddressOf Add_СвойстваТовара_OneRow, True)
   3:              PerformEachRow("select * from ""ТоварныйКлассификатор"" where toimport=" & ServerImportID.ToString, AddressOf Add_ТоварныйКлассификатор_OneRow, False)
   4:  ....
   5:   
   6:   
   7:      Sub Add_СвойстваТовара_OneRow(ByVal Col As String())
   8:          Add_СвойстваТовара(PG, ClientImportID, Col(2), Col(3))
   9:      End Sub
  10:   
  11:      Sub Add_ТоварныйКлассификатор_OneRow(ByVal Col As String())
  12:          Dim ServerTovarID As Integer
  13:          ClientTovarID = Add_ТоварныйКлассификатор(PG, ClientImportID, Col(2), Col(3), Col(4), Col(5), Col(6), Col(7), Col(8), Col(9), Col(10), Col(11))
  14:          ServerTovarID = Col(0)
  15:          '
  16:          PerformEachRow("select * from ""ЗначенияСвойствТовара"" where totovar=" & ServerTovarID.ToString, AddressOf Add_ЗначенияСвойствТовара_OneRow, False)
  17:          PerformEachRow("select * from ""ЗначенияРеквизитовТовара"" where totovar=" & ServerTovarID.ToString, AddressOf Add_ЗначенияРеквизитовТовара_OneRow, False)
  18:      End Sub
  19:   
  20:      Dim ClientTovarID As Integer
  21:   
  22:      Sub Add_ЗначенияСвойствТовара_OneRow(ByVal Col As String())
  23:          Add_ЗначенияСвойствТовара(PG, ClientImportID, Col(2), Col(3))
  24:      End Sub
  25:   
  26:      Sub Add_ЗначенияРеквизитовТовара_OneRow(ByVal Col As String())
  27:          Add_ЗначенияРеквизитовТовара(PG, ClientImportID, Col(2), Col(3))
  28:      End Sub

Когда идут массовые запросы на репликацию, конечно нет необходимости без конца вычитывать один и тот же открытый ключ сервера (в тексте выше я его вычитал в строке 2, а затем просто использую для шифрования). Так GSM/GPRS трафик еще более сокращается и выглядит вот так (красным видны RSA-шифрованные запросы серверу, а синим ZIP-шифрованные ответы сервера:



Описанный монитор репликации у меня применен для терминальной сети, чтобы терминалы вычитывали всякие полезные сведения для своей работы с сервера терминалов. На описанном драйвере можно построить полностью распределенную систему - чтобы любой терминал мог принять на себя роль сервера. Для этого он только должен быть на хорошем канале (например подключен по локалке) и зарегистрировать себя в DynDns.org




Надеюсь публикация этого кода будет полезной для развития OpenSource технологий. Как видите, я по возможности почти из каждого своего проекта беру 1-2 процента наиболее общественно-полезного кода и стараюсь опубликовать его для всех.

Вы можете скачать весь описанный здесь OpenSource код в уже откомпилированном виде отсюда и сразу использовать описанный здесь OpenSource код в своих проектах.



Комментарии к этой страничке ( )
ссылка на эту страничку: http://www.vb-net.ru/RemoteSQLexecute/index.htm
<Назад>  <На главную>  <В раздел ASP>  <В раздел NET>  <В раздел SQL>  <В раздел Разное>  <Написать автору>  < Поблагодарить>