Android数据库设计

一、SQLITE数据库字段类型

　　NULL: 表示一个NULL值

　　INTEGER: 用来存储一个整数,根据大小可以使用1,2,3,4,6,8位来存储.

　　REAL: IEEE 浮点数

　　TEXT: 按照字符串来存储

　　BLOB: 按照二进制值存储,不做任何改变.

      id自增长字段：id integer primary key autoincrement

二、SQLITE的CRUD操作

1. insert操作

   1	ContentValues values=new ContentValues();

   2	values.put("name", person.getName());

   3	database.insert("person", null, values);

2. update操作
   

   1	ContentValues values=new ContentValues();

   2	values.put("name", person.getName());

   3	database.update("person", values, "personid=?", new String[]{String.valueOf(person.getPersonId())});

   
   

3. select操作
   

   1	Cursor cursor=database.query("person", null, null, null, null, null, null, offerset+","+maxResult);

   2	while(cursor.moveToNext()){

   3	int personIda =cursor.getInt(cursor.getColumnIndex("personid"));

   4	String name=cursor.getString(cursor.getColumnIndex("name"));

   5	Person person=new Person(personIda,name);

   6	persons.add(person);

   7

   }

   
   
   
   
4. delete操作       

    

1	database.delete("person", "personid=?", new String[]{String.valueOf(personId)});

 三、数据库版本问题

为防止应用的数据库版本变更，每次发布的应用都应指定相应的数据库版本；

如果数据库结构有变动，那么针对版本的变更进行相关升级操作

四、数据库事务操作

database.beginTransaction();//开启事务
database.setTransactionSuccessful();//当操作成功的时候把事务状态改为成功状态
database.endTransaction();//提交事务  会根据事务的状态来提交事务还是回滚事务

附wordpress的数据库类源码

view sourceprint?

0001	package org.wordpress.android;

0002

0003	import java.text.StringCharacterIterator;

0004	import java.util.HashMap;

0005	import java.util.Vector;

0006

0007	import android.content.ContentValues;

0008	import android.content.Context;

0009	import android.content.Intent;

0010	import android.database.Cursor;

0011	import android.database.SQLException;

0012	import android.database.sqlite.SQLiteDatabase;

0013

