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.

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.

Database Schema¶

Database Schema 2 (since 4.0)¶

Tables ¶

The `data` Table (v2)¶

The `pushtests` Table (v2)¶

The `restart` Table (v2)¶

The `selectors` Table (v1)¶

The `sipcalls` Table (v2)¶

The `siptextmessages` Table (v2)¶

The `tokens` Table (v2)¶

Database migration ¶

Database Schema 1 (until 3.59)¶

Tables ¶

The `data` Table (v1)¶

The `pushtests` Table (v1)¶

The `restart` Table (v1)¶

The `selectors` Table (v1)¶

The `sipcalls` Table (v1)¶

The `siptextmessages` Table (v1)¶

The `tokens` Table (v1)¶