protected override void SetUp()
{
base.SetUp();
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("mysql");
dataSource.setDatabaseName("test");
dataSource.setServerName("localhost");
IDbConnection connection = dataSource.getConnection();
try {
PreparedStatement dropStatement =
connection.prepareStatement("DROP TABLE IF EXISTS " + DEFAULT_PREFERENCE_TABLE);
try
{
dropStatement.Execute();
} finally {
dropStatement.Close();
}
IDbCommand createStatement =
connection.prepareStatement("CREATE TABLE " + DEFAULT_PREFERENCE_TABLE + " (" +
DEFAULT_USER_ID_COLUMN + " VARCHAR(4) NOT NULL, " +
DEFAULT_ITEM_ID_COLUMN + " VARCHAR(4) NOT NULL, " +
DEFAULT_PREFERENCE_COLUMN + " FLOAT NOT NULL, " +
"PRIMARY KEY (" + DEFAULT_USER_ID_COLUMN + ", " +
DEFAULT_ITEM_ID_COLUMN + "), " +
"INDEX (" + DEFAULT_USER_ID_COLUMN + "), " +
"INDEX (" + DEFAULT_ITEM_ID_COLUMN + ") )");
try
{
createStatement.execute();
} finally {
createStatement.close();
}
IDbCommand insertStatement =
connection.prepareStatement("INSERT INTO " + DEFAULT_PREFERENCE_TABLE +
" VALUES (?, ?, ?)");
try {
String[] users =
new String[] { "A123", "A123", "A123", "B234", "B234", "C345", "C345", "C345", "C345", "D456" };
String[] itemIDs =
new String[] { "456", "789", "654", "123", "234", "789", "654", "123", "234", "456" };
double[] preferences = new double[] { 0.1, 0.6, 0.7, 0.5, 1.0, 0.6, 0.7, 1.0, 0.5, 0.1 };
for (int i = 0; i < users.Length; i++)
{
insertStatement.setString(1, users[i]);
insertStatement.setString(2, itemIDs[i]);
insertStatement.setDouble(3, preferences[i]);
insertStatement.execute();
}
}
finally
{
insertStatement.close();
}
} finally {
connection.close();
}
model = new MySQLJDBCDataModel(dataSource);
}
public static void GenerateDatabase(Context ctx)
{
IConnection DBConnection = null;
IConfigPersistent Config;
IPreparedStatement PreparedStatement;
try
{
// Create a configuration for the connection - parameters are the database name and the Android context
Config = DatabaseManager.CreateConfigurationFileAndroid(DatabaseName, ctx);
bool connected = false;
try
{
DBConnection = DatabaseManager.Connect(Config);
connected = true;
}
catch (Exception ex)
{
Log.Error("Connection error", ex.Message);
connected = false;
}
if (connected)
{
return;
}
// Connect to the database - CreateDatabase creates a new database
DBConnection = DatabaseManager.CreateDatabase(Config);
PreparedStatement = DBConnection.PrepareStatement(createTableRcustomer);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(createTableRdisc);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"CREATE TABLE IF NOT EXISTS ritem_categ (
id INTEGER NOT NULL,
item_categ_desc VARCHAR(120) NOT NULL,
PRIMARY KEY ( id ASC )
) ");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"
CREATE TABLE IF NOT EXISTS ritem_categ2 (
id INTEGER NOT NULL,
item_categ_desc VARCHAR(120) NULL,
PRIMARY KEY ( id ASC )
)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(createTableRitems);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"CREATE TABLE IF NOT EXISTS ritemlast (
id INTEGER NOT NULL,
cst_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
last_date DATE NOT NULL,
PRIMARY KEY ( id ASC, item_id ASC ),
FOREIGN KEY(item_id) REFERENCES ritems(id)
)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"CREATE TABLE IF NOT EXISTS rmemo (
id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
memo VARCHAR(4000) NULL,
memo_date TIMESTAMP NULL,
cst_id INTEGER NOT NULL,
PRIMARY KEY ( id ASC )
)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(CreateTableRstatistic);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(CreateTableRtransCust);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"CREATE TABLE IF NOT EXISTS rtrustees (
trus_id NUMERIC(6,0) NOT NULL,
trus_cod VARCHAR(10) NOT NULL,
trus_desc VARCHAR(120) NOT NULL,
PRIMARY KEY ( trus_id ASC )
)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"CREATE TABLE IF NOT EXISTS rusers (
deal_id integer NOT NULL,
user_id integer NOT NULL,
login_name VARCHAR(120) NOT NULL,
user_pass VARCHAR(120) NOT NULL,
user_active VARCHAR(120) NOT NULL,
PRIMARY KEY ( deal_id ASC )
)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@"CREATE TABLE IF NOT EXISTS rerrors (
error_id INTEGER NOT NULL DEFAULT AUTOINCREMENT,
message VARCHAR(512) NOT NULL,
stack VARCHAR(4096) NOT NULL,
error_date VARCHAR(64) NULL,
PRIMARY KEY ( error_id ASC )
)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(createTableRtransDet);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(createTableRtransHed);
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@" CREATE PUBLICATION pblErrors (
TABLE rerrors)");
PreparedStatement.Execute();
PreparedStatement.Close();
PreparedStatement = DBConnection.PrepareStatement(@" CREATE PUBLICATION pblUploadTrans (
TABLE rtrans_hed, TABLE rtrans_det)");
PreparedStatement.Execute();
PreparedStatement.Close();
//TABLE contact,
PreparedStatement = DBConnection.PrepareStatement(@" CREATE PUBLICATION pblUsers (
TABLE rusers)");
PreparedStatement.Execute();
PreparedStatement.Close();
//TABLE contact,
PreparedStatement = DBConnection.PrepareStatement(@" CREATE PUBLICATION pblMain1 (
TABLE rcustomer,
TABLE rdisc,
TABLE ritem_categ,
TABLE ritem_categ2,
TABLE ritemlast,
TABLE ritems,
TABLE rmemo,
TABLE rstatistic,
TABLE rtranscust,
TABLE rtrustees,
TABLE rtrans_det,
TABLE rtrans_hed,
TABLE rusers)");
PreparedStatement.Execute();
PreparedStatement.Close();
DBConnection.Commit();
}
catch (Java.Lang.Exception ex)
{
// Log any errors to the device debug log
Android.Util.Log.Error("UltraliteApplication", string.Format("An error has occurred: {0}", ex.Message));
}
finally
{
DBConnection.Release();
}
}
/// <summary>Populates the Access table with generated records.</summary>
/// <exception cref="Java.Sql.SQLException"/>
private void PopulateAccess()
{
PreparedStatement statement = null;
try
{
statement = connection.PrepareStatement("INSERT INTO Access(url, referrer, time)"
+ " VALUES (?, ?, ?)");
Random random = new Random();
int time = random.Next(50) + 50;
int ProbabilityPrecision = 100;
// 1 / 100
int NewPageProbability = 15;
// 15 / 100
//Pages in the site :
string[] pages = new string[] { "/a", "/b", "/c", "/d", "/e", "/f", "/g", "/h", "/i"
, "/j" };
//linkMatrix[i] is the array of pages(indexes) that page_i links to.
int[][] linkMatrix = new int[][] { new int[] { 1, 5, 7 }, new int[] { 0, 7, 4, 6 }
, new int[] { 0, 1, 7, 8 }, new int[] { 0, 2, 4, 6, 7, 9 }, new int[] { 0, 1 },
new int[] { 0, 3, 5, 9 }, new int[] { 0 }, new int[] { 0, 1, 3 }, new int[] { 0,
2, 6 }, new int[] { 0, 2, 6 } };
//a mini model of user browsing a la pagerank
int currentPage = random.Next(pages.Length);
string referrer = null;
for (int i = 0; i < time; i++)
{
statement.SetString(1, pages[currentPage]);
statement.SetString(2, referrer);
statement.SetLong(3, i);
statement.Execute();
int action = random.Next(ProbabilityPrecision);
// go to a new page with probability
// NEW_PAGE_PROBABILITY / PROBABILITY_PRECISION
if (action < NewPageProbability)
{
currentPage = random.Next(pages.Length);
// a random page
referrer = null;
}
else
{
referrer = pages[currentPage];
action = random.Next(linkMatrix[currentPage].Length);
currentPage = linkMatrix[currentPage][action];
}
}
connection.Commit();
}
catch (SQLException ex)
{
connection.Rollback();
throw;
}
finally
{
if (statement != null)
{
statement.Close();
}
}
}
