自己动手写 android 数据库（1）

偏执的太偏执、 2022-08-05 07:29 180阅读 0赞

package com.neolink.providers.contacts;  
  
import java.util.HashMap;  
  
import neolink.telephony.PrivateContactConst;  
import neolink.telephony.PrivateContactContracts.CallLogColumns;  
import neolink.telephony.PrivateContactContracts.Contacts;  
import neolink.telephony.PrivateContactContracts.ContactsColumns;  
import neolink.telephony.PrivateContactContracts.Data;  
import neolink.telephony.PrivateContactContracts.DataColumns;  
import neolink.telephony.PrivateContactContracts.GroupContact;  
import neolink.telephony.PrivateContactContracts.GroupContactColumns;  
import neolink.telephony.PrivateContactContracts.MimetypesColumns;  
import neolink.telephony.PrivateContactContracts.ZoneColumns;  
  
import android.content.Context;  
import android.database.DatabaseUtils;  
import android.database.sqlite.SQLiteDatabase;  
import android.database.sqlite.SQLiteDoneException;  
import android.database.sqlite.SQLiteOpenHelper;  
import android.database.sqlite.SQLiteStatement;  
import android.provider.BaseColumns;  
import android.provider.ContactsContract.RawContacts;  
import com.neolink.providers.util.Utils;  
  
