CastleDB - The structured static database

CastleDB

CastleDB is a structured static database easing collaboration.

Why

CastleDB is used to input structured static data.

Everything that is usually stored in XML or JSON files is subject to be stored and modified with CastleDB instead.

For instance in a game you would like to have all of your items and monsters stored in it, including their names, description, logic effects, etc.

How

CastleDB looks like any spreadsheet editor, except that each sheet has a data model.

The model allows the editor to validate and ease user input.

For example a given column can reference another sheet row, so you will be able to select it directly.

Storage

CastleDB store both its data model and rows data into an easily readable JSON file.

It can then easily be loaded and used by any program.

For instance in a game you would like to have all of your items and monsters stored in it, including their names, description, logic effects, etc.

Collaboration

CastleDB allows efficient collaboration on data editing.

It uses the JSON format with newlines to store its data, which allows RCS such as GIT or SVN to diff/merge the data files.

Unlike online spreadsheet editors, changes are made locally, which allows local experiments before either commiting or reverting.

The Data Model

A database consists in several sheets. Each sheet is a collection of structured object (similar to a TABLE in a traditional database).

Each sheet consists in several named columns. The columns will represent the stored data object fields.

Each column has a given type which represents the kind of data that is stored in this column.

CastleDB handles column renaming, deleting, and conversion between column types.

Column Types

The following types are available for columns types:

Unique Identifier
This is an unique identifier for the current row. It will allows to reference this row from other sheets or columns. Unique identifiers must be valid code identifiers [A-Za-z_][A-Za-z0_9_]*.
Text
Any text can be input into this column. CastleDB currently does not allow multiline text.
Boolean
A checkbox will allow to specify if the column is true or false.
Integer
A integer number (which does not have fractional component).
Float
Any number.
Enumeration
An exclusive choice between a number of given custom values. For instance Yes,No,Cancel,Error
Flags
Several optional choices between a list of given custom values. For instance hasHat,hasShirt,hasShoes.
Reference
A reference to another sheet row, using its unique idenfier.
Image
An image to be displayed.
List
A list of structured values learn more.
Custom
A custom type learn more.

Column Storage and default value

The following table tells how each type is stored into the CDB/JSON file:

Column Type Storage Default Value
Unique Identifier the identifier String ""
Text the text String ""
Boolean true or false false
Integer the integer value 0
Float the number value 0
Enumeration the integer index of the selected value 0 (first value)
Flags a bit is set for each index of the selected values 0 (no selected value)
Reference the string of the target unique identifier "" (missing identifier)
Image the string of the MD5 of the image content bytes "" (missing image)
List an Array of structured objects learn more [] (empty array)
Custom a mixed array representation learn more null (missing type)

Optional Column

If you uncheck the Required checkbox when creating/modifying a column, default data will be null. It will also remove the field from the stored object in case no data is present.

Using CastleDB Editor

Loading and Saving

Using the File menu, you can create a New file and Load and Save As your .cdb file.

Everytime you make a change, the database file is automatically saved.

Keys

Here's the most used keys for CastleDB:

  • Arrows are used to navigate between the cells. You can start typing to replace the cell content or use F2 or Enter to edit it.
  • Use Esc to exit cell edition or close opened List
  • Use Insert to insert a new row at the cursor position
  • Use Ctrl+Z and Ctrl+Y to Undo/Redo your changes
  • Use Tab and Shift+Tab to navigate to next/prev cell on the same row
  • Use F4 to go the row referenced by the cell and F3 to search for references of this row"

Managing sheets

In order to manage sheets, right click on the sheet title in the bottom bar of the editor. You will be able to:

  • Add a new sheet
  • Move the sheet to reorder them
  • Rename and Delete the sheet
  • Access sheet options such as Index and Groups

Managing columns

Right click on the column title in order to access to the following options:

  • Edit the column, which allows to rename it, change its type and required flag.
  • Move the column to reorder them
  • Add a new column
  • Delete the column or set it as Display Column
  • Convert the column content if some convert functions are available for this type.

CastleDB offers automatic conversion between column types, which is used when changing the column type or copy/pasting data between columns/sheets.

If no convertion is available, you will get an error when changing the column type.

Managing rows

Right click on a row index to select the row and access the following options:

  • Move Up and Down the row, which can also be triggered by using Ctrl+Up and Ctrl+Down arrows
  • Insert a new row at the cursor position, same as Insert key
  • Delete the selected row(s), same as Delete key
  • Insert or remove a Separator for this row
  • look for the references pointing to this row (requires an unique identifier)

Selection

You can select one row by clicking on its index, then select several of them by clicking while olding shift on another row index.

You can select a range of cells by first selecting a cell then clicking while holding shift on another cell.

You can Copy/Paste using Ctrl+C, Ctrl+V and Ctrl+X between columns and rows.

More Features

Display Column

If you right click on a column and check Display Column, this column will be used instead of the unique identifier when displaying a row reference.

This can be useful for instance to display the real name of an item instead of its unique identifier.

The stored data and that data input is still made using the unique identifier, this only affects how things are displayed in CastleDB editor.

Index

When you open CastleDB, each row is referenced by an 0-based index.

Usually this index is not part of the exported data but you can add it by right-clicking on the sheet name, then check Index. It will add an index object field to your data.

