Android database master secret (3)-upgrade table using LitePal, androidlitepal

Android database master secret (3)-upgrade table using LitePal, androidlitepal

Reprinted please indicate the source: http://blog.csdn.net/guolin_blog/article/details/39151617

In the previous article, we learned the basic usage of LitePal and experienced the convenience of using the framework to create tables. However, we all know that creating a table is only the most basic step in database operations. The table structure we created at the beginning is very likely to need to be modified at the later stage as demand changes. Therefore, upgrading tables is crucial to any project. Let's take a look at how to upgrade tables in traditional Android development, and how to use LitePal to upgrade a table. If you have not read the previous article, you are advised to refer to it first.Android Database Expert secret (2)-basic usage of creating tables and LitePal.

The Project address for LitePal is: https://github.com/LitePalFramework/LitePal

Traditional table update Methods

In the previous article, we used MySQLiteHelper to create the news table, which is also the first version of the database demo. db. However, the demand has changed. In addition to reading news, our software should also allow users to comment. Therefore, we need to upgrade the database and add a comment table.

What should we do? Add a table creation statement for the comment table, and then execute it in the onCreate () method? Yes. In this case, the two tables will be created at the same time. The Code is as follows:

public class MySQLiteHelper extends SQLiteOpenHelper {public static final String CREATE_NEWS = "create table news ("+ "id integer primary key autoincrement, "+ "title text, "+ "content text, "+ "publishdate integer,"+ "commentcount integer)";public static final String CREATE_COMMENT = "create table comment ("+ "id integer primary key autoincrement, "+ "content text)";public MySQLiteHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_NEWS);db.execSQL(CREATE_COMMENT);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}

This is completely normal for users who installed our software for the first time. However, if some users have installed the software of the previous version, we are sorry that the comment table cannot be created, because the database has been created before, the onCreate () method will not be re-executed.

In this case, we need to use the upgrade method to solve the problem. Do you see the fourth parameter in the MySQLiteHelper constructor method? This is the ID of the database version number, when the version number increases, the onUpgrade () method is called. You only need to process the update table operation here. The simple and crude method is to delete all existing tables in the database and recreate them. The Code is as follows:

public class MySQLiteHelper extends SQLiteOpenHelper {......@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_NEWS);db.execSQL(CREATE_COMMENT);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {db.execSQL("drop table if exists news");onCreate(db);}}

We can see that when the database is upgraded, we first Delete the news table, and then re-execute the onCreate () method to ensure that the tables in the database are all up-to-date.

However, if data already exists in the news table, this upgrade will cause all data loss in the table. Therefore, this is not a recommended upgrade method. So what is a better upgrade method? This is a little complicated. You need to add the specific upgrade logic in the onUpgrade () method based on the version number. Let's try it. For example, if the previous database version is 1, you can write it in the onUpgrade () method as follows:

