#SDD-0005-OS-User-Database

#Table of contents

1. History
2. Scope
3. Abbreviations
4. Introduction
5. Design considerations
6. Database tables
7. Database triggers
8. SMtask service & DBUS communication
9. References

#History

#Scope

This document is defines how SMS and contacts databases are constructed.

#Abbreviations

#Introduction

This document describes details of different user information database tables:

#Design

#Database tables

#1. Contacts joining table

Name: contacts

Possible values of the type field in the contacts' table

#2. Contacts name table

Name: contact_names

#3. Contacts number table

Name: contact_numbers

Possible values of the type field

#4. Contacts ring assets table

Name: contact_rings

#5. Contacts address table

Name: contact_address

Possible values of the address type field

#6. SMS messages table

Name: sms

Possible values of the type field

NOTE: Date is designed to be stored in UNIX (EPOCH) format - number of seconds since 1970-01-01 00:00:00 UTC.

#7. SMS threads table

Name: threads

#8. SMS threads counter table

Name: threads_count

#9. SMS template table

Name: sms_template

#10. Phone settings

Name: settings

Table describes possible values of pin_mode field.

Table describes possible values of language field.

#11. Table with BT paired devices.

Name: bt_devices.

#12. Table with information about notes.

Name: notes.

#13. Table with information about alarms

Name: alarms

Table describes possible states of status field for alarms.

#14. Table with information about calls

Name: calllog.

presentation field - Taken from https://developer.android.com/reference/android/provider/CallLog.Calls.html

type field - Taken from https://developer.android.com/reference/android/provider/CallLog.Calls.html

#14. Table with information about notifications

Name: notifications.

key field

#15. Dictionary defining groups

Name: contact_groups

#15.1 Default groups

By default sepecial gorups are created:

#16. Joining table for groups and contacts

#17. Events table

Name: events

#18. Settins table - newer approach

Name: settings_v2

#Database Triggers

This trigger is responsible for taking action when new thread is created and inserted to threads table. As a result value of the count column with _id equal to 1 in the threads_count table is incremented by 1.

CREATE TRIGGER on_thread_insert AFTER INSERT ON threads BEGIN UPDATE threads_count SET count=count+1 WHERE _id=1; END;

This trigger is responsible for taking action when thread is removed from threads table. As a result value of the count column with _id equal to 1 in the threads_count table is decremented by 1.

CREATE TRIGGER on_thread_remove AFTER DELETE ON threads BEGIN UPDATE threads_count SET count=count-1 WHERE _id=1; END;

#SMtask service & DBUS communication

Database related task is created by Database_Service_Init() function. This function is responsible for all database initialisation & task creation.

SMTask handles used:

• initHandler - used to initialise SQLite, open DB files & initialise tables if necessary
• closeHandler - responsible for closing DB files & shutting SQLite
• sleepHandler - responsible to close DB files
• wakeupHandler - used to openDB files after sleep
• runHandler - main task routine

For communication & control 2 DBUS channels are created:

• DB_TASK_CHANNEL_DB_RESPONSES
• DB_TASK_CHANNEL_DB_COMMANDS

Preferences (TBD)

• timezone (+/- n hours from UTC)
• language

Commands available:

    DB_SMS_ADD,
    DB_SMS_DEL,
    DB_SMS_FIND_ID,
    DB_SMS_FIND,
    DB_SMS_GET_COUNT,
    DB_SMS_GET_RANGE,
    DB_SMS_GET_ALL,
    DB_CONTACTS_ADD,
    DB_CONTACTS_DEL,
    DB_CONTACTS_FIND_ID,
    DB_CONTACTS_FIND,
    DB_CONTACTS_GET_COUNT,
    DB_CONTACTS_GET_RANGE,
    DB_CONTACTS_GET_ALL,
    DB_GROUPS_GET_GROUPS

Each command must be accompanied by DBDBusMessage_t structure which containes data for each command. Each response is accompanied by similar structure containing returned data. All structures & data types are described in db.h file.

#Database open & close

If client wants to access SMS or Contacts database DB_OPEN_SMS_DB or DB_OPEN_CONTACTS_DB command has to be sent first. When database transaction is finished, database has to be closed in order to flush it's internal buffers & commit all transactions. This can be done by sending DB_CLOSE_SMS_DB & DB_CLOSE_CONTACTS_DB commands to database service.

#Database Triggers

This trigger is responsible for taking action when new thread is created and inserted to threads table. As a result value of the count column with _id equal to 1 in the threads_count table is incremented by 1.

CREATE TRIGGER on_thread_insert AFTER INSERT ON threads BEGIN UPDATE threads_count SET count=count+1 WHERE _id=1; END;

This trigger is responsible for taking action when thread is removedf rom threads table. As a result value of the count column with _id equal to 1 in the threads_count table is decremented by 1.

CREATE TRIGGER on_thread_remove AFTER DELETE ON threads BEGIN UPDATE threads_count SET count=count-1 WHERE _id=1; END;

#References