public class PrivateContactsDataHelper extends SQLiteOpenHelper \{  
private static final String LOG\_TAG = "PrivateContactsDataHelper";  
  
private static final String DATABASE\_NAME = "privatecontacts2.db";  
private static final int VERSION = 47;  
private static PrivateContactsDataHelper sSingleton;  
private SQLiteDatabase mDefaultWritableDatabase = null;  
private Context mContext;  
/\*\* In-memory cache of previously found MIME-type mappings \*/  
public final HashMap<String, Long> mMimetypeCache = new HashMap<String, Long>();  
  
public interface PrivateContact \{  
public static final String CONCRETE\_CONTACT\_ID = Tables.PRIVATE\_CONTACT  
\+ "." + ContactsColumns.\_ID;  
\}  
  
public interface PrivateData \{  
public static final String CONCRETE\_DATA\_ID = Tables.PRIVATE\_DATA + "."  
\+ DataColumns.\_ID;  
public static final String CONCRETE\_DATA\_CONTACT\_ID = Tables.PRIVATE\_DATA  
\+ "." + Data.CONTACT\_ID;  
public static final String CONCRETE\_DATA\_GROUP\_ID = Tables.PRIVATE\_DATA  
\+ "." + Data.GROUP\_ID;  
public static final String CONCRETE\_DATA\_MIMETYPE\_ID = Tables.PRIVATE\_DATA  
\+ "." + Data.MIMETYPE\_ID;  
\}  
  
public interface PrivateMimeType \{  
public static final String CONCRETE\_MIMETYPE\_ID = Tables.PRIVATE\_MIMETYPE  
\+ "." + MimetypesColumns.\_ID;  
public static final String CONCRETE\_MIMETYPE\_MIMETYPE = Tables.PRIVATE\_MIMETYPE  
\+ "." + MimetypesColumns.MIMETYPE;  
\}  
  
public interface PrivateGroup \{  
public static final String CONCRETE\_GROUP\_ID = Tables.PRIVATE\_GROUP\_CONTACT  
\+ "." + GroupContactColumns.\_ID;  
\}  
  
public interface Tables \{  
public static final String PRIVATE\_CONTACT = "p\_contacts";  
public static final String PRIVATE\_DATA = "p\_data";  
public static final String PRIVATE\_MIMETYPE = "p\_mimetypes";  
public static final String PRIVATE\_CALL = "p\_calls";  
public static final String PRIVATE\_GROUP\_CONTACT = "p\_group";  
public static final String PRIVATE\_ZONE = "p\_zone";  
\}  
  
public interface Views \{  
public static final String PRIVATE\_CONTACT = "view\_pcontact";  
public static final String PRIVATE\_DATA = "view\_pdata";  
\}  
  
public static final String CREATE\_DATABASE\_PCONTACTS = "CREATE TABLE "  
\+ Tables.PRIVATE\_CONTACT + " (" + BaseColumns.\_ID  
\+ " INTEGER PRIMARY KEY AUTOINCREMENT," + Contacts.DISPLAY\_NAME  
\+ " TEXT," + Contacts.NUMBER + " INTEGER ,"  
\+ Contacts.CONTACTS\_TYPE + " INTEGER DEFAULT -1 ,"  
\+ Contacts.CONTACTS\_PCTOOLS + " INTEGER DEFAULT -1 ,"  
\+ Contacts.SEARCH\_INDEX\_NAME + " TEXT,"  
\+ Contacts.SEARCH\_INDEX\_NUMBER + " TEXT," + Contacts.CURRENT\_MODE  
\+ " INTEGER DEFAULT -1 ," + Contacts.SORT\_KEY + " );";  
  
public static final String CREATE\_DATABASE\_PDATA = "CREATE TABLE "  
\+ Tables.PRIVATE\_DATA + " (" + BaseColumns.\_ID  
\+ " INTEGER PRIMARY KEY AUTOINCREMENT," + Data.CONTACT\_ID  
\+ " INTEGER ," + Data.GROUP\_ID + " INTEGER ," + Data.MIMETYPE\_ID  
\+ " INTEGER ," + Data.DATA1 + " TEXT," + Data.DATA2 + " TEXT,"  
\+ Data.DATA3 + " TEXT" + " )";  
  
public static final String CREATE\_DATABASE\_PGCONTACT = "CREATE TABLE "  
\+ Tables.PRIVATE\_GROUP\_CONTACT + " (" + BaseColumns.\_ID  
\+ " INTEGER PRIMARY KEY AUTOINCREMENT,"  
\+ GroupContactColumns.GROUP\_ID + " INTEGER DEFAULT -1 ,"  
\+ GroupContactColumns.GROUP\_CONTACT\_NAME + " TEXT ,"  
\+ GroupContactColumns.GROUP\_CONTACT\_NUMBER + " TEXT ,"  
\+ GroupContactColumns.GROUP\_MODE\_TYPE + " INTEGER DEFAULT -1 ,"  
\+ GroupContactColumns.GROUP\_CONTACT\_TYPE + " INTEGER DEFAULT -1 ,"  
\+ GroupContactColumns.GROUP\_ZONE\_ID + " INTEGER DEFAULT -1 ,"  
\+ GroupContactColumns.GROUP\_ZONE\_NAME + " INTEGER DEFAULT -1 ,"  
\+ GroupContactColumns.GROUP\_DEFAULT + " INTEGER DEFAULT -1 ,"  
\+ GroupContact.GROUP\_CONTACT\_SORT\_KEY + " TEXT " + " )";  
  
public static final String CREATE\_DATABASE\_PMIMETYPE = "CREATE TABLE "  
\+ Tables.PRIVATE\_MIMETYPE + " (" + MimetypesColumns.\_ID  
\+ " INTEGER PRIMARY KEY AUTOINCREMENT," + MimetypesColumns.MIMETYPE  
\+ " TEXT " + " )";  
  
public static final String CREATE\_DATABASE\_PZONE = "CREATE TABLE "  
\+ Tables.PRIVATE\_ZONE + " (" + ZoneColumns.\_ID  
\+ " INTEGER PRIMARY KEY AUTOINCREMENT," + ZoneColumns.ZONE\_NAME  
\+ " TEXT "  
\+ " )";  
  
public static final String CREATE\_DATABASE\_PCALLS = "CREATE TABLE "  
\+ Tables.PRIVATE\_CALL + " (" + CallLogColumns.\_ID  
\+ " INTEGER PRIMARY KEY AUTOINCREMENT," + CallLogColumns.CONTACTS\_ID  
\+ " INTEGER," + CallLogColumns.GROUP\_ID  
\+ " INTEGER,"+ CallLogColumns.NUMBER + " TEXT,"  
\+ CallLogColumns.DISPLAY\_NAME + " TEXT," + CallLogColumns.DATE  
\+ " INGETER," + CallLogColumns.DURATION + " INTEGER,"  
\+ CallLogColumns.TYPE + " INTEGER, " + CallLogColumns.IS\_READ  
\+ " INTEGER ," + CallLogColumns.IS\_HANDUP + " INTEGER ,"  
\+ CallLogColumns.CALL\_LOG\_MODE + " INTEGER ,"  
\+ CallLogColumns.IS\_NEW + " INTEGER NOT NULL DEFAULT 0" + " )";  
  
public static final String CREATE\_INDEX\_ON\_PMIMETYPE = "CREATE UNIQUE INDEX p\_mimetype ON "  
\+ Tables.PRIVATE\_MIMETYPE + " (" + MimetypesColumns.MIMETYPE + " )";  
  
public PrivateContactsDataHelper(Context context) \{  
super(context, DATABASE\_NAME, null, VERSION);  
Utils.logProvider("PrivateContactsDataHelper constructor");  
mContext = context;  
\}  
  
public static synchronized PrivateContactsDataHelper getInstance(  
Context context) \{  
Utils.logProvider("PrivateContactsDataHelper getInstance");  
if (sSingleton == null) \{  
sSingleton = new PrivateContactsDataHelper(context);  
\}  
return sSingleton;  
\}  
  
@Override  
public void onCreate(SQLiteDatabase db) \{  
Utils.logProvider("PrivateContactsDataHelper database version is:"  
\+ VERSION);  
this.mDefaultWritableDatabase = db;  
createDatabaseContacts(db);  
createDatabaseData(db);  
createDatabaseMimetype(db);  
createDatabasePrivateCalls(db);  
createDatabasePrivateGroupContacts(db);  
createDatabasePrivateGroupZone(db);  
createdefaultMiMetype(db);  
createContactsTriggers(db);  
createGroupTriggers(db);  
createContactsIndexes(db, false);  
createGroupIndexes(db, false);  
createViews(db);  
// boolean isPreLoadGroupContact =  
// mContext.getResources().getBoolean(R.bool.group\_pre\_load);  
// if(isPreLoadGroupContact)\{  
// loadTestGroupContact(db);  
// \}  
\}  
  
// private void loadTestGroupContact(SQLiteDatabase db)\{  
// String\[\] nameArray =  
// mContext.getResources().getStringArray(R.array.group\_contact\_name);  
// String\[\] numberArray =  
// mContext.getResources().getStringArray(R.array.group\_contact\_number);  
// if(nameArray.length != numberArray.length)throw new  
// IllegalStateException("preLoadGroupContact not formated");  
// for(int i=0;i<nameArray.length;i++)\{  
// String name = nameArray\[i\];  
// String number = numberArray\[i\];  
//  
// ContentValues values = new ContentValues();  
// values.put(GroupContact.GROUP\_CONTACT\_NAME, name);  
// values.put(GroupContact.GROUP\_CONTACT\_NUMBER, number);  
// db.insert(Tables.PRIVATE\_GROUP\_CONTACT, null, values);  
// \}  
// \}  
  
@Override  
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) \{  
Utils.logProvider("PrivateContactsDatabaseHelper oldVersion is:"  
\+ oldVersion + " newVersion is:" + newVersion);  
this.mDefaultWritableDatabase = db;  
if (oldVersion != newVersion) \{  
dropAll(db);  
onCreate(db);  
\}  
\}  
  
