/*
* Copyright (C) 2012 The Android Open Source Project
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.motorolamobility.studio.android.db.core.model;
import static com.motorola.studio.android.common.log.StudioLogger.error;
import static com.motorola.studio.android.common.log.StudioLogger.info;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import org.eclipse.core.runtime.IPath;
import org.eclipse.core.runtime.IStatus;
import org.eclipse.core.runtime.Status;
import org.eclipse.datatools.connectivity.ConnectionProfileException;
import org.eclipse.datatools.connectivity.IConnectionProfile;
import org.eclipse.datatools.connectivity.IManagedConnection;
import org.eclipse.datatools.connectivity.ProfileManager;
import org.eclipse.datatools.connectivity.drivers.DriverManager;
import org.eclipse.datatools.connectivity.sqm.core.connection.ConnectionInfo;
import org.eclipse.datatools.connectivity.sqm.core.connection.DatabaseConnectionRegistry;
import org.eclipse.datatools.connectivity.sqm.core.rte.ICatalogObject;
import org.eclipse.datatools.connectivity.sqm.internal.core.connection.ConnectionInfoImpl;
import org.eclipse.datatools.modelbase.sql.schema.Catalog;
import org.eclipse.datatools.modelbase.sql.schema.Database;
import org.eclipse.datatools.modelbase.sql.schema.SQLSchemaPackage;
import org.eclipse.datatools.modelbase.sql.schema.Schema;
import org.eclipse.datatools.modelbase.sql.tables.Column;
import org.eclipse.datatools.modelbase.sql.tables.Table;
import org.eclipse.datatools.sqltools.data.internal.ui.editor.TableDataEditor;
import org.eclipse.datatools.sqltools.internal.refresh.ICatalogObject2;
import org.eclipse.datatools.sqltools.result.OperationCommand;
import org.eclipse.datatools.sqltools.result.ResultsViewAPI;
import org.eclipse.emf.common.util.EList;
import org.eclipse.osgi.util.NLS;
import org.eclipse.ui.IEditorPart;
import com.motorola.studio.android.common.CommonPlugin;
import com.motorola.studio.android.common.utilities.EclipseUtils;
import com.motorolamobility.studio.android.db.core.DbCoreActivator;
import com.motorolamobility.studio.android.db.core.exception.MotodevDbException;
import com.motorolamobility.studio.android.db.core.i18n.DbCoreNLS;
import com.motorolamobility.studio.android.db.core.model.Field.AutoIncrementType;
/**
* This class represents a datamodel. Is responsible to hold a IConnectionProfile to a database and execute operations with this profile.
*/
public class DbModel
{
static final String JDBC_DRIVER_INSTANCE_NAME = CommonPlugin.JDBC_DRIVER_INSTANCE_NAME;
public static final String DBNAME_PROPERTY =
"org.eclipse.datatools.connectivity.db.databaseName"; //$NON-NLS-1$
public static final String PROVIDER_ID =
"org.eclipse.datatools.enablement.sqlite.connectionProfile"; //$NON-NLS-1$
public static final String URL_PROPERTY = "org.eclipse.datatools.connectivity.db.URL"; //$NON-NLS-1$
public static final String JDBC_SQLITE_PREFIX = "jdbc:sqlite:"; //$NON-NLS-1$
public static final String LOCALPATH_PROPERTY = "com.motorola.studio.db.localPathProperty"; //$NON-NLS-1$
private final IConnectionProfile connProfile;
private final IPath dbPath;
/**
* Creates a new DbModel Object based on the sqlite database at dbPath
* @param dbPath SQLite3 database file path
* @throws MotodevDbException if dbPath does not contains a valid SQLite3 database file
*/
public DbModel(IPath dbPath) throws MotodevDbException
{
this(dbPath, false);
}
/**
* Creates a new DbModel Object creating a empty database file at dbPath if create is true.
* If create is false the behavior is the same as DbModel(Path dbPath)
* @param dbPath SQLite3 database file path
* @param create flag indicating if the database should be created
* @throws MotodevDbException if dbPath does not contains a valid SQLite3 database file
*/
public DbModel(IPath dbPath, boolean create) throws MotodevDbException
{
this(dbPath, create, false);
}
/**
* Creates a new DbModel Object creating a empty database file at dbPath if create is true.
* If create is false the behavior is the same as DbModel(Path dbPath)
* @param dbPath SQLite3 database file path
* @param create flag indicating if the database should be created
* @throws MotodevDbException if dbPath does not contains a valid SQLite3 database file
*/
public DbModel(IPath dbPath, boolean create, boolean overwrite) throws MotodevDbException
{
if (create)
{
File dbFile = dbPath.toFile();
try
{
DbCoreActivator.getDefault().copyTemplateDbFile(dbFile, overwrite);
}
catch (IOException e)
{
throw new MotodevDbException(e);
}
}
this.dbPath = dbPath;
if (isValidSQLiteDatabase(dbPath.toFile()))
{
connProfile = getProfile(dbPath);
}
else
{
throw new MotodevDbException(NLS.bind(DbCoreNLS.DbModel_Not_Valid_Database,
dbPath.toOSString()));
}
}
/**
* Checks if a compatible JDBC driver is registered. If not, registers one
*/
public static void assertDriverExistsAtModel()
{
DriverManager driverMan = DriverManager.getInstance();
String allDrivers = driverMan.getFullJarList();
String driverPath = getDriverPath();
if ((allDrivers == null) || (!allDrivers.contains(driverPath)))
{
String templateId = "org.eclipse.datatools.enablement.sqlite.3_5_9.driver"; //$NON-NLS-1$
driverMan.createNewDriverInstance(templateId, JDBC_DRIVER_INSTANCE_NAME, driverPath);
info("Created a MOTODEV Studio JDBC driver instance at Data Tools."); //$NON-NLS-1$
}
}
/**
* If a compatible JDBC driver is registered, removes it
*/
public static void deleteDriverFromModel()
{
DriverManager driverMan = DriverManager.getInstance();
String jarList = driverMan.getFullJarList();
if ((jarList != null) && (jarList.contains(DbModel.JDBC_DRIVER_INSTANCE_NAME)))
{
driverMan.removeDriverInstance(DbModel.JDBC_DRIVER_INSTANCE_NAME);
info("Removed the MOTODEV Studio JDBC driver instance from Data Tools."); //$NON-NLS-1$
}
}
private IConnectionProfile getProfile(IPath dbPath) throws MotodevDbException
{
String fullPath = dbPath.toOSString();
IConnectionProfile profile = null;
profile = ProfileManager.getInstance().getProfileByFullPath(fullPath);
if (profile == null)
{
Properties prop =
getBaseConnProperties(getDriverPath(), dbPath.lastSegment(),
dbPath.toOSString());
try
{
profile =
ProfileManager.getInstance().createProfile(fullPath, "", PROVIDER_ID, prop); //$NON-NLS-1$
profile.setBaseProperties(prop);
}
catch (ConnectionProfileException e)
{
throw new MotodevDbException(NLS.bind("Unable to create Profile for db {0}",
dbPath.toOSString()));
}
}
return profile;
}
/**
* Retrieves the location of the driver
* @return
*/
private static String getDriverPath()
{
String driverPath = null; //$NON-NLS-1$
driverPath = CommonPlugin.getDefault().getDriverPath();
if (driverPath == null)
{
driverPath = "";
}
return driverPath;
}
// /*
// * Retrieves the JDBC Sqlite3 driver file
// */
// private static File getDriver(String pathAtPlugin)
// {
// URL location = DbCoreActivator.getDefault().getBundle().getEntry(pathAtPlugin);
//
// debug("JDBC Driver Location:" + location + " JDBC Driver getBundle().getLocation():" //$NON-NLS-1$ //$NON-NLS-2$
// + DbCoreActivator.getDefault().getBundle().getLocation());
//
// File file = null;
// try
// {
// IPath p = new Path(FileLocator.toFileURL(location).getFile());
// debug("JDBC Driver Path:" + p.toOSString()); //$NON-NLS-1$
// file = p.toFile();
// }
// catch (IOException e)
// {
// error("Error while trying to locate jdbc driver into db plugin:" + e.getMessage()); //$NON-NLS-1$
// }
// return file;
//
// }
public static Properties getBaseConnProperties(String driverPath, String dbName, String dbPath)
{
Properties prop = new Properties();
prop.put("org.eclipse.datatools.connectivity.db.vendor", "SQLITE"); //$NON-NLS-1$ //$NON-NLS-2$
prop.put("org.eclipse.datatools.connectivity.db.password", ""); //$NON-NLS-1$ //$NON-NLS-2$
prop.put("org.eclipse.datatools.connectivity.driverDefinitionID", //$NON-NLS-1$
"DriverDefn.org.eclipse.datatools.enablement.sqlite.3_5_9.driver." //$NON-NLS-1$
+ JDBC_DRIVER_INSTANCE_NAME);
prop.put("org.eclipse.datatools.connectivity.drivers.defnType", //$NON-NLS-1$
"org.eclipse.datatools.enablement.sqlite.3_5_9.driver"); //$NON-NLS-1$
prop.put("org.eclipse.datatools.connectivity.db.savePWD", "false"); //$NON-NLS-1$ //$NON-NLS-2$
prop.put("org.eclipse.datatools.connectivity.db.connectionProperties", ""); //$NON-NLS-1$ //$NON-NLS-2$
prop.put("org.eclipse.datatools.connectivity.db.version", "3.5.9"); //$NON-NLS-1$ //$NON-NLS-2$
prop.put(DBNAME_PROPERTY, dbName);
prop.put("jarList", driverPath); //$NON-NLS-1$
prop.put("org.eclipse.datatools.connectivity.db.username", ""); //$NON-NLS-1$ //$NON-NLS-2$
prop.put("org.eclipse.datatools.connectivity.db.driverClass", "org.sqlite.JDBC"); //$NON-NLS-1$ //$NON-NLS-2$
prop.put(URL_PROPERTY, JDBC_SQLITE_PREFIX + dbPath); //$NON-NLS-1$
prop.put(LOCALPATH_PROPERTY, dbPath);
return prop;
}
public IStatus connect()
{
IPath dbPath = getDbPath();
File file = dbPath.toFile();
if (file.exists() && isValidSQLiteDatabase(file))
{
return connProfile.connect();
}
else
{
return new Status(IStatus.ERROR, DbCoreActivator.PLUGIN_ID, DbCoreNLS.bind(
DbCoreNLS.Invalid_Db_Error, dbPath));
}
}
public IStatus disconnect()
{
return connProfile.disconnect();
}
/**
* Create a table based on the infomration provided via param table, on this db model.
* @param table The {@link TableModel}
* @return IStatus.OK if the table was created successfully, IStatus.ERROR otherwise. The status message explains the fail reason.
*/
public IStatus createTable(TableModel table)
{
StringBuilder strBuilder = new StringBuilder("CREATE TABLE "); //$NON-NLS-1$
strBuilder.append(table.getName());
strBuilder.append("("); //$NON-NLS-1$
List<Field> fields = table.getFields();
boolean firstField = true;
for (Field field : fields)
{
if (firstField)
{
firstField = false;
}
else
{
strBuilder.append(", "); //$NON-NLS-1$
}
strBuilder.append(field.getName());
strBuilder.append(" "); //$NON-NLS-1$
strBuilder.append(field.getType().toString());
if (field.isPrimaryKey())
{
strBuilder.append(" PRIMARY KEY"); //$NON-NLS-1$
if (field.getAutoIncrementType() != AutoIncrementType.NONE)
{
strBuilder.append(" "); //$NON-NLS-1$
strBuilder.append(field.getAutoIncrementType().toString());
}
}
if ((field.getDefaultValue() != null) && (!field.getDefaultValue().equals(""))) //$NON-NLS-1$
{
strBuilder.append(" default \'"); //$NON-NLS-1$
strBuilder.append(field.getDefaultValue());
strBuilder.append("\'"); //$NON-NLS-1$
}
}
strBuilder.append(")"); //$NON-NLS-1$
return executeSingleStatement(strBuilder.toString());
}
/**
* Delete a table from this dbModel with the name tableName
* @param tableName The name of the table to be deleted
* @return IStatus.OK if the table was deleted successfully, IStatus.ERROR otherwise. The status message explains the fail reason.
*/
public IStatus deleteTable(String tableName)
{
return executeSingleStatement("DROP TABLE " + tableName); //$NON-NLS-1$
}
/**
* Retrieves the {@link Table} with name tableName from this dbModel
* @param tableName the name of the table within this dbModel to be retrieved.
* @return The {@link Table} object if existent, null otherwise.
*/
public Table getTable(String tableName)
{
List<Table> tables = getTables();
Table table = null;
for (Table t : tables)
{
if (t.getName().toUpperCase().equals(tableName.toUpperCase()))
{
table = t;
break;
}
}
return table;
}
/**
* Retrieves all tables from this dbModel.
* @return all tables from found on this dbModel. All catalogs and schemas are used during the search.
* An empty list is returned if there's no table on this dbModel.
*/
@SuppressWarnings("unchecked")
public List<Table> getTables()
{
List<Table> tables = new ArrayList<Table>();
ConnectionInfo connectionInfo = getConnectionInfo();
if (connectionInfo != null)
{
Database database = connectionInfo.getSharedDatabase();
connectionInfo = DatabaseConnectionRegistry.getConnectionForDatabase(database);
EList<Catalog> catalogs = database.getCatalogs();
for (Catalog catalog : catalogs)
{
EList<Schema> schemas = catalog.getSchemas();
schemas.addAll(database.getSchemas());
for (Schema schema : schemas)
{
//Schema must be refreshed in order to retrieve the latest information, instead of the cached info.
if (schema instanceof ICatalogObject2)
{
String context =
((ICatalogObject2) schema).getRefreshContext(new Integer(
SQLSchemaPackage.SCHEMA__TABLES));
((ICatalogObject2) schema).refresh(context);
}
else
{
((ICatalogObject) schema).refresh();
}
EList<Table> schemaTables = schema.getTables();
tables.addAll(schemaTables);
}
}
}
return tables;
}
/**
* Delete the db file represented by this dbModel.
* @return Status.OK if operation is successful, Status.ERROR otherwise. The status message explains the fail reason.
*/
public IStatus deleteDb()
{
IStatus status = Status.OK_STATUS;
if (connProfile.getConnectionState() != IConnectionProfile.DISCONNECTED_STATE)
{
disconnect();
}
try
{
cleanModel();
boolean deleteSuccesfull = dbPath.toFile().delete();
if (!deleteSuccesfull)
{
status =
new Status(IStatus.ERROR, DbCoreActivator.PLUGIN_ID, NLS.bind(
DbCoreNLS.DbModel_Could_Not_Delete_DbFile, dbPath.toOSString()));
}
}
catch (ConnectionProfileException e)
{
status =
new Status(IStatus.ERROR, DbCoreActivator.PLUGIN_ID, NLS.bind(
DbCoreNLS.DbModel_Could_Not_Disconnect_Profile, connProfile.getName()),
e);
}
return status;
}
/**
* @throws ConnectionProfileException
*/
public void cleanModel() throws ConnectionProfileException
{
disconnect();
ProfileManager.getInstance().deleteProfile(connProfile);
}
/**
* Verifies if the databaseFile is a valid SQLite3 file.
* @param databaseFile the SQLIte3 db file to be verified
* @return true if the file is a valid SQLite3 file or false otherwise.
*/
public static boolean isValidSQLiteDatabase(File databaseFile)
{
boolean result = true;
final int BYTE_ARRAY_SIZE = 16;
byte[] headerByteArray = new byte[16];
headerByteArray[0] = 0x53;
headerByteArray[1] = 0x51;
headerByteArray[2] = 0x4c;
headerByteArray[3] = 0x69;
headerByteArray[4] = 0x74;
headerByteArray[5] = 0x65;
headerByteArray[6] = 0x20;
headerByteArray[7] = 0x66;
headerByteArray[8] = 0x6f;
headerByteArray[9] = 0x72;
headerByteArray[10] = 0x6d;
headerByteArray[11] = 0x61;
headerByteArray[12] = 0x74;
headerByteArray[13] = 0x20;
headerByteArray[14] = 0x33;
headerByteArray[15] = 0x00;
byte[] fileByteArray = new byte[BYTE_ARRAY_SIZE];
FileInputStream fis = null;
try
{
fis = new FileInputStream(databaseFile);
fis.read(fileByteArray);
}
catch (Exception e)
{
result = false;
}
finally
{
if (fis != null)
{
try
{
fis.close();
}
catch (IOException e)
{
//Do nothing.
}
}
}
ByteArrayInputStream bais = null;
try
{
bais = new ByteArrayInputStream(fileByteArray);
for (int aux = 0; aux < BYTE_ARRAY_SIZE; aux++)
{
int myByte = bais.read();
if (myByte != headerByteArray[aux])
{
result = false;
}
}
}
finally
{
if (bais != null)
{
try
{
bais.close();
}
catch (IOException e)
{
//Do nothing.
}
}
}
return result;
}
private ConnectionInfo getConnectionInfo()
{
if (!isConnected())
{
connProfile.connect();
}
IManagedConnection managedConnection =
connProfile.getManagedConnection(ConnectionInfo.class.getName()); //$NON-NLS-1$
ConnectionInfo connectionInfo = null;
if (managedConnection != null)
{
connectionInfo = (ConnectionInfo) managedConnection.getConnection().getRawConnection();
}
return connectionInfo;
}
/**
* @return true if the profile is connected, false otherwise
*/
public boolean isConnected()
{
return connProfile.getConnectionState() == IConnectionProfile.CONNECTED_STATE;
}
private IStatus executeSingleStatement(String statement)
{
IStatus status = Status.OK_STATUS;
Connection connection = getManagedSqlConnection();
try
{
Statement sqlStatement = connection.createStatement();
sqlStatement.execute(statement);
}
catch (Exception e)
{
status =
new Status(IStatus.ERROR, DbCoreActivator.PLUGIN_ID, NLS.bind(
DbCoreNLS.DbModel_Could_Not_Execute_Statement, statement), e);
}
return status;
}
private Connection getManagedSqlConnection()
{
if (!isConnected())
{
connProfile.connect();
}
IManagedConnection managedConnection =
connProfile.getManagedConnection(Connection.class.getName());
Connection connection = (Connection) managedConnection.getConnection().getRawConnection();
return connection;
}
/**
* @return the absolute path for the db file
*/
public IPath getDbPath()
{
return dbPath;
}
/**
* @return the name of the associated connection profile
*/
public String getProfileName()
{
return connProfile.getName();
}
/**
* @return
*/
public Set<IEditorPart> getAssociatedEditors()
{
Collection<IEditorPart> allEditors = EclipseUtils.getAllOpenedEditors();
Set<IEditorPart> selectedEditors = new HashSet<IEditorPart>();
for (IEditorPart e : allEditors)
{
if (e instanceof TableDataEditor)
{
TableDataEditor tde = (TableDataEditor) e;
Table table = tde.getSqlTable();
Catalog cat = table.getSchema().getCatalog();
Database database =
cat != null ? cat.getDatabase() : table.getSchema().getDatabase();
ConnectionInfo connInfo =
DatabaseConnectionRegistry.getConnectionForDatabase(database);
if (connInfo != null)
{
IConnectionProfile editorProfile =
((ConnectionInfoImpl) connInfo).getConnectionProfile();
if (editorProfile == connProfile)
{
selectedEditors.add(e);
}
}
}
}
return selectedEditors;
}
private void sampleContents(Table table, Column column)
{
String tableName = table.getName();
StringBuilder queryBuilder = new StringBuilder("select "); //$NON-NLS-1$
if (column != null)
{
queryBuilder.append(column.getName());
}
else
{
queryBuilder.append("*"); //$NON-NLS-1$
}
queryBuilder.append(" from "); //$NON-NLS-1$
queryBuilder.append(tableName);
String queryString = queryBuilder.toString();
executeSingleStatement(queryString);
String cosummerName = null;
String dbName = null;
OperationCommand cmd =
new OperationCommand(OperationCommand.ACTION_EXECUTE, queryString, cosummerName,
connProfile.getName(), dbName);
ResultsViewAPI resultsView = ResultsViewAPI.getInstance();
resultsView.createNewInstance(cmd, null);
resultsView.appendStatusMessage(cmd, DbCoreNLS.DbModel_Sampling_Contents_From + tableName);
Connection managedSqlConnection = getManagedSqlConnection();
try
{
Statement statement = managedSqlConnection.createStatement();
ResultSet resultSet = statement.executeQuery(queryString);
resultsView.appendResultSet(cmd, resultSet);
resultsView.updateStatus(cmd, OperationCommand.STATUS_SUCCEEDED);
}
catch (SQLException e)
{
resultsView.appendThrowable(cmd, e);
resultsView.updateStatus(cmd, OperationCommand.STATUS_FAILED);
}
}
/**
* @param table
*/
public void sampleContents(Table table)
{
sampleContents(table, null);
}
/**
* @param column
*/
public void sampleContents(Column column)
{
sampleContents(column.getTable(), column);
}
/**
*
*/
public static void cleanPreviousProfiles()
{
ProfileManager profileManager = ProfileManager.getInstance();
IConnectionProfile[] profiles = profileManager.getProfiles();
for (IConnectionProfile profile : profiles)
{
try
{
profileManager.deleteProfile(profile);
}
catch (ConnectionProfileException e)
{
error(DbModel.class, "Could not delete all profiles", e); //$NON-NLS-1$
}
}
}
}