public static void DeleteReport(MoodReport report)
{
try {
Console.WriteLine("Delete report {0}", report.Id);
string imagesFolder = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "Reports", report.Id.ToString());
DirectoryInfo dir = new DirectoryInfo(imagesFolder);
Console.WriteLine("Lets delete image files");
if (dir.Exists)
{
dir.Delete(true);
}
Console.WriteLine("Deleted image files");
//
// foreach (var snapshot in report.Snapshots) {
// snapshot.Moods.DeleteAll ();
// }
//
// report.Snapshots.DeleteAll ();
DeleteReportSnapshots(report);
Console.WriteLine("Delete report");
var deleteReport = "delete from MoodReport where Id = @ReportId";
CSDatabase.ExecuteNonQuery(deleteReport, new { ReportId = report.Id });
//report.Delete ();
Console.WriteLine("Report deleted");
} catch (Exception ex) {
MyMoodLogger.Current.Error("Failed deleting report", ex, 1);
}
}
public void SetupServer()
{
CSConfig.SetDB(new CSDataProviderSqlServer("Initial Catalog=cstest;Data Source=DBSERV;User ID=nunit;PWD=nunit;"));
CSDatabase.ExecuteNonQuery(_sqlCreateTables);
}
public virtual void DeleteData()
{
CSDatabase.ExecuteNonQuery("delete from tblOrderItems");
CSDatabase.ExecuteNonQuery("delete from tblOrders");
CSDatabase.ExecuteNonQuery("delete from tblCustomers");
CSDatabase.ExecuteNonQuery("delete from tblSalesPeople");
CSDatabase.ExecuteNonQuery("delete from tblPaymentMethods");
}
private static void DeleteReportSnapshots(MoodReport report)
{
var deleteMoodSnapshotsSql = "delete from MoodSnapshot where Id in" +
"(select ms.Id from MoodSnapshot ms inner join Snapshot s on ms.SnapshotId = s.Id where s.MoodReportId = @ReportId)";
var deleteSnapshotSql = "delete from Snapshot where MoodReportId = @ReportId";
Console.WriteLine("Delete mood snapshots");
CSDatabase.ExecuteNonQuery(deleteMoodSnapshotsSql, new { ReportId = report.Id });
Console.WriteLine("Delete snapshots");
CSDatabase.ExecuteNonQuery(deleteSnapshotSql, new { ReportId = report.Id });
}
void delete_Click(object sender, EventArgs e)
{
var indice = (int)(((View)sender).Tag);
Android.App.AlertDialog.Builder builder = new Android.App.AlertDialog.Builder(Activity);
builder.SetMessage(Resource.String.msg_supprimer);
builder.SetPositiveButton(Resource.String.oui, delegate
{
CSDatabase.ExecuteNonQuery("DELETE FROM CategoriesArticlesConfig WHERE CategorieArticlesConfigID=" + list[indice].CategorieArticlesConfigID);
list.RemoveAt(indice);
NotifyDataSetChanged();
});
builder.SetNegativeButton(Resource.String.non, (Android.Content.IDialogInterfaceOnClickListener)null);
builder.Show();
}
public ProductsTableViewCtrl(IntPtr handle) :
base(handle)
{
string dbDirectory = Path.Combine(
NSBundle.MainBundle.BundlePath,
DataDirectory
);
string dbPath = Path.Combine(dbDirectory, DataFile);
CSConfig.SetDB(dbPath, SqliteOption.CreateIfNotExists, () =>
{
CSDatabase.ExecuteNonQuery(AmazonProductInfo.CreateDbQuery);
}
);
TableView.Source = new TableSource(this, AmazonProductInfo.All());
}
public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.vdb3"));
if (File.Exists(path))
{
File.Delete(path);
}
IVistaDBDatabase database = VistaDBEngine.Connections.OpenDDA().CreateDatabase(path, false, null, 0, 0, false);
database.Close();
CSConfig.SetDB(new CSDataProviderVistaDB(@"Data Source=" + path));
CSDatabase.ExecuteNonQuery(_sqlCreateTables);
}
// This method checks to see if the database exists, and if it doesn't, it creates
// it and inserts some data. It also sets our database to be the default database
// connection.
protected void CheckAndCreateDatabase(string dbName)
{
// determine whether or not the database exists
bool dbExists = File.Exists(GetDBPath(dbName));
// configure the current database, create if it doesn't exist, and then run the anonymous
// delegate method after it's created
CSConfig.SetDB(GetDBPath(dbName), SqliteOption.CreateIfNotExists, () => {
CSDatabase.ExecuteNonQuery("CREATE TABLE People (PersonID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName text, LastName text)");
// if the database had to be created, let's populate with initial data
if (!dbExists)
{
// declare vars
CSList <Person> people = new CSList <Person> ();
Person person;
// create a list of people that we're going to insert
person = new Person()
{
FirstName = "Peter", LastName = "Gabriel"
};
people.Add(person);
person = new Person()
{
FirstName = "Thom", LastName = "Yorke"
};
people.Add(person);
person = new Person()
{
FirstName = "J", LastName = "Spaceman"
};
people.Add(person);
person = new Person()
{
FirstName = "Benjamin", LastName = "Gibbard"
};
people.Add(person);
// save the people collection to the database
people.Save();
}
});
}
public void TypedQuery()
{
SetupTestData();
TestQueryClass[] items = CSDatabase.RunQuery <TestQueryClass>();
Assert.AreEqual(5, items.Length);
Assert.AreEqual(4, items[3].NumOrders);
Assert.AreEqual("Customer 1", items[0].Name);
Assert.AreEqual("Customer 2", items[1].Name);
Assert.AreEqual("Customer 3", items[2].Name);
Assert.AreEqual("Customer 4", items[3].Name);
TestQueryClass item = CSDatabase.RunSingleQuery <TestQueryClass>();
Assert.IsNotNull(item);
Assert.AreEqual("Customer 1", item.Name);
Assert.AreEqual(5, TestQuery.Run().Length);
}
public static Snapshot GetClosestSnapshot(MoodReport report, DateTime snapshotLocalTime)
{
//return report.Snapshots.ToList().Where(s => s.TimeOfSnapshotLocal <= snapshotLocalTime).OrderByDescending(s => s.TimeOfSnapshotLocal).FirstOrDefault();
Console.WriteLine("Fetching closest snapshot");
//Id TEXT PRIMARY KEY, MoodReportId TEXT, TimeOfSnapshot DATETIME, TimeOfSnapshotLocal DATETIME, TotalResponses INTEGER, IsFirstGlance INTEGER, CreatedOn datetime
var sql = "select Id from Snapshot where MoodReportId = @ReportId and TimeOfSnapshotLocal <= @SnapTime order by TimeOfSnapshotLocal desc limit 1";
var record = CSDatabase.RunQuery(sql, new { ReportId = report.Id, SnapTime = snapshotLocalTime }).FirstOrDefault();
object snapshotId = null;
record.TryGetValue("Id", out snapshotId);
//string snapshotId = CSDatabase.RunQuery(sql, new { ReportId = report.Id, SnapTime = snapshotLocalTime}).FirstOrDefault();
Snapshot snapshot = null;
if (snapshotId != null)
{
snapshot = Snapshot.ReadSafe(snapshotId.ToString());
}
//var utcTime = snapshotLocalTime.ToLocalTime(-(ApplicationState.Current.EventTimeOffset));
//var snapshot = Snapshot.OrderedList("TimeOfSnapshotLocal-", "MoodReport.Id = @ReportId and TimeOfSnapshotLocal <= @SnapTime", new { ReportId = report.Id, SnapTime = snapshotLocalTime}).FirstOrDefault();
Console.WriteLine("Got closest snapshot");
return(snapshot);
}
protected void InitDB(string dbName)
{
// The following line will tell CoolStorage where the database is,
// create it if it does not exist, and call a delegate which
// creates the necessary tables (only if the database file was
// created new)
CSConfig.SetDB(dbName, SqliteOption.CreateIfNotExists, () => {
CSDatabase.ExecuteNonQuery(_sqlCreateApplicationState);
CSDatabase.ExecuteNonQuery(_sqlCreateLog);
CSDatabase.ExecuteNonQuery(_sqlCreateMoodCategory);
CSDatabase.ExecuteNonQuery(_sqlCreateMood);
CSDatabase.ExecuteNonQuery(_sqlCreateActivity);
CSDatabase.ExecuteNonQuery(_sqlCreateMoodPrompt);
CSDatabase.ExecuteNonQuery(_sqlCreateMoodResponse);
CSDatabase.ExecuteNonQuery(_sqlCreateMoodReport);
CSDatabase.ExecuteNonQuery(_sqlCreateSnapshot);
CSDatabase.ExecuteNonQuery(_sqlCreateMoodSnapshot);
//indexes
CSDatabase.ExecuteNonQuery(_sqlCreateIxMoodPrompt);
CSDatabase.ExecuteNonQuery(_sqlCreateIxMoodResponse);
CSDatabase.ExecuteNonQuery(_sqlCreateIxMoodReport);
CSDatabase.ExecuteNonQuery(_sqlCreateIxSnapshot);
CSDatabase.ExecuteNonQuery(_sqlCreateIxMoodSnapshot);
});
MoodReport.AnyObjectDeleting += (MoodReport sender, ObjectDeleteEventArgs e) => {
Console.WriteLine("Delete report event");
sender.Snapshots.DeleteAll();
};
Snapshot.AnyObjectDeleting += (Snapshot sender, ObjectDeleteEventArgs e) => {
Console.WriteLine("Delete snapshot event");
sender.Moods.DeleteAll();
};
}
public void SetupServer()
{
CSConfig.SetDB(new CSDataProviderMySql("Server=192.168.1.41;Database=cstest;UID=nunit;PWD=nunit"));
CSDatabase.ExecuteNonQuery("drop table if exists tblCustomers");
CSDatabase.ExecuteNonQuery("drop table if exists tblCustomerPaymentMethodLinks");
CSDatabase.ExecuteNonQuery("drop table if exists tblOrderItems");
CSDatabase.ExecuteNonQuery("drop table if exists tblOrders");
CSDatabase.ExecuteNonQuery("drop table if exists tblSalesPeople");
CSDatabase.ExecuteNonQuery("drop table if exists tblCoolData");
CSDatabase.ExecuteNonQuery("drop table if exists tblPaymentMethods");
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID INTEGER PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID INTEGER PRIMARY KEY AUTO_INCREMENT,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price real NOT NULL,
Description varchar(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID INTEGER PRIMARY KEY AUTO_INCREMENT,
Date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState varchar(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID integer primary key auto_increment,
Name varchar(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID integer primary key auto_increment,
Name varchar(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID varchar(50) PRIMARY KEY,
Name varchar(50) NULL)");
// CSDatabase.ExecuteNonQuery(
//@"CREATE TABLE tblColdData (
// ColdDataID varchar(50) PRIMARY KEY,
// Name varchar(50) NULL)");
//
//
// CSDatabase.ExecuteNonQuery(
//
// @"CREATE TRIGGER
//newid
//BEFORE INSERT ON
//tblColdData
//FOR EACH ROW
//SET NEW.id = UUID()"
//
// );
}
public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.mdb"));
if (File.Exists(path))
{
File.Delete(path);
}
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5");
CSConfig.SetDB(new CSDataProviderAccess(path));
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID COUNTER PRIMARY KEY,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID counter PRIMARY KEY,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price double NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID counter PRIMARY KEY,
[Date] datetime NOT NULL DEFAULT DATE()+TIME(),
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID counter primary key,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID counter primary key,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID guid NOT NULL PRIMARY KEY,
Name text(50) NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblColdData (Name text(50) NULL)");
cat.let_ActiveConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
ADOX.Column column = new ADOX.Column();
column.Name = "ColdDataID";
column.Type = ADOX.DataTypeEnum.adGUID;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = false;
column.Properties["Fixed Length"].Value = true;
column.Properties["Jet OLEDB:AutoGenerate"].Value = true;
column.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
cat.Tables["tblColdData"].Columns.Append(column, ADOX.DataTypeEnum.adGUID, 0);
CSDatabase.ExecuteNonQuery("ALTER TABLE tblColdData ADD CONSTRAINT PK_COLD_DATA PRIMARY KEY (ColdDataID)");
}
public void SetupServer()
{
string path = "coolstorage.sqlite";
File.Delete(path);
CS.SetDB(path, SqliteOption.CreateIfNotExists | SqliteOption.UseConnectionPooling, () =>
{
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID INTEGER PRIMARY KEY AUTOINCREMENT,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price real NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
Date TEXT(30) NOT NULL DEFAULT CURRENT_TIMESTAMP,
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID integer primary key autoincrement,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID integer primary key autoincrement,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID text(50) PRIMARY KEY,
Name text(50) NULL)");
});
}
protected void UpdateReportDays(MoodReport report, IEnumerable <GlobalMoodReportModel> days)
{
Console.WriteLine("Update report days");
var allMoods = Mood.All().ToList();
NSDictionary userInfo = NSDictionary.FromObjectAndKey(NSObject.FromObject("Saving data ..."), new NSString("Status"));
NSNotificationCenter.DefaultCenter.PostNotificationName("RequestingReportStatus", null, userInfo);
int count = 1;
var insertSnapshotSql = "insert into Snapshot (Id, MoodReportId, TimeOfSnapshot, TimeOfSnapshotLocal, TotalResponses, CreatedOn) " +
" values (@Id, @MoodReportId, @TimeOfSnapshot, @TimeOfSnapshotLocal, @TotalResponses, @CreatedOn)";
var insertMoodSnapshotSql = "insert into MoodSnapshot (Id, SnapshotId, MoodId, ResponseCount, ResponsePercentage) " +
" values (@Id, @SnapshotId, @MoodId, @ResponseCount, @ResponsePercentage)";
foreach (var day in days)
{
foreach (var s in day.Snapshots)
{
userInfo = NSDictionary.FromObjectAndKey(NSObject.FromObject(string.Format("Saving snapshot {0}", count)), new NSString("Status"));
NSNotificationCenter.DefaultCenter.PostNotificationName("RequestingReportStatus", null, userInfo);
count++;
var snapshotId = System.Guid.NewGuid().ToString();
CSDatabase.ExecuteNonQuery(insertSnapshotSql, new{ Id = snapshotId,
MoodReportId = report.Id,
TimeOfSnapshot = s.t,
TimeOfSnapshotLocal = s.t.ToLocalTime(ApplicationState.Current.EventTimeOffset),
TotalResponses = s.r,
CreatedOn = DateTime.UtcNow });
// Snapshot snap = Snapshot.New ();
// snap.Id = System.Guid.NewGuid ().ToString ();
// snap.MoodReport = report;
// snap.TimeOfSnapshot = s.t;
// snap.TimeOfSnapshotLocal = s.t.ToLocalTime (ApplicationState.Current.EventTimeOffset);
// snap.TotalResponses = s.r;
// snap.IsFirstGlance = false;
// snap.CreatedOn = DateTime.UtcNow;
// snap.Save ();
//make sure a snapshot is recorde for all moods even if no data provided
var snapMoods = from m in allMoods
join d in s.d
on m.DisplayIndex equals d.i into gm
from subm in gm.DefaultIfEmpty()
select new {
Mood = m,
ResponseCount = subm == null ? 0 : subm.c,
ResponsePercentage = subm == null ? 0 : subm.p,
MoodType = m.MoodType
};
foreach (var d in snapMoods)
{
CSDatabase.ExecuteNonQuery(insertMoodSnapshotSql, new {
Id = System.Guid.NewGuid().ToString(),
SnapshotId = snapshotId,
MoodId = d.Mood.Id,
ResponseCount = d.ResponseCount,
ResponsePercentage = d.ResponsePercentage
});
// MoodSnapshot ms = MoodSnapshot.New ();
// ms.Id = System.Guid.NewGuid ().ToString ();
// ms.Snapshot = snap;
// ms.Mood = d.Mood;
// //ms.Name = d.Mood.Name;
// //ms.DisplayIndex = d.Mood.DisplayIndex;
// ms.ResponseCount = d.ResponseCount;
// ms.ResponsePercentage = d.ResponsePercentage;
// ms.Save ();
}
}
}
}
public static void Setup(TestContext context)
{
System.Diagnostics.Debug.WriteLine("[Constructor]");
CS.SetDB("test.db", SqliteOption.CreateAlways, null);
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID INTEGER PRIMARY KEY AUTOINCREMENT,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price real NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
Date TEXT(30) NOT NULL DEFAULT CURRENT_TIMESTAMP,
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID integer primary key autoincrement,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID integer primary key autoincrement,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID text(50) PRIMARY KEY,
Name text(50) NULL)");
}
public void SetupServer()
{
CSConfig.SetDB(new CSDataProviderOracle("user id=CS;password=ABCDEFG;data source=dev-cweber/xe"));
/*
* -- Script to create the user with necessary roles
* -- USER SQL
* CREATE USER CS IDENTIFIED BY ABCDEFG
* DEFAULT TABLESPACE "SYSTEM"
* TEMPORARY TABLESPACE "TEMP";
*
* -- ROLES
* GRANT "CONNECT" TO CS ;
* GRANT "DBA" TO "CS" ;
* ALTER USER "CS" DEFAULT ROLE "DBA","CONNECT";
*/
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblCustomers"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblCustomerPaymentMethodLinks"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblOrderItems"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblOrders"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblSalesPeople"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblCoolData"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop table tblPaymentMethods"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop sequence Customer_seq"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop sequence Order_seq"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop sequence SalesPerson_seq"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop sequence PaymentMethod_seq"));
DontCare(() => CSDatabase.ExecuteNonQuery("drop sequence OrderItem_seq"));
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomers (
CustomerID INTEGER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID INTEGER PRIMARY KEY,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price float NOT NULL,
Description varchar2(200) NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID INTEGER PRIMARY KEY,
""DATE"" DATE DEFAULT sysdate,
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState varchar2(50)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID integer primary key,
Name varchar2(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID integer primary key,
Name varchar2(50) NOT NULL,
SalesPersonType integer NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID RAW(16) PRIMARY KEY,
Name varchar2(50) NULL
)");
CSDatabase.ExecuteNonQuery(@"create sequence Customer_seq start with 1 increment by 1 nomaxvalue ");
CSDatabase.ExecuteNonQuery(@"create sequence Order_seq start with 1 increment by 1 nomaxvalue ");
CSDatabase.ExecuteNonQuery(@"create sequence SalesPerson_seq start with 1 increment by 1 nomaxvalue ");
CSDatabase.ExecuteNonQuery(@"create sequence PaymentMethod_seq start with 1 increment by 1 nomaxvalue ");
CSDatabase.ExecuteNonQuery(@"create sequence OrderItem_seq start with 1 increment by 1 nomaxvalue ");
}
public void SetupServer()
{
string fn = Path.GetTempFileName();
string path = Path.GetFullPath(fn);
if (File.Exists(path))
{
File.Delete(path);
}
if (File.Exists(path + "-journal"))
{
File.Delete(path + "-journal");
}
CSConfig.SetDB(new CSDataProviderSQLite(@"data source=" + path));
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID INTEGER PRIMARY KEY AUTOINCREMENT,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price real NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
Date TEXT(30) NOT NULL DEFAULT CURRENT_TIMESTAMP,
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID integer primary key autoincrement,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID integer primary key autoincrement,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID text(50) PRIMARY KEY,
Name text(50) NULL)");
}
