Today I Learned How to Setup Azure Pipelines CI

Our last EDMUG meetup was an excellent presentation about Azure DevOps. Azure DevOps reminds me of GitLab where it is more then just continuous integration (CI). It includes issues tracking, repositories, and continuous delivery. All pretty standard stuff.

However, one thing did jump out at me. The fact it had build built in images you could use to run the build on. Build images with Visual Studio pre-installed. They also have macOS X Mojave! No need to create you own build runner, either VM or Docker, like you do with so many other CI tools.

I don’t think using a 3rd party build image is the answer for everything. There are cases where I would want more control over the build image but for the example code I use for presentations the default image is good enough. No need for me to create and maintain a build image.

For this example I’m going to use my Introduction to ORM for DBAs presentation code. It’s a good project to start with as the presentation code is simple and does not have many dependencies. I also want to get an automated build working before trying GitHub’s Dependabot to auto-magically update dependencies for the presentation code.

Once I created my Azure DevOps account I then created the Introduction to ORM for DBAs project. For this new project the first thing I do is disable all the features but the Pipelines as I only need CI, not Git, bug tracking, etc.

Next I created the Pipeline.

I was prompted where the code is stored. In my case it’s stored in GitHub. I also was prompted to give Azure Pipelines access to GitHub which I did.

Now need to pick the repository and allow Azure Pipelines to access that repository.

Pipelines now presents some default builds. I picked the ASP .NET Core.

Then generates a reasonable default build file. This file will be stored in the root of you project with the name azure-pipelines.yml.

# ASP.NET Core (.NET Framework)
# Build and test ASP.NET Core projects targeting the full .NET Framework.
# Add steps that publish symbols, save build artifacts, and more:
# https://docs.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- master

pool:
  vmImage: 'windows-latest'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
- task: NuGetToolInstaller@0

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

- task: VSTest@2
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

Overall I like the defaults that where picked. Let’s examine this scrip in more details and see what it is doing. First it triggers a build on any changes to the master branch. I wonder if it will also build on pull requests?

trigger:
- master

Next it lists the image the build will be preformed on. The ‘windows-latest‘ is VS 2019 on Windows Server 2019. Works for me.

pool:
  vmImage: 'windows-latest'

Next it defines some variables to use in the actual build. I like the fact it finds all the solution files as the example project has 10 separate solutions for each of the steps.

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

Next are the actual build steps with the first being install the NuGet packages. Nothing special here.

- task: NuGetToolInstaller@0

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

After that is the step to compile. In this case compile is done using Visual Studio. I wonder why Visual Studio was picked instead of just building using .NET Core command line? Maybe so it can produce the deploy packages?

I think I can remove building the deploy packages as this build is just for an example and will never be released. For now let’s just leave it and see what happens.

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    msbuildArgs: '/p:DeployOnBuild=true /p:WebPublishMethod=Package /p:PackageAsSingleFile=true /p:SkipInvalidConfigurations=true /p:DesktopBuildPackageLocation="$(build.artifactStagingDirectory)\WebApp.zip" /p:DeployIisAppPath="Default Web Site"'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

The final step is running the tests. I don’t have any tests for my example so I’ll be removing this step but for now just leave it and see what happens with the first build.

- task: VSTest@2
  inputs:
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

Let’s try to build with the default file and see what happens.

That is no good. Digging into the error message it appears that my application uses .NET Core 2.0 but it’s not installed on the image. We can fix this by installing .NET Core 2.0 using the DotNetCoreInstaller command as our first step.

When specifying the version of .NET Core it wants the SDK version, not the public .NET Core version. In my example I’m using the out of support .NET Core 2.0 but I would like to install the latest version of it. Using this handy chart I can see it’s SDK version 2.1.202.

Let me try the build now and see what happens.

That looks better. The final couple steps is to remove the build steps we don’t need, such as tests, and also don’t bother creating the deployment packages. I also changed the image to windows-2019 instead of latest which should prevent the build from magically failing if latest changes to VS 2020 or a different version of Windows. The final build script now looks like:

