SQLiteORM Documentation

Introduction

#1 sqlite library in Unity

Mingo Sqlite is an plugin for Unity, it's very easy to use to working with database.

Features

Object Relational Mapping
Save, Query, Update and Delete in one line code
Simple transaction
Perfect migration
No reflect with code generation, fastest performance

Example code

ORM entity definition

[Entity]
class User : DatabaseEntity {

    [Id]
    [AutoIncrement]
    public long Id;

    [Index(Unique=true)]
    public string Username;

    public string Password;

    public int Age;

    [Index]
    public int Gender;

}

Save an user

var session = SqliteManager.shared.Session;
var userDao = session.CreateDao<User>();

// save
var user = new User();
user.Username = "mingo";
user.Password = "123456";
user.Age = 24;
user.Gender = 1;
userDao.Save(user);

Here we see an example of use this library to save an entity to the database.

Next step, let's see how to setup this in your project.

Getting Started

This article let you know how to setup this plugin step by step in your project.

1. Import this plugin in Unity

Structure:

Mingo              required
  APT              required
  SQLite           required

MingoExamples      optional
  SQLite           optional

Plugins            required
  Editor           required        
  SQLite
    android        required in android
    x64            required in x64 platform like iPhone/iPad
    x86            required in standalone
  *.dll            required

The folder MingoExample is optional, anyway you can delete it after imported.

2. Setup configuration

Here for convience, we create a simple subclass of MonoBehavior.

[SqliteConfig(generateCodePath="MingoExamples/SQLite/Generated")]
public class SetupSqlite : MonoBehaviour
{
    void Start() {
    }
}

This class is marked with an attributeSqliteConfig to setup configuration.

generateCodePath: specify where the files generate in

Mingo Sqlite use APT(Attribute Preprocessing Technology) to generate code instead of reflection in runtime.

3. Define first entity

[Entity]
class User : DatabaseEntity {

    [Id]
    [AutoIncrement]
    public long Id;

    [Index(Unique=true)]
    public string Username;

    public string Password;

    public int Gender;

}

We create a simple entity with members:

Id: primary key with auto increment (by Id and AutoIncrement attribute)
Username: a text type column with unique index
Password: normal text column
Gender: integer column

You may notice that class User is marked with Entity attribute, this is required.

4. Generate code

Click menu Mingo - APT - Generate code in Unity, this step will generate some code in folder where you configure at step 2. In this example, the following will generated:

UserMapping.cs
SqliteManager.cs
Resources/Sql/v1.txt

You can ignore what have generated in this step, this is automatically, we will explain later.

5. Init and open a database

[SqliteConfig(generateCodePath="MingoExamples/SQLite/Generated")]
public class SetupSqlite : MonoBehaviour
{
    void Start() {

        // declare a database name
        var databaseName = "my_first_database";

        // init
        SqliteManager.shared.Init(this);

        // open a database with file path,
        // this step will create a database file named my_first_database.db in persistent data path,
        // and create tables, then connect to this database.
        SqliteManager.shared.Open(string.Format("{0}/{1}.db", Application.persistentDataPath, databaseName));
    }
}

6. Save an user

[SqliteConfig(generateCodePath="MingoExamples/SQLite/Generated")]
public class SetupSqlite : MonoBehaviour
{
    void Start() {
        var databaseName = "my_first_database";
        SqliteManager.shared.Init(this);
        SqliteManager.shared.Open(string.Format("{0}/{1}.db", Application.persistentDataPath, databaseName));

        // create a dao for User entity
        var session = SqliteManager.shared.Session;
        var userDao = session.CreateDao<User>();

        // create user entity and save it
        var user = new User();
        user.Username = "mingo";
        user.Password = "123456";
        user.Age = 24;
        user.Gender = 1;
        userDao.Save(user);
    }
}

See how to:

Query data
Update an entity
Delete data

Query

This article introduce how to load or query data from database.

For example, we define an entity User:

[Entity]
class User : DatabaseEntity {

    [Id]
    [AutoIncrement]
    public long Id;

    [Index(Unique=true)]
    public string Username;

    public string Password;

    public int Age;

    [Index]
    public int Gender;

}

And database contains some data of users:

Id	Username	Password	Age	Gender
1	mingoA	********	18	1
2	mingoB	********	14	1
3	mingoC	********	24	2
...	...	...	..	...
26	mingoZ	********	30	1

26 rows

