Introduction

11 min

All Ticketmatic data is stored in a Postgres relational database. The tables in this database are not directly accessible. Ticketmatic instead provides a set of publicly available views on the database, called the public data model. The public data model gives you fine grained, low level access to the data stored in Ticketmatic. Access is limited to read-only. For higher level access or read/write access use the Ticketmatic API.

In order to retrieve data using the public data model, you use SQL queries. The supported sql syntax strongly conforms to the ANSI-SQL:2008 standard.

Using the public data model you can:

create custom Filters

create custom Field definitions to be used in Views, Ticket layouts or Documents

create custom Reports

use the console to perform ad-hoc queries

perform ad-hoc queries through the API by using the tools/queries api endpoint

The views in the public data model are contained in the namespace tm, so you should use this as prefix to reference a view in the public data model, for example tm.pricetype.

Structure

The public data model is roughly structured in 4 parts:

events, tickets and prices

orders and saleslog

contacts

settings

Events, tickets and prices

This part contains the information on the defined events, tickets and pricing. The most important views are:

tm.event

tm.tickettype

tm.ticket

tm.pricetype

tm.pricelist

tm.tickettypeprice

Orders and saleslog

This part contains the information on actual orders and sold tickets. The most important views are:

tm.order

tm.orderlog

tm.payment

tm.ticket

tm.saleslog

tm.saleslogitem

tm.batchprint

Contacts

This part contains information on the contacts. The most important views are:

tm.contact

tm.contactaddress

Settings

This part contains information on the settings. Some important views are:

tm.paymentscenario

tm.saleschannel

Some examples

Below you will find some examples to get you started. Go to Settings -> Console to try out these examples for yourself.

To retrieve a list of all the Pricetypes available:

select * from tm.pricetype

To retrieve a list of the last 10 payments:

select 
    id, 
    paidts, 
    orderid, 
    amount 
from tm.payment 
order by id desc 
limit 10

You can join tables to combine info. For example to retrieve contact info for the last 10 orders:

select 
    tm.order.id, 
    contact.firstname, 
    contact.lastname
from tm.order
left join tm.contact on tm.order.contactid=contact.id
order by tm.order.id desc
limit 10

To make a summary for the number of orders per month for orders in 2014:

select 
    date_trunc('month',tm.order.createdts),
    count(*) 
from tm.order 
where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01'
group by date_trunc('month',tm.order.createdts)
order by date_trunc('month',tm.order.createdts)

To get a list of all contacts that created an order in 2014:

select 
    id,
    firstname,
    lastname 
from tm.contact
where id in (
    select distinct contactid from tm.order where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01'
)
order by lastname, firstname

Multilanguage fields

Multilanguage fields have a different value for each language. The name for an event is an example of a multilanguage field. In the public data model you will always retrieve the value of these fields for all active languages. As a convention, we use <fieldname><languagecode> as name for the language-specific value for the field.

In an account with languages en and nl active, you can try out:

select id, nameen, namenl from ev.event

This will return both the english and the dutch translation of the name field for an event.

More info

Consult the reference for each table for more detailed info on available fields and foreign keys.