Database
I went for a quite simple design but that should easily be able to extend as I make the game more complete. The Players table store the username and the hashed password of the user as well as the session_id that is uses to authenticate the user. The Villages table is the core concept of the game, the players can have multiple villages (only one at the time of writing this) upgrade them, build units in them and protect their gold. In each village it is possible to construct a few Buildings as long as the gold and the space_capacity allows it. The building help the player become more powerful, for example a Gold mine is useful to generate gold with a polling method on the server side.
Every Village has a garnison army to defend the village all the time. The player can split this army into multiple other armies in order to attack other villages around him. When an attack launched a Battle therefore an Event as well is created. The battle is then set to happen in a certain ammount of time. The way is is handled is quite similar to the way I handle gold production.
Not all the values necessary to make the game work are stored on the database, for example the gold it takes to upgarde a building, the power of each units, etc. Those values are stored in the server code to avoid having to fetch them all the time even though they never change.
SQLite Zig wrapper
The library overwall was quite nice to use because of all the benefits I cited in the Introduction section of this project. But it still had a little drawback being the lack of support and clear documentations. I often found myself stuck with errors that are not very detailled.
One of the main issues I had was with chaining dynamic requests.
The following code is not working and returning an SQLiteRangeError error. This is because I tried to execute 2 queries inside one. Note that it is important here that the 2 queries are executed one after the other because of the last_insert_rowid() function that could not return the wanted id if something else has been done on the database at the same time.
pub fn createBuilding(db: *sqlite.Db, comptime BuildingType: type, building: *BuildingType) !void {
// 1. Create the building "parent"
switch (BuildingType) {
Building.GoldMine => |_| {
const query =
\\ INSERT INTO buildings(level,space_taken) VALUES(1,0);
\\ INSERT INTO gold_mines(building_id,productivity) VALUES(last_insert_rowid(),?)
;
const gm: *GoldMine = @ptrCast(building);
var stmt = try db.prepare(query);
defer stmt.deinit();
try stmt.exec(.{}, .{ .productivity = gm.productivity });
},
else => return error.UnkownBuildingType,
}
}This can be fixed in two different ways, the first is to not use the library ? wildcard but instead make my whole query without using those and then calling execMulti, note that execMulti can not take any ? parameters.
pub fn createBuilding(db: *sqlite.Db, comptime BuildingType: type, building: *BuildingType) !void {
// 1. Create the building "parent"
var arena = std.heap.ArenaAllocator.init(std.heap.page_allocator);
defer arena.deinit();
const allocator = arena.allocator();
// 2. Create the building "child"
switch (BuildingType) {
Building.GoldMine => |_| {
const gm: *GoldMine = @ptrCast(building);
const query = try std.fmt.allocPrint(allocator, "INSERT INTO buildings(level,space_taken) VALUES(1,0);INSERT INTO gold_mines(building_id,productivity) VALUES(last_insert_rowid(),{d})", .{gm.productivity});
try db.execMulti(query, .{});
},
else => return error.UnkownBuildingType,
}
}The second and in my opinion best way to do it in order to get both the advantages of executing requests together and also having the possibility to use ? parameter is to use a savepoint. The example below is not from the same code but it still illustrates the point well.
pub fn createAttackingArmy(self: *Village, db: *sqlite.Db, allocator: std.mem.Allocator, attackInfos: Troops) !*Army {
// Verify that he has enough units in his village
const source_village_army = try self.getArmy(db, allocator);
if (source_village_army.nb_ranged < attackInfos.nb_ranged or source_village_army.nb_infantry < attackInfos.nb_infantry or source_village_army.nb_cavalry < attackInfos.nb_cavalry) {
return error.NotEnoughUnitsInTheVillage;
}
var c1 = try db.savepoint("c1");
// Else we remove the units from the village and create the attacking army
try c1.db.execDynamic("UPDATE armies SET nb_ranged = nb_ranged - ?,nb_cavalry = nb_cavalry - ?,nb_infantry = nb_infantry - ? WHERE id = ?;", .{}, .{ attackInfos.nb_ranged, attackInfos.nb_cavalry, attackInfos.nb_infantry, self.army_id });
// And create new army
try c1.db.execDynamic("INSERT INTO armies (nb_ranged, nb_cavalry, nb_infantry, player_id) VALUES (?, ?, ?, ?);", .{}, .{ attackInfos.nb_ranged, attackInfos.nb_cavalry, attackInfos.nb_infantry, self.player_id });
c1.commit();
const created_army: *Army = try allocator.create(Army);
created_army.* = .{
.id = @intCast(c1.db.getLastInsertRowID()),
.nb_ranged = attackInfos.nb_ranged,
.nb_cavalry = attackInfos.nb_cavalry,
.nb_infantry = attackInfos.nb_infantry,
.player_id = self.player_id,
};
return created_army;
}