Load

Load an entity by primary key.

var userDao = session.CreateDao<User>();
var user = userDao.Load(1);
print(user.Username); // mingoA
print(user.Arge);     // 18

user = userDao.Load(3);
print(user.UserName); // mingoC
print(user.Arge);     // 24

LoadOne

Load an entity with anyone field.

var userDao = session.CreateDao<User>();
var user = userDao.LoadOne("Username", "mingoA");
print(user.Username); // mingoA
print(user.Arge);     // 18

user = userDao.LoadOne("Age", 24);
print(user.UserName); // mingoC
print(user.Arge);     // 24

QueryPage

Load an entity with anyone field.

var userDao = session.CreateDao<User>();
var userList = userDao.QueryPage(0, 5);
print(userList.Count); // 5

With a simple condition.

var userDao = session.CreateDao<User>();
var userList = userDao.QueryPage(0, 5, "Age", 24); // query 5 rows that Age is equals 24 in table User.
print(userList.Count); // maybe 1 or more, it's upon data of database.

Save, Update and Delete

This article introduce how to save and update data.

For example, we define an entity User:

[Entity]
class User : DatabaseEntity {

    [Id]
    [AutoIncrement]
    public long Id;

    [Index(Unique=true)]
    public string Username;

    public string Password;

    public int Age;

    [Index]
    public int Gender;

}

Save

var user = new User();
user.Username = "mingo";
user.Password = "*****";
user.Age = 18;
user.Gender = Gender.Man; // 1 is man for example

var userDao = session.CreateDao<User>();
userDao.Save(user);

Update

Update after load.

var userDao = session.CreateDao<User>();
var user = userDao.LoadOne("Username", "mingo");
user.Password = "*****";
user.Age = 30;
userDao.Save(user);

Update specify field with primary key.

var userDao = session.CreateDao<User>();
userDao.Update(1, "Age", 24); // update User which id is 1 and set it's Age to 24.

Delete

Delete by entity.

var userDao = session.CreateDao<User>();
var user = userDao.LoadOne("Username", "mingo");
userDao.Delete(user);

Delete by primary key.

var userDao = session.CreateDao<User>();
userDao.Delete(1);

Attributes

This article introduce usage of all attributes.

Entity

Entity attribute mark at a class represented a table of database.

// simple usage
[Entity]
class User : DatabaseEntity { ... }

// custom table name
[Entity(TableName="my_user_table")]
class User : DatabaseEntity { ... }

Id and AutoIncrement

A member marked with Id means it's a primary key in database, so AutoIncrement means the value this member will be generated by database with auto increment value.

Note: the value type of the member marked with AutoIncrement must be a number type such as int long

[Entity]
class User : DatabaseEntity {

    [Id]
    [AutoIncrement]
    public long id;
}

Ignore

A member marked with Ignore means it's not a column of database, this member will be ignored when creating table.

[Entity]
class User : DatabaseEntity {

    [Ingore]
    public bool isLogin; // a temporary state
}

Column

Column is an optional attribute for a member, it could be used when you want to custom the column name of a member.

[Entity]
class User : DatabaseEntity {

    [Column("username")] // custom column name for account
    public string account;
}

Index

A member marked with Index means this column will be created with an index of database.

[Entity]
class User : DatabaseEntity {

    [Index(Unique=true)]
    public string username;
}

With previous code, it's an unique index with name "IDX_User_username" will be created. SQL like:

CREATE UNIQUE INDEX IF NOT EXISTS IDX_User_username in User ("username" ASC);

Version

Version is use to working with database migration.

Configurations

SqliteConfig

[SqliteConfig(
    generateCodePath = "...",            // where the code should generate in.
    useBuiltinMigration = true/false,    // whether use builtin migration, true is recommended.
)]

generateCodePath

where the code should generate in, this path must not contains any Editor folder.

useBuiltinMigration

whether use builtin migration, true is recommended, set to true everything will be automatically, migration does with Version attribute, it's very very simple way.

Support

Email: zzmingo88@gmail.com
Twitter: @MingoZ88

SQLiteORM Documentation

Introduction

Features​

Example code​

Getting Started

1. Import this plugin in Unity​

2. Setup configuration​

3. Define first entity​

4. Generate code​

5. Init and open a database​

6. Save an user​

More​

Query

Load​

LoadOne​

QueryPage​

Save, Update and Delete

Save​

Update​

Delete​