Add a Custom Table to the Database
Rattail's core schema offers quite a few tables, but of course more are often added to a given system, based on custom needs. The process involves 3 steps, described further below:
- define model class for ORM
- generate and edit schema migration script
- run schema migration script
For our example we'll add two tables, for two related models: Widget and Component. It is assumed that one widget may have "multiple" (zero or more) components.
As usual the name "Poser" is a stand-in and should be replaced with your project name.
It is important to add a custom prefix to all custom tables you create. In the example below we use poser_ as our table name prefix, even though we do not prefix the model class names. The latter are more flexible whereas the table names must "obey" the rule of steering clear of the "root table namespace" (in other words, custom tables should never have an empty prefix). This is to avoid any possible issues should Rattail later add a core table of the same name.
New Table Checklist
As a convenience, this general checklist is provided to help ensure "nothing is forgotten" when adding a new table to the database. Most items are optional, depending on your needs.
- add the actual table (i.e. what the rest of this wiki page is all about)
create new master web view(s) for basic CRUD access
- and/or modify other web view(s) to expose table data
create new importer(s) to populate table from other system(s)
- and/or "exporters" to push this table's data onto other system(s)
- add watcher/consumer logic for datasync with other system(s)
- watcher is likely already provided by Rattail (and needed only if pushing data to other systems)
- consumer is only possible/relevant if other system(s) is "watched" by datasync
- don't forget to implement (allow config to specify) "runas" user
- run live importer(s) to "catch up" data for all systems
- don't forget versions (if applicable) for any affected Rattail nodes
- configure server automation for importer(s) and/or datasync
- don't forget to specify "runas" user as needed, for data versioning
- create new batch type(s) to further manipulate table data
Define Model Class
The "model class" here refers to the Python class definition, to which the underlying table is mapped by the ORM. These will live under the poser.db.model package.
Rattail uses SQLAlchemy for the ORM, so that documentation may also be helpful.
For our example we will define both our classes in a single module. Create the file at e.g. ~/src/poser/poser/db/model/widgets.py with contents:
1 import sqlalchemy as sa
2 from sqlalchemy import orm
3
4 from rattail.db import model
5
6
7 class Widget(model.Base):
8 """
9 Represents a widget object.
10 """
11 __tablename__ = 'poser_widget'
12
13 uuid = model.uuid_column()
14
15 description = sa.Column(sa.String(length=255), nullable=False, doc="""
16 Description for the widget.
17 """)
18
19 active = sa.Column(sa.Boolean(), nullable=False, default=True, doc="""
20 Flag indicating whether the widget is currently "active".
21 """)
22
23 def __str__(self):
24 return str(self.description)
25
26
27 class Component(model.Base):
28 """
29 Represents a single component of a widget.
30 """
31 __tablename__ = 'poser_component'
32 __table_args__ = (
33 sa.ForeignKeyConstraint(['widget_uuid'], ['poser_widget.uuid'], name='poser_component_fk_widget'),
34 )
35
36 uuid = model.uuid_column()
37
38 widget_uuid = sa.Column(sa.String(length=32), nullable=False)
39 widget = orm.relationship(
40 Widget, doc="""
41 Reference to the widget to which this component belongs.
42 """,
43 backref=orm.backref(
44 'components', doc="""
45 List of components for the widget.
46 """))
47
48 item_code = sa.Column(sa.String(length=20), nullable=False, doc="""
49 Item code (as string) for the component.
50 """)
51
52 description = sa.Column(sa.String(length=255), nullable=False, doc="""
53 Description for the component.
54 """)
55
56 active = sa.Column(sa.Boolean(), nullable=False, default=True, doc="""
57 Flag indicating whether the component is currently "active".
58 """)
59
60 def __str__(self):
61 return str(self.description)
Next you must import these model classes into the "root" of your model namespace. In practice that means editing the file at e.g. ~/src/poser/poser/db/model/__init__.py so that it includes the new tables:
At this point you should be able to reference the new tables via the model namespace, e.g.:
Create Migration Script
Next we must create a schema migration script, which will be responsible for creating our new tables within a database. Rattail uses Alembic to handle all schema migrations, so that project's documentation may also be helpful.
Alembic thankfully is able to generate most of the script for us. It does this by magically comparing the model class definitions (in poser.db.model) with the repository of known migrations (I think??). At any rate the first step then is to let Alembic generate the initial script:
cd /srv/envs/poser bin/alembic -c app/rattail.conf revision --head poser@head --autogenerate -m "add widgets, components"
Note that this is probably the only command you'll see where we do not run as the rattail user. This is because it will create the script file within your source folder - e.g. ~/src/poser/poser/db/alembic/versions/ - and it's assumed the rattail user does not have write access to that. However in practice your user account may not have access to write to the app/log/rattail.log file, in which case the above may throw an error. If so, something like this should work to get past that: sudo chmod go+w app/log/rattail.log
If the command completes okay, its last line of output should tell you the path to the script file it created, e.g.:
Generating /home/lance/src/poser/poser/db/alembic/versions/c145640355dc_add_widgets_components.py ... done
Now you must open that file to ensure it does not need any tweaks etc. Depending on your preference, a bit of formatting cleanup may be in order. But generally speaking, Alembic does a pretty good job generating the code and any manual changes made are "usually" just cosmetic. In some cases though, some editing may be required. (That gets outside of the scope for this documentation, see Alembic's for more info.)
Run Migration Script
While the process of creating the migration script can vary somewhat depending on the changes involved, running the script should always work the same. It's pretty much just:
cd /srv/envs/poser sudo -u rattail bin/alembic -c app/rattail.conf upgrade heads
At this point your configured (i.e. local) database should have two new tables: poser_widget and poser_component. Now you are safe to use them via Python:
1 from rattail.db import Session
2 from poser.db import model
3
4 # open database session
5 s = Session()
6
7 # add a widget with one component
8 widget = model.Widget()
9 widget.description = "First Widget Ever!"
10 widget.components.append(model.Component(item_code="42", description="Basic Component"))
11 s.add(widget)
12
13 # commit the database transaction, so widget is now truly persisted
14 s.commit()
15
16 # just for kicks, make sure we only have one widget and component
17 assert s.query(model.Widget).count() == 1
18 assert s.query(model.Component).count() == 1
19
20 # done with the database
21 s.close()