def addIntersects(db, globals): cursor = globals['cursor'] fileid = sqlbackend.parameter_list(['fileID'], globals['backend']) command = ''' SELECT moments.ticks, moments.row_id FROM moments WHERE moments.file_id = %s ORDER BY moments.ticks; ''' % fileid[0] cursor.execute(command, globals) moments = cursor.fetchall() p = sqlbackend.parameter_list(['moment_id', 'note_id'], globals['backend']) param = ', '.join(p) insert_command = 'INSERT INTO intersects (moment_id, note_id) ' \ + 'VALUES (%s);' % param oldProgress = 96 command = ''' SELECT startmoment.ticks, stopmoment.ticks, notes.row_id FROM notes JOIN events as start ON notes.startevent_id = start.row_id JOIN moments as startmoment ON startmoment.row_id = start.moment_id JOIN events as stop ON notes.endevent_id = stop.row_id JOIN moments as stopmoment ON stopmoment.row_id = stop.moment_id WHERE start.file_id = %s ORDER BY startmoment.ticks, stopmoment.ticks; ''' % fileid[0] cursor.execute(command, globals) notes = cursor.fetchall() rows = [] begin = newBegin = oldProgress = 0 for i in range(len(moments)): (momentTick, momentID) = moments[i] progress = int(96 + 5 * i / len(moments)) if progress > oldProgress: warn('%3d%%\n' % progress) oldProgress = progress if newBegin > begin: begin = newBegin newBegin = None for j in range(begin, len(notes)): (noteStart, noteEnd, noteID) = notes[j] if momentTick < noteStart: break if momentTick >= noteEnd: continue if newBegin == None: newBegin = j d = {'moment_id': int(momentID), 'note_id': int(noteID)} rows.append(d) # Because pyformat is broken in executemany() in MySQL: for row in rows: cursor.execute(insert_command, row)
def insertItem(table, dict, globals): ''' Use the items in a dictionary to create a new SQL table entry. ''' fullDict = {} for (k, v) in dict.iteritems(): fullDict[k] = v if v == '': fullDict[k] = None fields = ', '.join([k.replace('-', '_') for k in fullDict.iterkeys()]) p = sqlbackend.parameter_list(fullDict.keys(), globals['backend']) params = ', '.join(p) command = 'INSERT INTO %s (%s) VALUES (%s)' % (table, fields, params) cursor = globals['cursor'] errors = globals['errors'] try: cursor.execute(command, fullDict) except errors.get('IntegrityError'), err: warn("Error: item already exists in '%s' table.\n" % table) raise sys.exit(1)
def addParts(dom, fileInfo, parts, db, basicCursor, backend, measure_range, highlighted, printer): # Get measures ticks, measures = measure_ticks(basicCursor, fileInfo['row_id'], measure_range) if highlighted: highlighted = highlighted.split(',') part_id = sqlbackend.parameter_list(['part_id'], backend)[0] # Get voices voice_command = ''' SELECT DISTINCT voice FROM noteheads JOIN events ON events.row_id = noteheads.startevent_id WHERE events.part_id = %s ORDER BY voice ''' % part_id # Setup measure content cursors note_command = ''' SELECT noteheads.row_id, noteheads.*, moments.ticks, beam1, beam2, beam3, beam4, beam5, beam6 FROM noteheads JOIN events on events.row_id = noteheads.startevent_id JOIN moments on moments.row_id = events.moment_id LEFT OUTER JOIN beams on beams.notehead_id = noteheads.row_id WHERE events.part_id = %s AND moments.ticks >= %d AND moments.ticks < %d ORDER BY moments.ticks ''' % (part_id, ticks[0], ticks[1]) attribute_command = ''' SELECT DISTINCT attributes.*, moments.ticks FROM attributes JOIN events ON events.row_id = attributes.startevent_id JOIN moments ON moments.row_id = events.moment_id WHERE events.part_id = %s AND moments.ticks >= %d AND moments.ticks < %d ORDER BY moments.ticks ''' % (part_id, ticks[0], ticks[1]) barline_command = ''' SELECT barlines.*, moments.ticks FROM barlines JOIN events ON events.row_id = barlines.event_id JOIN moments ON moments.row_id = events.moment_id WHERE events.part_id = %s AND moments.ticks >= %d AND moments.ticks < %d ORDER BY moments.ticks ''' % (part_id, ticks[0], ticks[1]) direction_command = ''' SELECT directions.*, moments.ticks FROM directions JOIN events ON events.row_id = directions.event_id JOIN moments ON moments.row_id = events.moment_id WHERE events.part_id = %s AND moments.ticks >= %d AND moments.ticks < %d ORDER BY moments.ticks ''' % (part_id, ticks[0], ticks[1]) oldProgress = 0 for j in range(len(parts)): part = parts[j] printer.write('\t<part id="%s">\n' % part['part_attr']) part_id = {'part_id': part['row_id']} basicCursor.execute(voice_command, part_id) voices = basicCursor.fetchall() voices = [v[0] for v in voices] cursors = {'notes': db.cursor(), 'attributes': db.cursor(), 'barlines': db.cursor(), 'directions': db.cursor()} for cursor in cursors.values(): cursor.arraysize = 100 cursors['notes'].execute(note_command, part_id) cursors['attributes'].execute(attribute_command, part_id) cursors['barlines'].execute(barline_command, part_id) cursors['directions'].execute(direction_command, part_id) buffers = {'notes': [], 'attributes': [], 'barlines': [], 'directions': []} fetchmany(buffers['attributes'], cursors['attributes']) if not buffers['attributes']: getAttributeBefore(ticks[0], part, buffers['attributes'], basicCursor) for i in range(len(measures)): measure = measures[i] doc = dom.createDocumentFragment() mNode = addElement(dom, doc, 'measure', attr={'number': measure['number']}) addMeasureContents(dom, mNode, voices, measure, fileInfo, cursors, buffers, highlighted) partInc = 1 / len(parts) progress = int(100*(float(j) + float(i) / len(measures)) / len(parts)) if progress > oldProgress: timeLeft = (time.time() - StartTime) * \ (100 / float(progress) - 1) timeLeft = time.strftime('%M:%S', time.localtime(timeLeft)) warn('%d%% (%s remaining)\n' % (progress, timeLeft)) oldProgress = progress printer.write(nodeXML(mNode, '\t\t')) printer.write('\t</part>\n')