#29: Moving to Elixir Part 5

Now that we have our interface working, we need to handle our inventory. Specifically we need to figure out how to create, update, and remove cars from our site.

Every day a new CSV with information about the cars available for sale is uploaded to a website. This can include things like updated pricing and descriptions of existing cars, as well as completely new cars for sale. We need to fetch that CSV and use it to update our inventory.

Let’s get started.

We’ll open our editor and create a new directory in lib/dealership called inventory_sync. Here we can house any logic that’s related to syncing our inventory with what’s provided on the CSV. The first step will be to fetch the CSV and write it to a file locally.

Let’s create a new module in inventory_sync called downloader.ex.

This module will have a single public function called download! that we can call.

Now this function will need to do two things. First it will fetch the CSV data from a URL. Then once we have that data, we’ll write it a new CSV.

You’ll notice that we’re ending our function name with a !. This is to indicate that it will raise an error if something goes awry.

It will take two arguments - the URL that we want to fetch the CSV data from. And the filename that we want to save the data to locally.

Now let’s write out what we want our function to do. Let’s start by taking the URL of the CSV. And we’ll pipe it into a function that fetches the data - csv_data!. Then we’ll take our CSV data and write it to a file locally.

With that let’s go ahead and implement our csv_data! function. Here we need to make a request, parse out the body of our response, and then return it.

There are a lot of great Elixir HTTP clients out there, but let’s take a different approach and instead of bringing in another dependency, let’s use Erlang’s httpc module.

Let’s go to the command line and open up an iex session to see how it works.

Let’s first set the URL we want to use. Since we’re using the Erlang httpc module, we need to create our URL as a charlist. In Elixir we create a charlist using single-quotes. We want this to be a get request. And we’ll use the body_format: :binary so we can get the body back as a string.

And we get an error. We need to start it with :inets.start. Also, since our URL is https, we need to run :ssl.start

And if we make our request again - it works:

$ iex
> url = 'https://elixircastsio.github.io/inventory/incoming/new_cars.csv'
> :inets.start
> :ssl.start
> :httpc.request(:get, {url, []}, [], [body_format: :binary])

We can see some info about the request, and our CSV data. Now that we know the format of the response, let’s match against it.

We’ll go back to our downloader.ex module and create another private function to do the request. We’ll call it fetch_csv_data and it will take our url. And let’s copy over the request we used in iex.

Now let’s go to our csv_data! function and call fetch_csv_data. And Let’s use it in a case statement. Our first case will match if the request was a success. And we’ll get our body and then return it. Then let’s use an underscore to match against all other cases. And then we’ll raise an error.

Now we need to define our new CsvRequestError. At the bottom of the file let’s create a module named CsvRequestError

And we’ll use defexception to define our exception. Let’s also give it a message. With that let’s implement our write_csv! function.

It will take our data returned in csv_data! and let’s also have it take the filename we want to write it to. And then we’ll use File.write! to write our fetched data to a new file.

With that let’s make a few changes to download!. And we’ll pass in our filename to write_csv!

Let’s also return our filename at the end of the function. Now let’s test that our module is working.

lib/dealership/inventory_sync/downloader.exdefmodule Dealership.InventorySync.Downloader do

  def download!(url, filename) do
    url
    |> csv_data!()
    |> write_csv!(filename)

    filename
  end

  defp write_csv!(data, filename) do
    File.write!(filename, data)
  end

  defp csv_data!(url) do
    case fetch_csv_data(url) do
      {:ok, {{_, 200, _}, _headers, data}} ->
        data
      _ ->
        raise CsvRequestError
    end
  end

  defp fetch_csv_data(url) do
    :httpc.request(:get, {url, []}, [], body_format: :binary)
  end
end

defmodule CsvRequestError do
  defexception message: "Unable to fetch inventory data"
end

To do that, we’ll create a custom mix task. At first it will just download our CSV, but when we’re finished we’ll be able to run it to fully sync our inventory.

To create a mix task, we need to have 3 things.

First, our module name for the task needs to start with Mix.Tasks. It needs to use Mix.Task. And finally, it needs to define a function named run.

Let’s create our task. Inside /lib let’s create a new directory mix and then another directory tasks.

Then let’s create a file for our new task: dealership.importer.ex

Then we’ll define our module Mix.Tasks.Dealership.Importer and we’ll use Mix.Task in this module to create a Mix task.

