Get configuration version 1c directly from SQL

On duty, our office serves several organizations that use 1c for management and accounting.
1c, as is known, constantly releases updates for its configurations.
Accordingly, updating at least 5 bases takes a decent amount of time.
The story of how to achieve complete (except for downloading updates) process automation with MSSQL tools below.


Automate the process start from the "end"

In 1c there is a command line: but its parameters
The catch is as follows:

• In 1c update is possible only from a specific version to a specific one. This is due to the fact that the update files are not delivered in the form of a full "cast" configuration. And in the form of changes from the reference version.
• Also in the 1c code itself there are predefined treatments that run when switching from one version to another.

')
So we need to get the current version of 1c.
There is of course the option of using 1c-COMConnector, which establishes the connection for at least 2 seconds, “Eats” an extra license, memory, and drinks my coffee.
I went the other way. In versions 1c> 8.0 there is no database directory, therefore, the MSSQL database stores everything.
In database tables there is a _config table, and in it there is a line with FileName = root. Look at the contents (Binary Data):
{2,e0666db2-45d6-49b4-a200-061c6ba7d569}
Content is stored in compressed form. Below is an example of unpacking it.
By the method of scientific spear we find the same table there is an entry with FileName = e0666db2-45d6-49b4-a200-061c6ba7d569 (the same identifier):
{2,
{1e4190e9-76c2-456e-a607-4d817110ffd9},6,
{9cd510cd-abfc-11d4-9434-004095e12fc7,
{1,
{36,
{0,
{0,
{0,3,6b021b70-482d-4baf-b590-b86b68d4730e},"",
{1,"ru"," , 2.5"},""}
},"",1,
{1,"ru"," , 2.5"},
{1,"ru"," , 2.5"},
{1,"ru","Copyright () ""1C"", 2007-2011. "},
{1,"ru","http://www.1c.ru"},
{1,"ru","http://v8.1c.ru/hrm/"},72ef09b4-4393-4d23-a530-7e5b50cf1d24,31e2aa98-4a9c-4d99-a3e7-540a13396b8c,1e7940af-fba5-46c3-8ffb-e8389ac2b79f,87090b31-2cd0-4b45-8b24-efdb2383b1a2,1," ""1""","2.5.40.3","http://downloads.v8.1c.ru/tmplts/",1,
(only the most interesting part of the file is shown)

You probably guessed what this 1c configuration is.
Now you need to explain this to MS SQL.
In c #, I wrote a class:

 using System; using System.Collections.Generic; using System.Text; using System.IO; namespace metadata { class v8metadata { public object[] array_data; public v8metadata[] child; public v8metadata parent; public string filename; private Decoder d; public v8metadata(MemoryStream ms, string file_name, v8metadata p,Decoder dec) { d = dec; filename = file_name; array_data = new object[0]; child = new v8metadata[0]; parent = p; parse_ms(ms, true); } //     MD  1  7.7 //     public v8metadata(MemoryStream ms, string file_name,Decoder dec) { d = dec; filename = file_name; array_data = new object[0]; child = new v8metadata[0]; if (parent == null) ms.Position = 0; parse_ms(ms); } public v8metadata(MemoryStream ms, string file_name) { d = Encoding.UTF8.GetDecoder(); filename = file_name; array_data = new object[0]; child = new v8metadata[0]; if (parent == null) ms.Position = 0; parse_ms(ms); } public object get(int pos) { return array_data[pos]; } public string get(string descr) { string res = "error wrong param"; if (descr == "version") try { res = ((v8metadata)(((v8metadata)(((v8metadata)(this.array_data[3])).array_data[1])).array_data[1])).array_data[15].ToString(); } catch { res = ""; } else if (descr == "vendor") res = ((v8metadata)(((v8metadata)(((v8metadata)(this.array_data[3])).array_data[1])).array_data[1])).array_data[14].ToString(); else if (descr == "conf") res = ((v8metadata)(((v8metadata)(((v8metadata)(((v8metadata)(((v8metadata)(this.array_data[3])).array_data[1])).array_data[1])).array_data[1])).array_data[1])).array_data[2].ToString(); if ((res.Substring(0, 1) == "\"") && (res.Substring(res.Length - 1) == "\"")) { res = res.Substring(1); res = res.Substring(0, res.Length - 1); } return res.Replace("\"\"", "\""); } public string ToStr(string lev) { string ret = ""; for (int i = 0; i < this.array_data.Length; i++) { ret = ret+ lev +"_" + i.ToString() + ":"; if (this.array_data[i].GetType().FullName == "System.String") ret = ret + " " + this.array_data[i].ToString() + @" "; else ret = ret + ((v8metadata)this.array_data[i]).ToStr(lev + "_" + i.ToString()) + @" "; } return ret; } private void parse_ms(MemoryStream ms) { parse_ms(ms, false); } private int parse_ms(MemoryStream ms, Boolean findbegin) { int cur; byte[] bytedata = new byte[0]; //for (int i = Convert.ToInt16(ms.Position); i < ms.Length; i++) while (ms.Position < ms.Length) { cur = ms.ReadByte(); //  if (cur == 13) { cur = ms.ReadByte(); if (cur == 10) continue; else { Addtoarraybyte(ref bytedata, 13); Addtoarraybyte(ref bytedata, cur); } } if (cur == 123) { if (findbegin) { adddata(bytedata); adddata(new v8metadata(ms, filename, this,d)); } else { findbegin = true; bytedata = new byte[0]; } continue; } if (cur == 44) { // adddata(bytedata); bytedata = new byte[0]; continue; } if (cur == 125) break; Addtoarraybyte(ref bytedata, cur); } adddata(bytedata); return 0; } private static void Addtoarraybyte(ref byte[] arr, int val) { byte[] tmp = new byte[arr.Length + 1]; Array.Copy(arr, 0, tmp, 0, arr.Length); arr = tmp; arr[arr.Length - 1] = Convert.ToByte(val); } private void adddata(byte[] bytedata) { if (bytedata.Length == 0) return; char[] cs = new char[d.GetCharCount(bytedata, 0, bytedata.Length)]; d.GetChars(bytedata, 0, bytedata.Length, cs, 0); //  string a_data = new string(cs); object[] tmp = new object[array_data.Length + 1]; Array.Copy(array_data, 0, tmp, 0, array_data.Length); array_data = tmp; array_data[array_data.Length - 1] = a_data; } private void adddata(v8metadata childdata) { object[] tmp = new object[array_data.Length + 1]; Array.Copy(array_data, 0, tmp, 0, array_data.Length); array_data = tmp; array_data[array_data.Length - 1] = childdata; addv8metadata(childdata); } private void addv8metadata(v8metadata childdata) { v8metadata[] tmp1 = new v8metadata[child.Length + 1]; Array.Copy(child, 0, tmp1, 0, child.Length); child = tmp1; child[child.Length - 1] = childdata; } } } 

When creating a class, pass the MemoryStream binary data from the _config table.
As you can see in the code, they can parse and configuration 7.7, before unpacking

Now you can get to the configuration version by knowing its “address”: Next, just create an empty database with the “VERSION” version and find its “address” (v8metadata) (((v8metadata) (((v8metadata)) (this.array_data [3]) ) .array_data [1])). array_data [1])). array_data [15] .ToString ();

"Binding" class:

 using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.IO.Compression; using System.IO; namespace metadata { class v8config { public SqlConnection con; public string rootfile; public string platform; public string vendor; public string conf; public string version; public v8metadata v8; private string db; private string server = ""; public v8config(string constring) { con = new SqlConnection(constring); db = con.Database; MemoryStream ms = file_data("root"); v8metadata v82 = new v8metadata(ms, "root"); rootfile = v82.get(1).ToString(); if (v82.array_data.Length > 2) platform = "8.2"; else platform = "8.1"; update_prop(); } //  linked  public v8config(SqlConnection conn, string srv, string database) { con = conn; server = srv; db = database; if (check_is_v8()) { MemoryStream ms = file_data("root"); v8metadata v82 = new v8metadata(ms, "root"); rootfile = v82.get(1).ToString(); if (v82.array_data.Length == 3) platform = "8.2"; else if (v82.array_data.Length > 3) { platform = "8.0"; rootfile = "root"; } else platform = "8.1"; update_prop(); } } public v8config(SqlConnection conn) { con = conn; db = con.Database; MemoryStream ms = file_data("root"); v8metadata v82 = new v8metadata(ms, "root"); rootfile = v82.get(1).ToString(); update_prop(); } //,  ,   private void update_prop() { if (platform != "8.0") { if (v8 == null) { v8 = new v8metadata(file_data(rootfile), rootfile); } if (((v8metadata)(((v8metadata)(((v8metadata)(v8.array_data[3])).array_data[1])).array_data[1])).array_data.Length > 20) platform = "8.2"; vendor = v8.get("vendor"); conf = v8.get("conf"); version = v8.get("version"); } else { vendor = "  "; conf = "  "; version = "  "; } } public void SaveToFile(string file, string filename) { FileStream fs = File.OpenWrite(filename); MemoryStream ms = file_data(file); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); } private bool check_is_v8() { try { bool doopencon = false; SqlCommand cmdu1; bool haslinked = true; if (con.State != System.Data.ConnectionState.Open) { con.Open(); doopencon = true; } cmdu1 = new SqlCommand("", con); cmdu1.CommandText = "select name from [" + server + "].[master].dbo.sysdatabases where name='" + db + "'"; if (cmdu1.ExecuteScalar() == null) { haslinked = false; } if (!haslinked) { if (doopencon) con.Close(); return false; } cmdu1.CommandText = "select name from [" + server + "]." + db + ".dbo.sysobjects where name ='config'"; if (cmdu1.ExecuteScalar() == null) { haslinked = false; } if (doopencon) con.Close(); return haslinked; } catch { return false; } } private MemoryStream file_data(string name) { MemoryStream ms = new MemoryStream(); if (con.State != System.Data.ConnectionState.Open) con.Open(); string sql; if (server != "") sql = "select BinaryData from [" + server + "].[" + db + "].dbo.Config where [filename]='" + name + "'"; else sql = "select BinaryData from [" + db + "].dbo.Config where [filename]='" + name + "'"; SqlCommand cmdu1 = new SqlCommand(sql, con); cmdu1.CommandTimeout = 200; SqlDataReader rs = cmdu1.ExecuteReader(); if (rs.Read()) { DeflateStream dfs = new DeflateStream(rs.GetSqlBytes(0).Stream, CompressionMode.Decompress); byte[] BufferOut = new byte[100]; int BytesRead; while ((BytesRead = dfs.Read(BufferOut, 0, 100)) > 0) ms.Write(BufferOut, 0, BytesRead); dfs.Close(); } con.Close(); return ms; } public string get(string descr) { if (rootfile == null) return "     1 v8"; if (v8 == null) { v8 = new v8metadata(file_data(rootfile), rootfile); } if (descr == "test") { string fname = ((v8metadata)(((v8metadata)(((v8metadata)(v8.array_data[4])).array_data[2])).array_data[2])).array_data[3].ToString() + ".5"; MemoryStream ms = this.file_data(fname); v8metadata nv8 = new v8metadata(ms, fname); return "test"; } else return v8.get(descr); } } } 

Usage:

  v8config v8 = new v8config("server=;database=;uid=sa;pwd=;Connection Timeout=300;"); v8.get("version"); 

But where is MSSQL here?
Here is the CLR function that will receive this data in MSSQL itself:
We have a separate database that stores the server and database. Accordingly, the function is adapted to this.

 using System; using System.Collections; using System.Data; using System.Data.Sql; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class _1c_conf_functions { [SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRowConfig", TableDefinition = "[platform] nvarchar(5),vendor nvarchar(150),conf nvarchar(250),version nvarchar(100)")] public static IEnumerable v8getconfig(string srv, string dbs) { using (SqlConnection conn = new SqlConnection("context connection=true")) { metadata.v8config v8 = new metadata.v8config(conn, srv, dbs); metadata.v8config[] t = new metadata.v8config[1]; t[0] = v8; return t; } //v8metadata.v8_confmanager vconf = new v8metadata.v8_confmanager(@"\\ackiy_gw\public\1c_distr\tmplts_8.1"); //return vconf.find_config(" \"1\"", "").data; } public static void FillRowConfig(object obj, out SqlChars platform, out SqlChars vendor, out SqlChars conf, out SqlChars version) { // dateupdate = new SqlDateTime(((v8metadata.v8info)obj).dataupdate); metadata.v8config cfg = (metadata.v8config)obj; platform = new SqlChars(cfg.platform); vendor = new SqlChars(cfg.vendor); conf = new SqlChars(cfg.conf); version = new SqlChars(cfg.version); } }; 

And how to determine which update is needed for this 1c configuration?
When installing update 1c, you can use the update directory on the server. Each update has a .mft file of the form:
Vendor= "1"
Name=
Version=2.0.25.5
AppVersion=8.2
........

And the file UpdInfo.txt
Version=2.0.25.5
FromVersions=;2.0.24.10;
UpdateDate=11.07.2011

That's all we need !!!
Knowing FromVersions and the release date of the update, we can automatically generate a string to run the 1c update. (link to the parameters at the beginning of the topic)

But there is another problem - the presence of users in the database. 1c is not updated. We write "expulsion" of users (vbscript)

 'basename-     1 'updateway -     'platform -  1 (8.1/8.2) 'srv1c -  1 'srvUser -      1 'srvPasswd-       1 'confchanged -  -  .      Function GetUpdateForConfig(basename,updateway,platform,srv1c,srvUser,srvPasswd,baseUsr,basepwd,confchanged) allowdisconnect=false '    12  6  -     if Hour(now())>0 and Hour(now)<6 then allowdisconnect=true End if set Connector=CreateObject("V" & Replace(platform,".","") & +".ComConnector") set AgentConnection=Connector.ConnectAgent(srv1c) set Cluster=AgentConnection.GetClusters() (0) AgentConnection.Authenticate Cluster,srvUser,srvPasswd 'WorkingProcess = AgentConnection.GetWorkingProcesses(Cluster)[0]; Process = AgentConnection.GetWorkingProcesses(Cluster) for each WorkingProcess in Process if WorkingProcess.Running<>0 then ConnectString = WorkingProcess.HostName & ":" & WorkingProcess.MainPort set WorkingProcessConnection = Connector.ConnectWorkingProcess("tcp://" & ConnectString) WorkingProcessConnection.AddAuthentication baseUsr,basepwd set ibDesc = WorkingProcessConnection.CreateInfoBaseInfo() ibDesc.Name = basename Connections = WorkingProcessConnection.GetInfoBaseConnections(ibDesc) for each Connection in Connections if LCase(Connection.AppID) <> "comconsole" then if allowdisconnect then WorkingProcessConnection.Disconnect Connection ShowStatus "Discconnect  " & Trim(Connection.ConnID),true,false else ShowStatus "  .",false,false GetUpdateForConfig=0 Exit function End if End if Next End if Next ShowStatus "  " & updateway,true,false ProcId=0 constr="/S""" & srv1c & "\" & basename & """" if baseUsr<>"" then constr=constr & " /N" & baseUsr & " /P" & basepwd end if if not confchanged then constr=constr & " /UpdateCfg""" & updateway & """" end if constr=constr & " /UpdateDBCfg" if Way1cv81="" then ShowStatus "    81",true,true GetUpdateForConfig="" Exit function End if RunString="""" & Way1cv81 & "1cv8.exe"" CONFIG " & constr & "" Resfile=WorkCatalog() & "result.txt" RunString=RunString & " /Out""" & Resfile & """" ShowStatus " : " & RunString,true,false CreateProcess RunString,true GetUpdateForConfig=ReadFileText(Resfile) End Function 

It remains only to add a puzzle.