# ASP.NET Core (.NET Framework)
# Build and test ASP.NET Core projects targeting the full .NET Framework.
# Add steps that publish symbols, save build artifacts, and more:
# https://docs.microsoft.com/azure/devops/pipelines/languages/dotnet-core

trigger:
- master

pool:
  vmImage: 'windows-2019'

variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'

steps:
# Install .NET 2.0.9.  Not supported anymore but
# is what we want to upgrade to too to fix the security
# issues.  After the build is working we will upgrade
# to a support .NET version.
- task: DotNetCoreInstaller@0
  inputs:
    version: '2.1.202'

- task: NuGetToolInstaller@0

- task: NuGetCommand@2
  inputs:
    restoreSolution: '$(solution)'

- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'

When this build runs there won’t be any warnings for the tests because they have been removed.

That was relatively painless. If you are curious you can find the public pipeline build here.

P.S. – For some reason I find Angels and Airwaves great programming music for getting into the zone and their latest song, Rebel Girl, is no exception.

D-d-do you wanna go back to where we started?
Back before we were broken hearted?

Posted in Today I Learned | Tagged , | Comments Off on Today I Learned How to Setup Azure Pipelines CI

Introduction to ORMs for DBAs: Part 8 – Create Game Players CRUD – Part 1

I gave the Introduction to ORMs for DBAs presentation at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the GamePlayers table.  The GamePlayers table stores who played a game and if they won or lost.  In this post we will start creating the CRUD method pages for the GamePlayers table.

If you skipped the previous post but want to follow along open up 08-Create Game Players CRUD. Then run the migrations to create the Player, Games, GameResultTypes, GamesPlayed, and GamePlayers tables in the database.  Your database should be similar to the below screen shot.  The migration IDs can be different but the tables should exist.

Previously when adding CRUD methods we used commands to create scaffolding for index, create, view, edit, and delete pages. This makes sense when you have a simple model, such as players or games and don’t require a fancy interface. For games a player has won or lost it would be nice to have a fancier interface. Actually, it would be nice if you could edit the players that took part in a game in the Games Played page. Some like:

We can’t use scaffolding to build this interface, we have to do it the old fashioned way. We will keep our workflow simple. When a user choose the create a new Game Played we take them to the Game Played Create page where they can enter the game and date it was played.

When the user clicks the Create Button instead of redirecting them to the Games Played Create Page we will direct them to the Gamed Played Edit page where they can add players to the game and who won or lost. To do this we need to open up the GamesPlayedController and find the Post Create method which looks like the below.

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,GameId,DatePlayed")] GamePlayed gamePlayed)
{
    if (ModelState.IsValid)
    {
        _context.Add(gamePlayed);
        await _context.SaveChangesAsync();
        return RedirectToAction(nameof(Index));
    }
    ViewData["GameId"] = new SelectList(_context.Games, "Id", "Name", gamePlayed.GameId);
    return View(gamePlayed);
}

Update the redirect so it goes to the Edit page instead of the Index. Instead of:

return RedirectToAction(nameof(Index));

Change it too:

return RedirectToAction(nameof(Edit), new {id = gamePlayed.Id)};

Test by running the application and clicking on the button and you should be redirected.

That works. The next step is let the user enter some players as outlined in our mockup at the top of this post. Let’s start by adding the controls to add players to an existing game. Do this by opening the Games Played Edit page (Views/GamesPlayed/Edit.cshtml) and adding the following:

<div class="col-md-4">
        <form asp-action="CreateGamePlayer">
            <input type="hidden" id="GamePlayedId" name="GamePlayedId" value="@Model.Id">
            <div class="form-group">
                <label class="control-label">Player</label>
                <select id="PlayerId" name="PlayerId" class="form-control" asp-items="ViewBag.Players"></select>
            </div>
            <div class="form-group">
                <label class="control-label">Results</label>
                <select id="GameResultTypeId" name="GameResultTypeId" class="form-control" asp-items="ViewBag.GameResults"></select>
            </div>
            <div class="form-group">
                <input type="submit" value="Add Player" class="btn btn-default">
            </div>
        </form>
    </div>

If you run the application now you will see the controls but no data in the drop-down lists. Also the player controls will be on the left when we want the on the right. That will be fixed later.