Now we can define our run function that is invoked when the task is ran. run receives a list of arguments from the command line, but we’ll ignore them here since we won’t use any.

Then inside the function we can call Downloader.download! with our url and the filename we want to write our inventory data to. And let’s set our url and filename as a module attributes.

Then let’s make our task public and give it a description by including the shortdoc module attribute. Finally let’s alias our Downloader module.

lib/mix/tasks/dealership.importer.exdefmodule Mix.Tasks.Dealership.Importer do
  use Mix.Task
  alias Dealership.InventorySync.Downloader

  @shortdoc "Syncs inventory"
  @url 'https://elixircastsio.github.io/inventory/incoming/new_cars.csv'
  @filename "new_inventory.csv"

  def run(_args) do
    Downloader.download!(@url, @filename)
  end

end

Then let’s go to the command line and compile our app:

$ mix compile

And if we run $ mix help we can see our new task is listed along with with our description of the task.

$ mix help
…
mix dealership.importer # Syncs inventory

Let’s give it a go and run our task:

$ mix dealership.importer

And if we look at our files we see our new file new_inventory.csv has been created.

Now that our downloader is working, let’s turn our attention to importing our new car listings from it. In our inventory_sync directory let’s create a new file importer.ex.

And inside our module, let’s give it a public function - sync_inventory! - that we can call. This function will take the filename of our newly written CSV.

Now we need to determine how we want to update our inventory.

Let’s keep it simple. First we’ll deactivate any sold inventory, which is any listing that’s not included in our CSV. Then we’ll create or update our inventory based on the contents of the CSV.

Let’s create a new function deactivate_sold_inventory! which will take our filename. In this function we’ll need to do a few things. First we’ll want to parse our CSV and grab all the vins from it. Any vin included will be an ‘active’ listing.

Once we have the active vins, we’ll query to get any car that’s listed for sale, but is not included in our active vins. These will be our recently sold cars.

And once we have them we can update their car_isListed column to deactivate them. Let’s start by getting the active vins.

Since our file is a CSV, let’s bring another package into our application to parse our CSV, making it easier to work with.

We’ll use the CSV package.

Let’s open up our ‘Mixfile’ and add the new dependency.

mix.exs

defp deps do
[
…
{:csv, "~> 2.0"},
…
]
end

Then let’s download our new dependency.

$ mix deps.get

We can take our filename and pipe it into File.stream!. This will create a stream that we can then pipe into CSV.decode!.

Let’s also set the header option to true, which will transform a row of data to a map with the column names as the keys.

In fact, let’s open an iex session and see what a row of data will look like.

$ iex -S mix
> "new_inventory.csv" |> File.stream!() |> CSV.decode!(headers: true) |> Enum.take(1)

And it returns a map that we can easily access. Let’s go back to our module and since we need collect our vins, we’ll add Stream.map. We’ll pass in each row of data and fetch the "car_VIN" from it. And since this is a stream - the computation is only performed when we call a function from the Enum module. So let’s add Enum.to_list. This will return a list of all our active vins.

With that we can build our query to get the cars we need to deactivate. Let’s create another pipeline starting with our Car and we’ll pipe it into Car.for_sale to get all are cars that are currently for sale.

Since Ecto queries are composable, we can take the query it generates and pipe it into another query that will get all listed cars NOT contained in the active vins.

Let’s create another function to do this in our car.ex module. We’ll create a function named not_included that will take a query and our active vins.

Then we’ll define an Ecto query to get all cars where their vin is not included in the active vins.

lib/dealership/listings/car.ex…
  def not_included(query, active_vins) do
    from car in query,
    where: not(car.car_VIN in ^active_vins)
  end
…

Now we can go back to our importer and add Car.not_included passing in our active vins.

And since we are returning our completed query, we’ll pipe it into Repo.update_all which we can use to update all the records in our query. We’ll set their car_isListed to the string "N".

Then we’ll return the filename at the bottom of the function. Now that we’re using our Repo and Car modules, we’ll need to include aliases for them.

With that let’s now add a function that will handle creating and updating our listings. Let’s define a new function named import_inventory! and let’s also pass in our filename to it.

Just like in deactivate_sold_inventory! we’ll pipe our filename into File.stream! and the result of that into CSV.decode! and we’ll set the headers to true here as well.

