301 Days (redux)

Day 26 - Live Cells

by Joe Threeohone

We need to keep a closer eye on the map cells.

Keeping tabs on those cells

But why? Reading in a map file is good enough, right? No, because the map changes. Doors are opened and closed, trees are burnt down, walls are blown up, etc. But by now we should certainly know how to keep track of stuff.

Server side

As usual, we shove things into the database as they change, queueing them up to do one big update per round…

DragonsSpine/SQL Scripts/LiveCell.sqllink
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

CREATE TABLE [dbo].[LiveCell](
    [lastRoundChanged] [int] NOT NULL,
    [facet] [smallint] NOT NULL,
    [land] [smallint] NOT NULL,
    [map] [smallint] NOT NULL,
    [xCord] [smallint] NOT NULL,
    [yCord] [smallint] NOT NULL,
    [zCord] [int] NOT NULL,
    [cellGraphic] [char](2) NOT NULL,
    [displayGraphic] [char](2) NOT NULL
 CONSTRAINT [PK_LiveCell] PRIMARY KEY CLUSTERED 
(
    [facet] ASC,
    [land] ASC,
    [map] ASC,
    [xCord] ASC,
    [yCord] ASC,
    [zCord] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
DragonsSpine/DAL/DBWorld.cslink
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185

internal static int SaveLiveCell(Cell cell) {
    int result = 0;
    lock (lockObjectLiveCellUpdate) {
        if (DragonsSpineMain.GameRound != DAL.DBWorld.roundToUpdate) {
            try {
                String sptouse = "prApp_LiveCell_Update";
                using (SqlConnection tempConnection = DataAccess.GetSQLConnection()) {
                    Utils.Log("Round " + DragonsSpineMain.GameRound + ": Inserting " + DAL.DBWorld.cellsToUpdate.Count + " live cells.", Utils.LogType.Unknown);
                    foreach (Cell currCell in DAL.DBWorld.cellsToUpdate) {
                        using (SqlStoredProcedure sp = new SqlStoredProcedure(sptouse, tempConnection)) {
                            sp.AddParameter("@lastRoundChanged", SqlDbType.Int, 4, ParameterDirection.Input, DAL.DBWorld.roundToUpdate);
                            sp.AddParameter("@facet", SqlDbType.Int, 4, ParameterDirection.Input, currCell.FacetID);
                            sp.AddParameter("@land", SqlDbType.Int, 4, ParameterDirection.Input, currCell.LandID);
                            sp.AddParameter("@map", SqlDbType.Int, 4, ParameterDirection.Input, currCell.MapID);
                            sp.AddParameter("@xCord", SqlDbType.Int, 4, ParameterDirection.Input, currCell.X);
                            sp.AddParameter("@yCord", SqlDbType.Int, 4, ParameterDirection.Input, currCell.Y);
                            sp.AddParameter("@zCord", SqlDbType.Int, 4, ParameterDirection.Input, currCell.Z);
                            sp.AddParameter("@cellGraphic", SqlDbType.Char, 2, ParameterDirection.Input, currCell.CellGraphic);
                            sp.AddParameter("@displayGraphic", SqlDbType.Char, 2, ParameterDirection.Input, currCell.DisplayGraphic);
                            result = sp.ExecuteNonQuery();
                        }
                    }
                    DAL.DBWorld.cellsToUpdate.Clear();
                    DAL.DBWorld.roundToUpdate = DragonsSpineMain.GameRound;
                }
            } catch (Exception e) {
                Utils.LogException(e);
                result = -1;
            }
        }
        cellsToUpdate.Add(cell);
        return result;
    }
}

…and other code/scripts pretty much the same as the live NPC stuff. It seems like the server is pretty slow to start up now; I wonder why…

 {Unknown} Round 1: Inserting 1770 live NPCs.
 {Unknown} Round 1: Inserting 426095 live cells.
 {Unknown} Round 2: Inserting 1623 live NPCs.
 {Unknown} Round 2: Inserting 18 live cells.

I guess inserting those initial 426k+ cells takes some time; good thing we only update changed cells on subsequent rounds.

Visualizer side

First we’ll just make sure we can read the live cell data and do something silly like count the number of open doors.

Assets/DragonsSpine/DAL/DBWorld.cslink
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167

public static int UpdateCellDict(Dictionary<CellLoc, CellLite> cellDict, int sinceRound) {
    int numUpdated = 0;
    CellLoc tmpCellLoc;
    List<CellLite> cellList = GetUpdatedLiveCells(sinceRound);
    foreach (CellLite cell in cellList) {
        tmpCellLoc = new CellLoc(cell.x, cell.y, cell.z);
        cellDict[tmpCellLoc] = cell;
        numUpdated += 1;
    }
    return numUpdated;
}

public static List<CellLite> GetUpdatedLiveCells(int sinceRound) {
    List<CellLite> cellList = new List<CellLite>();
    int tmpRound = 0;
    using (SqlConnection tempConnection = DataAccess.GetSQLConnection())
    using (SqlStoredProcedure sp = new SqlStoredProcedure("prApp_LiveCell_by_MapID", tempConnection)) {
        sp.AddParameter("@facet", SqlDbType.Int, 4, ParameterDirection.Input, 0);
        sp.AddParameter("@land", SqlDbType.Int, 4, ParameterDirection.Input, 0);
        sp.AddParameter("@map", SqlDbType.Int, 4, ParameterDirection.Input, 0);
        using (DataTable dtCells = sp.ExecuteDataTable()) {
            foreach (DataRow dr in dtCells.Rows) {
                tmpRound = Convert.ToInt32(dr["lastRoundChanged"]);
                if (tmpRound >= sinceRound) {
                    CellLite tmpCell = new CellLite();
                    tmpCell.x = Convert.ToInt16(dr["xCord"]);
                    tmpCell.y = Convert.ToInt16(dr["yCord"]);
                    tmpCell.z = Convert.ToInt16(dr["zCord"]);
                    tmpCell.displayGraphic = dr["displayGraphic"].ToString();
                    cellList.Add(tmpCell);
                }
            }
        }
    }
    return cellList;
}
Assets/Managers/MapManager.cslink
276
277

//... time consuming job is performed here...
int result = DragonsSpine.DAL.DBWorld.UpdateCellDict(param, 0);
Assets/Managers/MapManager.cslink
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187

void Update() {
    if (updateComplete && Time.time > nextDBUpdate) {
        print("Time to read from the DB! " + Time.time);
        nextDBUpdate = Time.time + dbUpdateDelta;
        updateComplete = false;
        int doorsOpen = 0;
        foreach (KeyValuePair<CellLoc, CellLite> pair in cells) {
            if (pair.Value.displayGraphic == "/ " || pair.Value.displayGraphic == "\\ ") {
                doorsOpen += 1;
            }
        }
        print("Open doors: " + doorsOpen);
        UpdateCellContents();
    }
}

Let this run for a bit, and…

$ grep "Open doors:" Editor.log
Open doors: 0
Open doors: 113
Open doors: 116
Open doors: 115

So we’re able to read the DB and update the display graphic. But we need to make that useful… ***

Visualizer Side pt 2

Time to upgrade the cell objects in the scene. CellScript is a slightly modified simpler version of NpcScript that we’ll attach to the cell prefab.

Assets/CellScript.cslink
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

public class CellScript : MonoBehaviour {

    public CellLoc cellLocation;
    public Vector3 newPosition, newScale;
    Vector3 oldPosition, oldScale;
    float timeSinceStable;
    public bool toBeSeen; // independent of local concerns, does the UI want to see me?

    public string displayString;

    private Material currMaterial;
    public Material CurrMaterial {
        get { return currMaterial; }
        set {
            if (currMaterial != value && myRend != null) {
                currMaterial = value;
                myRend.material = currMaterial;
                myRend.material.mainTextureScale = new Vector2(-1f,-1f);
                oldScale = transform.localScale = Vector3.zero;
            }
        }
    }

    private Renderer myRend;

    public MapManager mapManager;

    void Awake () {
        Reset();
        myRend = GetComponent<Renderer>();
    }

    public void Reset() {
        oldPosition = newPosition = transform.position;
        oldScale = newScale = transform.localScale;
        timeSinceStable = 0f;
        toBeSeen = true;
    }

    void Update () {
        timeSinceStable += Time.deltaTime;
        if (transform.position != newPosition) {
            transform.position = Vector3.Slerp(oldPosition,newPosition,timeSinceStable);
            if (timeSinceStable >= 1f) transform.position = newPosition;
        }
        if (transform.localScale != newScale) {
            transform.localScale = Vector3.Slerp(oldScale,newScale,timeSinceStable);
            if (timeSinceStable >= 1f) transform.localScale = newScale;
        } 
        if (transform.localScale == newScale && transform.position == newPosition) {
            timeSinceStable = 0f;
            oldPosition = transform.position;
            oldScale = transform.localScale;
        }

        // disappear if desired
        myRend.enabled = toBeSeen;
    }


}

This required a few changes to MapManager. Disabling the chunking for now, and keeping a dictionary of CellScript objects instead of transforms, we instantiate a little differently:

Assets/Managers/MapManager.cslink
94
95
96
97
98

tempCell = (CellScript)Instantiate(cellScript);
tempCell.mapManager = this;
tempCell.newPosition = position;

cell_objects[c] = tempCell;

Then we update the material if necessary due to DB updates:

Assets/Managers/MapManager.cslink
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203

void Update() {
    if (updateComplete && Time.time > nextDBUpdate) {
        print("Time to read from the DB! " + Time.time);
        nextDBUpdate = Time.time + dbUpdateDelta;
        updateComplete = false;
        int doorsOpen = 0;
        foreach (KeyValuePair<CellLoc, CellLite> pair in cells) {

            if (!cellMaterials.ContainsKey(pair.Value.displayGraphic)) {
                String materialName = StringToAsciiHex(pair.Value.displayGraphic);
                Material cellMaterial = Resources.Load("Materials/Cells/" + materialName, typeof(Material)) as Material;
                if (cellMaterial == null) {
                    print("Couldn't find material " + materialName + " for display graphic \"" + pair.Value.displayGraphic + "\"!");
                    Debug.Break();
                } else {
                    cellMaterials[pair.Value.displayGraphic] = cellMaterial;
                }
            }
            cell_objects[pair.Key].CurrMaterial = cellMaterials[pair.Value.displayGraphic];
            
            if (pair.Value.displayGraphic == "/ " || pair.Value.displayGraphic == "\\ ") {
                doorsOpen += 1;
            }
        }
        print("Open doors: " + doorsOpen);
        UpdateCellContents();
    }
}

With that and a few other tweaks, we see the results pretty quickly. Doors are being opened by NPCs, and the trees change because of the randomness in their placement when the server loads the map.

Day 26 code - server Day 26 code - visualizer