Project

General

Profile

Actions
History

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:

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_arch
  • 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:

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

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

[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_arch]
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:

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

You should modify that to look like this:

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

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:

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

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.

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

[alembic]
script_location = rattail.db:alembic
version_locations = rattail.db:alembic/versions

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

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

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:

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.

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.

Updated by Lance Edgar 3 months ago ยท 2 revisions