Simple SQL based tagging and the associated sqltags command line script, supporting both tagged named objects and tagged timestamped log entries.

Compared to cs.fstags and its associated fstags command, this is oriented towards large numbers of items not naturally associated with filesystem objects.

My initial use case is an activity log (unnamed timestamped tag sets) but I'm also using it for ontologies (named tag sets containing metadata).

Many basic tasks can be performed with the sqltags command line utility, documented under the SQLTagsCommand class below.

See the SQLTagsORM documentation for details about how data are stored in the database. See the SQLTagSet documentation for details of how various tag value types are supported.

Short summary:

• BaseSQLTagsCommand: Common features for commands oriented around an SQLTags database.

• glob2like: Convert a filename glob to an SQL LIKE pattern.

• main: Command line mode.

• PolyValue: A namedtuple for the polyvalues used in an SQLTagsORM.

• PolyValueColumnMixin: A mixin for classes with (float_value,string_value,structured_value) columns. This is used by the Tags and TagMultiValues relations inside SQLTagsORM.

• PolyValued: A mixin for classes with (float_value,string_value,structured_value) columns.

• prefix2like: Convert a prefix string to an SQL LIKE pattern.

• SQLParameters: The parameters required for constructing queries or extending queries with JOINs.

• SQLTagBasedTest: A cs.tagset.TagBasedTest extended with a .sql_parameters method.

• SQLTagProxies: A proxy for the tags supporting Python comparison => SQLParameters.

• SQLTagProxy: An object based on a Tag name which produces an SQLParameters when compared with some value.

• SQLTags: A class using an SQL database to store its TagSets.

• SQLTagsCommand: sqltags main command line utility.

• SQLTagSet: A singleton TagSet attached to an SQLTags instance.

• SQLTagsORM: The ORM for an SQLTags.

• SQTCriterion: Subclass of TagSetCriterion requiring an .sql_parameters method which returns an SQLParameters providing the information required to construct an sqlalchemy query. It also resets .CRITERION_PARSE_CLASSES, which will pick up the SQL capable criterion classes below.

• SQTEntityIdTest: A test on entity.id.

• UsesSQLTags: A mixin subclassing Entities to support classes which use an SQLTags to store their data.

• verbose: Emit message if in verbose mode.

Functions

glob2like(glob: str) -> str

Convert a filename glob to an SQL LIKE pattern.

main(argv=None)

Command line mode.

