Content Management System

By Zero OneUnity

LiteDB versus SQLite

Having worked on several content management systems for education and training I have experience with different ways of managing assets. The Widget Services in the Unity Asset store has Monobehaviours that allow you to create turntables for assets in the form of list handlers for sprites, audio and other data items.

One method I have seen to create content systems is with raw file and folder handling using JSON to create META descriptions for projects. Do you want to always use a reference to the asset, always make a copy of the asset that could be instanced or allow the user to choose?

I started to explore the Lite DB system and the asset for Unity. It was straight forward with examples. With memory limits on Lite DB at 1 MB (about the size of a Bible) its it rather limiting. There are options to stream and use larger files.

The SQLite methodology has a clear example of the implementation of blobs. When you store byte data and types you can then recreate the assets. You can also store JSON files to describe your content if you want to create a folder and file structure.

Both databases have their strengths and are suited for different use cases. SQLite is great for applications requiring a robust SQL database, while LiteDB is ideal for .NET applications needing a lightweight NoSQL solution.

LiteDB

  • Type: NoSQL database.
  • Language: Written in .NET C#.
  • Usage: Ideal for mobile apps, desktop applications, and web applications.
  • Features:
    • Serverless and delivered in a single small DLL.
    • Fast and lightweight.
    • Supports ACID transactions and single data file.
    • Recovery data in writing failure (WAL mode).
    • Fluent API for custom mapping and LINQ query support.
    • Free for everyone, including commercial use.
    • Supports SQL-like language for data manipulation.

SQLite

  • Type: SQL database engine.
  • Language: Written in C.
  • Usage: Widely used in mobile phones, computers, and various applications.
  • Features:
    • Small, fast, and self-contained.
    • High reliability and full featured.
    • Stable, cross-platform, and backward compatible.
    • Public domain and free to use.
    • Supports SQL syntax, pragmas, and various SQL functions.
    • Used for transferring rich content and long-term data archival

Key Differences

  • Database Type: SQLite is a SQL database, while LiteDB is a NoSQL database.
  • Language: SQLite is written in C, whereas LiteDB is written in .NET C#.
  • Usage: SQLite is more widely used and integrated into various systems, while LiteDB is tailored for .NET applications.
  • Features: SQLite focuses on being a full-featured SQL database with high reliability, while LiteDB offers a lightweight, serverless NoSQL solution with features like ACID transactions and LINQ support.

In the end I am using the SQLite solution. Creating tables, updates and selections in C# and translating the data to structures.

I found an asset. SimpleSQL from Echo17 is not a free one however when I want to implement SQLite data connectivity this is going to be my go-to asset. It was updated not too long ago. I have been using this asset successfully in my content management system.

License Agreement: Standard Unity Asset Store EULA License Type: Extension Asset File Size: 45.3 MB Latest Version: 2.16.1 Latest Release Date: March 3, 2024, Original Unity Version: 2017.4.8 or higher

Overview: SimpleSQL helps you integrate database storage into your game or project. You can create level map links, store player stats, keep inventory specs, and much more! Please note that SimpleSQL is a local database solution and cannot connect to server databases.

Features:

  • Attach SQLite databases to managers in your scene.
  • No need to move files around for mobile devices!
  • Simple and efficient access to data using customizable classes or writing your own SQL statements.
  • Store your data using an ORM structure or simple tables.
  • Optionally use the .NET System Data library.
  • Works with PC Standalone, Mac Standalone, Linux, iOS, and Android.
  • Works on both Unity and Unity Pro.
  • C# Source code included.
  • Works with Playmaker.

Notes:

  • WP8 and Windows Store, Webplayer / WebGL are not currently supported because file access is required.