|
1 // Copyright (c) 2007-2009 Nokia Corporation and/or its subsidiary(-ies). |
|
2 // All rights reserved. |
|
3 // This component and the accompanying materials are made available |
|
4 // under the terms of "Eclipse Public License v1.0" |
|
5 // which accompanies this distribution, and is available |
|
6 // at the URL "http://www.eclipse.org/legal/epl-v10.html". |
|
7 // |
|
8 // Initial Contributors: |
|
9 // Nokia Corporation - initial contribution. |
|
10 // |
|
11 // Contributors: |
|
12 // |
|
13 // Description: |
|
14 // |
|
15 |
|
16 #include "sqldbaccessor.h" |
|
17 #include "sqldbtransaction.h" |
|
18 #include "urilist.h" |
|
19 #include "tldlist.h" |
|
20 |
|
21 #if defined (TESTDBNAME) |
|
22 _LIT( KDatabaseName, "[A00026FF]URIList.dat" ); |
|
23 #else |
|
24 _LIT( KDatabaseName, "[20009D70]URIList.dat" ); |
|
25 #endif |
|
26 |
|
27 CSqlDbAccessor::CSqlDbAccessor () |
|
28 { |
|
29 |
|
30 } |
|
31 |
|
32 CSqlDbAccessor::~CSqlDbAccessor () |
|
33 { |
|
34 iDatabase.Close (); |
|
35 } |
|
36 |
|
37 MDBAccessor* CSqlDbAccessor::NewL ( const RStringPool& aStringPool ) |
|
38 { |
|
39 CSqlDbAccessor* self = new ( ELeave ) CSqlDbAccessor (); |
|
40 CleanupStack::PushL ( self ); |
|
41 self->ConstructL ( aStringPool ); |
|
42 CleanupStack::Pop (); |
|
43 |
|
44 return self; |
|
45 } |
|
46 |
|
47 void CSqlDbAccessor::ConstructL ( const RStringPool& aStringPool ) |
|
48 { |
|
49 TInt result = iDatabase.Open ( KDatabaseName() ); |
|
50 if ( result == KErrNotFound ) |
|
51 { |
|
52 // Database is not existing. Create one. |
|
53 CreateL ( aStringPool ); |
|
54 } |
|
55 else |
|
56 { |
|
57 User::LeaveIfError ( result ); |
|
58 } |
|
59 } |
|
60 |
|
61 /** |
|
62 Creates a new database and its schemas. |
|
63 */ |
|
64 void CSqlDbAccessor::CreateL ( const RStringPool& aStringPool ) |
|
65 { |
|
66 // Create the URI List database. |
|
67 |
|
68 // First create security policy |
|
69 RSqlSecurityPolicy securityPolicy; |
|
70 CleanupClosePushL ( securityPolicy ); |
|
71 |
|
72 // Create security policies container object using a default security policy. |
|
73 TSecurityPolicy defaultPolicy ( TSecurityPolicy::EAlwaysPass ); |
|
74 User::LeaveIfError (securityPolicy.Create ( defaultPolicy )); |
|
75 |
|
76 // Set up policy to apply to database schema and assign it |
|
77 TSecurityPolicy schemaPolicy ( TSecurityPolicy::EAlwaysPass ); |
|
78 User::LeaveIfError (securityPolicy.SetDbPolicy ( RSqlSecurityPolicy::ESchemaPolicy, schemaPolicy ) ); |
|
79 |
|
80 // Set up policy to apply to write activity on the database and assign it |
|
81 TSecurityPolicy writePolicy ( TSecurityPolicy::EAlwaysPass ); |
|
82 User::LeaveIfError (securityPolicy.SetDbPolicy ( RSqlSecurityPolicy::EWritePolicy, writePolicy )); |
|
83 |
|
84 // Set up policy to apply to write activity to the database table named "URIList" and assign it |
|
85 TSecurityPolicy readPolicy ( TSecurityPolicy::EAlwaysPass ); |
|
86 User::LeaveIfError (securityPolicy.SetDbPolicy(RSqlSecurityPolicy::EReadPolicy, readPolicy)); |
|
87 |
|
88 User::LeaveIfError ( iDatabase.Create ( KDatabaseName, securityPolicy ) ); |
|
89 |
|
90 CreateSchemaL ( aStringPool ); |
|
91 |
|
92 CleanupStack::PopAndDestroy (); // securityPolicy |
|
93 } |
|
94 |
|
95 /** |
|
96 Creates the schemas for the database. We create 2 tables ( URITbl & URIPropsTbl ), 1 view based |
|
97 on those 2 tables for easy retrieval of data and the triggers to update the view. In SQLite the |
|
98 view updation is done via the triggers. By default in SQLite views are read-only |
|
99 */ |
|
100 void CSqlDbAccessor::CreateSchemaL ( const RStringPool& aStringPool ) |
|
101 { |
|
102 RBuf8 stmtBuf; |
|
103 CleanupClosePushL ( stmtBuf ); |
|
104 stmtBuf.CreateL ( KMaxDbStmtLen ); |
|
105 |
|
106 const TDesC8& id ( aStringPool.String (URILIST::EId,URILIST::Table).DesC() ); |
|
107 const TDesC8& scheme ( aStringPool.String (URILIST::EScheme,URILIST::Table).DesC() ); |
|
108 const TDesC8& userInfo ( aStringPool.String (URILIST::EUserInfo,URILIST::Table).DesC() ); |
|
109 const TDesC8& host ( aStringPool.String (URILIST::EHost,URILIST::Table).DesC() ); |
|
110 const TDesC8& port ( aStringPool.String (URILIST::EPort,URILIST::Table).DesC() ); |
|
111 const TDesC8& path ( aStringPool.String (URILIST::EPath,URILIST::Table).DesC() ); |
|
112 const TDesC8& query ( aStringPool.String (URILIST::EQuery,URILIST::Table).DesC() ); |
|
113 const TDesC8& fragments ( aStringPool.String (URILIST::EFragments,URILIST::Table).DesC() ); |
|
114 |
|
115 const TDesC8& RefId ( aStringPool.String (URILIST::EURIRefId,URILIST::Table).DesC() ); |
|
116 const TDesC8& propId ( aStringPool.String (URILIST::EPropId,URILIST::Table).DesC() ); |
|
117 const TDesC8& serviceType (aStringPool.String (URILIST::EServiceType,URILIST::Table).DesC()); |
|
118 const TDesC8& listType (aStringPool.String (URILIST::EListType,URILIST::Table).DesC()); |
|
119 const TDesC8& permission (aStringPool.String (URILIST::EPermission,URILIST::Table).DesC()); |
|
120 const TDesC8& favouriteName (aStringPool.String (URILIST::EFavouriteName,URILIST::Table).DesC()); |
|
121 |
|
122 const TDesC8& KTldname ( aStringPool.String (TLDLIST::ETLDName,TLDLIST::Table).DesC() ); |
|
123 const TDesC8& KTldListType ( aStringPool.String (TLDLIST::EListType,TLDLIST::Table).DesC() ); |
|
124 const TDesC8& KCharacterSet ( aStringPool.String (TLDLIST::ECharacterSet,TLDLIST::Table).DesC() ); |
|
125 |
|
126 _LIT8 ( KURITblCreateStmt, "CREATE TABLE %S ( \ |
|
127 %S INTEGR, \ |
|
128 %S TEXT,\ |
|
129 %S TEXT, \ |
|
130 %S TEXT, \ |
|
131 %S TEXT, \ |
|
132 %S TEXT, \ |
|
133 %S TEXT, \ |
|
134 %S TEXT );" ); |
|
135 |
|
136 _LIT8 ( KURIPropTblCreateStmt, "CREATE TABLE %S ( \ |
|
137 %S INTEGER, \ |
|
138 %S INTEGER,\ |
|
139 %S INTEGER, \ |
|
140 %S INTEGER, \ |
|
141 %S INTEGER, \ |
|
142 %S TEXT );" ); |
|
143 |
|
144 _LIT8 ( KTldTblCreateStmt, "CREATE TABLE %S ( \ |
|
145 %S TEXT,\ |
|
146 %S INTEGER,\ |
|
147 %S BLOB );" ); |
|
148 |
|
149 |
|
150 _LIT8 ( KViewStmt, "CREATE VIEW %S AS SELECT \ |
|
151 T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, T1.%S %S, \ |
|
152 T2.%S %S, T2.%S %S, T2.%S %S, T2.%S %S, T2.%S %S \ |
|
153 FROM \ |
|
154 %S T1, %S T2 \ |
|
155 WHERE \ |
|
156 T1.%S = T2.%S;" ); |
|
157 |
|
158 _LIT8 ( KTriggerInsertStmt, "CREATE TRIGGER %S INSTEAD OF INSERT ON %S \ |
|
159 BEGIN \ |
|
160 INSERT INTO %S \ |
|
161 VALUES ( new.%S, new.%S, new.%S, new.%S, new.%S, new.%S, new.%S, new.%S, \ |
|
162 new.%S, new.%S, new.%S, new.%S, new.%S); \ |
|
163 END;" ); |
|
164 |
|
165 |
|
166 _LIT8 ( KTriggerUpdateStmt, "CREATE TRIGGER %S INSTEAD OF UPDATE ON %S \ |
|
167 BEGIN \ |
|
168 UPDATE %S SET %S=new.%S, %S=new.%S \ |
|
169 WHERE %S=old.%S; \ |
|
170 END;" ); |
|
171 _LIT8 ( KTriggerDeleteStmt, "CREATE TRIGGER %S INSTEAD OF DELETE ON %S \ |
|
172 BEGIN \ |
|
173 DELETE FROM %S WHERE %S=old.%S; \ |
|
174 END;" ); |
|
175 |
|
176 _LIT8 ( KTldTableIndexStmt, "CREATE INDEX %S ON %S ( %S, %S )" ); |
|
177 |
|
178 stmtBuf.Format ( KURITblCreateStmt(), &(KUriTblName()), &id, &scheme, &userInfo, &host, &port, &path, &query, &fragments ); |
|
179 ExecuteL ( stmtBuf ); |
|
180 stmtBuf.Format ( KURIPropTblCreateStmt(), &(KUriPropsTblName()), &RefId, &propId, &serviceType, &listType, &permission, &favouriteName ); |
|
181 ExecuteL ( stmtBuf ); |
|
182 stmtBuf.Format ( KViewStmt(), &(KViewName()), &id, &id, &scheme, &scheme, &userInfo, &userInfo, &host, &host, &port, &port, |
|
183 &path, &path, &query, &query, &fragments, &fragments, &propId, &propId, &serviceType, &serviceType, |
|
184 &listType, &listType, &permission, &permission, &favouriteName, &favouriteName, &(KUriTblName()), &(KUriPropsTblName()), &id, &propId ); |
|
185 ExecuteL ( stmtBuf ); |
|
186 stmtBuf.Format ( KTriggerInsertStmt(), &(KTriggerInsertView()), &(KViewName()), &(KViewName()), &id, &scheme, &userInfo, &host, &port, &path, &query, &fragments, &propId, &serviceType, &listType, &permission, &favouriteName ); |
|
187 ExecuteL ( stmtBuf ); |
|
188 stmtBuf.Format ( KTriggerUpdateStmt(), &(KTriggerUpdateView()), &(KViewName()), &(KViewName()), &listType, &listType, &favouriteName, &favouriteName, &propId, &propId ); |
|
189 ExecuteL ( stmtBuf ); |
|
190 stmtBuf.Format ( KTriggerDeleteStmt(), &(KTriggerDeleteView()), &(KViewName()), &(KViewName()), &propId, &propId ); |
|
191 ExecuteL ( stmtBuf ); |
|
192 |
|
193 stmtBuf.Format ( KTldTblCreateStmt(), &(KTldTblName()), &KTldname, &KTldListType, &KCharacterSet ); |
|
194 ExecuteL ( stmtBuf ); |
|
195 |
|
196 stmtBuf.Format ( KTldTableIndexStmt(), &(KTldTblIndex()), &(KTldTblName()), &KTldname, &KTldListType ); |
|
197 ExecuteL ( stmtBuf ); |
|
198 |
|
199 CleanupStack::PopAndDestroy ( ); // stmtBuf |
|
200 } |
|
201 |
|
202 /** |
|
203 Executes the SQL statement and leave incase of error |
|
204 */ |
|
205 void CSqlDbAccessor::ExecuteL ( const TDesC8& aStmt ) |
|
206 { |
|
207 User::LeaveIfError ( iDatabase.Exec ( aStmt ) ); |
|
208 } |
|
209 |
|
210 /** |
|
211 Prepares a new SQL transaction object by providing a SQL statement. |
|
212 */ |
|
213 MDBTransaction* CSqlDbAccessor::PrepareTransactionL ( const TDesC8& aQueryStmt ) |
|
214 { |
|
215 return CSqlDbTransaction::NewL ( iDatabase, aQueryStmt ); |
|
216 } |
|
217 |
|
218 /** |
|
219 Scalar queries are queries which returns a single value upon completion. This function |
|
220 executes a scalar query and returns the result |
|
221 */ |
|
222 TInt CSqlDbAccessor::ExecuteScalarQueryL ( const TDesC8& aQueryStmt ) |
|
223 { |
|
224 TSqlScalarFullSelectQuery selectQuery ( iDatabase ); |
|
225 return selectQuery.SelectIntL ( aQueryStmt ); |
|
226 } |
|
227 |
|
228 void CSqlDbAccessor::BeginTransactionL () |
|
229 { |
|
230 _LIT8 ( KBeginTrans, "BEGIN TRANSACTION;" ); |
|
231 ExecuteL ( KBeginTrans() ); |
|
232 } |
|
233 |
|
234 void CSqlDbAccessor::CommitTransactionL () |
|
235 { |
|
236 _LIT8 ( KCommitTrans, "COMMIT TRANSACTION;" ); |
|
237 ExecuteL ( KCommitTrans() ); |
|
238 } |
|
239 |
|
240 void CSqlDbAccessor::RollbackTransaction () |
|
241 { |
|
242 _LIT8 ( KRollbackTrans, "ROLLBACK TRANSACTION;" ); |
|
243 TInt err = iDatabase.Exec ( KRollbackTrans() ); |
|
244 } |
|
245 |
|
246 void CSqlDbAccessor::Release () |
|
247 { |
|
248 delete this; |
|
249 } |