prefix2like(prefix: str, esc='\') -> str

Convert a prefix string to an SQL LIKE pattern.

verbose(msg, *a)

Emit message if in verbose mode.

Classes

class BaseSQLTagsCommand(cs.cmdutils.BaseCommand, SQLTagsCommandsMixin)

Common features for commands oriented around an SQLTags database.

Usage summary:

Usage: basesqltags [common-options...] subcommand [options...]
  Common features for commands oriented around an `SQLTags` database.
  Subcommands:
    dbinit [common-options...]
      Initialise the database supporting `self.sqltags`.
      This includes defining the schema and making the root metanode.
    dbshell [common-options...]
      Start an interactive database shell.
    edit criteria...
      Edit the entities specified by criteria.
    export [common-options...] [-F format] [{tag[=value]|-tag}...]
      Export entities matching all the constraints.
      -F format Specify the export format, either CSV or FSTAGS.
    find [common-options...] [-o output_format] {tag[=value]|-tag}...
      List entities matching all the constraints.
      -o output_format
                  Use output_format as a Python format string to lay out
                  the listing.
                  Default: {localtime} {headline}
    help [common-options...] [-l] [-s] [subcommand-names...]
      Print help for subcommands.
      This outputs the full help for the named subcommands,
      or the short help for all subcommands if no names are specified.
      Options:
        -l  Long listing.
        -r  Recurse into subcommands.
        -s  Short listing.
    import [common-options...] [{-u|--update}] {-|srcpath}...
      Import CSV data in the format emitted by "export".
      Each argument is a file path or "-", indicating standard input.
      -u, --update  If a named entity already exists then update its tags.
                    Otherwise this will be seen as a conflict
                    and the import aborted.
    info [common-options...] [field-names...]
      Recite general information.
      Explicit field names may be provided to override the default listing.
    log [common-options...] [-c category,...] [-d when] [-D strptime] {-|headline} [tags...]
      Record entries into the database.
      If headline is '-', read headlines from standard input.
      Options:
        -c categories       Specify the categories for this log entry.
                            The default is to recognise a leading CAT,CAT,...: prefix.
        -d dt               Use dt, an ISO8601 date, as the log entry timestamp.
        -D strptime-format  Read the time from the start of the headline
                            according to the provided strptime specification.
    orm [common-options...] define_schema
      Runs the ORM's `define_schema()` method, which creates missing tables
      and entity 0 if missing.
    repl [common-options...]
      Run a REPL (Read Evaluate Print Loop), an interactive Python prompt.
      Options:
        --banner banner  Banner.
    shell [common-options...]
      Run a command prompt via cmd.Cmd using this command's subcommands.
    tag [common-options...] {-|entity-name} {tag[=value]|-tag}...
      Tag an entity with multiple tags.
      With the form "-tag", remove that tag from the direct tags.
      A entity-name named "-" indicates that entity-names should
      be read from the standard input.

BaseSQLTagsCommand.Options(cmd: Optional[str] = None, dry_run: bool = False, force: bool = False, verbosity: int = 0, runstate: Optional[cs.resources.RunState] = None, runstate_signals: Tuple[int] = (<Signals.SIGHUP: 1>, <Signals.SIGINT: 2>, <Signals.SIGQUIT: 3>, <Signals.SIGTERM: 15>), ssh_exe: str = <factory>, db_url: str = None) -> None

Options for the BaseSQLTagsCommand class.

BaseSQLTagsCommand.USAGE_FORMAT

('Usage: {cmd} subcommand [options...]\n'
 '  Common features for commands oriented around an `SQLTags` database.')

BaseSQLTagsCommand.parse_tagset_criterion(crit_s, tag_based_test_class=None)

Parse a TagSet criterion from crit_s.

The criterion may be either:

• an integer specifying a TagSet id
• a tag criterion

BaseSQLTagsCommand.run_context(self)

Prepare the SQLTags around each command invocation.

class PolyValue(PolyValue, PolyValued)

A namedtuple for the polyvalues used in an SQLTagsORM.

We express various types in SQL as one of 3 columns:

• float_value: for floats and ints which round trip with float
• string_value: for str
• structured_value: a JSON transcription of any other type

This allows SQL indexing of basic types.

Note that because str gets stored in string_value this leaves us free to use "bare string" JSON to serialise various nonJSONable types.

The SQLTagSets class has a to_polyvalue factory which produces a PolyValue suitable for the SQL rows. NonJSONable types such as datetime are converted to a str but stored in the structured_value column. This should be overridden by subclasses as necessary.

On retrieval from the database the tag rows are converted to Python values by the SQLTagSets.from_polyvalue method, reversing the process above.

class PolyValueColumnMixin(PolyValued)

A mixin for classes with (float_value,string_value,structured_value) columns. This is used by the Tags and TagMultiValues relations inside SQLTagsORM.

PolyValueColumnMixin.float_value

Column(None, Float(), table=None, comment='tag value in numeric form')

PolyValueColumnMixin.string_value

Column(None, String(), table=None, comment='tag value in string form')

PolyValueColumnMixin.structured_value

Column(None, JSON(), table=None, comment='tag value in JSON form')

class PolyValued

A mixin for classes with (float_value,string_value,structured_value) columns.

PolyValued.as_polyvalue(self)

Return this row's value as a PolyValue.

PolyValued.is_valid(self)

Test that at most one attribute is non-None.

PolyValued.set_polyvalue(self, pv: 'PolyValued')

Set all the value fields.

PolyValued.value_test(other_value)

Return (column,test_value) for constructing tests against other_value where column if the appropriate SQLAlchemy column and test_value is the comparison value for testing.

For most other_values the test_value will just be other_value, but for certain types the test_value will be:

• NoneType: None, and the column will also be None
• datetime: datetime2unixtime(other_value)

class SQLParameters(SQLParameters)

The parameters required for constructing queries or extending queries with JOINs.

Attributes:

• criterion: the source criterion, usually an SQTCriterion subinstance
• alias: an alias of the source table for use in queries
• entity_id_column: the entities id column, alias.id if the alias is of entities, alias.entity_id if the alias is of tags
• constraint: a filter query based on alias

class SQLTagBasedTest(cs.tagset.TagBasedTest, SQTCriterion)

A cs.tagset.TagBasedTest extended with a .sql_parameters method.

SQLTagBasedTest.SQL_COMPLETE

True

SQLTagBasedTest.SQL_ID_VALUE_COMPARISON_FUNCS

{   None: <function SQLTagBasedTest.<lambda> at 0x111a56a20>,
    '<': <function SQLTagBasedTest.<lambda> at 0x111a57420>,
    '<=': <function SQLTagBasedTest.<lambda> at 0x111a57380>,
    '=': <function SQLTagBasedTest.<lambda> at 0x111a572e0>,
    '>': <function SQLTagBasedTest.<lambda> at 0x111a57560>,
    '>=': <function SQLTagBasedTest.<lambda> at 0x111a574c0>,
    '~': <function SQLTagBasedTest.<lambda> at 0x111a57600>}

SQLTagBasedTest.SQL_NAME_VALUE_COMPARISON_FUNCS

{   None: <function SQLTagBasedTest.<lambda> at 0x111a576a0>,
    '<': <function SQLTagBasedTest.<lambda> at 0x111a57880>,
    '<=': <function SQLTagBasedTest.<lambda> at 0x111a577e0>,
    '=': <function SQLTagBasedTest.<lambda> at 0x111a57740>,
    '>': <function SQLTagBasedTest.<lambda> at 0x111a579c0>,
    '>=': <function SQLTagBasedTest.<lambda> at 0x111a57920>,
    '~': <function SQLTagBasedTest.<lambda> at 0x111a57a60>}

SQLTagBasedTest.SQL_TAG_VALUE_COMPARISON_FUNCS

{   None: <function SQLTagBasedTest.<lambda> at 0x111a56c00>,
    '<': <function SQLTagBasedTest.<lambda> at 0x111a56fc0>,
    '<=': <function SQLTagBasedTest.<lambda> at 0x111a56e80>,
    '=': <function SQLTagBasedTest.<lambda> at 0x111a56d40>,
    '>': <function SQLTagBasedTest.<lambda> at 0x111a57240>,
    '>=': <function SQLTagBasedTest.<lambda> at 0x111a57100>,
    '~': <function SQLTagBasedTest.<lambda> at 0x111a568e0>}

SQLTagBasedTest.SQL_UNIXTIME_VALUE_COMPARISON_FUNCS

{   None: <function SQLTagBasedTest.<lambda> at 0x111a57b00>,
    '<': <function SQLTagBasedTest.<lambda> at 0x111a57ce0>,
    '<=': <function SQLTagBasedTest.<lambda> at 0x111a57c40>,
    '=': <function SQLTagBasedTest.<lambda> at 0x111a57ba0>,
    '>': <function SQLTagBasedTest.<lambda> at 0x111a57e20>,
    '>=': <function SQLTagBasedTest.<lambda> at 0x111a57d80>}

SQLTagBasedTest.TE_VALUE_COMPARISON_FUNCS

{   '<': <function SQLTagBasedTest.<lambda> at 0x111968040>,
    '<=': <function SQLTagBasedTest.<lambda> at 0x111a57f60>,
    '=': <function SQLTagBasedTest.<lambda> at 0x111a57ec0>,
    '>': <function SQLTagBasedTest.<lambda> at 0x111968180>,
    '>=': <function SQLTagBasedTest.<lambda> at 0x1119680e0>,
    '~': <function SQLTagBasedTest.<lambda> at 0x111968220>,
    '~/': <function SQLTagBasedTest.<lambda> at 0x1119682c0>}

SQLTagBasedTest.match_tagged_entity(self, te: cs.tagset.TagSet) -> bool

Match this criterion against te.

class SQLTagProxies

A proxy for the tags supporting Python comparison => SQLParameters.

Example:

sqltags.tags.dotted.name.here == 'foo'

class SQLTagProxy

An object based on a Tag name which produces an SQLParameters when compared with some value.

Example:

>>> sqltags = SQLTags('sqlite://')
>>> sqltags.init()
>>> # make a SQLParameters for testing the tag 'name.thing'==5
>>> sqlp = sqltags.tags.name.thing == 5
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.float_value = :float_value_1'
>>> sqlp = sqltags.tags.name.thing == 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value = :string_value_1'

SQLTagProxy.__eq__(self, other, alias=None) -> cs.sqltags.SQLParameters

Return an SQL = test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing == 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value = :string_value_1'

SQLTagProxy.__ge__(self, other)

Return an SQL >= test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing >= 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value >= :string_value_1'

SQLTagProxy.__getattr__(self, sub_tag_name)

Magic access to dotted tag names: produce a new SQLTagProxy from ourself.

SQLTagProxy.__gt__(self, other)

Return an SQL > test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing > 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value > :string_value_1'

SQLTagProxy.__hash__

None

SQLTagProxy.__le__(self, other)

Return an SQL <= test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing <= 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value <= :string_value_1'

SQLTagProxy.__lt__(self, other)

Return an SQL < test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing < 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value < :string_value_1'

SQLTagProxy.__ne__(self, other, alias=None) -> cs.sqltags.SQLParameters

Return an SQL <> test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing != 'foo'
>>> str(sqlp.constraint)
'tags_1.name = :name_1 AND tags_1.string_value != :string_value_1'

SQLTagProxy.by_op_text(self, op_text, other, alias=None)

Return an SQLParameters based on the comparison's text representation.

Parameters:

• op_text: the comparsion operation text, one of: '=', '<=', '<', '>=', '>', '~'.
• other: the other value for the comparison, used to infer the SQL column name and kept to provide the SQL value parameter
• alias: optional SQLAlchemy table alias

SQLTagProxy.likeglob(self, globptn: str) -> cs.sqltags.SQLParameters

Return an SQL LIKE test approximating a glob as an SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing.likeglob('foo*')
>>> str(sqlp.constraint)
"tags_1.name = :name_1 AND tags_1.string_value LIKE :string_value_1 ESCAPE '\\'"

SQLTagProxy.startswith(self, prefix: str) -> cs.sqltags.SQLParameters

Return an SQL LIKE prefix test SQLParameters.

Example:

>>> sqlp = SQLTags('sqlite://').tags.name.thing.startswith('foo')
>>> str(sqlp.constraint)
"tags_1.name = :name_1 AND tags_1.string_value LIKE :string_value_1 ESCAPE '\\'"

class SQLTagSet(cs.obj.SingletonMixin, cs.tagset.TagSet)

A singleton TagSet attached to an SQLTags instance.

As with the TagSet superclass, tag values can be any Python type. However, because we are storing these values in an SQL database it is necessary to provide a conversion facility to prepare those values for storage.

The database schema is described in the SQLTagsORM class; in short we directly support None, float and str, ints which round trip with float, and list, tuple and dict whose contents transcribe to JSON.

ints which are too large to round trip with float are treated as an extended "bigint" type using the scheme described below.

Because the ORM has distinct float and str columns to support indexing, there will be no plain strings in the remaining JSON blob column. Therefore we support other types by providing functions to convert each type to a str and back, and an associated "type label" which will be prefixed to the string; the resulting string is stored in the JSON blob.

The default mechanism is based on the following class attributes and methods:

• TYPE_JS_MAPPING: a mapping of a type label string to a 3 tuple of (type,to_str,from_str) being the extended type, a function to convert an instance to str and a function to convert a str to an instance of this type
• to_js_str: a method accepting (tag_name,tag_value) and returning tag_value as a str; the default implementation looks up the type of tag_value in TYPE_JS_MAPPING to locate the corresponding to_str function
• from_js_str: a method accepting (tag_name,js) which uses the leading type label prefix from the js to look up the corresponding from_str function from TYPE_JS_MAPPING and use it on the tail of js

The default TYPE_JS_MAPPING has mappings for:

• "bigint": conversions for int
• "date": conversions for datetime.date
• "datetime": conversions for datetime.datetime

Subclasses wanting to augument the TYPE_JS_MAPPING should prepare their own with code such as:

class SubSQLTagSet(SQLTagSet,....):
    ....
    TYPE_JS_MAPPING=dict(SQLTagSet.TYPE_JS_MAPPING)
    TYPE_JS_MAPPING.update(
      typelabel=(type, to_str, from_str),
      ....
    )

SQLTagSet.TYPE_JS_MAPPING

{   'bigint': (<class 'int'>, <class 'str'>, <class 'int'>),
    'date': (   <class 'datetime.date'>,
                <method 'isoformat' of 'datetime.date' objects>,
                <built-in method fromisoformat of type object at 0x10dbee580>),
    'datetime': (   <class 'datetime.datetime'>,
                    <method 'isoformat' of 'datetime.datetime' objects>,
                    <built-in method fromisoformat of type object at 0x10dbee8c0>),
    'uuid': (<class 'uuid.UUID'>, <class 'str'>, <class 'uuid.UUID'>)}

SQLTagSet.add_db_tag(self, tag_name, pv: cs.sqltags.PolyValue)

Add a tag to the database.

SQLTagSet.child_tagsets(self, tag_name='parent')

Return the child TagSets as defined by their parent Tag, by default the Tag named 'parent'.

SQLTagSet.db_session(self, new=False)

Context manager to obtain a new session if required, just a shim for self.sqltags.db_session.

SQLTagSet.discard_db_tag(self, tag_name: str, pv: Optional[cs.sqltags.PolyValue] = None)

Discard a tag from the database.

SQLTagSet.format_attributes

{'json': <function TagSet.json at 0x10f6bd080>}

SQLTagSet.from_js_str(tag_name: str, js: str)

Convert the str js to a Tag value. This is the reverse of as_js_str.

Subclasses wanting extra type support should either: (usual approach) provide their own TYPE_JS_MAPPING class attribute as described at the top of this class or (for unusual requirements) override this method and also to_js_str.

SQLTagSet.from_polyvalue(tag_name: str, pv: cs.sqltags.PolyValue)

Convert an SQL PolyValue to a tag value.

This can be overridden by subclasses along with to_polyvalue. The tag_name is provided for context in case it should influence the normalisation.

SQLTagSet.id

<property object at 0x1118d2020>

SQLTagSet.jsonable(value)

Convert value to a form which can be directly JSON serialised.

In particular this converts non-list/set/tuple Sequences to lists and non-dict Mappings to dicts.

Warning: this is not robust against cycles.

SQLTagSet.name

<property object at 0x11197cdb0>

SQLTagSet.parent_tagset(self, tag_name='parent')

Return the parent TagSet as defined by a Tag, by default the Tag named 'parent'.

SQLTagSet.setdefault(self, tag_name, value)

Return self[tag_name], setting it to value if not already present.

SQLTagSet.to_js_str(tag_name: str, tag_value) -> str

Convert tag_value to a str suitable for storage in structure_value. This can be reversed by from_js_str.

Subclasses wanting extra type support should either: (usual approach) provide their own TYPE_JS_MAPPING class attribute as described at the top of this class or (for unusual requirements) override this method and also from_js_str.

SQLTagSet.to_polyvalue(tag_name: str, tag_value) -> cs.sqltags.PolyValue

Normalise Tag values for storage via SQL. Preserve things directly expressable in JSON. Convert other values via to_js_str. Return a PolyValue for use with the SQL rows.

class SQLTags(cs.obj.SingletonMixin, cs.tagset.BaseTagSets, cs.deco.Promotable)

A class using an SQL database to store its TagSets.

SQLTags.DBURL_DEFAULT

'~/var/sqltags.sqlite'

SQLTags.DBURL_ENVVAR

'SQLTAGS_DBURL'

SQLTags.DB_URL_MEMORY

'sqlite://'

SQLTags.TAGSETCLASS_DEFAULT(self, *a, _sqltags=None, **kw)

Factory to return a suitable TagSet subclass instance. This produces an SQLTagSet instance correctly associated with this SQLTags.

SQLTags.TagSetClass(self, *, name, **kw)

Local implementation of TagSetClass so that we can annotate it with a .singleton_also_by attribute.

SQLTags.__getitem__(self, index)

Return an SQLTagSet for index (an int or str).

SQLTags.__setitem__(self, index, te)

Dummy __setitem__ which checks te against the db by type because the factory has already inserted it into the database.

SQLTags.db_entity(self, index)

Return the Entities instance for index or None.

SQLTags.db_session(self, *, new=False)

Context manager to obtain a db session if required (or if new is true).

SQLTags.dbshell(self)

Run a database shell connected to the ORM database.

SQLTags.default_db_session

<property object at 0x11197ec50>

SQLTags.default_factory(self, name: Optional[str] = None, *, unixtime=None, tags=None)

Fetch or create an SQLTagSet for name. Return the SQLTagSet.

Note that name may be None to create a new "log" entry.

SQLTags.find(self, *criteria, _without_tags=False, **crit_kw)

A generator to create and run a query derived from criteria, yielding SQLTagSet instances.

Parameters:

• criteria: positional arguments which should be SQTCriterions or a str suitable for SQTCriterion.from_str
• _without_tags: optional flag to return entities without tags, default False; this can be used for a much faster scan of the entities because it omits the JOIN against the tag table
• crit_kw: keyword parameters are appended to the criteria as further tag equality tests

SQLTags.flush(self)

Flush the current session state to the database.

SQLTags.from_str(db_url: str)

Create an SQLTags from a db_url string.

SQLTags.get(self, index, default=None)

Return an SQLTagSet matching index, or default if there is no such entity.

SQLTags.import_csv_file(self, f, *, update_mode=False)

Import CSV data from the file f.

If update_mode is true named records which already exist will update from the data, otherwise the conflict will raise a ValueError.

SQLTags.import_tagged_entity(self, te, *, update_mode=False) -> None

Import the TagSet te.

This updates the database with the contents of the supplied TagSet, which has no inherent relationship to the database.

If update_mode is true named records which already exist will update from te, otherwise the conflict will raise a ValueError.

SQLTags.infer_db_url(envvar=None, default_path=None)

Infer the database URL.

Parameters:

• envvar: environment variable to specify a default, default from cls.DBURL_ENVVAR i.e. from $SQLTAGS_DBURL.
• default_path: optional default db URL if no environment variable, default from cls.DBURL_DEFAULT (~/var/sqltags.sqlite).

SQLTags.items(self, *, prefix=None) -> Iterable[Tuple[str, cs.sqltags.SQLTagSet]]

Return an iterable of (tagset_name,TagSet). Excludes unnamed TagSets.

Constrain the names to those starting with prefix if prefix is not None.

SQLTags.keys(self, *, prefix=None)

Yield all the nonNULL names.

Constrain the names to those starting with prefix if not None.

SQLTags.metanode

<property object at 0x11197e840>

SQLTags.preload(self, *criteria) -> Set[cs.sqltags.SQLTagSet]

Preload the SQLTagSets matching each criterion in criteria. Note that is is effectively an OR of the criteria, not an AND. Return a set of the entities.

SQLTags.startup_shutdown(self)

Open the ORM while the SQLTags is open.

SQLTags.values(self, *, prefix=None)

Return an iterable of the named TagSets. Excludes unnamed TagSets.

Constrain the names to those starting with prefix if not None.

class SQLTagsCommand(BaseSQLTagsCommand)

sqltags main command line utility.

Usage summary:

Usage: sqltags [common-options...] subcommand [options...]
  Common features for commands oriented around an `SQLTags` database.
  Subcommands:
    dbinit [common-options...]
      Initialise the database supporting `self.sqltags`.
      This includes defining the schema and making the root metanode.
    dbshell [common-options...]
      Start an interactive database shell.
    edit criteria...
      Edit the entities specified by criteria.
    export [common-options...] [-F format] [{tag[=value]|-tag}...]
      Export entities matching all the constraints.
      -F format Specify the export format, either CSV or FSTAGS.
    find [common-options...] [-o output_format] {tag[=value]|-tag}...
      List entities matching all the constraints.
      -o output_format
                  Use output_format as a Python format string to lay out
                  the listing.
                  Default: {localtime} {headline}
    help [common-options...] [-l] [-s] [subcommand-names...]
      Print help for subcommands.
      This outputs the full help for the named subcommands,
      or the short help for all subcommands if no names are specified.
      Options:
        -l  Long listing.
        -r  Recurse into subcommands.
        -s  Short listing.
    import [common-options...] [{-u|--update}] {-|srcpath}...
      Import CSV data in the format emitted by "export".
      Each argument is a file path or "-", indicating standard input.
      -u, --update  If a named entity already exists then update its tags.
                    Otherwise this will be seen as a conflict
                    and the import aborted.
    info [common-options...] [field-names...]
      Recite general information.
      Explicit field names may be provided to override the default listing.
    list [common-options...] [entity-names...]
      List entities and their tags.
      Options:
        -l  Long mode.
    log [common-options...] [-c category,...] [-d when] [-D strptime] {-|headline} [tags...]
      Record entries into the database.
      If headline is '-', read headlines from standard input.
      Options:
        -c categories       Specify the categories for this log entry.
                            The default is to recognise a leading CAT,CAT,...: prefix.
        -d dt               Use dt, an ISO8601 date, as the log entry timestamp.
        -D strptime-format  Read the time from the start of the headline
                            according to the provided strptime specification.
    ls [common-options...] [entity-names...]
      List entities and their tags.
      Options:
        -l  Long mode.
    orm [common-options...] define_schema
      Runs the ORM's `define_schema()` method, which creates missing tables
      and entity 0 if missing.
    repl [common-options...]
      Run a REPL (Read Evaluate Print Loop), an interactive Python prompt.
      Options:
        --banner banner  Banner.
    shell [common-options...]
      Run a command prompt via cmd.Cmd using this command's subcommands.
    tag [common-options...] {-|entity-name} {tag[=value]|-tag}...
      Tag an entity with multiple tags.
      With the form "-tag", remove that tag from the direct tags.
      A entity-name named "-" indicates that entity-names should
      be read from the standard input.

SQLTagsCommand.cmd_list(self, argv)

Usage: {cmd} [entity-names...] List entities and their tags. Options: -l Long mode.

SQLTagsCommand.cmd_ls(self, argv)

Usage: {cmd} [entity-names...] List entities and their tags. Options: -l Long mode.

class SQLTagsCommandsMixin(cs.tagset.TagsCommandMixin)

SQLTagsCommandsMixin.GETOPT_SPEC

'f:'

SQLTagsCommandsMixin.cmd_dbinit(self, argv)

Usage: {cmd} Initialise the database supporting self.sqltags. This includes defining the schema and making the root metanode.

SQLTagsCommandsMixin.cmd_dbshell(self, argv)

Usage: {cmd} Start an interactive database shell.

SQLTagsCommandsMixin.cmd_edit(self, argv)

Usage: edit criteria... Edit the entities specified by criteria.

SQLTagsCommandsMixin.cmd_export(self, argv)

Usage: {cmd} [-F format] [{{tag[=value]|-tag}}...] Export entities matching all the constraints. -F format Specify the export format, either CSV or FSTAGS.

The CSV export format is CSV data with the following columns:

• unixtime: the entity unixtime, a float
• id: the entity database row id, an integer
• name: the entity name
• tags: a column per Tag

SQLTagsCommandsMixin.cmd_find(self, argv)

Usage: {cmd} [-o output_format] {{tag[=value]|-tag}}... List entities matching all the constraints. -o output_format Use output_format as a Python format string to lay out the listing. Default: {FIND_OUTPUT_FORMAT_DEFAULT}

SQLTagsCommandsMixin.cmd_import(self, argv)

Usage: {cmd} [{{-u|--update}}] {{-|srcpath}}... Import CSV data in the format emitted by "export". Each argument is a file path or "-", indicating standard input. -u, --update If a named entity already exists then update its tags. Otherwise this will be seen as a conflict and the import aborted.

TODO: should this be a transaction so that an import is all or nothing?

SQLTagsCommandsMixin.cmd_log(self, argv)

Record a log entry.

Usage: {cmd} [-c category,...] [-d when] [-D strptime] {{-|headline}} [tags...] Record entries into the database. If headline is '-', read headlines from standard input. Options: -c categories Specify the categories for this log entry. The default is to recognise a leading CAT,CAT,...: prefix. -d dt Use dt, an ISO8601 date, as the log entry timestamp. -D strptime-format Read the time from the start of the headline according to the provided strptime specification.

SQLTagsCommandsMixin.cmd_orm(self, argv)

Usage: {cmd} define_schema Runs the ORM's define_schema() method, which creates missing tables and entity 0 if missing.

SQLTagsCommandsMixin.cmd_shell(self, argv)

Usage: {cmd} Run an interactive Python prompt with some predefined local names.

SQLTagsCommandsMixin.cmd_tag(self, argv)

Usage: {cmd} {{-|entity-name}} {{tag[=value]|-tag}}... Tag an entity with multiple tags. With the form "-tag", remove that tag from the direct tags. A entity-name named "-" indicates that entity-names should be read from the standard input.

SQLTagsCommandsMixin.parse_categories(categories)

Extract "category" words from the str categories, return a list of category names.

Splits on commas, strips leading and trailing whitespace, downcases.

class SQLTagsORM(cs.sqlalchemy_utils.ORM, cs.dateutils.UNIXTimeMixin)

The ORM for an SQLTags.

The current implementation uses 3 tables:

• entities: this has a NULLable name and unixtime UNIX timestamp; this is unique per name if the name is not NULL
• tags: this has an entity_id, name and a value stored in one of three columns: float_value, string_value and structured_value which is a JSON blob; this is unique per (entity_id,name)
• tag_subvalues: this is a broken out version of tags when structured_value is a sequence or mapping, breaking out the values one per row; this exists to support "tag contains value" lookups

Tag values are stored as follows:

• None: all 3 columns are set to NULL
• float: stored in float_value
• int: if the int round trips to float then it is stored in float_value, otherwise it is stored in structured_value with the type label "bigint"
• str: stored in string_value
• list, tuple, dict: stored in structured_value; if these containers contain unJSONable content there will be trouble
• other types, such as datetime: these are converted to strings with identifying type label prefixes and stored in structured_value

The float_value and string_value columns allow us to provide indices for these kinds of tag values.

The type label scheme takes advantage of the fact that actual strs are stored in the string_value column. Because of this, there will be no actual strings in structured_value. Therefore, we can convert nonJSONable types to str and store them here.

The scheme used is to provide conversion functions to convert types to str and back, and an associated "type label" prefix. For example, we store a datetime as the ISO format of the datetime with "datetime:" prefixed to it.

The actual conversions are kept with the SQLTagSet class (or any subclass). This ORM receives the 3-tuples of SQL ready values from that class as the PolyValue namedtuple and does not perform any conversion itself. The conversion process is described in SQLTagSet.

SQLTagsORM.declare_schema(self)

Define the database schema / ORM mapping.

SQLTagsORM.prepare_metanode(self, *, session)

Ensure row id 0, the metanode, exists.

SQLTagsORM.search(self, criteria, *, session, mode='tagged')

Construct a query to match Entity rows matching the supplied criteria iterable. Return an SQLAlchemy Query.

The mode parameter has the following values:

• 'id': the query only yields entity ids
• 'entity': (default) the query yields entities without tags
• 'tagged': (default) the query yields entities left outer joined with their matching tags

Note that the 'tagged' result produces multiple rows for any entity with multiple tags, and that this requires the caller to fold entities with multiple tags together.

Note: due to implementation limitations the SQL query itself may not apply all the criteria, so every criterion must still be applied to the results using its .match_entity method.

If name is omitted or None the query will match log entities otherwise the entity with the specified name.

The criteria should be an iterable of SQTCriterion instances used to construct the query.

class SQTCriterion(cs.tagset.TagSetCriterion)

Subclass of TagSetCriterion requiring an .sql_parameters method which returns an SQLParameters providing the information required to construct an sqlalchemy query. It also resets .CRITERION_PARSE_CLASSES, which will pick up the SQL capable criterion classes below.

SQTCriterion.CRITERION_PARSE_CLASSES

[<class 'cs.sqltags.SQTEntityIdTest'>, <class 'cs.sqltags.SQLTagBasedTest'>]

SQTCriterion.SQL_COMPLETE

False

SQTCriterion.from_equality(tag_name, tag_value)

Return an SQTCriterion instance based on tag_name==tag_value. This supports SQLTags.find's keyword parameters.

SQTCriterion.match_tagged_entity(self, te: cs.tagset.TagSet) -> bool

Perform the criterion test on the Python object directly. This is used at the end of a query to implement tests which cannot be sufficiently implemented in SQL. If self.SQL_COMPLETE it is not necessary to call this method.

SQTCriterion.sql_parameters(self, orm) -> cs.sqltags.SQLParameters

Subclasses must return am SQLParameters instance parameterising the SQL queries that follow.

class SQTEntityIdTest(SQTCriterion)

A test on entity.id.

SQTEntityIdTest.SQL_COMPLETE

True

SQTEntityIdTest.match_tagged_entity(self, te: cs.tagset.TagSet) -> bool

Test the TagSet te against self.entity_ids.

SQTEntityIdTest.parse(s, offset=0, delim=None)

Parse a decimal entity id from s.

class UsesSQLTags(cs.tagged.Entities)

A mixin subclassing Entities to support classes which use an SQLTags to store their data.

As with cs.tagset.Entities, subclasses must supply a hastags_class and may supply a tagsets_class if it should not be SQLTags. The subclass must also define a .TYPE_ZONE class attribute. An example from cs.cdrip:

class MBDB(UsesSQLTags, MultiOpenMixin, RunStateMixin:

    TYPE_ZONE = 'mbdb'
    EntityClass = _MBEntity
    TagsetsClass = MBSQLTags