Lets get some data in the drop-downs. If you look at the code in the Games Played Edit there are placeholders for the drop-down data in the ViewBag called ViewBag.Players and ViewBag.GameResults. To get data into the ViewBags open up the GamesPlayedController and find the Get Edit method.

Notice we already load data for the Game drop-down? It’s this line:

ViewData["GameId"] = new SelectList(_context.Games, "Id", "Name", gamePlayed.GameId);

Let’s add a couple more lines to load the data for the Player and Results drop-downs. If we where writing SQL statements the queries would be simple. Get all the players and all the game result types (i.e. win, lose, etc).

Select * From Players;
Select * From GameResultTypes;

Since we are using Entity Framework ORM we write a query to get all records from a table as:

_context.Players

We combine the ORM lookup with a helper method to populate the drop-down and we get:

ViewData["Players"] = new SelectList(_context.Players, "Id", "Name");
ViewData["GameResults"] = new SelectList(_context.GameResultTypes, "Id", "Type");

Now when you run the app the drop-downs should be populated.

The Add Player button still does nothing so lets fix that. Notice the in code we added to Games Played Edit view there is a form tag. In the form tag is the action that will executed when the Add Player button is clicked. In this case the CreateGamePlayer method will be called on the GamesPlayedController. The button does nothing because that method does not exist yet so lets create it.

[HttpPost]
public async Task<IActionResult> CreateGamePlayer([Bind("GamePlayedId,PlayerId,GameResultTypeId")] GamePlayer gamePlayer)
{
    _context.Add(gamePlayer);
    await _context.SaveChangesAsync();

    return RedirectToAction(nameof(Edit), new { id = gamePlayer.GamePlayedId});
}

The Bind("GamePlayedId,PlayerId,GameResultTypeId")populates the gamePlayer argument with only the listed arguments. That prevents malicious users from submitting parameters we might not want, such as the ID field.

The first two lines of the method insert the new GamePlayer record. The first statement queues new GamePlayer record to inserted.

_context.Add(gamePlayer);

The second line saves all the changes contained in the context. In this case it’s just the new GamePlayer record.

await _context.SaveChangesAsync();

The final line redirects us back to the Games Played Edit page.

return RedirectToAction(nameof(Edit), new { id = gamePlayer.GamePlayedId });

Try it out and make sure new results are added to the database. Since we currently don’t show adding new game players on the page we need to check the database.

This post is getting long and this seems like a good place to stop. In the next post we will finishing creating the user interface and CRUD methods.

If you got stuck you can find completed Part 8 here.  If you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 8 – Create Game Players CRUD – Part 1

My Takeaway from Reading When

When: The scientific Secrets of Perfect Timing

Book: When: The Scientific Secrets of Perfect Timing
Author: Daniel H. Pink

Any book that promotes napping is a win for me. These days it’s easy for me to catch a couple ZZZs as I work from home. When I was an employee or working at a clients site I would sometimes have an “errand” to run in the afternoon. An “errand” that involved driving to secluded place, tilting the seat back, and inspecting the inside of my eyelids for 20 minutes.

Aside from taking more naps my main takeaway from this book is to force start, middle, and ends. At least the good parts of starting, ending, and reaching the half-way points. You know, the old advice about breaking up a long project or task into smaller bits. Not something I didn’t already know but still good to be reminded of.

When also brought up the importance of the middle. Not just middle life but also the middle of a project or a task. Acknowledging the middle could be useful when you are stuck but it does not make sense to force and end/start. According to When most of the work on task gets done after the halfway mark and this holds true for short or long tasks.

Finally the last thing from When I’ll try to incorporate into my life is starting something important on good start days. Days like the start of the month, start of the week, after a holiday, anniversary of a life event, etc.

Posted in Takeaways | Tagged , | Comments Off on My Takeaway from Reading When

Thank you for Attending my SQL Saturday 840 Presentation – Create a Time Travelling Database

Thank you to everyone who attended my SQL Saturday 840Create a Time Travelling Database” presentation. I enjoyed your questions and the discussion after the presentation.

If you have any further questions feel free to reach out to me at chris.cumming@saturdaymp.com or @saturdaymp. I can also be found on the DevEdmonton and Legacy Code Rocks Slack channels.

