class sqlite:
schema = Schema(
tables = dict(
queries = Table(
id ='integer not null primary key autoincrement',
name ='varchar(65) unique not null',
path ='text(255) not null',
),
),
indexes = dict( shortcut_name_index = Index('queries', 'name')),
)
def save_shortcut( me, q):
log('save_shortcut:', q)
if not q.id:
me.sql('insert into queries (name, path) values (?,?)', q.name, q.path)
q.id = me.last_insert_rowid()
return q.id
me.sql('update queries set name=? path=? where id=?', q.name, q.path, q.id)
def delete_shortcut( me, q):
me.sql('delete from queries where id = ?', q.id)
def get_shortcut( me, name):
d = me.first( me.query('select id, name, path from queries where name=?', name))
if d:
id_, name, path = d
return Queries.Shortcut( id=id_, name=name, path=path)
return None
def get_all_shortcuts( me):
return [ Queries.Shortcut( id=id_, name=name, path=path)
for id_, name, path in me.query('select id, name, path from queries') ]
class sqlite:
schema = Schema(
tables=dict(items=Table(
id='integer not null primary key autoincrement',
name='text(255) not null',
), ),
indexes=dict(item_name_index=Index('items', 'name')),
)
def get_item(me, id_=None, name=None):
where = []
args = []
if id_:
where.append('id=?')
args.append(id_)
if name:
where.append('name=?')
args.append(name)
s = 'select id, name from items' + (' where ' if where else
'') + ' and '.join(where)
return [
Archive.Item(id=_id, name=name)
for _id, name in me.query(s, *args)
]
def save_item(me, item):
if not item.id:
log('create_item:', item)
me.sql('insert into items (name) values (?)', item.name)
item.id = me.last_insert_rowid()
return item.id
me.sql('update items set name=? where id=?', item.name, item.id)
def delete_item(me, item):
me.sql('delete from items where id = ?', item.id)
class sqlite:
schema = Schema(
UsesTables( Archive, 'items'),
UsesTables( Tags, 'tagging'),
)
def get_untagged( me):
q = me.query('''
select id, name from items
where not exists (select * from tagging where tagging.item_id == items.id)
''')
return [ Archive.Item( id=_id, name=name) for _id, name in q ]
class sqlite:
schema = Schema(
UsesTables( Archive, 'items'),
UsesTables( Tags, 'tags', 'tagging'),
)
def build_items_sql( me, items_tree):
s = me.build_tags_sql( items_tree)
s = s.replace('tagging', 'Tagging')
s = s.replace('item', 'Tag')
s = s.replace('tag', 'Item')
return s
def query_by_items( me, items_tree):
return [ Tags.Tag( id=id_, name=name) for id_,name in me.query( me.build_items_sql( items_tree)) ]
def get_tag_by_name( me, name):
d = me.first( me.query('select id, name from tags where name=?', name))
if d:
return Tags.Tag( id=d[0], name=d[1])
return None
class sqlite( Storage):
conn = None
schema = Schema()
def __init__( me, context, memory =False):
name = me.name = memory and ':memory:' or os.path.join( context.repository, 'db.sql')
if not me.conn:
log('open db name: ', name)
me.__class__.conn = sqlite3.connect( name)
me.create_schema()
def create_schema( me):
for name, columns in me.schema.iter_over('tables'):
me._create_table( name, **columns)
for name, index in me.schema.iter_over('indexes'):
me._create_index( name, index.table, *index.columns)
for name, trigger in me.schema.iter_over('triggers'):
me._create_trigger( name, trigger.when, *trigger.what)
def drop_schema( me):
s = 'drop %(typ)s if exists %(name)s'
for typ in me.schema.target_types:
for name, ignored in me.schema.iter_over( typ):
me.conn.execute( s % locals())
@classmethod
def include( me, db):
sch = getattr( db, 'schema', None)
if sch:
me.schema.update( sch)
me._include( db)
def _create_table( me, table_, **columns):
constraints = dict( (k,v) for k,v in columns.iteritems() if isinstance(v, Constraint))
for k in constraints:
del columns[k]
s = 'create table if not exists %(table_)s' % locals()
s += '('
s += ', '.join([ '%s %s' % (k,v) for k,v in columns.iteritems() ])
if constraints:
s += ', '+', '.join([ 'constraint %s %s' % (k,v) for k,v in constraints.iteritems() ])
s += ')'
me.conn.execute( s)
def _create_index( me, index_, table_, *columns):
s = 'create index if not exists %(index_)s on %(table_)s' % locals()
s += '(' + ','.join( columns)+')'
me.conn.execute( s)
def _create_trigger( me, trigger_, when_, *what_):
s = 'create trigger if not exists %(trigger_)s %(when_)s begin\n' % locals()
s += '\n'.join( [w+';' for w in what_])
s += 'end'
me.conn.execute( s)
def sql( me, s, *values):
log('sqlllll:', s)
me.conn.execute( s, values)
def sql_many( me, s, values):
log('sqlllll many:', s)
me.conn.executemany( s, values)
def query( me, sql, *values):
log('queryyy:', sql)
c = me.conn.cursor()
c.execute( sql, values)
res = c.fetchall()
c.close()
return res
def first( me, r):
return None if not r else r[0]
def value( me, sql, *args):
return me.first( me.first( me.query( sql, *args)))
def commit( me):
me.conn.commit()
def rollback( me):
me.conn.rollback()
def last_insert_rowid( me):
return me.value('select last_insert_rowid()')
class sqlite:
schema = Schema(
UsesTables(Tags, 'tags'),
tables=dict(
inctags=Table(
ltag='integer not null',
rtag='integer not null',
inctags_key=Constraint(
'unique (ltag, rtag)'), #or compose-primary-key?
), ),
indexes=dict(inctags_index=Index('inctags', 'ltag', 'rtag')),
)
# store only primary links ; infer in-memory the consequences
@exec_before('query_by_tags')
def reason_tree(me, tree):
new_tree = []
for ands in tree:
new_ands = []
for tag in ands:
neg = None
if isinstance(tag, QAspect.Negative):
neg = tag
tag = neg.operand
alt_tags = list(RelationsBase.links.query(tag, IS)) + list(
RelationsBase.links.query(tag, HAS))
if alt_tags:
if neg:
alt_tags = [QAspect.Negative(t) for t in alt_tags]
new_ands.append([neg or tag] + alt_tags)
else:
new_ands.append(neg or tag)
new_tree.append(new_ands)
tree[:] = new_tree # modify param for query_by_tags
@exec_after('delete_tag')
def on_delete_tag(me, tag):
me.sql('delete from inctags where ltag=? or rtag=?', tag.id,
tag.id)
RelationsBase.links.delete_tag(tag)
def save_rel(me, ltag, rtag, typ):
log('save_rel:', ltag, rtag, typ)
me.sql(
'delete from inctags where (ltag=? and rtag=?) or (ltag=? and rtag=?)',
ltag.id, rtag.id, rtag.id, ltag.id)
eq = typ == 'is'
if eq or typ == 'has':
me.sql('insert into inctags( ltag, rtag) values(?,?)', ltag.id,
rtag.id)
if eq or typ == 'in':
me.sql('insert into inctags( ltag, rtag) values(?,?)', rtag.id,
ltag.id)
RelationsBase.links.set(ltag, rtag, typ)
def delete_rel(me, ltag, rtag, typ):
log('delete_rel:', ltag, rtag, typ)
eq = typ == 'is'
if eq or typ == 'has':
me.sql('delete from inctags where ltag=? and rtag=?', ltag.id,
rtag.id)
if eq or typ == 'in':
me.sql('delete from inctags where ltag=? and rtag=?', rtag.id,
ltag.id)
RelationsBase.links.unset(ltag, rtag, typ)
def get_all_rels(me):
return [(Tags.Tag(id=lid, name=lname), Tags.Tag(id=rid,
name=rname))
for lid, lname, rid, rname in me.query('''
select inctags.ltag as leftid, ltags.name as lname, inctags.rtag as rightid, rtags.name as rname
from inctags
join tags as ltags on ltags.id == inctags.ltag
join tags as rtags on rtags.id == inctags.rtag''')]
class sqlite:
schema = Schema(
UsesTables( Tags, 'tags'),
tables = dict(
metatags = Table(
id ='integer not null primary key autoincrement',
name ='varchar(65) unique not null',
),
metatagging = Table(
tag_id ='integer not null',
meta_id ='integer not null',
joint_id ='integer not null',
metatagging_key=Constraint('unique (tag_id, meta_id, joint_id)'),
),
),
indexes = dict(
metatagging_index = Index('metatagging', 'tag_id', 'meta_id', 'joint_id'),
),
)
# metatags
def save_meta( me, metatag):
if not metatag.id:
log('create_metatag:', metatag)
me.sql('insert into metatags(name) values(?)', metatag.name)
metatag.id = me.last_insert_rowid()
else:
me.sql('update metatags set name=? where id=?', metatag.name, metatag.id)
MetaTags.name2metatag[ metatag.name] = metatag
def delete_meta( me, metatag):
me.sql('delete from metatags where id = ?', metatag.id)
joints = [ Tags.Tag(id=id_, name=name)
for id_, name in me.query('''
select tags.id as tag_id, tags.name as tag_name
from tags
join metatagging on tags.id == metatagging.joint_id
where metatagging.meta_id=?''', metatag.id) ]
for j in joints:
me.delete_tag( j)
del MetaTags.name2metatag[ metatag.name ]
def get_all_metatags( me):
q = me.query('select id, name from metatags')
return [ MetaTags.MetaTag( id=id_, name=name) for id_, name in q ]
# tag and metatags
def get_tag_metatags( me, tag):
q = me.query('''
select metatags.id as meta_id, metatags.name as meta_name from metatags
join metatagging on metatags.id == metatagging.meta_id
where metatagging.tag_id = ?''', tag.id)
return [ MetaTags.MetaTag( id=id_, name=name) for id_, name in q ]
def join_tag_meta( me, tag, *metatags):
for meta in metatags:
joint = Tags.Tag( name=MetaTags.joint_name( meta, tag))
me.save_tag( joint)
me.sql('insert into metatagging(meta_id, tag_id, joint_id) values(?,?,?)', meta.id, tag.id, joint.id)
@exec_after('delete_tag')
def unjoin_tag_meta( me, tag, *metatags):
if not metatags:
me.sql('delete from metatagging where tag_id = ? or joint_id = ?', tag.id, tag.id)
elif len(metatags) == 1:
meta_id = metatags[0].id
me.sql('delete from metatagging where meta_id = ? and tag_id = ?', meta_id, tag.id)
else:
values = [ m.id for m in metatags ] + [ tag.id ]
me.sql('delete from metatagging where meta_id in (%s) and tag_id = ?' % ','.join('?'*len(metatags)), *values)
build_metatags_sql = QAspect.sqlite.make_sql_template(
sql_intersect = '''
select tags.id as tag_id, tags.name as tag_name from tags
join metatagging on metatagging.tag_id = tags.id
join metatags on metatags.id = metatagging.meta_id
where %s'''
, sql_select = 'select distinct tag_id, tag_name from ('
, where4operand = 'metatags.id = %d'
, where4negative = 'tags.id not in (select tag_id from metatagging where meta_id = %d)'
, where4operand_many = 'metatags.id in %s'
, where4negative_many = 'tags.id not in (select tag_id from metatagging where meta_id in %s)'
)
def query_by_metatags( me, tree):
return [ Tags.Tag( id=id_, name=name) for id_,name in me.query( me.build_metatags_sql( tree)) ]
class sqlite:
schema = Schema(
UsesTables(Archive, 'items'),
tables=dict(
tags=Table(
id='integer not null primary key autoincrement',
name='varchar(65) unique not null',
tags_key=Constraint('unique (name)'),
),
tagging=Table(
item_id='integer not null',
tag_id='integer not null',
tagging_key=Constraint('unique (item_id, tag_id)'),
),
),
indexes=dict(tags_index=Index('tagging', 'item_id', 'tag_id')),
)
def save_tag(me, tag):
if not tag.id:
log('create_tag:', tag)
me.sql('insert into tags(name) values(?)', tag.name)
tag.id = me.last_insert_rowid()
else:
me.sql('update tags set name=? where id=?', tag.name, tag.id)
Tags.name2tag[tag.name] = tag
def delete_tag(me, tag):
me.sql('delete from tags where id = ?', tag.id)
me.sql('delete from tagging where tag_id = ?', tag.id)
del Tags.name2tag[tag.name]
def get_all_tags(me):
q = me.query('select id, name from tags')
return [Tags.Tag(id=id_, name=name) for id_, name in q]
# item and tags
def get_item_tags(me, item):
q = me.query(
'''
select tags.id as tag_id, tags.name as tag_name from tags
join tagging on tags.id == tagging.tag_id
where tagging.item_id = ?''', item.id)
return [Tags.Tag(id=id_, name=name) for id_, name in q]
def tag_item(me, item, *tags):
me.sql_many('insert into tagging(tag_id, item_id) values(?,?)',
[(tag.id, item.id) for tag in tags])
@exec_after('delete_item')
def untag_item(me, item, *tags):
if not tags:
me.sql('delete from tagging where item_id = ?', item.id)
elif len(tags) == 1:
tag_id = tags[0].id
me.sql('delete from tagging where tag_id = ? and item_id = ?',
tag_id, item.id)
else:
values = [t.id for t in tags] + [item.id]
me.sql(
'delete from tagging where tag_id in (%s) and item_id = ?'
% ','.join('?' * len(tags)), *values)
build_tags_sql = QAspect.sqlite.make_sql_template(
sql_intersect='''
select items.id as item_id, items.name as item_name from items
join tagging on tagging.item_id = items.id
join tags on tags.id = tagging.tag_id
where %s''',
sql_select='select distinct item_id, item_name from (',
where4operand='tags.id = %d',
where4negative=
'items.id not in (select item_id from tagging where tag_id = %d)',
where4operand_many='tags.id in %s',
where4negative_many=
'items.id not in (select item_id from tagging where tag_id in %s)')
def query_by_tags(me, tags_tree):
return [
Archive.Item(id=id_, name=name)
for id_, name in me.query(me.build_tags_sql(tags_tree))
]