Java-tastic Mod-ilicious Minecraft-ium

That’s my fancy title for saying I’m taking a stab at learning how to mod Minecraft.  Really I don’t care to learn how to do it…but my little 8 year old wants to learn how and is asking me to teach him.  How can I say no to that?

In all honesty…I’m not sure he’s ready.  The stuff you have to know in order to do this successfully are a pretty tall order for an 8 year old whose only real knowledge about a computer is how to double click an icon and navigate URL’s in a web browser.  I’ve tried to install some deeper knowledge in him but so far there hasn’t been a real hunger to know how things work under the covers.  So what I’m going to be doing with this post is laying out what I think he’ll need to learn in order to achieve his end goal of making a mod for Minecraft.  I’ll be coming back to this post from time to time to add / update content as I find things out for myself that I want to pass along to him.  With that being typed…

Here’s the list of things in order of priority:

Knowledge of the File System

Since I’m a Windows guy…I won’t be going into Unix \ Linux \ Apple based OS’s.  A person who’s trying to figure out how to mod Minecraft MUST know how to navigate around a File System in Windows.  You have to be able to know what it means to create a directory, unzip a file, open a command prompt…etc…if a person doesn’t understand these basic concepts it will be impossible for them to make a mod.

Know what Java is

If the prospective modder doesn’t know what Java is and why it’s important to Minecraft, they probably shouldn’t try writing a mod.  Writing code without fundamentally understanding the langauge being used will cause problems.  Personally I know how to code in Java because it’s very similar to C# and I know that very well.  The minor differences I can find out how to handle easily enough by doing a quick search on the internet so that’s the same approach I’ll be taking when teaching my son.  It works for me.  😉

So not only do they need to know what Java is…but they’ll need to know how to install the JDK which is short for Java Development Kit.  It’s a little more than just the Java Runtime Environment in that it includes some tools used in developing Java code.

Know what an IDE for Java is

IDE is short for Integrated Development Environment.  Basically it’s the program you use to write code.  For most languages I use Visual Studio.  I’m used to it…I know all the keyboard shortcuts…I like it.  But for Java, it doesn’t really work so well…so I use the free Java specific one called Eclipse.  It seems to be the most popular and it’s fairly easy to muddle your way through it if you’re used to Visual Studio.  If you’re going to create a mod…you’re going to have to get familiar with an IDE.

Know what Forge is

There are other methods of modding Minecraft, but in my opinion the are a waste of time when you have a wonderfully built framework like Forge to build on top of already there just waiting to be used.  Using Forge will take a lot of the learning curve out as the modder won’t have to know how to actually hook into Minecraft…which is really the hard part.  Instead they’ll get to focus on the code for their new recipe’s, block’s, etc…which is really what the excercise is about.  If he gets interested in coding, then I’ll let him sink his teeth into figuring out how to hook into Minecraft however he wants, but I’m starting him off using Forge.

So along with knowing what Forge is and what it’s doing…he’ll have to know how to install the MDK for it which is short for Mod Development Kit.  Forge is super nice in that they set it up ahead of time to work with Eclipse so that’ll save some time as well.

This provides instructions which give an okay-ish experience on installing the Forge MDK…but there’s some gaps I’ll try to fill in here.

Gaps:

  • It gives a vague “make sure Java is installed” disclaimer at the top with some very basic stuff on how to get it ready…really if you can’t open up a command prompt and type in “java -version” without it producing an error…don’t even bother trying anything on this page yet…go back to your Java installation and get it figured out before doing anything more with Forge
  • It tells you to use this “gradlew” command for downloading source code for the various code libraries used in modding.  The 2nd one that I used for Eclipse was deceiving in that I thought it was pulling down Eclipse and installing it…that’s not the case…it is only pulling down the special package info Eclipse uses for projects…you still need to install Eclipse on your own.

Know where to find where Minecraft is running from

If you’re going to mod Minecraft you better know where to find it.  Although the Forge MDK does include it with it’s distribution so this one isn’t “as” important as the others…but I think it’s important knowledge regardless…

Mod Minecraft