Multiple Timelines

More resources about temporal databases:

P.S. – It was toss up between the below song and Rocky Horror Picture Show Time Warp. Guess I’m in a melancholy mood on this snowy day. Yes it is snowing on April 30th in Edmonton.

But there never seems to be enough time
To do the things you want to do, once you find them
I’ve looked around enough to know
That you’re the one I want to go through time with

Posted in Uncategorized | Comments Off on Thank you for Attending my SQL Saturday 840 Presentation – Create a Time Travelling Database

Introduction to ORMs for DBAs: Part 7 – Create GamePlayers Table

I gave the Introduction to ORMs for DBAs presentation at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the GamesPlayed table and CRUD methods.  The GamesPlayed table stores when a game was played but it does not store who played the game.  In this post we will create the GamePlayers table to store who played the game and, more importantly, who won.

If you skipped the previous post but want to follow along open up 07-Create Game Players Table. Then run the migrations to create the Player, Games, GameResultTypes, and GamesPlayed tables in the database.  Your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

In this post we will create the last table the GamePlayers. It’s the table that tracks which players where involved with a game and if they won or loss.

Game Tracker ERD

As usual we start with the model. The only thing different about this model is it joins three other tables so had three foreign keys. Remember to add a foreign key in Entity Framework you add the ID and the Class.

using System.ComponentModel.DataAnnotations.Schema;

namespace SaturdayMP.GameTracker.Models
{
    [Table("GamePlayers")]
    public class GamePlayer
    {
        public int Id { get; set; }

        public int GamePlayedId { get; set; }
        public GamePlayed GamePlayed { get; set; }

        public int PlayerId { get; set; }
        public Player Player { get; set; }

        public int GameResultTypeId { get; set; }
        public GameResultType GameResultType { get; set; }
    }
}

You also need to add the other side the foreign key to the existing models. First the GamePlayed model:

public ICollection<GamePlayer> GamePlayers { get; set; }

Then the Game model:

public ICollection<GamePlayed> GamesPlayed { get; set; }

Finally the GameResultType model: ….Actually we won’t add the reference relationship to the GameResultType. This is to prevent a common mistake with ORMs where we accidentally load a bunch of records.

Say we did add the GameResultType->GamePlayer then in our code called that relationship:

myGamePlayed.GamesPlayed.Count

The above would load all the games played for the given type. Not a problem when your application is young and you only have a couple of games played. What happens when you have hundred or thousands of games played? Then it becomes a problem.

When we do need to filter by the game results, such as who won, we would write it from the perspective of the player. For example:

var wins = _context.Players
  .Where(p =&gt; p.Id == 1)
  .Select(p =&gt; new
  {
    PlayerName = p.Name,
    Wins = p.GamesPlayers.Where(gp => gp.GameResultType.KeyCode == 10).Count(),
  })
  .First();

With that out of the way, let us get back to coding and add the new model to the context:

public DbSet<GamePlayer> GamePlayers { get; set; }

Now that the models are setup we can create the migration:

dotnet ef migrations add CreateGamePlayersTable

Check the migration file to make sure it looks reasonable:

Now that the migration exists and looks correct we can run the migration on the database. Once the migration is complete you should be view the new GamePlayers table in the database.

dotnet ef database update

Good work. That was our most complicated table yet. This seems like a good time to stop and take a break. In the next post we will create the Game Players CRUD methods.

If you got stuck you can find completed Part 7 here.  If you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Code Examples, Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 7 – Create GamePlayers Table

My Takeaway from Moneyball

Moneyball cover.

Book: Moneyball: The Art Of Winning An Unfair Game
Author: Michael Lewis

Both the movie and book are great and truth be told I watched the movie well before reading the book. In both cases they do a great job of showing how statics can find undervalued baseball players. Lots of time spent focusing on what stats actually win baseball games such as the famous “he gets on base” quote.

However, that was not my takeaway from reading this book. I already know I should take actions that, statistically speaking, maximize my chances of a positive outcome. The problem is that is easier said then done. Statics only work if you are consistent over time and emotions are not consistent.

