FEAT: Alter table tier

[CBroz1]

Feature Request

Problem

We can imagine a pipeline where ...

• A table SomeParams was declared as dj.Manual.
• In practice, initial users in Lab A discover dj.Lookup makes more sense.
• Maintainers wish to share this pipeline with Lab B as dj.Lookup with a relevant contents attribute.

Because table tier impacts the server-side table name, modifying the table tier within a python script declares a new table of the new type and does nothing to the existing one.

Project maintainers might choose to ...

• Have separate copies of table definitions across labs A and B.
• Drop and repopulate lab A's instance with the new table type.
• Add a helper function for inserting defaults as a post-declaration process or manual chore

Currently DataJoint allows for two identically named classes to declare different tables, even if only the latter is accessible after running the python script.

schema = dj.schema('my_schema')

@schema 
class SomeParams(dj.Manual):
    ...

@schema 
class SomeParams(dj.Lookup):
    ...

By disallowing the above and checking existing matching tables on declaration, DataJoint could instead permit the altering of table types

Requirements

This updated alter would...

• check the table name against matching names of other tiers
• adjust the name on SQL, with corresponding adjustments to foreign key references

Justification

Allowing more comprehensive alters would promote the continued use of existing infrastructure across multiple projects, rather than starting over to revise past decisions.

Alternative Considerations

• Maintaining a multiple copies of the codebase - seems impractical for syncing up other aspects like make functions
• Dropping the tables and/or migrating existing data - seems high risk for average users
• An insert_default helper (eg - 1 of ~30 in the codebase) - doesn't cover other cases like dj.Manual -> dj.Computed

Related Errors

n/a

Screenshots

n/a

Additional Research and Context

I recognize that, from a dev perspective, this would be a heavy lift to allow modifying of foreign key references, but it would likely also address existing NotImplemented errors (see also #901)

datajoint-python/datajoint/declare.py

Lines 440 to 445 in 35dbbfa

	if primary_key != primary_key_:
	raise NotImplementedError("table.alter cannot alter the primary key (yet).")
	if foreign_key_sql != foreign_key_sql_:
	raise NotImplementedError("table.alter cannot alter foreign keys (yet).")
	if index_sql != index_sql_:
	raise NotImplementedError("table.alter cannot alter indexes (yet)")