def numTasksBy(self, qualifier, type='task', status=None):
from helix.database.sql import Manager
from helix import Fix, Show
if isinstance(self, Show):
idQualifier = None
show = self.alias
else:
idQualifier = self.__class__.__name__.lower() + 'Id'
show = self.show
with Manager(willCommit=False) as mgr:
query = """SELECT {}, COUNT(*) FROM {} WHERE show='{}' and type='{}'""".format(qualifier, Fix.TABLE, show, type)
if idQualifier is not None:
query += " AND {}='{}'".format(idQualifier, self.id)
if status is not None:
query += " AND status='{}'".format(status)
query += " GROUP BY {}".format(qualifier)
rows = mgr.connection().execute(query).fetchall()
results = collections.defaultdict(int)
if rows:
for r in rows:
if not r[0]:
results['_'] += r[1]
else:
results[r[0]] += r[1]
return results
else:
return results
def get(self, attr, default=None):
with Manager(willCommit=False) as mgr:
try:
return mgr.connection().execute('SELECT {} FROM {} WHERE {}="{}"'.format(attr, self.table, self.pk, getattr(self, self.pk, None))).fetchone()[0]
except sqlite3.OperationalError as e:
print 'No such attribute: {}, defaulting to {}'.format(attr, default)
return default
def numTasks(self, type='task', status=None, department=None, user=None):
from helix.database.sql import Manager
from helix import Fix, Show
if isinstance(self, Show):
idQualifier = None
show = self.alias
else:
idQualifier = self.__class__.__name__.lower() + 'Id'
show = self.show
with Manager(willCommit=False) as mgr:
query = """SELECT COUNT(*) FROM {} WHERE show='{}' and type='{}'""".format(Fix.TABLE, show, type)
if idQualifier is not None:
query += " AND {}='{}'".format(idQualifier, self.id)
if status is not None:
query += " AND status='{}'".format(status)
if department is not None:
query += " AND for_dept='{}'".format(department)
if user is not None:
query += " AND fixer='{}'".format(user)
row = mgr.connection().execute(query).fetchone()
if row and row[0]:
return row[0]
else:
return 0
def completion(self):
from helix.database.sql import Manager
from helix import Fix, Show
if isinstance(self, Show):
idQualifier = None
show = self.alias
else:
idQualifier = self.__class__.__name__.lower() + 'Id'
show = self.show
with Manager(willCommit=False) as mgr:
query = """SELECT status, COUNT(*) FROM {} WHERE show='{}' and type='task'""".format(Fix.TABLE, show)
if idQualifier is not None:
query += " AND {}='{}'".format(idQualifier, self.id)
query += "GROUP BY status"
rows = mgr.connection().execute(query).fetchall()
if rows:
done = 0
total = 0
for r in rows:
total += r[1] # Add count for this status to total
if r[0] == 'done':
done += r[1]
return float(done) / total
else:
# No tasks at all, 0% completion. Maybe we want no tasks to mean 100% completion?
return 0
def testFix(self):
fix1 = Fix('Test fix', 'This is the body', show='foobar')
self.assertTrue(fix1._exists)
self.assertTrue(fix1.exists())
self.assertTrue(Show(fix1.show).exists())
self.assertIs(fix1.sequence, None)
self.assertIs(fix1.sequenceId, None)
self.assertFalse(
Fix('Test fix2', 'This is the body', show='foobar').exists())
self.assertFalse(
Fix('Test fix', 'This is the body', show='foobar',
sequence=100).exists())
# Nonextant sequence
with self.assertRaises(ValueError):
f = Fix('Test fix',
'This is the body',
show='foobar',
sequence=200)
# Nonextant user
with self.assertRaises(ValueError):
f = Fix('Another fix',
'This is the body',
show='foobar',
author='Bob')
with Manager(willCommit=False) as mgr:
for c in [c[0] for c in mgr.getColumnNames(fix1.table)]:
self.assertTrue(hasattr(fix1, c))
# Try inserting again, should fail
self.assertFalse(fix1.insert())
def getShots(self, seqs=[], nums=[]):
from helix.database.sql import Manager
from helix.database.shot import Shot
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {} WHERE show='{}'""".format(
Shot.TABLE, self.alias)
if seqs is not None:
if isinstance(seqs, int):
seqs = [seqs]
if seqs:
query += " AND sequence IN ({})".format(','.join(
["'{}'".format(n) for n in seqs]))
if nums is not None:
if isinstance(nums, int):
nums = [nums]
if nums:
query += " AND num IN ({})".format(','.join(
["'{}'".format(n) for n in nums]))
shots = []
for row in mgr.connection().execute(query).fetchall():
shots.append(Shot.dummy().unmap(row))
return shots
def setUpClass(cls):
if not os.path.exists(os.environ['HELIX_DB']):
if not os.path.isdir(os.path.dirname(os.environ['HELIX_DB'])):
os.makedirs(os.path.dirname(os.environ['HELIX_DB']))
open(os.environ['HELIX_DB'], 'w').close()
with Manager() as mgr:
mgr.initTables()
Person('spaouellet').insert()
Person('foo').insert()
Show('foobar', makeDirs=True).insert()
Sequence(100, 'foobar', makeDirs=True).insert()
Sequence(900, 'foobar', makeDirs=True).insert()
Shot(100, 100, 'foobar', makeDirs=True).insert()
Shot(200, 100, 'foobar', makeDirs=True).insert()
Shot(200, 900, 'foobar', makeDirs=True).insert()
Element('test', 'prop', 'foobar', makeDirs=True).insert()
e = Element('camera',
'camera',
'foobar',
sequence=100,
makeDirs=True)
e.set('assigned_to', 'foo', insertIfMissing=True)
Element('render',
'plate',
'foobar',
shot=100,
sequence=100,
makeDirs=True).insert()
Fix('Test fix', 'This is the body', show='foobar').insert()
env.setEnvironment('show', 'foobar')
def testPF(self):
el = Element('test', 'prop', 'foobar')
pf = PublishedFile(el.name, el.type, '', show=el.show)
self.assertFalse(pf.exists())
self.assertIsNotNone(pf.elementId)
self.assertEqual(pf.version, 1)
self.assertTrue(pf.insert())
# Nonextant element
with self.assertRaises(ValueError):
p = PublishedFile('nonextant', 'prop', '', show=el.show)
# Nonextant fix
with self.assertRaises(ValueError):
e = Element('test2', 'prop', 'foobar')
e.insert()
p = PublishedFile(e.name, e.type, '', show=e.show, fix=2)
self.assertEqual(
PublishedFile(el.name, el.type, '', show=el.show).version, 2)
with Manager(willCommit=False) as mgr:
for c in [c[0] for c in mgr.getColumnNames(pf.table)]:
self.assertTrue(hasattr(pf, c))
# Insert again..
self.assertFalse(pf.insert())
def getShow(alias):
from helix.database.show import Show
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {} WHERE alias='{}'""".format(Show.TABLE, alias)
row = mgr.connection().execute(query).fetchone()
if row and row[0]:
return Show.dummy().unmap(row)
return None
def getPublishedVersions(self):
from helix.database.sql import Manager
from helix.database.publishedFile import PublishedFile
with Manager(willCommit=False) as mgr:
query = """SELECT version FROM {} WHERE show='{}' AND elementId='{}'""".format(
PublishedFile.TABLE, self.show, self.id)
rows = mgr.connection().execute(query).fetchall()
return [r[0] for r in rows]
def getElements():
from helix.database.element import Element
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {}""".format(Element.TABLE)
rows = mgr.connection().execute(query).fetchall()
elements = []
for r in rows:
elements.append(Element.dummy().unmap(r))
return elements
def getShows():
from helix.database.show import Show
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {}""".format(Show.TABLE)
rows = mgr.connection().execute(query).fetchall()
shows = []
for r in rows:
shows.append(Show.dummy().unmap(r))
return shows
def getUsers():
from helix.database.person import Person
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {}""".format(Person.TABLE)
rows = mgr.connection().execute(query).fetchall()
users = []
for r in rows:
users.append(Person.dummy().unmap(r))
return users
def getStages(self):
from helix.database.sql import Manager
from helix import Stage
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {} WHERE shotId='{}'""".format(Stage.TABLE, self.id)
stages = []
for row in mgr.connection().execute(query).fetchall():
stages.append(Stage.dummy().unmap(row))
return stages
def nextVersion(show, element):
from helix.database.sql import Manager
with Manager(willCommit=False) as mgr:
res = mgr.connection().execute('''
SELECT MAX(version) from {} WHERE show='{}' AND elementId='{}'
'''.format(PublishedFile.TABLE, show, element)).fetchone()
if res and res[0]:
return int(res[0]) + 1
else:
return 1
def map(self):
with Manager(willCommit=False) as mgr:
values = ()
for c, notNull in mgr.getColumnNames(self.table):
val = getattr(self, c, None)
if val is None and notNull:
raise ValueError('{} for {} cannot be null'.format(c, type(self).__name__))
values += (val, )
return values
def __repr__(self):
with Manager(willCommit=False) as mgr:
vals = []
for c, _ in mgr.getColumnNames(self.table):
val = getattr(self, c, None)
if val is None:
val = 'NULL'
vals.append((c + '=' + str(val)))
return type(self).__name__ + ' (' + ', '.join(vals) + ')'
def sql(statement): from helix.database.sql import Manager willCommit = False if statement.split()[0].lower() != 'select': willCommit = True with Manager(willCommit=willCommit) as mgr: res = mgr.connection().execute(statement) for l in res: print ' | '.join([str(s) for s in l])
def unmap(self, values): with Manager(willCommit=False) as mgr: for col, val in zip([c[0] for c in mgr.getColumnNames(self.table)], list(values)): try: setattr(self, col, val) except: # Gross, but for private variables we are # probably calculating them a different way anyway # when we retrieve them later pass self._exists = True return self
def nextSnapshotNum(show, sequence, shot):
from helix.database.sql import Manager
with Manager(willCommit=False) as mgr:
res = mgr.connection().execute(
'''
SELECT MAX(num) from {} WHERE show='{}' AND sequenceId='{}' AND shotId='{}'
'''.format(Snapshot.TABLE, show, sequence, shot)
).fetchone()
if res and res[0]:
return int(res[0]) + 1
else:
return 1
def delete(self, clean=False):
with Manager() as mgr:
if clean:
try:
if hasattr(self, 'work_path'):
shutil.rmtree(self.work_path)
if hasattr(self, 'release_path'):
shutil.rmtree(self.release_path)
except:
raise DatabaseError('Unable to delete some files on disk')
finally:
return mgr._delete(self)
else:
return mgr._delete(self)
def set(self, attr, val, insertIfMissing=False):
with Manager() as mgr:
if self.exists():
mgr.connection().execute("UPDATE {} SET {}={} WHERE {}='{}'".format(
self.table,
attr,
"'{}'".format(val) if val is not None else "NULL",
self.pk,
getattr(self, self.pk))
)
else:
setattr(self, attr, val)
if insertIfMissing:
self.insert()
def testElement(self):
el = Element('test', 'prop', 'foobar', 100, 100)
self.assertFalse(el._exists)
self.assertFalse(el.exists())
self.assertTrue(Show(el.show).exists())
self.assertTrue(Sequence(el.sequence, el.show).exists())
self.assertTrue(Shot(el.shot, el.sequence, el.show).exists())
self.assertTrue(Element('test', 'prop', 'foobar').exists())
el.insert()
pf = PublishedFile('test',
'prop',
'',
shot=100,
sequence=100,
show='foobar')
pf.insert()
self.assertEqual(el.getPublishedFiles()[0].id, pf.id)
self.assertIn(el.table, Manager.TABLE_LIST)
self.assertFalse(os.path.exists(el.work_path))
self.assertFalse(os.path.exists(el.release_path))
# No element type
with self.assertRaises(ValueError):
badEl = Element('foo', None, 'foobar')
# Can be nameless, but only if we give shot and seq
with self.assertRaises(ValueError):
badEl = Element(None, 'prop', 'foobar')
# Should be procedurally generated
self.assertIsNotNone(Element(None, 'prop', 'foobar', 100, 100).name)
# Improper element type
with self.assertRaises(ValueError):
badEl = Element('foo', 'bar', 'foobar')
with Manager(willCommit=False) as mgr:
for c in [c[0] for c in mgr.getColumnNames(el.table)]:
self.assertTrue(hasattr(el, c))
# Try inserting again, should fail
self.assertFalse(Element('test', 'prop', 'foobar').insert())
def fromPk(cls, pk):
if not pk:
return None
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {} WHERE {}='{}'""".format(
cls.TABLE,
cls.PK,
pk
)
row = mgr.connection().execute(query).fetchone()
if row:
return cls.dummy().unmap(row)
return None
def byNum(fixNum, show=None):
show = show if show else env.show
if not show:
raise ValueError(
'Tried to fallback to environment-set show, but it was null.')
from helix.database.sql import Manager
with Manager(willCommit=False) as mgr:
res = mgr.connection().execute('''
SELECT * from {} WHERE num='{}' AND show='{}'
'''.format(Fix.TABLE, fixNum, show)).fetchone()
if res:
return Fix('.', '.', show).unmap(res)
else:
return None
def nextFixNum(show=None):
show = show if show else env.show
if not show:
raise ValueError(
'Tried to fallback to environment-set show, but it was null.')
from helix.database.sql import Manager
with Manager(willCommit=False) as mgr:
res = mgr.connection().execute('''
SELECT MAX(num) from {} WHERE show='{}'
'''.format(Fix.TABLE, show)).fetchone()
if res and res[0]:
return int(res[0]) + 1
else:
return 1
def getLatestSnapshot(self):
from helix.database.sql import Manager
from helix import Snapshot
with Manager(willCommit=False) as mgr:
res = mgr.connection().execute(
'''
SELECT *
FROM {table}
WHERE num = (SELECT MAX(num) FROM {table} WHERE show='{show}' AND sequenceId='{seq}' AND shotId='{shot}')
'''.format(table=Snapshot.TABLE, show=self.show, seq=self.parent.id, shot=self.id)
).fetchone()
if res:
return Snapshot.dummy().unmap(res)
else:
return None
def getAll(cls):
from helix.database.show import Show
from helix.database.sequence import Sequence
from helix.database.shot import Shot
from helix.database.element import Element
from helix.database.person import Person
from helix.database.fix import Fix
from helix.environment.permissions import PermissionGroup
with Manager(willCommit=False) as mgr:
query = """SELECT * FROM {}""".format(cls.TABLE)
rows = mgr.connection().execute(query).fetchall()
instances = []
for r in rows:
instances.append(cls.dummy().unmap(r))
return instances
def fromPath(path):
if not os.path.isdir(os.path.dirname(path)):
return None
fs = FrameSequence(path)
if fs.isValid():
path = fs.getFormatted(includeDir=True)
from helix.database.sql import Manager
with Manager(willCommit=False) as mgr:
res = mgr.connection().execute('''
SELECT * from {} WHERE file_path='{}' OR versionless_path='{}'
'''.format(PublishedFile.TABLE, path, path)).fetchone()
if res:
return PublishedFile.dummy().unmap(res)
return None
def exists(self, fetch=False):
# we cache the exists after construction because we either fetched
# it from the DB or made a new one
if self._exists is not None and not fetch:
return self._exists
with Manager(willCommit=False) as mgr:
try:
rows = mgr.connection().execute('SELECT * FROM {} WHERE {}="{}"'.format(self.table, self.pk, getattr(self, self.pk, None))).fetchall()
if fetch:
return rows[0] if rows else None
else:
return len(rows) > 0
except sqlite3.OperationalError as e:
print e
if fetch:
return None
else:
return False