301 Days

A year of gamedev experiments.

Day 5 - Frampton's Wolves

| Comments

Producing and consuming some real moment-to-moment data…


Finally something good in Update()

Let’s try checking the portal locations on a regular basis first. First in Map.cs, the ability to update Cell info without reloading the whole map:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public void UpdateCells() {
    // load the cell information from the Cell table
    List<Cell> cellsList = DAL.DBWorld.GetCellList(this.Name);
    // The above was previously commented out in favor of:
    //    List<Cell> cellsList = new List<Cell>();

    foreach (string tempKey in this.cells.Keys) {
        this.cells[tempKey].IsMapPortal = false;
    }

    string key = "";
    foreach (Cell iCell in cellsList)
    {
        key = iCell.X.ToString() + "," + iCell.Y.ToString() + "," + iCell.Z.ToString();
        if (this.cells.ContainsKey(key))
        {
            this.cells[key].Segue = iCell.Segue;
            this.cells[key].Description.Trim();
            this.cells[key].Description = iCell.Description + " " + this.cells[key].Description;
            this.cells[key].Description.Trim();
            this.cells[key].cellLock = iCell.cellLock;
            this.cells[key].IsMapPortal = iCell.IsMapPortal;
            this.cells[key].IsTeleport = iCell.IsTeleport;
            this.cells[key].IsSingleCustomer = iCell.IsSingleCustomer;
        }
    }
}

and in MapManager.cs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
void Update () {
    if (Time.time > nextDBUpdate) {
        print("Time to update the DB! " + Time.time);
        nextDBUpdate = Time.time + dbUpdateDelta;
        UpdateCellContents();
        foreach (Transform t in portalTransforms) {
            Destroy(t.gameObject);
        }
        portalTransforms = new List<Transform>();
        foreach (Vector3 v in cellsKeys) {
            if (cells[v].IsMapPortal) {
                Vector3 position = new Vector3((-1f) * cells[v].X, (-1f) * cells[v].Y, (0.1f * cells[v].Z) + 0.5f);
                print(cells[v].IsMapPortal + "Portal at " + position);
                Transform tempts = Instantiate(portalPrefab,
                                               position,
                                               Quaternion.identity) as Transform;
                portalTransforms.Add(tempts);
            }
        }
    }
}

void UpdateCellContents() {
    ourMap.UpdateCells();
}

The portals don’t actually move around on their own, but we can always tweak the database manually: Cool. But it’s taken an embarrassingly long time for me to get this far, and it’s really not yet what I was looking for. Hmm.


Warning: Live Animals

