Database Schema
===============
.. include:: ../../common.inc.rst
.. contents::
:local:
:depth: 3
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`` |br|
Auto-incrementing row identifier.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``name``
``character varying(64) not null`` |br|
The first part of ``name`` and ``value`` pairs that store information about user SIP accounts.
``value``
``character varying(255)`` |br|
The second part of ``name`` and ``value`` pairs that store information about user SIP accounts.
``longvalue``
``text`` |br|
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`` |br|
Auto-incrementing row identifier.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``pushid``
``character varying(64) not null`` |br|
ID of the push test.
``platform``
``character varying(255) not null`` |br|
Platform of the device which initiated the push test.
``started``
``timestamp without time zone not null`` |br|
Time when the test push was sent to the device.
``concluded``
``timestamp without time zone`` |br|
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`` |br|
Auto-incrementing row identifier.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``name``
``character varying(64) not null`` |br|
The first part of ``name`` and ``value`` pairs that store information about user SIP accouts.
``seq``
``integer default 0 not null`` |br|
Used to store arrays of values as in "name[seq]: value".
``value``
``character varying(255)`` |br|
The second part of ``name`` and ``value`` pairs that store information about user SIP accounts.
``longvalue``
``text`` |br|
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`` |br|
Integer identifier assigned to the associated instance selector.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``lastseen``
``timestamp with time zone not null default now()`` |br|
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`` |br|
Auto-incrementing row identifier.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``scheme``
``character varying(6) not null`` |br|
Scheme part of the caller URI.
``userdisplayname``
``character varying(255) not null`` |br|
Display name part of the caller URI.
``username``
``character varying(255) not null`` |br|
User name part of the caller URI.
``domain``
``character varying(255) not null`` |br|
Domain part of the caller URI.
``domainport``
``integer not null`` |br|
Domain port part of the caller URI.
``callid``
``character varying(255) not null`` |br|
SIP ``Call-ID`` header of the incoming ``INVITE`` request.
``time``
``timestamp with time zone not null`` |br|
Timestamp of arrival of the incoming ``INVITE`` request.
``missedreason``
``character varying(64) not null`` |br|
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`` |br|
Auto-incrementing row identifier.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``scheme``
``character varying(6) not null`` |br|
Scheme part of the sender URI.
``userdisplayname``
``character varying(255) not null`` |br|
Display name part of the sender URI.
``username``
``character varying(255) not null`` |br|
User name part of the sender URI.
``domain``
``character varying(255) not null`` |br|
Domain part of the sender URI.
``domainport``
``integer not null`` |br|
Domain port part of the sender URI.
``msgid``
``character varying(255) not null`` |br|
SIP ``Call-ID`` header of the incoming ``MESSAGE`` request.
``contenttype``
``character varying(255) not null`` |br|
SIP ``Content-Type`` header of the incoming ``MESSAGE`` request.
``headers``
``text not null`` |br|
SIP headers of the incoming ``MESSAGE`` request in XML format.
``body``
``text not null`` |br|
Encrypted body of the incoming ``MESSAGE`` request.
``time``
``timestamp with time zone not null`` |br|
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`` |br|
Auto-incrementing row identifier.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``token``
``text not null`` |br|
Base64-encoded device token used for push notifications.
``appid``
``character varying(200) not null`` |br|
Identifier of the application that handles
the associated instance selector.
``lastseen``
``timestamp with time zone`` |br|
Timestamp of the last time any SIPIS server
saw the associated device token.
.. _db-migrate:
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**
.. code-block:: sh
# su postgres
$ psql -c "CREATE DATABASE sipis2 OWNER sipis TEMPLATE template0 ENCODING 'SQL_ASCII';"
$ exit
**Stop sipis service**
.. code-block:: sh
# 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.
.. code-block:: sh
# 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
.. code-block:: xml
with (notice ``dbname=sipis2`` instead of ``dbname=sipis``)
.. code-block:: xml
**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.
.. code-block:: sh
# 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.
.. code-block:: sh
# 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`` |br|
Auto-incrementing row identifier.
``id``
Foreign key to the ``selectors`` table.
``name``
``character varying(64)`` |br|
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`` |br|
ID of the push test.
``platform``
``character varying(255) not null`` |br|
Platform of the device to which the test push was sent.
``started``
``timestamp without time zone not null`` |br|
Time when the test push was sent to the device.
``concluded``
``timestamp without time zone`` |br|
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`` |br|
Auto-incrementing row identifier.
``id``
``integer not null`` |br|
Foreign key to the ``selectors`` table.
``name``
``character varying(64) not null`` |br|
The first part of ``name`` and ``value`` pairs that store information about user SIP accouts.
``value``
``character varying(255)`` |br|
The second part of ``name`` and ``value`` pairs that store information about user SIP accounts.
``seq``
``integer default 0 not null`` |br|
TBD.
``server``
``character varying(100)`` |br|
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`` |br|
Integer identifier assigned to the associated instance selector.
``selector``
``character varying(64) not null`` |br|
Instance selector string.
``lastseen``
``timestamp with time zone default now() not null`` |br|
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`` |br|
Auto-incrementing row identifier.
``id``
``integer not null`` |br|
Foreign key to the ``selectors`` table.
``token``
``text not null`` |br|
Base64-encoded device token used for push notifications.
``appid``
``character varying(200) not null`` |br|
Identifier of the application that handles
the associated instance selector.
``lastseen``
``timestamp with time zone`` |br|
Timestamp of the last time any SIPIS server
saw the associated device token.