Introduction

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:

Orders and saleslog

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

Contacts

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

Settings

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

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:

1 select * from tm.pricetype

To retrieve a list of the last 10 payments:

1 select 
2     id, 
3     paidts, 
4     orderid, 
5     amount 
6 from tm.payment 
7 order by id desc 
8 limit 10

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

1 select 
2     tm.order.id, 
3     contact.firstname, 
4     contact.lastname
5 from tm.order
6 left join tm.contact on tm.order.contactid=contact.id
7 order by tm.order.id desc
8 limit 10

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

1 select 
2     date_trunc('month',tm.order.createdts),
3     count(*) 
4 from tm.order 
5 where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01'
6 group by date_trunc('month',tm.order.createdts)
7 order by date_trunc('month',tm.order.createdts)

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

1 select 
2     id,
3     firstname,
4     lastname 
5 from tm.contact
6 where id in (
7     select distinct contactid from tm.order where tm.order.createdts>='2014-01-01' and tm.order.createdts<'2015-01-01'
8 )
9 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:

1 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.