So my takeaway from Moneyball is more a reminder. Don’t let emotions derail my long term plans. Stick with actions that, statically speaking, that are likely to lead to positive outcomes and minimize negative outcomes. Don’t let one negative outcome derail my long term plan. Play the long game.

Posted in Takeaways | Tagged , | Comments Off on My Takeaway from Moneyball

Today I Learned How to Create a React-Rails App

A working example can be found here in the Saturday MP Examples GitHub.

First thing you need to do is create a basic Rails app as outlined in my previous post.  My setup is the same as creating Rails app: Ubuntu 18.04 LTS host using Docker to containerize my development environment.

Once your basic Rails app is up and running you can add React.  This example uses React-Rails.  First, you need to update the Docker file to install Node JS and Yarn.  Open up the DockerFile and change it so it looks like the below.

FROM ruby:2.5.3

# To install a later version of Node JS and Yarn.
RUN curl -sL https://deb.nodesource.com/setup_10.x | bash -
RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list

# Install the needed software.
RUN apt-get update -qq &amp;amp;&amp;amp; apt-get install -y build-essential libpq-dev nodejs yarn

# Create the website folder and map the Gemfiles.
RUN mkdir /website
WORKDIR /website
COPY Gemfile /website/Gemfile
COPY Gemfile.lock /website/Gemfile.lock

# Update the bundler then install the gems.
RUN gem install bundler
RUN bundle install

# Copy our files to the website.
COPY . /website
Changes to Docker file.

Actually only the top of the file is changed, the bottom is the same just with better layout and comments.  The top we add a reference to the Node and Yarn repositories then added nodejs and yarn to the install list.

Next add the required Gems: webpacker and react-rails:

# React
gem 'webpacker'
gem 'react-rails'

Now that the Docker and Gemfile is updated we can rebuild the container:

docker-compose build web
Rebuilding the Docker container with Node and Yarn.

A couple final steps.  Run the newly build container and run the following commands:

docker-compose run web bash

rails webpacker:install
rails webpacker:install:react
rails g react:install
Installing Webpacker.
Installing Webpacker React.
Generating react.

The Rails app should be React ready.  To smoke test we need a view and controller.  If you already have a page in your app you can skip this step.

Generate the home controller by executing the following:

rails g controller Home index

Generate home controller and view.

Then generate a basic React component:

rails g react:component HelloWorld greeting:string
Generate React Hello World component.

Then add the following line to the application layout file:

<%= javascript_pack_tag 'application' %>
Add React application to layout.

Finally add the following line to the home view:

<%= react_component("HelloWorld", { greeting: "Hello from react-rails." }) %>
Add React component to Home page.

Now run the add and navigate to the http://localhost:3000/home/index and you should see the React message:

Greetings from React message.
In

In memory of Walk of the Earth’s Beard Guy I present one my favourite songs Walk of the Earth songs:

You gotta hold on to what you got, babe
It ain’t always greener on the other side, you know
We ain’t rich but we’re worth a lot, babe
I wanna see the world with your hand in mine, you know

Posted in Code Examples, Software Development, Today I Learned | Tagged , | Comments Off on Today I Learned How to Create a React-Rails App

Happy Holidays!

Saturday MP (i.e. Chris and Ada) are taking some time off over the holidays to visit with family, friends, and spending a day not changing out of our pyjamas and playing games all day.  We will be back to our regularly scheduled programming on January 2nd, 2019.

Happy holidays and all the best in 2019!

Old Timey Record Player

P.S. – My daughter wanted a record player for Christmas.  I know, everything old is new again.  Lucky for my daughter she got the above old timey looking record player as an early Christmas gift when her Aunt and family where visiting this weekend.  Not only does it play records but is also plays tapes, CDs, radio, and Bluetooth.  Now we just need to get some records.

In honour of the great gift I present my daughters 2nd favourite Christmas song.

Posted in Fun | Tagged | Comments Off on Happy Holidays!

Today I Learned how to Generate a ERD for Rails Application

Back in the day Rails apps didn’t add foreign keys to database tables.  Believe it or not this was a feature not a bug.  The idea was that you shouldn’t “repeat” the relationship between you models in the code and in the database.  It wasn’t until Rails 4.2 that support was added for foreign keys.  

