CORE-POS Integration

{{>toc}}

# CORE-POS Integration Setup for Messkit

At this time the setup is manual; see below for steps.

## Install Packages

There are a few packages required but you only need to specify one, the rest are brought in as dependencies.  Make sure your virtual environment is activated and then install:

```sh

source /path/to/envs/messkit/bin/activate

pip install tailbone-corepos

```

## Configure Messkit

Now you'll add the database connection(s) to `/path/to/envs/messkit/app/rattail.conf` - examples follow but first to clarify:

CORE has (at least!) 5 different types of databases: 3 for Office, 2 for Lane.  Rattail splits these up at a logical level, meaning each of the 5 types must be configured separately.  In practice you normally only need to use 1 or 2 of these types:

- `office_op`

- `office_trans`

- `office_trans_archive`

- `lane_op`

- `lane_trans`

Also, you must give these connections an explicit timeout, after which they will be "refreshed" when in use.  This to work around MySQL behavior which will "expire" a connection if has not been used for a while.  IIRC it will expire after like 8 hours?  But we set our timeout to 1 hour (3600 seconds) to be safe.  You must do this for *each* connection.  (It technically should only matter for "long-running" processes..of which the web app would be one, if you have it talking to the CORE DB..datasync would be another.)

If you just want to query the CORE Office "operational" DB:

```ini

[corepos.db.office_op]

default.url = mysql+mysqlconnector://USERNAME:PASSWORD@pos-server/core_op

default.pool_recycle = 3600

```

But you can specify as many as you want.  (Note, these are all "Office Op" type!)  There ideally should be a "default" connection though:

```ini

[corepos.db.office_op]

keys = default, store2, store3

default.url = mysql+mysqlconnector://USERNAME:PASSWORD@store1-server/core_op

default.pool_recycle = 3600

store2.url = mysql+mysqlconnector://USERNAME:PASSWORD@store2-server/core_op

store2.pool_recycle = 3600

store3.url = mysql+mysqlconnector://USERNAME:PASSWORD@store3-server/core_op

store3.pool_recycle = 3600

```

And if you want to query the other types too, here is a "complete" setup for 1 server and 2 lanes:

```ini

[corepos.db.office_op]

default.url = mysql+mysqlconnector://USERNAME:PASSWORD@pos-server/core_op

default.pool_recycle = 3600

[corepos.db.office_trans]

default.url = mysql+mysqlconnector://USERNAME:PASSWORD@pos-server/core_trans

default.pool_recycle = 3600

[corepos.db.office_trans_archive]

default.url = mysql+mysqlconnector://USERNAME:PASSWORD@pos-server/trans_archive

default.pool_recycle = 3600

# nb. no default here, but that is okay as long as no code requires one!

[corepos.db.lane_op]

keys = lane1, lane2

lane1.url = mysql+mysqlconnector://USERNAME:PASSWORD@lane1/opdata

lane1.pool_recycle = 3600

lane2.url = mysql+mysqlconnector://USERNAME:PASSWORD@lane2/opdata

lane2.pool_recycle = 3600

[corepos.db.lane_trans]

keys = lane1, lane2

lane1.url = mysql+mysqlconnector://USERNAME:PASSWORD@lane1/translog

lane1.pool_recycle = 3600

lane2.url = mysql+mysqlconnector://USERNAME:PASSWORD@lane2/translog

lane2.pool_recycle = 3600

```

You also should modify your `/path/to/envs/messkit/app/upgrade.sh` script so that the integration packages are updated whenever your app is.

In that script you should see this line:

```sh

$PIP install $QUIET --upgrade --upgrade-strategy eager Messkit

```

You should modify that to look like this:

```sh

$PIP install $QUIET --upgrade --upgrade-strategy eager Messkit tailbone-corepos

```

### Test Connectivity

With the above in place you can test connectivity with a simple Python script - create the file at `/path/to/envs/messkit/app/test-core-cxn.py` and in it put:

```python

from rattail.config import make_config

config = make_config('app/quiet.conf', versioning=False)

from corepos.db.office_op import Session as CoreSession, model as corepos

core_session = CoreSession() # nb. uses 'default' engine

for store in core_session.query(corepos.Store).order_by('storeID'):

    print(store)

core_session.close()

```

Then run it like so:

```sh

cd /path/to/envs/messkit

bin/python app/test-core-cxn.py

```

If you want to test connectivity for *all* your Office Op DBs then the script could be like (run it the same way):

```python

from rattail.config import make_config

config = make_config('app/quiet.conf', versioning=False)

from corepos.db.office_op import Session as CoreSession, model as corepos

for key, engine in config.core_office_op_engines.items():

    print("{} says:".format(key))

    core_session = CoreSession(bind=engine)

    for store in core_session.query(corepos.Store).order_by('storeID'):

        print("\t{}".format(store))

    core_session.close()

```

### Include CORE Views

Now that you can talk to the CORE DB, that means Messkit can show you that data directly in the web app.  To see it you must "include" whichever views you want.  For more information see [Configurable Views](https://rattailproject.org/docs/messkit/features.html#configurable-views).

However please note that the integration comes with two different types of views.  The ones you want at this point are views which show the CORE tables (listed under the "corepos" section when configuring).

The other type of views it has are customizations of the native "rattail" views.  But you can't use those until you extend the DB schema, next section.

### Extend DB Schema

If you want to *import* data from CORE into Messkit then you will need to extend its schema.  If you only need to *query* the CORE data but not import to Messkit, you can skip this section.

One of the packages that got installed (namely, [rattail-corepos](https://redmine.rattailproject.org/projects/corepos-integration/repository/rattail-corepos)) contains the DB extensions and you now must configure Messkit to use them.

Edit the file at `/path/to/envs/messkit/app/rattail.conf` and locate its `[alembic]` section.  It should look like this currently:

```ini

[alembic]

script_location = rattail.db:alembic

version_locations = rattail.db:alembic/versions

```

But you should update that last line so it looks like this:

```ini

[alembic]

script_location = rattail.db:alembic

version_locations = rattail_corepos.db:alembic/versions rattail.db:alembic/versions

```

Then run the migration tool to apply changes to your Messkit DB:

```sh

cd /path/to/envs/messkit

bin/alembic -c app/rattail.conf upgrade heads

```

So the DB tables exist now, but Messkit also needs to know those tables should be considered part of the app so to speak.  Edit `/path/to/envs/messkit/app/poser/poser/db/model/__init__.py` and add the line:

```python

from rattail_corepos.db.model import *

```

That's all for setup, now you can test the import.  This command only imports the Department (`departments`) table from CORE (the "default" DB of "office_op" type), into corresponding Messkit table.  But it uses the `--dry-run` flag which means the DB transaction is rolled back.  Omit that flag if you really want the data in Messkit.

```sh

cd /path/to/envs/messkit

bin/rattail -c app/quiet.conf import-corepos-db Department --progress --dry-run

```

You now can enable/include the customized "rattail" views provided by `tailbone_corepos` if you like.