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

TBD.

TBD.

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.