For List column types, the index will be unique for the given column.

Separators

If you right click on a row index, you can check Separator which will create a small visible separator between this row and the previous one.

You can also double-click on the separator to name it.

Add Group

If you right-click on the sheet name and check the Add Group, it will create for each row a group field that will be an integer index.

The index starts at 0, then each time a new separator with a title set is reached, it will increment by one (unless the first row separator has a title).

This will allow you to categorize your sheet rows easily without having to create a specific column for it.

Advanced Types

List Column Type

When you set the type of a column as List this will create a new hidden sheet.

You can then add columns to this sub-sheet and modify it as you would for another sheet.

However the difference is that the data of this sheet is split between your original sheet rows.

You can click on the list to toggle its content and insert rows into it.

A List is similar to the one-to-many database association.

Image

The images inserted into the database are stored into a separate .img file. Only the MD5 key of the image is stored into the .cdb database.

As a consequence it means that the same image can be used multiple times without increasing overall file size.

If you do a lot of images changes, you can use the Clean Images command in the application File menu. This will remove from the .img file all images that are no longer used.

Custom Types

Custom types are a structured type declared as enums, consisting in several constructors.

Custom types can be created and modified by using the Edit Types link at the bottom-right of the editor.

Here's an example of a custom type with four constructors:

enum MyCustomType {
	Fixed;
	Random( v : Float );
	Monster( ?m : monsters );
	Or( a : MyCustomType, b : MyCustomType );
}

In that case, a column of this custom type can be one of the following values:

Fixed
Random(0.5)
Monster(MyMonsterId)
Or(Random(0.5),Fixed)

A custom type constructor parameter can use the following types:

  • Int : an integer value
  • Bool : a boolean value
  • Float : any number value
  • String : a string value
  • CustomType : any custom type, including itself
  • SheetName : any sheet name created in the database

If a constructor parameter is prefixed with a ?, then it means it's optional and can be omited.

Custom types are stored as a mixed array content. The first element of the Array is the index in the constructors list, followed by the eventual parameters for this constructor.

Value example Stored value
Fixed [0]
Random(0.5) [1,0.5]
Monster(MyMonsterId) [2,"MyMonsterId"]
Or(Random(0.5),Fixed) [3,[1,0.5],[0]]

Custom type input is strictly validated by CastleDB editor.

Haxe Integration

CastleDB was created using the Haxe technology, it also allows some Haxe-specific integration.

The powerful Haxe Macros will allow to directly generate all the types declaration from the CastleDB Data Model.

This is simply done by creating a source file such as:

// Data.hx
private typedef Init = haxe.macro.MacroType<[cdb.Module.build("myDataFile.cdb")]>;

When compiling with -lib castle, this will create all the types stored in the CDB model.

You can still initialize at runtime your CDB data, then access it using the accessors:

var content : String = .... // load CDB file content
Data.load(content);
Data.monsters.all; // all the objects of the "monsters" sheet
var dragon : Data.Monsters = Data.monsters.get(Dragon); // using the unique ID generated enum
trace(d.loot.name); // automatically fetch the Refenced objects

All the accesses are strictly typed by Haxe.

Generated types

The main type of the module is generated with the load static field and for each declared sheet a static field corresponding to the sheet name.

If the sheet does not have an unique identifier, only the all field is available.

If the sheet has an unique identifier, you can access the all field, but also use get (by id) and resolve (by string).

For each sheet named mySheet we will generate the following types:

  • MySheetDef is the original object as parsed by JSON.
  • MySheet is an abstract type that allows only reading the fields and perform some conversions (see below)
  • MySheetKind is an enum containing all the unique identifiers found in mySheet

The MySheetKind type is an abstract value so the actual runtime value is still the identifier string, however it allows for strictly typing the identifiers. You can still use .toString() to access the original string.

Column type mapping

The different column types are mapped to the following types:

Unique Identifier
SheetNameKind : an abstract enum is created for each sheet
Text
String
Boolean
Bool
Integer
Int
Float
Float
Enumeration
SheetName_ColumName : an abstract enum is created. If they have the same values in the same order, they are all aliases to the same enum.
Flags
cdb.Module.Flags<SheetName_ColumnName> : an abstract enum is created as for Enumeration. The bits are wrapped using the Flags abstract which allows typed has and iterator methods.
Reference
TargetSheetName : referenced object is fetched when accessed. You can access only the identifier by using columnNameId field.
Image
String : the MD5 key only, no image loading is supported atm.
List
cdb.Module.ArrayRead<SheetName_ColumnName> : a read-only array of the structured objects, allows indexed access, length and iteration.
Custom
SheetName_ColumnName : values are converted on-the-fly to the enum created as it's been declared.

Example

You can see a small example of Haxe integration on GitHub here.

If you want to see what code is being actually generated, you can compile with -D dump=pretty and look at the dump directory after successful compilation.

Warning : castle library require either Haxe 3.2+ or a recent Haxe build to work. You can download it from this page

About

CastleDB was created by @ncannasse using Haxe and Node Webkit. It's hosted on GitHub and used professionally by Shiro Games.

CastleDB is an open source software usable in commercial projects without any restriction.

If you wish to help CastleDB development, you can