private void createDatabaseData(SQLiteDatabase db) \{  
db.execSQL(CREATE\_DATABASE\_PDATA);  
\}  
  
private void createDatabaseContacts(SQLiteDatabase db) \{  
db.execSQL(CREATE\_DATABASE\_PCONTACTS);  
\}  
  
private void createDatabaseMimetype(SQLiteDatabase db) \{  
db.execSQL(CREATE\_DATABASE\_PMIMETYPE);  
db.execSQL(CREATE\_INDEX\_ON\_PMIMETYPE);  
\}  
  
private void createDatabasePrivateCalls(SQLiteDatabase db) \{  
db.execSQL(CREATE\_DATABASE\_PCALLS);  
\}  
  
private void createDatabasePrivateGroupContacts(SQLiteDatabase db) \{  
db.execSQL(CREATE\_DATABASE\_PGCONTACT);  
\}  
  
private void createDatabasePrivateGroupZone(SQLiteDatabase db) \{  
db.execSQL(CREATE\_DATABASE\_PZONE);  
\}  
  
private void createdefaultMiMetype(SQLiteDatabase db) \{  
/\*\*  
\* contacats number 9  
\*/  
db.execSQL("INSERT INTO "  
\+ Tables.PRIVATE\_MIMETYPE  
\+ " ("  
\+ MimetypesColumns.\_ID  
\+ ","  
\+ MimetypesColumns.MIMETYPE  
\+ " )"  
\+ "VALUES"  
\+ "("  
\+ 9  
\+ ", '"  
\+ PrivateContactConst.DataKindMimeType.CONTACTS\_NUMBER\_CONTENT\_ITEM\_TYPE  
\+ " ' " + ")");  
  
mMimetypeCache.put(PrivateContactConst.DataKindMimeType.CONTACTS\_NUMBER\_CONTENT\_ITEM\_TYPE, (long) 9);  
/\*\*  
\* contacats name 10  
\*/  
db.execSQL("INSERT INTO "  
\+ Tables.PRIVATE\_MIMETYPE  
\+ " ("  
\+ MimetypesColumns.\_ID  
\+ ","  
\+ MimetypesColumns.MIMETYPE  
\+ " )"  
\+ "VALUES"  
\+ "("  
\+ 10  
\+ ", '"  
\+ PrivateContactConst.DataKindMimeType.CONTACTS\_NAME\_CONTENT\_ITEM\_TYPE  
\+ " ' " + ")");  
  
mMimetypeCache.put(PrivateContactConst.DataKindMimeType.CONTACTS\_NAME\_CONTENT\_ITEM\_TYPE, (long) 10);  
/\*\*  
\* group number 11  
\*/  
db.execSQL("INSERT INTO "  
\+ Tables.PRIVATE\_MIMETYPE  
\+ " ("  
\+ MimetypesColumns.\_ID  
\+ ","  
\+ MimetypesColumns.MIMETYPE  
\+ " )"  
\+ "VALUES"  
\+ "("  
\+ 11  
\+ ", '"  
\+ PrivateContactConst.DataKindMimeType.GROUP\_NUMBER\_CONTENT\_ITEM\_TYPE  
\+ " ' " + ")");  
  
mMimetypeCache.put(PrivateContactConst.DataKindMimeType.GROUP\_NUMBER\_CONTENT\_ITEM\_TYPE, (long) 11);  
/\*\*  
\* group name 12  
\*/  
db.execSQL("INSERT INTO "  
\+ Tables.PRIVATE\_MIMETYPE  
\+ " ("  
\+ MimetypesColumns.\_ID  
\+ ","  
\+ MimetypesColumns.MIMETYPE  
\+ " )"  
\+ "VALUES"  
\+ "("  
\+ 12  
\+ ", '"  
\+ PrivateContactConst.DataKindMimeType.GROUP\_NAME\_CONTENT\_ITEM\_TYPE  
\+ " ' " + ")");  
mMimetypeCache.put(PrivateContactConst.DataKindMimeType.GROUP\_NAME\_CONTENT\_ITEM\_TYPE, (long) 12);  
/\*\*  
\* group memeber ship 13  
\*/  
db.execSQL("INSERT INTO "  
\+ Tables.PRIVATE\_MIMETYPE  
\+ " ("  
\+ MimetypesColumns.\_ID  
\+ ","  
\+ MimetypesColumns.MIMETYPE  
\+ " )"  
\+ "VALUES"  
\+ "("  
\+ 13  
\+ ", '"  
\+ PrivateContactConst.DataKindMimeType.GROUP\_MEMBER\_ITEM\_TYPE  
\+ " ' " + ")");  
mMimetypeCache.put(PrivateContactConst.DataKindMimeType.GROUP\_MEMBER\_ITEM\_TYPE, (long) 13);  
  
/\*\*  
\* group memeber ship 13  
\*/  
db.execSQL("INSERT INTO "  
\+ Tables.PRIVATE\_MIMETYPE  
\+ " ("  
\+ MimetypesColumns.\_ID  
\+ ","  
\+ MimetypesColumns.MIMETYPE  
\+ " )"  
\+ "VALUES"  
\+ "("  
\+ 14  
\+ ", '"  
\+ PrivateContactConst.DataKindMimeType.GROUP\_ZONE\_ITEM\_TYPE  
\+ " ' " + ")");  
mMimetypeCache.put(PrivateContactConst.DataKindMimeType.GROUP\_ZONE\_ITEM\_TYPE, (long) 14);  
\}  
  