Now that we’re getting our data back, let’s pipe it into another function that will handle creating and updating our cars.

But instead of creating that function in this module, let’s implement it in our Lisitngs module. We’ll call Stream.map, passing each row of data into Listings.create_or_update_car(params).

Now let’s create our new function. We’ll open our listings.ex module. And we’ll define our new function.

Now our function needs first lookup a car from the database based on the params passed in. If it exists we’ll update it with the new params. Otherwise we’ll create a new record in the database.

Let’s take our params and since a car’s VIN is unique, we can query off that. We’ll grab the vin from the params with params["car_VIN"]

Then let’s use Repo.get_by to lookup a car by its vin. Now this will return a car struct if the car was found and nil if we don’t have a car with that vin.

Let’s use a case statement to match against these two cases. If nil is returned, let’s return a car struct with the vin.

And if a car is found we’ll return it.

Now we’ll need a way to cast and validate our car records when we write them to the database. We can do that with a changeset.

We don’t currently have a changeset function to use, so let’s create one.

Let’s open our car.ex module.

Then we’ll create a new function named changeset. We’ll pattern match to ensure the first argument is a %Car{} struct, the second argument will be our attributes.

We’ll take our car and pipe it into Ecto.Changesets cast function. It will take our attributes, and then the attributes we want to allow.

Let’s go ahead and set our allowed attributes as a module attribute.

Then let’s require the year, make, model, and vin. We can do that with the validate_required function. So let’s pipe the result of our cast function into validate_required and then we’ll give it a list of the attributes we want to require. Then let’s go to the top of our module and import Ecto.Changeset. We’ll also need to alias our car module.

lib/dealership/listings/car.exdefmodule Dealership.Listings.Car do
  use Ecto.Schema
  import Ecto.Query, only: [from: 2]
  import Ecto.Changeset
  alias Dealership.Listings.Car

  @allowed_attrs [:car_Stock,
                  :car_VIN,
                  :car_Year,
                  :car_Make,
                  :car_Model,
                  :car_Body,
                  :car_Trim,
                  :car_Doors,
                  :car_ExteriorColor,
                  :car_InteriorColor,
                  :car_EngineCylinders,
                  :car_EngineDisplacement,
                  :car_Transmission,
                  :car_Miles,
                  :car_SellingPrice,
                  :car_Certified,
                  :car_Description,
                  :car_Options,
                  :car_Engine_Description,
                  :car_Transmission_Speed,
                  :car_Transmission_Description,
                  :car_Drivetrain,
                  :car_Fuel_Type,
                  :car_CityMPG,
                  :car_HighwayMPG,
                  :car_PassengerCapacity,
                  :car_isFeatured,
                  :car_isReduced,
                  :car_isListed,
                  :car_Carfax_One_Owner,
                  :image_list]
…
  def changeset(%Car{} = car, attrs) do
    car
    |> cast(attrs, @allowed_attrs)
    |> validate_required([:car_Year, :car_Make, :car_Model, :car_VIN])
  end
…
end

With that let’s go back to our listings.ex module and pipe the result of Repo.get into our new changeset function, along with its params.

Then we can pipe that into the Repo.insert_or_update! function. And just like its name indicates, this will either update our record if it exists or create a new one if it doesn’t.

Now let’s do a little cleanup and move our logic to lookup car into its own function. Let’s create a new private function get_car_struct and it will take our params. And we’ll move the case statement into it.

Then we’ll update our create_or_update_car function to use the new function. And let’s go down to our get_car_struct function and pull our vin out into it’s own variable.

Now since all the updating and creating of cars will be handled by this function, we can remove the unused functions from this module.

We’ll remove the create, update, delete, and change functions.

lib/dealership/listings/listings.exdefmodule Dealership.Listings do
  @moduledoc """
  The Listings context.
  """

  import Ecto.Query, warn: false
  alias Dealership.Repo

  alias Dealership.Listings.Car

  def create_or_update_car!(params) do
    params
    |> get_car_struct()
    |> Car.changeset(params)
    |> Repo.insert_or_update!()
  end

  defp get_car_struct(params) do
    car_vin = params["car_VIN"]

    case Repo.get_by(Car, car_VIN: car_vin) do
      nil ->
        %Car{car_VIN: car_vin}
      car ->
        car
    end
  end

  def list_cars(params) do
    Car
    |> Car.for_sale()
    |> Repo.paginate(params)
  end

  def get_car!(id), do: Repo.get!(Car, id)

