lib-sql 库参考

原文: https://finproducts.atlassian.net/wiki/spaces/JIDD3/pages/34793652588/FIN+5.3.1+lib-sql 中文整理基于 FIN 5.3.1

导读： SQL 连接器用于通过 JDBC 与关系数据库集成。它遵循标准连接器模型：

以下为 API 参考，保留英文以便对照源码；目录已列出（英文标题便于检索）；概述已尽量中译。

---

目录（导读）

• lib
• meta
• docs
• tags
• funcs
• Overview
• Setup
• Connectors
• Funcs
• His Sync
  • def
• sqlDebugDrivers()
  • supertypes
• sqlExecute(conn, sql)
• sqlInsert(data, conn, table)
• sqlPing(conn)
• sqlQuery(conn, sql)
• sqlSyncHis(points, span: null)
• sqlTables(conn)
  • usage
  • tags

---

概述（中译）

SQL连接器

---

API 正文（英文）

docs

Overview • Setup • Connectors • Funcs • His Sync

---

tags

sqlConn	SQL connector to a relational database using JDBC
sqlConnRef	Used on a proxy point to reference its parent sqlConn
sqlHis	Arbitrary key used by the SQL connector to synchronized history data
sqlPoint	Point which synchronizes data via a SQL connector
sqlSyncHisExpr	This is a tag which must be configured on each sqlConn record used by the connSyncHis() function

---

funcs

sqlDebugDrivers	Return plain text report on JDBC drivers installed
sqlExecute	Execute a SQL statement and if applicable return a result
sqlInsert	Insert a record or grid of records into the given table
sqlPing	Deprecated - use connPing()
sqlQuery	Execute a SQL query and return the result as a grid
sqlSyncHis	Deprecated - use connSyncHis()
sqlTables	Query the tables defined for the database

---

lib sql

Overview

The SQL connector is used to integrate with relational database using JDBC. It follows the standard connector model:

• Connectors: used to configure a JDBC connection to a database server
• Functions: provide access read/write for Axon scripts
• His Sync: framework for scheduling synchronization of external RDBMS data with the historian

Setup

The SQL connector leverages the Fantom and Java JDBC infrastructure for connecting to databases. For setup:

1. Ensure your JDBC driver is installed and available via the system class path. The best place to stick it is in the {home}/lib/java/ext directory.

2. Ensure the JDBC class is loaded into memory. The simplest way to preload the class is to ensure the classname is defined in {home}/etc/sql/config.props:

   java.drivers=com.microsoft.sqlserver.jdbc.SQLServerDriver

If using Microsoft SQL Server:

1. Assuming you are running Java 1.8 or higher, then make sure you put only "sqljdbc4.jar" into your classpath (do not put "sqljdbc.jar" in the path)
2. Classname is "com.microsoft.sqlserver.jdbc.SQLServerDriver" (for java.drivers in etc/sql/config.props)
3. JDBC URL format is "jdbc:sqlserver://{host};database={name}"

Connectors

Each database you communicate with needs to have a sqlConn record defined with the following tags:

• conn: required marker tag
• sqlConn: required marker tag
• uri: JDBC URI such as "jdbc:mysql://localhost:3306/mytestdb"; review database JDBC documentation to verify the format of the JDBC URI
• username: username to use for database login
• password: must have password stored in password db for connector's record id
• sqlSyncHisExpr: this tag is required if using the connSyncHis() function which is discussed later

You can test our your connector using the connPing() function:

read(sqlConn).connPing

If the ping is successful it will update your connector record with tags indicating version of the database and JDBC driver.

Funcs

There are four Axon basic functions for working with a SQL database:

• sqlTables(): query tables defined
• sqlQuery(): run "select" query and return grid
• sqlExecute(): run modification command such as "create", "drop", etc
• sqlInsert(): export data to a SQL table

Some examples for using a SQL connector:

// read list of tables
read(sqlConn).sqlTables

// read tables using connector id
sqlTables(sqlConnId)

// simple query
read(sqlConn).sqlQuery("select * from some_table")

// simple execute
read(sqlConn).sqlExecute("drop table old_table")

// insert sites into SQL table
readAll(site).sqlInsert(sqlConnId, "site_table")