0014	public class WordPressDB {

0015

0016	private static final int DATABASE_VERSION = 9;

0017

0018	private static final String CREATE_TABLE_SETTINGS = "create table if not exists accounts (id integer primary key autoincrement, "

0019	+ "url text, blogName text, username text, password text, imagePlacement text, centerThumbnail boolean, fullSizeImage boolean, maxImageWidth text, maxImageWidthId integer, lastCommentId integer, runService boolean);";

0020	private static final String CREATE_TABLE_EULA = "create table if not exists eula (id integer primary key autoincrement, "

0021	+ "read integer not null, interval text, statsdate integer);";

0022	private static final String SETTINGS_TABLE = "accounts";

0023	private static final String DATABASE_NAME = "wordpress";

0024

0025	//localDrafts

0026	private static final String CREATE_TABLE_LOCALDRAFTS = "create table if not exists localdrafts (id integer primary key autoincrement, blogID text, uploaded boolean, title text,content text, picturePaths text, tags text, categories text, publish boolean);";

0027	private static final String CREATE_TABLE_LOCALPAGEDRAFTS = "create table if not exists localpagedrafts (id integer primary key autoincrement, blogID text, uploaded boolean, title text,content text, picturePaths text, publish boolean);";

0028

0029	private static final String LOCALDRAFTS_TABLE = "localdrafts";

0030	private static final String LOCALPAGEDRAFTS_TABLE = "localpagedrafts";

0031

0032	private static final String ADD_LATITUDE = "alter table localdrafts add latitude real";

0033	private static final String ADD_LONGITUDE = "alter table localdrafts add longitude real";

0034

0035	private static final String ADD_STATUS = "alter table localdrafts add status text";

0036	private static final String ADD_PAGE_STATUS = "alter table localpagedrafts add status text";

0037

0038	//postStore

0039	private static final String CREATE_TABLE_POSTSTORE = "create table if not exists poststore (blogID text, postID text, title text, postDate text, postDateFormatted text);";

0040	private static final String CREATE_TABLE_PAGES = "create table if not exists pages (blogID text, pageID text, parentID text, title text, pageDate text, pageDateFormatted text);";

0041	private static final String CREATE_TABLE_COMMENTS = "create table if not exists comments (blogID text, postID text, iCommentID integer, author text, comment text, commentDate text, commentDateFormatted text, status text, url text, email text, postTitle text);";

0042	private static final String POSTSTORE_TABLE = "poststore";

0043	private static final String PAGES_TABLE = "pages";

0044	private static final String COMMENTS_TABLE = "comments";

0045

0046

//eula

0047	private static final String EULA_TABLE = "eula";

0048

0049	//categories

0050	private static final String CREATE_TABLE_CATEGORIES = "create table if not exists cats (id integer primary key autoincrement, "

0051	+ "blog_id text, wp_id integer, category_name text not null);";

0052	private static final String CATEGORIES_TABLE = "cats";

0053

0054	//for capturing blogID, trac ticket #

0055	private static final String ADD_BLOGID = "alter table accounts add blogId integer;";

0056	private static final String UPDATE_BLOGID = "update accounts set blogId = 1;"; //set them all to 1 if updating

0057

0058	//add notification options

0059	private static final String ADD_SOUND_OPTION = "alter table eula add sound boolean default false;";

0060	private static final String ADD_VIBRATE_OPTION = "alter table eula add vibrate boolean default false;";

0061	private static final String ADD_LIGHT_OPTION = "alter table eula add light boolean default false;";

0062	private static final String ADD_TAGLINE = "alter table eula add tagline text;";

0063	private static final String ADD_TAGLINE_FLAG = "alter table eula add tagline_flag boolean default false;";

0064

0065	//for capturing blogID, trac ticket #

0066	private static final String ADD_LOCATION_FLAG = "alter table accounts add location boolean default false;";

0067

0068	//fix commentID data type

0069	private static final String ADD_NEW_COMMENT_ID = "ALTER TABLE comments ADD iCommentID INTEGER;";

0070	private static final String COPY_COMMENT_IDS = "UPDATE comments SET iCommentID = commentID;";

0071

0072	//add wordpress.com stats login info

0073	private static final String ADD_DOTCOM_USERNAME = "alter table accounts add dotcom_username text;";

0074	private static final String ADD_DOTCOM_PASSWORD = "alter table accounts add dotcom_password text;";

0075	private static final String ADD_API_KEY = "alter table accounts add api_key text;";

0076	private static final String ADD_API_BLOGID = "alter table accounts add api_blogid text;";

0077

0078	//add wordpress.com flag and version column

0079	private static final String ADD_DOTCOM_FLAG = "alter table accounts add dotcomFlag boolean default false;";

0080	private static final String ADD_WP_VERSION = "alter table accounts add wpVersion text;";

0081

0082	//add new unique identifier to no longer use device imei

0083	private static final String ADD_UNIQUE_ID = "alter table eula add uuid text;";

0084

0085	//add new table for QuickPress homescreen shortcuts

0086	private static final String CREATE_TABLE_QUICKPRESS_SHORTCUTS = "create table if not exists quickpress_shortcuts (id integer primary key autoincrement, accountId text, name text);";

0087	private static final String QUICKPRESS_SHORTCUTS_TABLE = "quickpress_shortcuts";

0088

0089	private SQLiteDatabase db;

0090

0091	public WordPressDB(Context ctx) {

0092	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0093	//db.execSQL("DROP TABLE IF EXISTS "+ SETTINGS_TABLE);

0094	db.execSQL(CREATE_TABLE_SETTINGS);

0095	//added eula to this class to fix trac #49

0096	db.execSQL(CREATE_TABLE_EULA);

0097	//int test = db.getVersion();

0098

0099	db.execSQL(CREATE_TABLE_LOCALDRAFTS);

0100	db.execSQL(CREATE_TABLE_LOCALPAGEDRAFTS);

0101

0102	db.execSQL(CREATE_TABLE_POSTSTORE);

0103	db.execSQL(CREATE_TABLE_PAGES);

0104	db.execSQL(CREATE_TABLE_COMMENTS);

0105

0106	db.execSQL(CREATE_TABLE_CATEGORIES);

0107

0108	db.execSQL(CREATE_TABLE_QUICKPRESS_SHORTCUTS);

0109

0110

try {

0111	if (db.getVersion() < 1){ //user is new install

0112	db.execSQL(ADD_BLOGID);

0113	db.execSQL(UPDATE_BLOGID);

0114	db.execSQL(ADD_SOUND_OPTION);

0115	db.execSQL(ADD_VIBRATE_OPTION);

0116	db.execSQL(ADD_LIGHT_OPTION);

0117	db.execSQL(ADD_LOCATION_FLAG);

0118	db.execSQL(ADD_LATITUDE);

0119	db.execSQL(ADD_LONGITUDE);

0120	db.execSQL(ADD_TAGLINE);

0121	db.execSQL(ADD_TAGLINE_FLAG);

0122	db.execSQL(ADD_DOTCOM_USERNAME);

0123	db.execSQL(ADD_DOTCOM_PASSWORD);

0124	db.execSQL(ADD_API_KEY);

0125	db.execSQL(ADD_API_BLOGID);

0126	db.execSQL(ADD_DOTCOM_FLAG);

0127	db.execSQL(ADD_WP_VERSION);

0128	db.execSQL(ADD_UNIQUE_ID);

0129	db.execSQL(ADD_STATUS);

0130	db.execSQL(ADD_PAGE_STATUS);

0131	db.setVersion(DATABASE_VERSION); //set to latest revision

0132

}

0133	else if (db.getVersion() == 1){ //v1.0 or v1.0.1

0134	db.execSQL(ADD_BLOGID);

0135	db.execSQL(UPDATE_BLOGID);

0136	db.execSQL(ADD_SOUND_OPTION);

0137	db.execSQL(ADD_VIBRATE_OPTION);

0138	db.execSQL(ADD_LIGHT_OPTION);

0139	db.execSQL(ADD_LOCATION_FLAG);

0140	db.execSQL(ADD_LATITUDE);

0141	db.execSQL(ADD_LONGITUDE);

0142	db.execSQL(ADD_TAGLINE);

0143	db.execSQL(ADD_TAGLINE_FLAG);

0144	db.execSQL(ADD_NEW_COMMENT_ID);

0145	db.execSQL(COPY_COMMENT_IDS);

0146	db.execSQL(ADD_DOTCOM_USERNAME);

0147	db.execSQL(ADD_DOTCOM_PASSWORD);

0148	db.execSQL(ADD_API_KEY);

0149	db.execSQL(ADD_API_BLOGID);

0150	db.execSQL(ADD_DOTCOM_FLAG);

0151	db.execSQL(ADD_WP_VERSION);

0152	db.execSQL(ADD_UNIQUE_ID);

0153	db.execSQL(ADD_STATUS);

0154	db.execSQL(ADD_PAGE_STATUS);

0155	db.setVersion(DATABASE_VERSION); //set to latest revision

0156

}

0157	else if (db.getVersion()  == 2){

0158	db.execSQL(ADD_SOUND_OPTION);

0159	db.execSQL(ADD_VIBRATE_OPTION);

0160	db.execSQL(ADD_LIGHT_OPTION);

0161	db.execSQL(ADD_LOCATION_FLAG);

0162	db.execSQL(ADD_LATITUDE);

0163	db.execSQL(ADD_LONGITUDE);

0164	db.execSQL(ADD_TAGLINE);

0165	db.execSQL(ADD_TAGLINE_FLAG);

0166	db.execSQL(ADD_NEW_COMMENT_ID);

0167	db.execSQL(COPY_COMMENT_IDS);

0168	db.execSQL(ADD_DOTCOM_USERNAME);

0169	db.execSQL(ADD_DOTCOM_PASSWORD);

0170	db.execSQL(ADD_API_KEY);

0171	db.execSQL(ADD_API_BLOGID);

0172	db.execSQL(ADD_DOTCOM_FLAG);

0173	db.execSQL(ADD_WP_VERSION);

0174	db.execSQL(ADD_UNIQUE_ID);

0175	db.execSQL(ADD_STATUS);

0176	db.execSQL(ADD_PAGE_STATUS);

0177	db.setVersion(DATABASE_VERSION);

0178

}

0179	else if (db.getVersion() == 3){

0180	db.execSQL(ADD_LOCATION_FLAG);

0181	db.execSQL(ADD_LATITUDE);

0182	db.execSQL(ADD_LONGITUDE);

0183	db.execSQL(ADD_TAGLINE);

0184	db.execSQL(ADD_TAGLINE_FLAG);

0185	db.execSQL(ADD_NEW_COMMENT_ID);

0186	db.execSQL(COPY_COMMENT_IDS);

0187	db.execSQL(ADD_DOTCOM_USERNAME);

0188	db.execSQL(ADD_DOTCOM_PASSWORD);

0189	db.execSQL(ADD_API_KEY);

0190	db.execSQL(ADD_API_BLOGID);

0191	db.execSQL(ADD_DOTCOM_FLAG);

0192	db.execSQL(ADD_WP_VERSION);

0193	db.execSQL(ADD_UNIQUE_ID);

0194	db.execSQL(ADD_STATUS);

0195	db.execSQL(ADD_PAGE_STATUS);

0196	db.setVersion(DATABASE_VERSION);

0197

}

0198	else if (db.getVersion() == 4){

0199	db.execSQL(ADD_LOCATION_FLAG);

0200	db.execSQL(ADD_LATITUDE);

0201	db.execSQL(ADD_LONGITUDE);

0202	db.execSQL(ADD_TAGLINE);

0203	db.execSQL(ADD_TAGLINE_FLAG);

0204	db.execSQL(ADD_NEW_COMMENT_ID);

0205	db.execSQL(COPY_COMMENT_IDS);

0206	db.execSQL(ADD_DOTCOM_USERNAME);

0207	db.execSQL(ADD_DOTCOM_PASSWORD);

0208	db.execSQL(ADD_API_KEY);

0209	db.execSQL(ADD_API_BLOGID);

0210	db.execSQL(ADD_DOTCOM_FLAG);

0211	db.execSQL(ADD_WP_VERSION);

0212	db.execSQL(ADD_UNIQUE_ID);

0213	db.execSQL(ADD_STATUS);

0214	db.execSQL(ADD_PAGE_STATUS);

0215	db.setVersion(DATABASE_VERSION);

0216

}

0217	else if (db.getVersion() == 5){

0218	db.execSQL(ADD_TAGLINE);

0219	db.execSQL(ADD_TAGLINE_FLAG);

0220	db.execSQL(ADD_NEW_COMMENT_ID);

0221	db.execSQL(COPY_COMMENT_IDS);

0222	db.execSQL(ADD_DOTCOM_USERNAME);

0223	db.execSQL(ADD_DOTCOM_PASSWORD);

0224	db.execSQL(ADD_API_KEY);

0225	db.execSQL(ADD_API_BLOGID);

0226	db.execSQL(ADD_DOTCOM_FLAG);

0227	db.execSQL(ADD_WP_VERSION);

0228	db.execSQL(ADD_UNIQUE_ID);

0229	db.execSQL(ADD_STATUS);

0230	db.execSQL(ADD_PAGE_STATUS);

0231	db.setVersion(DATABASE_VERSION);

0232

}

0233	else if (db.getVersion() == 6){

0234	db.execSQL(ADD_NEW_COMMENT_ID);

0235	db.execSQL(COPY_COMMENT_IDS);

0236	db.execSQL(ADD_DOTCOM_USERNAME);

0237	db.execSQL(ADD_DOTCOM_PASSWORD);

0238	db.execSQL(ADD_API_KEY);

0239	db.execSQL(ADD_API_BLOGID);

0240	db.execSQL(ADD_DOTCOM_FLAG);

0241	db.execSQL(ADD_WP_VERSION);

0242	db.execSQL(ADD_UNIQUE_ID);

0243	db.execSQL(ADD_STATUS);

0244	db.execSQL(ADD_PAGE_STATUS);

0245	db.setVersion(DATABASE_VERSION);

0246

}

0247	else if (db.getVersion() == 7){

0248	db.execSQL(ADD_UNIQUE_ID);

0249	db.execSQL(ADD_STATUS);

0250	db.execSQL(ADD_PAGE_STATUS);

0251	db.setVersion(DATABASE_VERSION);

0252

}

0253	else if (db.getVersion() == 8){

0254	db.execSQL(ADD_STATUS);

0255	db.execSQL(ADD_PAGE_STATUS);

0256	db.setVersion(DATABASE_VERSION);

0257

}

0258	} catch (SQLException e) {

0259	e.printStackTrace();

0260

}

0261	db.close();

0262

0263

}

0264

0265

0266	public boolean addAccount(Context ctx, String url, String blogName, String username, String password, String imagePlacement, boolean centerThumbnail, boolean fullSizeImage, String maxImageWidth, int maxImageWidthId, boolean runService, int blogId, boolean wpcom, String wpVersion) {

0267	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0268	ContentValues values = new ContentValues();

0269	values.put("url", url);

0270	values.put("blogName", blogName);

0271	values.put("username", username);

0272	values.put("password", password);

0273	values.put("imagePlacement", imagePlacement);

0274	values.put("centerThumbnail", centerThumbnail);

0275	values.put("fullSizeImage", fullSizeImage);

0276	values.put("maxImageWidth", maxImageWidth);

0277	values.put("maxImageWidthId", maxImageWidthId);

0278	values.put("runService", runService);

0279	values.put("blogId", blogId);

0280	values.put("dotcomFlag", wpcom);

0281	values.put("wpVersion", wpVersion);

0282	boolean returnValue = db.insert(SETTINGS_TABLE, null, values) > 0;

0283	db.close();

0284	return (returnValue);

0285

}

0286	public Vector<HashMap<String, Object>> getAccounts(Context ctx) {

0287	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0288	Cursor c = db.query(SETTINGS_TABLE, new String[] { "id", "blogName", "username", "runService", "blogId", "url"}, null, null, null, null, null);

0289	String id;

0290	String blogName, username, url;

0291	int blogId;

0292	int runService;

0293	int numRows = c.getCount();

0294	c.moveToFirst();

0295	Vector<HashMap<String, Object>> accounts = new Vector<HashMap<String, Object>>();

0296	for (int i = 0; i < numRows; i++) {

0297

0298	id = c.getString(0);

0299	blogName = c.getString(1);

0300	username = c.getString(2);

0301	runService = c.getInt(3);

0302	blogId = c.getInt(4);

0303	url = c.getString(5);

0304	if (id != null)

0305

{

0306	HashMap<String, Object> thisHash = new HashMap<String, Object>();

0307

0308	thisHash.put("id", id);

0309	thisHash.put("blogName", blogName);

0310	thisHash.put("username", username);

0311	thisHash.put("runService", runService);

0312	thisHash.put("blogId", blogId);

0313	thisHash.put("url", url);

0314	accounts.add(thisHash);

0315

}

0316	c.moveToNext();

0317

}

0318	c.close();

0319	db.close();

0320

0321	return accounts;

0322

}

0323

0324	public boolean checkMatch(Context ctx, String blogName, String blogURL, String username) {

0325	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0326	Cursor c = db.query(SETTINGS_TABLE, new String[] {"blogName", "url"}, "blogName='" + addSlashes(blogName) + "' AND url='" + addSlashes(blogURL) + "'" + " AND username='" + username + "'", null, null, null, null);

0327	int numRows = c.getCount();

0328	boolean result = false;

0329

0330	if (numRows > 0){

0331	//this account is already saved, yo!

0332	result = true;

0333

}

0334

0335	c.close();

0336	db.close();

0337

0338	return result;

0339

}

0340

0341	public static String addSlashes( String text ){

0342	final StringBuffer sb                   = new StringBuffer( text.length() * 2 );

0343	final StringCharacterIterator iterator  = new StringCharacterIterator( text );

0344

0345	char character = iterator.current();

0346

0347	while( character != StringCharacterIterator.DONE ){

0348	if( character == '"' ) sb.append( "\\\"" );

0349	else if( character == '\'' ) sb.append( "\'\'" );

0350	else if( character == '\\' ) sb.append( "\\\\" );

0351	else if( character == '\n' ) sb.append( "\\n" );

0352	else if( character == '{'  ) sb.append( "\\{" );

0353	else if( character == '}'  ) sb.append( "\\}" );

0354	else sb.append( character );

0355

0356	character = iterator.next();

0357

}

0358

0359	return sb.toString();

0360

}

0361

0362	public boolean saveSettings(Context ctx, String id, String url, String username, String password, String imagePlacement, boolean centerThumbnail, boolean fullSizeImage, String maxImageWidth, int maxImageWidthId, boolean location, boolean isWPCom, String originalUsername) {

0363	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0364	ContentValues values = new ContentValues();

0365	values.put("url", url);

0366	values.put("username", username);

0367	values.put("password", password);

0368	values.put("imagePlacement", imagePlacement);

0369	values.put("centerThumbnail", centerThumbnail);

0370	values.put("fullSizeImage", fullSizeImage);

0371	values.put("maxImageWidth", maxImageWidth);

0372	values.put("maxImageWidthId", maxImageWidthId);

0373	values.put("location", location);

0374	boolean returnValue = db.update(SETTINGS_TABLE, values, "id=" + id, null) > 0;

0375	if (isWPCom){

0376	//update the login for other wordpress.com accounts

0377	ContentValues userPass = new ContentValues();

0378	userPass.put("username", username);

0379	userPass.put("password", password);

0380	returnValue = db.update(SETTINGS_TABLE, userPass, "username=\"" + originalUsername + "\" AND dotcomFlag=1" , null) > 0;

0381

}

0382	db.close();

0383	return (returnValue);

0384

}

0385

0386	public boolean deleteAccount(Context ctx, String id) {

0387	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0388	int rowsAffected = db.delete(SETTINGS_TABLE, "id=" + id, null);

0389

0390	boolean returnValue = false;

0391	if (rowsAffected > 0){

0392	returnValue = true;

0393

}

0394

0395	// delete QuickPress homescreen shortcuts connected with this account

0396	Vector<HashMap<String, Object>> shortcuts = this.getQuickPressShortcuts(ctx, id);

0397	for(int i = 0; i < shortcuts.size(); i++) {

0398	HashMap<String, Object> shortcutHash = shortcuts.get(i);

0399

0400	Intent shortcutIntent = new Intent();

0401	shortcutIntent.setClassName(editPost.class.getPackage().getName(), editPost.class.getName());

0402	shortcutIntent.addFlags(Intent.FLAG_ACTIVITY_NEW_TASK);

0403	shortcutIntent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP);

0404	shortcutIntent.setAction(Intent.ACTION_VIEW);

0405	Intent broadcastShortcutIntent = new Intent();

0406	broadcastShortcutIntent.putExtra(Intent.EXTRA_SHORTCUT_INTENT, shortcutIntent);

0407	broadcastShortcutIntent.putExtra(Intent.EXTRA_SHORTCUT_NAME, shortcutHash.get("name").toString());

0408	broadcastShortcutIntent.putExtra("duplicate", false);

0409	broadcastShortcutIntent.setAction("com.android.launcher.action.UNINSTALL_SHORTCUT");

0410	ctx.sendBroadcast(broadcastShortcutIntent);

0411

0412	deleteQuickPressShortcut(ctx, shortcutHash.get("id").toString());

0413

}

0414

0415	db.close();

0416	return (returnValue);

0417

}

