BansheeGz

Description

There is a downloadable plugin, which adds support for data exchange between BGDatabase and SQLite in Unity Editor. See the list of available operations below.

Available operations

Name	Description
Check structure	Checks if SQLite database has required structure for data. The following requirements apply Each BGDatabase table should have a corresponding SQLite table with a Guid compatible primary key (ID column). ID column is used to identify matching rows and for relations values Each database field should have a corresponding SQLite column with compatible type
Sync structure	Creates missing SQLite tables and fields for data exchange using BGDatabase structure as a source. This function only creates missing tables/fields, it does not change or delete existing columns. For example, if the required column exists, but does not have compatible type, exception will be thrown, and it should be fixed manually
Export	Export data from BGDatabase to SQLite
Import	Import data from SQLite to BGDatabase

Restrictions

Tables should have ID column with PRIMARY KEY constraint and Guid-compatible type (TEXT or BLOB)
Export/import is slightly different compared to Excel or Google Sheets. A SQLite database has value constraints that can prevent SQL statements from executing.

ID column

The ID column, with a PRIMARY KEY constraint, is used to keep the link between a BGDatabase row and a SQLite row. It is also used by relation fields to reference rows. The ID column is created when the table is created. It can have the following types (choose the one and stick to it):

TEXT: Default Guid text format (36 characters, more characters, faster serialization)
TEXT: Default BGDatabase text format (22 characters, less characters, slower serialization)
BLOB: 16 bytes long binary array (not human-readable)

The types are all based on Guid values and can be converted from one to another, but ID column is created during table creation and can not be changed later automatically. Choose one type and stick with it.

Supported fields

There are several fields that have built‑in support. Other fields can be added with custom C# code (read below)

BGDatabase type	SQLite type
bool, byte, short, int, long, bool?, int?, long?	INTEGER
float, double, float?, double?	REAL
string, text	TEXT
relationSingle	TEXT or BLOB
relationMultiple	additional table(#1) is used

relationMultiple field requires an additional, junction table to store the values. The name of the table is {TableName}_{RelationName}(without braces). It has two columns: FROM_REF, which references a relation-owner entity, and TO_REF, which references a second row.

Custom field processors

You can customize the export/import process to some extent by using custom field processors. Here is the 2 options, you could use, to add support for Vector2 field

Option #1:

Store the value in a single BLOB field as a 8 bytes long byte array

Custom Vector2 field processor code

using System;
using System.Buffers.Binary;
using System.Collections.Generic;
using UnityEngine;

namespace BansheeGz.BGDatabase.Editor
{
    public class SQLiteVector2Processor : BGSQLiteFieldProcessor
    {
        public override bool IsApplicableTo(BGSQLiteFPGetProcessorRequest request) => request.Field is BGFieldVector2Contract;

        public override List%lt;BGSQLiteMappingColumn%gt; GetColumns(BGSQLiteFPGetColumnsRequest request) => new() { new Vector2Column(request.Field) { NotNull = true, DefaultValue = "X'0000000000000000'" } };

        private class Vector2Column : BGSQLiteMappingColumn
        {
            public Vector2Column(BGField field) : base(field, "BLOB")
            {
            }

            public override object GetValue(BGField field, BGEntity entity)
            {
                //read BGDatabase value
                var vector2 = ((BGFieldVector2Contract)field)[entity];
                //convert Vector2 to byte array
                Span%lt;byte%gt; span = stackalloc byte[8];
                BinaryPrimitives.WriteInt32LittleEndian(span[..4], BitConverter.SingleToInt32Bits(vector2.x));
                BinaryPrimitives.WriteInt32LittleEndian(span.Slice(4, 4), BitConverter.SingleToInt32Bits(vector2.y));
                return span.ToArray();
            }

            public override void SetValue(BGField field, BGEntity entity, object value)
            {
                // convert byte array to Vector2
                Span%lt;byte%gt; span = (byte[])value;
                if (span.Length != 8) throw new ArgumentException("The span must be 8 bytes long.", nameof(span));
                var x = BinaryPrimitives.ReadInt32LittleEndian(span[..4]);
                var y = BinaryPrimitives.ReadInt32LittleEndian(span.Slice(4, 4));
                //write to BGDatabase
                field.SetValue(entity, new Vector2(BitConverter.Int32BitsToSingle(x), BitConverter.Int32BitsToSingle(y)));
            }
        }
    }
}

Option #2:

Store the value in two REAL fields ({FieldName}_X and {FieldName}_Y)

Custom Vector2 field processor code

using System;
using System.Collections.Generic;
using UnityEngine;

namespace BansheeGz.BGDatabase.Editor
{
    public class SQLiteVector2Processor : BGSQLiteFieldProcessor
    {
        public override bool IsApplicableTo(BGSQLiteFPGetProcessorRequest request) => request.Field is BGFieldVector2Contract;

        public override List%lt;BGSQLiteMappingColumn%gt; GetColumns(BGSQLiteFPGetColumnsRequest request) => new()
        {
            new Vector2Column(request.Field, 'X') { NotNull = true, DefaultValue = "0" },
            new Vector2Column(request.Field, 'Y') { NotNull = true, DefaultValue = "0" },
        };

        private class Vector2Column : BGSQLiteMappingColumn
        {
            private readonly char property;

            public Vector2Column(BGField field, char property) : base(field, "REAL")
            {
                this.property = property;
                Name = field.Name + "_" + property;
            }

            public override object GetValue(BGField field, BGEntity entity)
            {
                //read BGDatabase value
                var vector2 = ((BGFieldVector2Contract)field)[entity];
                //return property value
                return property switch
                {
                    'X' => vector2.x,
                    'Y' => vector2.y,
                    _ => throw new ArgumentOutOfRangeException(nameof(property))
                };
            }

            public override void SetValue(BGField field, BGEntity entity, object value)
            {
                //cast to required types
                var castedValue = (float)(double)value;
                var castedField = (BGFieldVector2Contract)field;
                var currentValue = castedField[entity];

                // set value
                castedField[entity] = property switch
                {
                    'X' => new Vector2(castedValue, currentValue.y),
                    'Y' => new Vector2(currentValue.x, castedValue),
                    _ => throw new ArgumentOutOfRangeException(nameof(property))
                };
            }
        }
    }
}

Data source parameters

These parameters apply to all the jobs that reference this data source.

Parameter	Description
Table name prefix	A prefix to prepend to the table name. For example, if prefix = "B_" and the table name is "Test", the final name becomes "B_Test". This is useful when you have other tables and want to make the tables from BGDatabase distinguishable.
ID format	The format for ID column and foreign key columns. It should be chosen beforehand because it can not be changed later (at least automatically)
Update schema on export	If set to true, "Sync structure" operation is executed before exporting data
Settings	You can include and exclude tables and fields using the Settings tab.