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 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 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 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