0418

0419	public Vector<Object> loadSettings(Context ctx, String id) {

0420	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0421

0422	Cursor c = db.query(SETTINGS_TABLE, new String[] { "url", "blogName", "username", "password", "imagePlacement", "centerThumbnail", "fullSizeImage", "maxImageWidth", "maxImageWidthId", "runService", "blogId", "location", "dotcomFlag"}, "id=" + id, null, null, null, null);

0423

0424	int numRows = c.getCount();

0425	c.moveToFirst();

0426

0427	Vector<Object> returnVector = new Vector<Object>();

0428	if (numRows > 0){

0429	if (c.getString(0) != null){

0430	returnVector.add(c.getString(0));

0431	returnVector.add(c.getString(1));

0432	returnVector.add(c.getString(2));

0433	returnVector.add(c.getString(3));

0434	returnVector.add(c.getString(4));

0435	returnVector.add(c.getInt(5));

0436	returnVector.add(c.getString(6));

0437	returnVector.add(c.getString(7));

0438	returnVector.add(c.getInt(8));

0439	returnVector.add(c.getInt(9));

0440	returnVector.add(c.getInt(10));

0441	returnVector.add(c.getInt(11));

0442	returnVector.add(c.getInt(12));

0443

}

0444

else

0445

{

0446	returnVector = null;

0447

}

0448

}

0449	c.close();

0450	db.close();

0451

0452	return returnVector;

0453

}