/\*\*  
\* Convert a mimetype into an integer, using \{@link Tables\#MIMETYPES\} for  
\* lookups and possible allocation of new IDs as needed.  
\*/  
public long getMimeTypeId(String mimetype) \{  
// Try an in-memory cache lookup  
if (mMimetypeCache.containsKey(mimetype))  
return mMimetypeCache.get(mimetype);  
  
return lookupMimeTypeId(mimetype, getWritableDatabase());  
\}  
  
private long lookupMimeTypeId(String mimetype, SQLiteDatabase db) \{  
final SQLiteStatement mimetypeQuery = db.compileStatement("SELECT "  
\+ MimetypesColumns.\_ID + " FROM " + Tables.PRIVATE\_MIMETYPE  
\+ " WHERE " + MimetypesColumns.MIMETYPE + "=?");  
  
final SQLiteStatement mimetypeInsert = db  
.compileStatement("INSERT INTO " + Tables.PRIVATE\_MIMETYPE  
\+ "(" + MimetypesColumns.MIMETYPE + ") VALUES (?)");  
  
try \{  
return lookupAndCacheId(mimetypeQuery, mimetypeInsert, mimetype,  
mMimetypeCache);  
\} finally \{  
mimetypeQuery.close();  
mimetypeInsert.close();  
\}  
\}  
  
/\*\*  
\* Perform an internal string-to-integer lookup using the compiled  
\* \{@link SQLiteStatement\} provided. If a mapping isn't found in database,  
\* it will be created. All new, uncached answers are added to the cache  
\* automatically.  
\*  
\* @param query  
\* Compiled statement used to query for the mapping.  
\* @param insert  
\* Compiled statement used to insert a new mapping when no  
\* existing one is found in cache or from query.  
\* @param value  
\* Value to find mapping for.  
\* @param cache  
\* In-memory cache of previous answers.  
\* @return An unique integer mapping for the given value.  
\*/  
private long lookupAndCacheId(SQLiteStatement query,  
SQLiteStatement insert, String value, HashMap<String, Long> cache) \{  
long id = -1;  
try \{  
// Try searching database for mapping  
DatabaseUtils.bindObjectToProgram(query, 1, value);  
id = query.simpleQueryForLong();  
\} catch (SQLiteDoneException e) \{  
// Nothing found, so try inserting new mapping  
DatabaseUtils.bindObjectToProgram(insert, 1, value);  
id = insert.executeInsert();  
\}  
if (id != -1) \{  
// Cache and return the new answer  
cache.put(value, id);  
return id;  
\} else \{  
// Otherwise throw if no mapping found or created  
throw new IllegalStateException("Couldn't find or create internal "  
\+ "lookup table entry for value " + value);  
\}  
\}  
  
private void createContactsTriggers(SQLiteDatabase db) \{  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
\+ "\_deleted;");  
db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_CONTACT + "\_deleted "  
\+ " BEFORE DELETE ON " + Tables.PRIVATE\_CONTACT + " BEGIN "  
\+ " DELETE FROM " + Tables.PRIVATE\_DATA + " WHERE "  
\+ DataColumns.CONTACT\_ID + "=OLD." + ContactsColumns.\_ID + ";"  
\+ " END");  
  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
