def position_calculated(position, session, checkpoint=None, start=None, end=None):
if start is None:
start = position.position_cp_timestamp or timestamp_to_dt(0)
if checkpoint is None:
checkpoint = position.position_checkpoint or 0
rows = session.query(func.sum(models.Posting.quantity).label('quantity_sum'),
func.max(models.Journal.timestamp).label('last_timestamp')).filter_by(
username=position.username).filter_by(
contract_id=position.contract_id).filter(
models.Journal.id==models.Posting.journal_id).filter(
models.Journal.timestamp > start)
if end is not None:
rows = rows.filter(models.Journal.timestamp <= end)
try:
grouped = rows.group_by(models.Posting.username).one()
calculated = grouped.quantity_sum
last_posting_timestamp = grouped.last_timestamp
except NoResultFound:
calculated = 0
last_posting_timestamp = start
return int(checkpoint + calculated), last_posting_timestamp
def position_calculated(position,
session,
checkpoint=None,
start=None,
end=None):
if start is None:
start = position.position_cp_timestamp or timestamp_to_dt(0)
if checkpoint is None:
checkpoint = position.position_checkpoint or 0
rows = session.query(
func.sum(models.Posting.quantity).label('quantity_sum'),
func.max(models.Journal.timestamp).label('last_timestamp')).filter_by(
username=position.username).filter_by(
contract_id=position.contract_id).filter(
models.Journal.id == models.Posting.journal_id).filter(
models.Journal.timestamp > start)
if end is not None:
rows = rows.filter(models.Journal.timestamp <= end)
try:
grouped = rows.group_by(models.Posting.username).one()
calculated = grouped.quantity_sum
last_posting_timestamp = grouped.last_timestamp
except NoResultFound:
calculated = 0
last_posting_timestamp = start
return int(checkpoint + calculated), last_posting_timestamp
def get_transaction_history(self, from_timestamp, to_timestamp, username):
result = yield self.dbpool.runQuery(
"SELECT contracts.ticker, SUM(posting.quantity) FROM posting, journal, contracts "
"WHERE posting.journal_id=journal.id AND posting.username=%s AND journal.timestamp<%s "
"AND posting.contract_id=contracts.id GROUP BY contracts.ticker",
(username, util.timestamp_to_dt(from_timestamp)))
balances = collections.defaultdict(int)
for row in result:
balances[row[0]] = int(row[1])
result = yield self.dbpool.runQuery(
"SELECT contracts.ticker, journal.timestamp, posting.quantity, journal.type, posting.note "
"FROM posting, journal, contracts WHERE posting.journal_id=journal.id AND "
"posting.username=%s AND journal.timestamp>=%s AND journal.timestamp<=%s "
"AND posting.contract_id=contracts.id ORDER BY journal.timestamp",
(username, util.timestamp_to_dt(from_timestamp),
util.timestamp_to_dt(to_timestamp)))
transactions = []
for row in result:
balances[row[0]] += row[2]
quantity = abs(row[2])
# Here we assume that the user is a Liability user
if row[2] < 0:
direction = 'debit'
else:
direction = 'credit'
transactions.append({
'contract': row[0],
'timestamp': util.dt_to_timestamp(row[1]),
'quantity': quantity,
'type': row[3],
'direction': direction,
'balance': balances[row[0]],
'note': row[4]
})
returnValue(transactions)
def get_transaction_history(self, from_timestamp, to_timestamp, username):
result = yield self.dbpool.runQuery(
"SELECT contracts.ticker, SUM(posting.quantity) FROM posting, journal, contracts "
"WHERE posting.journal_id=journal.id AND posting.username=%s AND journal.timestamp<%s "
"AND posting.contract_id=contracts.id GROUP BY contracts.ticker",
(username, util.timestamp_to_dt(from_timestamp)))
balances = collections.defaultdict(int)
for row in result:
balances[row[0]] = int(row[1])
result = yield self.dbpool.runQuery(
"SELECT contracts.ticker, journal.timestamp, posting.quantity, journal.type, posting.note "
"FROM posting, journal, contracts WHERE posting.journal_id=journal.id AND "
"posting.username=%s AND journal.timestamp>=%s AND journal.timestamp<=%s "
"AND posting.contract_id=contracts.id ORDER BY journal.timestamp",
(username, util.timestamp_to_dt(from_timestamp), util.timestamp_to_dt(to_timestamp)))
transactions = []
for row in result:
balances[row[0]] += row[2]
quantity = abs(row[2])
# Here we assume that the user is a Liability user
if row[2] < 0:
direction = 'debit'
else:
direction = 'credit'
transactions.append({'contract': row[0],
'timestamp': util.dt_to_timestamp(row[1]),
'quantity': quantity,
'type': row[3],
'direction': direction,
'balance': balances[row[0]],
'note': row[4]})
returnValue(transactions)
def atomic_commit(self, postings):
start = time.time()
log.msg("atomic commit called for %s at %f" % (postings, start))
try:
# sanity check
if len(postings) == 0:
raise INTERNAL_ERROR
types = [posting["type"] for posting in postings]
counts = [posting["count"] for posting in postings]
if not all(type == types[0] for type in types):
raise TYPE_MISMATCH
if not all(count == counts[0] for count in counts):
raise COUNT_MISMATCH
# balance check
debitsum = defaultdict(int)
creditsum = defaultdict(int)
log.msg("auditing postings at %f" % (time.time() - start))
for posting in postings:
if posting["direction"] == "debit":
debitsum[posting["contract"]] += posting["quantity"]
if posting["direction"] == "credit":
creditsum[posting["contract"]] += posting["quantity"]
for ticker in debitsum:
if debitsum[ticker] - creditsum[ticker] is not 0:
raise QUANTITY_MISMATCH
# create the journal and postings
# The journal is created separately from the postings but this is ok because
# all the postings are created at once. If the posting commit fails then we'll
# just end up with an empty journal which won't break anything
# TODO: Create the journal and postings together
log.msg("creating the journal at %f" % (time.time() - start))
ins = Journal.__table__.insert()
result = self.execute(ins, type=types[0], timestamp=datetime.datetime.utcnow())
journal_id = result.inserted_primary_key[0]
log.msg("creating the db postings at %f" % (time.time() - start))
db_postings = []
for posting in postings:
contract_table = Contract.__table__
s = select([contract_table.c.id], contract_table.c.ticker==posting["contract"])
result = self.execute(s)
contract_id = result.first()[0]
user_table = User.__table__
s = select([user_table.c.type], user_table.c.username==posting["username"])
result = self.execute(s)
user_type = result.first()[0]
username = posting["username"]
quantity = posting["quantity"]
direction = posting["direction"]
note = posting["note"]
if posting["timestamp"] is not None:
timestamp = timestamp_to_dt(posting["timestamp"])
else:
timestamp = None
if direction == 'debit':
if user_type == 'Asset':
sign = 1
else:
sign = -1
else:
if user_type == 'Asset':
sign = -1
else:
sign = 1
posting = {'username': username,
'contract_id': contract_id,
'quantity': sign * quantity,
'note': note,
'timestamp': timestamp,
'journal_id': journal_id
}
db_postings.append(posting)
log.msg("done making posting at %f: %s" % (time.time() - start, posting))
ins = Posting.__table__.insert()
result = self.execute(ins, db_postings)
log.msg("Inserted %d rows of %d postings" % (result.rowcount, len(db_postings)))
log.msg("Done committing postings at %f" % (time.time() - start))
return True
except Exception, e:
log.err("Caught exception trying to commit. Postings were:")
for posting in postings:
log.err(str(posting))
log.err("Stack trace follows:")
log.err()
if isinstance(e, SQLAlchemyError):
raise DATABASE_ERROR
raise e
def atomic_commit(self, postings):
start = time.time()
log.msg("atomic commit called for %s at %f" % (postings, start))
try:
# sanity check
if len(postings) == 0:
raise INTERNAL_ERROR
types = [posting["type"] for posting in postings]
counts = [posting["count"] for posting in postings]
if not all(type == types[0] for type in types):
raise TYPE_MISMATCH
if not all(count == counts[0] for count in counts):
raise COUNT_MISMATCH
# balance check
debitsum = defaultdict(int)
creditsum = defaultdict(int)
log.msg("auditing postings at %f" % (time.time() - start))
for posting in postings:
if posting["direction"] == "debit":
debitsum[posting["contract"]] += posting["quantity"]
if posting["direction"] == "credit":
creditsum[posting["contract"]] += posting["quantity"]
for ticker in debitsum:
if debitsum[ticker] - creditsum[ticker] is not 0:
raise QUANTITY_MISMATCH
# create the journal and postings
# The journal is created separately from the postings but this is ok because
# all the postings are created at once. If the posting commit fails then we'll
# just end up with an empty journal which won't break anything
# TODO: Create the journal and postings together
log.msg("creating the journal at %f" % (time.time() - start))
ins = Journal.__table__.insert()
result = self.execute(ins,
type=types[0],
timestamp=datetime.datetime.utcnow())
journal_id = result.inserted_primary_key[0]
log.msg("creating the db postings at %f" % (time.time() - start))
db_postings = []
for posting in postings:
contract_table = Contract.__table__
s = select([contract_table.c.id],
contract_table.c.ticker == posting["contract"])
result = self.execute(s)
contract_id = result.first()[0]
user_table = User.__table__
s = select([user_table.c.type],
user_table.c.username == posting["username"])
result = self.execute(s)
user_type = result.first()[0]
username = posting["username"]
quantity = posting["quantity"]
direction = posting["direction"]
note = posting["note"]
if posting["timestamp"] is not None:
timestamp = timestamp_to_dt(posting["timestamp"])
else:
timestamp = None
if direction == 'debit':
if user_type == 'Asset':
sign = 1
else:
sign = -1
else:
if user_type == 'Asset':
sign = -1
else:
sign = 1
posting = {
'username': username,
'contract_id': contract_id,
'quantity': sign * quantity,
'note': note,
'timestamp': timestamp,
'journal_id': journal_id
}
db_postings.append(posting)
log.msg("done making posting at %f: %s" %
(time.time() - start, posting))
ins = Posting.__table__.insert()
result = self.execute(ins, db_postings)
log.msg("Inserted %d rows of %d postings" %
(result.rowcount, len(db_postings)))
log.msg("Done committing postings at %f" % (time.time() - start))
return True
except Exception, e:
log.err("Caught exception trying to commit. Postings were:")
for posting in postings:
log.err(str(posting))
log.err("Stack trace follows:")
log.err()
if isinstance(e, SQLAlchemyError):
raise DATABASE_ERROR
raise e
