@LargeTest
public void testManyRowsTxtLong() throws Exception {
mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, txt TEXT, data INT);");
Random random = new Random(System.currentTimeMillis());
StringBuilder randomString = new StringBuilder(1979);
for (int i = 0; i < 1979; i++) {
randomString.append((random.nextInt() & 0xf) % 10);
}
// if cursor window size changed, adjust this value too
final int count = 600;
for (int i = 0; i < count; i++) {
StringBuilder sql = new StringBuilder(2100);
sql.append("INSERT INTO test (txt, data) VALUES ('");
sql.append(randomString);
sql.append("','");
sql.append(i);
sql.append("');");
mDatabase.execSQL(sql.toString());
}
Cursor c = mDatabase.query("test", new String[] {"txt", "data"}, null, null, null, null, null);
assertNotNull(c);
int i = 0;
while (c.moveToNext()) {
assertEquals(randomString.toString(), c.getString(0));
assertEquals(i, c.getInt(1));
i++;
}
assertEquals(count, i);
assertEquals(count, c.getCount());
c.close();
}
@MediumTest
public void testCursorUpdate() {
mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, d INTEGER, s INTEGER);");
for (int i = 0; i < 20; i++) {
mDatabase.execSQL("INSERT INTO test (d, s) VALUES (" + i + "," + i % 2 + ");");
}
Cursor c = mDatabase.query("test", null, "s = 0", null, null, null, null);
int dCol = c.getColumnIndexOrThrow("d");
int sCol = c.getColumnIndexOrThrow("s");
int count = 0;
while (c.moveToNext()) {
assertTrue(c.updateInt(dCol, 3));
count++;
}
assertEquals(10, count);
assertTrue(c.commitUpdates());
assertTrue(c.requery());
count = 0;
while (c.moveToNext()) {
assertEquals(3, c.getInt(dCol));
count++;
}
assertEquals(10, count);
assertTrue(c.moveToFirst());
assertTrue(c.deleteRow());
assertEquals(9, c.getCount());
c.close();
}
@MediumTest
public void testLargeField() throws Exception {
mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
StringBuilder sql = new StringBuilder(2100);
sql.append("INSERT INTO test (data) VALUES ('");
Random random = new Random(System.currentTimeMillis());
StringBuilder randomString = new StringBuilder(1979);
for (int i = 0; i < 1979; i++) {
randomString.append((random.nextInt() & 0xf) % 10);
}
sql.append(randomString);
sql.append("');");
mDatabase.execSQL(sql.toString());
Cursor c = mDatabase.query("test", null, null, null, null, null, null);
assertNotNull(c);
assertEquals(1, c.getCount());
assertTrue(c.moveToFirst());
assertEquals(0, c.getPosition());
String largeString = c.getString(c.getColumnIndexOrThrow("data"));
assertNotNull(largeString);
assertEquals(randomString.toString(), largeString);
c.close();
}
public void reCreate() {
SQLiteDatabase db = dbhelper.getWritableDatabase();
db.execSQL("DROP TABLE IF EXISTS month");
db.execSQL("DROP TABLE IF EXISTS hour");
onCreate(db);
db.close();
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_VICTIM);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_GPS);
onCreate(db);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
db.execSQL(CREATE_CATEGORY);
Log.d("MyDatabaseHelper", "Create succeeded");
// Toast.makeText(mContext,"Create succeeded",Toast.LENGTH_SHORT).show();
}
@Override
public void onCreate(SQLiteDatabase db) {
String query =
"CREATE TABLE "
+ TABLE_USER
+ "("
+ COLUMN_U_USERID
+ " INTEGER UNIQUE,"
+ COLUMN_U_EMAIL
+ " TEXT PRIMARY KEY,"
+ COLUMN_U_FNAME
+ " TEXT,"
+ COLUMN_U_LNAME
+ " TEXT,"
+ COLUMN_U_PASSWORD
+ " TEXT,"
+ COLUMN_U_STATE
+ " INTEGER);";
db.execSQL(query);
query =
"CREATE TABLE "
+ TABLE_FRIEND
+ "("
+ COLUMN_F_FRIENDID
+ " INTEGER PRIMARY KEY,"
+ COLUMN_F_USERID
+ " INTEGER REFERENCES User(USERID),"
+ COLUMN_F_NAME
+ " TEXT,"
+ COLUMN_F_DOB
+ " TEXT,"
+ COLUMN_F_STATE
+ " INTEGER);";
db.execSQL(query);
query =
"CREATE TABLE "
+ TABLE_GIFT
+ "("
+ COLUMN_G_ASIN
+ " TEXT PRIMARY KEY,"
+ COLUMN_G_FRIENDID
+ " INTEGER REFERENCES Friend(FRIENDID),"
+ COLUMN_G_TITLE
+ " TEXT,"
+ COLUMN_G_STATE
+ " INTEGER);";
db.execSQL(query);
query =
"CREATE TABLE "
+ TABLE_INTEREST
+ "("
+ COLUMN_I_INTERESTNAME
+ " TEXT PRIMARY KEY,"
+ COLUMN_I_FRIENDID
+ " INTEGER REFERENCES Friend(FRIENDID),"
+ COLUMN_I_STATE
+ " INTEGER);";
db.execSQL(query);
System.out.println("db created?");
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ Tables.CHOSEN_PHOTOS
+ " ("
+ ChosenPhotos._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ ChosenPhotos.URI
+ " TEXT NOT NULL,"
+ "UNIQUE ("
+ ChosenPhotos.URI
+ ") ON CONFLICT REPLACE)");
db.execSQL(
"CREATE TABLE "
+ Tables.METADATA_CACHE
+ " ("
+ MetadataCache._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ MetadataCache.URI
+ " TEXT NOT NULL,"
+ MetadataCache.DATETIME
+ " INTEGER,"
+ MetadataCache.LOCATION
+ " TEXT,"
+ MetadataCache.VERSION
+ " INTEGER,"
+ "UNIQUE ("
+ MetadataCache.URI
+ ") ON CONFLICT REPLACE)");
}
public int insertSong(ArchiveSongObj song) {
db.execSQL(
"INSERT INTO songTbl(fileName,songTitle,show_id,isDownloaded,folderName) "
+ "SELECT '"
+ song.getFileName()
+ "','"
+ song.toString().replaceAll("'", "''")
+ "',show._id,'false','' "
+ "FROM showTbl show "
+ "WHERE show.showIdent = '"
+ song.getShowIdentifier()
+ "' "
+ "AND NOT EXISTS (SELECT 1 FROM songTbl song WHERE song.fileName = '"
+ song.getFileName()
+ "')");
Cursor cur =
db.rawQuery(
"Select _id as song_id from songTbl " + "where fileName = '" + song.getFileName() + "'",
null);
cur.moveToFirst();
int id = cur.getInt(cur.getColumnIndex(PLAYLISTSONG_SONG_KEY));
cur.close();
if (song.hasFolder()) {
db.execSQL("Update songTbl set folderName = '" + song.getFolder() + "' where _id = " + id);
}
return id;
}
// 创建数据库
private void upgrade(SQLiteDatabase db, int oldV, final int newV) {
db.execSQL("DROP TABLE IF EXISTS " + DownloadColumn.TABLE_NAME);
db.execSQL(
"CREATE TABLE "
+ DownloadColumn.TABLE_NAME
+ " ("
+ DownloadColumn._ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ DownloadColumn.NAME
+ " TEXT, "
+ DownloadColumn.DOWNLOAD_ID
+ " INTEGER, "
+ DownloadColumn.PKG_NAME
+ " TEXT, "
+ DownloadColumn.URI
+ " TEXT, "
+ DownloadColumn.PATH
+ " TEXT, "
+ DownloadColumn.FILE_SIZE
+ " TEXT, "
+ DownloadColumn.SUFFIX
+ " TEXT, "
+ DownloadColumn.STATUS
+ " INTEGER, "
+ DownloadColumn.TYPE
+ " INTEGER, "
+ DownloadColumn.VERSION
+ " TEXT, "
+ DownloadColumn.ICON_URI
+ " TEXT, "
+ DownloadColumn.CURRENT_BYTE
+ " LONG, "
+ DownloadColumn.TOTAL_BYTE
+ " LONG); ");
}
// from
// http://stackoverflow.com/questions/3424156/upgrade-sqlite-database-from-one-version-to-another
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.beginTransaction();
// run a table creation with if not exists (we are doing an upgrade, so the table might
// not exists yet, it will fail alter and drop)
db.execSQL(getManualBookmarksCreationString());
// put in a list the existing columns
List<String> columns = GetColumns(db, "tbl_manual_bookmarks");
// backup table
db.execSQL("ALTER TABLE tbl_manual_bookmarks RENAME TO 'temp_tbl_manual_bookmarks'");
// create new table (with new scheme)
db.execSQL(getManualBookmarksCreationString());
// get the intersection with the new columns, this time columns taken from the upgraded table
columns.retainAll(GetColumns(db, "tbl_manual_bookmarks"));
// restore data
String cols = joinStrings(columns, ",");
db.execSQL(
String.format(
"INSERT INTO %s (%s) SELECT %s from 'temp_%s",
"tbl_manual_bookmarks", cols, cols, "tbl_manual_bookmarks'"));
// remove backup table
db.execSQL("DROP table 'temp_tbl_manual_bookmarks'");
db.setTransactionSuccessful();
db.endTransaction();
}
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
// TODO Auto-generated method stub
db.execSQL("DROP TABLE IF EXISTS " + table_file + ";");
db.execSQL("DROP TABLE IF EXISTS " + table_product + ";");
onCreate(db);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE "
+ BILL_TABLE
+ " ("
+ BILL_NUMBER
+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ BILL_TYPE
+ " TEXT, "
+ BILL_DATE
+ " TEXT, "
+ BILL_AMOUNT
+ " INTEGER);");
db.execSQL(
"CREATE TABLE "
+ FRIENDS_TABLE
+ " ("
+ FRIENDS_ID
+ " INTEGER, "
+ FRIENDS_NAME
+ " TEXT, "
+ FRIENDS_EMAIL
+ " TEXT, "
+ FRIENDS_PERCENT
+ " INTEGER, "
+ FRIENDS_AMOUNT
+ " INTEGER);");
Log.d("Database operations", "Table created");
// db.execSQL(String.valueOf(count));
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// This database is only a cache for online data, so its upgrade policy is
// to simply to discard the data and start over
db.execSQL(SQL_DELETE_ENTRIES);
db.execSQL(SQL_DELETE_ENTRIES_NOTIFICATIONS);
onCreate(db);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(MySql.createProductTable);
db.execSQL(MySql.createUserTable);
db.execSQL(MySql.createCollectTable);
db.execSQL(MySql.createShoppingTable);
}
static void createVideoTable(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE IF NOT EXISTS video ("
+ "_id INTEGER PRIMARY KEY,"
+ "_data TEXT NOT NULL,"
+ "_display_name TEXT,"
+ "_size INTEGER,"
+ "mime_type TEXT,"
+ "date_added INTEGER,"
+ "date_modified INTEGER,"
+ "title TEXT,"
+ "duration INTEGER,"
+ "artist TEXT,"
+ "album TEXT,"
+ "resolution TEXT,"
+ "description TEXT,"
+ "isprivate INTEGER,"
+ // for YouTube videos
"tags TEXT,"
+ // for YouTube videos
"category TEXT,"
+ // for YouTube videos
"language TEXT,"
+ // for YouTube videos
"mini_thumb_data TEXT,"
+ "latitude DOUBLE,"
+ "longitude DOUBLE,"
+ "datetaken INTEGER,"
+ "mini_thumb_magic INTEGER"
+ ");");
db.execSQL("ALTER TABLE video ADD COLUMN bucket_id TEXT;");
db.execSQL("ALTER TABLE video ADD COLUMN bucket_display_name TEXT");
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(
"CREATE TABLE " + HOUR + " (" + "id INTEGER PRIMARY KEY, " + RATE + " INTEGER" + ");");
db.execSQL(
"CREATE TABLE "
+ MONTH
+ " ("
+ "id INTEGER PRIMARY KEY, "
+ RATE
+ " INTEGER, "
+ HOUR
+ " INTEGER"
+ ");");
db.execSQL(
"CREATE TABLE "
+ SALARY
+ " ("
+ "id INTEGER, "
+ RECD
+ " INTEGER, "
+ DATE
+ " INTEGER"
+ ");");
}
public int startApp(String app, String packageName) {
int id = -1;
try {
Cursor result = db.rawQuery("select * from currentid", null);
if (result.moveToFirst()) {
id = result.getInt(0);
++id;
String curTime =
Long.toString(Calendar.getInstance(TimeZone.getTimeZone("GMT")).getTimeInMillis());
String q =
"insert into processes values('"
+ Integer.toString(id)
+ "','"
+ packageName
+ "','"
+ curTime
+ "','"
+ curTime
+ "')";
Log.d("AppsDb", q);
db.execSQL(q);
q = "update currentid set id = '" + Integer.toString(id) + "'";
Log.d("AppsDb", q);
db.execSQL(q);
this.setAttributes(packageName, app, null, null);
}
} catch (Exception e) {
Log.e("Exception 1", e.toString());
return -1;
}
return id;
}
/**
* On upgrade called when DB is upgraded.
*
* @param db the db
* @param oldVersion the old version
* @param newVersion the new version
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w("Example", "Upgrading database, this will drop tables and recreate.");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CHATTER);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
onCreate(db);
}
// public List<Teacher> getTeachers(){
// //readDB.ex
//
// }
// c.setCno(cno.equals("") ? preCourse.getCno() : cno);
// c.setName(name.equals("") ? preCourse.getName() : name);
// c.setScore(eles.get(index).html().equals("") ? preCourse.getScore() :
// eles.get(index++).html()
//
// );
// c.setType(eles.get(index).html().equals("") ? preCourse.getType() :
// eles.get(index++).html());
// c.setCourseType(eles.get(index).html().equals("") ? preCourse.getCourseType() :
// eles.get(index++).html());
// c.setClassNo(eles.get(index).html().equals("") ? preCourse.getClassNo() :
// eles.get(index++).html());
// c.setClassName(eles.get(index).html().equals("") ? preCourse.getClassName() :
// eles.get(index++).html());
// c.setNumbers(eles.get(index).html().equals("") ? preCourse.getNumbers() :
// eles.get(index++).html());
// c.setTime(eles.get(index).html().equals("") ? preCourse.getTime() :
// eles.get(index++).html());
// c.setAddress(eles.get(index).html().equals("") ? preCourse.getAddress() :
// eles.get(index++).html());
public void insertCourses(List<Course> courses, String tno) {
String courseSql = "insert into course values(?,?,?,?,?,?,?,?,?,?)";
String tnoSql = "insert into tc values(?,?)";
String preCno = "";
for (Course c : courses) {
String[] items =
new String[] {
c.getCno(),
c.getName(),
c.getScore(),
c.getType(),
c.getCourseType(),
c.getClassNo(),
c.getClassName(),
c.getNumbers(),
c.getTime(),
c.getAddress()
};
readDB.execSQL(courseSql, items);
if (!c.getCno().equals(preCno)) {
String[] items2 = new String[] {c.getCno(), tno};
readDB.execSQL(tnoSql, items2);
preCno = c.getCno();
}
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO: proper migrations
db.execSQL("DROP TABLE IF EXISTS " + Tables.CHOSEN_PHOTOS);
db.execSQL("DROP TABLE IF EXISTS " + Tables.METADATA_CACHE);
onCreate(db);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(ClientContract.getSqlCreateTable());
db.execSQL(UserContract.getSqlCreateTable());
db.execSQL(UserContract.insertUserAdmin());
}
@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
Log.w("mycp", "updating database from version " + oldVersion + " to " + newVersion);
database.execSQL("drop table if exists urlpermission");
database.execSQL("drop table if exists urlhistory");
onCreate(database);
}
private void populateDefaultTable() {
mDatabase.execSQL("CREATE TABLE test (_id INTEGER PRIMARY KEY, data TEXT);");
mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString1 + "');");
mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString2 + "');");
mDatabase.execSQL("INSERT INTO test (data) VALUES ('" + sString3 + "');");
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL(CREATE_USER_TABLE);
db.execSQL(CREATE_LAST_USER_TABLE);
db.execSQL(CREATE_LOCAL_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(NoteDBEntity.SQL_DELETE);
db.execSQL(PicDBEntity.SQL_DELETE);
db.execSQL(PathDBEntity.SQL_DELETE);
onCreate(db);
}
public void onCreate(SQLiteDatabase db) {
ContentValues cv = new ContentValues();
db.execSQL(
"create table position ("
+ " id integer primary key, "
+ " name text, "
+ " salary integer);");
for (int i = 0; i < position_id.length; i++) {
cv.clear();
cv.put("id", position_id[i]);
cv.put("name", position_name[i]);
cv.put("salary", position_salary[i]);
db.insert("position", null, cv);
}
db.execSQL(
"create table people ("
+ " id integer primary key autoincrement, "
+ " name text, "
+ " posid integer);");
for (int i = 0; i < people_name.length; i++) {
cv.clear();
cv.put("name", people_name[i]);
cv.put("posid", people_posid[i]);
db.insert("people", null, cv);
}
}
private void Save() {
synchronized (saveSync) {
SQLiteDatabase mDatabase = getContext().openOrCreateDatabase("MP3Base", 0, null);
ContentValues cv = new ContentValues();
try {
mDatabase.execSQL("DROP TABLE IF EXISTS MP3ENTITYES");
} catch (Exception e) {
e.printStackTrace();
}
mDatabase.execSQL(
"CREATE TABLE IF NOT EXISTS MP3ENTITYES (_id INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "TITLE VARCHAR(300),"
+ "ARTIST VARCHAR(300),"
+ "TIME VARCHAR(300),"
+ "DIRECTORY VARCHAR(300))");
Iterator<MP3Entity> iter = getMp3entityes().iterator();
//noinspection WhileLoopReplaceableByForEach
while (iter.hasNext()) {
MP3Entity Mp3Entity = iter.next();
cv.put("TITLE", Mp3Entity.getTitle());
cv.put("ARTIST", Mp3Entity.getArtist());
cv.put("TIME", Mp3Entity.getTime());
cv.put("DIRECTORY", Mp3Entity.getDirectory());
mDatabase.insert("MP3ENTITYES", null, cv);
}
mDatabase.close();
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + FILM_TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + SEANCE_TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + EVENT_TABLE_NAME);
onCreate(db);
}
@MediumTest
public void testSimpleQuery() throws Exception {
mDatabase.execSQL("CREATE TABLE test (num INTEGER NOT NULL, str TEXT NOT NULL);");
mDatabase.execSQL("INSERT INTO test VALUES (1234, 'hello');");
SQLiteStatement statement1 = mDatabase.compileStatement("SELECT num FROM test WHERE str = ?");
SQLiteStatement statement2 = mDatabase.compileStatement("SELECT str FROM test WHERE num = ?");
try {
statement1.bindString(1, "hello");
long value = statement1.simpleQueryForLong();
assertEquals(1234, value);
statement1.bindString(1, "world");
statement1.simpleQueryForLong();
fail("shouldn't get here");
} catch (SQLiteDoneException e) {
// expected
}
try {
statement2.bindLong(1, 1234);
String value = statement1.simpleQueryForString();
assertEquals("hello", value);
statement2.bindLong(1, 5678);
statement1.simpleQueryForString();
fail("shouldn't get here");
} catch (SQLiteDoneException e) {
// expected
}
statement1.close();
statement2.close();
}