def insertMonoPricedata(instrument, granularity, startprice, start, end,
direction, pipdiff):
db = MSSQLDB()
pricetbl = lib.names.getPriceTable(instrument, granularity)
priceunit = tradelib.pip2Price(1, instrument)
unitsecs = tradelib.getUnitSecs(granularity)
start -= (start % unitsecs)
end -= (end % unitsecs)
curr = start
price = startprice
cnt = 0
while curr <= end:
i = cnt % 3
if i == 2:
price -= pipdiff * priceunit * direction
else:
price += pipdiff * priceunit * direction
if direction == 1:
o = price
c = price + 2 * pipdiff * priceunit
else:
o = price + 2 * pipdiff * priceunit
c = price
sql = "insert into %s ([ep],[dt],[o],[h],[l],[c],[v]) \
values (%d,'%s',%f,%f,%f,%f,%d)" % (
pricetbl, curr, lib.epoch2str(curr, "%Y-%m-%d %H:%M:%S"), o,
price + 2 * pipdiff * priceunit, price, c, (i + 1) * 10)
db.execute(sql)
curr += unitsecs
cnt += 1
def getTradeHistoryEventQueue(self, startep=-1, endep=-1):
db = MSSQLDB()
tradeh = EventQueue()
tradehT = names.getTradeHistoryTable(self.transaction_name)
sql = "select id, trade_id, startep, endep, \
instrument, side, units, start_price, end_price, profit, \
takeprofit_price, stoploss_price, [desc] from %s " % (tradehT)
wherelist = []
if startep > 0:
wherelist.append("startep >= %d" % startep)
if endep > 0:
wherelist.append("endep <= %d" % endep)
wherestr = sqllib.getWhereFromList(wherelist)
sql += wherestr + ";"
for (_id, trade_id, startep, endep, \
instrument, side, units, start_price, end_price, profit, \
takeprofit_price, stoploss_price, desc) in db.execute(sql):
event = TradeEvent(_id, trade_id,
instrument, side, units, start_price, startep,
env.ESTATUS_TRADE_CLOSED, takeprofit_price, stoploss_price,
end_price, profit, endep, desc)
tradeh.append(_id, event)
return tradeh
def getOrderHistoryEventQueue(self, startep=-1, endep=-1):
db = MSSQLDB()
orderhT = names.getOrderHistoryTable(self.transaction_name)
orderh = EventQueue()
self.orderhT = names.getOrderHistoryTable(self.transaction_name)
sql = "select id, order_id, startep, validep, instrument, side, \
order_type, units, price, takeprofit_price, stoploss_price, [desc] \
from %s" % orderhT
wherelist = []
if startep > 0:
wherelist.append("startep >= %d" % startep)
if endep > 0:
wherelist.append("endep <= %d" % endep)
wherestr = sqllib.getWhereFromList(wherelist)
sql += wherestr + ";"
for (_id, order_id, startep, validep, instrument, side, \
order_type, units, price, takeprofit_price, stoploss_price, desc) \
in db.execute(sql):
event = OrderEvent(_id, order_id,
instrument, side, order_type, units, price,
startep, status=env.ESTATUS_ORDER_CLOSED,
takeprofit_price=takeprofit_price,
stoploss_price=stoploss_price,
validep=-1, endep=endep, desc="")
orderh.append(_id, event)
return orderh
def testGetH1MSSQLPrices(self):
env.run_mode = env.MODE_UNITTEST
from db.mssql import MSSQLDB
msgetter.N_REQUEST_ROWS = 10
d = MSSQLDB()
instrument = "USD_JPY"
granularity = "M5"
pTable = names.getPriceTable(instrument, granularity)
d.execute("drop table if exists %s;" % pTable)
fmt = "%Y/%m/%d %H:%M"
og = msgetter.MSSQLGetter(OandaGetter(instrument, granularity))
def _verify(ststr, edstr, tcnt, rcnt, openp, vol):
st = lib.str2epoch(ststr, fmt)
ed = lib.str2epoch(edstr, fmt)
t, o, h, l, c, v = og.getPrice(st, ed)
self.assertEqual(d.countTable(pTable),tcnt)
self.assertEqual(t[0], st)
self.assertEqual(t[-1], ed)
self.assertEqual(len(t), rcnt)
self.assertEqual(v[0], vol)
self.assertEqual(o[0], openp)
# 11:00 - 11:20
_verify("2019/11/13 11:00", "2019/11/13 11:20", 5,5, 108.94, 39)
# 11:00 - 11:40
_verify("2019/11/13 11:30", "2019/11/13 11:40", 9,3, 108.963, 21)
# 10:40 - 11:40
_verify("2019/11/13 10:40", "2019/11/13 10:50", 13,3, 108.906, 32)
# 10:35 - 11:40
_verify("2019/11/13 10:35", "2019/11/13 10:55", 14,5, 108.916, 39)
# 10:35 - 11:50
_verify("2019/11/13 11:30", "2019/11/13 11:50", 16,5, 108.963, 21)
# 10:35 - 12:50
_verify("2019/11/13 11:30", "2019/11/13 12:50", 28,17, 108.963, 21)
def getLastTransactionID(self):
db = MSSQLDB()
tname = names.getOandaTrandactionIDTableName()
db.createTable(tname, "oanda_transaction_id")
sql = "select last_transaction_id from %s " % tname
oconf = env.conf["oanda"]
wherelist = [
"transaction_name = '%s'" % self.transaction_name,
"environment = '%s'" % oconf["environment"],
"account_id = '%s'" % oconf["accountid"]
]
wherestr = sqllib.getWhereFromList(wherelist)
sql += wherestr
last_transaction_id = 1
for tid in db.execute(sql):
last_transaction_id = tid
return last_transaction_id
class MSSQLGetter(DataGetter):
def __init__(self, childDG):
self.instrument = childDG.instrument
self.granularity = childDG.granularity
self.unitsecs = tradelib.getUnitSecs(self.granularity)
self.childDG = childDG
self.metatbl = lib.names.getMetainfTable(self.instrument,
self.granularity)
self.pricetbl = lib.names.getPriceTable(self.instrument,
self.granularity)
self.db = MSSQLDB()
self.db.createTable(self.metatbl, "metainf")
self.db.createTable(self.pricetbl, "prices")
self.maxID = -1
self.metainf = {}
self.getMetaInf()
def getMetaInf(self):
cur = self.db.execute("select id, startep, endep \
from %s;" % self.metatbl)
for row in cur.fetchall():
if row.id > self.maxID:
self.maxID = row.id
self.metainf[row.id] = [row.id, row.startep, row.endep]
def insertMetaInf(self, oids):
if len(oids) == 0:
return
for oid in oids:
m = self.metainf[oid]
sql = "insert into %s (id, startep, endep, startdt, enddt) \
values (?,?,?,?,?);" % (self.metatbl)
self.db.execute(
sql, (oid, m[1], m[2], lib.epoch2dt(m[1]), lib.epoch2dt(m[2])))
def updateMetaInf(self, oids):
if len(oids) == 0:
return
for oid in oids:
m = self.metainf[oid]
sql = "update %s set startep=?, endep=?, startdt=?, enddt=? where id=%d;" % \
(self.metatbl, oid)
self.db.execute(
sql, (m[1], m[2], lib.epoch2dt(m[1]), lib.epoch2dt(m[2])))
def deleteMetaInf(self, oids):
if len(oids) == 0:
return
moids = map(str, oids)
sql = "delete from %s where id in (%s)" % (self.metatbl,
",".join(moids))
self.db.execute(sql)
def insertData(self, ep, o, h, l, c, v):
sql = ""
for i in range(len(ep)):
sql = "%sinsert into %s ([ep],[dt],[o],[h],[l],[c],[v]) \
values (%s,'%s', %s,%s,%s,%s,%s);" % \
(sql, self.pricetbl,
ep[i],
lib.epoch2str(ep[i],env.DATE_FORMAT_NORMAL2),
o[i],h[i],l[i],c[i],v[i])
self.db.execute(sql)
def selectData(self, startep, endep):
sql = "select ep,o,h,l,c,v from %s where ep>=%d and ep<=%d" % \
(self.pricetbl, startep, endep)
cur = self.db.execute(sql)
rows = cur.fetchall()
(ep, o, h, l, c, v) = self.getIniPriceLists(0, len(rows))
i = 0
for row in rows:
ep[i] = row.ep
o[i] = row.o
h[i] = row.h
l[i] = row.l
c[i] = row.c
v[i] = row.v
i += 1
return (ep, o, h, l, c, v)
def _getPriceFromChild(self, startep, endep):
(ep, o, h, l, c, v) = self.childDG.getPrice(startep, endep)
if len(ep) == 0:
return (-1, -1)
try:
self.insertData(ep, o, h, l, c, v)
except Exception as e:
sqlstate = e.args[0]
if sqlstate == '23000':
lib.printInfo(ep[0], str(e))
else:
raise e
return (ep[0], ep[-1])
def _getSortedMetaList(self):
mList = []
for k in self.metainf.keys():
mList.append(self.metainf[k])
def _getMin(m):
return m[1]
mList.sort(key=_getMin)
return mList
def updateMaxID(self):
self.maxID += 1
return self.maxID
def getPrice(self, startep, endep):
interval_secs = self.unitsecs * N_REQUEST_ROWS
(ep, o, h, l, c, v) = ([], [], [], [], [], [])
baseep = startep
while True:
lastep = min(endep, baseep + interval_secs)
(ep1, o1, h1, l1, c1, v1) = self._getPriceProc(baseep, lastep)
ep.extend(ep1)
o.extend(o1)
h.extend(h1)
l.extend(l1)
c.extend(c1)
v.extend(v1)
if lastep >= endep:
break
baseep += interval_secs + self.unitsecs
return (ep, o, h, l, c, v)
def _getPriceProc(self, startep, endep):
startep = tradelib.getNearEpoch(self.granularity, startep)
endep = tradelib.getNearEpoch(self.granularity, endep)
errret = ([], [], [], [], [], [])
if len(self.metainf) == 0:
(_, _) = self._getPriceFromChild(startep, endep)
(ep, o, h, l, c, v) = self.selectData(startep, endep)
if len(ep) == 0:
return (ep, o, h, l, c, v)
oid = self.updateMaxID()
self.metainf[oid] = [oid, startep, endep]
self.insertMetaInf([oid])
return (ep, o, h, l, c, v)
mList = self._getSortedMetaList()
updates = {}
inserts = {}
deletes = {}
newstart = startep
oldid = -1
i = 0
startID = -1
endID = -1
oid = -1
for m in mList:
mstart = m[1]
mend = m[2]
mid = m[0]
if endep < mstart:
(st, _) = self._getPriceFromChild(newstart, endep)
if st == -1:
return errret
st = newstart
ed = endep
if st == 0:
break
if startep < newstart or \
(oldid >= 0 and newstart - self.unitsecs - 1 <= mend):
self.metainf[oldid][2] = ed
endID = oldid
updates[oldid] = 1
else:
oid = self.updateMaxID()
self.metainf[oid] = [oid, st, ed]
inserts[oid] = 1
startID = oid
endID = oid
break
if newstart < mstart and mstart <= endep:
(st, _) = self._getPriceFromChild(newstart, mstart - 1)
if st == -1:
return errret
st = newstart
ed = tradelib.getNearEpoch(self.granularity, mstart - 1)
if st == 0:
pass
elif startep < newstart:
self.metainf[oldid][2] = ed
updates[oldid] = 1
else:
if oldid >= 0 and oldid > mid:
oldst = self.metainf[oldid][1]
if st > oldst:
st = oldst
self.metainf[mid][1] = st
updates[mid] = 1
startID = mid
if newstart <= mend and mend < endep and i < len(mList) - 1:
if newstart == startep:
startID = mid
newstart = mend + self.unitsecs + 1
if i >= len(mList) - 1:
if newstart <= mend and mend < endep:
(st, _) = self._getPriceFromChild(mend + self.unitsecs,
endep)
if st == -1:
return errret
st = tradelib.getNearEpoch(self.granularity,
mend + self.unitsecs)
ed = endep
if st > 0:
self.metainf[mid][2] = ed
updates[mid] = 1
if startep == newstart:
startID = mid
endID = mid
if newstart > mend:
(st, _) = self._getPriceFromChild(startep, endep)
if st == -1:
return errret
st = startep
ed = endep
if st > 0:
oid = self.updateMaxID()
self.metainf[oid] = [oid, st, ed]
inserts[oid] = 1
startID = oid
endID = oid
if endep <= mend:
endID = oid
break
i += 1
oldid = mid
mList = self._getSortedMetaList()
firstm = None
oldm = None
mIndex = {}
for j in range(len(mList)):
mIndex[mList[j][0]] = j
for m in mList:
mid = m[0]
mstart = m[1]
mend = m[2]
if startID == mid:
if oldm != None and oldm[2] + self.unitsecs >= mstart:
firstm = oldm
else:
firstm = m
oldm = m
continue
if firstm == None:
oldm = m
continue
if startID >= 0 and mstart >= mList[mIndex[startID]][1] \
and firstm[2]+self.unitsecs>=mstart:
firstm[2] = max(mend, firstm[2])
updates[firstm[0]] = 1
del self.metainf[mid]
deletes[mid] = 1
updates[mid] = 0
inserts[mid] = 0
if endID == mid:
break
oldm = m
mList = None
self.deleteMetaInf(deletes.keys())
updList = []
for oid in updates.keys():
if updates[oid] == 1:
updList.append(oid)
self.updateMetaInf(updList)
insList = []
for oid in inserts.keys():
if inserts[oid] == 1:
insList.append(oid)
self.insertMetaInf(insList)
(ep, o, h, l, c, v) = self.selectData(startep, endep)
return (ep, o, h, l, c, v)
if inserts[oid] == 1:
insList.append(oid)
self.insertMetaInf(insList)
(ep, o, h, l, c, v) = self.selectData(startep, endep)
return (ep, o, h, l, c, v)
if __name__ == "__main__":
import env
from data_getter.oanda import OandaGetter
env.run_mode = env.MODE_UNITTEST
from db.mssql import MSSQLDB
d = MSSQLDB()
d.execute("drop table if exists USD_JPY_M1_prices;")
d.execute("drop table if exists USD_JPY_M1_metainf;")
og = MSSQLGetter(OandaGetter("USD_JPY", "M1"))
st = lib.str2epoch("2019/04/01 00:10", "%Y/%m/%d %H:%M")
ed = lib.str2epoch("2019/04/01 00:15", "%Y/%m/%d %H:%M")
t, o, h, l, c, v = og.getPrice(st, ed)
og = MSSQLGetter(OandaGetter("USD_JPY", "M1"))
st = lib.str2epoch("2019/03/31 23:55", "%Y/%m/%d %H:%M")
ed = lib.str2epoch("2019/04/01 00:00", "%Y/%m/%d %H:%M")
t, o, h, l, c, v = og.getPrice(st, ed)
og = MSSQLGetter(OandaGetter("USD_JPY", "M1"))
st = lib.str2epoch("2019/04/01 00:00", "%Y/%m/%d %H:%M")
ed = lib.str2epoch("2019/04/01 00:05", "%Y/%m/%d %H:%M")
class MSSQLTransaction(Transaction):
def __init__(self, name):
super(MSSQLTransaction, self).__init__(name)
self.db = MSSQLDB()
self.seqname = names.getTransactionSequence(name)
self.orderhT = names.getOrderHistoryTable(name)
self.tranhT = names.getTradeHistoryTable(name)
self.db.createSequence(self.seqname)
self.db.createTable(self.orderhT, "orderhistory")
self.db.createTable(self.tranhT, "tradehistory")
if env.run_mode in [
env.MODE_BACKTESTING, env.MODE_SIMULATE, env.MODE_UNITTEST
]:
self.db.truncateTable(self.orderhT)
self.db.truncateTable(self.tranhT)
self.db.restartSeq(self.seqname)
def genID(self):
return self.db.nextSeq(self.seqname)
def getPortofolio(self):
return MSSQLPortfolio(self.name)
def flushOrderHistory(self):
while True:
(_id, oh) = self.order_hqueue.pop()
if _id == -1:
break
sql = "insert into %s values(\
?,?,?,?,?,?,?,?,?,?,?,?,?,?);" % self.orderhT
self.db.execute(
sql,
(
oh.id, #[ID] INT NOT NULL,
oh.order_id, # [ORDER_ID] INT,
oh.startep, # [STARTEP] INT,
oh.validep, # [VALIDEP] INT,
lib.epoch2dt(oh.startep),
lib.epoch2dt(oh.validep),
oh.instrument, #[INSTRUMENT] char(7),
oh.side,
oh.order_type,
oh.units,
oh.price,
oh.takeprofit_price,
oh.stoploss_price,
oh.desc))
'''
[STARTDT] INT,
[VALIDDT] INT,
[INSTRUMENT] char(7),
[SIDE] INT,
[ORDER_TYPE] varchar(20),
[UNITS] INT,
[PRICE] FLOAT,
[TAKEPROFIT_PRICE] FLOAT,
[STOPLOSS_PRICE] FLOAT,
[DESC] VARCHAR(100),
'''
def flushTradeHistory(self):
while True:
(_id, oh) = self.trade_hqueue.pop()
if _id == -1:
break
sql = "insert into %s values(\
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);" % self.tranhT
self.db.execute(
sql,
(
oh.id, #[ID] INT NOT NULL,
oh.trade_id, #[TRADE_ID] INT,
oh.startep, #[STARTEP] INT,
oh.endep, #[ENDEP] INT,
lib.epoch2dt(oh.startep), #[STARTDT] datetime,
lib.epoch2dt(oh.endep), #[ENDDT] datetime,
oh.instrument, #[INSTRUMENT] char(7),
oh.side, #[SIDE] INT,
oh.units, #[UNITS] INT,
oh.start_price,
oh.end_price,
oh.profit,
oh.takeprofit_price,
oh.stoploss_price,
oh.desc))
'''
def makeSmallPeriodData(instrument, large_period, small_period):
reCreatePriceTable(instrument, small_period)
lus = tradelib.getUnitSecs(large_period)
sus = tradelib.getUnitSecs(small_period)
if lus <= sus:
raise Exception("large_period must be greater than small_period!")
db = MSSQLDB()
lpricetbl = lib.names.getPriceTable(instrument, large_period)
spricetbl = lib.names.getPriceTable(instrument, small_period)
diffrate = lus / sus
cnt = db.countTable(lpricetbl)
if cnt == 0:
raise Exception("No data in %s" % lpricetbl)
for (ep, o, h, l, c, v) in db.execute("select ep,o,h,l,c,v from %s;" %
lpricetbl).fetchall():
bar_size = (h - l) / diffrate
pdiff = (h - l - bar_size) / diffrate
if c > o:
direction = 1
price = l
else:
direction = -1
price = h
vs = int(v / diffrate)
for i in range(int(diffrate)):
os = price
if direction == 1:
hs = price + bar_size
ls = os
cs = hs
else:
hs = price
ls = price - bar_size
cs = ls
if i >= diffrate - 1:
if direction == 1:
hs = h
cs = h
else:
ls = l
cs = l
sql = "insert into %s ([ep],[dt],[o],[h],[l],[c],[v]) \
values (%d,'%s',%f,%f,%f,%f,%d)" % (
spricetbl, ep, lib.epoch2str(
ep, "%Y-%m-%d %H:%M:%S"), os, hs, ls, cs, vs)
db.execute(sql)
shuff = i % 4
if shuff == 2:
price -= direction * pdiff
elif shuff == 3:
price += direction * pdiff * 2
else:
price += direction * pdiff
ep += sus
def reCreatePriceTable(instrument, granularity):
pricetbl = lib.names.getPriceTable(instrument, granularity)
db = MSSQLDB()
db.execute("drop table if exists %s;" % pricetbl)
db.createTable(pricetbl, "prices")
class MSSQLGetter(DataGetter):
def __init__(self, childDG):
self.instrument = childDG.instrument
self.granularity = childDG.granularity
self.unitsecs = tradelib.getUnitSecs(self.granularity)
self.childDG = childDG
self.pricetbl = lib.names.getPriceTable(self.instrument, self.granularity)
self.db = MSSQLDB()
self.db.createTable(self.pricetbl, "prices")
self.maxID = -1
def insertData(self, ep,o,h,l,c,v):
sql = ""
for i in range(len(ep)):
sql = "%sinsert into %s ([ep],[dt],[o],[h],[l],[c],[v]) \
values (%s,'%s', %s,%s,%s,%s,%s);" % \
(sql, self.pricetbl,
ep[i],
lib.epoch2str(ep[i],env.DATE_FORMAT_NORMAL2),
o[i],h[i],l[i],c[i],v[i])
if i >= N_REQUEST_ROWS and i % N_REQUEST_ROWS == 0:
self.db.execute(sql)
sql = ""
self.db.execute(sql)
def selectData(self, startep, endep):
sql = "select ep,o,h,l,c,v from %s where ep>=%d and ep<=%d" % \
(self.pricetbl, startep, endep)
cur = self.db.execute(sql)
rows = cur.fetchall()
(ep, o, h, l, c, v) = self.getIniPriceLists(0, len(rows))
i = 0
for row in rows:
ep[i] = row.ep
o[i] = row.o
h[i] = row.h
l[i] = row.l
c[i] = row.c
v[i] = row.v
i += 1
return (ep, o, h, l, c, v)
def getEpEdges(self):
sql = "select min(ep), max(ep) from %s;" % (self.pricetbl)
cur = self.db.execute(sql)
if cur == None:
return (-1, -1)
res = cur.fetchone()
if res == None:
return (-1, -1)
(mi, ma) = res
if mi == None:
return (-1, -1)
return (mi, ma)
'''
def _getPriceFromChild(self, startep, endep):
(ep,o,h,l,c,v) = self.childDG.getPrice(startep, endep)
if len(ep) == 0:
return ([],[],[],[],[],[])
try:
self.insertData(ep, o, h, l, c, v)
except Exception as e:
sqlstate = e.args[0]
if sqlstate == '23000':
lib.printInfo(ep[0], str(e))
else:
raise e
lib.log("Data from child. %s - %s - %d rows" % (
lib.epoch2str(startep, env.DATE_FORMAT_NORMAL),
lib.epoch2str(endep, env.DATE_FORMAT_NORMAL),
len(ep)
))
return (ep,o,h,l,c,v)
'''
def _getPriceFromChild(self, startep, endep):
interval_secs = self.unitsecs * N_REQUEST_ROWS
(ep,o,h,l,c,v) = ([],[],[],[],[],[])
baseep = startep
while True:
lastep = min(endep,baseep+interval_secs)
(ep1,o1,h1,l1,c1,v1) = self.childDG.getPrice(baseep, lastep)
ep.extend(ep1)
o.extend(o1)
h.extend(h1)
l.extend(l1)
c.extend(c1)
v.extend(v1)
if lastep >= endep:
break;
baseep += interval_secs + self.unitsecs
if len(ep) == 0:
return ([],[],[],[],[],[])
try:
self.insertData(ep, o, h, l, c, v)
except Exception as e:
sqlstate = e.args[0]
if sqlstate == '23000':
lib.printInfo(ep[0], str(e))
else:
raise e
lib.log("Data from child. %s - %s - %d rows" % (
lib.epoch2str(startep, env.DATE_FORMAT_NORMAL),
lib.epoch2str(endep, env.DATE_FORMAT_NORMAL),
len(ep)
))
return (ep,o,h,l,c,v)
'''
def getPrice(self, startep, endep):
interval_secs = self.unitsecs * N_REQUEST_ROWS
(ep,o,h,l,c,v) = ([],[],[],[],[],[])
baseep = startep
while True:
lastep = min(endep,baseep+interval_secs)
(ep1,o1,h1,l1,c1,v1) = self._getPriceProc(baseep, lastep)
ep.extend(ep1)
o.extend(o1)
h.extend(h1)
l.extend(l1)
c.extend(c1)
v.extend(v1)
if lastep >= endep:
break;
baseep += interval_secs + self.unitsecs
return (ep,o,h,l,c,v)
'''
def getPrice(self, startep, endep):
startep = tradelib.getNearEpoch(self.granularity, startep)
endep = tradelib.getNearEpoch(self.granularity, endep)
(mi, ma) = self.getEpEdges()
if mi < 0:
return self._getPriceFromChild(startep, endep)
if mi > startep:
self._getPriceFromChild(startep, mi-self.unitsecs)
if ma < endep:
self._getPriceFromChild(ma+self.unitsecs, endep)
return self.selectData(startep, endep)