public class MySQLiteHelper extends SQLiteOpenHelper {......@Overridepublic void onCreate(SQLiteDatabase db) {db.execSQL(CREATE_NEWS);db.execSQL(CREATE_COMMENT);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {switch (oldVersion) {case 1:db.execSQL(CREATE_COMMENT);default:}}}

We can see that a switch judgment is added in the onUpgrade () method. If the oldVersion is equal to 1, create another comment table. Now you only need to call the following code to create or upgrade the table:

SQLiteOpenHelper dbHelper = new MySQLiteHelper(this, "demo.db", null, 2);SQLiteDatabase db = dbHelper.getWritableDatabase();

Here we add the version number to 1. If the user upgrades from the old version, a comment table will be added. If the user installs the new version directly, it will be in onCreate () the two tables are created together.

OK. Now the second version of the software has been released, but shortly after the release, we suddenly found that a field is missing in the comment table, and we did not record the release time of the comment. No way. We have to fix this issue in the third edition. How should we add this field? The Code is as follows:

public class MySQLiteHelper extends SQLiteOpenHelper {......public static final String CREATE_COMMENT = "create table comment ("+ "id integer primary key autoincrement, " + "content text, " + "publishdate integer)";......@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {switch (oldVersion) {case 1:db.execSQL(CREATE_COMMENT);break;case 2:db.execSQL("alter table comment add column publishdate integer");break;default:}}}

We can see that the publishdate column is added to the table creation statement, so that this column will be available in the comment table when the onCreate () method is executed to create a table. So what if it was upgraded from the old version? No problem. We have handled all the upgrade logic in the onUpgrade () method. When the oldVersion is 2, the alter statement will be executed to add the publishdate column. Call the following code to create or upgrade a database:

SQLiteOpenHelper dbHelper = new MySQLiteHelper(this, "demo.db", null, 3);SQLiteDatabase db = dbHelper.getWritableDatabase();

Set the database version number to 3 to ensure that the tables in the database are the latest.

Now we have learned how to add tables and new columns. If a column in a table is useless, how can I delete this column? Unfortunately, SQLite does not support the column deletion function. In this case, most software ignores the column deletion function and will not be able to use it in the future, therefore, there is no simple solution to this need.

This is probably the way to upgrade the database table in traditional development. Although you can write such code, it indicates that you have a clear understanding of the Database Upgrade operations, but as the number of versions increases, the logic in the onUpgrade () method will also become more complicated. If you don't care about it, errors may occur. Therefore, if you can allow the code to automatically control the upgrade logic, rather than manually managing it, it would be better. Next we will learn how to use LitePal to upgrade the table.

Upgrade table using LitePal

After learning from the previous article, we know that LitePal Is An ORM framework and you are familiar with the table creation process. I believe you will be familiar with upgrading tables. Now we need to create a comment table to mimic the requirements in the traditional table upgrade method. What should I do in the first step? I believe you may have guessed it for a long time. Of course, you should first create a Comment class, as shown below:

Package com. example. databasetest. model; public class Comment {private int id; private String content; // automatically generate get and set methods ...}

OK. The Comment class contains the id and content fields, which means that the comment table has the id and content columns.

Modify the configuration in litepal. xml, add the Cooment class to the ing list, and add the version number to 1, as shown below:

<?xml version="1.0" encoding="utf-8"?><litepal>    <dbname value="demo" ></dbname>    <version value="2" ></version>    <list>        <mapping class="com.example.databasetest.model.News"></mapping>        <mapping class="com.example.databasetest.model.Comment"></mapping>    </list></litepal>

Yes, it's that simple. The upgrade is completed in just two steps. Now we only need to operate the database and the comment table will be automatically generated, as shown below:

SQLiteDatabase db = Connector.getDatabase();

The. table command is used to view the result, as shown in:

OK. The comment table has been released. Run The pragma command to view its table structure:

No problem. The comment table currently has the id and content columns, which are consistent with the fields in the Comment model class.

Now we have a new requirement. We need to add a publishdate column to the comment table. What should we do? Don't worry, follow your intuition. I believe you have guessed that you should add such a field to the Comment class, as shown below:

Public class Comment {private int id; private String content; private Date publishDate; // automatically generate get and set methods ...}

What then? The remaining operations are very simple. You only need to add 1 to the version number in litepal. xml, as shown below:

<litepal>    <dbname value="demo" ></dbname>    <version value="3" ></version>    ...</litepal>

In this way, the publishdate column will be automatically added to the comment table when we perform the next database operation. Call Connector. getDatabase () and query the comment table structure again, as shown below:

You can see that the publishdate column has been successfully added to the comment table.

Through the comparison of the two upgrade methods, I believe you have fully realized the convenience of using LitePal to upgrade the table. We do not need to write any upgrade-related logic or care about the version from which the program is upgraded. The only thing we need to do is determine what the latest Model structure is, then, set the litepal. if the version number in xml is added to 1, all the upgrade logic is automatically completed. LitePal makes upgrading database tables extremely simple, so many programmers can free themselves from the trouble of maintaining database table upgrades.

However, LitePal is obviously better. As mentioned above, the final conclusion about column deletion cannot be solved, because SQLite does not support column deletion commands. However, if you use LitePal, this problem can be solved simply. For example, if you do not want the publishdate column, you only need to delete it in the Comment class, add the version number to 1, and the column will disappear the next time you operate the database.

Some may ask, isn't SQLite not supporting the command to delete columns? Then how does LitePal do it? In fact, LitePal does not delete any column. It only first renames the comment table into a temporary table, and then generates a new Comment table based on the structure of the latest comment class, copy the data in the temporary table except publishdate to the new table, and delete the temporary table. Therefore, it seems that the column deletion function is enabled.

This is also the benefit of using the framework. without the help of the framework, we obviously will not waste weeks to write so much code to delete a column, but use the framework, we don't have to worry about the specific implementation logic. We just need to control the data structure of the model class.

In addition, if you want to delete a table, the operation is very simple. In the ing list of litepal. xml, the corresponding class is deleted, and the table does not exist. Some other upgrade operations are similar. I believe you can give the opposite picture, so I will not repeat it here.

Now, let's take a look at LitePal. In the next article, we will learn how to use LitePal to perform table join operations.

How can I write a database in android? For more information about how to create a table, see!

In android, database operations in android Application Development are hard to avoid not using databases. Let's talk about the operations in the android data library. I can't post them to you any more.
I. Introduction to basic knowledge about databases in android
1. What database is used
The database used in android is SQLite, a lightweight embedded open source database, which is built in c language. For more information, see the link.
2. Basic Database knowledge
It is necessary for me to quickly understand some basic concepts and basic statements in the SQL92 standard, just as I have not actually studied database technology, in this way, you will not be confused when using the Android database-related method to execute some database statements.
① Basic structure of the database-table
A table is the basic architecture for storing data in a database. The table is divided into column and row ). Each column represents a piece of data, and each column represents a part of the data. For example, if we have a table that records customer data, the column may include surname, name, address, city, country, birthday, etc. Each Table has a unique Name that allows you to locate it. A typical table structure is as follows:
Store_Information table
Store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 this table the table name is Store_Information, there are three columns in total, namely store_name, Sales, and Data. Four columns have been input, so there are four columns.
② Data Types
Unlike other databases, sqlite has no type. That is, when creating a table, you do not need to declare the type of data to be stored in each column. When you add data entries to the table, sqlite will automatically find the type of the stored data.
SQLite allows you to ignore the data type. However, we recommend that you specify the data type in the Create Table statement, because the data type facilitates program readability. SQLite supports common data types, such as VARCHAR, NVARCHAR, TEXT, INTEGER, FLOAT, BOOLEAN, CLOB, BLOB, TIMESTAMP, NUMERIC, VARYING, CHARACTER, NATl0NAI, and VARYINGCHARACTER. These data types are the standard database data types specified in the SQL92 standard. For more information, see the table below.
SQL database data type description bit integer bit data type is integer, its value can only be 0, 1 or null. This data type is used to store data with only two possible values, such as Yes, No, True, or Fa lse, On, or Off int integer int data types can be stored from-231 (-2147483648) an integer between 231 and 2147483 (647. This type can be used for almost all numeric data stored in the database. This data type occupies four bytes in the database. The smallint integer smallint data type can store integers from-215 (-32768) to 215 (32767. This type of data is useful for storing numeric data that is often limited to a specific range. This data type occupies 2 bytes of tinyi in the database... the remaining full text>

How to manage the database of WSS 30 (2) -- Database Operations

This makes many things easier to understand. All content of WSS 3.0, page files, website configurations, user settings, and website content are not saved in the form of files, but saved in the SQL database in the background. Therefore, in the IIS Directory, you cannot see all the htm files and user-uploaded documents that SharePoint should have as a website. By default, when WSS 3.0 is installed, the following databases are generated/created: SharePoint_AdminContent_GUID. This content database is used to store all Windows SharePoint Services 3.0 content. You can use the SharePoint Administration Center website to add a new database. SharePoint_Config this configuration database contains most of the databases used, including all Internet Information Services (IIS) websites, Web applications, Web parts packages, and website templates. WSS_Search _ computer name this database is used for search services and contains the required content for search. By using this database, the Windows SharePoint search service allows users to search for all website content on the server. WSS_Content this database is used to contain data specific to Web applications and their website sets. It contains content data, such as a document uploaded to a list or library. By default, a content database is created for each Web application. All new website sets added to the Web application use the same database. By default, these databases are stored in the following path: C: \ Windows \ SYSMSI \ SSEE \ MSSQL.2005 \ MSSQL \ Data: in many cases, users will make full use of the SharePoint document library function. Use SharePoint as a file server. Indeed, the SharePoint document management function is very good! Full-text search and permission management (combined with windows accounts !), Version control, web interface upload and download, these functions are all available, it is difficult to make people do not worry. However, unfortunately, as mentioned above, all content of WSS 3.0 is stored in the SQL database in the background, and all types of documents uploaded by users (MP3, movies, and rarfiles) it is also saved in the SQL database. This brings about a serious problem: SQL Server has a serious efficiency problem when processing a large number of/large binary data files, and occupies a high disk capacity. For example, a user uploads some RAR/ZIP files to a SharePoint website, each of which is relatively large, about MB. During the upload/download process, the processing speed will be very slow, and the server resources are very high. More importantly, the background database occupies a large amount of disk space! (Many are occupied by log files ). C: \ Windows \ SYSMSI \ SSEE \ MSSQL.2005 \ MSSQL \ Data Step 1: download and install Microsoft SQL Server Management Studio Express through \\. \ pipe \ mssql $ microsoft # ssee \ SQL \ query connection name, connect to the SQL database that enters the SharePoint background. Step 2: check which database file occupies a large amount of space. (If your server has multiple websites, you need to find them one by one .) Step 3: Completely back up the database to another directory. Step 4: contract the database or shrink the database file. (If the log file is too large, shrink the database file. Select log file to shrink .)