// insert list of dict literals
data: [{first_name:"Alice", last_name:"Rock"},
       {first_name:"Bob",   last_name:"Smith"}]
sqlInsert(data, sqlConnId, "people_table")

His Sync

The SQL extension follows the standard connSyncHis() design used by other connectors except you must provide the implementation. You configure SQL proxy points as normal his-points along with the sqlConnRef tag.

Each sqlConn must define the sqlSyncHisExpr tag which evaluates to an Axon function that takes the syncConn, his, and span. This function is responsible for performing a SQL query to get the data into the correct format used to write into the historian.

For a simple example let's assume that each history is defined in its own unique table, here is a strategy:

1. Add a tableName tag to each of the proxy histories
2. Create "sqlSyncFromTable" function (shown below)
3. Set sqlSyncHisExpr: "sqlSyncFromTable" in the sqlConn

Here is example code for "sqlSyncFromTable":

(conn, his, range) => do
   sql: "select timestamp, value from " + his->tableName +
        " where timestamp >= '" + range.start.format("YYYY-MM-DD hh:mm:ss") + "'" +
        " and   timestamp <= '" + range.end.format("YYYY-MM-DD hh:mm:ss") + "'"
   sqlQuery(conn, sql)
 end

Note: The system requires the your timestamps to be the correct timezones of the target histories. Since SQL databases tend to have very weak timezone support you will want to test this thoroughly, especially if your projects span multiple timezones.

Once everything is setup, you can use connSyncHis() to synchronize one or more histories with the SQL connector:

connSyncHis(points, pastWeek)

---

---

def

func:sqlDebugDrivers

sqlDebugDrivers()

Return plain text report on JDBC drivers installed.

---

meta

admin	✓
def	func:sqlDebugDrivers
is	func
lib	lib:sql
name	sqlDebugDrivers

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

func:sqlExecute

sqlExecute(conn, sql)

Execute a SQL statement and if applicable return a result. If the statement produced auto-generated keys, then return an list of the keys generated, otherwise return number of rows modified.

WARNING: any admin user will have full access to update the database based on the user account configured by the sqlConn.

---

meta

admin	✓
def	func:sqlExecute
is	func
lib	lib:sql
name	sqlExecute

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

func:sqlInsert

sqlInsert(data, conn, table)

Insert a record or grid of records into the given table. If data is a dict, thena single row is inserted. If data is a grid or list of dicts, then each row is inserted. The data's column names must match the table's columns. If the data has a tag/column not found in the table then it is ignored.

WARNING: any admin user will have full access to update the database based on the user account configured by the sqlConn.

---

meta

admin	✓
def	func:sqlInsert
is	func
lib	lib:sql
name	sqlInsert

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

func:sqlPing

sqlPing(conn)

Deprecated - use connPing()

---

meta

admin	✓
def	func:sqlPing
deprecated	✓
is	func
lib	lib:sql
name	sqlPing

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

func:sqlQuery

sqlQuery(conn, sql)

Execute a SQL query and return the result as a grid. Blob columns under 10K are returned as base64.

Examples:

read(sqlConn).sqlQuery("select * from some_table")
sqlQuery(sqlConnId, "select * from some_table")

WARNING: any admin user will have full access to query the database based on the user account configured by the sqlConn.

---

meta

admin	✓
def	func:sqlQuery
is	func
lib	lib:sql
name	sqlQuery

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

func:sqlSyncHis

sqlSyncHis(points, span: null)

Deprecated - use connSyncHis()

---

meta

admin	✓
def	func:sqlSyncHis
deprecated	✓
is	func
lib	lib:sql
name	sqlSyncHis

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

func:sqlTables

sqlTables(conn)

Query the tables defined for the database. Return a grid with the name column.

Examples:

read(sqlConn).sqlTables
sqlTables(sqlConnId)

---

meta

admin	✓
def	func:sqlTables
is	func
lib	lib:sql
name	sqlTables

---

supertypes

feature	Feature namespace of definitions formatted as feature:name
func	Axon function

---

---

def

sqlConn

SQL connector to a relational database using JDBC.

---

meta

def	sqlConn
icon	sql
is	conn
lib	lib:sql

