You can use the pattoo API to retrieve data using a GraphQL interface. It's best to become familiar with GraphQL before reading further.
After completing this tutorial you'll be able to do programmatic GraphQL queries.
By default the pattoo server will run on port TCP 20202.
Interactive GraphQL allows you to test your queries using your web browser.
If you are running it on your local machine go to the http://localhost:20202/pattoo/api/v1/web/igraphql to see the interactive query tool.
If you want to access GraphQL programmatically, without using your browser then you'll need to access the non-interactive GraphQL URL.
If you are running it on your local machine go to the http://localhost:20202/pattoo/api/v1/web/graphql URL to get your results.
You can use the get function in this file to get GraphQL data from the pattoo API server. https://github.com/PalisadoesFoundation/pattoo-web/blob/master/pattoo_web/phttp.py
Note This section is very detailed, but it will help you with understanding how the GraphQL keywords required for your queries were created.
There are two important files in the repository's pattoo/db directory.
- models.py: Defines the database structure using the python SQLAlchemy package
- schema.py: Maps the database structure from SQLAlchemy to GraphQL queries using the graphene-sqlalchemy package.
This file defines the tables and columns in the database.
- Each class defines a table
- Each variable in the class defines the columns. The variable name is the column name
The python graphene-sqlalchemy package used to present GraphQL will convert column names into camelCase, removing any underscores. Therefore a column named idx_datapoint will be idxDatapoint in your GraphQL queries.
You will notice some tables will have foreign keys as part of the RDBMS structure. Here is an example in the AgentXlate table:
class AgentXlate(BASE):
"""Class defining the pt_agent_xlate table of the database."""
__tablename__ = 'pt_agent_xlate'
__table_args__ = (
UniqueConstraint('idx_language', 'agent_program'),
{'mysql_engine': 'InnoDB'}
)
idx_agent_xlate = Column(
BIGINT(unsigned=True), primary_key=True,
autoincrement=True, nullable=False)
idx_language = Column(
BIGINT(unsigned=True),
ForeignKey('pt_language.idx_language'),
index=True, nullable=False, server_default='1')
You will also notice that this class also has a backref relationship near the bottom. This is what graphene-sqlalchemy uses to track the relationships for queries. In this case, the backref has the name Agent_xlate_language which will be converted to agentXlateLanguage camelCase in your GraphQL queries
language = relationship(
Language,
backref=backref(
'Agent_xlate_language', uselist=True,cascade='delete,all'))
This file contains the mappings from SQLAlchemy table definitions to GraphQL queries.
- Database tables defined as SQLAlchemy classes in models.py are imported as Model classes in this file.
- You’ll notice that if you manually type in your GraphQL queries in the /igraphql URL that you’ll see lists of each available table column with explanations. These explanations are defined in the Attribute classes in this file.
- Attributes and models are tied together in the SQLAlchemyObjectType classes.
from pattoo.db.models import AgentXlate as AgentXlateModel
class InstrumentedQuery(SQLAlchemyConnectionField):
"""Class to allow GraphQL filtering by SQlAlchemycolumn name."""
def __init__(self, type_, **kwargs):
...
...
...
class AgentXlateAttribute():
"""Descriptive attributes of the AgentXlate table.
A generic class to mutualize description of attributes for both queries
and mutations.
"""
idx_agent_xlate = graphene.String(
description='AgentXlate table index.')
idx_language = graphene.String(
description='Language table index (ForeignKey).')
agent_program = graphene.String(
resolver=resolve_agent_program,
description=('Agent progam'))
translation = graphene.String(
resolver=resolve_translation,
description='Translation of the agent program name.')
enabled = graphene.String(
description='"True" if enabled.')
class AgentXlate(SQLAlchemyObjectType, AgentXlateAttribute):
"""AgentXlate node."""
class Meta:
"""Define the metadata."""
model = AgentXlateModel
interfaces = (graphene.relay.Node,)
Next we'll discuss the Query class you'll find further down the file. This class:
- Uses the InstrumentedQuery class to filter queries by database column values. This InstrumentedQuery class makes things a lot easier. The graphene-sqlalchemy implementation of GraphQL has limited filtering capabilities. For example:
- Every row of every database table has a fixed unique automatically generated GraphQL ID which is a graphene.relay.node.GlobalID object. You can filter specifically on this ID.
- You also get lists of database row results containing the first X and last X rows.
- Lists of database row results can also be obtained for values before and/or after X GraphQL ID values retrieved from a database table.
- Custom filtering for specific values in a database column can be using resolvers, but you have to manually create a resolver for each table’s column. This per query customization is not ideal.
- Has Node entries for single value GraphQL queries, or as a definition inside an "edges" section of a GraphQL query. You can filter Nodes by the GraphQL graphene.relay.node.GlobalID too. This will be shown later.
class Query(graphene.ObjectType):
"""Define GraphQL queries."""
node = relay.Node.Field()
# Results as a single entry filtered by 'id' and as a list
agent_xlate = graphene.relay.Node.Field(AgentXlate)
all_agent_xlate = InstrumentedQuery(AgentXlate)
Here are some query examples using the example database table we have been using. Run these queries in the /igraphql url.
Note:
- In all the examples in this section the “id” represents the graphene.relay.node.GlobalID string. You can use this to get information on a specific row of a specific table.
- The InstrumentedQuery related queries in the Query class can only filter on a database table value, not the graphene.relay.node.GlobalID string.
This section covers Agent table queries.
This will provide information on all the known polling agents.
The agentProgram value will be used later for getting a translation into a meaningful name.
{
allAgent {
edges {
node {
id
idxAgent
agentPolledTarget
agentProgram
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
You’ll notice that this query also gives you the following information that will be required for translations later on: #. key-value pair key value for translating Datapoint metadata #. agentProgram for translating the program name into something meaningful #. idxPairXlateGroup for translating the key values
{
agent(id: "QWdlbnQ6Mg==") {
datapointAgent {
edges {
cursor
node {
id
idxDatapoint
idxAgent
agent {
agentProgram
agentPolledTarget
idxPairXlateGroup
pairXlateGroup {
id
}
}
glueDatapoint {
edges {
node {
pair {
key
value
}
}
}
}
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
}
This query will show:
- All Datapoints for an Agent
- The charts to which each datapoint belongs
- The favorites to which the charts belong
{
agent(id: "QWdlbnQ6MQ==") {
datapointAgent {
edges {
cursor
node {
id
idxDatapoint
idxAgent
chartDatapointDatapoint {
edges {
node {
idxChartDatapoint
chart {
id
idxChart
name
checksum
favoriteChart {
edges {
node {
idxFavorite
}
}
}
}
}
}
}
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
}
Here we have some representative queries you can do:
To see all DataPoints and their data enter this query on the left hand side of the viewer.
{
allDatapoints {
edges {
node {
id
idxDatapoint
checksum
dataType
lastTimestamp
pollingInterval
enabled
}
}
}
}
Here is the result of all DataPoints. Take note of (id: "RGF0YVBvaW50OjE=") as we'll use it for querying timeseries data.
{
"data": {
"allDatapoints": {
"edges": [
{
"node": {
"id": "RGF0YVBvaW50OjE=",
"idxDatapoint": "1",
"checksum": "ea5ee349b38fa7dc195b3689872c8487e7696201407ef27231b19be837fbc6da0847f5227f1813d893100802c70ffb18646e2097a848db0b7ea4ec15caced101",
"dataType": 99,
"lastTimestamp": 1575174588079,
"pollingInterval": 10000,
"enabled": "1"
}
},
{
"node": {
"id": "RGF0YVBvaW50OjI=",
"idxDatapoint": "2",
"checksum": "2b15d147330183c49a1672790bf09f54f8e849f9391c82385fd8758204e87940ab1ffef1bb67ac725de7cc0aa6aba9b6baeff34497ee494c38bee7f24eef65df",
"dataType": 99,
"lastTimestamp": 1575174588084,
"pollingInterval": 10000,
"enabled": "1"
}
}
]
}
}
}Here we have some representative queries you can do:
To see all Key-Pair-Values enter this query on the left hand side of the viewer.
{
allPairs {
edges {
node {
id
idxPair
key
value
}
}
}
}
Here is the result of all Key-Pair-Values.
{
"data": {
"allPairs": {
"edges": [
{
"node": {
"id": "UGFpcjox",
"idxPair": "1",
"key": "pattoo_agent_hostname",
"value": "palisadoes"
}
},
{
"node": {
"id": "UGFpcjoy",
"idxPair": "2",
"key": "pattoo_agent_id",
"value": "23a224313e4aaa4678a81638025ab02b42cb8a5b7c47b3dd2efced06d1a13d39"
}
},
{
"node": {
"id": "UGFpcjoz",
"idxPair": "3",
"key": "pattoo_agent_polled_device",
"value": "device.example.com"
}
},
{
"node": {
"id": "UGFpcjo0",
"idxPair": "4",
"key": "pattoo_agent_program",
"value": "pattoo_agent_modbustcpd"
}
}
]
}
}
}Here we have some representative queries you can do:
To see all GluePoints enter this query on the left hand side of the viewer. This table maps all the key-value pairs associated with an individual DataPoint
{
allGlues {
edges {
node {
id
idxPair
idxDatapoint
}
}
}
}
{
"data": {
"allGlues": {
"edges": [
{
"node": {
"id": "R2x1ZTooMSwgMSk=",
"idxPair": "1",
"idxDatapoint": "1"
}
},
{
"node": {
"id": "R2x1ZTooMSwgMik=",
"idxPair": "1",
"idxDatapoint": "2"
}
},
{
"node": {
"id": "R2x1ZTooMSwgMyk=",
"idxPair": "1",
"idxDatapoint": "3"
}
},
{
"node": {
"id": "R2x1ZTooMSwgNCk=",
"idxPair": "1",
"idxDatapoint": "4"
}
}
]
}
}
}Here we have some representative queries you can do:
To see all numeric data for a specific datapoint (id: "RGF0YVBvaW50OjE="), enter this query on the left hand side of the viewer.
{
datapoint(id: "RGF0YVBvaW50OjE=") {
id
idxDatapoint
checksum
dataType
pollingInterval
dataChecksum {
edges {
node {
id
timestamp
value
}
}
}
}
}
Here is all the timeseries data from (id: "RGF0YVBvaW50OjE=").
{
"data": {
"datapoint": {
"id": "RGF0YVBvaW50OjE=",
"idxDatapoint": "1",
"checksum": "ea5ee349b38fa7dc195b3689872c8487e7696201407ef27231b19be837fbc6da0847f5227f1813d893100802c70ffb18646e2097a848db0b7ea4ec15caced101",
"dataType": 99,
"pollingInterval": 10000,
"dataChecksum": {
"edges": [
{
"node": {
"id": "RGF0YTooMSwgMTU3NTE3MjgzNTAyOCk=",
"timestamp": "1575172835028",
"value": "738.0000000000"
}
},
{
"node": {
"id": "RGF0YTooMSwgMTU3NTE3Mjg0NTIxOSk=",
"timestamp": "1575172845219",
"value": "738.0000000000"
}
},
{
"node": {
"id": "RGF0YTooMSwgMTU3NTE3Mjg1NTM2NCk=",
"timestamp": "1575172855364",
"value": "738.0000000000"
}
}
]
}
}
}
}This query provides all the configured languages. The code returned is the language code. In the results, a code of en is english. Make translation queries based on this code value.
{
allLanguage {
edges {
node {
id
idxLanguage
code
name
}
}
}
}
This section outlines how to view Agent translation data.
You can use this query to get the translation for an agentProgram name for a specific language.This is useful for the home page.
{
allAgentXlate {
edges {
node {
id
idxAgentXlate
idxLanguage
agentProgram
translation
enabled
tsCreated
tsModified
language {
id
name
code
idxLanguage
}
}
}
}
}
In this case we get translations for the agentProgram named pattoo_agent_snmp_ifmibd.
{
allAgentXlate(agentProgram: "pattoo_agent_snmp_ifmibd") {
edges {
node {
id
idxAgentXlate
idxLanguage
agentProgram
translation
enabled
tsCreated
tsModified
}
}
}
}
In this case:
- The ID is a graphene.relay.node.GlobalID string.
- The translation for the agentProgram is in the “translation” field.
{
agentXlate(id: "QWdlbnRYbGF0ZToy") {
id
idxAgentXlate
idxLanguage
agentProgram
translation
enabled
tsCreated
tsModified
}
}
There are some things to note:
- This will provide a list of translations for all configured languages. The translation for the agentProgram is in the “translation” field.
- Normally you’d be able to filter by “id” with GraphQL. Unfortunately this capability was lost when we added the customized ability to filter by any database table column. Hopefully the Python Graphene (GraphQL) team will be able to fix this later as part of their standard build.
{
allAgentXlate(idxAgentXlate: "4") {
edges {
node {
id
idxAgentXlate
idxLanguage
agentProgram
translation
enabled
tsCreated
tsModified
language {
id
name
}
}
}
}
}
This section outlines how to view key-pair translation data.
Here's the query you'll need to view all translations:
{
allPairXlate {
edges {
node {
id
idxLanguage
idxPairXlate
idxPairXlateGroup
key
translation
}
}
}
}
In this example, we filter by idxPairXlateGroup
{
allPairXlate (idxPairXlateGroup: "2"){
edges {
node {
id
idxLanguage
idxPairXlate
idxPairXlateGroup
key
translation
}
}
}
}
This section outlines how to view favorites data.
This is the query string you'll need to see all the favorites in the database.
{
allFavorite {
edges {
node {
id
idxFavorite
order
user {
id
idxUser
username
firstName
lastName
}
chart {
name
chartDatapointChart {
edges {
node {
idxDatapoint
}
}
}
}
}
}
}
}
This section outlines how to view favorites data.
This query will show:
- All users
- Their favorites
- The charts associated with each favorite
{
allUser {
edges {
node {
id
username
firstName
lastName
enabled
favoriteUser {
edges {
node {
order
chart {
id
idxChart
name
}
}
}
}
}
}
}
}
This query will show:
- The filtered username (“pattoo”)
- Its favorites
- The charts associated with each favorite
{
allUser(username: "pattoo") {
edges {
node {
id
username
favoriteUser {
edges {
node {
order
chart {
id
idxChart
name
}
}
}
}
}
}
}
}
This query will show:
- The user
- Its favorites
- The charts associated with each favorite
{
user(id: "VXNlcjox") {
id
username
favoriteUser {
edges {
node {
order
chart {
id
idxChart
name
}
}
}
}
}
}
This is a custom query that requires you enter a username and password. Regular query results are returned when found, a Null result is returned upon failure.
{
authenticate(username: "[email protected]", password: "123456") {
id
}
}
Results are returned when found.
{
"data": {
"authenticate": [
{
"id": "VXNlcjo3"
}
]
}
}
A Null result is returned when not found.
{
"data": {
"authenticate": null
}
}
This section outlines how to do simple pagination
This query will return all Datapoint values.
{
allDatapoints {
edges {
node {
idxDatapoint
idxAgent
id
tsCreated
tsModified
}
}
}
}
It’s important to note the startCursor and endCursor values when wanting to paginate. They are useful in subsequent queries where you may want ranges of values that are not relative to the very start and very end of database table rows.
{
allDatapoints(first: x) {
edges {
node {
idxDatapoint
idxAgent
id
tsCreated
tsModified
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
It’s important to note the startCursor and endCursor values when wanting to paginate. They are useful in subsequent queries where you may want ranges of values that are not relative to the very start and very end of database table rows.
{
allDatapoints(last: x) {
edges {
node {
idxDatapoint
idxAgent
id
tsCreated
tsModified
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
Note:
- It’s important to note the endCursor of the previous query.
- The next X results would need a query like the one below, starting at the endCursor value of the previous query.
{
allDatapoints(first: X, after: "END_CURSOR_VALUE") {
edges {
node {
idxDatapoint
idxAgent
id
tsCreated
tsModified
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
Note:
- It’s important to note the startCursor of the previous query.
- The previous X results would need a query like the one below, starting at the startCursor value of the previous query.
{
allDatapoints(last: X, before: "START_CURSOR_VALUE") {
edges {
node {
idxDatapoint
idxAgent
id
tsCreated
tsModified
}
}
pageInfo {
startCursor
endCursor
hasNextPage
hasPreviousPage
}
}
}
Mutation is the terminology that GraphQL uses for database updates. Here are some query examples using the example database table we have been using. Run these queries in the /igraphql url.
This section outlines how to mutate chart data.
This mutation will add the chart then return the resulting fields:
- id
- name
- Enabled status
mutation {
createChart(Input: {name: "Flying Fish"}) {
chart {
id
name
enabled
}
}
}
{
"data": {
"createChart": {
"chart": {
"id": "Q2hhcnQ6MjM5",
"name": "Flying Fish",
"enabled": "1"
}
}
}
}
This mutation will change the chart name from “Flying Fish” to “Teddy Bear”:
mutation {
updateChart(Input: {idxChart: "239", name: "Teddy Bear"}) {
chart {
id
name
enabled
}
}
}
{
"data": {
"updateChart": {
"chart": {
"id": "Q2hhcnQ6MjM5",
"name": "Teddy Bear",
"enabled": "1"
}
}
}
}
This section outlines how to mutate ChartDataPoint data.
This mutation will add a DataPoint to an existing chart then return the resulting fields:
mutation {
createChartDataPoint(Input: {idxDatapoint: "3", idxChart: "239"}) {
chartDatapoint {
id
idxChartDatapoint
idxDatapoint
idxChart
}
}
}
{
"data": {
"createChartDataPoint": {
"chartDatapoint": {
"id": "Q2hhcnREYXRhUG9pbnQ6MjQy",
"idxChartDatapoint": "242",
"idxDatapoint": "3",
"idxChart": "239"
}
}
}
}
This mutation will remove a DataPoint from the ChartDataPoint entry (Disable the entry for the chart):
mutation {
updateChartDataPoint(Input: {idxChartDatapoint: "242", enabled: "0"}) {
chartDatapoint {
id
idxChartDatapoint
idxDatapoint
idxChart
enabled
}
}
}
{
"data": {
"updateChartDataPoint": {
"chartDatapoint": {
"id": "Q2hhcnREYXRhUG9pbnQ6MjQy",
"idxChartDatapoint": "242",
"idxDatapoint": "3",
"idxChart": "239",
"enabled": "0"
}
}
}
}
This section outlines how to mutate user data.
This mutation will add a User then return the resulting fields:
mutation {
createUser(Input: {username: "[email protected]", firstName: "Foo", lastName: "Fighter", password: "123456"}) {
user {
id
idxUser
firstName
lastName
username
enabled
}
}
}
{
"data": {
"createUser": {
"user": {
"id": "VXNlcjoz",
"idxUser": "3",
"firstName": "Foo",
"lastName": "Fighter",
"username": "[email protected]",
"enabled": "1"
}
}
}
}
This mutation will remove a DataPoint from the ChartDataPoint entry (Disable the entry for the chart):
mutation {
updateUser(Input: {idxUser: "3", firstName: "Street"}) {
user {
idxUser
firstName
lastName
username
enabled
}
}
}
{
"data": {
"updateUser": {
"user": {
"idxUser": "3",
"firstName": "Street",
"lastName": "Fighter",
"username": "[email protected]",
"enabled": "1"
}
}
}
}
This section outlines how to mutate favorite data.
This mutation will add a Favorite then return the resulting fields:
mutation {
createFavorite(Input: {idxUser: "3", idxChart: "149", order: "2"}) {
favorite{
id
idxFavorite
idxChart
idxUser
enabled
}
}
}
{
"data": {
"createFavorite": {
"favorite": {
"id": "RmF2b3JpdGU6Mg==",
"idxFavorite": "2",
"idxChart": "149",
"idxUser": "3",
"enabled": "1"
}
}
}
}
This mutation will remove the Favorite entry (Disable the entry):
mutation {
updateFavorite(Input: {idxFavorite: "2", enabled: "0"}) {
favorite {
idxFavorite
idxChart
idxUser
enabled
}
}
}
{
"data": {
"updateFavorite": {
"favorite": {
"idxFavorite": "2",
"idxChart": "149",
"idxUser": "3",
"enabled": "0"
}
}
}
}