As you can probably guess I think this was a mistake.  If you have worked with a database without any foreign key constraints you quickly run into data integrity issues.  I don’t care how careful you think you are you will eventually screw up.  If not you then another person or application will corrupt your data.  Line items not attached to an order, address without customers, cats and dogs living together, etc.

It’s also harder to use tools on a database without foreign keys.  Reporting tools often relay on foreign keys to help the user create queries and display data.

Finally without foreign keys it’s hard to see how tables are related to each other.  You can’t just generate a ERD.  While you can, but you end up with just a bunch of tables with no idea how they are related to each other.  This is the problem I’m currently having with a database for a Rails 3.2 application.  How can see the relationships between the tables?

What I need is a tool that can read the relationships between the models in the application.  Lucky for me this tool exists and is called Rails-ERD.

You can find the install instructions here.  In my case I had a Rails 3.2 application running in a Docker container but that was client code that I can’t show here.  Instead my example will be the Saturday MP website which is Rails 4 with foreign keys but will work fine for this example. 

My website also uses Docker.  If you don’t use Docker then just run the commands on you local machine.  First step is to update the Dockerfile to install Graphviz.

Then add the rails-erd gem to the Gemfile.  Just add it to development as we don’t need it for tests and production.

Then rebuild the container to install Graphviz and the Rails-ERD gem.

Since we are using Docker we first need to spin up our container then ran the rake command to create the ERD.

docker-compose run web bash up

rake erd

This will create a PDF version of your ERD.  If you have a small ERD, like my website, then you are done. 

If you have a larger ERD, like the client code I can’t show you, then you are not done as the ERD will be unreadably small if you print it.  Printing things is something old men with bad eyes like to do.  What you can do is break up the database into sections.  For example, we could just show the product part of my database with the following command:

rake erd filename="products_erd" title="Saturday MP Products ERD" only="Product,ProductVersion"

What I like to do is create a batch file that will generate all the ERDs and but it in the databases folder.  Then you can run this batch file whenever you update the database to generate updated ERDs.

#!/usr/bin/env bash
rake erd filename="erd" title="Saturday MP ERD"
rake erd filename="products_erd" title="Saturday MP Products ERD" only="Product,ProductVersion"

Thanks for Voormedia for creating Rails-ERD and sharing it.

P.S. – About a month ago we saw The Once live and they sang the below song.  Actually, they got the the audience to sing the “By the glow of the kerosene light” part.  Weirdly it got really dusty in the theater at the end of the song because everyones eyes where watery, including the bands.

And sometimes love bloomed and sometimes dreams die
By the glow of the kerosene light.

By the glow of the kerosene light.

Posted in Code Examples, Software Development, Today I Learned | Tagged , , , | Comments Off on Today I Learned how to Generate a ERD for Rails Application

Introduction to ORMs for DBAs: Part 6 – Create GamesPlayed Table and CRUD

I gave the Introduction to ORMs for DBAs presentation at SQL Saturday 710 but it didn’t go as well as I would have liked (i.e. I had trouble getting my demo working).  Since the demo didn’t work live I thought I would show you what was supposed to happen during the demo.  You can find the slides I showed before the demo here.

The goal of this demo is create a basic website that tracks the games you and your friends play and display wins and losses on the home page.  I used a MacBook Pro for my presentation so you will see Mac screen shots.  That said the demo will also work on Windows.

In the previous post we created the Games Results Type table and CRUD methods.  This table stores the possible outcomes of a game (i.e. win, lose, or tie).  In this post we will create the Game Played Table and CRUD.  This table tracks when a game was played but does not track who played the game.  More on that later.

If you skipped the previous post but want to follow along open up 06 – Create GamesPlayed Table and CRUD.  Then run the migrations to create the Player, Games, and GameResultTypes tables in the database.  Your database similar to the below screen shot.  The migration IDs can be different but the tables should exist.

Part 6 Initial Database

After completing Part 5 our database has the edge tables completed (Players, Games, and GameResultTypes) but none of the central tables (GamesPlayed and GamePlayers).  Lets start with our second most important edge table, the GamesPlayed table.

Game Tracker ERD