0454

0455	public Vector<String> loadStatsLogin(Context ctx, String id) {

0456	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0457

0458	Cursor c = db.query(SETTINGS_TABLE, new String[] { "dotcom_username", "dotcom_password"}, "id=" + id, null, null, null, null);

0459

0460	c.moveToFirst();

0461

0462	Vector<String> returnVector = new Vector<String>();

0463	if (c.getString(0) != null){

0464	returnVector.add(c.getString(0));

0465	returnVector.add(c.getString(1));

0466

}

0467

else

0468

{

0469	returnVector = null;

0470

}

0471	c.close();

0472	db.close();

0473

0474	return returnVector;

0475

}

0476

0477	public boolean saveStatsLogin(Context ctx, String id, String statsUsername, String statsPassword) {

0478	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0479	ContentValues values = new ContentValues();

0480	values.put("dotcom_username", statsUsername);

0481	values.put("dotcom_password", statsPassword);

0482	boolean returnValue = db.update(SETTINGS_TABLE, values, "id=" + id, null) > 0;

0483	db.close();

0484

0485	return (returnValue);

0486

0487

}

0488

0489	public Vector<String> loadAPIData(Context ctx, String id) {

0490	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0491

0492	Cursor c = db.query(SETTINGS_TABLE, new String[] { "api_key", "api_blogid"}, "id=" + id, null, null, null, null);

0493

0494	c.moveToFirst();

0495

0496	Vector<String> returnVector = new Vector<String>();

0497	if (c.getString(0) != null){

0498	returnVector.add(c.getString(0));

0499	returnVector.add(c.getString(1));

0500

}

0501

else

0502

{

0503	returnVector = null;

0504

}

0505	c.close();

0506	db.close();

0507

0508	return returnVector;

0509

}

0510

0511	public boolean saveAPIData(Context ctx, String id, String apiKey, String apiBlogID) {

0512	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0513	ContentValues values = new ContentValues();

0514	values.put("api_key", apiKey);

0515	values.put("api_blogid", apiBlogID);

0516	boolean returnValue = db.update(SETTINGS_TABLE, values, "id=" + id, null) > 0;

0517	db.close();

0518

0519	return (returnValue);

0520

0521

}

0522

0523	public int getLatestCommentID(Context ctx, String id) {

0524	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0525	int returnInt = 0;

0526	Cursor c = db.query(SETTINGS_TABLE, new String[] { "lastCommentId"  }, "id=" + id, null, null, null, null);

0527	c.moveToFirst();

0528	if (c.getString(0) != null){

0529	returnInt = Integer.valueOf(c.getString(0));

0530

}

0531	c.close();

0532	db.close();

0533	return returnInt;

0534

}

0535

0536

0537	public boolean updateLatestCommentID(Context ctx, String id, Integer newCommentID) {

0538	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0539	ContentValues values = new ContentValues();

0540	values.put("lastCommentId", newCommentID);

0541

0542	boolean returnValue = db.update(SETTINGS_TABLE, values, "id=" + id, null) > 0;

0543	db.close();

0544	return (returnValue);

0545

0546

}

0547

0548

0549	public Vector<Integer> getNotificationAccounts(Context ctx) {

0550	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0551

0552	Cursor c = null;

0553

try {

0554	c = db.query(SETTINGS_TABLE, new String[] { "id" }, "runService=1", null, null, null, null);

0555	} catch (Exception e) {

0556	e.printStackTrace();

0557

}

0558

0559	int numRows = c.getCount();

0560	c.moveToFirst();

0561

0562	Vector<Integer> returnVector = new Vector<Integer>();

0563	for (int i = 0; i < numRows; ++i) {

0564	int tempID = c.getInt(0);

0565	returnVector.add(tempID);

0566	c.moveToNext();

0567

}

0568

0569	c.close();

0570	db.close();

0571	return returnVector;

0572

}

0573

0574

0575	public String getAccountName(Context ctx, String accountID) {

0576	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0577	String accountName = "";

0578	Cursor c = db.query(SETTINGS_TABLE, new String[] { "blogName"  }, "id=" + accountID, null, null, null, null);

0579	c.moveToFirst();

0580	if (c.getString(0) != null){

0581	accountName = c.getString(0);

0582

}

0583	c.close();

0584	db.close();

0585	return accountName;

0586

}

