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