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
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:
This part contains information on the contacts. The most important views are:
This part contains information on the settings. Some important views are:
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 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.
Consult the reference for each table for more detailed info on available fields and foreign keys.