So we can pull live data from the database, but unfortunately there’s not a lot there. The game server is meant to just be running for long periods of time, with the current state of the world just kept in memory. Let’s change that.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/****** Object:  Table [dbo].[NPCLocation] ******/
CREATE TABLE [dbo].[NPCLocation](
    [NPCIndex] [int] NOT NULL,
    [active] [int] NOT NULL,
    [name] [nvarchar](255) 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,
 CONSTRAINT [PK_NPCLocation] PRIMARY KEY CLUSTERED
(
    [NPCIndex] ASC,
    [facet] ASC,
    [land] ASC,
    [map] 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/****** Object:  StoredProcedure [dbo].[prApp_NPCLocation_By_MapID] ******/
CREATE PROCEDURE [dbo].[prApp_NPCLocation_By_MapID]
    -- Add the parameters for the stored procedure here
    @facet smallint,
    @land smallint,
    @map smallint
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT *
    FROM NPCLocation
    WHERE facet=@facet AND land=@land AND map=@map
END

GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/****** Object:  StoredProcedure [dbo].[prApp_NPCLocation_Update] ******/
CREATE PROCEDURE [dbo].[prApp_NPCLocation_Update]
    -- Add the parameters for the stored procedure here
    @NPCIndex int,
    @active int,
    @name nvarchar(255),
    @facet smallint,
    @land smallint,
    @map smallint,
    @xCord smallint,
    @yCord smallint,
    @zCord int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    UPDATE NPCLocation
    SET name=@name, active=@active, xCord=@xCord, yCord=@yCord, zCord=@zCord
    WHERE NPCIndex=@NPCIndex AND facet=@facet AND land=@land AND map=@map;
    IF @@ROWCOUNT=0
        INSERT INTO NPCLocation (NPCIndex, active, name, facet, land, map, xCord, yCord, zCord)
        VALUES (@NPCIndex, @active, @name, @facet, @land, @map, @xCord, @yCord, @zCord)
END

GO

With a lot of help from Management Studio (I’m an SQL Server novice at best), we have a table to hold NPC locations, and two stored procedures to query and update it. I could have done without the stored procedures, but the game server code uses them for everything so I decided to do so for consistency. A careful observer will notice that we don’t have anything in place for clearing stuff out of the table, but let’s see what we can get working with this.

First, on the server side, in DAL/DBNPC.cs where the database calls for NPCs live:

SaveNPCLocationGitLab
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
internal static int SaveNPCLocation(NPC npc)
{
    String sptouse = "";
    int result = -1;
    try
    {
        sptouse = "prApp_NPCLocation_Update";

        using (SqlConnection tempConnection = DataAccess.GetSQLConnection())
        using (SqlStoredProcedure sp = new SqlStoredProcedure(sptouse, tempConnection))
        {
            int NPCIndex = Character.NPCList.IndexOf(npc);
            sp.AddParameter("@NPCIndex", SqlDbType.Int, 4, ParameterDirection.Input, npc.worldNpcID);
            sp.AddParameter("@active", SqlDbType.Int, 4, ParameterDirection.Input, DragonsSpineMain.GameRound);
            sp.AddParameter("@name", SqlDbType.NVarChar, 255, ParameterDirection.Input, npc.Name);
            sp.AddParameter("@facet", SqlDbType.Int, 4, ParameterDirection.Input, npc.FacetID);
            sp.AddParameter("@land", SqlDbType.Int, 4, ParameterDirection.Input, npc.LandID);
            sp.AddParameter("@map", SqlDbType.Int, 4, ParameterDirection.Input, npc.MapID);
            sp.AddParameter("@xCord", SqlDbType.Int, 4, ParameterDirection.Input, npc.X);
            sp.AddParameter("@yCord", SqlDbType.Int, 4, ParameterDirection.Input, npc.Y);
            sp.AddParameter("@zCord", SqlDbType.Int, 4, ParameterDirection.Input, npc.Z);
            Utils.Log("Round " + DragonsSpineMain.GameRound + ": Inserting NPC location, name: " + npc.Name, Utils.LogType.Unknown);
            result = sp.ExecuteNonQuery();
        }
    }
    catch (Exception e)
    {
        Utils.LogException(e);
    }
    return result;
}

I actually wasted a bunch of time implementing my own unique ID in the NPC class before finding worldNpcID in the Character class (which is the parent of NPC). Lesson learned: Always poke around and look for existing code that would require what you’re about to implement. In this case it was the NPC grouping code. I decided to push the current round number into “active” instead of just a boolean so that we can have an idea of when an NPC died/disappeared.

Second, again on the server side, in GameObjects/GameLiving/NPC/NPC.cs at the end of NPCEvent:

NPCEventGitLab
1853
1854
1855
// Save NPC location
if (!npc.IsDead)
    DAL.DBNPC.SaveNPCLocation(npc);

Initially, nothing worked. Turns out there is a global flag ProcessEmptyWorld that needs to be set to true; otherwise nothing happens unless a player is logged in. Once that was taken care of:

Now, to put that to use. Step three, on the Unity client side, in DAL\DBNPC.cs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
public static List<NPC> GetWolves()
{
    List<NPC> npclist = new List<NPC>();

    using (SqlConnection tempConnection = DataAccess.GetSQLConnection())
    using (SqlStoredProcedure sp = new SqlStoredProcedure("prApp_NPCLocation_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);
        DataTable dtNPCs = sp.ExecuteDataTable();
        foreach (DataRow dr in dtNPCs.Rows)
        {
            if (dr["name"].ToString() == "wolf") {
                NPC wolf = new NPC();
                wolf.Name = dr["name"].ToString();
                wolf.X = Convert.ToInt16(dr["xCord"]);
                wolf.Y = Convert.ToInt16(dr["yCord"]);
                wolf.Z = Convert.ToInt16(dr["zCord"]);
                npclist.Add(wolf);
            }
        }
    }
    return npclist;
}

So that in MapManager.cs we can keep a list of wolf transforms and add this to Update():

1
2
3
4
5
6
7
8
9
10
11
12
13
14
List<NPC> wolves = DragonsSpine.DAL.DBNPC.GetWolves();
print("Wolves?  I found: " + wolves.Count);
foreach (Transform t in wolfTransforms) {
    Destroy(t.gameObject);
}
wolfTransforms = new List<Transform>();
foreach (NPC wolf in wolves) {
    Vector3 position = new Vector3((-1f) * wolf.X, (-1f) * wolf.Y, (0.1f * wolf.Z) + 0.2f);
    print("Wolf at " + position);
    Transform tempts = Instantiate(wolfPrefab,
                                   position,
                                   Quaternion.identity) as Transform;
    wolfTransforms.Add(tempts);
}

Now all we need to do is throw together a wolf prefab with some public domain clip art, and:


Things to fix. Tomorrow? Maybe.

  1. We’re hammering the database server with NPC location updates.
  2. Multiple NPCs on the same cell appear as one.
  3. Inactive NPCs will still show up.
  4. The location table doesn’t get cleared when we restart the game server.

Day 5 code - client Day 5 code - server

Comments