As usual we will start by creating the model.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace SaturdayMP.GameTracker.Models
{
    [Table("GamesPlayed")]
    public class GamePlayed
    {
        public int Id { get; set; }

        public int GameId { get; set; }
        public Game Game { get; set; }

        public DateTime DatePlayed { get; set; }
    }
}

This should look similar to the other models we have created so far with one minor difference.  This model is part of a one-to-many relationship with the Games table.  One game can be played many times. 

The below line is what links our new model to the Games table.  It says a GamesPlayed record belongs to a Game record.

public int GameId { get; set; }
public Game Game { get; set; }

Entity Framework, and most other ORMs, are smart enough to correctly map a reference to another model as a foreign key.

This relationship goes both ways and the relationship needs to be added to the Games table as well.  In this case it says a Game record can have many GamesPlayed records which is why a list is used.

public ICollection<GamePlayed> GamesPlayed { get; set; }

With the model created lets generate the migration.  This command should be familiar by now if you have been following along since the beginning.

dotnet ef migrations add CreateGamesPlayedTable

Checking the created migration we can see the foreign key created.

Run the migration on the database to create the new table.

dotnet ef database update

Next lets create the CRUD.

dotnet aspnet-codegenerator controller -name GamesPlayedController -outDir Controllers -m GamePlayed -dc GameTrackerContext -udl

Remember if the files don’t appear in the solution you might need to close the solution and re-open it.

To see our new CRUD methods we need to add the menu item to Layout.cshtml file.

<li><a asp-area="" asp-controller="GamesPlayed" asp-action="Index">Games Played</a></li>

Add Games Played Menu to Layout

Now lets run the application and navigate to the Games Played page.  Assuming you have a couple games created you should see something similar to the below.  If you don’t have any Games created, go to Games page first and add a couple.

Games Played Page with Game ID

Notice that the Game is linked to the Game ID?  That is not very user friendly.  We should show the name of the game instead. 

Lets start with the Index page.  Open up the page can change the following line so it references the name of the game instead of the ID. 

NOTE: you don’t need to stop running the application.  After your change you can just reload the page in your browser and see you changes.  Just make sure you have saved your changes you might have to hold down the Shift key when reloading the browser.

@Html.DisplayFor(modelItem => item.Game.Name)

That looks better.  Next up the Create page.  If we open up the view we don’t see a reference to the GamesPlayed object.  Instead we see a ViewBag.  This ViewBag contains a list of Games that we want to populate the dropdown with.

To show the game name in the create view we need to make changes to the controller.  Open up the GamesPlayedController and find the Get Create method.  Notice the creation of the ViewData?  ViewData and ViewBag are just different way of referencing the same data.  For some reason the Microsoft scaffolding decided to not be consistent.

The SelectList method is helper method to generate a list of data for populating a dropdown.  It takes a list of objects and what field you want to use as the key (DataValueField) and what field to display (DataTextField) to the user.  Currently the ID field is used as both the data and text fields.  To show the game name change the DataTextField argument, the 2nd one, to “Name”.

When you make changes to the controller you have to restart your application.  You can’t just reload the we page like we did when we made changes to the view.

Restart the application and create a new game played and it should now show the name of the game instead of the ID.

There are 3 more views that need to fixed: Details, Edit, and Delete.  In all cases the changes are similar to the ones we have done above.

To fix the Games Played Details update the view.  Change the Game.Id to Game.Name.

For the edit we need to update the SelectList to show the name but we have to do this in two spots.  The first is the Get and the second is the Post methods of Edit.

Finally we need to update the Delete page to show the name by updating view:

The Games Played section is now complete.  In the next post we will create the Game Players table.  This table will be the most complicated table so we will just create the table and save the CRUD for later.  It should also result in a smaller blog post.

If you got stuck you can find completed Part 6 here.  If you have any questions or spot an issue in the code I would prefer if you opened a issue in GitHub but you can e-mail (chris.cumming@saturdaymp.com) me as well.

Posted in Code Examples, Introduction to ORMs for DBAs, Software Development | Tagged , , , , , , | Comments Off on Introduction to ORMs for DBAs: Part 6 – Create GamesPlayed Table and CRUD