Metaprogramming

We can use either SQL statements or metaprogramming to conduct update or delete operations on in-memory tables.

Create a partitioned in-memory table:

n=1000000
sym=rand(`IBM`MSFT`GOOG`FB`IBM`MSFT,n)
date=rand(2018.01.02 2018.01.02 2018.01.02 2018.01.03 2018.01.03 2018.01.03,n)
price=rand(1000.0,n)
qty=rand(10000,n)
trades=table(sym,date,price,qty)

The parameters of some DolphinDB built-in functions need to use metaprogramming.

The following example defines a function to generate a report. The user only needs to enter the table object, the column names and the corresponding formats.

def generateReport(tbl, colNames, colFormat, filter){
	colCount = colNames.size()
	colDefs = array(ANY, colCount)
	for(i in 0:colCount){
		if(colFormat[i] == "") 
			colDefs[i] = sqlCol(colNames[i])
		else
			colDefs[i] = sqlCol(colNames[i], format{,colFormat[i]})
	}
	return sql(colDefs, tbl, filter).eval()
}

Create sample data:

n=31
date=2020.09.30+1..n
sym=take(`A,n) join take(`B,n)
t=table(take(date,2*n) as date, sym, 100+rand(1.0,2*n) as price, rand(1000..10000,2*n) as volume)
select * from t where date=2020.10.16 and sym=`B;

date       sym price               volume
---------- --- ------------------- ------
2020.10.16 B   100.324969965266063 6657

generateReport(t,`date`sym`price`volume,["MM/dd/yyyy","","###.00","#,###"],<date=2020.10.16 and sym=`B >);

date       sym price  volume
---------- --- ------ ------
10/16/2020 B   100.32 6,657

The script above is equivalent to the following script:

select format(date,"MM/dd/yyyy") as date, sym, format(price,"###.00") as price, format(volume,"#,###") as volume from t where date=2020.10.16 and sym=`B

Sometimes we may need to execute a large number of similar queries on the same table and then combine the results. We can use metaprogramming to simplify these queries.

The first row of the data used in this example:

mt |vn |bc |cc |stt |vt |gn |bk |sc |vas |pm |dls |dt |ts |val |vol
-------- |-------- |-- |--- |--- |-- |-- |---- |-- |--- |-- |---------- |---------- |------ |----- |----- 52354955 |50982208 |25 |814 |11 |2 |1 |4194 |0 |0 |0 |2020.02.05 |2020.02.05 |153234 |5.374 |18600

We may need to execute thousands of queries with a day's observations. For simplicity, we use the following 4 queries:

select * from t where vn=50982208,bc=25,cc=814,stt=11,vt=2, dls=2020.02.05, mt<52355979 order by mt desc limit 1
select * from t where vn=50982208,bc=25,cc=814,stt=12,vt=2, dls=2020.02.05, mt<52355979 order by mt desc limit 1
select * from t where vn=51180116,bc=25,cc=814,stt=12,vt=2, dls=2020.02.05, mt<52354979 order by mt desc limit 1
select * from t where vn=41774759,bc=1180,cc=333,stt=3,vt=116, dsl=2020.02.05, mt<52355979 order by mt desc limit 1

All of the where conditions in these queries have the same filtering columns and ordering columns. Some of the where conditions share the same filter value. All of the queries only output the first row after sorting. We can create a function bundleQuery with metaprogramming:

def bundleQuery(tbl, dt, dtColName, mt, mtColName, filterColValues, filterColNames){
	cnt = filterColValues[0].size()
	filterColCnt =filterColValues.size()
	orderByCol = sqlCol(mtColName)
	selCol = sqlCol("*")
	filters = array(ANY, filterColCnt + 2)
	filters[filterColCnt] = expr(sqlCol(dtColName), ==, dt)
	filters[filterColCnt+1] = expr(sqlCol(mtColName), <, mt)
	
	queries = array(ANY, cnt)
	for(i in 0:cnt)	{
		for(j in 0:filterColCnt){
			filters[j] = expr(sqlCol(filterColNames[j]), ==, filterColValues[j][i])
		}
		queries.append!(sql(select=selCol, from=tbl, where=filters, orderBy=orderByCol, ascOrder=false, limit=1))
	}
	return loop(eval, queries).unionAll(false)
}

The parameters of function bundleQuery are:

• tbl is the input table
• dt is the filter value for date
• dtColName is the column name for date
• mt is the filter value for mt
• mtColName is the column name for mt
• filterColValues is a tuple indicating the filter values in other where conditions. Each element of the tuple is a vector indicating the filter values of a where condition in each query.
• filterColNames is the column names used in other where conditions.

We can rewrite the aforementioned 4 lines of SQL statements with the following script:

dt = 2020.02.05 
dtColName = "dls" 
mt = 52355979 
mtColName = "mt"
colNames = `vn`bc`cc`stt`vt
colValues = [50982208 50982208 51180116 41774759, 25 25 25 1180, 814 814 814 333, 11 12 12 3, 2 2 2 116]

bundleQuery(t, dt, dtColName, mt, mtColName, colValues, colNames)

We can execute the following query to add bundleQuery as a function view. With this, we can call bundleQuery directly either after restarting or on the other nodes of the cluster.

//please login as admin first
addFunctionView(bundleQuery)