---

usage

sqlConn  conn

---

supertypes

marker	Marker labels a dict with typing information
entity	Top-level dicts with a unique identifier
conn	Connector for communication to a remote system

---

tags

actorTimeout	Timeout for messages send to the connector's actor thread
connErr	Error message associated when connStatus indicates an error condition
connLinger	Linger timeout used to keep a connector open
connOpenRetryFreq	Duration used for connector open retries
connPingFreq	Duration used to configure the auto-ping feature on a given connector
connState	Current connection state of a connector as one of the predefined strings
connStatus	Current status of a connector as one of the predefined strings See Connectors
connTuningRef	Reference to a tuning configuration defined by a connTuning rec
dis	Display name for an entity
disabled	Indicates the entity is in a disabled state
id	Defines the unique identifier of an entity in system using a ref value type
password	Manages password which is hashed/stored to the password store
sqlSyncHisExpr	This is a tag which must be configured on each sqlConn record used by the connSyncHis() function
uri	Universal resource identifier
username	Username for an authentication account

---

---

def

sqlConnRef

Used on a proxy point to reference its parent sqlConn

---

meta

def	sqlConnRef
is	ref
lib	lib:sql
of	sqlConn
tagOn	sqlPoint

---

supertypes

val	Data value type
scalar	Scalar is an atomic value kind
ref	Reference to an entity

---

---

def

sqlHis

Arbitrary key used by the SQL connector to synchronized history data.

---

meta

def	sqlHis
is	str
lib	lib:sql
tagOn	sqlPoint

---

supertypes

val	Data value type
scalar	Scalar is an atomic value kind
str	Unicode string of characters

---

---

def

sqlPoint

Point which synchronizes data via a SQL connector.

---

meta

def	sqlPoint
is	connPoint
lib	lib:sql

---

usage

sqlPoint  point

---

supertypes

marker	Marker labels a dict with typing information
entity	Top-level dicts with a unique identifier
point	Data point such as a sensor or actuator
connPoint	Point associated with a connector for data synchronization with a remote system

---

tags

connTuningRef	Reference to a tuning configuration defined by a connTuning rec
cur	Supports current value
curCalibration	Calibration value to apply to adjust the value read from the connector before updating curVal
curConvert	Conversion to apply when reading curVal from connector
dis	Display name for an entity
disMacro	Display name using macro syntax
disabled	Indicates the entity is in a disabled state
enum	Defines an eumeration of string key names
equipRef	Reference to equip which contains this entity
ghgFactorHis	Identifies point record with interval emission factor data that the Greenhouse Gas Emission Engine uses to calculate emissions
his	Supports historization of data
hisConvert	Conversion to apply when reading history data from connector
id	Defines the unique identifier of an entity in system using a ref value type
kind	Kind name used to identity a value data type
maxVal	Inclusive maximum for a numeric value
minVal	Inclusive minimum for a numeric value
navName	Navigation name commonly used with disMacro to create computed display strings
pointFunction	Classifies the point as a sensor, command, or setpoint
pointQuantity	Quantity the point senses or controls
pointSubject	What the point senses or controls
siteRef	Reference to site which contains the entity
spaceRef	Reference to space which contains this entity
sqlConnRef	Used on a proxy point to reference its parent sqlConn
sqlHis	Arbitrary key used by the SQL connector to synchronized history data
systemRef	Reference to system
tz	Timezone identifier from standard timezone database
unit	Unit identifier from standard unit database
writable	Supports writing data
writeConvert	Conversion to apply when writing writeVal to the connector

---

---

def

sqlSyncHisExpr

This is a tag which must be configured on each sqlConn record used by the connSyncHis() function. The value must be an Axon expression which evaluates to a function with three parameters:

• sqlConn: the sqlConn record
• his: the his-point record being synced
• span: a range of two DateTime values

The function must be return a grid with at exactly two columns for the history data to sync. The first column must be a timestamp in the correct timezone. The second column must be the value. See sql.

---

meta

def	sqlSyncHisExpr
is	str
lib	lib:sql
tagOn	sqlConn

---

supertypes

val	Data value type
scalar	Scalar is an atomic value kind
str	Unicode string of characters