Database Schema

Database Schema 2 (since 4.0)

Tables

The sipis database consists of the following tables.

badge

This table contains information necessary for calculating correct badge number for push notifications.

data

This table contains information about user SIP accounts. SIPIS consults this table whenever it needs to create a new SIP client instance.

pushtests

This table contains information about pending and completed push tests.

restart

This table contains state information about all instances currently running on a given SIPIS server. When SIPIS crashes or is restarted it uses this table to recreate all the SIP client instances that were running on it prior to the restart.

selectors

This table holds information about the time when a given selector was last seen by a sipis server.

sipcalls

This table contains information about missed and pending incoming calls that are still waiting to be reported to users.

siptextmessages

This table contains information about incoming text messages that are still waiting to be delivered to users.

tableversions

This table holds versioning information of the other tables in the database.

tokens

This table contains information used to associate selectors with device tokens used for push notifications.

The data Table (v2)

Changelog

v2 - Replaced id column with selector column.

The data table has the following columns.

rowid

bigint not null
Auto-incrementing row identifier.

selector

character varying(64) not null
Instance selector string.

name

character varying(64) not null
The first part of name and value pairs that store information about user SIP accounts.

value

character varying(255)
The second part of name and value pairs that store information about user SIP accounts.

longvalue

text
Stores value longer that 255 characters.

The pushtests Table (v2)

Changelog

v2 - Replaced id column with selector column. Added rowid column.

The pushtests table has the following colums.

rowid

bigint not null
Auto-incrementing row identifier.

selector

character varying(64) not null
Instance selector string.

pushid

character varying(64) not null
ID of the push test.

platform

character varying(255) not null
Platform of the device which initiated the push test.

started

timestamp without time zone not null
Time when the test push was sent to the device.

concluded

timestamp without time zone
Time of the reply from the device confirming that the push test was successful.

The restart Table (v2)

Changelog

v2 - Replaced id column with selector column. Removed server column in favor of a new entry named "server". The restart_replace_rule is no longer necessary because sipis uses the new "upsert" functionality in PostgreSQL since version 9.5.

The restart table has the following columns.

rowid

bigint not null
Auto-incrementing row identifier.

selector

character varying(64) not null
Instance selector string.

name

character varying(64) not null
The first part of name and value pairs that store information about user SIP accouts.

seq

integer default 0 not null
Used to store arrays of values as in "name[seq]: value".

value

character varying(255)
The second part of name and value pairs that store information about user SIP accounts.

longvalue

text
Stores value longer that 255 characters.

The selectors Table (v1)

Changelog

There were no changes in the selectors table.

The selectors table has the following columns.

id

bigint not null
Integer identifier assigned to the associated instance selector.

selector

character varying(64) not null
Instance selector string.

lastseen

timestamp with time zone not null default now()
Timestamp of the last time any SIPIS server saw the associated instance selector.

The sipcalls Table (v2)

Changelog

v2 - Replaced id column with selector column. Added missedreason column.

The sipcalls table has the following columns.

rowid

bigint not null
Auto-incrementing row identifier.

selector

character varying(64) not null
Instance selector string.

scheme

character varying(6) not null
Scheme part of the caller URI.

userdisplayname

character varying(255) not null
Display name part of the caller URI.

username

character varying(255) not null
User name part of the caller URI.

domain

character varying(255) not null
Domain part of the caller URI.

domainport

integer not null
Domain port part of the caller URI.

callid

character varying(255) not null
SIP Call-ID header of the incoming INVITE request.

time

timestamp with time zone not null
Timestamp of arrival of the incoming INVITE request.

missedreason

character varying(64) not null
Missed reason of the incoming call. Possible values are Missed, AnsweredElsewhere and RejectedByDnd.

The siptextmessages Table (v2)

Changelog

v2 - Replaced id column with selector column.

The siptextmessages table has the following columns.

rowid

bigint not null
Auto-incrementing row identifier.

selector

character varying(64) not null
Instance selector string.

scheme

character varying(6) not null
Scheme part of the sender URI.

userdisplayname

character varying(255) not null
Display name part of the sender URI.

username

character varying(255) not null
User name part of the sender URI.

domain

character varying(255) not null
Domain part of the sender URI.

domainport

integer not null
Domain port part of the sender URI.

msgid

character varying(255) not null
SIP Call-ID header of the incoming MESSAGE request.

contenttype

character varying(255) not null
SIP Content-Type header of the incoming MESSAGE request.

headers

text not null
SIP headers of the incoming MESSAGE request in XML format.

body

text not null
Encrypted body of the incoming MESSAGE request.

time

timestamp with time zone not null
Timestamp of arrival of the incoming MESSAGE request.

The tokens Table (v2)

Changelog

v2 - Replaced id column with selector column. The tokens_replace_rule is no longer necessary because sipis uses the new "upsert" functionality in PostgreSQL since version 9.5.