end

Now we’ll go back to our importer.ex module. And while calling our new function like this will work, we can actually shorten it by using the & - or capture operator - and the name of the function with its arity.

Again, since this is a stream, we’ll need to add another call to an Enum module. Let’s add Enum.to_list.

Then we’ll update our alias to include the Listings module.

Let’s also update our sync_inventory! function to take our filename and pipe it into our deactivate_sold_inventory! function, which we can then pipe into import_inventory!.

lib/dealership/inventory_sync/importer.exdefmodule Dealership.InventorySync.Importer do
  alias Dealership.Listings.Car
  alias Dealership.{Repo, Listings}

  def sync_inventory!(filename) do
    filename
    |> deactivate_sold_inventory!()
    |> import_inventory!()
  end

  defp import_inventory!(filename) do
    filename
    |> File.stream!()
    |> CSV.decode!(headers: true)
    |> Stream.map(&Listings.create_or_update_car!/1)
    |> Enum.to_list()
  end

  defp deactivate_sold_inventory!(filename) do
    active_vins = filename
                  |> File.stream!()
                  |> CSV.decode!(headers: true)
                  |> Enum.map(fn(row) -> Map.fetch!(row, "car_VIN") end)

    Car
    |> Car.for_sale()
    |> Car.not_included(active_vins)
    |> Repo.update_all(set: [car_isListed: "N"])

    filename
  end
end

Now let’s go back to our importer task and update it to update our inventory with our new module. We’ll first alias our new Importer module.

Then we’ll update our run function to call it. Let’s create a new pipeline that starts with our URL, which we’ll pipe into Downloader.download! with the filename.

With our file downloaded and the filename returned, we can pipe it into Importer.sync_inventory!

lib/mix/tasks/dealership.importer.exdefmodule Mix.Tasks.Dealership.Importer do
  use Mix.Task
  alias Dealership.InventorySync.{Downloader, Importer}

  @shortdoc "Syncs inventory"
  @url 'https://elixircastsio.github.io/inventory/incoming/new_cars.csv'
  @filename "new_inventory.csv"

  def run(_args) do
    @url
    |> Downloader.download!(@filename)
    |> Importer.sync_inventory!()
  end

end

Let’s do one other piece of cleanup.

We’ll open our car_controller.ex and remove our unneeded Car alias. And un-needed new, create, edit, update and delete functions.

lib/dealership_web/controllers/car_controller.exdefmodule DealershipWeb.CarController do
  use DealershipWeb, :controller

  alias Dealership.Listings

  def index(conn, params) do
    {cars, kerosene} = Listings.list_cars(params)
    render(conn, "index.html", cars: cars, kerosene: kerosene)
  end

  defp id_from_slug(slug) do
    slug
    |> String.split("-")
    |> List.last()
  end

  def show(conn, %{"id" => slug}) do
    car = slug
          |> id_from_slug()
          |> Listings.get_car!()
    render(conn, "show.html", car: car)
  end
end

Now let’s go to the command line and compile our app.

$ mix compile

And then run our importer task

$ mix dealership.importer

And an error is printed. Now that we’re using our Repo in our task, we’ll need to go back to our task and ensure it’s started and running. We can do that by importing Mix.Ecto

And then adding the ensure_started function and passing it our Repo. And since we’re now using Repo in this module, we’ll need to alias it as well.

lib/mix/tasks/dealership.importer.exdefmodule Mix.Tasks.Dealership.Importer do
  use Mix.Task
  alias Dealership.Repo
  alias Dealership.InventorySync.{Downloader, Importer}
  import Mix.Ecto

  @shortdoc "Syncs inventory"
  @url 'https://elixircastsio.github.io/inventory/incoming/new_cars.csv'
  @filename "new_inventory.csv"

  def run(_args) do
    ensure_started(Repo, [])

    @url
    |> Downloader.download!(@filename)
    |> Importer.sync_inventory!()
  end

end

We’ll go back to the command line and recompile our app

$ mix compile

And run our importer

$ mix dealership.importer

Great our importer runs and updates all our inventory. We can now set our task to run as needed with some type of scheduler. And if we go to the browser we see our inventory has been synced with the contents of the CSV.