Elenesski Object Database  EODB and NEODB
The Memento Pattern Database for Unity
33 Network - Database Creation

The Database

You need at least a mySQL 5.5 or later database to run NEODB. Creation of the core database is done with a script provided in the distribution, but looks like this: 

CREATE TABLE NEODB_NEW_OID (
    OID int NOT null,
    PRIMARY KEY(OID)
);
INSERT INTO NEODB_NEW_OID values ( 0 );

CREATE TABLE NEODB_OBJECT_DATA (
    OID int NOT null,
    LOCK_ID VARCHAR(25) not null,
    CLASSNAME VARCHAR(50) not null,
    DATA TEXT not null,
    UPDATE_DTTM INT NOT NULL,
    PRIMARY KEY(OID)
);

CREATE INDEX NEODB_OBJECT_DATA_IX1
ON NEODB_OBJECT_DATA ( LOCK_ID );

CREATE INDEX NEODB_OBJECT_DATA_IX2 
ON NEODB_OBJECT_DATA ( CLASSNAME );

CREATE INDEX NEODB_OBJECT_DATA_IX3
ON NEODB_OBJECT_DATA ( UPDATE_DTTM );

This SQL DDL (Data Definition Language) script creates two tables:

  • NEODB_NEW_OID - Is a table with a single row that represents the last OID assigned. It's possible to request more than one OID at a time, so that you don't need to make multiple requests.
  • NEODB_OBJECT_DATA - Is the table that contains your object data.
    • OID - Is the unique identifier for the data. OIDs less than zero, by convention, are considered to be static or hard coded values. OIDs greater than zero are considered to be dynamic data where the OID isn't known until it is created. You need an index or a class name search to find dynamic data.
    • LOCK_ID - Is a password like feature that is added to an object to prevent unauthorized updating. Lock IDs to not prevent reading of data.
    • CLASSNAME - Name of the class. It's used when indexes pull back more than one class at a time and you need a factory to differentiate them.
    • DATA - This is the data of your object. Data in the app is binary, but in mySQL it is stored as a base-64 value in order to simplify data transport and storage.
      • DATA is defined as TEXT which has a limit of 64K.
      • While we don't recommend this, you can change the type of data to MEDIUMTEXT which supports up to 16Mb. This is the maximum possible size because Unity's POST cannot send messages larger than 16Mb.
    • UPDATE_DTTM - Is the last time the data was created or updated. The value is the number of seconds since January 1, 1970 at midnight.

Object Data

Inside EODB, there is no limit as to how large you can make your objects. With NEODB objects should be limited to about 49K of binary data. EODB object data is transformed into base-64 for transport and storage, and this is why the limit is 49K. 49K -> via Base64 -> 64K.

You should carefully decide if you are going to use MEDIUMTEXT instead of TEXT to store your data. The decision to use up to 16Mb should be carefully considered because data transfer and latency becomes and issue when moving large amounts of data around. Furthermore, lots of large data moves can quickly eat up your ISP's database and server limits.

This is why we recommend small data over larger data; even though larger data is supported.

Indexes

You specify which permanent indexes your database will contain in "neodb_settings.php". You can create, purge and delete dynamic indexes within program logic when you want to isolate data that is for a specific part of your application. Once created they function nearly identically to permanent indexes. See the Usage Guide and Dynamic Indexes for more information.

When creating new permanent indexes you will need to manually create your indexes using the "setup_indexes.php" script. This will read the indexes and create the proper tables. One table per index. Once the indexes are created, it's not necessary to keep "setup_indexes.php" script on the server unless you might need to add new indexes in the future. If you do add indexes, simply return this PHP script. New index tables will be added without affecting previously created indexes.

Specifying the index in "neodb_setting.php" is done using a PHP multi-dimensional array, and looks like this: 

$NEODB_INDEXES = array(
    array ("DISPLAY","s","VARCHAR(50)"),
    array ("LEADER","i","INT")
);

This creates two indexes, one called "DISPLAY" and the other called "LEADER". The format for the inner array is as follows:

  • Field 1 is a index's name. Index names are case insensitive, they should be in uppercase to prevent confusion with this rule.
  • Field 2 is the PHP binding type. There are 3 possible values:
    • "i" for integer
    • "d" for double
    • "s" for string
  • Field 3 is the type of data that is stored in the index. To learn about what data types are possible with mySQL; see http://dev.mysql.com/doc/refman/5.7/en/data-types.html. (Note: This link is for a mySQL 5.7 database; you will need to use a different link if your database version is different.)

In this example, two indexes are created:

  • DISPLAY is bound with strings and is a varchar(50) field.
  • LEADER is bound with integers and is a INT field.

Warning, it is advised that if you have big strings you should keep the number of items in your indexes small or if you have big indexes keep your strings small. Big strings with big indexes use a lot of I/O and will slow your searches down and may impact your network latency when finding a lot of rows.

  • An index is considered big if it has more than 2000 items in it.
  • A string is considered big if is larger than 255 characters.

You can have any number of indexes, but try to limit the number of indexes you update with your data small (2 or less). Each SAVE requires 2 logical IOs, one to test to see if the data exists, one to write out the changes. INDEXES require 2 logical IOs and having a lot of them slows your saves down dramatically.

Once an index is defined, you can write to it using C#: 

aDescriptor.Index("DISPLAY",LastName + ", " + FirstName );

Running an aDescriptor.Index on an Index that does not exist will generate a failure. Similarly, if you reference an index that doesn't exist when doing a search, the system will generate a failure.

Dynamic Indexes

Dynamic Indexes are index that are created by the runtime environment and are always string indexes. While they were created to support specific game objects, they can be used exactly like a regular index for any purpose what-so-ever. The only difference is that index names must begin with "DYN_", so to create a dynamic index, you say: 

_NEODB.ServerCreateDynamicIndex("DYN_INDEXNAME",CreationSuccess,ReportFailure);

Once you have the index name, you can use any of the index search methods, such as: 

_NEODB.ServerFindEquals("DYN_INDEXNAME",NEODBSort.Ascending, "BOB", LoadSuccess, ReportFailure );

Dynamic Indexes have two additional methods (1) Delete the Dynamic Index _NEODB.ServerDeleteDynamicIndex("DYN_INDEXNAME",DeleteSuccess,ReportFailure); (2) Purge all the rows out of NEODB that are found in the index _NEODB.ServerPurgeDynamicIndex("DYN_INDEXNAME",PurgeSuccess,ReportFailure);