The tokens table has the following columns.

rowid

bigint not null
Auto-incrementing row identifier.

selector

character varying(64) not null
Instance selector string.

token

text not null
Base64-encoded device token used for push notifications.

appid

character varying(200) not null
Identifier of the application that handles the associated instance selector.

lastseen

timestamp with time zone
Timestamp of the last time any SIPIS server saw the associated device token.

Database migration

This section assumes that you are hosting your own sipis server and you have recently updated from version 3.x to version 4.x.

Sipis version 4.x will work well with the older database scheme, however migrating to the newer scheme will enable sipis to report proper missed call reason to the device, improving call history records.

To migrate to the new database scheme, follow these steps

Create a new empty database

# su postgres
$ psql -c "CREATE DATABASE sipis2 OWNER sipis TEMPLATE template0 ENCODING 'SQL_ASCII';"
$ exit

Stop sipis service

# systemctl stop sipis

Update sipis settings file, keeping the old one around for later reference

Note

It's important that the old settings file DOES NOT end with .xml extension.

# cp /etc/sipis/Settings.xml /etc/sipis/Settings.xml.old

In /etc/sipis/Settings.xml, update the database name in the Sipis/Database[OpenString] attribute, e.g: replace

<Database OpenString="host=localhost port=5432 dbname=sipis user=sipis password=*****" />

with (notice dbname=sipis2 instead of dbname=sipis)

<Database OpenString="host=localhost port=5432 dbname=sipis2 user=sipis password=*****" />

Invoke CreateSipisDb command to migrate the database

We pass both, the new and the old settings file as arguments. This instructs the CreateSipisDb command to migrate data from the old database to the newly created one.

# CreateSipisDb /etc/sipis/Settings.xml /etc/sipis/Settings.xml.old

Start sipis service

If everything went smoothly, then the migration is done at this point and you can start your sipis server again.

# systemctl start sipis

Cleanup

Optionally, you can delete the old database and the old settings file /etc/sipis/Settings.xml.old

Database Schema 1 (until 3.59)

Tables

The sipis database consists of the following tables.

data

This table contains information about user SIP accounts. SIPIS consults this table whenever it needs to create a new SIP client instance.

pushtests

This table contains information about pending and completed push tests.

restart

This table contains state information about all instances currently running on a given SIPIS server. When SIPIS crashes or is restarted it uses this table to recreate all the SIP client instances that were running on it prior to the restart.

selectors

This table contains mapping from selector strings to integer identifiers used in other tables.

sipcalls

This table contains information about missed and pending incoming calls that are still waiting to be reported to users.

siptextmessages

This table contains information about incoming text messages that are still waiting to be delivered to users.

tokens

This table contains information used to associate selectors with device tokens used for push notifications.

The data Table (v1)

The data table has the following columns.

rowid

integer not null
Auto-incrementing row identifier.

id

Foreign key to the selectors table.

name

character varying(64)
The first part of name and value pairs that store information about user SIP accounts.

value

character varying(255)
The second part of name and value pairs that store information about user SIP accounts.

longvalue

text
Stores value longer that 255 characters.

The pushtests Table (v1)

The pushtests table has the following colums.

id

Reference to the selectors table. This is not a foreign key since it is desired to keep push test results available even after instance gets turned off.

pushid

character varying(64) not null
ID of the push test.

platform

character varying(255) not null
Platform of the device to which the test push was sent.

started

timestamp without time zone not null
Time when the test push was sent to the device.

concluded

timestamp without time zone
Time of the reply from the device confirming that the push test was successful.

The restart Table (v1)

The restart table has the following columns.

rowid

integer not null
Auto-incrementing row identifier.

id

integer not null
Foreign key to the selectors table.

name

character varying(64) not null
The first part of name and value pairs that store information about user SIP accouts.

value

character varying(255)
The second part of name and value pairs that store information about user SIP accounts.

seq

integer default 0 not null
TBD.

server

character varying(100)
Name of the SIPIS server that owns the given entry.

longvalue

text
Stores value longer that 255 characters.

The selectors Table (v1)

The selectors table has the following columns.

id

integer not null
Integer identifier assigned to the associated instance selector.

selector

character varying(64) not null
Instance selector string.

lastseen

timestamp with time zone default now() not null
Timestamp of the last time any SIPIS server saw the associated instance selector.

The sipcalls Table (v1)

TBD.

The siptextmessages Table (v1)

TBD.

The tokens Table (v1)

The tokens table has the following columns.

rowid

integer not null
Auto-incrementing row identifier.

id

integer not null
Foreign key to the selectors table.

token

text not null
Base64-encoded device token used for push notifications.

appid

character varying(200) not null
Identifier of the application that handles the associated instance selector.

lastseen

timestamp with time zone
Timestamp of the last time any SIPIS server saw the associated device token.