\+ "\_updated1;");  
db.execSQL("CREATE TRIGGER "  
\+ Tables.PRIVATE\_CONTACT  
\+ "\_updated1 "  
\+ " AFTER UPDATE ON "  
\+ Tables.PRIVATE\_CONTACT  
\+ " BEGIN "  
\+ " UPDATE "  
\+ Tables.PRIVATE\_DATA  
\+ " SET "  
\+ DataColumns.DATA1  
\+ "=NEW."  
\+ ContactsColumns.DISPLAY\_NAME  
\+ " WHERE "  
\+ DataColumns.CONTACT\_ID  
\+ "=OLD."  
\+ ContactsColumns.\_ID  
\+ " AND "  
\+ DataColumns.MIMETYPE\_ID  
\+ "= "  
\+ getMimeTypeId(PrivateContactConst.DataKindMimeType.CONTACTS\_NAME\_CONTENT\_ITEM\_TYPE)  
\+ " ;" + " END");  
  
  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
\+ "\_updated2;");  
db.execSQL("CREATE TRIGGER "  
\+ Tables.PRIVATE\_CONTACT  
\+ "\_updated2 "  
\+ " AFTER UPDATE ON "  
\+ Tables.PRIVATE\_CONTACT  
\+ " BEGIN "  
\+ " UPDATE "  
\+ Tables.PRIVATE\_DATA  
\+ " SET "  
\+ DataColumns.DATA1  
\+ "=NEW."  
\+ ContactsColumns.NUMBER  
\+ " WHERE "  
\+ DataColumns.CONTACT\_ID  
\+ "=OLD."  
\+ ContactsColumns.\_ID  
\+ " AND "  
\+ DataColumns.MIMETYPE\_ID  
\+ "= "  
\+ getMimeTypeId(PrivateContactConst.DataKindMimeType.CONTACTS\_NUMBER\_CONTENT\_ITEM\_TYPE)  
\+ " ;" + " END");  
  
// db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
// + "\_inserted;");  
// db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_CONTACT + "\_inserted"  
// + " AFTER INSERT ON " + Tables.PRIVATE\_CONTACT + " BEGIN "  
// + " INSERT INTO " + Tables.PRIVATE\_DATA + " ( "  
// + DataColumns.CONTACT\_ID + "," + DataColumns.MIMETYPE\_ID + ","  
// + DataColumns.DATA1 + " ) " + " VALUES " + "(" + "NEW."  
// + ContactsColumns.\_ID + " , " + "NEW." + MimetypesColumns.\_ID  
// + " , " + "NEW." + ContactsColumns.DISPLAY\_NAME + ")" + ";"  
// + " END");  
//  
// db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
// + "\_contacts\_\_inserted;");  
// db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_CONTACT + "\_contacts"  
// + "\_inserted" + " AFTER INSERT ON " + Tables.PRIVATE\_CONTACT  
// + " BEGIN " + " INSERT INTO " + Tables.PRIVATE\_DATA + " ( "  
// + DataColumns.CONTACT\_ID + "," + DataColumns.MIMETYPE\_ID + ","  
// + DataColumns.DATA1 + " ) " + " VALUES " + "(" + "NEW."  
// + ContactsColumns.\_ID + " , " + "NEW." + MimetypesColumns.\_ID  
// + " , " + "NEW." + ContactsColumns.NUMBER + ")" + ";" + " END");  
//  
  
