Skip to content

mogoson/MGS.Sqlite

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

21 Commits
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

[TOC]

MGS.Sqlite

Summary

  • Generic sqlite data view and table for custom data class.

Environment

  • Unity 5.0 or above.
  • .Net Framework 3.5 or above.

Platform

  • Windows

Demand

  • Create data base file, view table runtime.
  • Select rows from view as DataTable and parse to custom data structure.
  • Select, Insert, Update, Delete table rows, use custom data structure.

Usage

Native

  • Relate to sqlite file.

    //Relate to data base file, this operate will create the file if it does not exists.
    var dbFile = string.Format("{0}/TestDB.db", Environment.CurrentDirectory);
    var dataBase = new GenericDataBase(dbFile);
  • Operate sqlite table.

    //Create table if not exists, this operate will create data base file if not exists.
    var statement = "table_t0(id INT32 PRIMARY KEY UNIQUE NOT NULL,name STRING NOT NULL)"
    var lines = dataBase.CreateTable(statement);
    
    //Select table form data base.
    var table = dataBase.SelectTable("table_t0");
    
    //Select rows form table.
    var dataTable = table.Select();//Select * from table.
    //var dataTable = table.Select(cmd);//Use custom cmd to select.
    
    //Insert new row.
    dataTable.Rows.Add(0, "name");//Add new row.
    var lines = table.Update(dataTable);//Commit to sqlite table.
    
    //Update row.
    var row_0 = dataTable.Rows[0];
    row_0.BeginEdit();
    row_0["name"] = "test update name";
    row_0.EndEdit();
    var lines = table.Update(dataTable);//Commit to sqlite table.
    
    //Delete row.
    dataTable.Rows[0].Delete();
    var lines = table.Update(dataTable);//Commit to sqlite table.
    
    //Delete table.
    var lines = dataBase.DeleteTable("table_t0");
  • Operate sqlite view.

    //Create view if not exists.
    var lines = dataBase.CreateView("view_v0 as select name from table_t0");
    
    //Select rows form view.
    var view = dataBase.SelectView("view_v0");
    var dataTable = view.Select();
    
    //Delete view.
    var lines = dataBase.DeleteView("view_v0");
  • Operate sqlite trigger.

    //Create sqlite trigger if not exists.
    var statement = "trigger_t0 AFTER INSERT ON table_t0 BEGIN INSERT INTO table_t1(id, salary)VALUES(new.ID, 0); END";
    var lines = dataBase.CreateTrigger(statement);
    
    //Create sqlite trigger if not exists.
    var name = "trigger_t0";
    var when = SqliteConst.AFTER;
    var action = SqliteConst.INSERT;
    var table = "table_t0";
    string scope = null;//SqliteConst.FOR_EACH_ROW
    string where = null;//id>=0
    var code = "INSERT INTO table_t1(id, salary)VALUES(new.ID, 0)";
    var lines = dataBase.CreateTrigger(name, when, action, table, scope, where, code);
    
    //Delete the trigger from data base.
    var lines = dataBase.DeleteTrigger("trigger_t0");

Expand

  • Custom TableRow.

    //Implement the ITableRow interface, the simple way is to inherit from TableRow.
    public class PersonT : TableRow
    {
        //Must mark a PrimaryKey.
        [ColumnField(PrimaryKey = true)]
        public int id;
    
        [ColumnField]
        public string name;
    }
    
    //TableRow is Implemented base on reflection,
    //so a better way is implement the ITableRow interface by yourself.
    public class PersonT : ITableRow
    {
        public int id;
        public string name;
    
        public string Statement
        {
            get { return "(id INT32 PRIMARY KEY UNIQUE NOT NULL,name STRING NOT NULL)"; }
        }
    
        public string PrimaryKey
        {
            get { return "id"; }
        }
    
        public object PrimaryValue
        {
            get { return id; }
        }
    
        public void FillFrom(DataRow row)
        {
            id = (int)row["id"];
            name = row["name"].ToString();
        }
    
        public void FillTo(DataRow row)
        {
            row["id"] = id;
            row["name"] = name;
        }
    }
  • Custom ViewRow.

    //Implement the IViewRow interface, the simple way is to inherit from ViewRow.
    public class PersonV : ViewRow
    {
        //Do not need mark PrimaryKey.
        [ColumnField]
        public int id;
    
        [ColumnField]
        public string name;
    }
    
    //ViewRow is Implemented base on reflection,
    //so a better way is implement the IViewRow interface by yourself.
    public class PersonV : IViewRow
    {
        public int id;
        public string name;
        
        public void FillFrom(DataRow row)
        {
            id = (int)row["id"];
            name = row["name"].ToString();
        }
    }
  • Use GenericDataBase for custom TableRow.

    //Create table if not exists.
    var lines = dataBase.CreateTable<PersonT>("table_person");
    
    //Select table from data base.
    var table = dataBase.SelectTable<PersonT>("table_person");
    
    //Insert new row.
    table.Insert(new PersonT() { id = 0, name = "name" });
    var lines = table.Commit();//Commit to sqlite table.
    
    //Select rows from table as custom structure.
    var persons = table.Select();
    var enumer = persons.GetEnumerator();
    enumer.MoveNext();
    var person_0 = enumer.Current;
    
    //person_0.id = 100;//person_0.id is primary key, modify it's value not supported.
    person_0.name = "Test update name";
    table.Update(person_0);
    var lines = table.Commit();//Commit to sqlite table.
    
    //Delete row from table.
    table.Delete(person_0);
    //table.Delete(person_0.id);//Use the value if primary key to delete row is ok.
    var lines = table.Commit();//Commit to sqlite table.
  • Use GenericDataBase for custom ViewRow.

    //Create view if not exists.
    var lines = dataBase.CreateView("view_person as select * from table_person");
    
    //Select view from data base.
    var view = dataBase.SelectView<PersonT>("view_person");
    
    //Select rows from view as custom structure.
    var persons = view.Select();

Copyright © 2021-2023 Mogoson. [email protected]