def CreateLoad():
import cgi
ret = Workspace()
form = cgi.FieldStorage()
# get the name, strip invalid quote characters
ret.name = form.getfirst('name', '').replace("'", '')
# make sure it's a valid request
if not ret.name:
return None
# get the workspace data
ret.db.execute("SELECT wsid, nextNoteNum,"
" DATE_FORMAT(time, '%%Y-%%m-%%d %%H:%%i:%%s')"
" FROM " + Db.getTableName(ret.name, 'workspaces') +
" WHERE wsname=%s", ret.name)
row = ret.db.fetchone()
if row:
ret.wsid, ret.nextNoteNum, ret.lasttime = row
if form.has_key('time'):
ret.lasttime = form['time'].value
# load the notes
table_name = Db.getTableName(ret.name, 'notes')
sql = ("SELECT %s FROM %s"
" WHERE wsid=%%s AND %s.time=%%s"
% (','.join(Note.Note.DBKEYS), table_name, table_name))
ret.db.execute(sql, [ret.wsid, ret.lasttime])
for row in ret.db.fetchall():
ret.notes.append(Note.Note.FromTuple(*row))
# this is a hack for now
# TODO: add this to the notes array
ret.newNoteText = (form.getfirst('nn', '')
.replace("\n", "\\n")
.replace("\r", "")
.replace("\l", ""))
if ret.newNoteText:
via = form.getfirst('via', '')
ret.newNoteText += "<br />via <a href='%s'>%s</a>" % (via, via)
return ret
def __del__(self):
"""Some cleanup code so the database doesn't get too icky"""
db = Db.getDBH()
cur = db.cursor()
cur.execute('SELECT wsid FROM wn_workspaces where wsname=%s',
[urllib.unquote(_WORKSPACE)])
wsid = cur.fetchone()[0]
cur.execute('DELETE FROM wn_notes WHERE wsid=%s AND time > %s',
[wsid, '20050530222438'])
def createUpdateWorkspace(self):
"""Create or update the workspaces table."""
table_name = Db.getTableName(self.name, 'workspaces')
self.db.execute("INSERT INTO " + table_name + "(wsname, nextNoteNum, time)"
" VALUES(%s, %s, %s)"
"ON DUPLICATE KEY UPDATE nextNoteNum=%s, time=%s",
(self.name, self.nextNoteNum, self.lasttime,
self.nextNoteNum, self.lasttime))
self.wsid = self.dbh.insert_id()
def __init__(self): self.name = '' self.notes = [] self.nextNoteNum = 0 self.lasttime = '' # string self.newNoteText = '' # create a db connection self.dbh = Db.getDBH() self.db = self.dbh.cursor()
def getDbInstance():
db_conf = CONF_PATH + '/db.ini'
confIns = loadConf(db_conf)
db_args = {}
db_args['host'] = confIns.get('db', 'host')
db_args['db'] = confIns.get('db', 'db')
db_args['user'] = confIns.get('db', 'user')
db_args['passwd'] = confIns.get('db', 'passwd')
db_args['charset'] = confIns.get('db', 'charset')
return Db.Mysql(db_args)
def main():
form = cgi.FieldStorage()
try:
offset = int(form.getfirst('offset', '0'))
except ValueError:
offset = 0
ws = Workspace.Workspace()
ws.name = form.getfirst('name', '')
# make sure it's a valid request
if not ws.name:
message.PlainText('No name entered.')
ws_table_name = Db.getTableName(ws.name, 'workspaces')
note_table_name = Db.getTableName(ws.name, 'notes')
ws.db.execute(("SELECT distinct"
" DATE_FORMAT(" + note_table_name + ".time, '%%Y-%%m-%%d %%H:%%i:%%s') as T"
" FROM " + note_table_name +
" INNER JOIN " + ws_table_name + " USING(wsid)"
" WHERE wsname=%s ORDER BY T DESC"
" LIMIT %s, %s"), (ws.name, offset, NUM_DATES+1))
loadTimes = [str(row[0]).strip() for row in ws.db.fetchall()]
message.PlainText('|'.join(loadTimes))
def updateData(brokerName, list):
sql = "insert into price values"
const = Const.Const()
for k in list.keys():
pattern = r"[0-9\.]+"
matchlist = re.findall(pattern, list[k])
b = matchlist[0]
a = matchlist[1]
s = round((float(a) - float(b)) * 100000) / 100000
sql = sql + "('" + brokerName + "','" + k + "'," + str(b) + "," + str(a) + "," + str(s) + ", current_timestamp),"
sql = sql[0:-1] + ";"
db = Db.Db()
db.execute(const.POSTGRE_HOST, const.POSTGRE_PORT, const.POSTGRE_DB, const.POSTGRE_USER, const.POSTGRE_PW, "delete from price where broker='" + brokerName + "';")
db.execute(const.POSTGRE_HOST, const.POSTGRE_PORT, const.POSTGRE_DB, const.POSTGRE_USER, const.POSTGRE_PW, sql)
def main():
form = cgi.FieldStorage()
ws = Workspace.Workspace()
ws.name = form.getfirst('name', '')
# make sure it's a valid request
if not ws.name:
message.PlainText('No name entered.')
return
table_name = Db.getTableName(ws.name, 'workspaces')
ws.db.execute("SELECT"
" DATE_FORMAT(time, '%%Y-%%m-%%d %%H:%%i:%%s') as T"
" FROM " + table_name +
" WHERE wsname=%s", ws.name)
row = ws.db.fetchone()
if row:
message.PlainText(str(row[0]).strip())
row = ws.db.fetchone()
else:
message.PlainText('')
def commit(self):
nowtime = datetime.datetime.now(TIMEZONE)
# we save dates in the database localized to the current timezone
self.lasttime = nowtime.strftime('%Y-%m-%d %H:%M:%S')
self.createUpdateWorkspace()
# save all the notes to wn_notes
if len(self.notes) > 0:
table_name = Db.getTableName(self.name, 'notes')
sql = ('INSERT INTO %s(%s, time, wsid)'
' VALUES(%s, %%s, %%s)'
% (table_name,
','.join(["%s" % k for k in Note.Note.DBKEYS]),
','.join(['%s'] * len(Note.Note.DBKEYS))))
values = [n.getValues() + [self.lasttime, self.wsid] for n in self.notes]
#log(sql)
self.db.executemany(sql, values)
self.dbh.commit()
return self.lasttime
your sysadmin to install it for you.</p>"""
raise
def create():
print "<p>Trying to create tables . . ."
try:
from lib import Db
from etc import common
assert common.TABLE_SHARDS > 0
assert len(common.TABLE_PREFIX) != 0
except Exception, e:
print "failed to read db values from config:", e
raise
try:
db = Db.getDBH()
cur = db.cursor()
shards = common.TABLE_SHARDS
max_digits = len(str(shards))
# create 'workspaces' and 'notes' table for each shard
for num in xrange(shards):
num = Db.getShardNum(num)
table_name = "%s%s%s" % (common.TABLE_PREFIX, "workspaces", num)
create_sql = CREATE_WORKSPACE % {'table_name': table_name}
cur.execute(create_sql)
table_name = "%s%s%s" % (common.TABLE_PREFIX, "notes", num)
create_sql = CREATE_NOTES % {'table_name': table_name}
cur.execute(create_sql)
except Exception, e:
#!/usr/bin/python
import MySQLdb
import csv
import sys
sys.path.insert(0, '..')
from lib import Db
cur = Db.getDBH().cursor()
print 'db'
cur.execute('select wsid, count(nid), time from wn_notes'
' where wsid != 15'
' group by wsid, time order by wsid')
writer = csv.writer(open('usage.csv', 'w'))
print 'write'
writer.writerows(cur.fetchall())
