= 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:

 1. define model class for ORM
 1. generate and edit schema migration script
 1. 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.

{{{#!wiki caution
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)
 * [[LilSnippets/AddMasterView|create new master web view(s)]] for basic CRUD access
  * and/or modify other web view(s) to expose table data
 * [[https://rattailproject.org/docs/rattail/narr/importers.html|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 [[https://www.sqlalchemy.org/|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:

{{{#!highlight python
import sqlalchemy as sa
from sqlalchemy import orm

from rattail.db import model


class Widget(model.Base):
    """
    Represents a widget object.
    """
    __tablename__ = 'poser_widget'

    uuid = model.uuid_column()

    description = sa.Column(sa.String(length=255), nullable=False, doc="""
    Description for the widget.
    """)

    active = sa.Column(sa.Boolean(), nullable=False, default=True, doc="""
    Flag indicating whether the widget is currently "active".
    """)

    def __str__(self):
        return str(self.description)


class Component(model.Base):
    """
    Represents a single component of a widget.
    """
    __tablename__ = 'poser_component'
    __table_args__ = (
        sa.ForeignKeyConstraint(['widget_uuid'], ['poser_widget.uuid'], name='poser_component_fk_widget'),
    )

    uuid = model.uuid_column()

    widget_uuid = sa.Column(sa.String(length=32), nullable=False)
    widget = orm.relationship(
        Widget, doc="""
        Reference to the widget to which this component belongs.
        """,
        backref=orm.backref(
            'components', doc="""
            List of components for the widget.
            """))

    item_code = sa.Column(sa.String(length=20), nullable=False, doc="""
    Item code (as string) for the component.
    """)

    description = sa.Column(sa.String(length=255), nullable=False, doc="""
    Description for the component.
    """)

    active = sa.Column(sa.Boolean(), nullable=False, default=True, doc="""
    Flag indicating whether the component is currently "active".
    """)

    def __str__(self):
        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:

{{{#!highlight python
# bring in all core tables from rattail
from rattail.db.model import *

# bring in our custom tables
from .widgets import Widget, Component
}}}

At this point you should be able to reference the new tables via the model namespace, e.g.:

{{{#!highlight python
from poser.db import model

print(model.Widget)
print(model.Component)
}}}

== 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 [[http://alembic.zzzcomputing.com/en/latest/|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"
}}}

{{{#!wiki caution
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:

{{{#!highlight python
from rattail.db import Session
from poser.db import model

# open database session
s = Session()

# add a widget with one component
widget = model.Widget()
widget.description = "First Widget Ever!"
widget.components.append(model.Component(item_code="42", description="Basic Component"))
s.add(widget)

# commit the database transaction, so widget is now truly persisted
s.commit()

# just for kicks, make sure we only have one widget and component
assert s.query(model.Widget).count() == 1
assert s.query(model.Component).count() == 1

# done with the database
s.close()
}}}