Database Interaction

Let’s make our extension just a bit more user friendly. Right now, when a user chooses to convert their transactions to another currency, that choice is lost when they navigate away from the page. How might we persist it?

We have one option already. We could store it in the cache. However, that’s not a long term solution. Anything more than a few hours and it may expire. Let’s use the Q2 database itself.

Overview

We’ve already used the database indirectly, both by calling HQ’s GetAccountHistoryById method and by storing our extension’s configuration with our WEDGE_ADDRESS_CONFIGS. However, storing choices on a per user basis will require a slightly different approach. Enter Database Objects, which serve as a programmatic interface to the database in all sorts of convenient ways. While not a full ORM, DbObjects give read/write access to dozens of database tables throughout the Q2 system, and in fact, are used behind the scenes in many of the command line calls you’ve already made, such as q2 install and q2 add_to_nav.

In this case, we are interested in storing data at a User level. Let’s check out the available DbObject List and see if one fits our needs. Looks like UserData is exactly what we need! In the Q2 Database, there’s a table called Q2_UserData. It has a column for UserId, a column for Name (called ShortName), and a column for Value (called GTDataValue). If we were writing raw sql, we could access this with something like:

SELECT *
FROM dbo.Q2_UserData ud
INNER JOIN dbo.Q2_UserDataElement ude on ude.DataID = ud.DataID
WHERE ud.UserID=1
    AND  ude.ShortName='MyAwesomeKey';

However, your extension won’t have database credentials (in the datacenter), and writing raw SQL can be tedious and dangerous. Also, as the database schema updates over time, your raw sql strings would have to adapt. For all these reasons, we prefer using DbObjects for this type of operation. In fact, this is so common, we give easy access to all DbObjects using self.db from your extension.

The same code in the SDK might look something like this:

async def default(self):
    db_response = await self.db.user_data.get(1, 'MyAwesomeKey')
    # db_response == List[UserDataRow], each of which have a .GTDataValue

It’s also possible to invoke the same query from the CLI, which can be useful for quick queries without opening up a SQL editor.

$ q2 db get_user_data 1 MyAwesomeKey

The CLI and the python variant both call the same underlying python code. The only difference is the CLI version prints out the response as columns optimized to view in a terminal.

Note

It’s possible to use this in a bash script, pipe it to other endpoints, and more. Use your imagination!

Armed with all this knowledge, let’s solve the original problem: persisting the user’s currency choice.

DbObjects From Python

First, add some getter and setter methods for the user’s currency_preference:

async def _set_currency_preference(self, currency):
    await self.db.user_data.create(self.online_user.user_id, 'currency_preference', currency)

async def _get_currency_preference(self):
    db_response = await self.db.user_data.get(self.online_user.user_id, 'currency_preference')
    selected_currency = 'USD'
    if db_response:
        selected_currency = db_response[0].GTDataValue
    return selected_currency

Note

The above method names start with _. While not necessary, this is a python convention that these are not meant to be called outside the class.

A new route:

@ajax
async def set_preferences(self):
    await self._set_currency_preference(self.form_fields['currency'])
    return Success()

Register the route:

@property
def router(self):
    router = super().router
    router.update({
        'default': self.default,
        'submit': self.submit,
        'get_currency_data': self.get_currency_data,
        'set_preferences': self.set_preferences,
    })

    return router

And alter the beginning of your submit method:

async def submit(self):
    rates_dictionary = await self.get_currency_rates()

    sorted_rates = sorted(rates_dictionary.keys())
    current_account_id = self.form_fields['account_id']
    if 'currency' in self.form_fields:
        selected_currency = self.form_fields['currency']
    else:
        selected_currency = await self._get_currency_preference()

    params_obj = GetAccountHistoryById.ParamsObj(
        self.logger,
        current_account_id,
        '',
        hq_credentials=self.hq_credentials
    )

    hq_response = await GetAccountHistoryById.execute(params_obj)
    ...

Finally, let’s update the transaction_history.html.jinja2 file to add a user interface way to call the set_preferences route:

