private static void HandleGroups(List <Group> groups, SQLiteConnection conn)
{
foreach (Group g in groups)
{
string sql = string.Format("SELECT GroupID, Name FROM DeviceGroups WHERE GroupID = {0}", g.id);
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader.GetString(1) != g.name)
{
Updater updater = new Updater("DeviceGroups", string.Format("GroupID = {0}", g.id), conn);
updater.Set("Name", g.name.Trim(), true);
updater.Execute();
}
}
else
{
Inserter insert = new Inserter("DeviceGroups", conn);
insert.Set("GroupID", g.id);
insert.Set("Name", g.name.Trim(), true);
insert.Execute();
}
}
}
}
public void Set(string path, string value, SQLiteConnection conn)
{
ILog log = LogManager.GetLogger(typeof(Attribute));
try
{
log.DebugFormat("Setting attribute '{0}' to '{1}'", path, value);
using (SQLiteTransaction t = conn.BeginTransaction())
{
Changer change = new Deleter("Attributes", $"Path = '{path}'", conn);
change.Execute();
change = new Inserter("Attributes", conn);
change.Set("Path", path, true);
change.Set("Value", value, false);
change.Execute();
t.Commit();
}
}
catch (Exception ex)
{
log.Error(ex);
}
}
/// <summary>
/// Used when a new device is being added to the system
/// </summary>
/// <param name="di">The device being added</param>
/// <param name="conn">The DB connection to use</param>
private static void AddDevice(DeviceInfo di, DateTimeOffset timestamp, SQLiteConnection conn)
{
Inserter inserter = new Inserter("Devices", conn);
if (di.id >= 0)
{
inserter.Set("DeviceID", di.id);
}
inserter.Set("Name", di.name, true);
inserter.Set("Type", (int)di.type);
inserter.Set("IPAddress", di.ipAddress, true);
inserter.Set("Username", di.username, true);
SimpleEncryptDecrypt sed = new SimpleEncryptDecrypt();
inserter.Set("Password", sed.Encrypt(di.password), true);
inserter.Set("DateActivated", timestamp);
if (di.groupID < 0)
{
inserter.SetNull("GroupID");
}
else
{
inserter.Set("GroupID", di.groupID);
}
inserter.Execute();
long device_id = conn.LastInsertRowId;
Database.AddCollectors(di, device_id, timestamp, conn);
}
private void UpdateMostRecentData(long data_id, SQLiteConnection conn)
{
Changer change = new Deleter("MostRecentDataPerCollector", $"CollectorID = {DCContext.ID.ID}", conn);
change.Execute();
change = new Inserter("MostRecentDataPerCollector", conn);
change.Set("CollectorID", DCContext.ID.ID);
change.Set("DataID", data_id);
change.Execute();
}
public long InsertData(SQLiteConnection conn)
{
Inserter insert = new Inserter("Data", conn);
insert.Set("CollectorID", DCContext.ID.ID);
insert.Set("Value", Value, false);
insert.Set("TimeStamp", Timestamp);
insert.Execute();
UpdateMostRecentData(conn.LastInsertRowId, conn);
return(conn.LastInsertRowId);
}
private static void Insert(SQLiteConnection conn, int count, Dictionary <int, string> dict)
{
Random r = new Random();
for (int i = 0; i < count; ++i)
{
int a = r.Next();
string b = Guid.NewGuid().ToString();
Inserter inserter = new Inserter("Temp", conn);
inserter.Set("A", a);
inserter.Set("B", b, false);
inserter.Execute();
dict[a] = b;
}
}
/// <summary>
/// Used to make sure the *Type tables are updated
/// </summary>
/// <param name="conn">The connection to the database</param>
/// <param name="values">The key/value pair that needs to exist in the DB. If there's a missing entry, or the description
/// changed, we'll insert or update as appropriate</param>
/// <param name="table">The table we're going to be updating</param>
/// <param name="type_column">The name of the type column.</param>
public static void PopulateTypesTable(this SQLiteConnection conn, Dictionary <int, string> values, string table, string type_column)
{
ILog log = LogManager.GetLogger(typeof(Extensions));
Dictionary <int, string> db_values = new Dictionary <int, string>();
string select_query = string.Format("SELECT {0}, Description FROM {1};", type_column, table);
using (SQLiteCommand command = new SQLiteCommand(select_query, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
db_values[reader.GetInt32(0)] = reader.GetString(1);
}
}
List <int> db_types = new List <int>(db_values.Keys);
List <int> types = new List <int>(values.Keys);
db_types.ForEach(t => types.Remove(t));
// Whatever's left needs to be added
foreach (int type in types)
{
log.Info($"Inserting {type}=>{values[type]} into {table}");
Inserter inserter = new Inserter(table, conn);
inserter.Set(type_column, type);
inserter.Set("Description", values[type], false);
inserter.Execute();
db_values[type] = values[type];
}
// Now compare the existing descriptions with the descriptions in values
foreach (int type in values.Keys)
{
if (values[type] != db_values[type])
{
log.Info($"Changing {type}=>{db_values[type]} to {values[type]} in {table}");
Updater updater = new Updater(table, $"{type_column} = {type}", conn);
updater.Set("Description", values[type], false);
updater.Execute();
}
}
}
public void ProperlyHandleDates()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT NOT NULL)");
Assert.True(conn.DoesTableExist("Temp"));
Dictionary <int, DateTimeOffset> dict = new Dictionary <int, DateTimeOffset>();
Random r = new Random();
int count = 100;
for (int i = 0; i < count; ++i)
{
int a = r.Next();
DateTimeOffset b = new DateTimeOffset(2018, r.Next(1, 12), r.Next(1, 28), r.Next(0, 23), r.Next(0, 59), r.Next(0, 59), r.Next(0, 999), TimeSpan.FromHours(r.Next(-12, 12)));
Inserter ins = new Inserter("Temp", conn);
ins.Set("A", a);
ins.Set("B", b);
ins.Execute();
dict[a] = b;
}
foreach (int a in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {a}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.True(read);
if (read)
{
DateTimeOffset b = DateTimeOffset.Parse(reader.GetString(0));
Assert.Equal(dict[a], b);
}
}
}
}
}
}
public void ProperlyHandleLongs()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B INTEGER NOT NULL)");
Assert.True(conn.DoesTableExist("Temp"));
Dictionary <int, long> dict = new Dictionary <int, long>();
Random r = new Random();
int count = 100;
for (int i = 0; i < count; ++i)
{
int a = r.Next();
long b = r.NextLong();
Inserter ins = new Inserter("Temp", conn);
ins.Set("A", a);
ins.Set("B", b);
ins.Execute();
dict[a] = b;
}
foreach (int a in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {a}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.True(read);
if (read)
{
long b = reader.GetInt64(0);
Assert.Equal(dict[a], b);
}
}
}
}
}
}
/// <summary>
/// Create a new Hyper file with a single table and write some data into it.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Insert data into a single tables within a new Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to hyper and create new Hyper file "superstore.hyper".
// Replaces file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "superstore.hyper", CreateMode.CreateAndReplace))
{
// The table is called "Extract" and will be created in the "Extract" schema.
// This has historically been the default table name and schema for extracts created by Tableau.
TableName extractTable = new TableName("Extract", "Extract");
TableDefinition extractTableDefinition = new TableDefinition(extractTable)
.AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Customer Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Loyalty Reward Points", SqlType.BigInt(), Nullability.NotNullable)
.AddColumn("Segment", SqlType.Text(), Nullability.NotNullable);
// Create the schema and the table
connection.Catalog.CreateSchema("Extract");
connection.Catalog.CreateTable(extractTableDefinition);
// Insert data into the "Extract"."Extract" table
using (Inserter inserter = new Inserter(connection, extractTable))
{
inserter.AddRow("DK-13375", "Dennis Kane", 518, "Consumer");
inserter.AddRow("EB-13705", "Ed Braxton", 815, "Corporate");
inserter.Execute();
}
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}");
Console.WriteLine($"Table {extractTable} has a count of {count} rows");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
public void NotBeNullable()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT NOT NULL)");
Assert.True(conn.DoesTableExist("Temp"));
Inserter i = new Inserter("Temp", conn);
i.Set("A", 1);
i.SetNull("B");
Assert.Throws <SQLiteException>(() => i.Execute());
}
}
}
public static void SetValue(string path, string value, bool normalize, DateTimeOffset timestamp, SQLiteConnection conn)
{
if (string.IsNullOrEmpty(path))
{
return;
}
string existing_value = GetValue(path, conn);
if (value != existing_value)
{
Clear(path, conn);
Inserter insert = new Inserter("Configuration", conn);
insert.Set("Path", path, true);
insert.Set("Value", value ?? string.Empty, normalize);
insert.Set("DateAdded", timestamp);
insert.Set("IsValid", 1);
insert.Execute();
}
}
/// <summary>
/// Push down computations to Hyper during Insertion using expressions
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Push down computations to Hyper during Insertion using Expressions");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to hyper and create new Hyper file "superstore.hyper".
// Replaces file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "orders.hyper", CreateMode.CreateAndReplace))
{
// The table is called "Extract" and will be created in the "Extract" schema.
// This has historically been the default table name and schema for extracts created by Tableau.
TableName extractTable = new TableName("Extract", "Extract");
TableDefinition extractTableDefinition = new TableDefinition(extractTable)
.AddColumn("Order ID", SqlType.Int(), Nullability.NotNullable)
.AddColumn("Ship Timestamp", SqlType.Timestamp(), Nullability.NotNullable)
.AddColumn("Ship Mode", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Ship Priority", SqlType.Int(), Nullability.NotNullable);
// Create the schema and the table
connection.Catalog.CreateSchema("Extract");
connection.Catalog.CreateTable(extractTableDefinition);
// Hyper API's Inserter allows users to transform data during insertion.
// To make use of data transformation during insertion, the inserter requires the following inputs
// 1. The connection to the Hyper instance containing the table.
// 2. The table name or table defintion into which data is inserted.
// 3. List of Inserter.ColumnMapping.
// This list informs the inserter how each column in the target table must be tranformed.
// The list must contain all the columns into which data is inserted.
// "Inserter.ColumnMapping" maps a valid SQL expression (if any) to a column in the target table
// For example new Inserter.ColumnMapping("target_column", $"{Sql.EscapeName("colA")}*{Sql.EscapeName("colB")})
// The column "target_column" contains the product of "colA" and "colB" after successful insertion.
// SQL expression string is optional in Inserter.ColumnMapping.
// For a column without any transformation (identity transformation) only the column name is required.
// For example new Inserter.ColumnMapping("no_data_transformation_column")
// 4. Inserter Definition, a list of column definitions for all the input values provided during insertion.
// Inserter definition contains the column definition for the values that are inserted.
List <TableDefinition.Column> inserterDefinition = new List <TableDefinition.Column>();
inserterDefinition.Add(new TableDefinition.Column("Order ID", SqlType.Int(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Ship Timestamp Text", SqlType.Text(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Ship Mode", SqlType.Text(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Ship Priority Text", SqlType.Text(), Nullability.NotNullable));
// Column 'Order Id' is inserted into "Extract"."Extract" as-is.
// Column 'Ship Timestamp' in "Extract"."Extract" of timestamp type is computed from Column 'Ship Timestamp Text' of text type using 'to_timestamp()'.
// Column 'Ship Mode' is inserted into "Extract"."Extract" as-is.
// Column 'Ship Priority' is "Extract"."Extract" of integer type is computed from Colum 'Ship Priority Text' of text type using 'CASE' statement.
string textToTimeStampExpression = $"to_timestamp({Sql.EscapeName("Ship Timestamp Text")}, {Sql.EscapeStringLiteral("YYYY-MM-DD HH24:MI:SS")})";
string shipPriorityAsIntCaseExpression = $"CASE {Sql.EscapeName("Ship Priority Text")}" +
$" WHEN {Sql.EscapeStringLiteral("Urgent")} THEN 1 " +
$" WHEN {Sql.EscapeStringLiteral("Medium")} THEN 2 " +
$" WHEN {Sql.EscapeStringLiteral("Low")} THEN 3 END";
List <Inserter.ColumnMapping> columnMappings = new List <Inserter.ColumnMapping>();
columnMappings.Add(new Inserter.ColumnMapping("Order ID"));
columnMappings.Add(new Inserter.ColumnMapping("Ship Timestamp", textToTimeStampExpression));
columnMappings.Add(new Inserter.ColumnMapping("Ship Mode"));
columnMappings.Add(new Inserter.ColumnMapping("Ship Priority", shipPriorityAsIntCaseExpression));
// Insert data into the "Extract"."Extract" table with expressions.
using (Inserter inserter = new Inserter(connection, extractTable, columnMappings, inserterDefinition))
{
inserter.AddRow(399, "2012-09-13 10:00:00", "Express Class", "Urgent");
inserter.AddRow(530, "2012-07-12 14:00:00", "Standard Class", "Low");
inserter.Execute();
}
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column.
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}");
Console.WriteLine($"Table {extractTable} has a count of {count} rows");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
public void InsertNullOK()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery(CreateTable);
Assert.True(conn.DoesTableExist("Temp"));
int count = 100;
Random r = new Random();
Dictionary <int, string> dict = new Dictionary <int, string>();
for (int i = 0; i < count; ++i)
{
Inserter inserter = new Inserter("Temp", conn);
int a = r.Next();
inserter.Set("A", a);
// Make every other one null
if (i % 2 == 0)
{
string b = Guid.NewGuid().ToString();
inserter.Set("B", b, false);
dict[a] = b;
}
else
{
inserter.SetNull("B");
dict[a] = null;
}
inserter.Execute();
}
foreach (int a in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {a}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.True(read);
if (read)
{
if (dict[a] != null)
{
Assert.False(reader.IsDBNull(0));
string b = reader.GetString(0);
Assert.Equal(dict[a], b);
}
else
{
Assert.True(reader.IsDBNull(0));
Assert.Throws <InvalidCastException>(() => reader.GetString(0));
}
}
}
}
}
}
}
/// <summary>
/// Create a new Hyper file with a single table and write spatial data into it.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Insert spatial data into a single tables within a new Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to hyper and create new Hyper file "spatial_data.hyper".
// Replaces file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "spatial_data.hyper", CreateMode.CreateAndReplace))
{
// The table is called "Extract" and will be created in the "Extract" schema.
// This has historically been the default table name and schema for extracts created by Tableau.
TableName extractTable = new TableName("Extract", "Extract");
TableDefinition extractTableDefinition = new TableDefinition(extractTable)
.AddColumn("Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Location", SqlType.Geography(), Nullability.NotNullable);
// Create the schema and the table
connection.Catalog.CreateSchema("Extract");
connection.Catalog.CreateTable(extractTableDefinition);
// Hyper API's Inserter allows users to transform data during insertion.
// To make use of data transformation during insertion, the inserter requires the following inputs
// 1. The connection to the Hyper instance containing the table.
// 2. The table name or table defintion into which data is inserted.
// 3. List of Inserter.ColumnMapping.
// This list informs the inserter how each column in the target table must be tranformed.
// The list must contain all the columns into which data is inserted.
// "Inserter.ColumnMapping" maps a valid SQL expression (if any) to a column in the target table
// For example new Inserter.ColumnMapping("target_column", $"{Sql.EscapeName("colA")}*{Sql.EscapeName("colB")})
// The column "target_column" contains the product of "colA" and "colB" after successful insertion.
// SQL expression string is optional in Inserter.ColumnMapping.
// For a column without any transformation (identity transformation) only the column name is required.
// For example new Inserter.ColumnMapping("no_data_transformation_column")
// 4. Inserter Definition, a list of column definitions for all the input values provided during insertion.
// Inserter definition contains the column definition for the values that are inserted.
// The data input has two text values Name and Location_as_text.
List <TableDefinition.Column> inserterDefinition = new List <TableDefinition.Column>();
inserterDefinition.Add(new TableDefinition.Column("Name", SqlType.Text(), Nullability.NotNullable));
inserterDefinition.Add(new TableDefinition.Column("Location_as_text", SqlType.Text(), Nullability.NotNullable));
// Column 'Name' is inserted into "Extract"."Extract" as-is.
// Column 'Location' in "Extract"."Extract" of geography type is computed from Column 'Location_as_text' of text type
// using the expression 'CAST("Location_as_text") AS GEOGRAPHY'.
// Inserter.ColumnMapping is used for mapping the CAST expression to Column 'Location'.
string textToGeographyCastExpression = $"CAST({Sql.EscapeName("Location_as_text")} AS GEOGRAPHY)";
List <Inserter.ColumnMapping> columnMappings = new List <Inserter.ColumnMapping>();
columnMappings.Add(new Inserter.ColumnMapping("Name"));
columnMappings.Add(new Inserter.ColumnMapping("Location", textToGeographyCastExpression));
// Insert spatial data into the "Extract"."Extract" table using CAST expression.
using (Inserter inserter = new Inserter(connection, extractTableDefinition, columnMappings, inserterDefinition))
{
inserter.AddRow("Seattle", "point(-122.338083 47.647528)");
inserter.AddRow("Munich", "point(11.584329 48.139257)");
inserter.Execute();
}
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column.
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {extractTable}");
Console.WriteLine($"Table {extractTable} has a count of {count} rows");
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
/// <summary>
/// Updates a device's status. If the new status is null, the existing status should be invalidated and no new
/// entry added. If the new status is non-null, see it the existing status matches the new status. If the new
/// and old status are identical, do nothing. If the new and old status are different somehow (the status itself,
/// the is-alarm flag or the message are different), invalidate the old status and insert a new status.
///
/// A single device may have different statuses, so we need to restrict our decisions to a set of types, which
/// is included in the 3rd parameter. For example, the memory statuses are adequate, low, or critically low. Only
/// one of these should be 'active' at a given time.
/// </summary>
/// <param name="device_id">The ID of the device we're checking</param>
/// <param name="type">The new type. Can be null to indicate the status should be cleared</param>
/// <param name="statuses">The full set of statuses we should be considering. Will be a subset of all
/// the statuses a device can have.</param>
/// <param name="message">A message regarding the status</param>
/// <param name="conn">The DB connection to use</param>
protected static void SetDeviceStatus(long device_id, EStatusType?type, List <EStatusType> statuses, string message, SQLiteConnection conn)
{
if (type.HasValue)
{
Debug.Assert(statuses.Contains(type.Value));
}
EAlertLevel alert_level = (type.HasValue ? type.Value.GetAlertLevel() : null) ?? EAlertLevel.Normal;
string in_clause = statuses.Join();
string clause = $"DeviceID = {device_id} AND StatusType IN ({in_clause}) AND IsValid = 1";
string sql = $"SELECT StatusType, IsAlarm, Message FROM DeviceStatus WHERE {clause};";
bool insert = type.HasValue;
bool remove = type.HasValue == false;
if (type.HasValue)
{
// We may be inserting a new type. We need to see if the current value for the device/status is the
// same, or has changed. If it's the same we don't need to do anything. If it's changed, we'll want to
// mark the old value as invalid, and insert the new value.
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
EStatusType existing_type = (EStatusType)reader.GetInt32(0);
EAlertLevel existing_alert_level = (EAlertLevel)reader.GetInt32(1);
string existing_message = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
// An existing record exists, so insert the new one and update the old one if something's changed.
// If nothing changed, we'll leave the old one alone
bool something_changed =
(type.Value != existing_type) ||
(alert_level != existing_alert_level) ||
(string.Compare(message, existing_message) != 0);
// If something has changed, we'll want to remove the old version, and insert the new
// version. If nothing changed, we don't want to do either.
insert = remove = something_changed;
}
// If it wasn't found, just insert, which is the default
}
}
// In this case there's no status value, so that means were clearing out the old one, if it exists.
if (insert || remove)
{
if (remove)
{
Updater updater = new Updater("DeviceStatus", clause, conn);
updater.Set("IsValid", 0);
updater.Execute();
}
if (insert)
{
Inserter inserter = new Inserter("DeviceStatus", conn);
inserter.Set("DeviceID", device_id);
inserter.Set("StatusType", (int)type);
inserter.Set("IsAlarm", (int)alert_level);
inserter.Set("Date", DateTimeOffset.Now);
inserter.Set("Message", message, false);
inserter.Set("IsValid", 1);
inserter.Execute();
}
}
// else, no change
}
protected override void CloseLogic()
{
_inserter.Execute();
}
/// <summary>
/// Create a new Hyper file with multiple tables and write some data into them.
/// </summary>
/// <param name="exampleDataDir">Path to the directory with example data.</param>
public override void Execute(string exampleDataDir)
{
Console.WriteLine("EXAMPLE - Insert data into multiple tables within a new Hyper file.");
// Start the Hyper process with telemetry enabled.
using (HyperProcess hyper = new HyperProcess(Telemetry.SendUsageDataToTableau))
{
// Connect to Hyper and create new Hyper file "superstore.hyper".
// It replaces the file if it already exists when CreateMode.CreateAndReplace is set.
using (Connection connection = new Connection(hyper.Endpoint, "superstore.hyper", CreateMode.CreateAndReplace))
{
// Create definitions for the tables to be created.
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition orders = new TableDefinition("Orders")
.AddColumn("Address ID", SqlType.SmallInt(), Nullability.NotNullable)
.AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Order Date", SqlType.Date(), Nullability.NotNullable)
.AddColumn("Order ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Ship Date", SqlType.Date())
.AddColumn("Ship Mode", SqlType.Text());
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition customer = new TableDefinition("Customer")
.AddColumn("Customer ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Customer Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Loyalty Reward Points", SqlType.BigInt(), Nullability.NotNullable)
.AddColumn("Segment", SqlType.Text(), Nullability.NotNullable);
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition products = new TableDefinition("Products")
.AddColumn("Category", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Product ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Product Name", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Sub-Category", SqlType.Text(), Nullability.NotNullable);
// Since the table name is not prefixed with an explicit schema name, the table will reside in the default "public" namespace.
TableDefinition lineItems = new TableDefinition("Line Items")
.AddColumn("Line Item ID", SqlType.BigInt(), Nullability.NotNullable)
.AddColumn("Order ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Product ID", SqlType.Text(), Nullability.NotNullable)
.AddColumn("Sales", SqlType.Double(), Nullability.NotNullable)
.AddColumn("Quantity", SqlType.SmallInt(), Nullability.NotNullable)
.AddColumn("Discount", SqlType.Double())
.AddColumn("Profit", SqlType.Double(), Nullability.NotNullable);
// Create tables in the database.
connection.Catalog.CreateTable(orders);
connection.Catalog.CreateTable(customer);
connection.Catalog.CreateTable(products);
connection.Catalog.CreateTable(lineItems);
// Insert data into Orders table.
using (Inserter inserter = new Inserter(connection, orders))
{
inserter.AddRow(399, "DK-13375", new Date(2012, 9, 7), "CA-2011-100006", new Date(2012, 9, 13), "Standard Class");
inserter.AddRow(530, "EB-13705", new Date(2012, 7, 8), "CA-2011-100090", new Date(2012, 7, 12), "Standard Class");
inserter.Execute();
}
// Insert data into Customers table.
using (Inserter inserter = new Inserter(connection, customer))
{
inserter.AddRow("DK-13375", "Dennis Kane", 518, "Consumer");
inserter.AddRow("EB-13705", "Ed Braxton", 815, "Corporate");
inserter.Execute();
}
// Insert data into Product table.
using (Inserter inserter = new Inserter(connection, products))
{
inserter.AddRow("TEC-PH-10002075", "Technology", "Phones", "AT&T EL51110 DECT");
inserter.Execute();
}
// Insert data into Line Items table.
using (Inserter inserter = new Inserter(connection, lineItems))
{
inserter.AddRow(2718, "CA-2011-100006", "TEC-PH-10002075", 377.97, 3, 0.0, 109.6113);
inserter.AddRow(2719, "CA-2011-100090", "TEC-PH-10002075", 377.97, 3, null, 109.6113);
inserter.Execute();
}
foreach (var name in new[] { orders.TableName, customer.TableName, products.TableName, lineItems.TableName })
{
// ExecuteScalarQuery is for executing a query that returns exactly one row with one column
long count = connection.ExecuteScalarQuery <long>($"SELECT COUNT(*) FROM {name}");
Console.WriteLine($"Table {name} has a count of {count} rows");
}
}
Console.WriteLine("The connection to the Hyper file has been closed.");
}
Console.WriteLine("The Hyper process has been shut down.");
}