0587

0588

0589	public void updateNotificationFlag(Context ctx, int id, boolean flag) {

0590	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0591	ContentValues values = new ContentValues();

0592	int iFlag = 0;

0593	if (flag){

0594	iFlag = 1;

0595

}

0596	values.put("runService", iFlag);

0597

0598	boolean returnValue = db.update(SETTINGS_TABLE, values, "id=" + String.valueOf(id), null) > 0;

0599	if(returnValue){

0600

}

0601	db.close();

0602

0603

}

0604

0605	public void updateNotificationSettings(Context ctx, String interval, boolean sound, boolean vibrate, boolean light, boolean tagline_flag, String tagline) {

0606	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0607	ContentValues values = new ContentValues();

0608	values.put("interval", interval);

0609	values.put("sound", sound);

0610	values.put("vibrate", vibrate);

0611	values.put("light", light);

0612	values.put("tagline_flag", tagline_flag);

0613	values.put("tagline", tagline);

0614

0615	boolean returnValue = db.update("eula", values, null, null) > 0;

0616	if (returnValue){};

0617	db.close();

0618

0619

}

0620

0621	public String getInterval(Context ctx) {

0622	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0623

0624	Cursor c = db.query("eula", new String[] { "interval" }, "id=0", null, null, null, null);

0625	int numRows = c.getCount();

0626	c.moveToFirst();

0627	String returnValue = "";

0628	if (numRows == 1){

0629	if (c.getString(0) != null){

0630	returnValue = c.getString(0);

0631

}

0632

}

0633	c.close();

0634	db.close();

0635

0636	return returnValue;

0637

0638

}

0639

0640	public HashMap<String, Object> getNotificationOptions(Context ctx) {

0641	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0642	Cursor c = db.query("eula", new String[] { "id", "sound", "vibrate", "light", "tagline_flag", "tagline"}, "id=0", null, null, null, null);

0643	int sound, vibrate, light;

0644	String tagline;

0645	HashMap<String, Object> thisHash = new HashMap<String, Object>();

0646	int numRows = c.getCount();

0647	if (numRows >= 1){

0648	c.moveToFirst();

0649

0650	sound = c.getInt(1);

0651	vibrate = c.getInt(2);

0652	light = c.getInt(3);

0653	tagline = c.getString(5);

0654	thisHash.put("sound", sound);

0655	thisHash.put("vibrate", vibrate);

0656	thisHash.put("light", light);

0657	thisHash.put("tagline_flag", c.getInt(4));

0658	if (tagline != null){

0659	thisHash.put("tagline", tagline);

0660

}

0661

else{

0662	thisHash.put("tagline", "");

0663

}

0664

0665

0666

}

0667

0668	c.close();

0669	db.close();

0670

0671	return thisHash;

0672

}

0673

0674	//localDrafts

0675	public boolean saveLocalDraft(Context ctx, String blogID, String title, String content, String picturePaths, String tags, String categories, String status, Double latitude, Double longitude) {

0676	boolean returnValue = false;

0677	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0678

0679	ContentValues values = new ContentValues();

0680	values.put("blogID", blogID);

0681	values.put("title", title);

0682	values.put("content", content);

0683	values.put("picturePaths", picturePaths);

0684	values.put("tags", tags);

0685	values.put("categories", categories);

0686	values.put("status", status);

0687	values.put("latitude", latitude);

0688	values.put("longitude", longitude);

0689	returnValue = db.insert(LOCALDRAFTS_TABLE, null, values) > 0;

0690

0691	db.close();

0692	return (returnValue);

0693

}

0694

0695	public boolean updateLocalDraft(Context ctx, String blogID, String postID, String title, String content, String picturePaths, String tags, String categories, String status, Double latitude, Double longitude) {

0696	boolean returnValue = false;

0697	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0698

0699	ContentValues values = new ContentValues();

0700	values.put("blogID", blogID);

0701	values.put("title", title);

0702	values.put("content", content);

0703	values.put("picturePaths", picturePaths);

0704	values.put("tags", tags);

0705	values.put("categories", categories);

0706	values.put("status", status);

0707	values.put("latitude", latitude);

0708	values.put("longitude", longitude);

0709	returnValue = db.update(LOCALDRAFTS_TABLE, values, "id=" + postID, null) > 0;

0710

0711	db.close();

0712	return (returnValue);

0713

}

0714

0715	public Vector<HashMap<String, Object>> loadPosts(Context ctx, String blogID) {

0716	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0717	Vector<HashMap<String, Object>> returnVector = new Vector<HashMap<String, Object>>();

0718	Cursor c = db.query(LOCALDRAFTS_TABLE, new String[] { "id", "title", "status", "uploaded"}, "blogID=" + blogID, null, null, null, "id desc");

0719	int numRows = c.getCount();

0720	c.moveToFirst();

0721

0722	for (int i = 0; i < numRows; ++i) {

0723	if (c.getString(0) != null){

0724	HashMap<String, Object> returnHash = new HashMap<String, Object>();

0725	returnHash.put("id", c.getInt(0));

0726	returnHash.put("title", c.getString(1));

0727	returnHash.put("status", c.getString(2));

0728	returnHash.put("uploaded", c.getInt(3));

0729	returnVector.add(i, returnHash);

0730

}

0731	c.moveToNext();

0732

}

0733	c.close();

0734	db.close();

0735

0736	if (numRows == 0){

0737	returnVector = null;

0738

}

0739

0740	return returnVector;

0741

}

0742

0743	public Vector<HashMap<String, Object>> loadPost(Context ctx, String postID) {

0744	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0745	Vector<HashMap<String, Object>> returnVector = new Vector<HashMap<String, Object>>();

0746	Cursor c = db.query(LOCALDRAFTS_TABLE, new String[] { "title", "content", "picturePaths", "tags", "categories", "status", "latitude", "longitude"}, "id=" + postID, null, null, null, null);

0747

0748	int numRows = c.getCount();

0749	c.moveToFirst();

0750

0751	for (int i = 0; i < numRows; ++i) {

0752	if (c.getString(0) != null){

0753	HashMap<String, Object> returnHash = new HashMap<String, Object>();

0754	returnHash.put("title", c.getString(0));

0755	returnHash.put("content", c.getString(1));

0756	returnHash.put("picturePaths", c.getString(2));

0757	returnHash.put("tags", c.getString(3));

0758	returnHash.put("categories", c.getString(4));

0759	returnHash.put("status", c.getString(5));

0760	returnHash.put("latitude", c.getDouble(6));

0761	returnHash.put("longitude", c.getDouble(7));

0762	returnVector.add(i, returnHash);

0763

}

0764	c.moveToNext();

0765

}

0766	c.close();

0767	db.close();

0768

0769	if (numRows == 0){

0770	returnVector = null;

0771

}

0772

0773	return returnVector;

0774

}

0775

0776	public boolean deletePost(Context ctx, String postID) {

0777	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0778

0779	boolean returnValue = false;

0780

0781	int result = 0;

0782	result = db.delete(LOCALDRAFTS_TABLE, "id=" + postID, null);

0783	db.close();

0784

0785	if (result == 1){

0786	returnValue = true;

0787

}

0788

0789	return returnValue;

0790

}

0791

