1/*
2 * Copyright (C) 2011 The Android Open Source Project
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *      http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17package com.android.cellbroadcastreceiver;
18
19import android.content.ContentValues;
20import android.content.Context;
21import android.database.Cursor;
22import android.database.sqlite.SQLiteDatabase;
23import android.database.sqlite.SQLiteException;
24import android.database.sqlite.SQLiteOpenHelper;
25import android.provider.Telephony;
26import android.telephony.SmsCbCmasInfo;
27import android.telephony.SmsCbEtwsInfo;
28import android.telephony.SmsCbMessage;
29import android.util.Log;
30
31import com.android.internal.telephony.gsm.SmsCbConstants;
32
33/**
34 * Open, create, and upgrade the cell broadcast SQLite database. Previously an inner class of
35 * {@code CellBroadcastDatabase}, this is now a top-level class. The column definitions in
36 * {@code CellBroadcastDatabase} have been moved to {@link Telephony.CellBroadcasts} in the
37 * framework, to simplify access to this database from third-party apps.
38 */
39public class CellBroadcastDatabaseHelper extends SQLiteOpenHelper {
40
41    private static final String TAG = "CellBroadcastDatabaseHelper";
42
43    static final String DATABASE_NAME = "cell_broadcasts.db";
44    static final String TABLE_NAME = "broadcasts";
45
46    /** Temporary table for upgrading the database version. */
47    static final String TEMP_TABLE_NAME = "old_broadcasts";
48
49    /**
50     * Database version 1: initial version
51     * Database version 2-9: (reserved for OEM database customization)
52     * Database version 10: adds ETWS and CMAS columns and CDMA support
53     * Database version 11: adds delivery time index
54     */
55    static final int DATABASE_VERSION = 11;
56
57    CellBroadcastDatabaseHelper(Context context) {
58        super(context, DATABASE_NAME, null, DATABASE_VERSION);
59    }
60
61    @Override
62    public void onCreate(SQLiteDatabase db) {
63        db.execSQL("CREATE TABLE " + TABLE_NAME + " ("
64                + Telephony.CellBroadcasts._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
65                + Telephony.CellBroadcasts.GEOGRAPHICAL_SCOPE + " INTEGER,"
66                + Telephony.CellBroadcasts.PLMN + " TEXT,"
67                + Telephony.CellBroadcasts.LAC + " INTEGER,"
68                + Telephony.CellBroadcasts.CID + " INTEGER,"
69                + Telephony.CellBroadcasts.SERIAL_NUMBER + " INTEGER,"
70                + Telephony.CellBroadcasts.SERVICE_CATEGORY + " INTEGER,"
71                + Telephony.CellBroadcasts.LANGUAGE_CODE + " TEXT,"
72                + Telephony.CellBroadcasts.MESSAGE_BODY + " TEXT,"
73                + Telephony.CellBroadcasts.DELIVERY_TIME + " INTEGER,"
74                + Telephony.CellBroadcasts.MESSAGE_READ + " INTEGER,"
75                + Telephony.CellBroadcasts.MESSAGE_FORMAT + " INTEGER,"
76                + Telephony.CellBroadcasts.MESSAGE_PRIORITY + " INTEGER,"
77                + Telephony.CellBroadcasts.ETWS_WARNING_TYPE + " INTEGER,"
78                + Telephony.CellBroadcasts.CMAS_MESSAGE_CLASS + " INTEGER,"
79                + Telephony.CellBroadcasts.CMAS_CATEGORY + " INTEGER,"
80                + Telephony.CellBroadcasts.CMAS_RESPONSE_TYPE + " INTEGER,"
81                + Telephony.CellBroadcasts.CMAS_SEVERITY + " INTEGER,"
82                + Telephony.CellBroadcasts.CMAS_URGENCY + " INTEGER,"
83                + Telephony.CellBroadcasts.CMAS_CERTAINTY + " INTEGER);");
84
85        createDeliveryTimeIndex(db);
86    }
87
88    private void createDeliveryTimeIndex(SQLiteDatabase db) {
89        db.execSQL("CREATE INDEX IF NOT EXISTS deliveryTimeIndex ON " + TABLE_NAME
90                + " (" + Telephony.CellBroadcasts.DELIVERY_TIME + ");");
91    }
92
93    /** Columns to copy on database upgrade. */
94    private static final String[] COLUMNS_V1 = {
95            Telephony.CellBroadcasts.GEOGRAPHICAL_SCOPE,
96            Telephony.CellBroadcasts.SERIAL_NUMBER,
97            Telephony.CellBroadcasts.V1_MESSAGE_CODE,
98            Telephony.CellBroadcasts.V1_MESSAGE_IDENTIFIER,
99            Telephony.CellBroadcasts.LANGUAGE_CODE,
100            Telephony.CellBroadcasts.MESSAGE_BODY,
101            Telephony.CellBroadcasts.DELIVERY_TIME,
102            Telephony.CellBroadcasts.MESSAGE_READ,
103    };
104
105    private static final int COLUMN_V1_GEOGRAPHICAL_SCOPE   = 0;
106    private static final int COLUMN_V1_SERIAL_NUMBER        = 1;
107    private static final int COLUMN_V1_MESSAGE_CODE         = 2;
108    private static final int COLUMN_V1_MESSAGE_IDENTIFIER   = 3;
109    private static final int COLUMN_V1_LANGUAGE_CODE        = 4;
110    private static final int COLUMN_V1_MESSAGE_BODY         = 5;
111    private static final int COLUMN_V1_DELIVERY_TIME        = 6;
112    private static final int COLUMN_V1_MESSAGE_READ         = 7;
113
114    @Override
115    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
116        if (oldVersion == newVersion) {
117            return;
118        }
119        // always log database upgrade
120        log("Upgrading DB from version " + oldVersion + " to " + newVersion);
121
122        // Upgrade from V1 to V10
123        if (oldVersion == 1) {
124            db.beginTransaction();
125            try {
126                // Step 1: rename original table
127                db.execSQL("DROP TABLE IF EXISTS " + TEMP_TABLE_NAME);
128                db.execSQL("ALTER TABLE " + TABLE_NAME + " RENAME TO " + TEMP_TABLE_NAME);
129
130                // Step 2: create new table and indices
131                onCreate(db);
132
133                // Step 3: copy each message into the new table
134                Cursor cursor = db.query(TEMP_TABLE_NAME, COLUMNS_V1, null, null, null, null,
135                        null);
136                try {
137                    while (cursor.moveToNext()) {
138                        upgradeMessageV1ToV2(db, cursor);
139                    }
140                } finally {
141                    cursor.close();
142                }
143
144                // Step 4: drop the original table and commit transaction
145                db.execSQL("DROP TABLE " + TEMP_TABLE_NAME);
146                db.setTransactionSuccessful();
147            } finally {
148                db.endTransaction();
149            }
150            oldVersion = 10;    // skip to version 10
151        }
152
153        // Note to OEMs: if you have customized the database schema since V1, you will need to
154        // add your own code here to convert from your version to version 10.
155        if (oldVersion < 10) {
156            throw new SQLiteException("CellBroadcastDatabase doesn't know how to upgrade "
157                    + " DB version " + oldVersion + " (customized by OEM?)");
158        }
159
160        if (oldVersion == 10) {
161            createDeliveryTimeIndex(db);
162            oldVersion++;
163        }
164    }
165
166    /**
167     * Upgrades a single broadcast message from version 1 to version 2.
168     */
169    private static void upgradeMessageV1ToV2(SQLiteDatabase db, Cursor cursor) {
170        int geographicalScope = cursor.getInt(COLUMN_V1_GEOGRAPHICAL_SCOPE);
171        int updateNumber = cursor.getInt(COLUMN_V1_SERIAL_NUMBER);
172        int messageCode = cursor.getInt(COLUMN_V1_MESSAGE_CODE);
173        int messageId = cursor.getInt(COLUMN_V1_MESSAGE_IDENTIFIER);
174        String languageCode = cursor.getString(COLUMN_V1_LANGUAGE_CODE);
175        String messageBody = cursor.getString(COLUMN_V1_MESSAGE_BODY);
176        long deliveryTime = cursor.getLong(COLUMN_V1_DELIVERY_TIME);
177        boolean isRead = (cursor.getInt(COLUMN_V1_MESSAGE_READ) != 0);
178
179        int serialNumber = ((geographicalScope & 0x03) << 14)
180                | ((messageCode & 0x3ff) << 4) | (updateNumber & 0x0f);
181
182        ContentValues cv = new ContentValues(16);
183        cv.put(Telephony.CellBroadcasts.GEOGRAPHICAL_SCOPE, geographicalScope);
184        cv.put(Telephony.CellBroadcasts.SERIAL_NUMBER, serialNumber);
185        cv.put(Telephony.CellBroadcasts.SERVICE_CATEGORY, messageId);
186        cv.put(Telephony.CellBroadcasts.LANGUAGE_CODE, languageCode);
187        cv.put(Telephony.CellBroadcasts.MESSAGE_BODY, messageBody);
188        cv.put(Telephony.CellBroadcasts.DELIVERY_TIME, deliveryTime);
189        cv.put(Telephony.CellBroadcasts.MESSAGE_READ, isRead);
190        cv.put(Telephony.CellBroadcasts.MESSAGE_FORMAT, SmsCbMessage.MESSAGE_FORMAT_3GPP);
191
192        int etwsWarningType = SmsCbEtwsInfo.ETWS_WARNING_TYPE_UNKNOWN;
193        int cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_UNKNOWN;
194        int cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_UNKNOWN;
195        int cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_UNKNOWN;
196        int cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_UNKNOWN;
197        switch (messageId) {
198            case SmsCbConstants.MESSAGE_ID_ETWS_EARTHQUAKE_WARNING:
199                etwsWarningType = SmsCbEtwsInfo.ETWS_WARNING_TYPE_EARTHQUAKE;
200                break;
201
202            case SmsCbConstants.MESSAGE_ID_ETWS_TSUNAMI_WARNING:
203                etwsWarningType = SmsCbEtwsInfo.ETWS_WARNING_TYPE_TSUNAMI;
204                break;
205
206            case SmsCbConstants.MESSAGE_ID_ETWS_EARTHQUAKE_AND_TSUNAMI_WARNING:
207                etwsWarningType = SmsCbEtwsInfo.ETWS_WARNING_TYPE_EARTHQUAKE_AND_TSUNAMI;
208                break;
209
210            case SmsCbConstants.MESSAGE_ID_ETWS_TEST_MESSAGE:
211                etwsWarningType = SmsCbEtwsInfo.ETWS_WARNING_TYPE_TEST_MESSAGE;
212                break;
213
214            case SmsCbConstants.MESSAGE_ID_ETWS_OTHER_EMERGENCY_TYPE:
215                etwsWarningType = SmsCbEtwsInfo.ETWS_WARNING_TYPE_OTHER_EMERGENCY;
216                break;
217
218            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_PRESIDENTIAL_LEVEL:
219                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_PRESIDENTIAL_LEVEL_ALERT;
220                break;
221
222            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_EXTREME_IMMEDIATE_OBSERVED:
223                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_EXTREME_THREAT;
224                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_EXTREME;
225                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_IMMEDIATE;
226                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_OBSERVED;
227                break;
228
229            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_EXTREME_IMMEDIATE_LIKELY:
230                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_EXTREME_THREAT;
231                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_EXTREME;
232                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_IMMEDIATE;
233                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_LIKELY;
234                break;
235
236            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_EXTREME_EXPECTED_OBSERVED:
237                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_SEVERE_THREAT;
238                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_EXTREME;
239                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_EXPECTED;
240                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_OBSERVED;
241                break;
242
243            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_EXTREME_EXPECTED_LIKELY:
244                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_SEVERE_THREAT;
245                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_EXTREME;
246                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_EXPECTED;
247                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_LIKELY;
248                break;
249
250            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_SEVERE_IMMEDIATE_OBSERVED:
251                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_SEVERE_THREAT;
252                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_SEVERE;
253                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_IMMEDIATE;
254                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_OBSERVED;
255                break;
256
257            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_SEVERE_IMMEDIATE_LIKELY:
258                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_SEVERE_THREAT;
259                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_SEVERE;
260                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_IMMEDIATE;
261                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_LIKELY;
262                break;
263
264            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_SEVERE_EXPECTED_OBSERVED:
265                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_SEVERE_THREAT;
266                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_SEVERE;
267                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_EXPECTED;
268                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_OBSERVED;
269                break;
270
271            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_SEVERE_EXPECTED_LIKELY:
272                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_SEVERE_THREAT;
273                cmasSeverity = SmsCbCmasInfo.CMAS_SEVERITY_SEVERE;
274                cmasUrgency = SmsCbCmasInfo.CMAS_URGENCY_EXPECTED;
275                cmasCertainty = SmsCbCmasInfo.CMAS_CERTAINTY_LIKELY;
276                break;
277
278            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_CHILD_ABDUCTION_EMERGENCY:
279                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_CHILD_ABDUCTION_EMERGENCY;
280                break;
281
282            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_REQUIRED_MONTHLY_TEST:
283                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_REQUIRED_MONTHLY_TEST;
284                break;
285
286            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_EXERCISE:
287                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_CMAS_EXERCISE;
288                break;
289
290            case SmsCbConstants.MESSAGE_ID_CMAS_ALERT_OPERATOR_DEFINED_USE:
291                cmasMessageClass = SmsCbCmasInfo.CMAS_CLASS_OPERATOR_DEFINED_USE;
292                break;
293        }
294
295        if (etwsWarningType != SmsCbEtwsInfo.ETWS_WARNING_TYPE_UNKNOWN
296                || cmasMessageClass != SmsCbCmasInfo.CMAS_CLASS_UNKNOWN) {
297            cv.put(Telephony.CellBroadcasts.MESSAGE_PRIORITY,
298                    SmsCbMessage.MESSAGE_PRIORITY_EMERGENCY);
299        } else {
300            cv.put(Telephony.CellBroadcasts.MESSAGE_PRIORITY,
301                    SmsCbMessage.MESSAGE_PRIORITY_NORMAL);
302        }
303
304        if (etwsWarningType != SmsCbEtwsInfo.ETWS_WARNING_TYPE_UNKNOWN) {
305            cv.put(Telephony.CellBroadcasts.ETWS_WARNING_TYPE, etwsWarningType);
306        }
307
308        if (cmasMessageClass != SmsCbCmasInfo.CMAS_CLASS_UNKNOWN) {
309            cv.put(Telephony.CellBroadcasts.CMAS_MESSAGE_CLASS, cmasMessageClass);
310        }
311
312        if (cmasSeverity != SmsCbCmasInfo.CMAS_SEVERITY_UNKNOWN) {
313            cv.put(Telephony.CellBroadcasts.CMAS_SEVERITY, cmasSeverity);
314        }
315
316        if (cmasUrgency != SmsCbCmasInfo.CMAS_URGENCY_UNKNOWN) {
317            cv.put(Telephony.CellBroadcasts.CMAS_URGENCY, cmasUrgency);
318        }
319
320        if (cmasCertainty != SmsCbCmasInfo.CMAS_CERTAINTY_UNKNOWN) {
321            cv.put(Telephony.CellBroadcasts.CMAS_CERTAINTY, cmasCertainty);
322        }
323
324        db.insert(TABLE_NAME, null, cv);
325    }
326
327    private static void log(String msg) {
328        Log.d(TAG, msg);
329    }
330}
331