//  
// db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
// + "\_contacts\_\_inserted;");  
// db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_CONTACT + "\_contacts"  
// + "\_inserted" + " AFTER INSERT ON " + Tables.PRIVATE\_CONTACT  
// + " BEGIN " + " INSERT INTO " + Tables.PRIVATE\_DATA + " ( "  
// + DataColumns.CONTACT\_ID + "," + DataColumns.MIMETYPE\_ID + ","  
// + DataColumns.DATA1 + " ) " + " VALUES " + "(" + "NEW."  
// + ContactsColumns.\_ID + " , " + "NEW." + MimetypesColumns.\_ID  
// + " , " + "NEW." + ContactsColumns.NUMBER + ")" + ";" + " END");  
// db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
// + "contacats\_inserted;");  
// db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_CONTACT+"\_contacts" + "contacats\_inserted"  
// + " AFTER INSERT ON " + Tables.PRIVATE\_CONTACT + " BEGIN "  
// + " INSERT INTO " + Tables.PRIVATE\_DATA + " ( "  
// + DataColumns.CONTACT\_ID + " , "  
// + DataColumns.DATA1 + " ) "+"SELECT "+ContactsColumns.\_ID+" , "  
// +ContactsColumns.DISPLAY\_NAME+" FROM "+Tables.PRIVATE\_CONTACT+" WHERE "  
// +"NEW."  
// + ContactsColumns.\_ID +"=NEW."  
// + DataColumns.GROUP\_ID+";"  
// + " END");  
//  
// db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
// + "\_contacts\_\_inserted;");  
// db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_CONTACT + "\_contacts"  
// + "\_inserted" + " AFTER INSERT ON " + Tables.PRIVATE\_CONTACT  
// + " BEGIN " + " INSERT INTO " + Tables.PRIVATE\_DATA + " ( "  
// + DataColumns.CONTACT\_ID + "," + DataColumns.MIMETYPE\_ID + ","  
// + DataColumns.DATA1 + " ) " + " VALUES " + "(" + "NEW."  
// + ContactsColumns.\_ID + " , " + "NEW." + MimetypesColumns.\_ID  
// + " , " + "NEW." + ContactsColumns.NUMBER + ")" +" WHERE "+ PrivateMimeType.CONCRETE\_MIMETYPE\_MIMETYPE  
// + "='"  
// + PrivateContactConst.DataKindMimeType.CONTACTS\_NUMBER\_CONTENT\_ITEM\_TYPE  
// + "' ;" + " END");  
  
// db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_CONTACT  
// + "\_updated1;");  
// db.execSQL("CREATE TRIGGER "  
// + Tables.PRIVATE\_CONTACT  
// + "\_updated1 "  
// + " AFTER UPDATE ON "  
// + Tables.PRIVATE\_CONTACT  
// + " BEGIN "  
// + " UPDATE "  
// + Tables.PRIVATE\_DATA  
// + " SET "  
// + DataColumns.DATA1  
// + "=NEW."  
// + ContactsColumns.DISPLAY\_NAME  
// + " WHERE "  
// + "NEW."+Data.CONTACT\_ID  
// + "=NEW."  
// + ContactsColumns.\_ID  
// + " AND "  
// + PrivateMimeType.CONCRETE\_MIMETYPE\_MIMETYPE  
// + "='"  
// + PrivateContactConst.DataKindMimeType.CONTACTS\_NAME\_CONTENT\_ITEM\_TYPE  
// + "' ;" + " END");  
  
  
/\*  
  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.RAW\_CONTACTS + "\_marked\_deleted;");  
db.execSQL("CREATE TRIGGER " + Tables.RAW\_CONTACTS + "\_marked\_deleted "  
\+ " AFTER UPDATE ON " + Tables.RAW\_CONTACTS  
\+ " BEGIN "  
\+ " UPDATE " + Tables.RAW\_CONTACTS  
\+ " SET "  
\+ RawContacts.VERSION + "=OLD." + RawContacts.VERSION + "+1 "  
\+ " WHERE " + RawContacts.\_ID + "=OLD." + RawContacts.\_ID  
\+ " AND NEW." + RawContacts.DELETED + "!= OLD." + RawContacts.DELETED + ";"  
\+ " END");  
\*/  
  