0792	public boolean saveLocalPageDraft(Context ctx, String blogID, String title, String content, String picturePaths, String status) {

0793	boolean returnValue = false;

0794	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0795

0796	ContentValues values = new ContentValues();

0797	values.put("blogID", blogID);

0798	values.put("title", title);

0799	values.put("content", content);

0800	values.put("picturePaths", picturePaths);

0801	values.put("status", status);

0802	returnValue = db.insert(LOCALPAGEDRAFTS_TABLE, null, values) > 0;

0803

0804	db.close();

0805	return (returnValue);

0806

}

0807

0808	public boolean updateLocalPageDraft(Context ctx, String blogID, String postID, String title, String content, String picturePaths, String status) {

0809	boolean returnValue = false;

0810	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0811

0812	ContentValues values = new ContentValues();

0813	values.put("blogID", blogID);

0814	values.put("title", title);

0815	values.put("content", content);

0816	values.put("picturePaths", picturePaths);

0817	values.put("status", status);

0818	returnValue = db.update(LOCALPAGEDRAFTS_TABLE, values, "id=" + postID, null) > 0;

0819

0820	db.close();

0821	return (returnValue);

0822

}

0823

0824	public Vector<HashMap<String, Object>> loadPageDrafts(Context ctx, String blogID) {

0825	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0826	Vector<HashMap<String, Object>> returnVector = new Vector<HashMap<String, Object>>();

0827	Cursor c = db.query(LOCALPAGEDRAFTS_TABLE, new String[] { "id", "title", "status", "uploaded"}, "blogID=" + blogID, null, null, null, "id desc");

0828	int numRows = c.getCount();

0829	c.moveToFirst();

0830

0831	for (int i = 0; i < numRows; ++i) {

0832	if (c.getString(0) != null){

0833	HashMap<String, Object> returnHash = new HashMap<String, Object>();

0834	returnHash.put("id", c.getInt(0));

0835	returnHash.put("title", c.getString(1));

0836	returnHash.put("status", c.getString(2));

0837	returnHash.put("uploaded", c.getInt(3));

0838	returnVector.add(i, returnHash);

0839

}

0840	c.moveToNext();

0841

}

0842	c.close();

0843	db.close();

0844

0845	if (numRows == 0){

0846	returnVector = null;

0847

}

0848

0849	return returnVector;

0850

}

0851

0852	public Vector<HashMap<String, Object>> loadPageDraft(Context ctx, String postID) {

0853	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0854	Vector<HashMap<String, Object>> returnVector = new Vector<HashMap<String, Object>>();

0855	Cursor c = db.query(LOCALPAGEDRAFTS_TABLE, new String[] { "title", "content", "picturePaths", "status"}, "id=" + postID, null, null, null, null);

0856

0857	int numRows = c.getCount();

0858	c.moveToFirst();

0859

0860	for (int i = 0; i < numRows; ++i) {

0861	if (c.getString(0) != null){

0862	HashMap<String, Object> returnHash = new HashMap<String, Object>();

0863	returnHash.put("title", c.getString(0));

0864	returnHash.put("content", c.getString(1));

0865	returnHash.put("picturePaths", c.getString(2));

0866	returnHash.put("status", c.getString(3));

0867	returnVector.add(i, returnHash);

0868

}

0869	c.moveToNext();

0870

}

0871	c.close();

0872	db.close();

0873

0874	if (numRows == 0){

0875	returnVector = null;

0876

}

0877

0878	return returnVector;

0879

}

0880

0881	public boolean deletePageDraft(Context ctx, String postID) {

0882	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0883

0884	boolean returnValue = false;

0885

0886	int result = 0;

0887	result = db.delete(LOCALPAGEDRAFTS_TABLE, "id=" + postID, null);

0888	db.close();

0889

0890	if (result == 1){

0891	returnValue = true;

0892

}

0893

0894	return returnValue;

0895

}

0896

0897	public int getLatestDraftID(Context ctx, String id) {

0898	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0899	Cursor c = db.query(LOCALDRAFTS_TABLE, new String[] {"id"}, "blogID=" + id, null, null, null, "id desc", "1");

0900

0901	int latestID = -1;

0902	int numRows = c.getCount();

0903	if (numRows != 0){

0904	c.moveToFirst();

0905	latestID = c.getInt(0);

0906

}

0907	c.close();

0908	db.close();

0909

0910	return latestID;

0911

}

0912

0913	public int getLatestPageDraftID(Context ctx, String id) {

0914	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0915	Cursor c = db.query(LOCALPAGEDRAFTS_TABLE, new String[] {"id"}, "blogID=" + id, null, null, null, "id desc", "1");

0916

0917	int latestID = -1;

0918	int numRows = c.getCount();

0919	if (numRows != 0){

0920	c.moveToFirst();

0921	latestID = c.getInt(0);

0922

}

0923	c.close();

0924	db.close();

0925

0926	return latestID;

0927

}

0928

0929	//postStore

0930	public boolean savePosts(Context ctx, Vector<?> postValues) {

0931	boolean returnValue = false;

0932	if (postValues.size() != 0)

0933

{

0934	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0935	HashMap<?, ?> firstHash = (HashMap<?, ?>) postValues.get(0);

0936	String blogID = firstHash.get("blogID").toString();

0937	//delete existing values

0938	db.delete(POSTSTORE_TABLE, "blogID=" + blogID, null);

0939

0940	for (int i = 0; i < postValues.size(); i++){

0941	ContentValues values = new ContentValues();

0942	HashMap<?, ?> thisHash = (HashMap<?, ?>) postValues.get(i);

0943	values.put("blogID", thisHash.get("blogID").toString());

0944	values.put("postID", thisHash.get("postID").toString());

0945	values.put("title", thisHash.get("title").toString());

0946	values.put("postDate", thisHash.get("postDate").toString());

0947	values.put("postDateFormatted", thisHash.get("postDateFormatted").toString());

0948	returnValue = db.insert(POSTSTORE_TABLE, null, values) > 0;

0949

}

0950

0951

0952	db.close();

0953

}

0954	return (returnValue);

0955

}

0956

0957	public Vector<HashMap<String, String>> loadSavedPosts(Context ctx, String blogID) {

0958	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0959	Vector<HashMap<String, String>> returnVector = new Vector<HashMap<String, String>>();

0960	Cursor c = db.query(POSTSTORE_TABLE, new String[] { "blogID", "postID", "title", "postDate", "postDateFormatted"}, "blogID=" + blogID, null, null, null, null);

0961

0962	int numRows = c.getCount();

0963	c.moveToFirst();

0964

0965	for (int i = 0; i < numRows; ++i) {

0966	if (c.getString(0) != null){

0967	HashMap<String, String> returnHash = new HashMap<String, String>();

0968	returnHash.put("blogID", c.getString(0));

0969	returnHash.put("postID", c.getString(1));

0970	returnHash.put("title", c.getString(2));

0971	returnHash.put("postDate", c.getString(3));

0972	returnHash.put("postDateFormatted", c.getString(4));

0973	returnVector.add(i, returnHash);

0974

}

0975	c.moveToNext();

0976

}

0977	c.close();

0978	db.close();

0979

0980	if (numRows == 0){

0981	returnVector = null;

0982

}

0983

0984	return returnVector;

0985

}

0986