<!-- Add in a new Save Preferences button -->
...
<q2-btn id="getCurrencies" intent="workflow-secondary">Get Currency Data</q2-btn>
<q2-btn id="savePreferences" intent="workflow-secondary">Save Preferences</q2-btn>
<input id="hidden_account_id_storage" name="account_id" value="{{current_account_id}}" type="hidden">
...
<!-- And the script to handle it -->
    <script>
        ...
            const preferencesButton = document.getElementById('savePreferences')
            preferencesButton.onclick = setPreferences

            function setPreferences(event) {
                tecton.sources.requestExtensionData({
                    route: 'set_preferences',
                    body: {
                        'currency': document.querySelector('q2-select').value
                    }
                })
                    .then(
                        () => {
                            tecton.actions.showModal({
                                title: 'Confirmation',
                                message: 'Preferences Saved',
                                modalType: 'info',
                            })
                        })
                    .catch( error => {
                        tecton.actions.showModal({
                            title: 'Error Saving Preferences',
                            message: `We ran into this error while trying to save preferences: ${error.message}`,
                            modalType: 'error'
                        })
                    } )
            }
            function fetchCurrencies(event) {
                ...
    </script>
</div>

With a few targeted updates, we now have a way to store user preferences in the database! Navigate to that page, choose a non USD currency and click ‘Save Preferences’. Navigate away and back and verify it worked.

DbObjects From CLI

Great work! We added some data to the database and that supported the new feature addition on the frontend. Let’s see what actually got stored in the database. We could use SQL, of course, but let’s see what we get from the CLI.

All the CLI commands for interacting with the Database Objects start with q2 db. There’s a lot of them! Check it out:

q2 db -h
usage: q2 db [-h] [-l {DEBUG,INFO,WARNING,ERROR,CRITICAL}] [--completion]
             {get_access_code_targets,add_access_code_targets,get_account_address,get_address,get_admin_user_property_data,add_admin_user_property_data,update
_admin_user_property_data,update_admin_user_property_data_by_id,remove_admin_user_property_data,get_admin_user_property_data_by_value,get_admin_user_property_
data_elements...

Too many to list here! Probably too many to actually see what is relevant. A convenient way to interact with this massive list is taking advantage of our tab completion engine. You can invoke this with q2 db <tab><tab>. The completion engine is smart enough to take your current typing into account. If you already have q2 db get_ at the CLI when you press <tab><tab>, you will only get commands that start with get_. The SDK supports both bash and zsh shell completions.

Note

If you are having trouble getting the completion to initiate, it’s possible you need to reinstall it. q2 install_completion should get you up and running.

Back to the problem at hand. We want to verify our UserData made it into the database. Let’s start with q2 db get_user<tab><tab>. That shows a manageable number of commands to scan through; one of which is get_user_data. Let’s dig in to see more of how it works:

$ q2 db get_user_data -h
usage: q2 db get_user_data [-h] [--no-trunc] user_id short_name

positional arguments:
  user_id     Q2_User.UserID
  short_name  Q2_UserDataElement.ShortName

optional arguments:
  -h, --help  show this help message and exit
  --no-trunc  Do not truncate GTDataValue

Every one of these commands take a -h (or a --help) flag which gives more detailed information about arguments and usage. In this case we need a user_id and short_name parameter. short_name is easy. That’s currency_preference as defined in our python code. But what about user_id? Maybe another DbObject can help us! q2 db get_user<tab><tab> shows a potentially useful get_users:

$ q2 db get_users -h
usage: q2 db get_users [-h] [--extended] [--combine] [-u USER_ID | -l LOGIN_NAME] [-s SOCIAL_SECURITY] [-p PRIMARY_CIF]

optional arguments:
  -h, --help            show this help message and exit
  --extended            Extends the output of rows
  --combine             Search by both ssn and cif
  -u USER_ID, --user-id USER_ID
                        Q2_User.UserID
  -l LOGIN_NAME, --login-name LOGIN_NAME
                        Q2_UserLogin.LoginName
  -s SOCIAL_SECURITY, --social-security SOCIAL_SECURITY
                        Q2_User.SSN
  -p PRIMARY_CIF, --primary-cif PRIMARY_CIF
                        Q2_User.PrimaryCIF

Get user by login_name. Great! We know that. It’s what we used to log in to online banking. For this demo, that’s retail0. It may differ in your environment:

$ q2 db get_users -l retail0
UserID  CustomerID  FirstName  LastName  SSN        PrimaryCIF
2       2           SDK        user      874026198  07212010

Wonderful, UserID is 2. Let’s run our get_user_data query now appropriately:

$ q2 db get_user_data 2 currency_preference
DataID  ShortName            UserID  GTDataValue  FileID  CreateDate                    DisplayName
9       currency_preference  2       INR          <null>  2021-10-25T15:48:23.03-05:00  <null>

And there we go! INR is in the currency this demo chose on the front end, and here it is stored in the database.

Databases Objects are almost always preferred to writing your own SQL, and they are supremely easy to work with. Not only that, they are easy to write for the Q2 team! If you have a new piece of functionality that doesn’t quite fit the existing model, please reach out through the Q2Developer.com portal and we’ll be happy to work with you. Often these requests lead to new and better DbObjects.

Almost done now! Let’s take a final look at what we’ve accomplished in the next step.