// INSERT INTO first\_table\_name \[(column1, column2, ... columnN)\]  
// SELECT column1, column2, ...columnN  
// FROM second\_table\_name  
// \[WHERE condition\];  
//  
// final String insertContactsWithoutAccount = (  
// " INSERT OR IGNORE INTO " + Tables.DEFAULT\_DIRECTORY +  
// " SELECT " + RawContacts.CONTACT\_ID +  
// " FROM " + Tables.RAW\_CONTACTS +  
// " WHERE " + RawContactsColumns.CONCRETE\_ACCOUNT\_ID +  
// "=" + Clauses.LOCAL\_ACCOUNT\_ID + ";");  
  
  
// CREATE TRIGGER audit\_log AFTER INSERT  
// ON COMPANY  
// BEGIN  
// INSERT INTO AUDIT(EMP\_ID, ENTRY\_DATE) VALUES (new.ID,  
// datetime('now'));  
// END;  
  
\}  
  
private void createGroupTriggers(SQLiteDatabase db) \{  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_GROUP\_CONTACT  
\+ "\_deleted;");  
db.execSQL("CREATE TRIGGER " + Tables.PRIVATE\_GROUP\_CONTACT  
\+ "\_deleted " + " BEFORE DELETE ON "  
\+ Tables.PRIVATE\_GROUP\_CONTACT + " BEGIN " + " DELETE FROM "  
\+ Tables.PRIVATE\_DATA + " WHERE " + DataColumns.GROUP\_ID  
\+ "=OLD." + ContactsColumns.\_ID + ";" + " END");  
  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_GROUP\_CONTACT  
\+ "\_updated1;");  
db.execSQL("CREATE TRIGGER "  
\+ Tables.PRIVATE\_GROUP\_CONTACT  
\+ "\_updated1 "  
\+ " AFTER UPDATE ON "  
\+ Tables.PRIVATE\_GROUP\_CONTACT  
\+ " BEGIN "  
\+ " UPDATE "  
\+ Tables.PRIVATE\_DATA  
\+ " SET "  
\+ DataColumns.DATA1  
\+ "=NEW."  
\+ GroupContactColumns.GROUP\_CONTACT\_NAME  
\+ " WHERE "  
\+ DataColumns.GROUP\_ID  
\+ "=OLD."  
\+ GroupContactColumns.\_ID  
\+ " AND "  
\+ DataColumns.MIMETYPE\_ID  
\+ "= "  
\+ getMimeTypeId(PrivateContactConst.DataKindMimeType.GROUP\_NAME\_CONTENT\_ITEM\_TYPE)  
\+ " ;" + " END");  
  
db.execSQL("DROP TRIGGER IF EXISTS " + Tables.PRIVATE\_GROUP\_CONTACT  
\+ "\_updated2;");  
db.execSQL("CREATE TRIGGER "  
\+ Tables.PRIVATE\_GROUP\_CONTACT  
\+ "\_updated2 "  
\+ " AFTER UPDATE ON "  
\+ Tables.PRIVATE\_GROUP\_CONTACT  
\+ " BEGIN "  
\+ " UPDATE "  
\+ Tables.PRIVATE\_DATA  
\+ " SET "  
\+ DataColumns.DATA1  
\+ "=NEW."  
\+ GroupContactColumns.GROUP\_CONTACT\_NUMBER  
\+ " WHERE "  
\+ DataColumns.GROUP\_ID  
\+ "=OLD."  
\+ GroupContactColumns.\_ID  
\+ " AND "  
\+ DataColumns.MIMETYPE\_ID  
\+ "= "  
\+ getMimeTypeId(PrivateContactConst.DataKindMimeType.GROUP\_NUMBER\_CONTENT\_ITEM\_TYPE)  
\+ " ;" + " END");  
  
\}  
  
private void createContactsIndexes(SQLiteDatabase db,  
boolean rebuildSqliteStats) \{  
db.execSQL("DROP INDEX IF EXISTS contacts\_lookup\_index");  
db.execSQL("CREATE INDEX contacts\_lookup\_index ON "  
\+ Tables.PRIVATE\_CONTACT + " (" + ContactsColumns.NUMBER + ","  
\+ ContactsColumns.CURRENT\_MODE + ", "  
\+ ContactsColumns.DISPLAY\_NAME + ", "  
\+ ContactsColumns.CONTACTS\_TYPE + ");");  
  
db.execSQL("DROP INDEX IF EXISTS name\_number\_lookup\_index");  
db.execSQL("CREATE INDEX name\_number\_lookup\_index ON "  
\+ Tables.PRIVATE\_CONTACT + " ("  
\+ ContactsColumns.SEARCH\_INDEX\_NAME + ","  
\+ ContactsColumns.SEARCH\_INDEX\_NUMBER + ", "  
\+ ContactsColumns.DISPLAY\_NAME + ", " + ContactsColumns.\_ID  
\+ ");");  
\}  
  