0987	public Vector<HashMap<String, String>> loadPages(Context ctx, String blogID) {

0988	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

0989	Vector<HashMap<String, String>> returnVector = new Vector<HashMap<String, String>>();

0990	Cursor c = db.query(PAGES_TABLE, new String[] { "blogID", "pageID", "title", "pageDate", "pageDateFormatted"}, "blogID=" + blogID, null, null, null, null);

0991

0992	int numRows = c.getCount();

0993	c.moveToFirst();

0994

0995	for (int i = 0; i < numRows; ++i) {

0996	if (c.getString(0) != null){

0997	HashMap<String, String> returnHash = new HashMap<String, String>();

0998	returnHash.put("blogID", c.getString(0));

0999	returnHash.put("pageID", c.getString(1));

1000	returnHash.put("title", c.getString(2));

1001	returnHash.put("pageDate", c.getString(3));

1002	returnHash.put("pageDateFormatted", c.getString(4));

1003	returnVector.add(i, returnHash);

1004

}

1005	c.moveToNext();

1006

}

1007	c.close();

1008	db.close();

1009

1010	if (numRows == 0){

1011	returnVector = null;

1012

}

1013

1014	return returnVector;

1015

}

1016

1017	public boolean savePages(Context ctx, Vector<?> pageValues) {

1018	boolean returnValue = false;

1019	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1020	HashMap<?, ?> firstHash = (HashMap<?, ?>) pageValues.get(0);

1021	String blogID = firstHash.get("blogID").toString();

1022	//delete existing values

1023	db.delete(PAGES_TABLE, "blogID=" + blogID, null);

1024

1025	for (int i = 0; i < pageValues.size(); i++){

1026	ContentValues values = new ContentValues();

1027	HashMap<?, ?> thisHash = (HashMap<?, ?>) pageValues.get(i);

1028	values.put("blogID", thisHash.get("blogID").toString());

1029	values.put("pageID", thisHash.get("pageID").toString());

1030	values.put("parentID", thisHash.get("parentID").toString());

1031	values.put("title", thisHash.get("title").toString());

1032	values.put("pageDate", thisHash.get("pageDate").toString());

1033	values.put("pageDateFormatted", thisHash.get("pageDateFormatted").toString());

1034	returnValue = db.insert(PAGES_TABLE, null, values) > 0;

1035

}

1036

1037

1038	db.close();

1039	return (returnValue);

1040

1041

}

1042

1043	public Vector<HashMap<String, Object>> loadComments(Context ctx, String blogID) {

1044	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1045	Vector<HashMap<String, Object>> returnVector = new Vector<HashMap<String, Object>>();

1046	Cursor c = db.query(COMMENTS_TABLE, new String[] { "blogID", "postID", "iCommentID", "author", "comment", "commentDate", "commentDateFormatted", "status", "url", "email", "postTitle"}, "blogID=" + blogID, null, null, null, null);

1047

1048	int numRows = c.getCount();

1049	c.moveToFirst();

1050

1051	HashMap<String, Object> numRecords = new HashMap<String, Object>();

1052	//add the number of stored records so the offset can be computed

1053	if (numRows > 0){

1054	numRecords.put("numRecords", numRows);

1055	returnVector.add(0, numRecords);

1056

}

1057

1058	for (int i = 1; i < (numRows + 1); ++i) {

1059	if (c.getString(0) != null){

1060	HashMap<String, Object> returnHash = new HashMap<String, Object>();

1061	returnHash.put("blogID", c.getString(0));

1062	returnHash.put("postID", c.getInt(1));

1063	returnHash.put("commentID", c.getInt(2));

1064	returnHash.put("author", c.getString(3));

1065	returnHash.put("comment", c.getString(4));

1066	returnHash.put("commentDate", c.getString(5));

1067	returnHash.put("commentDateFormatted", c.getString(6));

1068	returnHash.put("status", c.getString(7));

1069	returnHash.put("url", c.getString(8));

1070	returnHash.put("email", c.getString(9));

1071	returnHash.put("postTitle", c.getString(10));

1072	returnVector.add(i, returnHash);

1073

}

1074	c.moveToNext();

1075

}

1076	c.close();

1077	db.close();

1078

1079	if (numRows == 0){

1080	returnVector = null;

1081

}

1082

1083	return returnVector;

1084

}

1085

1086	public Vector<HashMap<String, Object>> loadMoreComments(Context ctx, String blogID, int limit) {

1087	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1088	Vector<HashMap<String, Object>> returnVector = new Vector<HashMap<String, Object>>();

1089	Cursor c = db.query(COMMENTS_TABLE, new String[] { "blogID", "postID", "iCommentID", "author", "comment", "commentDate", "commentDateFormatted", "status", "url", "email", "postTitle"}, "blogID=" + blogID, null, null, null, "iCommentID ASC", String.valueOf(limit));

1090	int numRows = c.getCount();

1091	c.moveToFirst();

1092

1093	//HashMap numRecords = new HashMap();

1094	//add the number of stored records so the offset can be computed

1095	/*if (numRows > 0){

1096	numRecords.put("numRecords", numRows);

1097	returnVector.add(0, numRecords);

1098

}*/

1099	for (int i = 0; i < numRows; i++) {

1100	if (c.getString(0) != null){

1101	HashMap<String, Object> returnHash = new HashMap<String, Object>();

1102	returnHash.put("blogID", c.getString(0));

1103	returnHash.put("postID", c.getInt(1));

1104	returnHash.put("commentID", c.getInt(2));

1105	returnHash.put("author", c.getString(3));

1106	returnHash.put("comment", c.getString(4));

1107	returnHash.put("commentDate", c.getString(5));

1108	returnHash.put("commentDateFormatted", c.getString(6));

1109	returnHash.put("status", c.getString(7));

1110	returnHash.put("url", c.getString(8));

1111	returnHash.put("email", c.getString(9));

1112	returnHash.put("postTitle", c.getString(10));

1113	returnVector.add(i, returnHash);

1114

}

1115	c.moveToNext();

1116

}

1117	c.close();

1118	db.close();

1119

1120	if (numRows == 0){

1121	returnVector = null;

1122

}

1123

1124	return returnVector;

1125

}

1126

1127	public boolean saveComments(Context ctx, Vector<?> commentValues, boolean loadMore) {

1128	boolean returnValue = false;

1129	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1130	HashMap<?, ?> firstHash = (HashMap<?, ?>) commentValues.get(0);

1131	String blogID = firstHash.get("blogID").toString();

1132	//delete existing values, if user hit refresh button

1133	if (!loadMore){

1134	db.delete(COMMENTS_TABLE, "blogID=" + blogID, null);

1135

}

1136

1137	for (int i = 0; i < commentValues.size(); i++){

1138	ContentValues values = new ContentValues();

1139	HashMap<?, ?> thisHash = (HashMap<?, ?>) commentValues.get(i);

1140	values.put("blogID", thisHash.get("blogID").toString());

1141	values.put("postID", thisHash.get("postID").toString());

1142	values.put("iCommentID", thisHash.get("commentID").toString());

1143	values.put("author", thisHash.get("author").toString());

1144	values.put("comment", thisHash.get("comment").toString());

1145	values.put("commentDate", thisHash.get("commentDate").toString());

1146	values.put("commentDateFormatted", thisHash.get("commentDateFormatted").toString());

1147	values.put("status", thisHash.get("status").toString());

1148	values.put("url", thisHash.get("url").toString());

1149	values.put("email", thisHash.get("email").toString());

1150	values.put("postTitle", thisHash.get("postTitle").toString());

1151	returnValue = db.insert(COMMENTS_TABLE, null, values) > 0;

1152

}

1153

1154

1155	db.close();

1156	return (returnValue);

1157

1158

}

