Üşenmedim Yazdım
namespace LibOpcDB
{
using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.Data.Entity;
using LibOpcDB.Models;
using Microsoft.Data.Entity.Metadata;
public class OpcDataModel : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionStrings = System.Configuration.ConfigurationManager.ConnectionStrings;
#if DEBUG
var connectionString = connectionStrings["localsqlite"].ConnectionString;
#else
var connectionString = connectionStrings["remote"].ConnectionString;
#endif
optionsBuilder.UseSqlite(connectionString);
}
public OpcDataModel() { }
public virtual DbSet<Branch> Branches { get; set; }
public virtual DbSet<Server> Servers { get; set; }
public virtual DbSet<Leaf> Leafs { get; set; }
public virtual DbSet<LeafData> Data { get; set; }
protected override void OnModelCreating(ModelBuilder m)
{
// --------------------- RELATIONS ---------------------
// Server - Branch
m.Entity<Branch>()
.HasOne<Server>()
.WithMany(e => e.Branches)
.HasForeignKey(e => e.ServerId)
.OnDelete(DeleteBehavior.Restrict);
m.Entity<Server>()
.HasMany<Branch>(e => e.Branches)
.WithOne(e => e.Server)
.HasForeignKey(e => e.ServerId)
.OnDelete(DeleteBehavior.Restrict);
// Branch - Branch Hiearchical
m.Entity<Branch>()
.HasMany<Branch>(e => e.Children)
.WithOne(e => e.ParentBranch)
.HasForeignKey(e => e.ParentBranchId)
.OnDelete(DeleteBehavior.Restrict);
// Leaf - Branch
m.Entity<Leaf>()
.HasOne<Branch>()
.WithMany(e => e.Leafs)
.HasForeignKey(e => e.BranchId)
.OnDelete(DeleteBehavior.Restrict);
m.Entity<Branch>()
.HasMany<Leaf>(e => e.Leafs)
.WithOne(e => e.Branch)
.HasForeignKey(e => e.BranchId)
.OnDelete(DeleteBehavior.Restrict);
// LeafData - Leaf
m.Entity<LeafData>()
.HasOne<Leaf>()
.WithMany(e => e.LeafData)
.HasForeignKey(e => e.LeafId)
.OnDelete(DeleteBehavior.Restrict);
m.Entity<Leaf>()
.HasMany<LeafData>(e => e.LeafData)
.WithOne(e => e.Leaf)
.HasForeignKey(e => e.LeafId)
.OnDelete(DeleteBehavior.Restrict);
// % --------------------- END RELATIONS ---------------------
// Servers
m.Entity<Server>()
.ToTable<Server>("servers")
.HasKey(e => e.Id);
m.Entity<Server>()
.HasIndex(e => e.Name)
.IsUnique(true);
m.Entity<Server>()
.Property(e => e.Id)
.HasColumnName("id");
m.Entity<Server>()
.Property(e => e.Name)
.IsRequired()
.HasColumnName("name");
// Branches
m.Entity<Branch>()
.ToTable<Branch>("branches")
.HasKey(e => e.Id);
m.Entity<Branch>()
.HasIndex(e => e.Name)
.IsUnique(true);
m.Entity<Branch>()
.Property(e => e.Id)
.HasColumnName("id");
m.Entity<Branch>()
.Property(e => e.Name)
.HasColumnName("name")
.IsRequired();
m.Entity<Branch>()
.Property(e => e.ServerId)
.HasColumnName("server")
.IsRequired();
m.Entity<Branch>()
.Property(e => e.ParentBranchId)
.HasColumnName("parent")
.IsRequired(false);
// Leafs
m.Entity<Leaf>()
.ToTable<Leaf>("leafs")
.HasKey(e => e.Id);
m.Entity<Leaf>().HasIndex(e => e.Key)
.IsUnique(true);
m.Entity<Leaf>()
.Property(e => e.Key)
.IsRequired();
m.Entity<Leaf>()
.Property(e => e.Id)
.HasColumnName("id");
m.Entity<Leaf>()
.Property(e => e.Name)
.IsRequired()
.HasColumnName("name");
m.Entity<Leaf>()
.Property(e => e.Key)
.IsRequired()
.HasColumnName("key");
m.Entity<Leaf>()
.Property(e => e.BranchId)
.HasColumnName("branch")
.IsRequired();
// Real Time Leaf Data
m.Entity<LeafData>()
.ToTable<LeafData>("data")
.HasKey(e => e.guid);
m.Entity<LeafData>()
.Property(e => e.guid)
.HasColumnName("id")
.ValueGeneratedOnAdd();
// 2 ^ (3 byte * 8bit/byte) = 16.777.216
m.Entity<LeafData>()
.Property( e => e.Hash )
.HasColumnName("hash")
.ValueGeneratedOnAdd()
.HasDefaultValueSql("HEX(RANDOMBLOB(3))");
m.Entity<LeafData>()
.Property(e => e.LeafId)
.IsRequired()
.HasColumnName("leaf");
m.Entity<LeafData>()
.Property(e => e.Quality)
.IsRequired()
.HasColumnName("quality");
m.Entity<LeafData>()
.Property(e => e.TimeStamp)
.IsRequired()
.HasColumnName("utc")
.HasColumnType("DateTime")
.ValueGeneratedOnAdd()
.HasDefaultValueSql("CURRENT_TIMESTAMP");
m.Entity<LeafData>()
.Property(e => e.Value)
.IsRequired()
.HasColumnName("value");
}
}
}
CREATE TABLE "servers" (
"id" INTEGER NOT NULL CONSTRAINT "PK_Server" PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL
);
CREATE TABLE "branches" (
"id" INTEGER NOT NULL CONSTRAINT "PK_Branch" PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL,
"parent" INTEGER,
"server" INTEGER NOT NULL,
CONSTRAINT "FK_Branch_Branch_ParentBranchId" FOREIGN KEY ("parent") REFERENCES "branches" ("id") ON DELETE RESTRICT,
CONSTRAINT "FK_Branch_Server_ServerId" FOREIGN KEY ("server") REFERENCES "servers" ("id") ON DELETE RESTRICT
);
CREATE TABLE "leafs" (
"id" INTEGER NOT NULL CONSTRAINT "PK_Leaf" PRIMARY KEY AUTOINCREMENT,
"branch" INTEGER NOT NULL,
"key" TEXT NOT NULL,
"name" TEXT NOT NULL,
CONSTRAINT "FK_Leaf_Branch_BranchId" FOREIGN KEY ("branch") REFERENCES "branches" ("id") ON DELETE RESTRICT
);
CREATE TABLE "data" (
"id" BLOB NOT NULL CONSTRAINT "PK_LeafData" PRIMARY KEY,
"hash" TEXT DEFAULT (HEX(RANDOMBLOB(3))),
"leaf" INTEGER NOT NULL,
"quality" INTEGER NOT NULL,
"utc" DateTime NOT NULL DEFAULT (CURRENT_TIMESTAMP),
"value" REAL NOT NULL,
CONSTRAINT "FK_LeafData_Leaf_LeafId" FOREIGN KEY ("leaf") REFERENCES "leafs" ("id") ON DELETE RESTRICT
);
CREATE UNIQUE INDEX "IX_Branch_Name" ON "branches" ("name");
CREATE UNIQUE INDEX "IX_Leaf_Key" ON "leafs" ("key");
CREATE UNIQUE INDEX "IX_Server_Name" ON "servers" ("name");
/* No STAT tables available */