Once we’re at this point…then it’s really up to him to figure out what he wants to make a mod of, what new blocks and recipes he needs, and then add them in…should be gravy from here.  🙂

This has some instruction to get you started, but the paths are all wrong…but if you can figure your way past that, the basic ideas that are being conveyed still seem to be relivant.  I was able to get my own mod created and injected into Minecraft after reading through it and making some correction for src paths.

My Experiement

Here’s the steps I went through to get a working mod that only spits out an “initialized” message in the Minecraft console.

  1. Installed JDK version 8u77 from here.
  2. Added the installed location for JDK to the system Path environment variable
  3. Installed Eclipse version Mars.2 (4.5.2) from here.
  4. Downloaded Forge MDK version 1.9 – 12.16.0.1816 from here.
  5. Unzipped the MDK onto the C: drive into a folder called “Forge”.  Ex: C:\Forge
  6. Opened a command prompt window and navigated to C:\Forge
  7. Execute the following command:
    gradlew setupDecompWorkspace --refresh-dependencies
  8. Execute the folloiwng command:
    gradlew eclipse
  9. Launched Eclipse and pointed at C:\Forge\eclipse for the working directory
  10. Locate the “examplemod” class that’s already created by looking in the Package Explorer and expanding MDKExample->src/main/java->com.example.examplemod.  From here you should see an ExampleMod.java file which will already work and spit out “DIRT BLOCK >> dirt.block” to the console…but I renamed mine to “Toffkins” and had it output something a little more specific to my mod.  “Congrats!  You’ve initialized the mod: [Toffkins] version: [1.0]”

That’s it…your first mod.  To try it out in Eclipse…just hit the little green “Run” arrow and it’ll launch Minecraft for you.  From here you can watch the console output right from Eclipse.  If you weed through all the spew…you’ll eventually see your mod’s output…or you can look within loaded Minecraft client and click the Mod button to see your new mod within the list of loaded mods.  Congrats…now go make it do something AMAZING and let me know what you’ve made.  🙂

Here’s a link of what I’m going to attempt next…which is adding a command to do something while logged into the game.  Small steps.  🙂

Update on 6/7/2016 – I’ve actually got my mod so I can type in commands while in game now.  I called it “Toffkins” and I’m going to make it a “Jarvis”-like thing for inside the Minecraft game.  Really I’m not going to be putting much more effort into this until someone starts asking me more questions in this area…but I’ve left it in a good spot where I can easily pick it back up and start doing cooler things.  Until then…

Java-tastic Mod-ilicious Minecraft-ium

Casting Call

Something short and to the point today.  I was talking to my office roommate this morning about old school coding habits vs newer syntax styles and the subject of “casting” came up.

You see, I’ve done casting like this for years and years…

string example = null;
example = (string)e.Value;

and when I started doing more coding with telerik controls, I see all their samples do casting like this

string example = null;
example = e.Value as string;

I didn’t think anything of it at first, just thinking the “as” way was just a newer way to do it that was a bit more readible…however my co-worker let me know that they actually do different things.

In my first example if you try to cast something that’s not a string into the “example” variable you will get an InvalidCastException thrown.

In my second example if you try to cast something that’s not a string into the “example” variable…”example” will just be equal to null.  That’s useful when you’re not absolutely sure what kind of type you’re dealing with and saves you from having to put up try \ catch blocks everywhere.

Anyway…this was just a quick useful piece of info I happened across today and thought I’d leave a blog post about it.  Hope it helps…Enjoy!  🙂

Casting Call

Linq To Sql and the art of motorcycle maintenace

Now that my superb title has caught your eye and dragged you into the post…why not stay and read the whole thing?  What a treat!

My last 2 technical posts were about creating a MS Sql database using a Sql Database Project template in Visual Studio…and then about basic table and relational data design.  Today I’m going to build off the last post and we’re going to make some Linq to Sql classes out of the tables we created.

We ended up with 2 tables.  One called “Vehicle” that had a column which was an Id that refered to another tabled called “Engine”.  If you need a refresher, go take a peak at the post…I’ll wait………….okay, good to have you back.

