WIP, coming soon – importing data from the original Northwind Traders database to the Nortwind Elixir Traders database, and running queries on it.
(BTW, I can imagine that Northwind Traders must have been a veiled reference to or an inside joke about Windows NT.)
Hello everyone,
First off, apologies for this update taking longer than usual, even despite my recent expectation that this update would come earlier, rather than later in last week. Life got in the way, and then I also got stuck with attempting to implement a clever-and-complicated solution to one of the things in this chapter using Enum.reduce_while/3 (after learning some cool things while working on IdenticonSvg, I unsuccessfully tried to construct a dependency tree between tables).
Still, this update is a medium-large one. 37 new pages have been added, and they cover using a dynamic Ecto repository to temporarily connect to a different SQLite database, inserting data in bulk, and various bits and pieces of Elixir that aren’t directly related to Ecto, but useful when writing Elixir applications and avoiding the hard-coding of data within them, regardless, such as the :application, Application and Module modules, the Kernel.apply/3 function, and the use of the __MODULE__ attribute.
With half of the tables having been modeled to a satisfactory degree over the past 9 chapters, the content of this chapter finally allows us to gradually import data from the original Northwind Traders database to tables as we gradually model them. With some laziness-driven automation through the DataImporter module, this will make such tasks in the next chapters fly by.
In the next chapters we can proceed with modeling the rest of the tables rapidly. My interim goal is to have all of the original dataset imported, and thus be able to execute queries both complex and simple. After the “basic” task of having the entire database implemented using Ecto, the goal is to branch out and explore the fringes a bit; consider improvements to the database to take it further, using embedded schemas and Enums, and other nooks and crannies of Ecto.
I’ve marked the completion degree as 28% but it’s closer to 75% realistically. Then again… famous last words. Pretty sure that the head shall meet the desk percussively and repeatedly many times over the next weeks.
As always, your feedback is welcome!
And, if you’re enjoying the book, I would appreciate if you would leave a comment on this thread, share a link to https://leanpub.com/northwind-elixir-traders on the social network of your preference, or anything else that you find meaningful to get more people to get the book and provide feedback.
Cheers,
Isaak
New chapter dropping sometime around (my) midnight tonight. This one’s will be on modeling the remaining tables, except for the OrderDetails “join table” (which we’ll deal with in a future chapter on many-to-many relationships).
This one seemed simple at first, but once you’ve seen the quality of data of the :phone fields in the original Suppliers and Shippers table, you’ll realize that a custom validation function is needed.
As a preview: this is a regex for NANP-formatted phone numbers, tested on all phone numbers in the Northwind Traders database: https://regexr.com/80rgh.
Good thing that the Suppliers table includes a :country field, so that we can make sense of the mess!
Stay tuned. That’s going to be a fun one to write!
Hello, hello,
The first part of Chapter 11 on modeling further tables is now available.
It’s only “the first part”, because this chapter truly kicked my butt, causing a couple of partial rewrites.
It started off uneventfully, with the main goal of modeling the remaining three tables (OrderDetails is temporarily left aside). Yet, it rapidly escalated to dealing with the phone numbers of the Suppliers and Customers tables of the original Northwind Traders database, with GETting and parsing a CSV file containing ISO 3166-1 information including dial codes (for eventually formatting and validating phone numbers as internationally-formatted, dial-code-prefixed strings).
BTW, did you know that the Dominican Republic is the only country with three different dial codes? That one was a fun one to discover, as was dealing with columns in a CSV file that contain a comma-separated list of values that break simplistic String.split/2 calls when parsing a CSV row.
Other things that this chapter includes:
GET request using Erlang’s :httpc client.Part 2 of this chapter is coming within the next 4-5 days.
Cheers,
Isaak
Hello again from Athens, Greece!
This release completes Chapter 11, which proved to be the longest so far, despite initial appearances that it would be rather straightforward.
Chapter 11 has seen a meticulous approach to table modeling within the Northwind Elixir Traders (NET) database. We revisit our methodical SIPOC’ed process of table creation four times, adapting it for each table’s unique needs beyond the OrderDetails table, which was reserved for future query capabilities via Ecto.Query.
The focus in this chapter (and where most of the content is spent) shifted to data integrity and internationalization for Shippers and Suppliers tables with phone number fields. By creating a custom changeset validation function using helper functions, we automate conversions of NANP-formatted numbers into international formats across all pertinent tables, including the development of associated modules that model Country records, and the PhoneNumbers module that includes various helper functions for phone number processing. Another fruit of this labor is a country validation function that uses publicly available ISO 3166-1 data on CLDR display names (“Greece”), Alpha-3 codes (“GRE”), and dial codes (“+30”).
The various custom validation functions have now been refactored in a separate Validations module, to that they can be used across various modules’ changeset functions.
Finally, the chapter also involves rudimentary exploration of the original Northwind Traders dataset as an investigation and source of discussion for potential improvements to the NET database schema to better match the practical needs of a real-life B2B business.
We are thus now at the cusp of modeling the last table of NT, OrderDetails, and thus leaving the original database behind–for good, and eventually, for better!
The next chapter will do exactly that, before we proceed to Ecto queries and other Ecto topics, such as embedded schemas, Enums, and custom field types. These latter elements will be part of the closing chapters of the book that will take NET beyond the pedagogical status of NT and well into something that could serve as a starting blueprint for a real-life B2B business (which is informed by my professional experience as the Managing Director of TECTRA Ltd, where my business programming journey began).
As always, I welcome your feedback! And if you have been enjoying the book, I would appreciate if you could send me a short testimonial and profile photo by email to isaak@overbring.com, and your permission to add that to the book’s testimonials on Leanpub.
Until next week!
Isaak
Hello dear readers of Northwind Elixir Traders,
After a long break due to other professional engagements (three of which, happily Elixir-related!), I bring you a short new chapter on modeling the final table of the original Northwind Traders database: the OrderDetails table. Plus, minor improvements to the previous chapter.
With that final table in place and all data imported thanks to the DataImporter module continuing to pay dividends, the main mission of the book is complete! We now have a “Northwind Elixir Traders” database in place, with all the original data available and ready to be accesssed with queries.
That doesn’t mean that the development of the book is over, however. Beyond queries, I want to use the next chapters to:
As always, thank you to everyone who has provided feedback over email and Linkedin!
Best regards from Athens, Greece,
Isaak
isaak@overbring.com
PS: I have extended the 10% discount by another 10 copies for Elixir Forum readers. Use this link: Northwind Elixir Traders [Leanpub PDF/iPad/Kindle]
Hello everyone, and especially readers of Northwind Elixir Traders!
This is just an update that I expect to be getting back on completing the book around mid-November, since I’ve been fully loaded with both Elixir-related and non-Elixir-related work since mid-July that made it impossible to maintain the weekly/bi-weekly writing pace.
At the same time, it’s great to see that the book has been so warmly received – so, in case any readers have feedback, feel free to share it here so that I can take it into account once I get back to writing in November!
The upside of the lull in writing is that I’ve been putting in practice everything in the book, plus Ecto queries (a pending chapter of Northwind Elixir Traders) while developing a Phoenix LiveView “sister app” to Breek.gr (which is written in NextJS with a PHP backend) – check it out:
And yes, Managers uses SQLite. And, it makes heavy use of my ExNominatim Elixir library for interacting with the OpenStreetMap Nominatim API.
Stay tuned!
(PS: I much prefer Phoenix LiveView to NextJS 
)
Will you be covering any PostGIS material? Doing queries by lat long, etc?
PS I bought the book yesterday, I’m already 75 pages in. I love the style of starting with the very basics and discovering the way forward via necessity.
Thank you Bryan for the feedback here and over email and LinkedIn! PostGIS is way beyond the scope of this book, as Northwind Elixir Traders uses SQLite as the database.
