cli/migrate-metadata: a command to adjust schema and data to use the new internal / metadata split
1 files changed, 70 insertions(+), 0 deletions(-)

M tshistory/cli.py
M tshistory/cli.py +70 -0
@@ 219,6 219,76 @@ def migrate_to_groups(db_uri, reset=Fals
     sch._create_group(engine, reset)
 
 
+@tsh.command(name='migrate-metadata')
+@click.argument('db-uri')
+@click.option('--namespace', default='tsh')
+def migrate_metadata(db_uri, namespace='tsh'):
+    engine = create_engine(find_dburi(db_uri))
+    ns = namespace
+
+    with engine.begin() as cn:
+
+        # check initial condition
+        unmigrated = cn.execute(
+            "select exists (select 1 "
+            "from information_schema.columns "
+            f"where table_schema='{ns}' and "
+            "        table_name='registry' and "
+            "        column_name='tablename'"
+            ")"
+        ).scalar()
+        # add internal_metadata, add gin indexes
+        # rename seriesname -> name
+        # split internal / user metadata
+        # drop tablename
+        cn.execute(
+            f'alter table "{ns}".registry '
+            f'add column if not exists "internal_metadata" jsonb'
+        )
+        cn.execute(
+            f'create index if not exists idx_metadata '
+            f'on "{ns}".registry using gin (metadata)'
+        )
+        cn.execute(
+            f'create index if not exists idx_internal_metadata '
+            f'on "{ns}".registry using gin (internal_metadata)'
+        )
+        if unmigrated:
+            cn.execute(
+                f'alter table "{ns}".registry rename column seriesname to name'
+            )
+
+        # collect all series metadata and split internal / user
+        if unmigrated:
+            print('migrating data')
+            allmetas = {}
+            metakeys = tshclass.metakeys | {'supervision_status'}
+
+            for name, tablename, imeta in cn.execute(
+                    f'select name, tablename, metadata from "{ns}".registry'):
+                umeta = {}
+                for k in list(imeta):
+                    if k not in metakeys:
+                        umeta[k] = imeta.pop(k)
+                imeta['tablename'] = tablename
+                allmetas[name] = (imeta, umeta)
+
+            # store them
+            for name, (imeta, umeta) in allmetas.items():
+                cn.execute(
+                    f'update "{ns}".registry '
+                    'set (internal_metadata, metadata) = '
+                    '    (%(imeta)s, %(umeta)s) '
+                    'where name=%(name)s',
+                    name=name,
+                    imeta=dumps(imeta),
+                    umeta=dumps(umeta)
+                )
+
+        cn.execute(
+            f'alter table "{ns}".registry drop column if exists "tablename"'
+        )
+
 # db maintenance
 
 @tsh.command(name='init-db')