So now we have this awesome database with those 2 tables…and we give them to the junkyard owner.  He gives you this crazy look because…well geez you just plopped a handful of electrons into his hands…yeah he’s going to look at you weird.  The other reason is he has no idea how to enter data into the database…clearly you have some work left to do like create some sort of interface for the guy to work with the database.

So lets aplogize to the junkyard owner and get back to work.  What do we use for an interface…back in the old days we used to SqlDataTable’s and spun through the tables hand crafting these elaborate object models to represent the tables as objects…then along came Linq which slowly has been creeping it’s way into our hearts…and now that whole processes has been automated.  Now it’s as easy as adding a new item into your .NET project by using a template called “Linq to SQL”.  It looks something like this…

LinqToSQLItem

Using this will bring up a design surface where you connect up to a database in your SQL Server Object Explorer and then drag your database objects over into your Linq to SQL design surface and presto…it goes and generates all the elaborate .NET objects to represent your database objects.  That looks something like this…

LinqToSqlDesignSurface

So once you get to this point…you “could” go take a peak at the objects it creates…but they are some crazy mystical things that just work so I wouldn’t bother digging into it.  The important part to remember is if you make any changes to your database, you must also come back to this design surface, delete, and then recreate the all the objects…it’s the safest most reliable way to go with Linq to Sql in my experience.

So I’ll give you the basic layout of the classes it creates…the actual code is much much more complex so what I’m showing is only the basic layout of properties.  I’ll also be using C# for my code layout…I don’t do VB.  🙂

First for Engine…

public class Engine
{
    public int Id { get; set; }

    public string Make { get; set; }

    public int Cylenders { get; set; }

    public string Displacement { get; set; }

    public int Horsepower { get; set; }
}

and then another for Vehicle…

public class Vehicle
{
    public int Id { get; set; }

    public int EngineId { get; set; }

    public Engine Engine { get; set; }

    public string Make { get; set; }

    public string Model { get; set; }

    public int Year { get; set; }

    public int WheelCount { get; set; }

    public int PassengerCapacity { get; set; }
}

So you can see in the Vehicle class, there’s not only the EngineId that points at a record to something in the Engine table…but there’s an actual object for the Engine hanging around as a property…and you better believe that it’s all ready to be filled up with the exact record the EngineId points at…but that’s the trick…it’s ALL READY to be filled up…it doesn’t actually contain the data until you go to use it.  This looks innocent and helpful, but it’s not when you’re doing recursive lookups through lists…it actually becomes a huge perf hit.  Let me explain.

So what’s actualy stored in that Engine property is a Sql query that gets executed once someone tries to use the object.  So imagine that we had a list of 100 engines and for some reason we needed to loop through them all to output to the console.

JunkyardJemsDataContext dataContext = new JunkyardJemsDataContext();

foreach(Engine engine in dataContext.Engines)
{
    Console.WriteLine($"Id          : {engine.Id}");
    Console.WriteLine($"Make        : {engine.Make}");
    Console.WriteLine($"Cylenders   : {engine.Cylenders}");
    Console.WriteLine($"Displacement: {engine.Displacement}");
    Console.WriteLine($"Horsepower  : {engine.Horsepower}");
}

What’s actually happening in the background is for each iteration of the loop it’s doing a Sql query to get all th details for just one record…so in our case that would be 100 Sql queries if we had 100 records.  That’s not so horrible.  But what if we did something like this and we had 100 Vehicles…

JunkyardJemsDataContext dataContext = new JunkyardJemsDataContext();

foreach(Vehicle vehicle in dataContext.Vehicles)
{
    Console.WriteLine($"Id          : {vehicle.Engine.Id}");
    Console.WriteLine($"Make        : {vehicle.Engine.Make}");
    Console.WriteLine($"Cylenders   : {vehicle.Engine.Cylenders}");
    Console.WriteLine($"Displacement: {vehicle.Engine.Displacement}");
    Console.WriteLine($"Horsepower  : {vehicle.Engine.Horsepower}");
}

