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:

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

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:

1select * from tm.pricetype

To retrieve a list of the last 10 payments:

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

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

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

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

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

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

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

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