1159

1160	public void updateCommentStatus(Context ctx, String blogID, String id, String newStatus) {

1161	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1162

1163	ContentValues values = new ContentValues();

1164	values.put("status", newStatus);

1165	boolean returnValue = db.update(COMMENTS_TABLE, values, "blogID=" + blogID + " AND iCommentID=" + id, null) > 0;

1166	if (returnValue){};

1167

1168	db.close();

1169

1170

}

1171

1172	public void clearPages(Context ctx, String blogID) {

1173	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1174	//delete existing values

1175	db.delete(PAGES_TABLE, "blogID=" + blogID, null);

1176	db.close();

1177

}

1178

1179	public void clearPosts(Context ctx, String blogID) {

1180	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1181	//delete existing values

1182	db.delete(POSTSTORE_TABLE, "blogID=" + blogID, null);

1183	db.close();

1184

1185

}

1186

1187	//eula table

1188	public boolean checkEULA(Context ctx){

1189	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1190

1191	Cursor c = db.query(EULA_TABLE, new String[] { "read" }, "id=0", null, null, null, null);

1192	int numRows = c.getCount();

1193	c.moveToFirst();

1194	boolean returnValue = false;

1195	if (numRows == 1){

1196	returnValue = (c.getInt(0) != 0);

1197

}

1198	c.close();

1199	db.close();

1200

1201	return returnValue;

1202

}

1203

1204	public void setEULA(Context ctx) {

1205	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1206	ContentValues values = new ContentValues();

1207	values.put("id", 0);

1208	values.put("read", 1); //set that they've read the EULA

1209	boolean returnValue = db.insert(EULA_TABLE, null, values) > 0;

1210	if (returnValue){};

1211	db.close();

1212

1213

}

1214

1215	public void setStatsDate(Context ctx) {

1216	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1217	ContentValues values = new ContentValues();

1218	values.put("statsdate", System.currentTimeMillis()); //set to current time

1219

1220	boolean returnValue = db.update(EULA_TABLE, values, "id=0", null) > 0;

1221	if (returnValue){};

1222

1223	db.close();

1224

}

1225

1226	public long getStatsDate(Context ctx) {

1227	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1228

1229	Cursor c = db.query(EULA_TABLE, new String[] { "statsdate" }, "id=0", null, null, null, null);

1230	int numRows = c.getCount();

1231	c.moveToFirst();

1232	long returnValue = 0;

1233	if (numRows == 1){

1234	returnValue = c.getLong(0);

1235

}

1236	c.close();

1237	db.close();

1238	return returnValue;

1239

}

1240

1241	//categories

1242	public boolean insertCategory(Context ctx, String id, int wp_id, String category_name) {

1243	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1244	ContentValues values = new ContentValues();

1245	values.put("blog_id", id);

1246	values.put("wp_id", wp_id);

1247	values.put("category_name", category_name.toString());

1248	boolean returnValue = db.insert(CATEGORIES_TABLE, null, values) > 0;

1249	db.close();

1250	return (returnValue);

1251

}

1252

1253	public Vector<String> loadCategories(Context ctx, String id) {

1254	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1255

1256	Cursor c = db.query(CATEGORIES_TABLE, new String[] { "id", "wp_id", "category_name" }, "blog_id=" + id, null, null, null, null);

1257	int numRows = c.getCount();

1258	c.moveToFirst();

1259	Vector<String> returnVector = new Vector<String>();

1260	for (int i = 0; i < numRows; ++i) {

1261	String category_name = c.getString(2);

1262	if (category_name != null)

1263

{

1264	returnVector.add(category_name);

1265

}

1266	c.moveToNext();

1267

}

1268	c.close();

1269	db.close();

1270

1271	return returnVector;

1272

}

1273

1274	public int getCategoryId(Context ctx, String id, String category){

1275	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1276

1277	Cursor c = db.query(CATEGORIES_TABLE, new String[] {"wp_id"}, "category_name=\"" + category + "\" AND blog_id=" + id, null, null, null, null);

1278	c.moveToFirst();

1279	int categoryID = 0;

1280	categoryID = c.getInt(0);

1281	db.close();

1282	return categoryID;

1283

}

1284

1285	public void clearCategories(Context ctx, String id){

1286	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1287	//clear out the table since we are refreshing the whole enchilada

1288	db.delete(CATEGORIES_TABLE, "blog_id=" + id, null);

1289	db.close();

1290

}

1291

1292	//unique identifier queries

1293	public void updateUUID(Context ctx, String uuid) {

1294	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1295	ContentValues values = new ContentValues();

1296	values.put("uuid", uuid);

1297	boolean returnValue = db.update("eula", values, null, null) > 0;

1298	if (returnValue){};

1299	db.close();

1300

}

1301

1302	public String getUUID(Context ctx) {

1303	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1304	Cursor c = db.query("eula", new String[] { "uuid" }, "id=0", null, null, null, null);

1305	int numRows = c.getCount();

1306	c.moveToFirst();

1307	String returnValue = "";

1308	if (numRows == 1){

1309	if (c.getString(0) != null){

1310	returnValue = c.getString(0);

1311

}

1312

}

1313	c.close();

1314	db.close();

1315

1316	return returnValue;

1317

1318

}

1319

1320	public boolean addQuickPressShortcut(Context ctx, String accountId, String name) {

1321	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1322	ContentValues values = new ContentValues();

1323	values.put("accountId", accountId);

1324	values.put("name", name);

1325	boolean returnValue = db.insert(QUICKPRESS_SHORTCUTS_TABLE, null, values) > 0;

1326	db.close();

1327	return (returnValue);

1328

}

1329

1330	public Vector<HashMap<String, Object>> getQuickPressShortcuts(Context ctx, String accountId) {

1331	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1332	Cursor c = db.query(QUICKPRESS_SHORTCUTS_TABLE, new String[] { "id", "accountId", "name"}, "accountId = "+accountId, null, null, null, null);

1333	String id, name;

1334	int numRows = c.getCount();

1335	c.moveToFirst();

1336	Vector<HashMap<String, Object>> accounts = new Vector<HashMap<String, Object>>();

1337	for (int i = 0; i < numRows; i++) {

1338

1339	id = c.getString(0);

1340	name = c.getString(2);

1341	if (id != null)

1342

{

1343	HashMap<String, Object> thisHash = new HashMap<String, Object>();

1344

1345	thisHash.put("id", id);

1346	thisHash.put("name", name);

1347	accounts.add(thisHash);

1348

}

1349	c.moveToNext();

1350

}

1351	c.close();

1352	db.close();

1353

1354	return accounts;

1355

}

1356

1357	public boolean deleteQuickPressShortcut(Context ctx, String id) {

1358	db = ctx.openOrCreateDatabase(DATABASE_NAME, 0, null);

1359	int rowsAffected = db.delete(QUICKPRESS_SHORTCUTS_TABLE, "id=" + id, null);

1360

1361	boolean returnValue = false;

1362	if (rowsAffected > 0){

1363	returnValue = true;

1364

}

1365

1366	db.close();

1367

1368	return (returnValue);

1369

}

1370

1371

}