On the surface it doesn’t look much different…but what’s really happened is you’ve at least doubled the amount of Sql queries going.  Since we said there’s 100 Vehicles…that’s 1 Sql query going on for each Vehicle…but it’s also another being done to get the Engine for the Vehicle…so now that’s 200 queries…and that’s with only one level of normalization.  Let’s say we decided to normalize out “Make” as well.  If we did that it could be used in both the Engine and Vehicle tables…lets’ pretend we did that.

Now there’s a query for the Vehicle, one for the Make, one for the Engine, which also does one for the Make…we’re up to 4 queries for just one thing…and we’ve only normalized out 2 pieces of data now.  Ok…now lets kick that up to 10,000 Vehicles.  Going from 10,000 Sql queries to 40,000 is a pretty big perf jump…it would be noticable.

One of the things I do to combat this is use the .ToList() method a lot.  If you go and do the following…it forces Linq to do the query and populate the results.

JunkyardJemsDataContext dataContext = new JunkyardJemsDataContext();
var vehicles = dataContext.Vehicles.ToList();
var engines = dataContext.Engines.ToList();

foreach(Vehicle vehicle in vehicles)
{
    var engine = engines.FirstOrDefault(e => e.Id == vehicle.EngineId);

    Console.WriteLine($"Id          : {engine.Id}");
    Console.WriteLine($"Make        : {engine.Make}");
    Console.WriteLine($"Cylenders   : {engine.Cylenders}");
    Console.WriteLine($"Displacement: {engine.Displacement}");
    Console.WriteLine($"Horsepower  : {engine.Horsepower}");
}

To me it looks like you’re creating more objects and wasting perf, but nope…this is way waaaaay faster than letting Linq to SQL do it’s behind the scene magic.  Doing the .ToList() on each table forces it to fill the “vehicles” and “engines” variables…then doing the .FirstOrDefault method is happening on the local collection and not the database.  Also notice I’m not making use of the Engine property that’s hanging off the Vehicle object…If you don’t use it…it won’t execute an extra sql query…so instead I use the EngineId in the FirstOrDefault call against the local collection where all the data is already filled out.

So there’s my Sql topics for now.  I’ve got some more advanced stuff to post about in the future, but these 3 should be enough to help any junkyard owner get up and running.  😉  Hope it helps.  Enjoy!

Linq To Sql and the art of motorcycle maintenace

A little tune that touches my soul

Hello loyal followers!  I think today I’ll be posting something musical.  I mentioned this song on Facebook a while back…but I heard it on my way into work this morning and refreshed it’s presense in my head so…away we go.

I’m not an overly religious person…and I’m not going to ever really chat about religious beliefs in this blog…let it suffice to say I follow pretty close to an agnostic like system.

That being said…these lyrics from Machine Head’s Darkness Within are something that make me understand that “they were wrote for me” cliche…

Mysterys forgotten chords
I strum in vain to please the lord
But he has never answered me
My faith has waned eternally
In empty men who pass along
The woes of all religions wrong
But now the shadowed veil it falls
Heed the clarion call
So pray to music build a shrine
Listen in these desperate times
Fill your heart with every note
Cherish it and cast afloat
Because god is in these clef and tone
Salvation is found alone
Haunted by its melody
Music it will set you free
Let it set you free

I used to be a bible thumpin hard core believer in Christianity…which the first 4 lines spells out beautifuly.  Then as I’ve grown older my experiences have shown me there’s the same short comings I found in Christianity in pretty much all other religions…and I eventually come to realize that my religion comes from the music I listen to.  It’s where I look to for comfort, solace, wisdom, direction, and faith.  “God is in these clef and tone…Salvation is found alone.”  That so fits me to my core.  Anyone that knows me will know it was true long before I ever heard this song.

Now every time I hear these lyrics, my eyes well up…maybe it’s the way Robb Flynn is delivering the message…maybe it’s just the power of the connection that resonates with me…but it’s no exaguation…Music!  Let it set you free!