private void createGroupIndexes(SQLiteDatabase db,  
boolean rebuildSqliteStats) \{  
db.execSQL("DROP INDEX IF EXISTS group\_lookup\_index");  
db.execSQL("CREATE INDEX group\_lookup\_index ON "  
\+ Tables.PRIVATE\_GROUP\_CONTACT + " (" + GroupContactColumns.\_ID  
\+ "," + GroupContactColumns.GROUP\_ID + ", "  
\+ GroupContactColumns.GROUP\_CONTACT\_NAME + ", "  
\+ GroupContactColumns.GROUP\_CONTACT\_NUMBER + ", "  
\+ GroupContactColumns.GROUP\_CONTACT\_TYPE + ", "  
\+ GroupContactColumns.GROUP\_MODE\_TYPE + ", "  
\+ GroupContactColumns.GROUP\_ZONE\_ID + ", "  
\+ GroupContactColumns.GROUP\_ZONE\_NAME + ", "  
\+ GroupContactColumns.GROUP\_MODE\_TYPE + ", "  
\+ GroupContactColumns.GROUP\_DEFAULT + ");");  
  
\}  
  
/\*  
\* private void createIndexdatabase(SQLiteDatabase db)\{  
\* db.execSQL(CREATE\_INDEX\_TABLE); \}  
\*/  
  
private void createViews(SQLiteDatabase db) \{  
db.execSQL("DROP VIEW IF EXISTS " + Views.PRIVATE\_CONTACT + " ;");  
db.execSQL("DROP VIEW IF EXISTS " + Views.PRIVATE\_DATA + " ;");  
  
String data\_view\_string = "SELECT " + PrivateData.CONCRETE\_DATA\_ID  
\+ ", " + Contacts.DISPLAY\_NAME + "," + Data.CONTACT\_ID + " ,"  
\+ Data.MIMETYPE\_ID + "," + MimetypesColumns.MIMETYPE + ","  
\+ Data.DATA1 + "," + Data.DATA2 + "," + Data.DATA3 + " FROM "  
\+ Tables.PRIVATE\_DATA + " JOIN " + Tables.PRIVATE\_MIMETYPE  
\+ " ON (" + PrivateData.CONCRETE\_DATA\_MIMETYPE\_ID + " = "  
\+ PrivateMimeType.CONCRETE\_MIMETYPE\_ID + " )" + " JOIN "  
\+ Tables.PRIVATE\_CONTACT + " ON ("  
\+ PrivateData.CONCRETE\_DATA\_CONTACT\_ID + " = "  
\+ PrivateContact.CONCRETE\_CONTACT\_ID + " )";  
Utils.logProvider(data\_view\_string);  
db.execSQL("CREATE VIEW " + Views.PRIVATE\_DATA + " AS "  
\+ data\_view\_string);  
  
String contact\_view\_string = "SELECT " + ContactsColumns.\_ID + ","  
\+ Contacts.DISPLAY\_NAME + "," + Contacts.SORT\_KEY + ","  
\+ Contacts.CURRENT\_MODE + "," + Contacts.CONTACTS\_PCTOOLS + ","+ Contacts.CONTACTS\_TYPE + ","  
\+ Contacts.SEARCH\_INDEX\_NAME + "," + Contacts.NUMBER + " FROM "  
\+ Tables.PRIVATE\_CONTACT;  
Utils.logProvider(contact\_view\_string);  
db.execSQL("CREATE VIEW " + Views.PRIVATE\_CONTACT + " AS "  
\+ contact\_view\_string);  
\}  
  
private void dropAll(SQLiteDatabase db) \{  
db.execSQL("DROP TABLE IF EXISTS " + Tables.PRIVATE\_CONTACT);  
db.execSQL("DROP TABLE IF EXISTS " + Tables.PRIVATE\_DATA);  
db.execSQL("DROP TABLE IF EXISTS " + Tables.PRIVATE\_MIMETYPE);  
db.execSQL("DROP TABLE IF EXISTS " + Tables.PRIVATE\_CALL);  
db.execSQL("DROP TABLE IF EXISTS " + Tables.PRIVATE\_GROUP\_CONTACT);  
db.execSQL("DROP TABLE IF EXISTS " + Tables.PRIVATE\_ZONE);  
\}  
  
@Override  
public SQLiteDatabase getWritableDatabase() \{  
final SQLiteDatabase db;  
if (mDefaultWritableDatabase != null) \{  
db = mDefaultWritableDatabase;  
\} else \{  
db = super.getWritableDatabase();  
\}  
return db;  
\}  
  
\}