构建SQLite数据库以分离可读/写数据
我正在编写一个应用程序,允许用户阅读存储在SQLite数据库中的短篇小说。构建SQLite数据库以分离可读/写数据
到目前为止这么好。
但是现在我想添加一些涉及写入数据库的功能(保存ScrollView的Y位置,以便用户可以选择它们离开的位置,为书签添加故事等)。
我应该将这些值添加到books
表,或者我应该创建一个单独的表user_settings
的列像id
(INT),story_id
(INT),y_position
(INT),bookmarked
(布尔)?
注意:我也在考虑未来将故事存储在非本地数据库中的可能性。
我的另一个问题是:我是否需要将数据库移动到某个地方才能写入它?我使用的SQLiteAssetHelper和数据库目前在/assets/databases/database.db
。我听到一些关于/data/data/mypackage
文件夹的讨论,但我无法在我的项目中看到它。
我的数据库设置为当前如下:
authors
id
name
name_alphanumeric
books
id
title
author_id
collection
body
如果它是有用的,这是我DatabaseHelper至今:
public class DatabaseHelper extends SQLiteAssetHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "database9.db";
private static final String BOOKS = "books";
private static final String AUTHORS = "authors";
public DatabaseHelper (Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// setForcedUpgrade();
}
// Getting all books
public ArrayList<Author> getAllAuthors() {
ArrayList<Author> authorList = new ArrayList<>();
// Select all query
String selectQuery = "SELECT id, name FROM " + AUTHORS + " ORDER BY name_alphabetic";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
// create new author object
Author author = new Author();
// set ID and name of author object
author.setID(Integer.parseInt(cursor.getString(0)));
author.setName(cursor.getString(1));
// pass author object to authorList array
authorList.add(author);
} while (cursor.moveToNext());
}
// return author list
return authorList;
}
// Getting all stories
public List<Book> getAllStories(int authorID) {
List<Book> storyList = new ArrayList<>();
// Select all query
String selectQuery = "SELECT id, title FROM " + BOOKS + " WHERE author_id = " + authorID;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Book book = new Book();
book.setStoryID(Integer.parseInt(cursor.getString(0)));
book.setTitle(cursor.getString(1));
storyList.add(book);
} while (cursor.moveToNext());
}
// return contact list
return storyList;
}
// Get all collections
public List<Book> getAllCollections(int authorID) {
List<Book> collectionsList = new ArrayList<>();
// Select all query
String selectQuery = "SELECT DISTINCT collection FROM " + BOOKS + " WHERE author_id = " + authorID;
Log.i("stories", selectQuery);
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Book book = new Book();
book.setCollection(cursor.getString(0));
// Log.i("stories", cursor.getString(0));
collectionsList.add(book);
} while (cursor.moveToNext());
}
return collectionsList;
// not sure how to log collectionsList here
}
// Get story
public String getStoryBody(int storyID) {
// Log.i("stories", Integer.toString(storyID));
String storyBody = "";
// String storyBody();
// Select all query
String selectQuery = "SELECT body FROM " + BOOKS + " WHERE id = " + storyID;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
storyBody = cursor.getString(0);
} while (cursor.moveToNext());
}
return storyBody;
}
public int setScrollPosition(int scrollY, int storyID) {
String insertQuery = "UPDATE " + BOOKS + " SET scroll_position = " + scrollY + " WHERE id = " + storyID;
Log.i("insert", insertQuery);
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL(insertQuery);
return 0;
}
public int getScrollPosition(int storyID) {
int scrollPosition = 0;
String selectQuery = "SELECT scroll_position FROM " + BOOKS + " WHERE id = " + storyID;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
scrollPosition = cursor.getInt(0);
} while (cursor.moveToNext());
}
return scrollPosition;
}
}
但现在我想补充一点,涉及写入功能数据库 (保存ScrollView的Y位置,以便用户可以从 中删除,为书签添加故事等)。
我应该将这些值添加到books表,或者我应该创建一个像ID(int)的列 单独的表user_settings,story_id (INT),y_position(INT),书签(布尔)?
我想你已经明确表示,他们是USER值,因此它很可能是一个单独的用户表将是更好的更易于管理的解决方案。
我的另一个问题是:我需要将数据库移动到 能写入它吗?我正在使用SQLiteAssetHelper,目前数据库为 ,位于/assets/databases/database.db。我听到一些关于 /data/data/mypackage文件夹的讨论,但我在我的项目中看不到它。
在所有likeliehood数据库已被复制从资产文件夹到data/data/yourpackage/databases/dbfilename
由SQLiteAssetHelper
(据我所知这主要就是它的功能。不过,我从来没有使用过。)这样的文件夹访问有限(通常只有应用程序(扎根设备是一个例外)),所以很可能是为什么你看不到它。
因此,写入/更新数据库的权限方式可能没有任何要求。