; save this as ArraySQL.au3
;
; this version allows the creation of "permanent" tables in the memory db
; allowing sql queries against more tables instead just one table at time
; this by using the 2 new functions: _ArrayToDbTable and _ArrayFromSQL
; (see functions infos for details)
;
; March 15, 2015
; added a new optional parameter in the _ArrayToDbTable function that allows
; the creation of one or more simple single-column indexes
;
; March 21, 2015
; slightly modified the above parameter ($sUserSQL) allowing 2 ways of use
; (see function info for details)
; Also removed the TEMP parameter from CREATE TABLE
;
; March 22, 2015
; slightly enhanced the $sUserSQL usage by allowing now
; the passing of one or more SQL queries separated by ";"
; (see function info for details)
;; 将其保存为ArraySQL.au3
;
; 这个版本允许在内存db中创建“永久”表
; 允许sql查询更多的表而不是一次只有一个表
; 通过使用2个新函数:_ArrayToDbTable和_ArrayFromSQL
; (详见函数信息)
;
; 2015年3月15日
; 在_ArrayToDbTable函数中添加了一个新的可选参数,允许
; 创建一个或多个简单的单列索引
;
; 2015年3月21日
; 稍微修改了上面的参数($ sUserSQL),允许2种使用方式
; (详见功能信息)
; 还从CREATE TABLE中删除了TEMP参数
;
; 2015年3月22日
; 现在允许略微增强$ sUserSQL的使用
; 传递一个或多个以“;”分隔的SQL查询
; (详见功能信息)
#include-once
#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
;
Global Static $g__sSQliteDll = _SQLite_Startup()
; Global Static $g__sSQliteDll = _SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, True)
Global Static $g__hMemDb = _SQLite_Open()
Global $g__sSQLiteError = "" ; will contains SQL error messages
; #FUNCTION# ====================================================================================================================
; Name...........: _ArraySQL
; Description ...: Allows to execute SQL queryes against 1D or 2D arrays
; Syntax.........: _ArraySQL( ByRef $avArray, $sSQL_Query )
; Parameters ....: $avArray - an 1D or 2D Array to be manage
; $sSQL_Query - a string containing the SQL query to execute against the array
; the passed array is referred as array as table name in the query
; the fields (column(s)) of the array are referred as column0, column1, column2 .... and so on
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
; Fillure: an empty string and the @error value is set as following
; 1 - First argument is not an array
; 2 - not a 1D or 2D array
; 3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; 名称...........:_ ArraySQL
; 说明...:允许对1D或2D数组执行SQL查询
; 语法.........:_ ArraySQL(ByRef $ avArray,$ sSQL_Query)
; 参数....:$ avArray - 要管理的1D或2D数组
; $ sSQL_Query - 包含要对数组执行的SQL查询的字符串
; 传递的数组在查询中称为数组作为表名
; 数组的字段(列(s))称为column0,column1,column2 ....等等
;
; 返回值。:成功:包含执行SQL查询所产生数据的1D或2D数组。
;
; Fillure:一个空字符串,@ error值设置如下
; 1 - 第一个参数不是数组
; 2 - 不是1D或2D阵列
; 3 - SQLite错误。在这种情况下,$ g__sSQLiteError全局变量包含错误描述。
; ===============================================================================================================================
Func _ArraySQL(ByRef $avArray, $sSQL_Query)
Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
$g__sSQLiteError = ""
__ArrayToSqlTable($avArray) ; clone $avArray to a temporary sql db table (memory resident)
; name of the temp table will be array
; name of the filed(s) will be column0 column1 column2 columnN and so on ....
;
If @error Then
$iError = @error
Return SetError($iError, TimerDiff($hTimer), "") ; something went wrong
EndIf
; now execute the SQL query
$iRval = _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns)
If Not $iRval = $SQLITE_OK Then ; an SQLite error occurred
$g__sSQLiteError = _SQLite_ErrMsg()
_SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
Return SetError(3, TimerDiff($hTimer), "")
Else
$g__sSQLiteError = ""
_SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
Return SetError(0, TimerDiff($hTimer), $aResult)
EndIf
EndFunc ;==>_ArraySQL
; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayToDbTable
; Description ...: clones $avArray to a "permanent" SQLite db table (memory resident)
; Syntax.........: _ArrayToDbTable ( ByRef $avArray, $sTableName [$sUserSQL = "" [,$iStep = 100]] )
; Parameters ....: $avArray - Array to clone to the SQLite table
;
; $sTableName - Is the name of the table that will be created in the memory db
; note:
; The name "array" is not allowed because is reserved to the default table used by _ArraySQL()
; $sUserSQL - Optional. This can be used in 2 ways and can be one of the following:
; 1) A string containing one, or more comma separated numbers, that indicate on which columns
; you want to create a "basic" index. Example: $sUserSQL = "2,5,7" will create 3 Single-Column Indexes
; on the table that is being created. index names are as follows: tablename_ndx2 tablename_ndx5 Tablename_ndx7.
; or
; 2) You can pass an entire SQL Query or even more than one separated by ";" (for complex indexes creation)
; this query (or those queries if more than one) will be executed right after the table creation.
; If more than one query are passed, those are executed one after another
; example of a string containing 3 queries:
; "CREATE INDEX Array1_ndx0 ON Array1 (column0); CREATE INDEX Array1_ndx3 ON Array1 (column3); CREATE INDEX Array1_ndx4 ON Array1 (column4);"
; IMPORTANT! do not forget to place also the last ; at the end of the string.
;
; $iStep - Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
; operation, that is the so called "chain insertion". This parameter is used only on recent versions
; of AutoIt while is ignored on older versions for compatibility.
; (Only one record at time is written in this second case)
;
; Return values .: Success: - @error = 0
; @extended contains the milliseconds spent by the table creation
;
; Fillure: - Sets @error as following:
; 1 - First argument is not an array
; 2 - Array is not an 1D or 2D array
; 3 - SQLite error (the $g__sSQLiteError variable contains error description)
; 4 - The table name "array" is a reserved name not allowed with this function
; 名称...........:_ ArrayToDbTable
; 说明...:克隆$ avArray到“永久”SQLite数据库表(内存驻留)
; 语法.........:_ ArrayToDbTable(ByRef $ avArray,$ sTableName [$ sUserSQL =“”[,$ iStep = 100]])
; 参数....:$ avArray - 要克隆到SQLite表的数组
;
; $ sTableName - 将在内存db中创建的表的名称
; 注意:
; 不允许使用名称“array”,因为它保留在_ArraySQL()使用的默认表中
; $ sUserSQL - 可选。这可以以两种方式来使用,并且可以是下列之一:
; 1)包含一个或多个逗号分隔数字的字符串,用于指示哪些列
; 你想创建一个“基本”索引。示例:$ sUserSQL =“2,5,7”将创建3个单列索引
; 在正在创建的表上。索引名称如下:tablename_ndx2 tablename_ndx5 Tablename_ndx7。
; 或
; 2)您可以传递整个SQL查询,甚至可以传递多个以“;”分隔的查询 (用于创建复杂索引)
; 在创建表之后立即执行此查询(或多个查询,如果有多个查询)。
; 如果传递了多个查询,则会一个接一个地执行
; 包含3个查询的字符串示例:
; “CREATE INDEX Array1_ndx0 ON Array1(column0); CREATE INDEX Array1_ndx3 ON Array1(column3); CREATE INDEX Array1_ndx4 ON Array1(column4);”
; 重要!别忘了也放在最后; 在字符串的末尾。
;
; $ iStep - 可选。默认值为100.这是每次SQLite写入时在表中插入的记录数
; 操作,即所谓的“链插入”。此参数仅用于最新版本
; 为了兼容性,在旧版本中忽略了AutoIt。
; (在第二种情况下,只写出一次记录)
;
; 返回值。:成功: - @error = 0
; @extended包含表创建所花费的毫秒数
;
; Fillure: - 将@error设置如下:
; 1 - 第一个参数不是数组
; 2 - 数组不是1D或2D数组
; 3 - SQLite错误($ g__sSQLiteError变量包含错误描述)
; 4 - 表名“array”是此函数不允许的保留名称
; ===============================================================================================================================
Func _ArrayToDbTable(ByRef $avArray, $sTableName, $sUserSQL = "", $iStep = 100)
If $sTableName = "array" Then Return SetError(4, 0, "")
__ArrayToSqlTable($avArray, $sTableName, $sUserSQL, $iStep)
Return SetError(@error, @extended, "")
EndFunc ;==>_ArrayToDbTable
; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayFromSQL
; Description ...: Allows to execute SQL queryes against table(s) in the Temp db and get the resulting table returned in an array
; Syntax.........: _ArrayFromSQL( $sSQL_Query )
; Parameters ....: $sSQL_Query - a string containing the SQL query to execute against the temp db
; it's up to you to pass a correct query with existing temp tablenames.
; P.S. (Related info)
; You can previously create required temp tables by the _ArrayToDbTable() function
; passing an array and a tablename to be created in the temp db (see function info for details)
;
; Return values .: Success: An 1D or 2D array containing data resulting from the execution of the SQL query.
;
; Fillure: an empty string and the @error value is set as following
; 3 - SQLite error. In this case the $g__sSQLiteError global variable contains the error description.
; 名称...........:_ ArrayFromSQL
; 说明...:允许对Temp数据库中的表执行SQL查询,并获取在数组中返回的结果表
; 语法.........:_ArrayFromSQL($ sSQL_Query)
; 参数....:$ sSQL_Query - 包含要对temp db执行的SQL查询的字符串
; 您可以使用现有临时表名传递正确的查询。
; PS(相关信息)
; 您可以通过_ArrayToDbTable()函数预先创建所需的临时表
; 传递要在temp db中创建的数组和tablename(有关详细信息,请参阅函数信息)
;
; 返回值。:成功:包含执行SQL查询所产生数据的1D或2D数组。
;
; Fillure:空字符串,@ error值设置如下
; 3 - SQLite错误。在这种情况下,$ g__sSQLiteError全局变量包含错误描述。
; ===============================================================================================================================
Func _ArrayFromSQL($sSQL_Query)
Local $aResult, $iRows, $iColumns, $iRval, $iError, $hTimer = TimerInit()
$g__sSQLiteError = ""
; now execute the SQL query
If _SQLite_GetTable2d(-1, $sSQL_Query, $aResult, $iRows, $iColumns) = $SQLITE_OK Then ; execution of query is OK
Return SetError(0, TimerDiff($hTimer), $aResult)
Else ; an SQLite error occurred
$g__sSQLiteError = _SQLite_ErrMsg()
Return SetError(3, TimerDiff($hTimer), "")
EndIf
EndFunc ;==>_ArrayFromSQL
;
; #INTERNAL USE# ================================================================================================================
; Name...........: __ArrayToSqlTable
; Description ...: clone $avArray to a temporary SQLite db table (memory resident)
; Syntax.........: __ArrayToSqlTable ( ByRef $avArray [$sTableName = "array" [,$sUserSQL = "" [,$iStep = 100]]])
; Parameters ....: $avArray - Array to clone to the SQLite temporary table
; $sTableName - Optional. Default is "array". Is the name of the table that will be created in the temp db
; This function is used mainly by the _ArraySQL() function, and the "array" table created by default
; will be automatically deleted after the execution of the query passed by _ArraySQL().
; Optionally, if you create a table with this parameter instead, assigning a different name to the table
; it will remain in the temp db till end of program execution or till you explicitly delete it using the
; _SQLite_Exec($g__hMemDb, "DROP TABLE yourtablename") for example.
; $sUserSQL - Optional. This can be used in 2 ways and can be one of the following:
; 1) A string containing one, or more comma separated numbers, that indicate on which columns
; you want to create a "basic" index. Example: $sUserSQL = "2,5,7" will create 3 Single-Column Indexes
; on the table that is being created. index names are as follows: tablename_ndx2 tablename_ndx5 Tablename_ndx7.
; or
; 2) You can pass an entire SQL Query or even more than one separated by ";" (for complex indexes creation)
; this query (or those queries if more than one) will be executed right after the table creation.
; If more than one query are passed, those are executed one after another
; example of a string containing 3 queries:
; "CREATE INDEX Array1_ndx0 ON Array1 (column0); CREATE INDEX Array1_ndx3 ON Array1 (column3); CREATE INDEX Array1_ndx4 ON Array1 (column4);"
; IMPORTANT! do not forget to place also the last ; at the end of the string.
;
; $iStep - Optional. Default is 100. This is the number of records inserted in the table for every SQLite write
; operation, that is the so called "chain insertion". This parameter is used only on recent versions
; of AutoIt while is ignored on older versions for compatibility problems.
; (Only one record at time is written in this second case)
;
; Return values .: Success: - @error = 0
; @extended contains the milliseconds spent by the table creation
;
; Fillure: - Sets @error as following:
; 1 - First argument is not an array
; 2 - Array is not an 1D or 2D array
; 3 - SQLite error (the $g__sSQLiteError variable contains error description)
;
; Remarks .......: Mainly for Internal use (but not only)
; ===============================================================================================================================
;名称...........:__ ArrayToSqlTable
; 说明...:将$ avArray克隆到临时的SQLite数据库表(内存驻留)
; 语法.........:__ ArrayToSqlTable(ByRef $ avArray [$ sTableName =“array”[,$ sUserSQL =“”[,$ iStep = 100]]])
; 参数....:$ avArray - 要克隆到SQLite临时表的数组
; $ sTableName - 可选。默认为“数组”。是将在临时数据库中创建的表的名称
; 此函数主要由_ArraySQL()函数和默认创建的“数组”表使用
; 将在执行_ArraySQL()传递的查询后自动删除。
; (可选)如果使用此参数创建表,请为表指定其他名称
; 它将保留在临时数据库中直到程序执行结束或直到您使用
; 例如,_SQLite_Exec($ g__hMemDb,“DROP TABLE yourtablename”)。
; $ sUserSQL - 可选。这可以以两种方式来使用,并且可以是下列之一:
; 1)包含一个或多个逗号分隔数字的字符串,用于指示哪些列
; 你想创建一个“基本”索引。示例:$ sUserSQL =“2,5,7”将创建3个单列索引
; 在正在创建的表上。索引名称如下:tablename_ndx2 tablename_ndx5 Tablename_ndx7。
; 或
; 2)您可以传递整个SQL查询,甚至可以传递多个以“;”分隔的查询 (用于创建复杂索引)
; 在创建表之后立即执行此查询(或多个查询,如果有多个查询)。
; 如果传递了多个查询,则会一个接一个地执行
; 包含3个查询的字符串示例:
; “CREATE INDEX Array1_ndx0 ON Array1(column0); CREATE INDEX Array1_ndx3 ON Array1(column3); CREATE INDEX Array1_ndx4 ON Array1(column4);”
; 重要!别忘了也放在最后; 在字符串的末尾。
;
; $ iStep - 可选。默认值为100.这是每次SQLite写入时在表中插入的记录数
; 操作,即所谓的“链插入”。此参数仅用于最新版本
; 对于兼容性问题,在旧版本上忽略AutoIt而忽略。
; (在第二种情况下,只写出一次记录)
;
; 返回值。:成功: - @error = 0
; @extended包含表创建所花费的毫秒数
;
; Fillure: - 将@error设置如下:
; 1 - 第一个参数不是数组
; 2 - 数组不是1D或2D数组
; 3 - SQLite错误($ g__sSQLiteError变量包含错误描述)
;
; 备注.......:主要供内部使用(但不仅限于)
Func __ArrayToSqlTable(ByRef $avArray, $sTableName = "array", $sUserSQL = "", $iStep = 100) ; Pass an array to a temporary SQL table in memory db
Local $hTimer = TimerInit()
If Not IsArray($avArray) Then Return SetError(1, TimerDiff($hTimer), 0) ; must be an array
Local $iDimensions = UBound($avArray, 0)
If $iDimensions > 2 Then Return SetError(2, TimerDiff($hTimer), 0) ; must be an 1D or 2D array
Local $aAutoItV = StringSplit(@AutoItVersion, ".", 2), $nAutoItVersion = StringFormat("%03i%03i%03i%03i", $aAutoItV[0], $aAutoItV[1], $aAutoItV[2], $aAutoItV[3])
; Local $iStep = 10 ; the number of records we want to load in each chain <---- the chain length
If $nAutoItVersion < 3003012000 Then $iStep = 1 ; if an older version of AutoIt then do not use the new SQLite "chain insertion"
Local $sDBfields = "" ; will hold the names of the fields (column0, column1, column2......) (zero based)
Local $vDBvalues = "" ; will hold the values of a field (1, 'aaa', 456)
Local $vDBvaluesChain = "" ; will hold the chain of n values (1, 'aaa', 456), (2, 'bbb', 147), ... , (235, 'xyz', 999)
Local $iRecords = UBound($avArray, 1) ; total number of rows in the array
Local $iLastRecord = $iRecords - 1
Local $iChains = Ceiling($iRecords / $iStep) ; how many chains we will fill?
Local $nRemainders = Mod($iRecords, $iStep) ; are there rmainders? (if so the last chain will be only partly full)
Local $iFields = 0 ; we suppose an 1D array
If $iDimensions = 2 Then ; if is a 2D array instead
$iFields = UBound($avArray, 2) - 1 ; number of fields (columns) in the 2D array (zero based)
EndIf
For $x = 0 To $iFields
$sDBfields &= "column" & String($x) & ","
Next
$sDBfields = StringTrimRight($sDBfields, 1) ; remove the last comma
If Not _SQLite_Exec(-1, "CREATE TABLE " & $sTableName & " (" & $sDBfields & ");") = $SQLITE_OK Then
$g__sSQLiteError = _SQLite_ErrMsg()
; _SQLite_Exec($g__hMemDb, "DROP TABLE array") ; delete the temporary table
Return SetError(3, TimerDiff($hTimer), "")
Else
#cs suggestion by jchd -> http://www.autoitscript.com/forum/topic/166536-manage-arrays-by-means-of-sql/?p=1216694
For maximizing efficiency of table population using INSERT, you can use the "chain insertion" new syntax:
insert into mytable (a,b,c) values (1, 'aaa', 456), (2, 'bbb', 147), (3, 'ccc', 258), ... , (235, 'xyz', 999)
You need to keep the query string under the size limit (depends on compile-time options)
but you can get N-fold (for some value N) improvement on insert speed with that simple trick.
为了使用INSERT最大化表格填充效率,您可以使用“链插入”新语法:
插入mytable(a,b,c)值(1,'aaa',456),( 2,'bbb',147),(3,'ccc',258),...,(235,'xyz',999)
您需要将查询字符串保持在大小限制之下(取决于编译时选项) )
但是你可以通过这个简单的技巧获得N倍(对于某些值N)的插入速度提高。
#ce
If Not $nRemainders Then ; there are no remainder records.
; then we can load all chains (with group of records)
; and we will have no residual records to manage
For $x = 0 To $iLastRecord Step $iStep ; we read records in groups of $iStep
$vDBvaluesChain = ""
For $iRecord = $x To $x + $iStep - 1
$vDBvalues = ""
___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
$vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
Next
$vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
; insert chain to table
___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
If @error Then Return SetError(3, TimerDiff($hTimer), "")
Next
;
Else ; if we are here is because there are remainders, so:
If $iChains - 1 Then ; if there are more than 1 chain (otherwise if records are less than $istep read only remainders)
For $x = 0 To $iLastRecord - $nRemainders Step $iStep
$vDBvaluesChain = ""
For $iRecord = $x To $x + $iStep - 1
$vDBvalues = ""
___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
$vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
Next
$vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
If @error Then Return SetError(3, TimerDiff($hTimer), "")
Next
EndIf
; -- now read remainders -----
$vDBvaluesChain = ""
For $iRecord = $iLastRecord - $nRemainders + 1 To $iLastRecord ; Step $iStep
$vDBvalues = ""
___BuildRecord($avArray, $iFields, $vDBvalues, $iRecord) ; build data record related to row $iRecord
$vDBvaluesChain &= $vDBvalues & "),(" ; build the chain of records
Next
$vDBvaluesChain = StringTrimRight($vDBvaluesChain, 3) ; remove last "),("
___InsertChain($sDBfields, $vDBvaluesChain, $sTableName)
If @error Then Return SetError(3, TimerDiff($hTimer), "")
EndIf
EndIf
If $sUserSQL <> "" Then ; check for indexes requests
; - some infos abount indexes -
; http://stackoverflow.com/questions/796359/does-a-multi-column-index-work-for-single-column-selects-too
; http://www.sqlite.org/optoverview.html
; http://www.sqlite.org/lang_createindex.html
;
If StringIsDigit(StringReplace($sUserSQL, ",", "")) Then ; if only digits in the request then
; create simple single columns indexes
Local $aNdxs = StringSplit($sUserSQL, ","), $sNdxs = ""
;
#cs ; --- single index with multi columns --------------------------------------------------------------------
For $i = 1 To $aNdxs[0]
$sNdxs &= "column" & $aNdxs[$i] & ","
Next
$sNdxs = StringTrimRight($sNdxs, 1) ; remove the last comma删除最后一个逗号
_SQLite_Exec($g__hMemDb, "CREATE INDEX " & $sTableName & "_ndx ON " & $sTableName & " (" & $sNdxs & ");")
#ce ; --------------------------------------------------------------------------------------------------------
; --- a different index for each column ------------------------------------------------------------------
For $i = 1 To $aNdxs[0]
_SQLite_Exec($g__hMemDb, "CREATE INDEX " & $sTableName & "_ndx" & $aNdxs[$i] & " ON " & $sTableName & " (column" & $aNdxs[$i] & ");")
Next
If @error Then Return SetError(3, TimerDiff($hTimer), "")
Else ; execute the query/queries as is
Local $sUserQueries = StringSplit($sUserSQL, ";")
For $i = 1 To $sUserQueries[0] - 1
_SQLite_Exec($g__hMemDb, $sUserQueries[$i] & ";")
If @error Then Return SetError(3, TimerDiff($hTimer), "")
Next
EndIf
EndIf
Return SetError(0, TimerDiff($hTimer), "")
EndFunc ;==>__ArrayToSqlTable
Func ___BuildRecord(ByRef $avArray, ByRef $iFields, ByRef $vDBvalues, ByRef $x)
For $y = 0 To $iFields
Switch $iFields
Case 0 ; just 1 field (1D Array)
If IsNumber($avArray[$x]) Then
$vDBvalues &= $avArray[$x] & ","
Else
$vDBvalues &= _SQLite_FastEscape($avArray[$x]) & ","
EndIf
Case Else ; multi fields (2D Array)
If IsNumber($avArray[$x][$y]) Then
$vDBvalues &= $avArray[$x][$y] & ","
Else
$vDBvalues &= _SQLite_FastEscape($avArray[$x][$y]) & ","
EndIf
EndSwitch
Next
$vDBvalues = StringTrimRight($vDBvalues, 1) ; remove last comma
EndFunc ;==>___BuildRecord
Func ___InsertChain(ByRef $sDBfields, ByRef $vDBvaluesChain, ByRef $sTableName)
If Not _SQLite_Exec(-1, "INSERT INTO " & $sTableName & " (" & $sDBfields & ") VALUES (" & $vDBvaluesChain & ");") = $SQLITE_OK Then
$g__sSQLiteError = _SQLite_ErrMsg()
_SQLite_Exec($g__hMemDb, "DROP TABLE " & $sTableName) ; delete the temporary table
Return SetError(3, 0, "")
EndIf
EndFunc ;==>___InsertChain