ANEXO IV: DIMENSIONAMIENTO DE LOS EJES
4.3. CÁLCULO A FATIGA
4.3.1. FACTORES CORRECTORES DE LÍMITES DE FATIGA
The Android SDK contains multiple classes that provide a level of abstraction between an app and the details of working with SQLite. These classes are located in the android.
database.sqlite package. Two of the most basic classes that are used to work with SQLite databases are SQLiteOpenHelper and SQLiteDatabase. These classes provide a Java API for low-level database access on Android.
SQLiteOpenHelper
The SQLiteOpenHelper class is used to manage the SQLite database file for a process.
Recall from Chapter 3, “An Introduction to SQLite,” that SQLite stores an entire database in a single file. SQLiteOpenHelper is responsible for creating the SQLite database as well as configuring the connections to the database and performing upgrade operations.
SQLiteOpenHelper is the main access point for an SQLite database in Android. While
SQLiteOpenHelper does not directly support manipulating the database via SQL, it does
ptg18221911 provide methods to get an SQLiteDatabase instance that supports interacting with the
database through SQL.
Because SQLiteOpenHelper is an abstract class, an app must provide a subclass that implements the SQLiteOpenHelper.onCreate() and SQLiteOpenHelper.onUpgrade()
methods as well as one of the constructor methods.
SQLiteOpenHelper Constructors
The SQLiteOpenHelper class contains two constructor methods:
■
Each constructor accepts a Context, a String, a SQLiteDatabase.CursorFactory, and an int. The CursorFactory is used to generate cursor objects in response to database query operations. Making this value null applies the default implementation.
The String parameter is used to define the name of the database file. The value of this parameter is the name of the SQLite database file that is stored in the Android file system.
This name is usually not important to an app’s Java code as an app typically does not interact with the SQLite database file directly. The database file name is important if the database needs to be inspected with external developer tools.
The int parameter defines the current schema version of the database. As the functionality of an app changes, the database may also need to evolve to support added functionality. It is typical for tables and/or views to be added to a database as the app evolves over time and gains functionality. Since requiring a user to uninstall/reinstall an app in order to upgrade leads to a bad experience, the SQLiteOpenHelper uses the schema version to trigger an upgrade process to allow the developer to provide a lightweight upgrade process for the user. When the schema needs to change, an incremented value can be passed to the constructor, and the SQLiteOpenHelper calls onUpgrade() to allow the database to be upgraded by the app.
The only difference between the constructors is that one includes a
DatabaseErrorHandler in the parameter list, which can be used to perform a custom action when Android detects that the database is corrupt.
Listing 4.1 shows a snippet of a class that extends SQLiteOpenHelper and its constructor.
ptg18221911 Android Database API 49
Listing 4.1 Implementing the SQLiteOpenHelper Constructor /* package */ class DevicesOpenHelper extends SQLiteOpenHelper {
private static final String TAG = DevicesOpenHelper.class.getSimpleName();
private static final int SCHEMA_VERSION = 1;
private static final String DB_NAME = “devices.db”;
private final Context context;
private static DevicesOpenHelper instance;
public synchronized static DevicesOpenHelper getInstance(Context context) { if (instance == null) {
instance = new DevicesOpenHelper(context.getApplicationContext());
}
return instance;
} /**
* Creates a new instance of the simple open helper.
*
* @param context Context to read assets. This will be helped by the
➥instance.
*/
private DevicesOpenHelper(Context context) { super(context, DB_NAME, null, SCHEMA_VERSION);
this.context = context;
}
Forcing an app to use the same instance of SQLiteOpenHelper can provide added thread safety if the database is accessed from multiple threads. Using the singleton pattern is one way to ensure that only one instance of the open helper is used through-out the app. In Listing 4.1, the DevicesOpenHelper is implemented as a singleton to ensure that all database access throughout the app uses the same instance to assure thread safety.
ptg18221911 The DevicesOpenHelper also uses constants to define the database name and schema
version, which are passed to the constructor of the parent class. The database file name is unlikely to change throughout the life of the app. However, the schema version will almost certainly change. If the schema needs to be updated, the global constant SCHEMA_VERSION
should be manually incremented in the DevicesOpenHelper class to reflect a new version.
This ensures that the onUpgrade() method is called to perform the upgrade operation.
SQLiteOpenHelper.onCreate()
The SQLiteOpenHelper.onCreate() method is used to create the database that the app will use. Like the Activity.onCreate() and Fragment.onCreate() methods, it is called only once when the database is being created for the first time. The SQLiteOpenHelper.
onCreate() method is the place where DDL can be used to create tables and views in the database. In addition, DML can be used to initialize any data the app might need.
While the SQLiteOpenHelper class cannot perform an SQL operation on its own, the
SQLiteOpenHelper.onCreate() method is passed an SQLiteDatabase object that can perform SQL operations on the database.
Listing 4.2 shows the implementation of DevicesOpenHelper.onCreate().
Listing 4.2 Implementing DevicesOpenHelper.onCreate()
@Override
public void onCreate(SQLiteDatabase db) { for (int i = 1; i <= SCHEMA_VERSION; i++) {
applySqlFile(db, i);
} }
The DevicesOpenHelper.onCreate() method makes multiple calls to the applySql()
method, passing the SQLiteDatabase instance as well as the schema version. The
applySql() method reads an SQL file from the assets resource and sends all the SQL statements from the file to the database.
Because the DevicesOpenHelper.onCreate() method is called only when the database is first created, the method loops through all the schema version files to create the latest version of the schema in the database. For the device database sample app, all the schema version files build upon each other, so it is necessary to run them all to have a complete database schema.
The DevicesOpenHelper.onCreate() uses asset files to read SQL statements to send to the database. However, the SQL statements needed to create the database can also be generated in Java code and sent, as Strings, to the SQLiteDatabase object.
SQLiteOpenHelper.onUpgrade()
The SQLiteOpenHelper.onUpgrade() method is called when Android detects that a database upgrade is needed. Android keeps track of the current schema version that is passed to the constructor of SQLiteOpenHelper by using SQLite’s PRAGMA user_version.
ptg18221911 Android Database API 51
SQLite PRAGMAs can be used to keep track of data that does not belong in a table because the PRAGMA data describes properties of the database itself. The user_version PRAGMA can be used by any application to store application-specific version data.
When the SQLiteOpenHelper detects that the current schema version is older than the version passed to the constructor, it calls the SQLiteOpenHelper.onUpgrade() method.
Listing 4.3 shows the implementation of DevicesOpenHelper.onUpgrade().
Listing 4.3 Implementing DevicesOpenHelper.onUpgrade()
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { for (int i = (oldVersion + 1); i <= newVersion; i++) {
applySqlFile(db, i);
} }
The onUpgrade() method is similar to the onCreate() method. Both use the
applySql() method to read a series of SQL files and apply the SQL statements to the database. The only difference between onCreate() and onUpgrade() is that onUpgrade()
is passed both the old schema version and the new schema version. This allows the
onUpgrade() method to process only SQL files that have not already been processed.
Listing 4.4 shows the implementation of applySql() that is used in both onCreate()
and onUpgrade().
Listing 4.4 Implementing applySql()
private void applySqlFile(SQLiteDatabase db, int version) { BufferedReader reader = null;
try {
String filename = String.format("%s.%d.sql", DB_NAME, version);
final InputStream inputStream = context.getAssets().open(filename);
reader = new BufferedReader(new InputStreamReader(inputStream));
final StringBuilder statement = new StringBuilder();
for (String line; (line = reader.readLine()) != null;) { if (BuildConfig.DEBUG) {
Log.d(TAG, "Reading line -> " + line);
}
ptg18221911 // Ignore empty lines
if (!TextUtils.isEmpty(line) && !line.startsWith("--")) { statement.append(line.trim());
}
if (line.endsWith(";")) { if (BuildConfig.DEBUG) {
Log.d(TAG, "Running statement " + statement);
}
db.execSQL(statement.toString());
statement.setLength(0);
} }
} catch (IOException e) {
Log.e(TAG, "Could not apply SQL file", e);
} finally {
if (reader != null) { try {
reader.close();
} catch (IOException e) {
Log.w(TAG, "Could not close reader", e);
} } } }
The applySql() method takes an SQLiteDatabase instance as a parameter as well as an int to indicate the schema version file that should be read. Using the DATABASE_NAME
constant and the schema version number, applySql() accesses the SQL file as an asset resource. It then reads each non-empty and non-comment line in the file until it finds a line that ends with a semicolon. These lines are concatenated to form an SQL statement that is then passed the SQLiteDatabase.execSQL(), which sends the statement to the actual SQLite database. The SQLiteDatabase.execSQL() method takes a String
representing raw SQL and runs the statements on the database.
ptg18221911 Android Database API 53
Database schema versions are handled by keeping multiple SQL files as asset resources and giving them a naming convention that includes the schema version in the file name.
Figure 4.1 shows the SQL resources in Android Studio.
The SQL files in Figure 4.1 represent the different database schema versions. Each of the SQL files follows a naming convention of devices.db.<schema_version>.sql. The
applySql() method looks for the file with the given schema version, reads its content, and sends the SQL statements to the database.
When extending SQLiteOpenHelper, the onCreate() and onUpgrade() methods are the only methods that must be implemented because they are abstract. However, there are other methods that can be useful to override, such as onConfigure() and
onDowngrade().
SQLiteOpenHelper.onConfigure()
The SQLiteOpenHelper.onConfigure() method is used to configure a connection to the database. Because this method performs the connection configuration, it is called before any other methods that may be used to manipulate the database (onCreate(),
onUpgrade(), onDowngrade()). Being called so early also means that the onConfigure()
method should not be used to make changes to the database as the database may be in an unpredictable state until one of the onCreate(), onUpgrade(), or onDowngrade()
methods is called.
Figure 4.1 SQL schema files
ptg18221911 Listing 4.5 shows the implementation of DevicesOpenHelper.onConfigure().
Listing 4.5 Implementing DevicesOpenHelper.onConfigure()
@Override
@TargetApi(Build.VERSION_CODES.JELLY_BEAN) public void onConfigure(SQLiteDatabase db) { super.onConfigure(db);
setWriteAheadLoggingEnabled(true);
db.setForeignKeyConstraintsEnabled(true);
}
The DevicesOpenHelper.onConfigure() method in Listing 4.5 enables write-ahead logging and foreign key support. This is also the method that can be used to set any
PRAGMA values that may be needed for a database.
Note
The SQLiteOpenHelper.onConfigure() method was introduced in API 16. Any devices running on an older version of Android need to handle the database connection configuration in a different way.
SQLiteOpenHelper.onDowngrade()
The SQLiteOpenHelper.onDowngrade() method is similar to the SQLiteOpenHelper.
onUpgrade() method, except it handles the case where the current schema version is higher than the new version. It has the same parameter list as the SQLiteOpenHelper.
onUpgrade() method, including two int parameters representing the current and new schema version values.
Putting It All Together
Listing 4.6 shows the entire implementation of DevicesOpenHelper.
Listing 4.6 Entire Implementation of DevicesOpenHelper
/* package */ class DevicesOpenHelper extends SQLiteOpenHelper {
private static final String TAG =DevicesOpenHelper.class.getSimpleName();
private static final int SCHEMA_VERSION = 3;
private static final String DB_NAME = "devices.db";
private final Context context;
ptg18221911 Android Database API 55
private static DevicesOpenHelper instance;
public synchronized static DevicesOpenHelper getInstance(Context ctx) { if (instance == null) {
instance = new DevicesOpenHelper(ctx.getApplicationContext());
}
return instance;
} /**
* Creates a new instance of the simple open helper.
*
* @param context Context to read assets. This will be helped by the
* instance.
*/
private DevicesOpenHelper(Context context) { super(context, DB_NAME, null, SCHEMA_VERSION);
this.context = context;
// This will happen in onConfigure for API >= 16
if (Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) { SQLiteDatabase db = getWritableDatabase();
db.enableWriteAheadLogging();
db.execSQL("PRAGMA foreign_keys = ON;");
} }
@Override
public void onCreate(SQLiteDatabase db) { for (int i = 1; i <= SCHEMA_VERSION; i++) {
applySqlFile(db, i);
} }
ptg18221911 @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
for (int i = (oldVersion + 1); i <= newVersion; i++) { applySqlFile(db, i);
} }
@Override
@TargetApi(Build.VERSION_CODES.JELLY_BEAN) public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
setWriteAheadLoggingEnabled(true);
db.setForeignKeyConstraintsEnabled(true);
}
private void applySqlFile(SQLiteDatabase db, int version) { BufferedReader reader = null;
try {
final InputStream inputStream =
context.getAssets().open(filename);
reader =
new BufferedReader(new InputStreamReader(inputStream));
final StringBuilder statement = new StringBuilder();
for (String line; (line = reader.readLine()) != null;) { if (BuildConfig.DEBUG) {
Log.d(TAG, "Reading line -> " + line);
}
// Ignore empty lines
if (!TextUtils.isEmpty(line) && !line.startsWith("--")) {
ptg18221911 Android Database API 57
statement.append(line.trim());
}
if (line.endsWith(";")) { if (BuildConfig.DEBUG) {
Log.d(TAG, "Running statement " + statement);
}
db.execSQL(statement.toString());
statement.setLength(0);
} }
} catch (IOException e) {
Log.e(TAG, "Could not apply SQL file", e);
} finally {
if (reader != null) { try {
reader.close();
} catch (IOException e) {
Log.w(TAG, "Could not close reader", e);
} } } } }
SQLiteDatabase
As discussed previously in this chapter, the SQLiteOpenHelper class is used to help manage creating and upgrading a database in Android. However, in order to actually use the database, an app needs to use additional classes that allow for interaction with the database. When using an open helper, the SQLiteDatabase class represents the connection to the database itself. SQLiteDatabase contains methods to interact with a database, including executing SQL statements. It is the class that will be used to perform typical SQL operations such as query(), create(), and delete() as well as allow for database configuration and transaction management.
ptg18221911 Before an app can interact with an SQLiteDatabase connection, it must first get an
instance of SQLiteDatabase. In most cases, apps use methods from SQLiteOpenHelper to get a reference to an SQLiteDatabase connection.
SQLiteOpenHelper contains two methods that return SQLiteDatabase
connections: SQLiteOpenHelper.getReadableDatabase() and SQLiteOpenHelper.
getWritableDatabase(). Both methods return an SQLiteDatabase or create one if necessary. However, as the method names indicate, getReadableDatabase() returns an
SQLiteDatabase that can be used for read operations, and getWritableDatabase()
returns an SQLiteDatabase that can be used for both reading and writing.
The actual database connection object that is returned by getReadableDatabase()
and getWritableDatabase() is cached inside SQLiteOpenHelper to improve performance. Additionally, once a writable database connection is created, getReadableDatabase() actually returns the same connection as
getWritableDatabase() since both methods return the cached version of the object.