So anyway…I think I’ll wrap it up by saying that music is so amazing.  The sound carries with it the history of our world with every new combination and mashing that’s thought up.  The words inspire emotion and passion in everyone…maybe not all the time, but no one can deny that music has brought out some degree of emotion from within them at some piont in their life…few things have that sort of all encompassing effect.  Anyway…that’s enough for now.  Hope you enjoyed the perspective.  🙂

A little tune that touches my soul

The exciting tale of designing Sql tables…

Ah…more Sql stuff to talk about…weeee.

In my last post I rambled on about what it’s like to create a database from scratch these days using a Visual Studio Sql Server Project template and a couple of the pitfalls that I encounter…

Grab a cup o’ coffee and get comfy because today I’m posting my thoughts on table design and when to normalize or not…with another post to follow that will further expand on what happens in Linq to Sql when you have it build objects out of the tables we design in this post.

I know…exciting stuff right?  I know I’m already setting up a caffine drip IV into my arm too!

Ok…so the basics which I’m sure you all know if you read the TLA SQL and dind’t GTFO…a database is a way to logically store data.  Put simply a database will let you define a structure for how you want to store data and then allow you to create relationships between that data.  A table is a way to logically group your data.  A column is one piece of data that’s being collected.  A row is one record of data containing one or more columns of data.  So those are the basics.  If you can wrap your head around the above paragraph…that’s all you really need to make a good and performant database…now you just need the wisdom on how to apply the above things.

Creating the database is an important step, but often there’s a Sql Administrator who takes care of that stuff…and I’m more of a schema kinda guy.  Sure I know how to do the creating stuff…but it’s not the part I like to focus on because the database configuration often needs to be fiddled with after it’s deployed, so when creating a new database I tend to do just enough to get it running and available over the network…then I work on the schema.  A schema is a way to refer and look at the entire collection of tables, views, stored procedures, functions, and their relationships that define the data being stored within a database.  Since out of those things, I’ve only mentioned tables and relationships…lets take a closer look at what those are.

So for a data set to use for my example, we’re going to build a database for a junk yard who gets junked vehicles and sells the parts off.  Lets start picking apart what we can logically group together as data.  So there’s going to be “vehichles”…all vehicles pretty much have a “make”, “model”, “year”, “number of wheels”, “engine”, “how many can ride in / on the vehicle”…that’s a good group of things that pretty much ever vehicle has.  So here’s how we make a table for it and I’ll explain some things after.

CREATE TABLE [dbo].[Vehicle]
(
    [Id]                    INT IDENTITY(1, 1) NOT NULL,
    [Make]                  NVARCHAR(50) NOT NULL,
    [Model]                 NVARCHAR(100) NOT NULL,
    [Year]                  INT NOT NULL,
    [WheelCount]            INT NOT NULL,
    [Engine]                NVARCHAR(MAX) NOT NULL,
    [PassengerCapacity]     INT NOT NULL, 

    CONSTRAINT PK_Vehicle PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT UNIQUE_Vehicle unique ([Make], [Model], [Year])
)
GO

