In the first time I have created one Table DEPTS. After that I want to create another two tables FEEDS and ARTICLES.
But I see that the commends for creating new tables never executed, why?
Here is my code:
package com.android.database;
// ... imports ...
public class DatabaseHelper extends SQLiteOpenHelper{
static final String dbName = "appDB";
static int dbVersion = 3;
private static final String DEBPT_TABLE = "Dept";
private static final String COL_DEPT_ID = "DeptID";
private static final String COL_DEPT_NAME = "DeptName";
private static final String COL_DEPT_LAT = "DeptLat";
private static final String COL_DEPT_LNG = "DeptLng";
private static final String FEEDS_TABLE = "feeds";
private static final String COL_FEED_ID = "feed_id";
private static final String COL_FEED_TITLE = "title";
private static final String COL_FEED_URL = "url";
private static final String ARTICLES_TABLE = "articles";
private static final String COL_ARTICLE_ID = "article_id";
private static final String COL_ARTICLE_FEED_ID = "feed_id";
private static final String COL_ATRICLE_TITLE = "title";
private static final String COL_ATRICLE_URL = "url";
public DatabaseHelper(Context context) {
super(context, dbName, null, dbVersion);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
/*onCreate(SQLiteDatabase db): invoked when the database is created,
this is where we can create tables and columns to them, create views or triggers. */
db.execSQL("CREATE TABLE "+DEBPT_TABLE+
"("+COL_DEPT_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
COL_DEPT_NAME+" TEXT NOT NULL, "+
COL_DEPT_LAT+" REAL NOT NULL, "+
COL_DEPT_LNG+" REAL NOT NULL);");
db.execSQL("CREATE TABLE "+ FEEDS_TABLE+
" ("+COL_FEED_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
COL_FEED_TITLE +" TEXT NOT NULL,"+
COL_FEED_URL+" TEXT_NOT_NULL);");
db.execSQL("CREATE TABLE "+ ARTICLES_TABLE+
" ("+COL_ARTICLE_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
COL_ARTICLE_FEED_ID+ " INTEGER NOT NULL, "+
COL_ATRICLE_TITLE+" TEXT NOT NULL, "+
COL_ATRICLE_URL+" TEXT NOT NULL);");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
/*onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): invoked when we make a modification to
the database such as altering, dropping , creating new tables. */
db.execSQL("DROP TABLE IF EXISTS "+DEBPT_TABLE);
db.execSQL("DROP TABLE IF EXISTS "+FEEDS_TABLE);
db.execSQL("DROP TABLE IF EXISTS "+ARTICLES_TABLE);
onCreate(db);
}
public boolean insertDept(Department dept){
SQLiteDatabase db = this.getReadableDatabase();// open database for read/write
ContentValues cv = new ContentValues();
cv.put(COL_DEPT_NAME, dept.getName());
cv.put(COL_DEPT_LAT, dept.getLat());
cv.put(COL_DEPT_LNG, dept.getLng());
long result = db.insert(DEBPT_TABLE, COL_DEPT_ID, cv);
// result : the row ID of the newly inserted row, or -1 if an error occurred
db.close();
return (result>0);
}
public boolean updateDept(Department dept){
SQLiteDatabase db = this.getReadableDatabase();
ContentValues cv = new ContentValues();
cv.put(COL_DEPT_NAME, dept.getName());
cv.put(COL_DEPT_LAT, dept.getLat());
cv.put(COL_DEPT_LNG, dept.getLng());
int result = db.update(DEBPT_TABLE, cv,COL_DEPT_ID+"=?", new String []{String.valueOf(dept.getID())});
//String[] args: The arguments of the WHERE clause
db.close();
return (result>0);
}
public boolean deleteDept(Department dept){
SQLiteDatabase db = this.getWritableDatabase();
int re = db.delete(DEBPT_TABLE, COL_DEPT_ID+"=?", new String[]{String.valueOf(dept.getID())});
db.close();
return (re>0);
}
public ArrayList<Department> getAllDepts(){
ArrayList<Department> depts = new ArrayList<Department>();
Department dept = null;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cur = db.rawQuery("SELECT "+COL_DEPT_ID+ " as _id,"+
COL_DEPT_NAME+", "+COL_DEPT_LAT+", "+COL_DEPT_LNG+" from "+DEBPT_TABLE,
new String[]{});
cur.moveToFirst();
while(cur.moveToNext()){
dept = new Department();
dept.setID(cur.getInt(cur.getColumnIndex("_id")));
dept.setName(cur.getString(cur.getColumnIndex(COL_DEPT_NAME)));
dept.setLat(cur.getDouble(cur.getColumnIndex(COL_DEPT_LAT)));
dept.setLat(cur.getDouble(cur.getColumnIndex(COL_DEPT_LNG)));
depts.add(dept);
}
db.close();
return depts;
}
public int GetDeptID(String Dept)
{
SQLiteDatabase db=this.getReadableDatabase();
Cursor c=db.query(DEBPT_TABLE, new String[]{COL_DEPT_ID+" as _id",COL_DEPT_NAME},
COL_DEPT_NAME+"=?", new String[]{Dept}, null, null, null);
//Cursor c=db.rawQuery("SELECT "+COL_DEPT_ID+" as _id FROM "+DEBPT_TABLE+"
//WHERE "+COL_DEPT_NAME+"=?", new String []{Dept});
c.moveToFirst();
return c.getInt(c.getColumnIndex("_id"));
}
public boolean insertFeed(String title, URL url) {
SQLiteDatabase db=this.getReadableDatabase();
ContentValues values = new ContentValues();
values.put(COL_FEED_TITLE, title);
values.put(COL_FEED_URL, url.toString());
return (db.insert(FEEDS_TABLE, null, values) > 0);
}
public boolean deleteFeed(Feed feed) {
SQLiteDatabase db=this.getReadableDatabase();
return (db.delete(FEEDS_TABLE, COL_FEED_ID+ "=" + feed.feedId, null) > 0);
}
public ArrayList<Feed> getFeeds() {
SQLiteDatabase db=this.getReadableDatabase();
ArrayList<Feed> feeds = new ArrayList<Feed>();
try {
Cursor c = db.query(FEEDS_TABLE, new String[] { COL_FEED_ID, COL_FEED_TITLE,
COL_FEED_URL }, null, null, null, null, null);
int numRows = c.getCount();
c.moveToFirst();
for (int i = 0; i < numRows; ++i) {
Feed feed = new Feed();
feed.feedId = c.getLong(0);
feed.title = c.getString(1);
feed.url = new URL(c.getString(2));
feeds.add(feed);
c.moveToNext();
}
} catch (SQLException e) {
Log.e("FEEDS DB", e.toString());
} catch (MalformedURLException e) {
Log.e("FEEDS DB", e.toString());
}
return feeds;
}
public boolean insertArticle(Long feedId, String title, URL url) {
SQLiteDatabase db=this.getReadableDatabase();
ContentValues values = new ContentValues();
values.put(COL_ARTICLE_FEED_ID, feedId);
values.put(COL_ATRICLE_TITLE, title);
values.put(COL_ATRICLE_URL, url.toString());
return (db.insert(ARTICLES_TABLE, null, values) > 0);
}
public boolean deleteAricles(Long feedId) {
SQLiteDatabase db=this.getReadableDatabase();
return (db.delete(ARTICLES_TABLE, COL_ARTICLE_FEED_ID+"=" + feedId.toString(), null) > 0);
}
public List<Article> getArticles(Long feedId) {
SQLiteDatabase db=this.getReadableDatabase();
ArrayList<Article> articles = new ArrayList<Article>();
try {
Cursor c = db.query(ARTICLES_TABLE, new String[] { "article_id",
"feed_id", "title", "url" },
"feed_id=" + feedId.toString(), null, null, null, null);
int numRows = c.getCount();
c.moveToFirst();
for (int i = 0; i < numRows; ++i) {
Article article = new Article();
article.articleId = c.getLong(0);
article.feedId = c.getLong(1);
article.title = c.getString(2);
article.url = new URL(c.getString(3));
articles.add(article);
c.moveToNext();
}
} catch (SQLException e) {
Log.e("ARTICLES DB", e.toString());
} catch (MalformedURLException e) {
Log.e("ARTICLES DB", e.toString());
}
return articles;
}
}
