Using an Entity Component System with Python and SQLite3

In the last blog post, I introduced entity component systems with some C++ template code to help out with safe typecasting. In this blog post, I want to share my experiment using Python and SQLite3 to implement the database structure I read about in Adam Martins’ blog.

I have not had the opportunity to test out the performance of using SQLite3, but the Python language makes it really easy to experiment and since I use Pythonista on iOS, it can be played around with on the road, during an errand, etc. So in this post, I will focus more on creating a simple system and the appropriate SQL queries. The other program I use is called DB Browser for SQLite which is handy for building your database and doing some maintenance.

My focus is on small games right now, so SQLite3 is a good choice since I can embed it in my programs. If I need something beefier, then there are other choices like PostgreSQL or MySQL.

To initialize SQLite with Python, you can start out with the following code:

import sqlite3

conn=sqlite3.connect(":memory:")
if not conn:
    print('oh no!')
c=conn.cursor()

We can use the execute function on the resulting c object to execute SQL statements. For example:

c.execute('CREATE TABLE entities (entityID integer primary key, desc text)')

To create a table for the entities, we use the CREATE TABLE query.

CREATE TABLE entities (
    entityID  INT PRIMARY KEY,
    entityDoc TEXT)

The components table is similar:

CREATE TABLE components (
    componentID    INT PRIMARY KEY,
    componentName  TEXT
    componentDoc   TEXT
    componentTable TEXT)

And the entity component data table is:

CREATE TABLE entityComponents (
    entityID    INT,
    componentID INT,
    rowID       INT)

The example source code can be found on PasteBin at https://pastebin.com/ruTeQqYE. However, what I want to talk about is the INNER JOIN that is used to combine the entityComponents table to the actual data rows. While you should do some research on JOINS, the short story is that if you have a foreign key on the left table and a primary key on the right table, you can use the following query:

SELECT * FROM (TblA LEFT JOIN TblB ON TblA.FK=TblB.ID)

The ON keyword sets the requirements for the JOIN otherwise you get every row on the left hand side and each row is matched up with every row on the right. This is a quadratic increase in size which is something you definitely do not want. So if you wanted to do a LEFT JOIN with three tables, then you could use a query like:

SELECT * FROM (((entityComponents LEFT JOIN entities ON entities.entityID=entityComponents.entityID) LEFT JOIN components ON entityComponents.componentID=components.componentID) LEFT JOIN positions ON entityComponents.rowID=positions.rowID)

If you take a look at this query, note that that the parentheses are used to accommodate the three LEFT JOINs. These are very useful in getting these queries to work correctly.

OK, post is getting a little long, but this brings me back to my original point. I do not know the efficiency of using a database when utilizing queries like this. I have no doubt that there is a lot of usefulness here for loading and saving data. But for real-time performance, that needs some experimentation and testing across several platforms with varying compute capabilities.