1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
|
/*
* Copyright (c) 2007-2009 Nokia Corporation and/or its subsidiary(-ies).
* All rights reserved.
* This component and the accompanying materials are made available
* under the terms of "Eclipse Public License v1.0"
* which accompanies this distribution, and is available
* at the URL "http://www.eclipse.org/legal/epl-v10.html".
*
* Initial Contributors:
* Nokia Corporation - initial contribution.
*
* Contributors:
*
* Description:
*
*/
#ifndef DBSQLCONSTANTS_H
#define DBSQLCONSTANTS_H
#include <cntdef.hrh>
const TInt KInitialValue = -1;
const TInt KNumOfTables = 4;
const TInt KNumColInPrefTable = 6;
const TInt KNumColInContactTable = 17;
const TInt KNumColInGroupTable = 3;
const TInt KNumColInCommTable = 6;
// tables in the contact database
_LIT(KSqlContactTableName, "contact");
_LIT(KSqlContactGroupTableName, "groups");
_LIT(KSqlContactPrefTableName, "preferences");
_LIT(KSqlContactCommAddrTableName, "comm_addr");
enum TDatabaseTables
{
EContactPrefTableName,
EContactTableName,
EContactGroupTableName,
EContactCommAddressTableName
};
// columns for contact table
_LIT(KContactId, "contact_id");
_LIT(KContactTemplateId, "template_id");
_LIT(KContactTypeFlags, "type_flags");
_LIT(KContactAccessCount, "access_count");
_LIT(KContactCreationDate, "creation_date");
_LIT(KContactLastModified, "last_modified");
_LIT(KContactGuidString, "guid_string");
_LIT(KContactFirstName, "first_name");
_LIT(KContactLastName, "last_name");
_LIT(KContactCompanyName, "company_name");
_LIT(KContactFirstNamePrn, "firstname_prn");
_LIT(KContactLastNamePrn, "lastname_prn");
_LIT(KContactCompanyNamePrn, "companyname_prn");
_LIT(KContactFavoriteIndex, "favorite_index");
_LIT(KContactTextFieldHeader, "text_fields_header");
_LIT(KContactBinaryFieldHeader, "binary_fields_header");
_LIT(KContactTextFields, "text_fields");
_LIT(KContactBinaryFields, "binary_fields");
//end columns for contact table
// parameters for contact table
_LIT(KContactIdParam, ":contact_id");
_LIT(KContactTemplateIdParam, ":template_id");
_LIT(KContactTypeFlagsParam, ":type_flags");
_LIT(KContactTypeParam, ":contact_type");
_LIT(KAttributesAndHintParam, ":attr_and_hint");
_LIT(KContactAccessCountParam, ":access_count");
_LIT(KContactCreationDateParam, ":creation_date");
_LIT(KContactLastModifiedParam, ":last_modified");
_LIT(KContactGuidStringParam, ":guid_string");
_LIT(KContactFirstNameParam, ":first_name");
_LIT(KContactLastNameParam, ":last_name");
_LIT(KContactCompanyNameParam, ":company_name");
_LIT(KContactFirstNamePrnParam, ":firstname_prn");
_LIT(KContactLastNamePrnParam, ":lastname_prn");
_LIT(KContactCompanyNamePrnParam, ":companyname_prn");
_LIT(KContactFavoriteIndexParam, ":favorite_index");
_LIT(KContactTextFieldHeaderParam, ":text_fields_header");
_LIT(KContactBinaryFieldHeaderParam, ":binary_fields_header");
_LIT(KContactTextFieldsParam, ":text_fields");
_LIT(KContactBinaryFieldsParam, ":binary_fields");
//end parameters for contact table
// create statements
// New Column should be added after the old columns. If the column has "Not NULL" constraint
// then Default Value should be provide.
// New Column should be added to CCntSqlDbStructure::InitializeContactsTableInfoL() also
_LIT(KContactCreateStmnt, "CREATE TABLE contact (contact_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, template_id INTEGER, type_flags INTEGER, access_count INTEGER, creation_date , last_modified , guid_string VARCHAR(255) NULL, first_name VARCHAR(255) NULL, last_name VARCHAR(255) NULL, company_name VARCHAR(255) NULL, firstname_prn VARCHAR(255) NULL, lastname_prn VARCHAR(255) NULL, companyname_prn VARCHAR(255) NULL, text_fields_header BLOB, binary_fields_header BLOB, text_fields TEXT, binary_fields BLOB, favorite_index VARCHAR(255) NULL); CREATE INDEX contact_guid_string_idx ON contact (guid_string);");
// end contact table
// comm-address table
// columns
_LIT(KCommAddrId, "comm_addr_id");
_LIT(KCommAddrType, "type");
_LIT(KCommAddrValue, "value");
_LIT(KCommAddrExtraValue, "extra_value");
_LIT(KCommAddrContactId, "contact_id");
_LIT(KCommAddrExtraTypeInfo, "extra_type_info");
// parameters
_LIT(KCommAddrIdParam, ":comm_addr_id");
_LIT(KCommAddrTypeParam, ":type");
_LIT(KCommAddrValueParam, ":value");
_LIT(KCommAddrExtraValueParam, ":extra_value");
_LIT(KCommAddrContactIdParam, ":contact_id");
_LIT(KCommAddrExtraTypeInfoParam, ":extra_type_info");
// create statements
// New Column should be added after the old columns. If the column has "Not NULL" constraint
// then Default Value should be provide.
// New Column should be added to CCntSqlDbStructure::InitializeCommAddrTableInfoL() also
_LIT(KCommAddrCreateStmnt, "CREATE TABLE comm_addr (comm_addr_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, type INTEGER NOT NULL, value CHAR(255) NOT NULL, extra_value CHAR(255), contact_id INTEGER NOT NULL, extra_type_info INTEGER, FOREIGN KEY (contact_id) REFERENCES contact(contact_id)); CREATE INDEX comm_addr_val_idx ON comm_addr (value); CREATE INDEX comm_addr_contact_id_idx ON comm_addr (contact_id);");
// end comm_address table
// groups table
// columns
_LIT(KGroupId, "group_id");
_LIT(KGroupContactGroupId, "contact_group_id");
_LIT(KGroupContactGroupMemberId, "contact_group_member_id");
// parameters
_LIT(KGroupIdParam, ":group_id");
_LIT(KGroupContactGroupIdParam, ":contact_group_id");
_LIT(KGroupContactGroupMemberIdParam, ":contact_group_member_id");
// create statements
// New Column should be added after the old columns. If the column has "Not NULL" constraint
// then Default Value should be provide.
// New Column should be added to CCntSqlDbStructure::InitializeGroupsTableInfoL() also
_LIT(KGroupsCreateStmnt, "CREATE TABLE groups (group_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, contact_group_id INTEGER NOT NULL, contact_group_member_id INTEGER NOT NULL, FOREIGN KEY (contact_group_id) REFERENCES contact(contact_id), FOREIGN KEY (contact_group_member_id) REFERENCES contact(contact_id) ); CREATE INDEX contact_group_id_idx ON groups (contact_group_id); CREATE INDEX contact_group_member_id_idx ON groups (contact_group_member_id);");
// end groups table
// preferences table
// columns
_LIT(KPrefPrefId,"preference_id");
_LIT(KPrefSchemaVersion,"data_schema_version");
_LIT(KPrefDatabaseUid,"database_uid");
_LIT(KPrefPrefCreationDate,"creation_date");
_LIT(KPrefMachineId, "machine_id");
_LIT(KPrefPreferredOrder, "preferred_sortorder");
_LIT(KPrefferredTemplateId,"preferred_cardtemplate_id");
// parameters
_LIT(KPrefIdParam, ":preference_id");
_LIT(KPrefSchemaVersionParam, ":data_schema_version");
_LIT(KPrefDatabaseUidParam, ":database_uid");
_LIT(KPrefCreationDateParam, ":creation_date");
_LIT(KPrefMachineIdParam, ":machine_id");
_LIT(KPrefPreferredOrderParam, ":preferred_sortorder");
_LIT(KPrefferredTemplateIdParam,":preferred_cardtemplate_id");
// create statements
_LIT(KPreferencesCreateStmnt, "CREATE TABLE preferences (preference_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, data_schema_version INTEGER NOT NULL, preferred_cardtemplate_id INTEGER, machine_id , creation_date , preferred_sortorder BLOB); ");
// end preferences table
// Condition strings for searching id
_LIT(KSelectCondition_SearchForEqual, "%S == :SeekId");
// SQLITE_SEQUENCE table stuff for getting last row_id
_LIT(KSelectLastIdSqlStmnt, "SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'contact';");
// SQLITE_SEQUENCE table stuff for getting last row_id
_LIT(KSqliteSequenceTableName, "SQLITE_SEQUENCE");
_LIT(KSqliteSequenceSeq, "seq");
_LIT(KSqliteSequenceName, "name");
_LIT(KSQLSelect, "SELECT ");
_LIT(KFrom, "FROM ");
_LIT(KSpace, " ");
_LIT(KComma, ",");
_LIT(KSelectTwoFields, "SELECT %S, %S FROM %S WHERE %S=%d");
_LIT(KSelectTwoFieldsWithGreater, "SELECT %S, %S FROM %S WHERE %S>=%d");
_LIT(KSelectFiveFields, "SELECT %S, %S, %S, %S, %S FROM %S");
_LIT(KCountSelect, "SELECT count(*) FROM %S");
_LIT(KCountTypeSelect, "SELECT count(*) FROM %S WHERE (%S>>%d)=%d AND ((%S>>%d)&%d)=0");
_LIT(KOneTypeField, "SELECT %S FROM %S WHERE (%S>>%d)=%d");
_LIT(KTwoTypeField, "SELECT %S, %S FROM %S WHERE (%S>>%d)=%d");
_LIT(KJoinConditionClause, "%S=%S and %S=%d");
_LIT(KConditionClause, "%S=%d");
_LIT(KTypeConditionClause, "(%S>>%d)=%d");
_LIT(KWhereStringEqualsStringFormatText, "%S = %S");
_LIT(KSelectFlagsSqlStmntFormat, "SELECT type_flags FROM contact WHERE contact_id = %d");
_LIT(KVar, ":var");
_LIT(KIntValue, "%d");
_LIT(KSelectCondition_SearchForContactId, "contact_id == :SeekId");
// COUNT function
_LIT(KCount, " COUNT(%S) ");
// NULL
_LIT(KNullText, "NULL"); //think this should be deleted
// AND
_LIT(KSqlAnd, " AND ");
// OR
_LIT(KSqlOr, " OR ");
// COUNT
_LIT(KSqlCount, "count(*)");
const TInt KFirstParam = 0;
/* constants defined for type_flags in contact table */
// definition for contact type flags
//Bit flags in the byte of Contact Type
enum TContactTypeFlags
{
EContactTypeFlags_ContactCard,
EContactTypeFlags_OwnCard,
EContactTypeFlags_ICCEntry,
EContactTypeFlags_Group,
EContactTypeFlags_Template,
EContactTypeFlags_CardTemplate,
EContactTypeFlags_UnknownType
};
const TInt KContactTypeShiftBits = 24; //ContactType byte stored in highest(left most) byte in the type flags integer
/* definition for contact attributes flags */
enum TContactAttrsFlags
{
EContactAttrsFlags_System = 1,
EContactAttrsFlags_Hidden = 2, // 1 << 1
EContactAttrsFlags_CompressedGUID = 4, // 1 << 2
EContactAttrsFlags_Deleted = 8, // 1 << 3
EContactAttrsFlags_Mask = 0x00FF0000
};
const TInt KContactAttrsShiftBits = 16; //Contact Attributes byte stored in second highest byte in the type flags integer
/* definition for contact hint flags */
const TInt KFastAccessFieldUids[] =
{
KUidContactFieldGivenNameValue,
KUidContactFieldFamilyNameValue,
KUidContactFieldCompanyNameValue,
KUidContactFieldGivenNamePronunciationValue,
KUidContactFieldFamilyNamePronunciationValue,
KUidContactFieldCompanyNamePronunciationValue
};
/* definition for contact hint flags */
enum TContactTypeFlagsMasks
{
EContactType_Mask = 0xFF000000,
EContactAttributes_Mask = 0x00FF0000,
EContactHintFlags_Mask = 0x0000FFFF // Hint value put lowest 2 bytes of the type flags integer
};
enum TContactTypeFlagsShitfs
{
EContactAttributes_Shift = 16,
EContactType_Shift = 24
};
enum TFastAccessField
{
EFirstFastAccessField = 0,
EGivenName = EFirstFastAccessField,
EFamilyName,
ECompanyName,
EGivenNamePrn,
EFamilyNamePrn,
ECompanyNamePrn,
ENumberOfFastAccessFields,
ENoSuchFastAccessField = -1
};
const TInt KFirstIndex = 0;
static const TInt MAX_INT_LEN = 11; // Max int value length
// For Database compatibility. Used in cntsqldbstructure
_LIT(KInteger, "INTEGER");
_LIT(KVarchar255,"VARCHAR(255)");
_LIT(KChar255,"CHAR(255)");
_LIT(KBlob,"BLOB");
_LIT(KText,"TEXT");
_LIT(KDefault, "DEFAULT \"\"");
_LIT(KNotNull, "NOT NULL");
_LIT(KNull, "NULL");
_LIT(KAutoIncrement, "PRIMARY KEY AUTOINCREMENT");
_LIT(KPragmaName, "Name");
_LIT(KPragmaType, "Type");
_LIT(KPragmaStmnt, "PRAGMA table_info(%S);");
_LIT(KContactTableAddColumn,"ALTER TABLE %S ADD COLUMN %S %S %S %S");
#endif // #ifndef DBSQLCONSTANTS_H
|
