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.