# -*- coding: utf-8 -*-
"""
Module Licence and docstring
TODO: ideally the ibeis.constants module would not be used here
and each function would use its own constant variables that are suffixed
with the last version number that they existed in
TODO:
Add a table for original_image_path
Add column for image exif orientation
CommandLine:
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema
"""
from __future__ import absolute_import, division, print_function
from ibeis import constants as const
from ibeis.control import _sql_helpers
try:
from ibeis.control import DB_SCHEMA_CURRENT
UPDATE_CURRENT = DB_SCHEMA_CURRENT.update_current
VERSION_CURRENT = DB_SCHEMA_CURRENT.VERSION_CURRENT
except:
UPDATE_CURRENT = None
VERSION_CURRENT = None
print("[dbcache] NO DB_SCHEMA_CURRENT AUTO-GENERATED!")
import utool as ut
profile = ut.profile
NAME_TABLE_v121 = const.NAME_TABLE_v121
NAME_TABLE_v130 = const.NAME_TABLE_v130
ANNOT_VISUAL_UUID = 'annot_visual_uuid'
ANNOT_SEMANTIC_UUID = 'annot_semantic_uuid'
ANNOT_UUID = 'annot_uuid'
ANNOT_YAW = 'annot_yaw'
ANNOT_VIEWPOINT = 'annot_viewpoint'
NAME_ROWID = 'name_rowid'
SPECIES_ROWID = 'species_rowid'
IMAGE_ROWID = 'image_rowid'
ANNOT_ROWID = 'annot_rowid'
ANNOT_PARENT_ROWID = 'annot_parent_rowid'
ANNOTGROUP_ROWID = 'annotgroup_rowid'
NAME_TEXT = 'name_text'
SPECIES_TEXT = 'species_text'
ANNOT_ROWID1 = 'annot_rowid1'
ANNOT_ROWID2 = 'annot_rowid2'
PARTY_ROWID = 'party_rowid'
PARTY_TAG = 'party_tag'
CONFIG_ROWID = 'config_rowid'
IMAGE_UUID = 'image_uuid'
CONFIG_SUFFIX = 'config_suffix'
# =======================
# Schema Version 1.0.0
# =======================
@profile
[docs]def update_1_0_0(db, ibs=None):
db.add_table(const.IMAGE_TABLE, (
(IMAGE_ROWID, 'INTEGER PRIMARY KEY'),
(IMAGE_UUID, 'UUID NOT NULL'),
('image_uri', 'TEXT NOT NULL'),
('image_ext', 'TEXT NOT NULL'),
('image_original_name', 'TEXT NOT NULL'), # We could parse this out of original_path
('image_width', 'INTEGER DEFAULT -1'),
('image_height', 'INTEGER DEFAULT -1'),
('image_time_posix', 'INTEGER DEFAULT -1'), # this should probably be UCT
('image_gps_lat', 'REAL DEFAULT -1.0'), # there doesn't seem to exist a GPSPoint in SQLite (TODO: make one in the __SQLITE3__ custom types
('image_gps_lon', 'REAL DEFAULT -1.0'),
('image_toggle_enabled', 'INTEGER DEFAULT 0'),
('image_toggle_reviewed', 'INTEGER DEFAULT 0'),
('image_note', 'TEXT',),
),
superkeys=[(IMAGE_UUID,)],
docstr='''
First class table used to store image locations and meta-data''')
db.add_table('encounters', (
('encounter_rowid', 'INTEGER PRIMARY KEY'),
('encounter_uuid', 'UUID NOT NULL'),
('encounter_text', 'TEXT NOT NULL'),
('encounter_note', 'TEXT NOT NULL'),
),
superkeys=[('encounter_text',)],
docstr='''
List of all encounters''')
db.add_table(const.LBLTYPE_TABLE, (
('lbltype_rowid', 'INTEGER PRIMARY KEY'),
('lbltype_text', 'TEXT NOT NULL'),
('lbltype_default', 'TEXT NOT NULL'),
),
superkeys=[('lbltype_text',)],
docstr='''
List of keys used to define the categories of annotation lables, text
is for human-readability. The lbltype_default specifies the
lblannot_value of annotations with a relationship of some
lbltype_rowid''')
db.add_table(const.CONFIG_TABLE, (
(CONFIG_ROWID, 'INTEGER PRIMARY KEY'),
(CONFIG_SUFFIX, 'TEXT NOT NULL'),
),
superkeys=[(CONFIG_SUFFIX,)],
docstr='''
Used to store the ids of algorithm configurations that generate
annotation lblannots. Each user will have a config id for manual
contributions ''')
##########################
# FIRST ORDER #
##########################
db.add_table(const.ANNOTATION_TABLE, (
(ANNOT_ROWID, 'INTEGER PRIMARY KEY'),
(ANNOT_UUID, 'UUID NOT NULL'),
('image_rowid', 'INTEGER NOT NULL'),
('annot_xtl', 'INTEGER NOT NULL'),
('annot_ytl', 'INTEGER NOT NULL'),
('annot_width', 'INTEGER NOT NULL'),
('annot_height', 'INTEGER NOT NULL'),
('annot_theta', 'REAL DEFAULT 0.0'),
('annot_num_verts', 'INTEGER NOT NULL'),
('annot_verts', 'TEXT'),
('annot_detect_confidence', 'REAL DEFAULT -1.0'),
('annot_exemplar_flag', 'INTEGER DEFAULT 0'),
('annot_note', 'TEXT'),
),
superkeys=[(ANNOT_UUID,)],
docstr='''
Mainly used to store the geometry of the annotation within its parent
image The one-to-many relationship between images and annotations is
encoded here Attributes are stored in the Annotation Label Relationship
Table''')
db.add_table(const.LBLIMAGE_TABLE, (
('lblimage_rowid', 'INTEGER PRIMARY KEY'),
('lblimage_uuid', 'UUID NOT NULL'),
('lbltype_rowid', 'INTEGER NOT NULL'), # this is "category" in the proposal
('lblimage_value', 'TEXT NOT NULL'),
('lblimage_note', 'TEXT'),
),
superkeys=[('lbltype_rowid', 'lblimage_value',)],
docstr='''
Used to store the labels (attributes) of images''')
db.add_table(const.LBLANNOT_TABLE, (
('lblannot_rowid', 'INTEGER PRIMARY KEY'),
('lblannot_uuid', 'UUID NOT NULL'),
('lbltype_rowid', 'INTEGER NOT NULL'), # this is "category" in the proposal
('lblannot_value', 'TEXT NOT NULL'),
('lblannot_note', 'TEXT'),
),
superkeys=[('lbltype_rowid', 'lblannot_value',)],
docstr='''
Used to store the labels / attributes of annotations.
E.G name, species ''')
##########################
# SECOND ORDER #
##########################
# TODO: constraint needs modification
db.add_table(const.CHIP_TABLE, (
('chip_rowid', 'INTEGER PRIMARY KEY'),
('annot_rowid', 'INTEGER NOT NULL'),
('config_rowid', 'INTEGER DEFAULT 0'),
('chip_uri', 'TEXT'),
('chip_width', 'INTEGER NOT NULL'),
('chip_height', 'INTEGER NOT NULL'),
),
superkeys=[('annot_rowid', 'config_rowid',)],
docstr='''
Used to store *processed* annots as chips''')
db.add_table(const.FEATURE_TABLE, (
('feature_rowid', 'INTEGER PRIMARY KEY'),
('chip_rowid', 'INTEGER NOT NULL'),
('config_rowid', 'INTEGER DEFAULT 0'),
('feature_num_feats', 'INTEGER NOT NULL'),
('feature_keypoints', 'NUMPY'),
('feature_sifts', 'NUMPY'),
),
superkeys=[('chip_rowid, config_rowid',)],
docstr='''
Used to store individual chip features (ellipses)''')
db.add_table('encounter_image_relationship', (
('egr_rowid', 'INTEGER PRIMARY KEY'),
('image_rowid', 'INTEGER NOT NULL'),
('encounter_rowid', 'INTEGER'),
),
superkeys=[(IMAGE_ROWID, 'encounter_rowid',)],
docstr='''
Relationship between encounters and images (many to many mapping) the
many-to-many relationship between images and encounters is encoded here
encounter_image_relationship stands for encounter-image-pairs.''')
##########################
# THIRD ORDER #
##########################
db.add_table(const.GL_RELATION_TABLE, (
('glr_rowid', 'INTEGER PRIMARY KEY'),
('image_rowid', 'INTEGER NOT NULL'),
('lblimage_rowid', 'INTEGER NOT NULL'),
('config_rowid', 'INTEGER DEFAULT 0'),
('glr_confidence', 'REAL DEFAULT 0.0'),
),
superkeys=[('image_rowid', 'lblimage_rowid', 'config_rowid',)],
docstr='''
Used to store one-to-many the relationship between images
and labels''')
db.add_table(const.AL_RELATION_TABLE, (
('alr_rowid', 'INTEGER PRIMARY KEY'),
('annot_rowid', 'INTEGER NOT NULL'),
('lblannot_rowid', 'INTEGER NOT NULL'),
('config_rowid', 'INTEGER DEFAULT 0'),
('alr_confidence', 'REAL DEFAULT 0.0'),
),
superkeys=[('annot_rowid', 'lblannot_rowid', 'config_rowid',)],
docstr='''
Used to store one-to-many the relationship between annotations (annots)
and labels''')
[docs]def post_1_0_0(db, ibs=None):
# We are dropping the versions table and rather using the metadata table
print('applying post_1_0_0')
db.drop_table(const.VERSIONS_TABLE)
[docs]def post_1_2_0(db, ibs=None):
print('applying post_1_2_0')
def schema_1_2_0_postprocess_fixuuids(ibs):
"""
schema_1_2_0_postprocess_fixuuids
Args:
ibs (IBEISController):
Example:
>>> # DISABLE_DOCTEST
>>> from ibeis.algo.preproc.preproc_annot import * # NOQA
>>> import ibeis
>>> #import sys
#>>> sys.argv.append('--force-fresh')
#>>> ibs = ibeis.opendb('PZ_MTEST')
#>>> ibs = ibeis.opendb('testdb1')
>>> ibs = ibeis.opendb('GZ_ALL')
>>> # should be auto applied
>>> ibs.print_annotation_table(verbosity=1)
>>> result = schema_1_2_0_postprocess_fixuuids(ibs)
>>> ibs.print_annotation_table(verbosity=1)
"""
import utool as ut
aid_list = ibs.get_valid_aids()
#ibs.get_annot_name_rowids(aid_list)
#
#ANNOT_ROWID = 'annot_rowid'
ANNOTATION_TABLE = 'annotations'
ANNOT_SEMANTIC_UUID = 'annot_semantic_uuid'
NAME_ROWID = 'name_rowid'
SPECIES_ROWID = 'species_rowid'
AL_RELATION_TABLE = 'annotation_lblannot_relationship'
def set_annot_semantic_uuids(ibs, aid_list, annot_semantic_uuid_list):
id_iter = aid_list
colnames = (ANNOT_SEMANTIC_UUID,)
ibs.db.set(ANNOTATION_TABLE, colnames,
annot_semantic_uuid_list, id_iter)
def set_annot_visual_uuids(ibs, aid_list, annot_visual_uuid_list):
id_iter = aid_list
colnames = (ANNOT_VISUAL_UUID,)
ibs.db.set(ANNOTATION_TABLE, colnames,
annot_visual_uuid_list, id_iter)
def set_annot_species_rowids(ibs, aid_list, species_rowid_list):
id_iter = aid_list
colnames = (SPECIES_ROWID,)
ibs.db.set(
ANNOTATION_TABLE, colnames, species_rowid_list, id_iter)
def set_annot_name_rowids(ibs, aid_list, name_rowid_list):
id_iter = aid_list
colnames = (NAME_ROWID,)
ibs.db.set(ANNOTATION_TABLE, colnames, name_rowid_list, id_iter)
def get_alr_lblannot_rowids(alrid_list):
lblannot_rowids_list = ibs.db.get(AL_RELATION_TABLE, ('lblannot_rowid',), alrid_list)
return lblannot_rowids_list
def get_annot_alrids_oftype(aid_list, lbltype_rowid):
"""
Get all the relationship ids belonging to the input annotations where the
relationship ids are filtered to be only of a specific lbltype/category/type
"""
alrids_list = ibs.db.get(AL_RELATION_TABLE, ('alr_rowid',), aid_list, id_colname='annot_rowid', unpack_scalars=False)
# Get lblannot_rowid of each relationship
lblannot_rowids_list = ibs.unflat_map(get_alr_lblannot_rowids, alrids_list)
# Get the type of each lblannot
lbltype_rowids_list = ibs.unflat_map(ibs.get_lblannot_lbltypes_rowids, lblannot_rowids_list)
# only want the nids of individuals, not species, for example
valids_list = [[typeid == lbltype_rowid for typeid in rowids] for rowids in lbltype_rowids_list]
alrids_list = [ut.compress(alrids, valids) for alrids, valids in zip(alrids_list, valids_list)]
alrids_list = [
alrid_list[0:1]
if len(alrid_list) > 1 else
alrid_list
for alrid_list in alrids_list
]
assert all([len(alrid_list) < 2 for alrid_list in alrids_list]),\
("More than one type per lbltype. ALRIDS: " + str(alrids_list) +
", ROW: " + str(lbltype_rowid) + ", KEYS:" + str(ibs.lbltype_ids))
return alrids_list
def get_annot_speciesid_from_lblannot_relation(aid_list, distinguish_unknowns=True):
""" function for getting speciesid the old way """
species_lbltype_rowid = ibs.db.get('keys', ('lbltype_rowid',), ('SPECIES_KEY',), id_colname='lbltype_text')[0]
alrids_list = get_annot_alrids_oftype(aid_list, species_lbltype_rowid)
lblannot_rowids_list = ibs.unflat_map(get_alr_lblannot_rowids, alrids_list)
speciesid_list = [lblannot_rowids[0] if len(lblannot_rowids) > 0 else const.UNKNOWN_LBLANNOT_ROWID for
lblannot_rowids in lblannot_rowids_list]
return speciesid_list
def get_annot_name_rowids_from_lblannot_relation(aid_list):
""" function for getting nids the old way """
individual_lbltype_rowid = ibs.db.get('keys', ('lbltype_rowid',), ('INDIVIDUAL_KEY',), id_colname='lbltype_text')[0]
alrids_list = get_annot_alrids_oftype(aid_list, individual_lbltype_rowid)
lblannot_rowids_list = ibs.unflat_map(get_alr_lblannot_rowids, alrids_list)
# Get a single nid from the list of lblannot_rowids of type INDIVIDUAL
# TODO: get index of highest confidencename
nid_list = [lblannot_rowids[0] if len(lblannot_rowids) > 0 else const.UNKNOWN_LBLANNOT_ROWID for
lblannot_rowids in lblannot_rowids_list]
return nid_list
nid_list1 = ibs.get_annot_name_rowids(aid_list, distinguish_unknowns=False)
speciesid_list1 = ibs.get_annot_species_rowids(aid_list)
# Get old values from lblannot table
nid_list = get_annot_name_rowids_from_lblannot_relation(aid_list)
speciesid_list = get_annot_speciesid_from_lblannot_relation(aid_list)
assert len(nid_list1) == len(nid_list), 'cannot update to 1_2_0 name length error'
assert len(speciesid_list1) == len(speciesid_list), 'cannot update to 1_2_0 species length error'
if (ut.list_all_eq_to(nid_list, 0) and ut.list_all_eq_to(speciesid_list, 0)):
print('... returning No information in lblannot table to transfer')
return
# Make sure information has not gotten out of sync
try:
assert all([(nid1 == nid or nid1 == 0) for nid1, nid in zip(nid_list1, nid_list)])
assert all([(sid1 == sid or sid1 == 0) for sid1, sid in zip(speciesid_list1, speciesid_list)])
except AssertionError as ex:
ut.printex(ex, 'Cannot update database to 1_2_0 information out of sync')
raise
# Move values into the annotation table as a native column
set_annot_name_rowids(ibs, aid_list, nid_list)
set_annot_species_rowids(ibs, aid_list, speciesid_list)
# Update visual uuids
# Moved this to post_process 1.21
#ibs.update_annot_visual_uuids(aid_list)
#ibs.update_annot_semantic_uuids(aid_list)
#ibs.print_annotation_table(verbosity=1)
if ibs is not None:
ibs._init_rowid_constants()
schema_1_2_0_postprocess_fixuuids(ibs)
else:
print('warning: ibs is None, so cannot apply name/species column fixes to existing database')
[docs]def post_1_2_1(db, ibs=None):
if ibs is not None:
print('applying post_1_2_1')
import utool as ut
from ibeis.algo.preproc import preproc_annot
if ibs is not None:
ibs._init_rowid_constants()
#db = ibs.db
UNKNOWN_ROWID = 0
UNKNOWN = '____'
UNKNOWN_NAME_ROWID = 0
ANNOTATION_TABLE = 'annotations'
SPECIES_TABLE = 'species'
LBLANNOT_TABLE = 'lblannot'
SPECIES_ROWID = 'species_rowid'
NAME_ROWID = 'name_rowid'
NAME_TEXT = 'name_text'
ANNOT_SEMANTIC_UUID = 'annot_semantic_uuid'
lblannot_colnames = ('lblannot_uuid', 'lblannot_value', 'lblannot_note',)
name_colnames = ('name_uuid', 'name_text', 'name_note',)
species_colspeciess = ('species_uuid', 'species_text', 'species_note',)
# Get old name and species rowids from annotaiton tables
aid_list = db.get_all_rowids(ANNOTATION_TABLE)
name_rowids1 = db.get(ANNOTATION_TABLE, (NAME_ROWID,), aid_list)
species_rowids1 = db.get(ANNOTATION_TABLE, (SPECIES_ROWID,), aid_list)
# Look at the unique non-unknown ones
unique_name_rowids1 = sorted(list( set(name_rowids1) - set([UNKNOWN_ROWID])))
unique_species_rowids1 = sorted(list(set(species_rowids1) - set([UNKNOWN_ROWID])))
# Get params out of label annotation tables
name_params_list = db.get(LBLANNOT_TABLE, lblannot_colnames, unique_name_rowids1)
species_params_list = db.get(LBLANNOT_TABLE, lblannot_colnames, unique_species_rowids1)
# Move params into name and species tables
unique_name_rowids2 = db._add(NAME_TABLE_v121, name_colnames, name_params_list)
unique_species_rowids2 = db._add(SPECIES_TABLE, species_colspeciess, species_params_list)
# Build mapping from old table to new table
name_rowid_mapping = dict(zip(unique_name_rowids1, unique_name_rowids2))
speices_rowid_mapping = dict(zip(unique_species_rowids1, unique_species_rowids2))
name_rowid_mapping[UNKNOWN_ROWID] = UNKNOWN_ROWID
speices_rowid_mapping[UNKNOWN_ROWID] = UNKNOWN_ROWID
# Apply mapping
name_rowids2 = ut.dict_take_list(name_rowid_mapping, name_rowids1)
species_rowid2 = ut.dict_take_list(speices_rowid_mapping, species_rowids1)
# Put new rowids back into annotation table
db.set(ANNOTATION_TABLE, (NAME_ROWID,), name_rowids2, aid_list)
db.set(ANNOTATION_TABLE, (SPECIES_ROWID,), species_rowid2, aid_list)
# HACK TODO use actual SQL to fix and move to 1.2.0
def get_annot_names_v121(aid_list):
name_rowid_list = ibs.get_annot_name_rowids(aid_list)
name_text_list = ibs.db.get(NAME_TABLE_v121, (NAME_TEXT,), name_rowid_list)
name_text_list = [
UNKNOWN
if rowid == UNKNOWN_NAME_ROWID or name_text is None
else name_text
for name_text, rowid in zip(name_text_list, name_rowid_list)]
return name_text_list
def get_annot_semantic_uuid_info_v121(aid_list, _visual_infotup):
visual_infotup = _visual_infotup
image_uuid_list, verts_list, theta_list = visual_infotup
# It is visual info augmented with name and species
def get_annot_viewpoints(ibs, aid_list):
viewpoint_list = ibs.db.get(const.ANNOTATION_TABLE, (ANNOT_VIEWPOINT,), aid_list)
viewpoint_list = [viewpoint if viewpoint >= 0.0 else None for viewpoint in viewpoint_list]
return viewpoint_list
view_list = get_annot_viewpoints(ibs, aid_list)
name_list = get_annot_names_v121(aid_list)
species_list = ibs.get_annot_species_texts(aid_list)
semantic_infotup = (image_uuid_list, verts_list, theta_list, view_list,
name_list, species_list)
return semantic_infotup
def get_annot_visual_uuid_info_v121(aid_list):
image_uuid_list = ibs.get_annot_image_uuids(aid_list)
verts_list = ibs.get_annot_verts(aid_list)
theta_list = ibs.get_annot_thetas(aid_list)
visual_infotup = (image_uuid_list, verts_list, theta_list)
return visual_infotup
def update_annot_semantic_uuids_v121(aid_list, _visual_infotup=None):
semantic_infotup = get_annot_semantic_uuid_info_v121(aid_list, _visual_infotup)
annot_semantic_uuid_list = preproc_annot.make_annot_semantic_uuid(semantic_infotup)
ibs.db.set(ANNOTATION_TABLE, (ANNOT_SEMANTIC_UUID,), annot_semantic_uuid_list, aid_list)
def update_annot_visual_uuids_v121(aid_list):
visual_infotup = get_annot_visual_uuid_info_v121(aid_list)
annot_visual_uuid_list = preproc_annot.make_annot_visual_uuid(visual_infotup)
ibs.db.set(ANNOTATION_TABLE, (ANNOT_VISUAL_UUID,), annot_visual_uuid_list, aid_list)
# If visual uuids are changes semantic ones are also changed
update_annot_semantic_uuids_v121(aid_list, visual_infotup)
update_annot_visual_uuids_v121(aid_list)
[docs]def post_1_3_4(db, ibs=None):
if ibs is not None:
ibs._init_rowid_constants()
ibs._init_config()
ibs.update_annot_visual_uuids(ibs.get_valid_aids())
[docs]def pre_1_3_1(db, ibs=None):
"""
need to ensure that visual uuid columns are unique before we add that
constaint to sql. This will remove any annotations that are not unique
"""
if ibs is not None:
#from ibeis.other import ibsfuncs
import utool as ut
import six
ibs._init_rowid_constants()
ibs._init_config()
aid_list = ibs.get_valid_aids()
def pre_1_3_1_update_visual_uuids(ibs, aid_list):
from ibeis.algo.preproc import preproc_annot
def pre_1_3_1_get_annot_visual_uuid_info(ibs, aid_list):
image_uuid_list = ibs.get_annot_image_uuids(aid_list)
verts_list = ibs.get_annot_verts(aid_list)
theta_list = ibs.get_annot_thetas(aid_list)
visual_infotup = (image_uuid_list, verts_list, theta_list)
return visual_infotup
def pre_1_3_1_get_annot_semantic_uuid_info(ibs, aid_list, _visual_infotup):
image_uuid_list, verts_list, theta_list = visual_infotup
def get_annot_viewpoints(ibs, aid_list):
viewpoint_list = ibs.db.get(const.ANNOTATION_TABLE, (ANNOT_VIEWPOINT,), aid_list)
viewpoint_list = [viewpoint if viewpoint >= 0.0 else None for viewpoint in viewpoint_list]
return viewpoint_list
# It is visual info augmented with name and species
viewpoint_list = get_annot_viewpoints(ibs, aid_list)
name_list = ibs.get_annot_names(aid_list)
species_list = ibs.get_annot_species_texts(aid_list)
semantic_infotup = (image_uuid_list, verts_list, theta_list, viewpoint_list,
name_list, species_list)
return semantic_infotup
visual_infotup = pre_1_3_1_get_annot_visual_uuid_info(ibs, aid_list)
annot_visual_uuid_list = preproc_annot.make_annot_visual_uuid(visual_infotup)
ibs.db.set(const.ANNOTATION_TABLE, (ANNOT_VISUAL_UUID,), annot_visual_uuid_list, aid_list)
# If visual uuids are changes semantic ones are also changed
# update semeantic pre 1_3_1
_visual_infotup = visual_infotup
semantic_infotup = pre_1_3_1_get_annot_semantic_uuid_info(ibs, aid_list, _visual_infotup)
annot_semantic_uuid_list = preproc_annot.make_annot_semantic_uuid(semantic_infotup)
ibs.db.set(const.ANNOTATION_TABLE, (ANNOT_SEMANTIC_UUID,), annot_semantic_uuid_list, aid_list)
pass
pre_1_3_1_update_visual_uuids(ibs, aid_list)
#ibsfuncs.fix_remove_visual_dupliate_annotations(ibs)
aid_list = ibs.get_valid_aids()
visual_uuid_list = ibs.get_annot_visual_uuids(aid_list)
ibs_dup_annots = ut.debug_duplicate_items(visual_uuid_list)
dupaids_list = []
if len(ibs_dup_annots):
for key, dupxs in six.iteritems(ibs_dup_annots):
aids = ut.take(aid_list, dupxs)
dupaids_list.append(aids[1:])
toremove_aids = ut.flatten(dupaids_list)
print('About to delete toremove_aids=%r' % (toremove_aids,))
#if ut.are_you_sure():
#ibs.delete_annots(toremove_aids)
#from ibeis.algo.preproc import preproc_annot
#preproc_annot.on_delete(ibs, toremove_aids)
ibs.db.delete_rowids(const.ANNOTATION_TABLE, toremove_aids)
aid_list = ibs.get_valid_aids()
visual_uuid_list = ibs.get_annot_visual_uuids(aid_list)
ibs_dup_annots = ut.debug_duplicate_items(visual_uuid_list)
assert len(ibs_dup_annots) == 0
# =======================
# Schema Version 1.0.1
# =======================
@profile
[docs]def update_1_0_1(db, ibs=None):
# Add a contributor's table
db.add_table(const.CONTRIBUTOR_TABLE, (
('contributor_rowid', 'INTEGER PRIMARY KEY'),
('contributor_tag', 'TEXT'),
('contributor_name_first', 'TEXT'),
('contributor_name_last', 'TEXT'),
('contributor_location_city', 'TEXT'),
('contributor_location_state', 'TEXT'),
('contributor_location_country', 'TEXT'),
('contributor_location_zip', 'INTEGER'),
('contributor_note', 'INTEGER'),
),
superkeys=[('contributor_rowid',)],
docstr='''
Used to store the contributors to the project
''')
db.modify_table(const.IMAGE_TABLE, (
# add column to v1.0.0 at index 1
(1, 'contributor_rowid', 'INTEGER', None),
))
db.modify_table(const.ANNOTATION_TABLE, (
# add column to v1.0.0 at index 1
(1, 'annot_parent_rowid', 'INTEGER', None),
))
db.modify_table(const.FEATURE_TABLE, (
# append column to v1.0.0 because None
(None, 'feature_weight', 'REAL DEFAULT 1.0', None),
))
# =======================
# Schema Version 1.0.2
# =======================
@profile
[docs]def update_1_0_2(db, ibs=None):
# Fix the contibutor table's constraint
db.modify_table(const.CONTRIBUTOR_TABLE, (
# add column to v1.0.1 at index 1
(1, 'contributor_uuid', 'UUID NOT NULL', None),
),
superkeys=[('contributor_tag',)]
)
# =======================
# Schema Version 1.1.0
# =======================
@profile
[docs]def update_1_1_0(db, ibs=None):
# Moving chips and features to their own cache database
db.drop_table(const.CHIP_TABLE)
db.drop_table(const.FEATURE_TABLE)
# Add viewpoint (radians) to annotations
db.modify_table(const.ANNOTATION_TABLE, (
# add column to v1.0.2 at index 11
#(11, ANNOT_VIEWPOINT, 'REAL DEFAULT 0.0', None),
(11, ANNOT_VIEWPOINT, 'REAL', None),
))
# Add contributor to configs
db.modify_table(const.CONFIG_TABLE, (
# add column to v1.0.2 at index 1
(1, 'contributor_uuid', 'UUID', None),
),
# FIXME: This change may have broken things
superkeys=[('contributor_uuid', CONFIG_SUFFIX,)]
)
# Add config to encounters
db.modify_table('encounters', (
# add column to v1.0.2 at index 2
(2, 'config_rowid', 'INTEGER', None),
),
superkeys=[('encounter_uuid', 'encounter_text',)]
)
# Error in the drop table script, re-drop again from post_1_0_0 to kill table's metadata
db.drop_table(const.VERSIONS_TABLE)
# =======================
# Schema Version 1.1.1
# =======================
@profile
[docs]def update_1_1_1(db, ibs=None):
# Change name of column
db.modify_table(const.CONFIG_TABLE, (
# rename column and change it's type
('contributor_uuid', 'contributor_rowid', 'INTEGER', None),
),
superkeys=[('contributor_rowid', CONFIG_SUFFIX,)]
)
# Change type of column
#db.modify_table(const.CONFIG_TABLE, (
# # rename column and change it's type
# ('contributor_rowid', '', 'INTEGER', None),
#))
# Change type of columns
db.modify_table(const.CONTRIBUTOR_TABLE, (
# Update column's types
('contributor_location_zip', '', 'TEXT', None),
('contributor_note', '', 'TEXT', None),
))
@profile
[docs]def update_1_2_0(db, ibs=None):
# Add columns to annotaiton table
tablename = const.ANNOTATION_TABLE
colmap_list = (
# the visual uuid will be unique w.r.t. the appearence of the annotation
(None, ANNOT_VISUAL_UUID, 'UUID', None),
# the visual uuid will be unique w.r.t. the appearence, name, and species of the annotation
(None, 'annot_semantic_uuid', 'UUID', None),
(None, 'name_rowid', 'INTEGER DEFAULT 0', None),
(None, 'species_rowid', 'INTEGER DEFAULT 0', None),
)
aid_before = db.get_all_rowids(const.ANNOTATION_TABLE)
#import utool as ut
db.modify_table(tablename, colmap_list)
# Sanity check
aid_after = db.get_all_rowids(const.ANNOTATION_TABLE)
assert aid_before == aid_after
@profile
[docs]def update_1_2_1(db, ibs=None):
# Names and species are taken away from lblannot table and upgraded
# to their own thing
db.add_table(NAME_TABLE_v121, (
('name_rowid', 'INTEGER PRIMARY KEY'),
('name_uuid', 'UUID NOT NULL'),
(NAME_TEXT, 'TEXT NOT NULL'),
('name_note', 'TEXT'),
),
superkeys=[(NAME_TEXT,)],
docstr='''
Stores the individual animal names
''')
db.add_table(const.SPECIES_TABLE, (
('species_rowid', 'INTEGER PRIMARY KEY'),
('species_uuid', 'UUID NOT NULL'),
(SPECIES_TEXT, 'TEXT NOT NULL'),
('species_note', 'TEXT'),
),
superkeys=[(SPECIES_TEXT,)],
docstr='''
Stores the different animal species
''')
[docs]def update_1_3_0(db, ibs=None):
db.modify_table(const.IMAGE_TABLE, (
(None, 'image_timedelta_posix', 'INTEGER DEFAULT 0', None),
))
db.modify_table(NAME_TABLE_v121, (
(None, 'name_temp_flag', 'INTEGER DEFAULT 0', None),
(None, 'name_alias_text', 'TEXT', None),
), tablename_new=NAME_TABLE_v130)
db.drop_table(NAME_TABLE_v121)
db.modify_table('encounters', (
(None, 'encounter_start_time_posix', 'INTEGER', None),
(None, 'encounter_end_time_posix', 'INTEGER', None),
(None, 'encounter_gps_lat', 'INTEGER', None),
(None, 'encounter_gps_lon', 'INTEGER', None),
(None, 'encounter_processed_flag', 'INTEGER DEFAULT 0', None),
(None, 'encounter_shipped_flag', 'INTEGER DEFAULT 0', None),
))
"""
* New Image Columns
- image_posix_timedelta
* New Name Columns
- name_temp_flag
- name_alias_text
- name_uuid
- name_visual_uuid
- name_member_annot_rowids_evalstr
- name_member_num_annot_rowids
* New Encounter Columns
- encounter_start_posix_time
- encounter_end_time_posix
- encounter_gps_lat
- encounter_gps_lon
- encounter_processed_flag
- encounter_shipped_flag
"""
# TODO: changed shipped to commited
# encounter_detected_flag
# encounter_identified_flag???
pass
# Need encounter processed and shipped flag
#db.modify_table(const.CONFIG_TABLE, (
# # rename column and change it's type
#)
[docs]def update_1_3_1(db, ibs=None):
"""
update the visual_uuid to be a superkey by adding a constraint
"""
# make annot_visual_uuid not null and add it as a superkey
db.modify_table(
const.ANNOTATION_TABLE,
[
# change type of annot_visual_uuid
(ANNOT_VISUAL_UUID, '', 'UUID NOT NULL', None),
],
# ERROR: this should have been ANNOT_SEMANTIC_UUID
superkeys=[(ANNOT_UUID,), (ANNOT_VISUAL_UUID,)])
#pass
[docs]def update_1_3_2(db, ibs=None):
"""
for SMART DATA
"""
db.modify_table('encounters', (
(None, 'encounter_smart_xml_fpath', 'TEXT', None),
(None, 'encounter_smart_waypoint_id', 'INTEGER', None),
))
[docs]def update_1_3_3(db, ibs=None):
# we should only be storing names here not paths
db.modify_table('encounters', (
('encounter_smart_xml_fpath', 'encounter_smart_xml_fname', 'TEXT', None),
))
[docs]def update_1_3_4(db, ibs=None):
# OLD ANNOT VIEWPOINT FUNCS. Hopefully these are not needed
#def get_annot_viewpoints(ibs, aid_list):
# viewpoint_list = ibs.db.get(const.ANNOTATION_TABLE, (ANNOT_VIEWPOINT,), aid_list)
# viewpoint_list = [viewpoint if viewpoint >= 0.0 else None for viewpoint in viewpoint_list]
# return viewpoint_list
#def set_annot_viewpoint(ibs, aid_list, viewpoint_list, input_is_degrees=False):
# id_iter = ((aid,) for aid in aid_list)
# #viewpoint_list = [-1 if viewpoint is None else viewpoint for viewpoint in viewpoint_list]
# if input_is_degrees:
# viewpoint_list = [-1 if viewpoint is None else ut.deg_to_rad(viewpoint)
# for viewpoint in viewpoint_list]
# #assert all([0.0 <= viewpoint < 2 * np.pi or viewpoint == -1.0 for viewpoint in viewpoint_list])
# val_iter = ((viewpoint, ) for viewpoint in viewpoint_list)
# ibs.db.set(const.ANNOTATION_TABLE, (ANNOT_VIEWPOINT,), val_iter, id_iter)
# ibs.update_annot_visual_uuids(aid_list)
print('executing update_1_3_4')
TAU = const.TAU
def convert_old_viewpoint_to_yaw(angle):
""" we initially had viewpoint coordinates inverted
Example:
>>> import math
>>> TAU = 2 * math.pi
>>> old_viewpoint_labels = [
>>> ('left' , 0.000 * TAU,),
>>> ('frontleft' , 0.125 * TAU,),
>>> ('front' , 0.250 * TAU,),
>>> ('frontright' , 0.375 * TAU,),
>>> ('right' , 0.500 * TAU,),
>>> ('backright' , 0.625 * TAU,),
>>> ('back' , 0.750 * TAU,),
>>> ('backleft' , 0.875 * TAU,),
>>> ]
>>> for lbl, angle in old_viewpoint_labels:
>>> yaw = convert_old_viewpoint_to_yaw(angle)
>>> angle2 = convert_old_viewpoint_to_yaw(yaw)
>>> print('old %15r %.2f -> new %15r %.2f' % (lbl, angle, lbl, yaw))
>>> print('old %15r %.2f -> new %15r %.2f' % (lbl, yaw, lbl, angle2))
"""
if angle is None:
return None
yaw = (-angle + (TAU / 2)) % TAU
return yaw
from dtool.sql_control import SQLDatabaseController
assert isinstance(db, SQLDatabaseController)
db.modify_table(const.IMAGE_TABLE, (
# Add original image path to image table for more data persistance and
# stewardship
(None, 'image_original_path', 'TEXT', None),
# Add image location as a simple workaround for not using the gps
(None, 'image_location_code', 'TEXT', None),
))
db.modify_table(const.ANNOTATION_TABLE, (
# Add image quality as an integer to filter the database more easilly
(None, 'annot_quality', 'INTEGER', None),
# Add a path to a file that will represent if a pixel belongs to the
# object of interest within the annotation.
#(None, 'annot_mask_fpath', 'STRING', None),
(ANNOT_VIEWPOINT, ANNOT_YAW, 'REAL', convert_old_viewpoint_to_yaw),
))
[docs]def update_1_3_5(db, ibs=None):
""" expand datasets to use new quality measures """
if ibs is not None:
# Adds a few different degrees of quality
aid_list = ibs.get_valid_aids()
qual_list = ibs.get_annot_qualities(aid_list)
assert len(qual_list) == 0 or max(qual_list) < 3, 'there were no qualities higher than 3 at this point'
old_to_new = {
2: 3,
1: 2,
}
new_qual_list = [old_to_new.get(qual, qual) for qual in qual_list]
ibs.set_annot_qualities(aid_list, new_qual_list)
[docs]def update_1_3_6(db, ibs=None):
# Add table for explicit annotation-vs-annotation match information
db.add_table(const.PARTY_TABLE, (
(PARTY_ROWID, 'INTEGER PRIMARY KEY'),
(PARTY_TAG, 'TEXT NOT NULL'),
),
superkeys=[(PARTY_TAG,)],
docstr='''
Serves as a group for contributors
''',
)
# instead of adding a specific many to many mapping relate images to both parties
# and contributors
db.modify_table(const.IMAGE_TABLE, [
(None, 'party_rowid', 'INTEGER', None),
],
shortname='image',
extern_tables=[const.PARTY_TABLE, const.CONTRIBUTOR_TABLE],
# TODO: add in many to 1 attribute mapping
)
#db.add_table(const.PARTY_CONTRIB_RELATION_TABLE, (
# ('party_contrib_relation_rowid', 'INTEGER PRIMARY KEY'),
# ('party_rowid', 'INTEGER NOT NULL'),
# ('contributor_rowid', 'INTEGER NOT NULL'),
#),
# superkeys=[('party_rowid', 'contributor_rowid')],
# relates=(const.PARTY_TABLE, const.CONTRIBUTOR_TABLE),
# docstr='''
# Relates parties and contributors
# ''',
#)
db.add_table(const.ANNOTMATCH_TABLE, (
('annotmatch_rowid', 'INTEGER PRIMARY KEY'),
(ANNOT_ROWID1, 'INTEGER NOT NULL'),
(ANNOT_ROWID2, 'INTEGER NOT NULL'),
('annotmatch_truth', 'INTEGER DEFAULT 2'),
('annotmatch_confidence', 'REAL DEFAULT 0'),
),
superkeys=[('annot_rowid1', 'annot_rowid2',)],
relates=(const.ANNOTATION_TABLE, const.ANNOTATION_TABLE),
#shortname='annotmatch',
docstr='''
Sparsely stores explicit matching / not matching information. This
serves as marking weather or not an annotation pair has been reviewed.
''',
)
# add metadata props
db.modify_table(
'encounter_image_relationship',
shortname='egr',
relates=(const.IMAGE_TABLE, 'encounters'))
db.modify_table(
const.AL_RELATION_TABLE,
shortname='alr',
relates=(const.ANNOTATION_TABLE, const.LBLANNOT_TABLE))
db.modify_table(
const.GL_RELATION_TABLE,
shortname='glr',
relates=(const.IMAGE_TABLE, const.LBLIMAGE_TABLE))
db.modify_table(
const.ANNOTATION_TABLE,
shortname='annot',
)
[docs]def update_1_3_7(db, ibs=None):
# Part of the dependsmap property might be infered, but at least the keys and tables are needed.
db.modify_table(
const.ANNOTATION_TABLE,
extern_tables=[const.NAME_TABLE, const.SPECIES_TABLE, const.IMAGE_TABLE],
superkeys=[(ANNOT_UUID,), (ANNOT_VISUAL_UUID,)],
dependsmap={
IMAGE_ROWID : (const.IMAGE_TABLE, (IMAGE_ROWID,), (IMAGE_UUID,)),
NAME_ROWID : (const.NAME_TABLE, (NAME_ROWID,), (NAME_TEXT,)),
SPECIES_ROWID : (const.SPECIES_TABLE, (SPECIES_ROWID,), (SPECIES_TEXT,)),
ANNOT_PARENT_ROWID : (const.ANNOTATION_TABLE, (ANNOT_ROWID,), (ANNOT_VISUAL_UUID,)),
}
)
db.modify_table(
const.ANNOTMATCH_TABLE,
dependsmap={
ANNOT_ROWID1: (const.ANNOTATION_TABLE, (ANNOT_ROWID,), (ANNOT_VISUAL_UUID,)),
ANNOT_ROWID2: (const.ANNOTATION_TABLE, (ANNOT_ROWID,), (ANNOT_VISUAL_UUID,)),
}
)
db.modify_table(
const.IMAGE_TABLE,
dependsmap={
'party_rowid': (const.PARTY_TABLE, ('party_rowid',), (PARTY_TAG,)),
'contributor_rowid': (const.CONTRIBUTOR_TABLE, ('contributor_rowid',), ('contributor_tag',)),
}
)
db.modify_table(
const.CONFIG_TABLE,
dependsmap={
'contributor_rowid': (const.CONTRIBUTOR_TABLE, ('contributor_rowid',), ('contributor_tag',)),
}
)
db.modify_table(
'encounters',
dependsmap={
'config_rowid': (const.CONFIG_TABLE, ('config_rowid',), ('contributor_rowid', CONFIG_SUFFIX,)),
}
)
db.modify_table(
'encounter_image_relationship',
dependsmap={
'image_rowid': (const.IMAGE_TABLE, (IMAGE_ROWID,), (IMAGE_UUID,)),
'encounter_rowid': ('encounters', ('encounter_rowid',), ('encounter_text',)),
}
)
[docs]def update_1_3_8(db, ibs=None):
# Encounters only care about their text again as a uuid We are removing
# config_rowid from encounters. Thus the dependency is not encoded
db.modify_table(
'encounters',
superkeys=[('encounter_text',)],
)
[docs]def update_1_3_9(db, ibs=None):
# Remove contributors from configs
db.modify_table(
const.CONFIG_TABLE,
superkeys=[(CONFIG_SUFFIX,)]
)
# Add primary superkey to annotations table
db.modify_table(
const.ANNOTATION_TABLE,
primary_superkey=('annot_visual_uuid',),
docstr='''
Mainly used to store the geometry of the annotation within its parent
image The one-to-many relationship between images and annotations is
encoded here
'''
)
[docs]def update_1_4_0(db, ibs=None):
# Remove contributors from configs
db.modify_table(
'encounter_image_relationship',
superkeys=[(IMAGE_ROWID, 'encounter_rowid',)],
#superkeys=[(CONFIG_SUFFIX,)]
)
[docs]def update_1_4_1(db, ibs=None):
db.modify_table(
'encounters',
dependsmap={
'config_rowid': (const.CONFIG_TABLE, ('config_rowid',), (CONFIG_SUFFIX,)),
}
)
[docs]def update_1_4_2(db, ibs=None):
db.modify_table(
const.ANNOTATION_TABLE, [
(None, 'contributor_rowid', 'INTEGER', None),
(None, 'annot_age_months_est_min', 'INTEGER DEFAULT -1', None),
(None, 'annot_age_months_est_max', 'INTEGER DEFAULT -1', None),
],
# HACK: Need a way to update the dependsmap without blowing the old one away
# Also need to not overspecify information. colname to tablename should be fine.
# we can have the extern colname be optional. superkey is definiately not needed
#dependsmap=db.get_metadata_val(const.ANNOTATION_TABLE + '_dependsmap', eval_=True) +
# {}
dependsmap={
IMAGE_ROWID : (const.IMAGE_TABLE, (IMAGE_ROWID,), (IMAGE_UUID,)),
NAME_ROWID : (const.NAME_TABLE, (NAME_ROWID,), (NAME_TEXT,)),
SPECIES_ROWID : (const.SPECIES_TABLE, (SPECIES_ROWID,), (SPECIES_TEXT,)),
ANNOT_PARENT_ROWID : (const.ANNOTATION_TABLE, (ANNOT_ROWID,), (ANNOT_VISUAL_UUID,)),
'contributor_rowid' : (const.CONTRIBUTOR_TABLE, None, None),
},
)
db.modify_table(
const.NAME_TABLE, [
(None, 'name_sex', 'INTEGER DEFAULT -1', None),
]
)
[docs]def update_1_4_3(db, ibs=None):
db.modify_table(
const.ANNOTATION_TABLE, [
(None, 'annot_is_occluded', 'INTEGER', None),
(None, 'annot_is_shadowed', 'INTEGER', None),
(None, 'annot_is_washedout', 'INTEGER', None),
(None, 'annot_is_blury', 'INTEGER', None),
(None, 'annot_is_novelpose', 'INTEGER', None),
(None, 'annot_is_commonpose', 'INTEGER', None),
]
)
db.modify_table(
const.ANNOTMATCH_TABLE, [
(None, 'annotmatch_is_hard', 'INTEGER', None),
(None, 'annotmatch_is_scenerymatch', 'INTEGER', None),
(None, 'annotmatch_is_photobomb', 'INTEGER', None),
(None, 'annotmatch_is_nondistinct', 'INTEGER', None),
],
)
[docs]def update_1_4_4(db, ibs=None):
db.add_table(const.ANNOTGROUP_TABLE, (
(ANNOTGROUP_ROWID, 'INTEGER PRIMARY KEY'),
('annotgroup_uuid', 'UUID NOT NULL'),
('annotgroup_text', 'TEXT NOT NULL'),
('annotgroup_note', 'TEXT NOT NULL'),
),
superkeys=[('annotgroup_text',)],
docstr='''
List of all annotation groups (annotgroups)''')
db.add_table(const.GA_RELATION_TABLE, (
('gar_rowid', 'INTEGER PRIMARY KEY'),
(ANNOTGROUP_ROWID, 'INTEGER NOT NULL'),
(ANNOT_ROWID, 'INTEGER'),
),
superkeys=[(ANNOTGROUP_ROWID, ANNOT_ROWID)],
docstr='''
Relationship between annotgroups and annots (many to many mapping) the
many-to-many relationship between annots and annotgroups is encoded here
annotgroup_annotation_relationship stands for annotgroup-annotation-pairs.''')
[docs]def update_1_4_5(db, ibs=None):
db.modify_table(
const.ANNOTMATCH_TABLE, [
(None, 'annotmatch_note', 'TEXT', None),
],
)
[docs]def update_1_4_6(db, ibs=None):
# Maybe we want the notation of each annotation having having a set of
# classes with probabilities (c, p). Or an annotation label with a
# confidence.
db.modify_table(
const.ANNOTATION_TABLE, [
# HACK: add a column for parsable tags, this should later be
# replaced with a tag table and a tag-annot relation table
(None, 'annot_tags', 'TEXT', None),
# Remove these columns
('annot_is_occluded', None, None, None),
('annot_is_shadowed', None, None, None),
('annot_is_washedout', None, None, None),
('annot_is_blury', None, None, None),
('annot_is_novelpose', None, None, None),
('annot_is_commonpose', None, None, None),
]
)
db.modify_table(
const.ANNOTMATCH_TABLE, [
(None, 'annotmatch_reviewed', 'INTEGER', None),
(None, 'annotmatch_reviewer', 'TEXT', None),
]
)
[docs]def update_1_4_7(db, ibs=None):
db.modify_table(const.IMAGE_TABLE, (
(4, 'image_uri_original', 'TEXT', None),
))
[docs]def post_1_4_7(db, ibs=None):
if ibs is not None:
gid_list = ibs._get_all_gids()
image_uri_list = ibs.get_image_uris(gid_list)
ibs.set_image_uris_original(gid_list, image_uri_list, overwrite=True)
db.modify_table(
const.IMAGE_TABLE,
[
# change type of image_uri_original
('image_uri_original', '', 'TEXT NOT NULL', None),
],
)
[docs]def pre_1_4_8(db, ibs=None):
"""
Args:
ibs (ibeis.IBEISController):
"""
if ibs is not None:
from ibeis import tag_funcs
annotmatch_rowids = ibs._get_all_annotmatch_rowids()
id_iter = annotmatch_rowids
annotmatch_is_photobomb_list = ibs.db.get(
const.ANNOTMATCH_TABLE, ('annotmatch_is_photobomb',), id_iter,
id_colname='rowid')
annotmatch_is_nondistinct = ibs.db.get(
const.ANNOTMATCH_TABLE, ('annotmatch_is_nondistinct',), id_iter,
id_colname='rowid')
annotmatch_is_hard = ibs.db.get(
const.ANNOTMATCH_TABLE, ('annotmatch_is_hard',), id_iter,
id_colname='rowid')
annotmatch_is_scenerymatch = ibs.db.get(
const.ANNOTMATCH_TABLE, ('annotmatch_is_scenerymatch',), id_iter,
id_colname='rowid')
#ibs.get_annotmatch_note(annotmatch_rowids)
annotmatch_note_list = ibs.db.get(
const.ANNOTMATCH_TABLE, ('annotmatch_note',), annotmatch_rowids,
id_colname='rowid')
new_notes_list = annotmatch_note_list
new_notes_list = tag_funcs.set_tags_in_textformat(
'photobomb', new_notes_list, annotmatch_is_photobomb_list)
new_notes_list = tag_funcs.set_tags_in_textformat(
'nondistinct', new_notes_list, annotmatch_is_nondistinct)
new_notes_list = tag_funcs.set_tags_in_textformat(
'hard', new_notes_list, annotmatch_is_hard)
new_notes_list = tag_funcs.set_tags_in_textformat(
'scenerymatch', new_notes_list, annotmatch_is_scenerymatch)
ibs.db.set(const.ANNOTMATCH_TABLE, ('annotmatch_note',),
new_notes_list,
annotmatch_rowids)
[docs]def update_1_4_8(db, ibs=None):
"""
change notes to tag_text_data
add configuration that made the match
add the score of the match
add concept of: DEFINIATELY MATCHES, DOES NOT MATCH, CAN NOT DECIDE
Probably want a separate table for the config_rowid matching results
because the primary key needs to be (config_rowid, aid1, aid2) OR just
(config_rowid, annotmatch_rowid)
"""
db.modify_table(
const.ANNOTATION_TABLE, [
# HACK: add a column for parsable tags, this should later be
# replaced with a tag table and a tag-annot relation table
('annot_tags', 'annot_tag_text', 'TEXT', None),
]
)
db.modify_table(
const.ANNOTMATCH_TABLE, [
('annotmatch_note', 'annotmatch_tag_text', 'TEXT', None),
(None, 'annotmatch_posixtime_modified', 'INTEGER', None),
(None, 'annotmatch_pairwise_prob', 'REAL', None),
(None, 'config_hashid', 'TEXT', None),
# Remove explicit case tags in favor of consistency
('annotmatch_is_photobomb', None, None, None),
('annotmatch_is_nondistinct', None, None, None),
('annotmatch_is_hard', None, None, None),
('annotmatch_is_scenerymatch', None, None, None),
]
)
[docs]def pre_1_4_9(db, ibs=None):
if ibs is not None:
remapping_dict = {
'frogs' : 'frog',
'giraffe' : 'giraffe_reticulated',
'seals_spotted' : 'seal_spotted',
'seals_saimma_ringed' : 'seal_saimma_ringed',
}
from os.path import join
species_rowid_list = ibs._get_all_species_rowids()
species_text_list = ibs.get_species_texts(species_rowid_list)
for rowid, text in zip(species_rowid_list, species_text_list):
if text in remapping_dict:
# Update record for reticulated giraffe
ibs._set_species_texts([rowid], [remapping_dict[text]])
# Delete obsolete cPkl file on disk
cPlk_path = join(ibs.get_dbdir(), '%s.cPkl' % (text, ))
ut.delete(cPlk_path)
# Recompute all effected annotation's semantic UUIDs
aid_list = ibs._get_all_aids()
annot_species_rowid_list = ibs.get_annot_species_rowids(aid_list)
flag_list = [
annot_species_rowid == rowid
for annot_species_rowid in annot_species_rowid_list
]
aid_list_ = ut.filter_items(aid_list, flag_list)
ibs.update_annot_semantic_uuids(aid_list_)
[docs]def update_1_4_9(db, ibs=None):
db.modify_table(const.SPECIES_TABLE, (
(3, 'species_code', 'TEXT', None),
))
db.modify_table(const.SPECIES_TABLE, (
(3, 'species_nice', 'TEXT', None),
))
db.modify_table(const.SPECIES_TABLE, (
(None, 'species_toggle_enabled', 'INTEGER DEFAULT 1', None),
))
[docs]def post_1_4_9(db, ibs=None):
if ibs is not None:
ibs._clean_species()
db.modify_table(
const.SPECIES_TABLE,
[
# change type of species_nice
('species_nice', '', 'TEXT NOT NULL', None),
('species_code', '', 'TEXT NOT NULL', None),
],
)
[docs]def update_1_5_0(db, ibs=None):
# Rename encounters to imagesets
db.rename_table('encounters', 'imagesets')
db.rename_table('encounter_image_relationship', 'imageset_image_relationship')
db.modify_table(
'imagesets', [
('encounter_rowid', 'imageset_rowid', 'INTEGER PRIMARY KEY', None),
('encounter_uuid', 'imageset_uuid', 'UUID NOT NULL', None),
('encounter_text', 'imageset_text', 'TEXT NOT NULL', None),
('encounter_note', 'imageset_note', 'TEXT NOT NULL', None),
('encounter_start_time_posix', 'imageset_start_time_posix', 'INTEGER', None),
('encounter_end_time_posix', 'imageset_end_time_posix', 'INTEGER', None),
('encounter_gps_lat', 'imageset_gps_lat', 'INTEGER', None),
('encounter_gps_lon', 'imageset_gps_lon', 'INTEGER', None),
('encounter_processed_flag', 'imageset_processed_flag', 'INTEGER DEFAULT 0', None),
('encounter_shipped_flag', 'imageset_shipped_flag', 'INTEGER DEFAULT 0', None),
('encounter_smart_xml_fname', 'imageset_smart_xml_fname', 'TEXT', None),
('encounter_smart_waypoint_id', 'imageset_smart_waypoint_id', 'INTEGER', None),
],
docstr='''
List of all imagesets. This used to be called the encounter table.
It represents a group of potentially many individuals seen in a
specific place at a specific time.
''',
superkeys=[('imageset_text',)],
)
db.modify_table(
'imageset_image_relationship', [
('egr_rowid', 'gsgr_rowid', 'INTEGER PRIMARY KEY', None),
('encounter_rowid', 'imageset_rowid', 'INTEGER', None),
],
docstr='''
Relationship between imagesets and images (many to many mapping) the
many-to-many relationship between images and imagesets is encoded
here imageset_image_relationship stands for imageset-image-pairs.
''',
superkeys=[('image_rowid', 'imageset_rowid')],
relates=('images', 'imagesets'),
shortname='gsgr',
dependsmap={
'imageset_rowid': ('imagesets', ('imageset_rowid',), ('imageset_text',)),
'image_rowid' : ('images', ('image_rowid',), ('image_uuid',)),
},
)
[docs]def update_1_5_1(db, ibs=None):
# Rename encounters to imagesets
db.modify_table(
const.IMAGE_TABLE,
superkeys=[(IMAGE_UUID,)],
)
db.modify_table(
const.ANNOTMATCH_TABLE,
superkeys=[('annot_rowid1', 'annot_rowid2',)],
)
[docs]def update_1_5_2(db, ibs=None):
# Add orientation to images
db.modify_table(const.IMAGE_TABLE, (
(12, 'image_orientation', 'INTEGER DEFAULT 0', None),
))
[docs]def post_1_5_2(db, ibs=None, verbose=False):
if ibs is not None:
from ibeis.other.duct_tape import parse_and_update_image_exif_orientations
parse_and_update_image_exif_orientations(ibs, verbose=verbose)
# ========================
# Valid Versions & Mapping
# ========================
# TODO: do we save a backup with the older version number in the file name?
base = const.BASE_DATABASE_VERSION
VALID_VERSIONS = ut.odict([
#version: (Pre-Update Function, Update Function, Post-Update Function)
(base , (None, None, None )),
('1.0.0', (None, update_1_0_0, post_1_0_0 )),
('1.0.1', (None, update_1_0_1, None )),
('1.0.2', (None, update_1_0_2, None )),
('1.1.0', (None, update_1_1_0, None )),
('1.1.1', (None, update_1_1_1, None )),
('1.2.0', (None, update_1_2_0, post_1_2_0 )),
('1.2.1', (None, update_1_2_1, post_1_2_1 )),
('1.3.0', (None, update_1_3_0, None )),
('1.3.1', (pre_1_3_1, update_1_3_1, None )),
('1.3.2', (None, update_1_3_2, None )),
('1.3.3', (None, update_1_3_3, None )),
('1.3.4', (None, update_1_3_4, post_1_3_4 )),
('1.3.5', (None, update_1_3_5, None )),
('1.3.6', (None, update_1_3_6, None )),
('1.3.7', (None, update_1_3_7, None )),
('1.3.8', (None, update_1_3_8, None )),
('1.3.9', (None, update_1_3_9, None )),
('1.4.0', (None, update_1_4_0, None )),
('1.4.1', (None, update_1_4_1, None )),
('1.4.2', (None, update_1_4_2, None )),
('1.4.3', (None, update_1_4_3, None )),
('1.4.4', (None, update_1_4_4, None )),
('1.4.5', (None, update_1_4_5, None )),
('1.4.6', (None, update_1_4_6, None )),
('1.4.7', (None, update_1_4_7, post_1_4_7 )),
('1.4.8', (pre_1_4_8, update_1_4_8, None )),
('1.4.9', (pre_1_4_9, update_1_4_9, post_1_4_9 )),
('1.5.0', (None, update_1_5_0, None )),
('1.5.1', (None, update_1_5_1, None )),
('1.5.2', (None, update_1_5_2, post_1_5_2 )),
])
"""
SeeAlso:
When updating versions need to test and modify in
IBEISController._init_sqldbcore
"""
LEGACY_UPDATE_FUNCTIONS = [
('1.4.1', _sql_helpers.fix_metadata_consistency),
]
def __test_db_version_table_constraints():
"""
test for updating from version x to version y
There is a problem where the contributor_table superkey is not in
PZ_Master0 and I don't know why. Perhaps it was just a fluke, and it will
be ensured from now on.
Here is the hacky fix script:
assert 'contributors_superkeys' not in ut.get_list_column(ibs.db.get_metadata_items(), 0)
sorted(ibs.db.get_metadata_items())
# So weird that the constraint was set, but not the superkeys
constraint_str = ibs.db.get_metadata_val('contributors_constraint')
parse_result = parse.parse('CONSTRAINT superkey UNIQUE ({superkey})', constraint_str)
superkey = parse_result['superkey']
assert superkey == 'contributor_tag'
assert None is ibs.db.get_metadata_val('contributors_superkey')
ibs.db.set_metadata_val('contributors_superkeys', "[('contributor_tag',)]")
# Made a mistake
print(ibs.db.get_table_csv_header('metadata'))
badrowid = ibs.db.get_rowid_from_superkey('metadata', [('contributors_superkey',)], ('metadata_key',))
assert len(badrowid) == 1
ibs.db.delete('metadata', [badrowid[0]])
TODO: make a script that generates an empty database at version X
"""
import ibeis
tmpdir = ut.ensuredir('tmpsqltestdir')
ut.delete(tmpdir)
tmpdir = ut.ensuredir('tmpsqltestdir')
tmpdir3 = ut.ensuredir('tmpsqltestdir3')
ut.delete(tmpdir3)
tmpdir3 = ut.ensuredir('tmpsqltestdir3')
# Should not show contributor table
ibs1 = ibeis.opendb(dbdir=tmpdir, request_dbversion='1.0.0', use_cache=False)
ibs1.db.print_schema()
assert 'contributors' not in ibs1.db.get_table_names()
ibs2 = ibeis.opendb(dbdir=tmpdir, request_dbversion='1.0.3', use_cache=False)
ibs2.db.print_schema()
assert 'contributors' in ibs2.db.get_table_names()
print(ibs2.db.get_schema_current_autogeneration_str('foo'))
assert 'contributors_superkeys' in ut.get_list_column(ibs2.db.get_metadata_items(), 0)
ibs3 = ibeis.opendb(dbdir=tmpdir3, use_cache=False)
ibs3.db.print_schema()
assert 'contributors' in ibs1.db.get_table_names()
#ibeis.control.IBEISControl.__ALL_CONTROLLERS__
ibs1.db.close()
ibs2.db.close()
ibs1.depc.close()
ibs2.depc.close()
del ibs1
del ibs2
[docs]def autogen_db_schema():
"""
autogen_db_schema
CommandLine:
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema --diff=1
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema -n=-1
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema -n=0
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema -n=1
python -m ibeis.control.DB_SCHEMA --force-incremental-db-update
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema --write
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema --force-incremental-db-update --dump-autogen-schema
python -m ibeis.control.DB_SCHEMA --test-autogen_db_schema --force-incremental-db-update
Example:
>>> # ENABLE_DOCTEST
>>> from ibeis.control.DB_SCHEMA import * # NOQA
>>> autogen_db_schema()
"""
from ibeis.control import DB_SCHEMA
from ibeis.control import _sql_helpers
n = ut.get_argval('-n', int, default=-1)
schema_spec = DB_SCHEMA
db = _sql_helpers.autogenerate_nth_schema_version(schema_spec, n=n)
return db
if __name__ == '__main__':
"""
python -m ibeis.algo.preproc.preproc_chip
python -m ibeis.control.DB_SCHEMA --allexamples
"""
import multiprocessing
multiprocessing.freeze_support()
import utool as ut
ut.doctest_funcs()