CREATE NONCLUSTERED INDEX NCI_Make
ON Vehicle([Make] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Model
ON Vehicle([Model] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Year
ON Vehicle([Year] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_WheelCount
ON Vehicle([WheelCount] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_PassengerCapacity
ON Vehicle([PassengerCapacity] ASC)

Ok, so there’s the basic layout. The first part before the CONSTRAINT definitions is where I’m actually defining the columns and what kind of data it contains. Id’s are generally always an INT…but I’ve seen them be GUID’s before. Make and Model are both strings…but in Sql we call them VARCHAR…and NVARCHAR is a unicode version of VARCHAR so I like to use that as it’s more friendly when doing globalization. Year, WheelCount, and PassengerCapacity are all INT’s…but Engine is setup as a NVARCHAR(MAX). What that’s saying is to allocate an obscenely huge chunk of space for a text blob that’s going to contain the defenition of the Vehicles engine. It could take a lot to explain an engine so I made it big.

Now we can talk about the constraints.  The first one I’ve already spoken about in my last post…but the second one is new.  What that one is saying is to setup a rule that defines what a “unique” vehicle is.  I chose to say that for every combination of Make, Model, and Year…I’m considering that unique.  What being unique does is it restricts any more data from being inserted if it matches a unique record that already exists within the table.  Technically I should probably add “Engine” into that equation…however NVARCHAR(MAX) cannot be used in a unique constraint…so that’s why I left it out.  I have a plan for that later though.  🙂

Now…onto the CREATE NONCLUSTERED INDEX lines.  What the heck is a NONCLUSTERED INDEX?  To be perfectly honest I used to know what the difference is between clustered and nonclustered but I’m a little fuzzy on it now and I’m going to be too lazy to look it up mostly because I know that you can only have one clustered index and the Id “usually” takes it up…for every database I have designed it does anyway.  🙂  So…let’s not worry about what the difference is…we’ll just focus on what an INDEX is.  Think of it like a cheat sheet for the database.  As you’re doing querying, the database is keeping track of things that are queried for…and as that happens it uses a private lookup table called the “INDEX” to help speed itself up.  What those CREATE NONCLUSTERED INDEX statements are for is to tell the database to add those columns into it’s little cheat sheet.  You may have noticed that there isn’t one for “Engine”.  That’s because NVARCHAR(MAX) cannot be indexed…it’s too big…again, like I said I have a plan for that.  Now just what should be indexed?  Well…that’s a topic of great discussion.  I tend to index everything possible…I feel it should never hurt to over-index things…the worst that’ll happen is those index’s just don’t get used.  The most important things to index are Id’s…or better put any sort of column that is being used to reference a specific row in a table.  So that means not just the identity column of a table, but Id’s being used in foreign key relationships as well…which I’ll be getting into in just a moment.

Ok…so now we have a basic table with some explination about what each part of the syntax is doing.  Woo to the hoo…but Engine is causing us some trouble because it’s big.  Can’t be used in consideration for uniqueness…can’t be indexed.  That darn column is going to slow down our database!  We can’t have that…so this is something in my experience that pops a red flag.  Whenever you have a piece of data in a table that should be considered for uniqueness and should be indexed but can’t because it’s a huge NVARCHAR(MAX)…see if you can break that huge string into it’s own table.  In our case Engine can probably be broken down to “make”, “cylenders”, “displacement”, “horsepower”…I’m sure there’s probably more things to call out, but I’m just coming up with example material so I’m not going to fully flesh this out.  So here’s our second table…

CREATE TABLE [dbo].[Engine]
(
    [Id]            INT IDENTITY(1, 1) NOT NULL,
    [Make]          NVARCHAR(50) NOT NULL,
    [Cylenders]     INT NOT NULL,
    [Displacement]  NVARCHAR(20) NOT NULL,
    [Horsepower]    INT NOT NULL, 

    CONSTRAINT PK_Engine PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT UNIQUE_Engine unique (Make, Cylenders, Displacement)
)
GO

CREATE NONCLUSTERED INDEX NCI_Make
ON Engine(Make ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Cylenders
ON Engine(Cylenders ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Displacement
ON Engine(Displacement ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Horsepower
ON Engine(Horsepower ASC)

Now we have 2 tables defined…but currently they really have nothing to do with one another.  Sure if you look at them…someone with a brain can start relating the data…but Sql is dumb and has no idea how they relate to one another so we have to be literal and tell it.  We do that by adding a foreign key relationship between the two.  To this we use a CONSTRAINT.  I’ll repost the code for the Vehicle table replacing the Engine column with a new EngineId column that points at the Engine table.

CREATE TABLE [dbo].[Vehicle]
(
    [Id]                    INT IDENTITY(1, 1) NOT NULL,
    [EngineId]              INT NOT NULL,
    [Make]                  NVARCHAR(50) NOT NULL,
    [Model]                 NVARCHAR(100) NOT NULL,
    [Year]                  INT NOT NULL,
    [WheelCount]            INT NOT NULL,
    [PassengerCapacity]     INT NOT NULL, 

    CONSTRAINT PK_Vehicle PRIMARY KEY CLUSTERED (Id ASC),
    CONSTRAINT UNIQUE_Vehicle UNIQUE ([EngineId], [Make], [Model], [Year]),
    CONSTRAINT FK_Vehicle_EngineId FOREIGN KEY (EngineId) REFERENCES Engine(Id)
)
GO

CREATE NONCLUSTERED INDEX NCI_EngineId
ON Vehicle([EngineId] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Make
ON Vehicle([Make] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Model
ON Vehicle([Model] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_Year
ON Vehicle([Year] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_WheelCount
ON Vehicle([WheelCount] ASC)
GO

CREATE NONCLUSTERED INDEX NCI_PassengerCapacity
ON Vehicle([PassengerCapacity] ASC)

Now you can see I’ve changed “Engine” to “EngineId”, changed the data type to an INT, modified the UNIQUE CONSTRAINT, added a new CONSTRAINT called FK_Vehicle_EngineId, and added an index for the EngineId column…that pretty much solves our problems but what does it really mean?  The only new thing you haven’t seen is the CONSTRAINT for the FOREIGN KEY.  So what this is saying is whenever something is being inserted into the Vehicle table…the Id being used in the EngineId column MUST exist in the Engine table before it can be inserted.  So if you had a 2 engines in the Engine table, Id’s 1 and 2…and then tried insert the value 3 into the Vehicle.EngineId column…you’d get an error telling you Id 3 doesn’t exist in the Engine table.

The whole process we just went through is called “normalizing”  Finding data that should be in it’s own table, creating a new table out of it, then setting up a foreign key relationship between the two.  There are dangers to watch out for though.  You should be aware on how this effects querying.

Before normalizing, this is pretty much what the query would look like…

SELECT [Make], [Model], [Year], [WheelCount], [Engine], [PassengerCapacity]
FROM Vehicle

and now after normalizing we’d have to do something like the following…

SELECT [v.Make], [v.Model], [v.Year], [v.WheelCount], [v.PassengerCapacity], [e.Make], [e.Cylenders], [e.Displacement], [e.Horsepower]
FROM Vehicle AS v
	JOIN Engine AS e ON v.EngineId = e.Id

See now how we had to add a JOIN to the statement in order to get to any of the Engine data…sure the data is a lot more defined and structured…however doing a join in a query is expensive in terms of perf.  The more you have, the slower your query will get.  Sometimes it’s an ok trade off…in this case, it was the right decision.  Notice how there’s a “Make” column in each table now…here’s something else that “could” be normalized, but I’m choosing not too…it’s a single string of data and it just adds too much overhead and complexity to the table design and the t-sql needed to query against it to bother for a small being of string data.

So that’s where I’ll leave off for now.  In my next post, I’ll show some Linq to Sql examples that really show the pitfalls of over-normalizing…but for now, hope this helps someone.  Enjoy!  🙂

The exciting tale of designing Sql tables…

MS Sql, how you have grown…

Hello again…today I’m going to start posting while reviewing my wonder app’s code…and it all starts from Sql so I’ll start there.

Where I work…MS Sql is pretty readily available…so I’ve always just used that.  To be honest, I don’t really have a preference…but I’ve used MS Sql for learning 90% of my Sql knowledge so that’s where I’m most comfortable.  The other 10% is split between some messing around in MySql for storing Minecraft mod data…and a little a for doing some php forums storage once a loooong time ago.

So my initial knowledge comes from way back in 2007-2008 time frame where I was given a lot of time to dig in and figure it out.  I’m one of those self taught idiots though so it took me a lot of time.  Back then I created my databases through use of script files that had to be run in a specific order and miticulously maintained through some sort of “master script”…a real pain in the butt.  I used a lot of stored procedures in my early days but that’s how it was done.  You create a sproc to do heavy processing of data and return the desired info in a dataset…you feed that into some C# objects and then you work with them using DataTables…pretty straight forward.

In current day, the Sql engine pretty much runs the same…sure there’s more bells and whistles…but deploying the database and accessing the data are handled way different.  I like it for the most part, but there’s bits that I’ve found to be a pain in the butt.

So now for deployment, I make use of a Sql Server Database Project in Visual Studio.

sqlproject

Really this doesn’t do anything different than what I was doing creating script files and running them in the correct order when deploying the database…however Visual Studio is now taking the creation and execution part out of the equation.  It takes care of all that now and I just have to focus on creating the tables and relationships between them.  Also the Schema Compare functionality has spoiled me pretty bad when it comes to deploying.

One of the pain in the butt things about using this however…when creating a new table it likes to use the following for defining the default Id column…

CREATE TABLE [dbo].[InterestingDemoStuff]
(
    [Id] INT NOT NULL PRIMARY KEY
)

This works just fine for creating new Id’s automagically…however what it doesn’t do is create an index for it that Linq To Sql can use. It creates an “unnamed” index which works within the context of Sql Server, but for some reason not Linq to Sql, so that being the case here’s what I like to use…

CREATE TABLE [dbo].[InterestingDemoStuff]
(
    ID  INT IDENTITY(1, 1) NOT NULL, 

    CONSTRAINT PK_InterestingDemoStuff PRIMARY KEY CLUSTERED (Id ASC)
)

This will create a named index that works exactly the same as the unnamed one. The other quirck about using a Sql Server Project is that it doesn’t handle scripting security roles very well…especially if dealing with an Azure Sql database…so take that with a grain of salt. You can set it up on the server, do a Schema Compare and import the security settings into the project…but since the project doesn’t have any access to Azure AD…there’s no way for it to validate the user names and it causes issues when trying to deploy through Visual Studio. Here’s how I’ve been doing it.

  1. Create the tables and relationships in Sql Server Project
  2. Create a database on the server with no tables
  3. Use the Sql Server Project to do a Schema Compare between the project and the newly created database
  4. Update the target with any differences detected in the Schema Compare
  5. Apply security settings to the database on the server

This seems to be a pretty “quirk free” process to get all the bits and pieces of a Sql Db deployed.  Next I’ll post about my thoughts on table design, normalizing, and foreign key relationships in the context of using it with Linq to Sql.  Until then…hopefully this will be useful to someone.  Enjoy!  🙂

MS Sql, how you have grown…

…aaaaaand done…

*breathes a deep breath*
Wow…what a coding marathon.  I don’t think I’ve wrote that much code in one sitting before.  So as you may or may not have noticed…I took a few days break from blogging because I had an obscene amount of code I needed to write for my job and didn’t want to be distracted with my knowledge pursuit.
What I’ve been doing is rewriting my wonder app from the ground up.  The wonder app is what I call this home brewed app I whipped up for my job.  I’ll never go into technical details about it’s doing…but let it suffice to say it does everything…at least it does now.  😉
I dread rewrites…but they are a constant plague to me.  As I learn new tricks I can’t help but want to go back to old code and update it…then…as in kung fu…I start seeing how the pieces go together differently and realize I could do something a lot more elligant and poof…rewrite.  This time, however, something else caused the need for a rewrite and I just took advantage of the situation…honestly I’m glad I did.  I’m waaay more happy with wonder app 2.0 and I think my customers will really dig the new hotness.
The wonder app is actually a collection of projects.
  • Sql Database for storage
  • Linq to Sql classes for CRUD operations to the database
  • Reference DLL which wraps Linq to Sql class into Models that can be used in UI elements without causing extra network traffic
  • MVC Read Only Website which references the DLL to get database access
  • WPF app which references the DLL to get database access

I rewrote all of it.  There was a bit of cutting and pasting from wonder app 1.0…but really I learned so many new tricks and had an epiphony on how to more logically structure the model classes by using extensions…and I can honestly say the code in 2.0 looks very little like 1.0 now…so that’s more or less why I’m calling it a rewrite.  The nice thing is it’s soooo much more smooth now.  Binding “magic” in WPF is pretty slick I gotta say.  🙂

So yeah…in the coming days I’ll be writing up some posts about a few issues I happened across whilst doing the rewrite…

  • Sql Db Design
  • Linq to Sql
  • Telerik WPF UI controls
  • Binding in WPF
  • Probably a few more as I review my code tomorrow…

Looking forward to sharing the experience…but for now, I sleep!  ZZzzzzzzz

 

…aaaaaand done…