Database Schema

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

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

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

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

The selectors table has the following columns.

id

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

selector

character varying(64)
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

TBD.

The siptextmessages Table

TBD.

The tokens Table

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.