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(); } } }