<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Michael Westbay on Medium]]></title>
        <description><![CDATA[Stories by Michael Westbay on Medium]]></description>
        <link>https://medium.com/@westbaystars?source=rss-45f8dbf755c4------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*-ri8htJQm1Ud5L0P.</url>
            <title>Stories by Michael Westbay on Medium</title>
            <link>https://medium.com/@westbaystars?source=rss-45f8dbf755c4------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 20:53:36 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@westbaystars/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[PC-8201 HID]]></title>
            <link>https://medium.com/@westbaystars/pc-8201-hid-4f221245cb88?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/4f221245cb88</guid>
            <category><![CDATA[raspberry-pi-pico-w]]></category>
            <category><![CDATA[custom-keyboard]]></category>
            <category><![CDATA[vintage-computing]]></category>
            <category><![CDATA[elixir]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Sat, 13 Sep 2025 13:41:36 GMT</pubDate>
            <atom:updated>2025-09-13T13:41:36.574Z</atom:updated>
            <content:encoded><![CDATA[<p>Getting the NEC PC-8201 (circa 1983) keyboard working as a Human Interface Device (HID) on modern hardware.</p><figure><img alt="The NEC PC-8201 keyboard is out of its computer shell and connected via wires to a breadboard with a Raspberry Pi Pico W. There is also a sheet of paper with the pin diagram of the Pico W for reference as well as pliers and a couple of extra wires." src="https://cdn-images-1.medium.com/max/1024/1*vIiitA6bMtplKHQkZLsDdA.png" /><figcaption>Connecting the PC-8201 keyboard to the Raspberry Pi Pico W</figcaption></figure><p>It’s been a while since my <a href="https://medium.com/@westbaystars/keyboard-firmware-80a08065dafc">last update</a> for giving my spare NEC PC-8201 a new life as a USB keyboard. When I left off, I was awaiting some wires and my tests with some alligator clips was proving positive.</p><p>Of note, I mistakenly thought that connecting power to the keyboard had gotten it to push through enough signal to be sensed on the Pico side as a key press. I now think that it was a fortunate wiring mistake that was responsible for it working. More on that later.</p><p>So, why has it taken this long to get back to this project? Well, the beginning of baseball season means that I’m much more busy with work. But I also don’t seem to have the knack for getting wires to connect to one another.</p><h3><strong>Crimping Problems</strong></h3><p>I ordered wires along with a set of pins with housings to plug directly into the Raspberry Pi Pico W. At least, that was the plan. But my very old, cheap tools proved difficult to get the wires to connect cleanly with the pins (male or female). I tried crimping them. I tried soldering them. Going into software instead of hardware was probably a good choice, given how awful I am with connecting a circuit. Repeatedly, I would try to get it all connected, but all I wound up doing was making the lead wires coming off of the keyboard shorter and shorter.</p><p>I finally ordered a breadboard to hold the Pi and managed to get most of the pins to have a current go through them. The second black wire (KS1) is still in need of some finesse.</p><figure><img alt="Grid showing the intersection of the keyboard matrix of rows and columns and what characters the intersections map to. There is also a red square highlighting the second row — which is the row that is not currently getting any current. The memory address for getting the key input is at port address E8 (hex). There is also a note stating that one may enable the scan of a row of the keyboard matrix by setting B9 and BA (hex) to low." src="https://cdn-images-1.medium.com/max/708/1*7b4ntC7Xde5Lp-Vnhn7Usw.png" /><figcaption>Keyboard matrix diagram</figcaption></figure><h3><strong>Rows and Columns</strong></h3><p>When I wrote the Lisp code for the keyboard matrix, I had the idea in my head that the black wires were the rows and the white wires were the columns. It turns out that the main issue I had from my earlier tests was that I had them backwards according to the source code.</p><pre>;; From KeypadPico.cake<br><br>- (var row-pins (array (const int)) ;; Samples. Black wires.<br>-   (array 16 17 18 19 20 21 22 26 27))  ;; KS0 - KS8<br>+ (var row-pins (array (const int)) ;; Samples. White wires.<br>+   (array 13 12 11 10 9 8 7 6))<br><br>- (var column-pins (array (const int))   ;; Sends power. White wires.<br>-   (array 13 12 11 10 9 8 7 6))         ;; KR0 - KR7<br>+ (var column-pins (array (const int)) ;; Sends power. Black wires.<br>+   (array 16 17 18 19 20 21 26 27 28))<br></pre><p>I still think of the black wires as the rows, since they are rows in the keyboard matrix diagram. But looking at the “<strong>Techno Book 8200</strong>” page 170 (the keyboard matrix above), it mentions (in Japanese) that enabling key scanning is done by checking the KR0 to KR7 (the white wires).</p><p>Changing the scanning loop to go over the KR<em>x</em> to read the values of the KS<em>y</em> was what was needed to get readings from the Pi as a HID.</p><pre>;; From KeypadPico.cake<br><br>(var matrix-to-keycodes-keypad (array 8 (array 9 uint8_t))<br>  (array<br>    (array HID_KEY_Z HID_KEY_A HID_KEY_Q HID_KEY_O HID_KEY_1 HID_KEY_9 HID_KEY_ARROW_UP HID_KEY_F1 HID_KEY_SHIFT_LEFT)                                ;; KR0<br>    (array HID_KEY_X HID_KEY_S HID_KEY_W HID_KEY_P HID_KEY_2 HID_KEY_0 HID_KEY_ARROW_DOWN HID_KEY_F2 HID_KEY_CONTROL_LEFT)                            ;; KR1<br>    (array HID_KEY_C HID_KEY_D HID_KEY_E HID_KEY_KEYPAD_AT HID_KEY_3 HID_KEY_SEMICOLON HID_KEY_ARROW_LEFT HID_KEY_F3 HID_KEY_GUI_LEFT)                ;; KR2<br>    (array HID_KEY_V HID_KEY_F HID_KEY_R HID_KEY_BACKSLASH HID_KEY_4 HID_KEY_KEYPAD_COLON HID_KEY_ARROW_RIGHT HID_KEY_F4 HID_KEY_LOCKING_SCROLL_LOCK) ;; KR3<br>    (array HID_KEY_B HID_KEY_G HID_KEY_T HID_KEY_COMMA HID_KEY_5 HID_KEY_MINUS HID_KEY_TAB HID_KEY_F5 HID_KEY_LOCKING_CAPS_LOCK)                      ;; KR4<br>    (array HID_KEY_N HID_KEY_H HID_KEY_Y HID_KEY_PERIOD HID_KEY_6 HID_KEY_SPACE HID_KEY_ESCAPE keycode-unused keycode-unused)                         ;; KR5<br>    (array HID_KEY_M HID_KEY_J HID_KEY_U HID_KEY_SLASH HID_KEY_7 HID_KEY_INSERT HID_KEY_ENTER keycode-unused keycode-unused)                          ;; KR6<br>    (array HID_KEY_L HID_KEY_K HID_KEY_I HID_KEY_KEYPAD_CARET HID_KEY_8 HID_KEY_BACKSPACE keycode-unused HID_KEY_STOP keycode-unused)                 ;; KR7<br>  )<br>)</pre><p>The main loop of the program goes over the 8 rows (white wires) to see where the connections turn up in the 9 columns (black wires). That reordering of the scans got the Pi to act as a keyboard.</p><p>I would like to say that I’m typing this on the PC-8201 keyboard now, but with the SK1 row still not working at the time of writing, that’s kind of difficult.</p><h3><strong>Next Steps</strong></h3><p>One would think that I’ll get the connection to the SK1 pin fixed and start using my old keyboard. Well, not quite. A number of the keys are reporting that they are not supported:</p><figure><img alt="Key events listed as ‘down {“key_code”:”unsupported”}’ and ‘up {“key_code”:”unsupported”}’." src="https://cdn-images-1.medium.com/max/750/1*0HrgGU7tRlKOOrdt2pWsmQ.png" /></figure><p>They seem to be the HID_KEY_KEYPAD_xxxx keys. And none of the function keys even register as being unsupported, as well as the arrow keys — just nothing.</p><p>These don’t seem to be insurmountable problems. But I’ve gotten distracted by <a href="https://github.com/atomvm/AtomVM/">AtomVM</a>.</p><p>What is AtomVM? From the Github repository:</p><blockquote>Brings Erlang, Elixir and other functional languages to really small systems.</blockquote><blockquote>AtomVM implements from scratch a minimal Erlang VM that supports a subset of ErlangVM features and that is able to run unmodified BEAM binaries on really small systems like MCUs.</blockquote><p>In other words, it will allow me to write my keyboard driver in Elixir! (Or Erlang.) I’ve been spending the past couple of weekends getting familiar with the source code with the plan to port over the HID library so that I can have a fully Elixir powered 1983 vintage keyboard! How does that sound for a plot twist?</p><p>I’ve gotten the embedded Hello World of a blinking LED working with it. And I’ve got a development environment working where I can compile and load the VM onto my Raspberry Pi Pico W. However, the <strong>main</strong> branch is currently compiling to be too large for the Pico. That leaves me with building for the <strong>stable</strong> branch or working out how to get the VM to compile smaller.</p><p>It’s been decades since I’ve written C code. Still, I’m ready to dive in and make that vintage keyboard powered by Elixir yet.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4f221245cb88" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Keyboard Firmware]]></title>
            <link>https://medium.com/@westbaystars/keyboard-firmware-80a08065dafc?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/80a08065dafc</guid>
            <category><![CDATA[retrocomputing]]></category>
            <category><![CDATA[custom-keyboard]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Mon, 13 Jan 2025 12:24:30 GMT</pubDate>
            <atom:updated>2025-01-14T01:06:52.969Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wQLQQ1i7PCab1HbZA5c-LQ.png" /><figcaption>Probing the Keyboard with the Raspberry Pi Pico W</figcaption></figure><h3><strong>Learning About Pi Firmware</strong></h3><p>With the keyboard pin layout figured out from <a href="https://medium.com/@westbaystars/retrofit-65e23e00ced5">the previous article</a>, it’s now time time to learn how to wire it up to my Raspberry Pi Pico W. I spent a good amount of my free time over the weekend learning how others have built their custom keyboards. Most go into a lot of detail about how to wire them up with the rows and columns, some order custom PCBs and 3D print their custom cases while others 3D print the boards and wire them up themselves. The custom keyboard community is huge.</p><p>I already have a keyboard, but I want to be able to have it act as a standard USB keyboard with my Pico. It seems that most of the custom keyboard community turns to firmware packages like KMK or QMK. I looked at these, but none really told me what was going on under the hood. Too much magic for me.</p><h3><strong>Using the Raspberry Pico SDK</strong></h3><p>Then I came across Macoy Madson’s blog post about <a href="https://macoy.me/blog/programming/PicoKeyboard">Programming a USB Keyboard with Raspberry Pi Pico SDK</a>. Macoy felt much like I did about QMK. Namely, I want to understand how it worked more than just filling out a JSON file and sticking it into a framework.</p><p>Macoy has a <a href="https://macoy.me/code/macoy/keyboards/src/commit/cca7b092c38b9475d1210d7e7c9f9dae63f4df9b">git repository</a> for his keyboard projects that uses a Lisp (<a href="https://macoy.me/code/macoy/cakelisp">CakeLisp</a>) program compile to machine code that can then be linked to the C based Pico SDK. While it’s been a couple of decades since I’d done anything in Lisp, it was actually very easy to follow. And I’m finding that I enjoy the language more than when I last tinkered with it at Yokohama National University in 1989. If I had more time, I can very much see getting into it again.</p><p>Anyway, Madson-san’s repository appears to build for two devices, a keyboard and a keypad. The keyboard that he built is actually in two pieces, which complicates the source code for what I want to do. I was able to get the keypad .cake files compiled linked, and flashed onto the Pico fairly easily. Placing the keyboard connectors to the Pico pins in some random positions showed that some of the keys were working!</p><p>The other thing I liked about Macoy’s code is that he labeled rows and columns the way that I was thinking of them.</p><h3><strong>Mapping the Keyboard to the Raspberry Pi‘s Pins</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/974/1*NAjsZxmUSEdBqHEwKNFztQ.png" /><figcaption>Raspberry Pi Pico W Pin Layout</figcaption></figure><p>The first thing I needed to do was decide which pins would take which wires from the keyboard. For that, I selected GPIO pins 6 through 13 for the 8 columns (white wires) and GPIO pins 16–22 and 26–27 for the 9 rows (black wires). The relavant Lisp code for this was:</p><pre>;; From KeypadPico.cake<br><br>(var row-pins (array (const int)) ;; Samples. Black wires.<br>  (array 16 17 18 19 20 21 22 26 27))  ;; KS0 - KS8<br><br>(var column-pins (array (const int))   ;; Sends power. White wires.<br>  (array 13 12 11 10 9 8 7 6))         ;; KR0 - KR7</pre><p>Then I need to map the rows and columns to actual key presses:</p><pre>;; From KeypadPico.cake<br><br>(var matrix-to-keycodes-keypad (array 9 (array 8 uint8_t))<br>  (array<br>    (array HID_KEY_Z HID_KEY_X HID_KEY_C HID_KEY_V HID_KEY_B HID_KEY_N HID_KEY_M HID_KEY_L) ;; KS0<br>    (array HID_KEY_A HID_KEY_S HID_KEY_D HID_KEY_F HID_KEY_G HID_KEY_H HID_KEY_J HID_KEY_K) ;; KS1<br>    (array HID_KEY_Q HID_KEY_W HID_KEY_E HID_KEY_R HID_KEY_T HID_KEY_Y HID_KEY_U HID_KEY_I) ;; KS2<br>    (array HID_KEY_O HID_KEY_P HID_KEY_KEYPAD_AT HID_KEY_BACKSLASH HID_KEY_COMMA HID_KEY_PERIOD HID_KEY_SLASH HID_KEY_KEYPAD_CARET) ;; KS3<br>    (array HID_KEY_1 HID_KEY_2 HID_KEY_3 HID_KEY_4 HID_KEY_5 HID_KEY_6 HID_KEY_7 HID_KEY_8) ;; KS4<br>    (array HID_KEY_9 HID_KEY_0 HID_KEY_SEMICOLON HID_KEY_KEYPAD_COLON HID_KEY_MINUS HID_KEY_SPACE HID_KEY_INSERT HID_KEY_BACKSPACE) ;; KS5<br>    (array HID_KEY_ARROW_UP HID_KEY_ARROW_DOWN HID_KEY_ARROW_LEFT HID_KEY_ARROW_RIGHT HID_KEY_TAB HID_KEY_ESCAPE HID_KEY_ENTER) ;; KS6<br>    (array HID_KEY_F1 HID_KEY_F2 HID_KEY_F3 HID_KEY_F4 HID_KEY_F5 keycode-unused keycode-unused HID_KEY_STOP) ;; KS7<br>    (array HID_KEY_SHIFT_LEFT HID_KEY_CONTROL_LEFT HID_KEY_GUI_LEFT HID_KEY_LOCKING_SCROLL_LOCK HID_KEY_LOCKING_CAPS_LOCK keycode-unused keycode-unused keycode-unused) ;; KS8<br>  )</pre><p>The HID_KEY_* codes can be found in the tinyusb/src/class/hid/hid.h header file in the Pico SDK.</p><p>Other changes I made were to adjust the lengths of the arrays from the from 4 rows and 5 columns to 9 rows and 8 columns. I also removed the key layer code, as it appears that Macoy had two modes for his keypad. Once I got all of my parenthesis matching, I recompiled, linked, and flashed the Pico, and rebooted it.</p><p>To test that the firmware was working, with Karabiner-EventViewer running on the connected computer, I connected a wire from GP13 and tapped the other end of the wire to GP16. It showed that I successfully hit the “z” key, which corresponds to the HID_KEY_Z (KS0 x KR0) in the above code. It worked!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QwbVgO65GPKUJmEsYXP82w.png" /><figcaption>Simulating a “Z” tap on the Pico in Karabiner-EventViewer</figcaption></figure><p>With that working, it was time to hook up the keyboard to the Pico. I’m still waiting for wires to arrive, so I attached alligator clips running from black wire 0 to GP16 and from the white wire 1 to GP13. I then hit the Z key on the keyboard, …, and nothing happened. Hmm. Testing the two wires to the keyboard with my voltmeter and hitting the Z key shows that a current goes through when the key is pressed. I know that it read keys with the original firmware connecting pin to pin, so what’s wrong?</p><p>Well, tapping on other pins on the Pico while experimenting earlier with pin to pin connections, I noticed that tapping on a GND pin would often output a lot of keys. I wonder if I need to ground the black 9 wire. … Nope, that doesn’t work. How about attaching the black 9 wire to Pin 36 (3V3(OUT))? Once that wire was attached (see first image) and I hit the Z key, I got a down (&quot;key_code&quot;:&quot;z&quot;) and up (&quot;key_code&quot;:&quot;z&quot;) in Karabiner-EventViewer! It worked!</p><h3><strong>Waiting for Wires</strong></h3><p>I tested with connecting to a few more pins to make sure that all rows and columns lined up with the keys I was expecting them to map to. All’s good. I just need some clean wire connections to make this work now. (I ordered them at the same time as the Pico from Amazon. The Pico arrived within days, but I didn’t notice that it would take a few weeks for the wires. Should have ordered through <a href="https://www.askul.co.jp/ksearch/?searchWord=%E3%83%96%E3%83%AC%E3%83%83%E3%83%89%E3%83%9C%E3%83%BC%E3%83%89%E7%94%A8%E3%82%B8%E3%83%A3%E3%83%B3%E3%83%91%E7%B7%9A+&amp;categoryLl=&amp;categoryL=&amp;categoryM=&amp;categoryS=&amp;categoryLll=&amp;fluxSearch=ON">ASKUL</a> — which has been amazingly fast for resisters and other hobby electronics parts. Will do so in the future.)</p><p>Anyway, once I have those, I’ll start tweaking which keys do what (I’m curious how the HID_KEY_LOCKING_CAPS_LOCK and HID_KEY_LOCKING_SCROLL_LOCK (I didn’t see a HID_KEY_LOCKING_KANA_LOCK) will work in practice.) The Pine64 projects I mentioned in the first article converted the CAPS_LOCK to a regular keyboard key rather than a locking toggle switch. Also, the lack of an ALT key (I have the GRAPHkey mapped to HID_KEY_GUI_LEFT) could be troublesome with modern operating systems. So I’m going to have to find a way to simulate that.</p><p>I’m really enjoying hardware hacking. I had seriously considered electronic engineering instead of software engineering back when I started college in 1985. Never too late to learn!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=80a08065dafc" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Retrofit]]></title>
            <link>https://medium.com/@westbaystars/retrofit-65e23e00ced5?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/65e23e00ced5</guid>
            <category><![CDATA[retrocomputing]]></category>
            <category><![CDATA[elixir]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Thu, 09 Jan 2025 11:32:52 GMT</pubDate>
            <atom:updated>2025-01-09T11:32:52.271Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZJvhsCROGB_XR_ijYK1qcQ.png" /><figcaption>Probing the Keyboard Pins</figcaption></figure><p>My original “problem to be solved” was that I really wanted my first computer, an NEC PC-8201 that I bought in 1983 with my summer savings, to run again. And thanks to tinkerer extraordinaire at the <a href="https://www.youtube.com/@HeyBirt">Hey Birt! channel on YouTube</a> and “Slabtop Computers” retro computer Discord channel, I was able to do just that. It took replacing the internal backup battery and the faulty N82 BASIC chip to do it.</p><p>The N82 BASIC chip came from a “donor computer” that I ordered off of Yahoo! Auctions (Japan’s eBay). That computer is actually in better shape than my original computer, but I prefer to keep mine intact, mainly for sentimental reasons. (Logic doesn’t seem to play much of a role in nostalgia.)</p><p>Still, it’s good to have this one around for parts as needed in the future.</p><p>So, what can I do with the spare computer, other than hide it away in a box somewhere? That’s the new problem to be solved.</p><h3><strong>What to do with a Dead Model 100?</strong></h3><p>This appears to have been asked by others before. I saw <a href="https://www.garyweber.net/pine-100">this project</a> linked to from the retro Discord server a while back and thought it would be the perfect thing to do with the spare. Gary had a dead Tandy Model 100 (the American cousin of the PC-8201) and decided to replace the LED screen with a modern1920x480 ISP display and the motherboard with a PINE A64. He got the idea after seeing <a href="https://fadsihave.wordpress.com/">this blog post</a> by “belsamber.” So I know that it’s a doable project.</p><h3><strong>Starting with the Keyboard</strong></h3><p>Both of the previous people are using a PINE A64 for the main computer, which has a lot of GPIO pins. I’ve been considering using either the <a href="https://www.beagleboard.org/boards/beaglebone-black">BeagleBone Black</a> or <a href="https://www.beagleboard.org/boards/beagleplay">Beagle Play</a> for my retrofit project.</p><p>The “Black” already has embedded Elixir support for it. Also, it has the GPIO pins to support connecting the keyboard to it directly, making it the simpler selection since most of the work in figuring out how to connect to it is done.</p><p>The “Play,” on the other hand, appears to be more modern, has wireless connectivity built in (so it can act as a home server or be tethered while “working” at the park without any additional hardware). But it’s going to be much tougher to ① connect the keyboard to the limited number of GPIO pins available and ② get embedded Elixir running on it. The challenge is very enticing.</p><p>While I mull over which motherboard to use, I may as well get the keyboard working with a Raspberry Pi Pico W. It has the number of pins that I’ll need and can act as a HID (Human Interface Device) keyboard/mouse to other computers if needed. And it supports Bluetooth Low Power should I go with the “Play” solution.</p><p>But first, I need to understand and map out which how the keyboard works.</p><h3><strong>Mapping the Pins</strong></h3><p>Thanks to the <em>PC-8200 Technical Library</em> book I purchased back in 1984, I have a place to start.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/678/1*fUxWgC6FWI28tuz5reBhrw.png" /><figcaption>Row and Column Layout of the PC-8201 Keyboard</figcaption></figure><p>The black wires connect to the motherboard with connection to the rows of the keyboard (KS0 through KS8), and the white wires connect to the motherboard as the columns of keys (KR0 through KR7 — which are really pins 1 through 8 on the ribon interface). Based on the keymap source code that the other two provided, pin 9 is GND for both interfaces and pin 0 is GND for the columns.</p><p>I also note that the source code provided by the other two show rows and columns in reverse. So row 0 goes Z, A, Q, O, 1, 9, <em>space</em> (↑), <em>F5</em> (<em>F1</em>), <em>left-shift</em> (<em>shift</em>). One experiment I will be doing is reversing this “row,” “column” enumeration of their source code to better fit my understanding of it. Since the keymap in the source code is a single array of the two dimentional grid, reordering by “my” rows and swapping row and column designations should logically work.</p><h3><strong>Playing with the Pico</strong></h3><p>And that’s pretty much as far as I’m going to get until the jumper wires that I ordered arrive. I am learning how to interface and flash firmware to the Raspberry Pi Pico for now. Once I found a cable that carries data (most of my spare micro USB cables seem to be power only), I got the “Hello World” of microcontrollers running, flashing an LED light.</p><p>The adventure in learning is just beginning!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=65e23e00ced5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Multilingual Names with Postgres, Elixir, and Ash — Searching JSON Embeds]]></title>
            <link>https://medium.com/@westbaystars/multilingual-names-with-postgres-elixir-and-ash-searching-json-embeds-160443bafc2c?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/160443bafc2c</guid>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Sun, 29 Dec 2024 13:39:43 GMT</pubDate>
            <atom:updated>2024-12-29T23:58:43.591Z</atom:updated>
            <content:encoded><![CDATA[<h3>Multilingual Names with Postgres, Elixir, and Ash — Searching JSON Embeds</h3><p>When we <a href="https://medium.com/@westbaystars/multilingual-names-with-postgres-elixir-and-ash-continued-eb152236b80b">last left off</a>, we got a specific language’s name to be automatically retrieved via a calculation that processed all names in a given JSON embed and returned the one we wanted, inserting it into the :name field.</p><p>Now I want to be able to search all JSON records for a specific name. That requires using Postgres’ JSON extensions, which are not covered in the standard SQL command libraries. Neither Ecto nor Ash have functions to take advantage of these extensions built in, like they do with building a SELECT or WHERE clause with the standard operations. But they do have “escape hatches” that allow one to call the raw SQL needed to use them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/928/1*bmvTRtAa2UtICcHEgOKauA.png" /><figcaption>Select all persons with surname or given name starting with “Maki”</figcaption></figure><p>Unfortunately, calling raw SQL in a safe way is hard. Any user input needs to be passed safely. That’s what ^arg(pram) is for. But more than safety, figuring out how to get one of these jsonpath functions to work with a parameter has turned out to be challenging.</p><p>After much searching on forums and StackOverflow, I finally reached out on the Elixir Forum’s <strong>Ash Forum</strong> section for advice. And the creator of Ash, Zach Daniel, gave me the <a href="https://elixirforum.com/t/filtering-with-json-path-postgres/68386/3?u=westbaystars">hint that I needed</a> to make it work.</p><h3><strong>The Problem to be Solved</strong></h3><p>First of all, what is the problem to be solved? How hard can it be to write a fragment that does the jsonpath query to find a person’s name? It turns out, very hard.</p><p>As mentioned before, I searched all kinds of places for people using JSONB fields in their Elixir projects. Almost all of them are using Ecto, and one would think that those solutions will also work with Ash. I’m sure they will, but all of the examples are how the data is stored and fetched via standard fields. Finding an example where someone is passing a parameter to find something in that array of JSON blobs has proven impossible (at least with my searching skills).</p><h3><strong>First Step — Get Query to Work in PSQL</strong></h3><p>The first thing I needed to do was to understand the PSQL statement that I ultimately wanted to build. PSQL’s documentation is very thorough with all of the functionality available. After a lot of reading of the documents and seeing samples on StackOverflow and Reddit, I finally came up with:</p><pre>select id, sid <br>from persons <br>where to_json(names)::jsonb @@ <br>  &#39;($[*].surname like_regex &quot;^maki&quot; flag &quot;i&quot;) || <br>   ($[*].given_name like_regex &quot;^maki&quot; flag &quot;i&quot;)&#39;;<br><br>  id   |        sid<br>-------+-------------------<br>  1170 | makita-kazuhisa<br>  2049 | makihara-taisei<br>...<br> 10386 | itoh-makizo<br>...</pre><p>Great! Verified that both surname and given_name records are found!</p><h3><strong>Next Step — How Do We Make This Work with Ash?</strong></h3><p>Before getting into the nitty-gritty, let’s establish some context. Namely, we need to know what thePerson module looks like.</p><pre>defmodule ScoutDragon.Sports.Person do<br>  use Ash.Resource,<br>    domain: ScoutDragon.Sports,<br>    data_layer: AshPostgres.DataLayer<br><br>  require Ash.{Expr, Query}<br><br>  alias ScoutDragon.Sports.{<br>    Attribute,<br>    Bridge,<br>    Draft,<br>    Gender,<br>    Lang,<br>    Link,<br>    PersonAlias,<br>    PersonName,<br>    Stint<br>  }<br><br>  alias ScoutDragon.Calculations.LangEmbeds<br><br>  postgres do<br>    table &quot;persons&quot;<br>    repo ScoutDragon.Repo<br><br>    custom_indexes do<br>      index [:sid], unique: true<br>      index [:xid], unique: true<br>    end<br><br>    check_constraints do<br>      check_constraint :sid, &quot;persons_sid_index&quot;<br>      check_constraint :xid, &quot;persons_xid_index&quot;<br>    end<br>  end<br><br>  actions do<br>    # :crate and :read actions go here<br>  end<br><br>  validations do<br>    validate present([:names])<br>  end<br><br>  attributes do<br>    integer_primary_key :id<br><br>    # Former ScoutDragon human readable ID<br>    attribute :sid, :string, public?: true<br><br>    # Former ScoutDragon person-id<br>    attribute :xid, :integer, public?: true<br><br>    attribute :names, {:array, PersonName}, public?: true, allow_nil?: false<br>    attribute :aliases, {:array, PersonAlias}, public?: true, default: []<br><br>    attribute :gender, Gender, public?: true, default: :unknown<br>    attribute :date_of_birth, :date, public?: true<br>    attribute :date_of_death, :date, public?: true<br><br>    attribute :bridges, {:array, Bridge}, public?: true, default: []<br>    attribute :links, {:array, Link}, public?: true, default: []<br>    attribute :attributes, {:array, Attribute}, public?: true, default: []<br>    attribute :drafts, {:array, Draft}, public?: true, default: []<br>    attribute :stints, {:array, Stint}, public?: true, default: []<br><br>    attribute :inserted_at, :utc_datetime_usec do<br>      writable? false<br>      default &amp;DateTime.utc_now/0<br>      match_other_defaults? true<br>      allow_nil? false<br>    end<br><br>    attribute :updated_at, :utc_datetime_usec do<br>      writable? false<br>      default &amp;DateTime.utc_now/0<br>      update_default &amp;DateTime.utc_now/0<br>      match_other_defaults? true<br>      allow_nil? false<br>    end<br>  end<br><br>  relationships do<br>    belongs_to :location_of_birth, ScoutDragon.Sports.Location<br>    belongs_to :location_of_death, ScoutDragon.Sports.Location<br>  end<br><br>  calculations do<br>    calculate :name,<br>              PersonName,<br>              {LangEmbeds, field: :names} do<br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end<br>  end<br><br>  identities do<br>    # Make sure the imported ScoutDragon IDs are unique<br>    identity :imported_person_id, [:sid], nils_distinct?: true<br>    identity :imported_person_xid, [:xid], nils_distinct?: true<br>  end<br>end</pre><p>No spoilers yet for the :read action to do the search. 😉</p><p>You can see that the calculation to get the :name is the same one we used in the previous article. I really like how reusable a lot of the pieces are between tables, :lang in the embedded names, even though it’s a PersonNameinstead of a regular Namein this case, being one of them.</p><p>Speaking of PersonName, let’s take a look at its module:</p><pre>defmodule ScoutDragon.Sports.PersonName do<br>  use Ash.Resource,<br>    data_layer: :embedded,<br>    embed_nil_values?: false<br><br>  validations do<br>    validate present([:lang, :given_name, :surname, :full_name])<br>  end<br><br>  attributes do<br>    attribute :lang, ScoutDragon.Sports.Lang do<br>      public? true<br>      allow_nil? false<br>      default :en<br>    end<br><br>    attribute :given_name, :ci_string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :middle_names, :ci_string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :surname, :ci_string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :full_name, :ci_string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :registered_name, :ci_string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :given_name_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :middle_names_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :surname_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :full_name_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :registered_name_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br>  end<br>end</pre><p>Very straight forward. It looks a lot like the Name module, but with :given_name, :middle_names, surname, :full_name, and :registered_name instead of :common_name and full_name. The pronuciation _ruby suffixes are on each of them as well.</p><p>After loading 41,185 persons from my old XML database, we’re ready to start trying to create the read :search_with_name action.</p><h3><strong>Closest Attempt</strong></h3><p>After about a week of intense research and trial and error, I got the following to work:</p><pre>    read :search_with_name do<br>      prepare build(load: [name: expr(%{lang: ^arg(:lang)})])<br>      filter (expr(fragment(&quot;to_json(names)::jsonb @@ &#39;($[*].surname like_regex \&quot;^Maki\&quot; flag \&quot;i\&quot;) || ($[*].given_name like_regex \&quot;^Maki\&quot; flag \&quot;i\&quot;)&#39;&quot;)))<br><br>      argument :name, :string do<br>        allow_nil? false<br>      end<br><br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end</pre><p>Great! At long last, I can search for player names!</p><p>However, when I replace the two `\&quot;^Maki\&quot; to?s, and added the necessary ^arg(:name) parameters, it failed.</p><pre>{:error,<br> %Ash.Error.Unknown{<br>   bread_crumbs: [&quot;Error returned from: Myapp.Sports.Person.search_with_name&quot;], <br>   query: &quot;#Query&lt;&gt;&quot;, <br>   errors: [<br>     %Ash.Error.Unknown.UnknownError{<br>       error: &quot;** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near \&quot;$1\&quot; of jsonpath input\n\n    query: SELECT p0.\&quot;attributes\&quot;, p0.\&quot;id\&quot;, p0.\&quot;links\&quot;, p0.\&quot;aliases\&quot;, p0.\&quot;names\&quot;, p0.\&quot;updated_at\&quot;, p0.\&quot;inserted_at\&quot;, p0.\&quot;sid\&quot;, p0.\&quot;xid\&quot;, p0.\&quot;bridges\&quot;, p0.\&quot;date_of_birth\&quot;, p0.\&quot;date_of_death\&quot;, p0.\&quot;drafts\&quot;, p0.\&quot;gender\&quot;, p0.\&quot;location_of_birth_id\&quot;, p0.\&quot;location_of_death_id\&quot;, p0.\&quot;stints\&quot; FROM \&quot;persons\&quot; AS p0 WHERE ((to_json(names)::jsonb @@ &#39;($[*].surname like_regex $1 flag \&quot;i\&quot;) || ($[*].given_name like_regex $2 flag \&quot;i\&quot;)&#39;))&quot;,<br>       field: nil,<br>       value: nil,<br>       splode: Ash.Error,<br>       bread_crumbs: [&quot;Error returned from: MyApp.Sports.Person.search_with_name&quot;],<br>       vars: [],<br>       path: [],<br>       stacktrace: #Splode.Stacktrace&lt;&gt;,<br>       class: :unknown<br>     }<br>   ]<br> }}</pre><p>I tried all kinds of things. The root of the problem was that the $1 and $2 were not getting their safe substitutions on the Postgres side of things. I thought that maybe the problem was that I had a :name attribute on the table thanks to my calculation. Changing the parameter name to search_name didn’t fix it.</p><p>That’s when I decided that I wasn’t going to be able to do this on my own and reached out on the Elixir Forum. I am so glad that I did.</p><h3><strong>The Root Cause</strong></h3><p>Even with the limited amount of information I shared on the Forum, Zach was able to piece together what was happening and explained:</p><blockquote>The primary issue here is that the whole value after @@ is a single string. The fragment logic is replacing ? with the parameter replacements, but postgres doesn’t accept parameters just inside of a string like that.</blockquote><blockquote>Try replacing your filter with this preparation. Note that I’ve also used the expression syntax to interpolate the names reference. You should do this instead of putting in names literally as that can cause ambiguous queries.</blockquote><pre>prepare fn query, _ -&gt; <br>  jsonpath = &quot;&#39;($[*].surname like_regex \&quot;^#{query.arguments.name}\&quot; flag \&quot;i\&quot;) || ($[*].given_name like_regex \&quot;^#{query.arguments.name}\&quot; flag \&quot;i\&quot;)&#39;&quot;<br>  <br>  Ash.Query.filter(query, fragment(&quot;to_json(?)::jsonb @@ ?&quot;, names, jsonpath)<br>end</pre><p>This didn’t work, but it was the hint that I needed to understand what was going on.</p><p>After more fiddling around for half a day, I finally decided that what I needed to do was what Zach suggested, but on the Postgres side.</p><h3><strong>Creating a Function in Postgres</strong></h3><p>I had created functions in Postgres around 20+ years ago, back before getting into XQuery for my database needs. So I needed a bit of a review.</p><p>Fortunately, the migrations that Ash generated when I setup the project contain a number of functions that Ash leverages and make for excellent samples. Combined with the Postgres documentation, I came up with:</p><pre>CREATE OR REPLACE FUNCTION person_name_search(person_names jsonb, name text)<br>RETURNS BOOLEAN AS $$<br>DECLARE<br>  name TEXT = COALESCE(NULLIF(TRIM(name), &#39;&#39;), &#39;a&#39;);<br>  jsonpath JSONPATH = concat(<br>    &#39;($[*].surname LIKE_REGEX &quot;^&#39;, name, &#39;&quot; FLAG &quot;i&quot;) || &#39;,<br>    &#39;($[*].given_name LIKE_REGEX &quot;^&#39;, name, &#39;&quot; FLAG &quot;i&quot;)&#39;);<br>BEGIN<br>  RETURN person_names @@ jsonpath;<br>END; $$<br>LANGUAGE PLPGSQL<br>IMMUTABLE;</pre><p>The important thing here is that I can pass name safely to the function, so that I’m building raw SQL safely.</p><p>I did add a check to see if the name was empty or NULL and search for all names beginning with a if so.</p><p>Giving it a try in Postgres:</p><pre>select id, sid from persons where person_name_search(to_json(names)::jsonb, &#39;Maki&#39;);<br><br>  id   |        sid<br>-------+-------------------<br>  1170 | makita-kazuhisa<br>  2049 | makihara-taisei<br>...<br> 10386 | itoh-makizo<br>...</pre><p>It worked!</p><p>I still need to convert the field (names) to JSON and cast it to jsonb. I’d like to keep that on the calling side as I want to use the same function with aliases and potentially any array of JSON in the future.</p><h3><strong>The </strong><strong>Action that Works</strong></h3><p>Finally, with everything now set up, it’s time to make the action get all records of Persons with a given name.</p><pre>    read :search_with_name do<br>      prepare build(load: [name: expr(%{lang: ^arg(:lang)})])<br><br>      filter expr(<br>               fragment(&quot;person_name_search(to_json(names)::jsonb, ?)&quot;, ^arg(:search_name)) ||<br>                 fragment(&quot;person_name_search(to_json(aliases)::jsonb, ?)&quot;, ^arg(:search_name))<br>             )<br><br>      argument :search_name, :string do<br>        # nil and empty strings handed on Postgres side<br>        # allow_nil? false<br>        # default &quot;a&quot;<br>      end<br><br>      argument :leagues, {:array, :string} do<br>        allow_nil? false<br>        default []<br>      end<br><br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end</pre><p>As you can see, I check both names and aliases fields. And I pass the sanitized :search_name parameter to the database. You may also notice the name getting loaded based on the :lang being passed from the previous work we did. It’s all starting to come together!</p><p>The final piece to searching for names is to add this action to the Sports domain.</p><pre>    resource ScoutDragon.Sports.Person do<br>      define :create_person, action: :create<br>      ...<br>      define :search_persons, <br>        args: [:search_name, :leagues, :lang], <br>        action: :search_with_name<br>    end</pre><p>And the final test:</p><pre>Sports.search_persons!(&quot;Maki&quot;, [&quot;npb&quot;, &quot;kbo&quot;, &quot;cpbl&quot;], :en)<br>|&gt; Enum.count()<br><br>35</pre><p>That matches what we get in the Postgres console:</p><pre>select count(id) <br>from persons <br>where person_name_search(to_json(names)::jsonb, &#39;Maki&#39;);<br><br> count<br>-------<br>    35</pre><p>The nice thing about declaring these in the domain is that both search_persons or search_persons! can be used, returning either {:ok, [%Person{...}, ...]} or [%Person{...}, ...] results respectively, depending on what you want. This saves a lot of boilerplate coding.</p><p>The next step will be to filter similarly with :leagues embedded in the stints JSON array. Now that I have a working example, the rest of the development should go much faster.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=160443bafc2c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Multilingual Names with Postgres, Elixir, and Ash Continued]]></title>
            <link>https://medium.com/@westbaystars/multilingual-names-with-postgres-elixir-and-ash-continued-eb152236b80b?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/eb152236b80b</guid>
            <category><![CDATA[elixir]]></category>
            <category><![CDATA[ash]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Fri, 27 Sep 2024 11:54:25 GMT</pubDate>
            <atom:updated>2024-09-27T11:54:25.866Z</atom:updated>
            <content:encoded><![CDATA[<p>This is a continuation of working on porting my XML sports database to Postgres with Elixir and the Ash framework. See <a href="https://medium.com/@westbaystars/multilingual-names-with-postgres-elixir-and-ash-6951c729bb43">here</a> for the first part.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lcalb2-H-duHw61t-mS0LA.png" /><figcaption>Specifying the league and which languages to get for the name and abbreviation</figcaption></figure><h3><strong>Where We Left Off</strong></h3><p>In the second version (first using Ash) of this porting attempt, I ended with the ability to query the League records and get back the full embedded :names and :abbrs lists for each league. But what I really wanted was to just have a single :name and :abbr fetched for a given :lang on each record. I suspected that calculations was the way to go about it, and I’ve gotten it to work!</p><p>We start off by adding the two calculated field under the calculations block:</p><pre># lib/my_app/sports/league.exe<br><br>  alias MyApp.Calculations.LangEmbeddedResource<br><br>  ...<br><br>  calculations do<br>    calculate :abbr,<br>              Abbr,<br>              {LangEmbeddedResource, field: :abbrs} do<br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end<br><br>    calculate :name,<br>              Name,<br>              {LangEmbeddedResource, field: :names} do<br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end<br>  end</pre><p>The two fields are named :abbr and :name respectively. They are of embedded types Abbr and Name respectively as well. And in order to “calculate” the values, they are tied to either the :abbrs or :names fields as appropriate and take a :lang parameter (which defaults to :en if not specified).</p><p>The actual “calculation” takes place in the MyApp.Calculations.LangEmbeddedResource module. Let’s take a look at it:</p><pre># lib/my_app/calculations/lang_embedded_resource.ex<br><br>defmodule MyApp.Calculations.LangEmbeddedResource do<br>  use Ash.Resource.Calculation<br><br>  @impl true<br>  def init(opts) do<br>    if opts[:field] &amp;&amp; is_atom(opts[:field]) do<br>      {:ok, opts}<br>    else<br>      {:error, &quot;Expected a :field option to a language specific embed&quot;}<br>    end<br>  end<br><br>  @impl true<br>  def load(_query, opts, _context) do<br>    opts[:field]<br>  end<br><br>  @impl true<br>  def calculate(records, [field: field], %{arguments: %{lang: lang}}) do<br>    Enum.map(records, fn record -&gt; get_field_for_lang(record, field, lang) end)<br>  end<br><br>  # Get the embedded item for a given record for either:<br>  #   * the specified langauge<br>  #   * in English<br>  #   * the first item<br>  defp get_field_for_lang(record, field, lang) do<br>    items = Map.get(record, field)<br>    item = Enum.find(items, fn embed -&gt; embed.lang == lang end)<br><br>    cond do<br>      item == nil and lang == :en -&gt; List.first(items)<br>      item == nil -&gt; Enum.find(items, List.first(items), fn embed -&gt; embed.lang == :en end)<br>      true -&gt; item<br>    end<br>  end<br>end</pre><p>This implements the Ash.Resource.Calculation behaviour. As such, we implement init(opts) , load(query, opts, context) , and calculate(records, opts, context) .</p><p>init(opts) verifies that we got a field name as an atom. Well, we don’t verify that the field name actually exists. But we do at least make sure that we’ve got the parameter we need.</p><p>load(_query, opts, _context) simply returns the value of the field name that was passed as an option.</p><p>And calculate(records, [field: field], %{arguments: %{lang: lang}}) is where the work happens. I use pattern matching in the function signature to extract both the field name and language specified. The function simply loops through all of the records and returns the embedded fragment based on:</p><ol><li>the language specified</li><li>English</li><li>the first item found</li></ol><p>If there is an embedded Name or Abbr, one will be returned. And this calculation may be used with any JSONB fragment that has a :lang attribute (PersonName is another variant I’ll want in the future).</p><h3><strong>Getting Close</strong></h3><p>I went down a number of dead ends when trying to get this to work.</p><p>Before that, though, let’s see what we’ve got. Open a new iex -S mix session, load our aliases and require Ash.Query so that we have the necessary macros available for querying:</p><pre>alias MyApp.Sports.{Lang, Abbr, Name, League}<br>require Ash.Query<br><br>League |&gt; Ash.Query.for_read(:read) |&gt; Ash.read!()<br>[debug] QUERY OK source=&quot;leagues&quot; db=6.8ms decode=0.7ms queue=3.4ms idle=993.8ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;sid&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 []<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>[<br>  #MyApp.Sports.League&lt;<br>    name: #Ash.NotLoaded&lt;:calculation, field: :name&gt;,<br>    abbr: #Ash.NotLoaded&lt;:calculation, field: :abbr&gt;,<br>    __meta__: #Ecto.Schema.Metadata&lt;:loaded, &quot;leagues&quot;&gt;,<br>    id: 1,<br>    sid: #Ash.CiString&lt;&quot;npb&quot;&gt;,<br>    abbrs: [<br>      #MyApp.Sports.Abbr&lt;<br>        __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>        lang: :en,<br>        abbr: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>        abbr_ruby: nil,<br>        ...</pre><p>We have our :name and :abbr singular fields, but they aren’t loaded. Okay, so let’s load them in the read :read block:</p><pre># lib/my_app/sports/league.xs<br><br>    ...<br>    read :read do<br>      prepare build(load: [:name, :abbr])<br>      primary? true<br><br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end<br>    ...</pre><p>This preloads the :name and :abbr calculations automatically. Perfect. And if we run it after recompiling in iex :</p><pre>League |&gt; Ash.Query.for_read(:read) |&gt; Ash.read!()                                                                                                                                    16:44:07 [248/1857]<br>[debug] QUERY OK source=&quot;leagues&quot; db=0.7ms idle=1488.5ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;sid&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 []<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>[<br>  #MyApp.Sports.League&lt;<br>    name: #MyApp.Sports.Name&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :en,<br>      common_name: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>      full_name: #Ash.CiString&lt;&quot;Nippon Professional Baseball&quot;&gt;,<br>      common_name_ruby: nil,<br>      full_name_ruby: nil,<br>      aggregates: %{},<br>      calculations: %{},<br>      ...<br>    &gt;,<br>    abbr: #MyApp.Sports.Abbr&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :en,<br>      abbr: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>      ...</pre><p>That looks like it works! Excellent!</p><p>What happens if we pass a :lang argument?</p><pre>League |&gt; Ash.Query.for_read(:read, %{lang: :ja}) |&gt; Ash.read!()                                                                                                                                    16:44:07 [248/1857]<br>[debug] QUERY OK source=&quot;leagues&quot; db=0.7ms idle=1488.5ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;sid&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 []<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>[<br>  #MyApp.Sports.League&lt;<br>    name: #MyApp.Sports.Name&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :en,<br>      common_name: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>      full_name: #Ash.CiString&lt;&quot;Nippon Professional Baseball&quot;&gt;,<br>      common_name_ruby: nil,<br>      full_name_ruby: nil,<br>      aggregates: %{},<br>      calculations: %{},<br>      ...<br>    &gt;,<br>    abbr: #MyApp.Sports.Abbr&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :en,<br>      abbr: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>      ...</pre><p>It’s the same! the %{lang: :ja} arguments passed to for_read didn’t seem to have any effect. All examples with arguments that I’ve seen have been for filtering or sorting. The default :en within the calculate definition seems to take precidence here.</p><h3><strong>What Worked!</strong></h3><p>I abandoned the prepare build and figured out how to get the :lang that I wanted for the calculated :name and :abbr. After removing the prepare statement from read :read, this query is what got it working:</p><pre>League |&gt; Ash.Query.for_read(:read) |&gt; Ash.Query.load(abbr: [lang: :ja]) |&gt; Ash.Query.load(name: [lang: :ja]) |&gt; Ash.read!()<br>[debug] QUERY OK source=&quot;leagues&quot; db=18.3ms idle=1231.3ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;sid&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 []<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>[<br>  #MyApp.Sports.League&lt;<br>    name: #MyApp.Sports.Name&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :ja,<br>      common_name: #Ash.CiString&lt;&quot;プロ野球&quot;&gt;,<br>      full_name: #Ash.CiString&lt;&quot;一般社団法人日本野球機構&quot;&gt;,<br>      common_name_ruby: &quot;エンピービー&quot;,<br>      full_name_ruby: &quot;いっぱんしゃだんほうじんにっぽんやきゅうきこう&quot;,<br>      aggregates: %{},<br>      calculations: %{},<br>      ...<br>    &gt;,<br>    abbr: #MyApp.Sports.Abbr&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :ja,<br>      abbr: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>      abbr_ruby: &quot;エンピービー&quot;,<br>      aggregates: %{},<br>      calculations: %{},<br>      ...<br>    &gt;,<br>    __meta__: #Ecto.Schema.Metadata&lt;:loaded, &quot;leagues&quot;&gt;,<br>    id: 1,<br>    ...</pre><p>That’s it! It got the :name and :abbr in :lang == :ja! Excellent!</p><p>However, having to include a pair of Ash.Query.load for every query seems kind of much. There must be a way to do it.</p><p>After reading over the documentation numerous times, and finding almost solutions on the Elixir Forum, Stack Overflow, and Ash-HQ.com forums, an experimentation finally paid off:</p><pre># lib/my_app/sports/league.ex<br><br>    ...<br>    read :read do<br>      prepare build(load: [name: expr(%{lang: ^arg(:lang)}), abbr: expr(%{lang: ^arg(:lang)})])<br>      primary? true<br><br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end<br>    ...</pre><p>recompile and specify %{lang: :ko} in the parameters of for_read and, pulling out the KBO record, we get:</p><pre>League |&gt; Ash.Query.for_read(:read, %{lang: :ko}) |&gt; Ash.read!()                                                                                                                                    20:06:34 [248/1835]<br>[debug] QUERY OK source=&quot;leagues&quot; db=13.4ms decode=0.1ms idle=1503.6ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;sid&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 []<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>[<br>...<br>  #MyApp.Sports.League&lt;<br>    name: #MyApp.Sports.Name&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :ko,<br>      common_name: #Ash.CiString&lt;&quot;KBO 리그&quot;&gt;,<br>      full_name: #Ash.CiString&lt;&quot;한국야구위원회&quot;&gt;,<br>      common_name_ruby: nil,<br>      full_name_ruby: nil,<br>      aggregates: %{},<br>      calculations: %{},<br>      ...<br>    &gt;,<br>    abbr: #MyApp.Sports.Abbr&lt;<br>      __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>      lang: :ko,<br>      abbr: #Ash.CiString&lt;&quot;KBO 리그&quot;&gt;,<br>      abbr_ruby: nil,<br>      aggregates: %{},<br>      calculations: %{},<br>      ...<br>    &gt;,<br>    __meta__: #Ecto.Schema.Metadata&lt;:loaded, &quot;leagues&quot;&gt;,<br>    id: 2,<br>    sid: #Ash.CiString&lt;&quot;kbo&quot;&gt;,<br>    ...</pre><p>Beautiful! When I got the :name and :abbr that I specified for the first time, I felt like I had created fire! This problem had stumpped me for days. It seemed like something that should be possible, and it is!</p><h3><strong>Get a Single League</strong></h3><p>With that hurdle out of the way, it should be easy to get a record for just a single league, right? Well, let’s see.</p><pre># lib/my_app/sports/league.ex<br><br>    ...<br>    read :by_league do<br>      prepare build(load: [name: expr(%{lang: ^arg(:lang)}), abbr: expr(%{lang: ^arg(:lang)})])<br>      filter expr(sid == ^arg(:sid))<br>      get? true<br><br>      argument :sid, :string do<br>        allow_nil? false<br>      end<br><br>      argument :lang, Lang do<br>        allow_nil? false<br>        default :en<br>      end<br>    end<br>    ...</pre><p>The read :by_league action adds a filter and the :sid argument for the human readable league ID. And how do we access it?</p><pre>League |&gt; Ash.Query.for_read(:by_league, %{sid: &quot;kbo&quot;, lang: :ko}) |&gt; Ash.read_one!()                                                                                                                20:17:45 [78/1994]<br>[debug] QUERY OK source=&quot;leagues&quot; db=11.9ms idle=1291.7ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;sid&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 WHERE (l0.&quot;sid&quot;::citext = $1::citext) [#Ash.CiString&lt;&quot;kbo&quot;&gt;]<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>#MyApp.Sports.League&lt;<br>  name: #MyApp.Sports.Name&lt;<br>    __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>    lang: :ko,<br>    common_name: #Ash.CiString&lt;&quot;KBO 리그&quot;&gt;,<br>    full_name: #Ash.CiString&lt;&quot;한국야구위원회&quot;&gt;,<br>    common_name_ruby: nil,<br>    full_name_ruby: nil,<br>    aggregates: %{},<br>    calculations: %{},<br>    ...<br>  &gt;,<br>  abbr: #MyApp.Sports.Abbr&lt;<br>    __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>    lang: :ko,<br>    abbr: #Ash.CiString&lt;&quot;KBO 리그&quot;&gt;,<br>    abbr_ruby: nil,<br>    aggregates: %{},<br>    calculations: %{},<br>    ...<br>  &gt;,<br>  __meta__: #Ecto.Schema.Metadata&lt;:loaded, &quot;leagues&quot;&gt;,<br>  id: 2,<br>  sid: #Ash.CiString&lt;&quot;kbo&quot;&gt;,<br>  ...</pre><p>We get the single League with the :name and :abbr in Korean! Oh, I’m very much warming to Ash. This is beautiful.</p><p>I’ve started and stopped porting my database over to a number of different database solutions over the past several years. Scalability and occasional race condition lock-ups with eXist (native XML database) prompted me to look for other possible solutions. I dream of eXist written in Elixir instead of Java. (Elixir has overtaken XQuery as my favorite language. Both are functional languages.) Postgres’ JSONB support gives me the multi-dimentionality that I like with a native XML database — where all data that belongs together is together rather than sharded all over the place. But the SQL extensions in PSQL aren’t nearly as elegant as XQuery.</p><p>Ash, so far, feels like it may cover up some of those blemishes.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=eb152236b80b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Multilingual Names with Postgres, Elixir, and Ash]]></title>
            <link>https://medium.com/@westbaystars/multilingual-names-with-postgres-elixir-and-ash-6951c729bb43?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/6951c729bb43</guid>
            <category><![CDATA[exist]]></category>
            <category><![CDATA[ash]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Wed, 25 Sep 2024 13:24:57 GMT</pubDate>
            <atom:updated>2024-09-27T02:58:50.898Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/700/0*od2CjNCZKdf2rLhb.png" /><figcaption>The Nippon Professional Baseball league document</figcaption></figure><h3><strong>Introduction</strong></h3><p>This is a follow up to the <a href="https://medium.com/@westbaystars/multilingual-names-with-postgres-and-elixir-d82dc4a3aa3e">Multilingual Names with Postgres and Elixir</a> post that I did a couple of years ago. Unfortunately, I got very busy soon after that and didn’t get back to that project of porting my database from eXist (native XML database) to Postgres. I’m starting that project back up, but using Ash for the database layer.</p><h3><strong>Setup</strong></h3><p>I’m assuming that you have an Elixir development environment already. This will eventually be a web application, so let’s start by initializing a new Phoenix app:</p><pre>mix phx.new my_app &amp;&amp; cd my_app</pre><p>Go ahead and fetch and install dependencies when prompted.</p><p>And speaking of dependencies, let’s add Ash’s ignitor to the dependencies list in mix.exs .</p><pre>    {:igniter, &quot;~&gt; 0.3&quot;}</pre><p>Now get and compile the new dependency.</p><pre>mix deps.get<br>mix deps.compile</pre><p>Then we want to add the following Ash dependencies with ignitor.</p><pre>mix igniter.install spark<br>mix igniter.install ash<br>mix igniter.install ash_postgres</pre><p>One of the great features of ignitor is that it will make necessary changes to you configuration and other boilerplate files for you, and always prompt you for approval before changing anything. One of the bigger changes is that it will change your Repo from using Ecto.Repo to AshPostgres.Repo. Many things that used to use Ecto now use Ash. :ash_postgres installation will prompt you for the lowest Postgres version major.minor.patch is that you use. If it is lower than version 16.0.0 then please enter it — otherwise the default will do.</p><h3>Starting Simple</h3><p>With everything now set up, once again, let’s start off with something easy. I’ve got some league documents (records) that contain multilingual metadata information about the leagues’ names. Person names are more complicated, so leagues are a good place to start.</p><p>First, let’s take a look at what the XML document for a league looks like.</p><pre>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;<br>&lt;y:league xmlns:y=&quot;http://scoutdragon.com/schema/yakyu-ml.xsd&quot; id=&quot;npb&quot;&gt;<br>    &lt;y:league-metadata&gt;<br>        &lt;y:name lang=&quot;en&quot; shortname=&quot;NPB&quot; fullname=&quot;Nippon Professional Baseball&quot;/&gt;<br>        &lt;y:name lang=&quot;ja&quot; shortname=&quot;プロ野球&quot; fullname=&quot;一般社団法人日本野球機構&quot; shortname-ruby=&quot;ぷろやきゅう&quot; fullname-ruby=&quot;いっぱんしゃだんほうじんにっぽんやきゅうきこう&quot;/&gt;<br>    &lt;/y:league-metadata&gt;<br>&lt;/y:league&gt;</pre><p>Whereas we used the mix phx.gen.live to initialize the database and a CRUD set of pages, Ash just focuses on the declaring what the data looks like. The file structure is similar in that one has all of one’s data declarations under a common context directory. Since all of the data I’m dealing with are related to sports (SportsML was the original insipiration of all of my schemas), let’s call the context sports and add a league.ex file for declaring the league.</p><pre># lib/my_app/sports/league.ex<br><br>defmodule MyApp.Sports.League do<br>  use Ash.Resource,<br>    domain: MyApp.Sports,<br>    data_layer: AshPostgres.DataLayer<br><br>  alias MyApp.Sports.{Abbr, Name}<br><br>  attributes do<br>    attribute :id, :integer do<br>      writable? false<br>      generated? true<br>      primary_key? true<br>      allow_nil? false<br>    end<br><br>    attribute :sid, :ci_string do<br>      allow_nil? false<br>      public? true<br>    end<br><br>    attribute :abbrs, {:array, Abbr}, public?: true<br>    attribute :names, {:array, Name}, public?: true<br><br>    attribute :inserted_at, :utc_datetime_usec do<br>      writable? false<br>      default &amp;DateTime.utc_now/0<br>      match_other_defaults? true<br>      allow_nil? false<br>    end<br><br>    attribute :updated_at, :utc_datetime_usec do<br>      writable? false<br>      default &amp;DateTime.utc_now/0<br>      update_default &amp;DateTime.utc_now/0<br>      match_other_defaults? true<br>      allow_nil? false<br>    end<br>  end<br><br>  postgres do<br>    table &quot;leagues&quot;<br>    repo MyApp.Repo<br>  end<br>end</pre><p>I have 6 fields for this table:</p><ul><li>:id — an auto-generated integer</li><li>:sid — a case insensitive string as a human readable ID</li><li>:abbrs — an array of embedded (JSON) abbreviations</li><li>:names — an array of embedded (JSON) names</li><li>:inserted_at — the timestamp of when the record was inserted</li><li>:updated_at — the timestamp of when the record was last updated</li></ul><p>When setting up an Ecto schema, the :id, :inserted_at, and :updated_at fields are all inferred. I actually prefer Ash’s approach to declare them. After toying with the idea of having the :id be an UUID, I’ve finally decided to make it a sequential integer. Unlike user IDs or bank accounts, no harm can come from guessing what the next ID number will be for a league. It’s also easier to manually enter should I want to call up a record.</p><p>As before, the abbreviations and names are embeddedschemas and need to be declared separately.</p><h3>Abbreviations</h3><p>Abbreviations are very simple.</p><pre># lib/my_app/sports/abbr.ex<br><br>defmodule MyApp.Sports.Abbr do<br>  use Ash.Resource,<br>    data_layer: :embedded,<br>    embed_nil_values?: false<br><br>  attributes do<br>    attribute :lang, MyApp.Sports.Lang do<br>      allow_nil? false<br>      default :en<br>    end<br><br>    attribute :abbr, :ci_string do<br>      public? true<br>      allow_nil? false<br>    end<br><br>    attribute :abbr_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br>  end<br><br>  validations do<br>    validate present([:lang, :abbr])<br>  end<br>end</pre><p>Like before, we have:</p><ul><li>:lang — the language code for the abbreviation</li><li>:abbr — the abbreviation as a case insensitive string</li><li>:abbr_ruby — the Japanese pronunciation string for the abbreviation</li></ul><h3>What is Ruby?</h3><p>If you aren’t familiar with Japanese, it has a pronunciation “alphabet” that is often written on top of words (or to the right of words when writing top to bottom) as a guide to reading the characters. In LaTeX and other publishing software, this is referred to as a ruby line. Take &quot;漢字&quot; (<em>kanji</em>) for example. To have its pronunciation (&quot;かんじ&quot;) written over it in LaTeX, one would write:</p><pre>\ruby{漢字}{かんじ}</pre><p>It also comes in handy for sorting in Japanese, ordering by how words are pronounced rather than their Unicode code value.</p><p>But this isn’t a Japanese lesson. Please accept that it’s a handy field to have in my work.</p><h3><strong>Languages</strong></h3><p>Also note that langis ofMyApp.Sports.Lang type. Before, we set them to be one of @langs ~w(end ja ko zh_cn zh_tw es)a. I imagine that I should have set them to an Ecto.Enum type. I don’t know why, but Ash makes that easier:</p><pre># lib/my_app/sports.lang.ex<br><br>defmodule MyApp.Sports.Lang do<br>  use Ash.Type.Enum, values: [<br>    en: &quot;English&quot;,<br>    ja: &quot;Japanese&quot;,<br>    ko: &quot;Korean&quot;,<br>    zh_cn: &quot;Mainland Chinese&quot;,<br>    zh_tw: &quot;Taiwan Chinese&quot;,<br>    es: &quot;Spanish&quot;<br>  ]<br>end</pre><p>One of the things that I like about the Ash.Type.Enum module is that it supports value descriptions.</p><h3>Names</h3><p>Names are only slightly more detailed than abbreviations.</p><pre># lib/my_app/sports/name.ex<br><br>defmodule MyApp.Sports.Name do<br>  use Ash.Resource,<br>    data_layer: :embedded,<br>    embed_nil_values?: false<br><br>  attributes do<br>    attribute :lang, MyApp.Sports.Lang do<br>      allow_nil? false<br>      default :en<br>    end<br><br>    attribute :common_name, :ci_string do<br>      public? true<br>      allow_nil? false<br>    end<br><br>    attribute :full_name, :ci_string do<br>      public? true<br>      allow_nil? false<br>    end<br><br>    attribute :common_name_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br><br>    attribute :full_name_ruby, :string do<br>      public? true<br>      allow_nil? true<br>    end<br>  end<br><br>  validations do<br>    validate present([:lang, :common_name, :full_name])<br>  end<br>end</pre><p>Here, for a given :lang I’ve got a :common_nameand a :full_name, both with pronunciation fields for Japanese. The only fields required here are :lang, :common_name, and :full_name.</p><p>The :lang is again pulled from the MyApp.Sports.Lang type. This was something I wanted to consolidate in my previous incarnation of this porting project.</p><h3>The Sports Domain</h3><p>Finally, all of the resources that can go into a table need to go into an Ash.Domain. I think of it as the context where we’re going to put the schemas for teams, players, etc.</p><pre># lib/my_app/sports/sports.ex<br><br>defmodule MyApp.Sports do<br>  use Ash.Domain<br><br>  resources do<br>    resource MyApp.Sports.League<br>  end<br>end</pre><p>The only thing we do in the Domain declaration is to list the resources. gwhere we have the embedded abbreviations and names declared with embeds_many. I&#39;m going with the default on_replace option of :raise. I&#39;m not yet sure about the ramifications of this, but I&#39;ll learn as I go. I just don&#39;t like the idea of having to delete and re-insert all of the options when something is updated.</p><h3>The Migration</h3><p>With our database tables now defined, let’s create the necessary migration to create the tables. I’m assuming that you have the necessary MyApp.Repo settings set in config/dev.exs.</p><p>To create the necessary migration, run:</p><pre>mix ash_postgres.generate_migrations --name add_league</pre><p>Whoops. We get an error:</p><pre>warning: No domains found, so no resource-related migrations will be generated.<br>Pass the `--domains` option or configure `config :your_app, ash_domains: [...]`<br>...</pre><p>ignitor didn’t know about our domains when Ash was installed, so it was unable to update config/config.exs appropriately at the time. So let’s add the following to config/config.exs after the config :ash block:</p><pre>config :my_app,<br>  ash_domains: [MyApp.Sports]</pre><p>And with that, let’s run our migration generator again:</p><pre>mix ash_postgres.generate_migrations --name add_league<br><br>...<br>Generating Migrations:<br>* creating priv/repo/migrations/20240925111552_add_league.exs</pre><p>Excellent. The migration was created. You’re welcome to check it out. It has two functions: up and down for creating and dropping the leagues table respectively.</p><p>Now let’s run the migration with:</p><pre>mix ash_postgres.migrate<br><br>20:24:42.911 [error] Postgrex.Protocol (#PID&lt;0.189.0&gt;) failed to connect: ** (Postgrex.Error) FATAL 3D000 (invalid_catalog_name) database &quot;my_app_dev&quot; does not exist</pre><p>Ah, we haven’t yet created the database. This the the regular mix ecto.create to initialize the database, then we can run the migration again.</p><pre>mix ash_postgres.migrate<br><br>...<br>20:27:42.149 [info] create table leagues<br>** (Postgrex.Error) ERROR 42704 (undefined_object) type &quot;citext&quot; does not exist<br>...</pre><p>Ah, I haven’t installed the citext extension to this database. To do that, I need to add &quot;citext&quot; to the installed_extensions in lib/my_app/repo.ex:</p><pre>defmodule MyApp.Repo do<br>  ...<br><br>  def installed_extensions do<br>    # Add extensions here, and the migration generator will install them.<br>    [&quot;ash-functions&quot;, &quot;citext&quot;]<br>  end<br><br>...<br>end</pre><p>To add this extension to the database, we need to generate another migration:</p><pre>mix ash_postgres.generate_migrations --name add_citext_to_db<br>Compiling 1 file (.ex)<br><br>Extension Migrations:<br>* creating priv/resource_snapshots/repo/extensions.json<br>* creating priv/repo/migrations/20240925115313_install_1_extensions.exs<br><br>Generating Tenant Migrations:<br><br>Generating Migrations:<br>No changes detected, so no migrations or snapshots have been created.</pre><p>Because no table migrations were generated, the --name add_citext_to_db is ignored and &lt;timestamp&gt;_install_1_extensions.exe migration is created.</p><p>Now things get tricky. Because our &lt;timestamp&gt;_add_league.exs migration runs first, and it needs the :ci_text type, we need the &quot;citext&quot; extension installed first. That’s actually easy enough to do. Take the following line out of the ...install_1_extensions.exs migration and paste it before the create table statement in the ...add_league.exs migration:</p><pre>use Ecto.Migration<br><br>  def up do<br>    execute(&quot;CREATE EXTENSION IF NOT EXISTS \&quot;citext\&quot;&quot;)<br><br>    create table(:leagues, primary_key: false) do<br>      add :id, :bigserial, null: false, primary_key: true<br>      ...</pre><p>Now let’s run the migration(s):</p><pre>mix ash_postgres.migrate<br><br>21:08:40.982 [info] == Running 20240925111552 MyApp.Repo.Migrations.AddLeague.up/0 forward<br><br>21:08:40.984 [info] execute &quot;CREATE EXTENSION IF NOT EXISTS \&quot;citext\&quot;&quot;<br><br>21:08:41.095 [info] create table leagues<br><br>21:08:41.108 [info] == Migrated 20240925111552 in 0.1s<br><br>21:08:41.133 [info] == Running 20240925115313 MyApp.Repo.Migrations.Install1Extensions20240925115311.up/0 forward<br><br>21:08:41.133 [info] execute &quot;CREATE EXTENSION IF NOT EXISTS \&quot;citext\&quot;&quot;<br><br>21:08:41.133 [info] extension &quot;citext&quot; already exists, skipping<br><br>21:08:41.133 [info] == Migrated 20240925115313 in 0.0s</pre><p>It added the :ci_text extension, created the leagues table, then skipped adding the :ci_text extension a second time. (I’m really going to need to remember to add that earlier in the process in the future.)</p><h3><strong>Insert and Query the League Data</strong></h3><p>Before we can insert or query the leagues table, we need to declare some actions. Insert the following actions block to the lib/my_app/sports/league.ex file:</p><pre>  actions do<br>    create :create do<br>      accept [:sid, :abbrs, :names]<br>    end<br><br>    defaults [:read]<br>  end</pre><p>The :create action is for inserting data, while the :read is to select data from a given table. For inserting, one may only pass three parameters, the :sid, :abbrs, and :names. Save that.</p><p>Let’s now add the following to the seeds.exs file:</p><pre># priv/repo/seeds.exs<br><br>alias MyApp.Sports.{League, Abbr, Name}<br><br>League<br>|&gt; Ash.Changeset.for_create(<br>  :create, %{<br>    sid: &quot;npb&quot;,<br>    abbrs: [<br>      %Abbr{lang: :en, abbr: &quot;NPB&quot;},<br>      %Abbr{lang: :ja, abbr: &quot;NPB&quot;, abbr_ruby: &quot;エンピービー&quot;}<br>    ],<br>    names: [<br>      %Name{lang: &quot;en&quot;, common_name: &quot;NPB&quot;, full_name: &quot;Nippon Professional Baseball&quot;},<br>      %Name{lang: &quot;ja&quot;, common_name: &quot;プロ野球&quot;, full_name: &quot;一般社団法人日本野球機構&quot;,<br>        common_name_ruby: &quot;エンピービー&quot;, full_name_ruby: &quot;いっぱんしゃだんほうじんにっぽんやきゅうきこう&quot;}<br>    ]<br>  }<br>)<br>|&gt; Ash.create!()<br><br>League<br>|&gt; Ash.Changeset.for_create(<br>  :create, %{<br>    sid: &quot;kbo&quot;,<br>    abbrs: [<br>      %Abbr{lang: :en, abbr: &quot;KBO&quot;},<br>      %Abbr{lang: :ja, abbr: &quot;KBOリーグ&quot;, abbr_ruby: &quot;ケービーオーリーグ&quot;},<br>      %Abbr{lang: :ko, abbr: &quot;KBO 리그&quot;}<br>    ],<br>    names: [<br>      %Name{lang: &quot;en&quot;, common_name: &quot;KBO League&quot;, full_name: &quot;Korea Baseball Organization&quot;},<br>      %Name{lang: &quot;ja&quot;, common_name: &quot;KBOリーグ&quot;, full_name: &quot;韓国野球委員会&quot;,<br>        common_name_ruby: &quot;ケービーオーリーグ&quot;, full_name_ruby: &quot;かんこくやきゅういいんかい&quot;},<br>      %Name{lang: &quot;ko&quot;, common_name: &quot;KBO 리그&quot;, full_name: &quot;한국야구위원회&quot;}<br>    ]<br>  }<br>)<br>|&gt; Ash.create!()<br><br>League<br>|&gt; Ash.Changeset.for_create(<br>  :create, %{<br>    sid: &quot;cpbl&quot;,<br>    abbrs: [<br>      %Abbr{lang: &quot;en&quot;, abbr: &quot;CPBL&quot;},<br>      %Abbr{lang: &quot;ja&quot;, abbr: &quot;台湾プロ野球&quot;, abbr_ruby: &quot;たいわんプロやきゅう&quot;},<br>      %Abbr{lang: &quot;zh_tw&quot;, abbr: &quot;中華職棒&quot;}<br>    ],<br>    names: [<br>      %Name{lang: &quot;en&quot;, common_name: &quot;CPBL&quot;, full_name: &quot;Chinese Professional Baseball League&quot;},<br>      %Name{lang: &quot;ja&quot;, common_name: &quot;中華職棒&quot;, full_name: &quot;中華職業棒球大聯盟&quot;,<br>        common_name_ruby: &quot;たいわんプロやきゅう&quot;, full_name_ruby: &quot;うかしょくぎょうぼうきゅうだいれんめい&quot;},<br>      %Name{lang: &quot;zh_tw&quot;, common_name: &quot;中華職棒&quot;, full_name: &quot;中華職業棒球大聯盟&quot;}<br>    ]<br>  }<br>)<br>|&gt; Ash.create!()</pre><p>You can see that we’re creating an Ash.Changeset with a map for each of NPB, KBO, and CPBL. The embedded structs need to have their structs included, while that isn’t necessary to build the League itself with them.</p><p>Running mix run priv/repo/seeds.exs will insert the three records into the database. In another terminal, you can check that:</p><pre>psql my_app_dev<br>select * from leagues;<br>id | sid  |                                                                                        abbrs                                                                                         |                                                                                                                                                                                                        names                                                                                                                                                                                                        |        inserted_at         |         updated_at<br>----+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+----------------------------<br>  1 | npb  | {&quot;{\&quot;abbr\&quot;: \&quot;NPB\&quot;, \&quot;lang\&quot;: \&quot;en\&quot;}&quot;,&quot;{\&quot;abbr\&quot;: \&quot;NPB\&quot;, \&quot;lang\&quot;: \&quot;ja\&quot;, \&quot;abbr_ruby\&quot;: \&quot;エンピービー\&quot;}&quot;}                                                                   | {&quot;{\&quot;lang\&quot;: \&quot;en\&quot;, \&quot;full_name\&quot;: \&quot;Nippon Professional Baseball\&quot;, \&quot;common_name\&quot;: \&quot;NPB\&quot;}&quot;,&quot;{\&quot;lang\&quot;: \&quot;ja\&quot;, \&quot;full_name\&quot;: \&quot;一般社団法人日本野球機構\&quot;, \&quot;common_name\&quot;: \&quot;プロ野球\&quot;, \&quot;full_name_ruby\&quot;: \&quot;いっぱんしゃだんほうじんにっぽんやきゅうきこう\&quot;, \&quot;common_name_ruby\&quot;: \&quot;エンピービー\&quot;}&quot;}                                                                                                  | 2024-09-25 12:39:45.448578 | 2024-09-25 12:39:45.448578<br>  2 | kbo  | {&quot;{\&quot;abbr\&quot;: \&quot;KBO\&quot;, \&quot;lang\&quot;: \&quot;en\&quot;}&quot;,&quot;{\&quot;abbr\&quot;: \&quot;KBOリーグ\&quot;, \&quot;lang\&quot;: \&quot;ja\&quot;, \&quot;abbr_ruby\&quot;: \&quot;ケービーオーリーグ\&quot;}&quot;,&quot;{\&quot;abbr\&quot;: \&quot;KBO 리그\&quot;, \&quot;lang\&quot;: \&quot;ko\&quot;}&quot;}          | {&quot;{\&quot;lang\&quot;: \&quot;en\&quot;, \&quot;full_name\&quot;: \&quot;Korea Baseball Organization\&quot;, \&quot;common_name\&quot;: \&quot;KBO League\&quot;}&quot;,&quot;{\&quot;lang\&quot;: \&quot;ja\&quot;, \&quot;full_name\&quot;: \&quot;韓国野球委員会\&quot;, \&quot;common_name\&quot;: \&quot;KBOリーグ\&quot;, \&quot;full_name_ruby\&quot;: \&quot;かんこくやきゅういいんかい\&quot;, \&quot;common_name_ruby\&quot;: \&quot;ケービーオーリーグ\&quot;}&quot;,&quot;{\&quot;lang\&quot;: \&quot;ko\&quot;, \&quot;full_name\&quot;: \&quot;한국야구위원회\&quot;, \&quot;common_name\&quot;: \&quot;KBO 리그\&quot;}&quot;}                            | 2024-09-25 12:39:45.498108 | 2024-09-25 12:39:45.498108<br>  3 | cpbl | {&quot;{\&quot;abbr\&quot;: \&quot;CPBL\&quot;, \&quot;lang\&quot;: \&quot;en\&quot;}&quot;,&quot;{\&quot;abbr\&quot;: \&quot;台湾プロ野球\&quot;, \&quot;lang\&quot;: \&quot;ja\&quot;, \&quot;abbr_ruby\&quot;: \&quot;たいわんプロやきゅう\&quot;}&quot;,&quot;{\&quot;abbr\&quot;: \&quot;中華職棒\&quot;, \&quot;lang\&quot;: \&quot;zh_tw\&quot;}&quot;} | {&quot;{\&quot;lang\&quot;: \&quot;en\&quot;, \&quot;full_name\&quot;: \&quot;Chinese Professional Baseball League\&quot;, \&quot;common_name\&quot;: \&quot;CPBL\&quot;}&quot;,&quot;{\&quot;lang\&quot;: \&quot;ja\&quot;, \&quot;full_name\&quot;: \&quot;中華職業棒球大聯盟\&quot;, \&quot;common_name\&quot;: \&quot;中華職棒\&quot;, \&quot;full_name_ruby\&quot;: \&quot;うかしょくぎょうぼうきゅうだいれんめい\&quot;, \&quot;common_name_ruby\&quot;: \&quot;たいわんプロやきゅう\&quot;}&quot;,&quot;{\&quot;lang\&quot;: \&quot;zh_tw\&quot;, \&quot;full_name\&quot;: \&quot;中華職業棒球大聯盟\&quot;, \&quot;common_name\&quot;: \&quot;中華職棒\&quot;}&quot;} | 2024-09-25 12:39:45.499857 | 2024-09-25 12:39:45.499857<br>(3 rows)</pre><p>Notice how the _ruby field is not included for non-Japanese entries for both abbrs and names? That is the embedded_nil_values?: false at work. I like it.</p><p>And how do we query the leagues?</p><p>Let’s start up an iex -S mix session and type:</p><pre>alias MyApp.Sports.{Lang, Abbr, Name, League}   # Handy aliases<br>require Ash.Query                               # For filter and other macros<br><br>League |&gt; Ash.Query.filter(sid == &quot;npb&quot;) |&gt; Ash.read!()<br>[debug] QUERY OK source=&quot;leagues&quot; db=13.3ms queue=2.6ms idle=1469.9ms<br>SELECT l0.&quot;id&quot;, l0.&quot;names&quot;, l0.&quot;sid&quot;, l0.&quot;abbrs&quot;, l0.&quot;inserted_at&quot;, l0.&quot;updated_at&quot; FROM &quot;leagues&quot; AS l0 WHERE (l0.&quot;sid&quot;::citext = $1::citext) [#Ash.CiString&lt;&quot;npb&quot;&gt;]<br>↳ anonymous fn/3 in AshPostgres.DataLayer.run_query/2, at: lib/data_layer.ex:769<br>[<br>  #MyApp.Sports.League&lt;<br>    __meta__: #Ecto.Schema.Metadata&lt;:loaded, &quot;leagues&quot;&gt;,<br>    id: 1,<br>    sid: #Ash.CiString&lt;&quot;npb&quot;&gt;,<br>    abbrs: [<br>      #MyApp.Sports.Abbr&lt;<br>        __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>        lang: :en,<br>        abbr: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>        abbr_ruby: nil,<br>        aggregates: %{},<br>        calculations: %{},<br>        ...<br>      &gt;,<br>      #MyApp.Sports.Abbr&lt;<br>        __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>        lang: :ja,<br>        abbr: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>        abbr_ruby: &quot;エンピービー&quot;,<br>        aggregates: %{},<br>        calculations: %{},<br>        ...<br>      &gt;<br>    ],<br>    names: [<br>      #MyApp.Sports.Name&lt;<br>        __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>        lang: :en,<br>        common_name: #Ash.CiString&lt;&quot;NPB&quot;&gt;,<br>        full_name: #Ash.CiString&lt;&quot;Nippon Professional Baseball&quot;&gt;,<br>        common_name_ruby: nil,<br>        full_name_ruby: nil,<br>        aggregates: %{},<br>        calculations: %{},<br>        ...<br>      &gt;,<br>      #MyApp.Sports.Name&lt;<br>        __meta__: #Ecto.Schema.Metadata&lt;:built, &quot;&quot;&gt;,<br>        lang: :ja,<br>        common_name: #Ash.CiString&lt;&quot;プロ野球&quot;&gt;,<br>        full_name: #Ash.CiString&lt;&quot;一般社団法人日本野球機構&quot;&gt;,<br>        common_name_ruby: &quot;エンピービー&quot;,<br>        full_name_ruby: &quot;いっぱんしゃだんほうじんにっぽんやきゅうきこう&quot;,<br>        aggregates: %{},<br>        calculations: %{},<br>        ...<br>      &gt;<br>    ],<br>    inserted_at: ~U[2024-09-25 12:39:45.448578Z],<br>    updated_at: ~U[2024-09-25 12:39:45.448578Z],<br>    aggregates: %{},<br>    calculations: %{},<br>    ...<br>  &gt;<br>]</pre><p>That worked very well. And it was a lot easier than my previous experiments with this, especially getting the embedded fields working.</p><h3>Getting a League with Just One Language</h3><p>And this is where things got difficult last time, and I’m still struggling this time. The way I really want this to work is to have the :abbrs and :names in the database as they are now, but set to public?: false. In their place will be :abbr and :name with the requested :lang or :en by default with the first one as the final fallback (if :en is not available).</p><p>This seems like a job for calculations. I’m still working on it.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6951c729bb43" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Deploying Elixir App with Custom Domain to Fly.io]]></title>
            <link>https://medium.com/@westbaystars/deploying-elixir-app-with-custom-domain-to-fly-io-013f6eea00cf?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/013f6eea00cf</guid>
            <category><![CDATA[elixirlang]]></category>
            <category><![CDATA[hosting-service]]></category>
            <category><![CDATA[deployment]]></category>
            <category><![CDATA[elixir]]></category>
            <category><![CDATA[flyio]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Wed, 04 Sep 2024 13:44:14 GMT</pubDate>
            <atom:updated>2024-09-04T13:44:14.353Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ctmAnPoxsr1aF9Usdh_PPg.png" /><figcaption>expasswd.org on Fly.io</figcaption></figure><p>First, the problem to be solved.</p><p>I created <a href="https://github.com/westbaystars/exk_passwd">an Elixir application</a>, deployed it to Fly.io, and it ran. So far, so good. But as soon as I added a <a href="https://fly.io/docs/networking/custom-domain/">custom domain name</a> and added a certificate by way of:</p><pre>fly certs add &quot;*.exkpasswd.org&quot;</pre><p>the Live Logs went crazy with the application repeatedly crashing.</p><p>I was in too much of a panic to read the error logs as they flew by, so I shut the machines down and scrolled through the error logs at a more relaxed pace. Here is what I kept getting (timestamps removed for clarity):</p><pre>[info] CONNECTED TO Phoenix.LiveView.Socket in 36µs<br>Transport: :longpoll<br>Serializer: Phoenix.Socket.V2.JSONSerializer<br>Parameters: %{&quot;_csrf_token&quot; =&gt; &quot;&lt;censored&gt;&quot;, ...}<br>[error] Could not check origin for Phoenix.Socket transport.<br>Origin of the request: https://www.exkpasswd.org<br>This happens when you are attempting a socket connection to<br>a different host than the one configured in your config/<br>files. For example, in development the host is configured<br>to &quot;localhost&quot; but you may be trying to access it from<br>&quot;127.0.0.1&quot;. To fix this issue, you may either:<br>1. update [url: [host: ...]] to your actual host in the<br>config file for your current environment (recommended)<br>2. pass the :check_origin option when configuring your<br>endpoint or when configuring the transport in your<br>UserSocket module, explicitly outlining which origins<br>are allowed:<br>check_origin: [&quot;https://example.com&quot;,<br>&quot;//another.com:888&quot;, &quot;//other.com&quot;]</pre><p>Okay. I’m definitely not accessing 127.0.0.1 or localhost. So that’s not the problem. The host name is most likely in the fly.toml file that was created when I deployed the application.</p><p>Yep, under [env] → PHX_HOST is the fly.dev host name. But I still want it to work along with exkpasswd.org and <a href="http://www.exkpasswd.org./">www.exkpasswd.org</a>. Changing that only fixes one of the host names.</p><p>Chris McCord chimed in on <a href="https://community.fly.io/t/phoenix-liveview-constantly-refreshes-with-custom-domain/3384/2">this thread</a> and made everything clear: instead of the following in config/runtime.exs:</p><pre>  config :exk_passwd, EXKPasswdWeb.Endpoint,<br>    url: [host: host, port: 443, scheme: &quot;https&quot;],</pre><p>I needed something more like:</p><pre>config :exk_passwd, EXKPasswdWeb.Endpoint,<br>  check_origin: [&quot;//exkpasswd.org&quot;, &quot;//www.exkpasswd.org&quot;, //#{host}]</pre><p>I added my custom domain to the fly.toml file, then read it in from System.get_env(“DNS_HOST”), and modified the check_origin as so:</p><pre>    check_origin: [<br>      &quot;//#{host}&quot;,<br>      &quot;//#{dns_host}&quot;,<br>      &quot;//www.#{dns_host}&quot;<br>    ],</pre><p>Now I can manage the host name in the fly.toml file and access all three domains with an SSL certificate properly.</p><p>Future me will now know to take this extra configuration step when deploying in the future.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=013f6eea00cf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Experiments with QR Codes in Livebook]]></title>
            <link>https://medium.com/@westbaystars/experiments-with-qr-codes-in-livebook-7015649e18f6?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/7015649e18f6</guid>
            <category><![CDATA[qrcodegenerator]]></category>
            <category><![CDATA[qr-code]]></category>
            <category><![CDATA[elixir]]></category>
            <category><![CDATA[livebook]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Fri, 03 Nov 2023 05:20:49 GMT</pubDate>
            <atom:updated>2023-11-04T08:06:23.938Z</atom:updated>
            <content:encoded><![CDATA[<p>I run a concierge ticketing service for tourists coming to Japan, mainly focused on Japanese baseball. Teams are more and more embracing QR Code based tickets, which opens up the possibility to make custom tickets that are multi-lingual.</p><p>Let’s take the following ticket as an example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/419/1*5qzDPtXi2effbYGR9sFS6A.png" /><figcaption>Hanshin Tigers Breeze Seat ticket for May 4th, 2023</figcaption></figure><p>Above is what a typical Hanshin Tigers e-ticket looks like before I’ve added an English translation to it.</p><p>What I would like to be able to do is to enter the ticket information into a database for the customer and display it on a mobile device in English for the customer or in Japanese for showing to an usher at the ballpark. As a backup, I currently also print the ticket in a more friendly manner, such as:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/820/1*okOUY7UhYjrmeerZGf7FxA.png" /><figcaption>Translated Hanshin Tigers Breeze Seat ticket for May 4th, 2023</figcaption></figure><p>Because I do it manually, it still looks very much like the original with English on the side. It would be nice to be able to print QR Code tickets in a consistent format for all the teams, which each have their own unique designs.</p><p>The first step to that is to be able to read in the QR code in a way that is reproducible. Let’s play around in Livebook to figure out how that’ll work.</p><h3><strong>QR Code Playground</strong></h3><p>If you’ve already scanned the above QR Code, you know that its contents are 202302088808H19425. Great. But I’d like to be able to automate the reading and creation of the QR Code without having to store the image anywhere. For that, let’s open a new <a href="https://livebook.dev/">Livebook</a> and install :evision and :kino to experiment with.</p><pre>Mix.install([<br>  {:evision, &quot;~&gt; 0.1.33&quot;},<br>  {:kino, &quot;0.11.0&quot;}<br>])</pre><p>Next, I take that first image of the Tigers ticket above and drag and drop it to the + Add file area in the Files section of the Livebook.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*giYvLPJPoXpdszBuhLJaag.png" /></figure><p>That adds it as an attachment to the current Livebook session. Now let’s read it in.</p><pre>alias Evision, as: Cv<br><br>Kino.FS.file_path(&quot;2023-05-04-han-breeze-35-97.png&quot;)<br>|&gt; Cv.imread()</pre><p>I’ve noticed that Evision is often aliased to Cv (for Computer Vision?), so I’ll go with that.</p><p>Here we simply get the path of the attached PNG file and pipe it to Evision’s imread/1 function. This results in the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*ev4GTHS2_yCj9hI8WsRvDw.png" /><figcaption>Read in the e-ticket’s image</figcaption></figure><p>Now let’s pipe that to the Cv.QRCodeDetector to get the contents of the QR Code.</p><pre>|&gt; then(&amp;Cv.QRCodeDetector.detectAndDecode(<br>  Cv.QRCodeDetector.qrCodeDetector(), &amp;1))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/923/1*hkH-CSHlBfcsUV40e3DQXA.png" /><figcaption>Detect and decode the QR Code in the image</figcaption></figure><p>And there we see the decoded string of the QR code! So let’s extract that for later use.</p><pre>alias Evision, as: Cv<br><br>{decoded_string, points, straight_qrcode} =<br>  Kino.FS.file_path(&quot;2023-05-04-han-breeze-35-97.png&quot;)<br>  |&gt; Cv.imread()<br>  |&gt; then(&amp;Cv.QRCodeDetector.detectAndDecode(<br>    Cv.QRCodeDetector.qrCodeDetector(), &amp;1))<br><br>decoded_string</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/923/1*aSBei922JEXP58ZRAqIGFQ.png" /><figcaption>Read in image, detect and decode QR Code, output decoded string</figcaption></figure><h3>Write QR Code</h3><p>Great, we now have the decoded contents of the QR Code which we can save in the database along with the gate, aisle, row, and seat number. While I’m sure that information is also contained within the QR Code, I’m not interested in decrypting it. I just want to reproduce the QR code at any size to output in various ways.</p><p>To that end, that’s very simple with the Evision module.</p><pre>Cv.QRCodeEncoder.encode(Cv.QRCodeEncoder.create(), decoded_string)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/925/1*VbOxk6H0G3WCg3TaAvRMAQ.png" /><figcaption>Create QR Code from decoded string</figcaption></figure><p>That created a QR Code, but it’s rather difficult to see. And I’m sure that it’s too small in the above compressed image to scan. Let’s make it a little bigger.</p><pre>|&gt; Cv.resize({300, 300})</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/927/1*1DugnFoazHjOaejrfC9RUw.png" /><figcaption>Enlarged generated QR Code</figcaption></figure><p>That works. It’s scannable at least. But it’s kind of blurry. Let’s clean that up with an image interpolation algorithm specifically designed for this.</p><pre>|&gt; Cv.resize({300, 300}, interpolation: CV.Constant.cv_INTER_AREA())</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*LMEcYLaDDjSXUKfBZJKb4A.png" /><figcaption>Sharpened generated enlarged QR Code</figcaption></figure><p>That’s much better, nice and sharp. In fact, if you compare it to the QR Code I used for my revised ticket above, you can see that the hard copy I created was expanded from the original, and was thus a slight bit blurrier. This kind of workflow will allow me to output at any size with clarity.</p><h3>More Complex QR Codes</h3><p>The Tigers’ QR Codes are all simple, basic codes. Pretty much all of the other teams use more complex, larger QR Codes. Here is a typical ticket for the Swallows.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/993/1*63Zk6dumaVE8zduF2t6Hfw.png" /><figcaption>Translated Yakult Swallows SS ticket for October 4th, 2023</figcaption></figure><p>After attaching the above image to the Livebook, let’s give it a try.</p><pre>{decoded_string, points, straight_qrcode} =<br>  Kino.FS.file_path(&quot;2023-10-04-yak-ss1-22-18.png&quot;)<br>  |&gt; Cv.imread()<br>  |&gt; then(&amp;Cv.QRCodeDetector.detectAndDecode(<br>    Cv.QRCodeDetector.qrCodeDetector(), &amp;1))<br><br>decoded_string</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/927/1*qpa3qcPAEU8c5Gc6oiJLuw.png" /><figcaption>Get the decoded string of the Swallows ticket</figcaption></figure><p>Great! So far, so good. Now let’s regenerate the QR Code.</p><pre>Cv.QRCodeEncoder.encode(Cv.QRCodeEncoder.create(), decoded_string)<br>|&gt; Cv.resize({300, 300}, interpolation: Cv.Constant.cv_INTER_AREA())</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/925/1*_GsIgfz8hx4qJlKKbclwOQ.png" /><figcaption>Regenerated Swallows ticket QR Code</figcaption></figure><p>Hmmm. It recreated <strong>a</strong> QR Code. But it doesn’t look like the original. Scanning the new one independently comes up with d34a78f948c92e681f66b34b7c34806eeaee98709e3ae76a69d620103d24fb37, which is the same decoded string. It appears to be a Base 16 encoded string, so UTF-8 or Shift JIS? No, the UTF-8 glyph of d34a is 퍊, which is a Korean character, and nothing has the first byte of 0xd3 in Shift JIS.</p><p>Let’s see what happens if we convert the Base 16 string into bytes and feed that back to generate a QR Code.</p><pre>{decoded_string, points, straight_qrcode} =<br>  Kino.FS.file_path(&quot;2023-10-04-yak-ss1-22-18.png&quot;)<br>  |&gt; Cv.imread()<br>  |&gt; then(&amp;Cv.QRCodeDetector.detectAndDecode(Cv.QRCodeDetector.qrCodeDetector(), &amp;1))<br><br>decoded_string<br>|&gt; Base.decode16!(case: :lower)<br>|&gt; IO.inspect()<br>|&gt; then(&amp;Cv.QRCodeEncoder.encode(Cv.QRCodeEncoder.create(), &amp;1))<br>|&gt; Cv.resize({300, 300}, interpolation: Cv.Constant.cv_INTER_AREA())</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*4nmjS2XLyo_JhudzO8iEtg.png" /><figcaption>Regenerated Swallows ticket QR Code after HEX decoding of the decoded value</figcaption></figure><p>Nope. That looks even more data sparse than the second version, which appeared to have lost information from the first.</p><p>Looks like I’m going to need to do some more research to get the bigger QR Codes working. If anybody knows what’s going on here, I’d love to hear from you.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7015649e18f6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Predicting Standings with Elixir and Machine Learning — Attempt 2]]></title>
            <link>https://medium.com/@westbaystars/predicting-standings-with-elixir-and-machine-learning-attempt-2-545e106ba46e?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/545e106ba46e</guid>
            <category><![CDATA[npb-baseball]]></category>
            <category><![CDATA[explorer]]></category>
            <category><![CDATA[livebook]]></category>
            <category><![CDATA[axon]]></category>
            <category><![CDATA[elixir]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Fri, 06 Jan 2023 11:49:35 GMT</pubDate>
            <atom:updated>2023-01-06T11:49:35.194Z</atom:updated>
            <content:encoded><![CDATA[<h3>Predicting Standings with Elixir and Machine Learning — Attempt 2</h3><p>Okay, so my <a href="https://medium.com/@westbaystars/predicting-standings-with-elixir-and-machine-learning-d0b78c6fee53">first attempt to predict team standings via Machine Learning in an Elixir Livebook</a> didn’t work out so well. Having each individual player contributing to a team’s standing created way too much noise for the training algorithm to figure out my intent. Let’s try this again — still with the same source data — but grouped by team for each year.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/356/0*qawSl5Tb2_2orfjd.png" /><figcaption>Nippon Professional Baseball (NPB) Teams</figcaption></figure><h3>The Livebook</h3><p>At the end of the first attempt, this is what our Livebook looks like:</p><p><strong>Notebook dependencies and setup</strong></p><pre>Mix.install([<br>  {:explorer, &quot;~&gt; 0.4&quot;},<br>  {:kino, &quot;~&gt; 0.8&quot;},<br>  {:download, &quot;~&gt; 0.0.4&quot;},<br>  {:exla, &quot;~&gt; 0.4&quot;},<br>  {:axon, &quot;~&gt; 0.3&quot;}<br>])</pre><p><strong>Read in Data</strong></p><pre>alias Explorer.DataFrame, as: DF<br>alias Explorer.Series, as: S<br>alias Explorer.TensorFrame, as: TF<br>require Explorer.DataFrame              # For DF.mutate<br><br>training_file = &quot;training-data-1.tsv&quot;<br>teams_file = &quot;teams.tsv&quot;<br><br>unless File.exists?(training_file),<br>  do:<br>    {:ok, _filename} =<br>      Download.from(<br>        &quot;https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/#{training_file}&quot;<br>      )<br><br>unless File.exists?(teams_file),<br>  do:<br>    {:ok, _filename} =<br>      Download.from(<br>        &quot;https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/#{teams_file}&quot;<br>      )<br><br>df = DF.from_csv!(training_file, delimiter: &quot;\t&quot;)<br>teams = DF.from_csv!(teams_file, delimiter: &quot;\t&quot;)<br>  |&gt; DF.mutate(rank: (rank - 1) / 12)<br><br>Kino.DataTable.new(teams)</pre><p><strong>Data Preparation Module</strong></p><pre>require Explorer.DataFrame    # Needed for DF.mutate<br><br>defmodule Prep do<br>  def get_dummies(df, column, new_column \\ nil) <br>  def get_dummies(df, column, new_column) <br>    when column != nil and new_column == nil, <br>    do: get_dummies(df, column, column)<br>  def get_dummies(df, column, new_column) do<br>    series = DF.pull(df, column)<br>    list = S.to_list(series)<br>    map = list<br>      |&gt; Enum.uniq()<br>      |&gt; Enum.with_index(&amp; {&amp;1, String.to_integer(&quot;#{&amp;2}&quot;)})<br>      |&gt; Enum.into(%{})<br><br>    new_series = S.transform(series, fn key -&gt; Map.get(map, key) end)<br>    <br>    DF.put(df, new_column, new_series)<br>  end<br><br>  def separate(map, id, target) do<br>    {<br>      map |&gt; Enum.map(&amp;Map.get(&amp;1, id)),<br>      if Map.has_key?(List.first(map), target) do<br>        map |&gt; Enum.map(&amp;([[Map.get(&amp;1, target)]] |&gt; Nx.tensor()))<br>      else<br>        nil<br>      end,<br>      map |&gt; Enum.map(&amp;Map.drop(&amp;1, [id, target]))<br>    }<br>  end<br><br><br>  def map_to_tensor(map) do<br>    map<br>    |&gt; Enum.map(&amp; [Map.values(&amp;1)] |&gt; Nx.tensor)<br>  end<br>end</pre><p><strong>Reduct Data to Numbers</strong></p><pre>df = df<br>  |&gt; DF.filter(games_played &gt; 0)<br>  |&gt; Prep.get_dummies(&quot;team&quot;, &quot;team_x&quot;)<br>  |&gt; Prep.get_dummies(&quot;stats_league&quot;)<br>  |&gt; Prep.get_dummies(&quot;is_pitcher&quot;)<br>  |&gt; Prep.get_dummies(&quot;is_new&quot;)<br>  |&gt; Prep.get_dummies(&quot;is_foreign&quot;)<br><br>Kino.DataTable.new(df)</pre><p><strong>Prepare the Training Set</strong></p><pre>left = df<br>  |&gt; DF.filter(year &lt; 2022)<br><br>right = teams<br>  |&gt; DF.select([&quot;team&quot;, &quot;year&quot;, &quot;rank&quot;])<br><br>{train_ids, train_targets, train_maps} = <br>  DF.join(left, right, how: :left)<br>  |&gt; DF.discard([&quot;id&quot;, &quot;team&quot;])<br>  |&gt; DF.to_rows()<br>  |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br><br>training_data = Enum.zip(Prep.map_to_tensor(train_maps), train_targets)</pre><p><strong>Create a Model</strong></p><pre>model =<br>  Axon.input(&quot;input&quot;, shape: {nil, 14})<br>  |&gt; Axon.dense(48, activation: :tanh)<br>  |&gt; Axon.dropout(rate: 0.2)<br>  |&gt; Axon.dense(48, activation: :tanh)<br>  |&gt; Axon.dense(1, activation: :sigmoid)<br><br>trained_state = model<br>  |&gt; Axon.Loop.trainer(:mean_squared_error, Axon.Optimizers.adam(0.0005))<br>  |&gt; Axon.Loop.metric(:accuracy, &quot;Accuracy&quot;)<br>  |&gt; Axon.Loop.run(training_data, %{}, epochs: 3, compiler: EXLA)</pre><p><strong>Create Test Data and Make Predictions</strong></p><pre>test = df<br>  |&gt; DF.filter(year == 2022)<br><br>{test_ids, _, test_maps} = test<br>  |&gt; DF.discard([&quot;id&quot;, &quot;team&quot;])<br>  |&gt; DF.to_rows()<br>  |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br><br>result = Prep.map_to_tensor(test_maps)<br>  |&gt; Enum.map(&amp; Axon.predict(model, trained_state, &amp;1)<br>      |&gt; Nx.to_flat_list |&gt; List.first)<br><br>map_2022 = test |&gt; DF.filter(year == 2022) |&gt; DF.to_series()<br>teams_2022 = S.to_list(map_2022[&quot;team&quot;])<br>ids_2022 = S.to_list(map_2022[&quot;id&quot;])<br>predictions = DF.new(id: ids_2022, team: teams_2022, rank: result)<br><br>Kino.DataTable.new(predictions)</pre><h3>Group Pitchers and Batters by Team and Year Separately</h3><p>Let’s first restore our is_pitcher flag by removing where we converted it to a number in the <strong>Reduce Data to Numbers</strong> section:</p><pre>  df = df<br>    |&gt; DF.filter(games_played &gt; 0)<br>    |&gt; Prep.get_dummies(&quot;team&quot;, &quot;team_x&quot;)<br>    |&gt; Prep.get_dummies(&quot;stats_league&quot;)<br>-   |&gt; Prep.get_dummies(&quot;is_pitcher&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_new&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_foreign&quot;)<br>  <br>  Kino.DataTable.new(df)</pre><p>Let’s now add a section named “Group Pitchers and Hitters” and a code block within it.</p><pre>pitcher_df = df<br>  |&gt; DF.filter(is_pitcher)<br>  |&gt; DF.group_by([&quot;team&quot;, &quot;team_x&quot;, &quot;year&quot;])<br>  |&gt; DF.summarise(<br>    batters_faced: sum(plate_appearances), <br>    singles_allowed: sum(singles),<br>    doubles_allowed: sum(doubles),<br>    triples_allowed: sum(triples),<br>    home_runs_allowed: sum(home_runs),<br>    runs_allowed: sum(runs),<br>    strikeouts_against: sum(strikeouts),<br>    bases_on_balls_allowed: sum(bases_on_balls)<br>    )<br><br>  hitter_df = df<br>  |&gt; DF.filter(not(is_pitcher))<br>  |&gt; DF.group_by([&quot;team&quot;, &quot;team_x&quot;, &quot;year&quot;])<br>  |&gt; DF.summarise(<br>    plate_appearances: sum(plate_appearances), <br>    singles: sum(singles),<br>    doubles: sum(doubles),<br>    triples: sum(triples),<br>    home_runs: sum(home_runs),<br>    runs_scored: sum(runs),<br>    strikeouts: sum(strikeouts),<br>    bases_on_balls: sum(bases_on_balls)<br>    )<br><br>teams_by_year_df = DF.join(pitcher_df, hitter_df, on: [&quot;team&quot;, &quot;team_x&quot;, &quot;year&quot;], how: :inner)<br><br>Kino.DataTable.new(hitter_df)</pre><p>The hitters’ DataFrame now looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/909/1*v6man2QAzV0eu6JetYbcEg.png" /><figcaption>Hitter’s DataFrame</figcaption></figure><p>And the pitchers’ DataFrame looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/912/1*TgRqngSm0BXRj0OQL2fuXw.png" /><figcaption>Pitchers’ DataFrame</figcaption></figure><p>Please note that I used different column names for each one. That’s because now we’re going to merge the two back together again. Just tack this line on after creating the two new DataFrames and let’s look at the result as a DataFrame (so we don’t have to scroll right to see all the fields):</p><pre>+ teams_by_year_df = DF.join(pitcher_df, hitter_df, on: [&quot;team&quot;, &quot;year&quot;], how: :inner)<br>- Kino.DataTable.new(hitter_df)</pre><pre>#Explorer.DataFrame&lt;<br>  Polars[168 x 19]<br>  team string [&quot;orx&quot;, &quot;yom&quot;, &quot;yak&quot;, &quot;hir&quot;, &quot;sft&quot;, ...]<br>  team_x integer [1, 2, 5, 3, 7, ...]<br>  year integer [2009, 2009, 2009, 2009, 2009, ...]<br>  batters_faced integer [5292, 5289, 5220, 4886, 4415, ...]<br>  singles_allowed integer [1146, 1083, 1035, 1021, 906, ...]<br>  doubles_allowed integer [0, 2, 16, 1, 0, ...]<br>  triples_allowed integer [0, 0, 1, 0, 1, ...]<br>  home_runs_allowed integer [122, 109, 140, 110, 103, ...]<br>  runs_allowed integer [922, 1101, 872, 811, 943, ...]<br>  strikeouts_against integer [321, 340, 371, 392, 314, ...]<br>  bases_on_balls_allowed integer [569, 516, 535, 532, 492, ...]<br>  plate_appearances integer [6033, 5043, 5237, 4810, 5664, ...]<br>  singles integer [940, 830, 950, 935, 971, ...]<br>  doubles integer [298, 232, 200, 164, 252, ...]<br>  triples integer [20, 13, 33, 8, 38, ...]<br>  home_runs integer [171, 174, 85, 83, 103, ...]<br>  runs_scored integer [709, 618, 582, 479, 610, ...]<br>  strikeouts integer [1041, 858, 801, 725, 1016, ...]<br>  bases_on_balls integer [477, 330, 387, 315, 364, ...]<br>&gt;</pre><p>Add the Kino.DataTable.new(teams_by_year_df) back to the end if you want to explore all of the years or investigate the details.</p><h3>Prepare the Training Set, Create a Model, and Make Predictions</h3><p>Let’s now modify the training set data in the <strong>Prepare the Training Set</strong> code block as so, then execute it:</p><pre>  left =<br>-   df<br>+   teams_by_year<br>    |&gt; DF.filter(year &lt; 2022)<br>  <br>  right =<br>    teams<br>    |&gt; DF.select([&quot;team&quot;, &quot;year&quot;, &quot;rank&quot;])<br>  <br>  {train_ids, train_targets, train_maps} =<br>    DF.join(left, right, how: :left)<br>-   |&gt; DF.discard([&quot;id&quot;, &quot;team&quot;])<br>+   |&gt; DF.discard([&quot;team&quot;])<br>    |&gt; DF.to_rows()<br>    |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br>  <br>  training_data = Enum.zip(Prep.map_to_tensor(train_maps), train_targets)</pre><p>The id field is no longer part of the DataFrame as all of the player IDs were summarized earlier.</p><pre>[<br>  {#Nx.Tensor&lt;<br>     s64[1][17]<br>     [<br>       [477, 569, 5292, 298, 0, 171, 122, 6033, 922, 709, 940, 1146, 1041, 321, 20, 0, 2009]<br>     ]<br>   &gt;,<br>   #Nx.Tensor&lt;<br>     f32[1][1]<br>     [<br>       [0.8333333134651184]<br>     ]<br>   &gt;,<br>   ...<br>]</pre><p>Note that the data portion of the training_data is now 17 items wide instead of 14. That’s going to have to be changed when building the model:</p><pre>  # Dynamic length, 14 columns<br>  model =<br>-   Axon.input(&quot;input&quot;, shape: {nil, 14})<br>+   Axon.input(&quot;input&quot;, shape: {nil, 17})<br>    |&gt; Axon.dense(48, activation: :tanh)<br>    |&gt; Axon.dropout(rate: 0.2)<br>    |&gt; Axon.dense(48, activation: :tanh)   <br>    |&gt; Axon.dense(1, activation: :sigmoid)</pre><p>The rest can be executed as-is to the end.</p><p>Well, each epoch finished after only 150 batches. That’s much faster than the 10,000 times we started with. But the accuracy going from only 0.059 to 0.072 at the end still worries me.</p><p>Nonetheless, let’s make similar changes to the <strong>Create Test Data and Make Predictions</strong> code block as we did above:</p><pre>  test =<br>-   df<br>+   teams_by_year_df<br>    |&gt; DF.filter(year == 2022)<br> <br>  {test_ids, _, test_maps} =<br>    test<br>-   |&gt; DF.discard([&quot;id&quot;, &quot;team&quot;])<br>+   |&gt; DF.discard([&quot;team&quot;])<br>    |&gt; DF.to_rows()<br>    |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br> <br>  result =<br>    Prep.map_to_tensor(test_maps)<br>    |&gt; Enum.map(&amp;(Axon.predict(model, trained_state, &amp;1) |&gt; Nx.to_flat_list() |&gt; List.first()))<br> <br>- map_2022 = test |&gt; DF.filter(year == 2022) |&gt; DF.to_series()<br>- teams_2022 = S.to_list(map_2022[&quot;team&quot;])<br>- ids_2022 = S.to_list(map_2022[&quot;id&quot;])<br>- predictions = DF.new(id: ids_2022, team: teams_2022, rank: result)<br>+ predictions =<br>+   test |&gt; DF.filter(year == 2022) |&gt; DF.to_series()<br>+   |&gt; then(&amp; S.to_list(&amp;1[&quot;team&quot;]))<br>+   |&gt; then(&amp; DF.new(team: &amp;1, rank: result))<br><br>  Kino.DataTable.new(predictions)</pre><p>I also refactored the predictions calculations to be in a pipeline rather than using a bunch of temporary variables. It looks so much nicer this way.</p><p>Let’s show all in the Kino.DataTable and sort in ascending order (since 1st place is better than 12th place).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/891/1*-7sAKxgFEqFOvq635NuzWg.png" /><figcaption>Teams for 2022 in Rank Order</figcaption></figure><p>Hmmm. The top 6 teams and bottom 4 teams all have the same calculated rank as each other. At least there’s some variation?</p><h3>Remove Year?</h3><p>What happens if I take the year out of the training and prediction data?</p><pre>{train_ids, train_targets, train_maps} =<br>  DF.join(left, right, how: :left)<br>- |&gt; DF.discard([&quot;team&quot;])<br>+ |&gt; DF.discard([&quot;team&quot;, &quot;year&quot;])<br>  |&gt; DF.to_rows()<br>  |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br> <br>...<br> <br>{test_ids, _, test_maps} =<br>  test<br>- |&gt; DF.discard([&quot;team&quot;])<br>+ |&gt; DF.discard([&quot;team&quot;, &quot;year&quot;])<br>  |&gt; DF.to_rows()<br>  |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)</pre><p>Oh, and the input shape is now {nil, 16}.</p><pre>  model =<br>-   Axon.input(&quot;input&quot;, shape: {nil, 17})<br>+   Axon.input(&quot;input&quot;, shape: {nil, 16})<br>    |&gt; Axon.dense(48, activation: :tanh)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/895/1*oWdgzOG2kGa7TiUJ383DyA.png" /><figcaption>Calculated Predictions without Year</figcaption></figure><p>Now everyone is even with each other. That’s not useful at all.</p><h3>What Does :sigmoid Do?</h3><p>What happens if I increase the activation: :sigmoid density to 2?</p><pre>  model =<br>    Axon.input(&quot;input&quot;, shape: {nil, 16})<br>    |&gt; Axon.dense(48, activation: :tanh)<br>    |&gt; Axon.dropout(rate: 0.02) <br>    |&gt; Axon.dense(48, activation: :tanh)  <br>-   |&gt; Axon.dense(1, activation: :sigmoid)<br>+   |&gt; Axon.dense(2, activation: :sigmoid)</pre><p>Whoa! Accuracy increased an order of magnitude! Although it did get steadily worse as it went on.</p><pre>Epoch: 0, Batch: 150, Accuracy: 0.7284768 loss: 0.0899520<br>Epoch: 1, Batch: 150, Accuracy: 0.6357617 loss: 0.0869262<br>Epoch: 2, Batch: 150, Accuracy: 0.5894040 loss: 0.0849954<br>Epoch: 3, Batch: 150, Accuracy: 0.5761589 loss: 0.0838641<br>Epoch: 4, Batch: 150, Accuracy: 0.5761590 loss: 0.0831332<br>Epoch: 5, Batch: 150, Accuracy: 0.5562915 loss: 0.0825061<br>Epoch: 6, Batch: 150, Accuracy: 0.5695364 loss: 0.0819108<br>Epoch: 7, Batch: 150, Accuracy: 0.5231787 loss: 0.0813999<br>Epoch: 8, Batch: 150, Accuracy: 0.5231786 loss: 0.0809584<br>Epoch: 9, Batch: 150, Accuracy: 0.4966888 loss: 0.0805738<br>Epoch: 10, Batch: 150, Accuracy: 0.4966888 loss: 0.0802360<br>Epoch: 11, Batch: 150, Accuracy: 0.4768212 loss: 0.0799371<br>Epoch: 12, Batch: 150, Accuracy: 0.4238411 loss: 0.0796705<br>Epoch: 13, Batch: 150, Accuracy: 0.4105960 loss: 0.0794313<br>Epoch: 14, Batch: 150, Accuracy: 0.4105960 loss: 0.0792153<br>Epoch: 15, Batch: 150, Accuracy: 0.3973510 loss: 0.0790193<br>Epoch: 16, Batch: 150, Accuracy: 0.3708608 loss: 0.0790420<br>Epoch: 17, Batch: 150, Accuracy: 0.4172185 loss: 0.0789080<br>Epoch: 18, Batch: 150, Accuracy: 0.4172185 loss: 0.0788180<br>Epoch: 19, Batch: 150, Accuracy: 0.4768212 loss: 0.0787298</pre><p>That was unexpected. Running through to the 2022 predictions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/890/1*2IrXAN681vr4AWsryjWQVw.png" /><figcaption>Bottom 10 Predicted Ranks with 2 :sigmoid</figcaption></figure><p>Well, Nippon Ham did finish dead last in 2022. It got that right. And the Hiroshima Carp certainly underperformed. But why is everyone else close to the same? What happens if I put the year back in like this?</p><pre>  left =<br>    teams_by_year_df<br>    |&gt; DF.filter(year &lt; 2022)<br> <br>  right =<br>    teams<br>    |&gt; DF.select([&quot;team&quot;, &quot;year&quot;, &quot;rank&quot;])<br> <br>  {train_ids, train_targets, train_maps} =<br>    DF.join(left, right, how: :left)<br>-   |&gt; DF.discard([&quot;team&quot;, &quot;year&quot;])<br>+   |&gt; DF.discard([&quot;team&quot;])<br>    |&gt; DF.to_rows()<br>    |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br><br>training_data = Enum.zip(Prep.map_to_tensor(train_maps), train_targets)</pre><p>The model takes in 17 columns again.</p><pre>  model =<br>-   Axon.input(&quot;input&quot;, shape: {nil, 16})<br>+   Axon.input(&quot;input&quot;, shape: {nil, 17})<br>    |&gt; Axon.dense(48, activation: :tanh)<br>    |&gt; Axon.dropout(rate: 0.02) <br>    |&gt; Axon.dense(48, activation: :tanh)  <br>    |&gt; Axon.dense(2, activation: :sigmoid)</pre><pre>  test =<br>    teams_by_year_df<br>    |&gt; DF.filter(year == 2022)<br> <br>  {test_ids, _, test_maps} =<br>    test<br>-   |&gt; DF.discard([&quot;team&quot;, &quot;year&quot;])<br>+   |&gt; DF.discard([&quot;team&quot;])<br>    |&gt; DF.to_rows()<br>    |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*oQGFTl_W74WzGM7vtKt6YQ.png" /><figcaption>Ranked Team Predictions for 2022</figcaption></figure><p>That does separate a few teams from the others. But the middle 6 teams are still a problem.</p><p>Well, that’s it for today. I do feel like I’m getting closer to something usable. It’s definitely interesting as I experiment further with this.</p><p>Until next time, take care all.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=545e106ba46e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Predicting Standings with Elixir and Machine Learning]]></title>
            <link>https://medium.com/@westbaystars/predicting-standings-with-elixir-and-machine-learning-d0b78c6fee53?source=rss-45f8dbf755c4------2</link>
            <guid isPermaLink="false">https://medium.com/p/d0b78c6fee53</guid>
            <category><![CDATA[elixir]]></category>
            <category><![CDATA[nx]]></category>
            <category><![CDATA[explorer]]></category>
            <dc:creator><![CDATA[Michael Westbay]]></dc:creator>
            <pubDate>Mon, 02 Jan 2023 03:00:55 GMT</pubDate>
            <atom:updated>2023-01-05T13:15:32.675Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/356/1*5KmeYxriftFiWEarpl4M0g.png" /><figcaption>Nippon Professional Baseball (NPB) Teams</figcaption></figure><p>I’m often invited on to the Japan Baseball Weekly Podcast prediction show at the end of March. Show host John Gibson does a great job helping we guests focus our team segment discussions to:</p><ul><li>Expected pitching rotation</li><li>Key additions</li><li>Roster hits</li><li>Outlook (all teams)</li><li><strong>Predicted finishing standings (all teams)</strong></li></ul><p>I’ve gone through a number of attempts to make my guesses somewhat data backed in the past. I’ve used R Studio to analyze teams’ preseason stats, and even set up a Monte Carlo season emulator one year. Machine Learning has long been on my list of things I’ve wanted to try, and thanks to a <a href="https://qiita.com/piacerex/items/a40c5d24190d59f68610">wonderful tutorial</a> by <a href="https://medium.com/u/e3d76e5c7f95">piacere @fukuoka.ex(love Elixir&amp;Gravity extremely)</a> (as part of the Elixir Advent Calendar, in Japanese) using Elixir’s Livebook and Nx, I feel like I can finally dive in and see what ML has to offer with this task.</p><p>This is mainly my notes to myself as I go through this learning experience. I don’t know if it’ll be useful to anyone else, so I won’t blame you for closing this tab midway through. No worries. If you do get to the end and have ideas about other techniques I can pursue, please feel free to leave a comment and I look forward to trying it out. You can find the final version of my Livebook along with the data on GitHub <a href="https://github.com/westbaystars/data/blob/main/team-ranking.livemd">here</a>.</p><h3>Data Preparations</h3><p>The Monte Carlo simulation I did a few years ago didn’t take into consideration player changes. It took team vs. team data from the previous season and added a random element (based on percentage chance of an outcome between the two teams) to determine the winner of each game. That gave it some variation from the previous season, but the results mirrored the former season too much.</p><p>I would like to take the individual players into account this time to determine how a team will do based on its players at the start of the season. How will players who have not played in Nippon Professional Baseball (NPB) before be handled? I have some high school data for a few of the new draftees, and MLB data for new foreign players coming over. That makes me start to wonder:</p><ul><li>How much weight should I consider farm team data?</li><li>Should I give players with no NPB experience some average data?</li><li>Should average initial data apply based on where players are coming from (i.e. high school, university, industrial leagues, etc.)?</li></ul><p>For this initial run, let’s just zero out any missing data and see where that takes us. Some of the magic in Machine Learning is making associations, right? So a flag that someone is coming in from the MLB with no data should weigh something when all the data has been processed. At least, that’s the theory.</p><p>Let’s create a dataset that contains both pitchers and hitters with the same fields: id, team, year, stats_league, is_pitcher, is_new, is_foreign, games_played, plate_appearances, singles, doubles, triples, home_runs, runs, strikeouts, bases_on_balls. Fields like plate_appearances or runs reflect batters_faced and runs_allowed when the player is a pitcher.</p><p>The team and year fields reflect the team a player is on at the start of the season. The stats will reflect those put up by the given player the previous season, since we’re trying to project the team ranking the player is on the following season. This disconnect between year and stats is already making my head hurt thinking about it. But let’s power through.</p><p>For now, if a player doesn’t have any NPB stats for the previous season, let’s fill the stats with 0s. We’ll experiment with other values later. We’ll also treat hitters and pitchers the same for now and see how things change handling them separately in the future. Also, since developmental (<em>ikusei</em>) players cannot contribute to the top team, I’m leaving them off — even if they do join the top team during the season. Including them in the training data may be another experiment for later. Oh, and Shohei Ohtani’s years with Nippon Ham are just getting his pitching contribution for now as well.</p><p>Is that all of the caveats yet? No. The is_new flag applies to players who were not in NPB the previous season. There are some players returning to NPB after a stint in the MLB or other leagues. Those players will have is_new set to true in those situations. Also, is_foreign only counts toward the “non-domestic” roster limitation. A few foreign players reach domestic free agency requirements and are no longer on the non-domestic roster limits, so the is_foreign flag will switch from true to false the following season.</p><p>I’m sure that there are more footnotes required with the data. But let’s create the data set.</p><p>[Time passes as work out timings of last minute trades and promotions from developmental players to the 80-man rosters.]</p><p>I also crated a team ranking file with the fields: team, year, wins, losses, ties, win_pcnt, rank. The ranking is from 1 to 12, not divided by league. The two tab separated files can be found at:</p><ul><li><a href="https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/training-data-1.tsv">https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/training-data-1.tsv</a></li><li><a href="https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/teams.tsv">https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/teams.tsv</a></li></ul><h3>Setup and Read Data in Livebook</h3><p>It’s finally time to get down to work. In a new <a href="https://livebook.dev/">Livebook</a>, we’re going to need the following modules:</p><pre>Mix.install([<br>  {:explorer, &quot;~&gt; 0.4&quot;},<br>  {:kino, &quot;~&gt; 0.6&quot;},<br>  {:download, &quot;~&gt; 0.0.4&quot;},<br>  {:exla, &quot;~&gt; 0.4&quot;},<br>  {:axon, &quot;~&gt; 0.3&quot;}<br>])</pre><p>We’ll use Explorer to manipulate the data into the shape we want it and pass it on as a TensorFrame to Axon when we’re ready to crate our data model.</p><pre>alias Explorer.DataFrame, as: DF<br>alias Explorer.Series, as: S<br>alias Explorer.TensorFrame, as: TF<br>require Explorer.DataFrame              # For DF.mutate<br><br>training_file = &quot;training-data-1.tsv&quot;<br>teams_file = &quot;teams.tsv&quot;<br><br>unless File.exists?(training_file),<br>  do:<br>    {:ok, _filename} =<br>      Download.from(<br>        &quot;https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/#{training_file}&quot;<br>      )<br><br>unless File.exists?(teams_file),<br>  do:<br>    {:ok, _filename} =<br>      Download.from(<br>        &quot;https://raw.githubusercontent.com/westbaystars/data/main/team-ranking/#{teams_file}&quot;<br>      )<br><br>df = DF.from_csv!(training_file, delimiter: &quot;\t&quot;)<br>teams = DF.from_csv!(teams_file, delimiter: &quot;\t&quot;)<br><br>Kino.DataTable.new(df)</pre><p>The above is standard boilerplate to read in the two tab separated files into a pair of DataFrames. If you wish to view the training set, there is Kino.DataTable.new(df) at the end of the block.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/911/1*3IgI-oT4IBr8XzskIfYZ7g.png" /><figcaption>First 10 records of the training data DataFrame</figcaption></figure><h3>Manipulate Training Set</h3><p>Unfortunately, the data cannot be fed to Axon as is. The first problem is with our team and league labels. They need to be numerical. So let’s transform the team and stats_league values to be numbers instead of strings.</p><p>To do this in R there is a function called get_dummies that creates dummy integer values for columns of strings. To house such a function (and others), let’s create a Prep module.</p><pre>require Explorer.DataFrame    # Needed for DF.mutate<br><br>defmodule Prep do<br>  def get_dummies(df, column, new_column \\ nil) <br>  def get_dummies(df, column, new_column) <br>    when column != nil and new_column == nil, <br>    do: get_dummies(df, column, column)<br>  def get_dummies(df, column, new_column) do<br>    series = DF.pull(df, column)<br>    list = S.to_list(series)<br>    map = list<br>      |&gt; Enum.uniq()<br>      |&gt; Enum.with_index(&amp; {&amp;1, String.to_integer(&quot;#{&amp;2}&quot;)})<br>      |&gt; Enum.into(%{})<br><br>    new_series = S.transform(series, fn key -&gt; Map.get(map, key) end)<br>    <br>    DF.put(df, new_column, new_series)<br>  end<br><br>  def separate(map, id, target) do<br>    {<br>      map |&gt; Enum.map(&amp;Map.get(&amp;1, id)),<br>      if Map.has_key?(List.first(map), target) do<br>        map |&gt; Enum.map(&amp;([[Map.get(&amp;1, target)]] |&gt; Nx.tensor()))<br>      else<br>        nil<br>      end,<br>      map |&gt; Enum.map(&amp;Map.drop(&amp;1, [id, target]))<br>    }<br>  end<br><br><br>  def map_to_tensor(map) do<br>    map<br>    |&gt; Enum.map(&amp; [Map.values(&amp;1)] |&gt; Nx.tensor)<br>  end<br>end<br><br>df = df<br>  |&gt; Prep.get_dummies(&quot;team&quot;, &quot;team_x&quot;)<br>  |&gt; DF.mutate(id: year * 100 + team_x)<br>  |&gt; Prep.get_dummies(&quot;stats_league&quot;)<br>  |&gt; Prep.get_dummies(&quot;is_pitcher&quot;)<br>  |&gt; Prep.get_dummies(&quot;is_new&quot;)<br>  |&gt; Prep.get_dummies(&quot;is_foreign&quot;)<br><br>Kino.DataTable.new(df)</pre><p>In the get_dummies function, the column Series is pulled out of the DataFrame and converted to a List. That list is then transformed into an indexed map of possible values which are then placed into a new Series and put back into the DataFrame with the new column name. If the value of new_column is the same as column, or new_column is not set (is nil), the column will be replaced in the DataFrame.</p><p>The result is the addition of a team_x column at the end and the stats_league column having all league string values replaced with numbers. Why replace stats_league and not team? Because we want to be able to map team to results later on, while the league a player played for the previous season may play a part in the calculations of the projection, but isn’t needed otherwise.</p><p>The boolean columns are also converted to integers via Prep.get_dummies.</p><p>Also, the player id is being replaced with a number combining the year and team dummy value. With Hanshin having the value 0, the 2009 Tigers get an id of 200900. This continues with Orix getting 200901 and so on. I’m treating the player id as irrelevant, so one may consider it having been dropped here.</p><h3>Prepare Training Set</h3><p>There are two more things still needed to be done to get our training set:</p><ol><li>Add column for final standings for each team by year</li><li>Filter out 2022 which will act as a test case later</li></ol><p>Explorer makes this very easy.</p><pre>left = df<br>  |&gt; DF.filter(year &lt; 2022)<br><br>right = teams<br>  |&gt; DF.select([&quot;team&quot;, &quot;year&quot;, &quot;rank&quot;])<br><br>{train_ids, train_targets, train_maps} = <br>  DF.join(left, right, how: :left)<br>  |&gt; DF.discard([&quot;team&quot;])<br>  |&gt; DF.to_rows()<br>  |&gt; Prep.separate(&quot;id&quot;, &quot;rank&quot;)<br><br>training_data = Enum.zip(Prep.map_to_tensor(train_maps), train_targets)</pre><p>Here I’m taking only the records with the year less than 2022 in the DataFrame we’ve been working with and assign it to left. Next, I take only the team, year, rank columns from the teams file we read in earlier and assign that DataFrame to right. The variable names can be anything, but using the DataFrame.join function, joining left and right with an inner join based on the table on the :left is easy to read.</p><p>We take the joined result, discard the team character based column, convert the whole thing to a row-based Map, then separate the id and rank fields into train_ids and train_targets respectively. train_maps gets the map of remaining values.</p><p>The training_data then then receives an array of tuples containing a row’s worth of data as a pair of Nx.Tensors, the first value being the data on the row, the second value being the expected value. It looks like this:</p><pre>[<br>  {#Nx.Tensor&lt;<br>     s64[1][15]<br>     [<br>       [17, 0, 32, 8, 0, 0, 0, 200, 34, 29, 0, 9, 0, 0, 2009]<br>     ]<br>   &gt;,<br>   #Nx.Tensor&lt;<br>     s64[1][1]<br>     [<br>       [8]<br>     ]<br>   &gt;},<br>  ...<br>]</pre><p>Now we’re now ready to create a model.</p><h3>Create a Model</h3><p>This is the area that magic seems to start to appear. These are the incantations suggested by Piacere-san which I’m going to recite while looking up what they each do. The goal is to just get something working for now, and I’ll dive more into the options later.</p><pre>model =<br>  Axon.input(&quot;input&quot;, shape: {nil, 15})   # Dynamic length, 15 columns<br>  |&gt; Axon.dense(48, activation: :tanh)    # Add hyperbolic tangent dense layer with 48 output units<br>  |&gt; Axon.dropout(rate: 0.2)              # Add a low dropout rate to network<br>  |&gt; Axon.dense(48, activation: :tanh)<br>  |&gt; Axon.dense(1, activation: :sigmoid)  # Add single Sigmoid layer with single output unit<br><br>trained_state = model<br>  |&gt; Axon.Loop.trainer(:mean_squared_error, Axon.Optimizers.adam(0.0005))<br>  |&gt; Axon.Loop.metric(:accuracy, &quot;Accuracy&quot;)<br>  |&gt; Axon.Loop.run(training_data, %{}, epochs: 20, compiler: EXLA)</pre><p>Running this I’m getting a consistent accuracy of 0.082 with a loss of 42.5 going through each batch. Looking at Piacera-san’s sample, it started out with a 0.67 accuracy rate and loss of 0.215 and got up to a 0.81 accuracy with 0.151 loss. These number are not looking good, nor are they getting any better each time through. Clearly there is something more than player stats from the previous year in determining the following year’s ranking for a given team. Acquiring good players is only part of the equation.</p><p>Okay, nonetheless, I want to see what kinds of results we get. Let’s extract the 2022 data, discard the teams column, convert the data to an array of maps representing the rows, and separate out the id and rank columns (ignoring rank in the results of that process.</p><pre>test = df<br>  |&gt; DF.filter(year == 2022)<br><br>{test_ids, _, test_maps} = test<br>  |&gt; DF.discard([&quot;team&quot;])<br>  |&gt; DF.to_rows()<br>  |&gt; Prep.separate(&quot;id&quot;, &quot;rank&quot;)<br><br>result = Prep.map_to_tensor(test_maps)<br>  |&gt; Enum.map(&amp; Axon.predict(model, trained_state, &amp;1)<br>      |&gt; Nx.to_flat_list |&gt; List.first)<br>  |&gt; then(&amp; Enum.zip(test_ids, &amp;1))<br>  |&gt; Enum.map(&amp; [elem(&amp;1, 0), elem(&amp;1, 1)])<br>  |&gt; then(&amp; [[&quot;Id&quot;, &quot;Rank&quot;] | &amp;1])</pre><p>With the test_ids and test_maps separated out from the DataFrame, similar to how we created the training_data, feed those along with the model we created to Axon.predict. That results in an array of Tensors which we flatten into a List of arrays, each array a single element long, so we pull out that single element, resulting in a list of resulting predicted ranks.</p><p>We then zip the test_ids column onto the left and change each row from a tuple to an array and add a row for the header. The result looks like this:</p><pre>[<br>  [&quot;Id&quot;, &quot;Rank&quot;],<br>  [202211, 1.0],<br>  [202211, 1.0],<br>  [202211, 1.0],<br>  [202203, 1.0],<br>  [202201, 1.0],<br>  [202202, 1.0],<br>  [202201, 1.0],<br>  [202202, 1.0],<br>  [202207, 1.0],<br>  [202204, 1.0],<br>  ...<br>]</pre><p>It appears that the prediction wants to be a number from 0 to 1. That explains why the accuracy was so far off — my predicted results were <strong>all</strong> 1 or larger.</p><h3>Refactor</h3><p>This gives us the opportunity to do a little bit of refactoring. The first thing we need to do is to make the rankings values go from 0 to 1. That’s as easy as mutating the rank after loading in the teams.tsv file up at the top:</p><pre>  teams = DF.from_csv!(teams_file, delimiter: &quot;\t&quot;)<br>+   |&gt; DF.mutate(rank: (rank - 1) / 12)</pre><p>Re-running the model and trained state, I’m getting somewhat more acceptable numbers with each batch process. While the accuracy is starting slightly lower at 0.064 and improving to 0.076, the loss is reading as 0.085 or lower. That’s promising a huge improvement on the loss side from 42.5 the first time.</p><p>And the final predicted rankings are:</p><pre>[<br>  [&quot;Id&quot;, &quot;Rank&quot;],<br>  [202211, 0.4400217831134796],<br>  [202211, 0.4400217831134796],<br>  [202211, 0.4400217831134796],<br>  [202203, 0.4400217831134796],<br>  [202201, 0.4400217831134796],<br>  [202202, 0.4400217831134796],<br>  [202201, 0.4400217831134796],<br>  [202202, 0.4400217831134796],<br>  [202207, 0.4400217831134796],<br>  ...<br>]</pre><p>There is still no differentiation between teams. Instead, all teams are below average. That’s not good.</p><p>Let’s start picking away at the data. The id, being a combination of team and year, doesn’t appear to be useful, especially considering that team_x is still in the data but not contributing to anything. So let’s remove the |&gt; DF.mutate(id: year * 100 + team_x) from our DataFrame. Also let’s discard id from our training data and use team_x as the subject that we’re evaluating for:</p><pre>  df = df<br>    |&gt; Prep.get_dummies(&quot;team&quot;, &quot;team_x&quot;)<br>-   |&gt; DF.mutate(id: year * 100 + team_x)<br>    |&gt; Prep.get_dummies(&quot;stats_league&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_pitcher&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_new&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_foreign&quot;)<br><br>...<br><br>  {train_ids, train_targets, train_maps} = <br>    DF.join(left, right, how: :left)<br>-   |&gt; DF.discard([&quot;team&quot;])<br>+   |&gt; DF.discard([&quot;id&quot;, &quot;team&quot;])<br>    |&gt; DF.to_rows()<br>-   |&gt; Prep.separate(&quot;id&quot;, &quot;rank&quot;)<br>+   |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)</pre><p>That means that we now have rows of 14 columns in our input shape:</p><pre>  model =<br>-   Axon.input(&quot;input&quot;, shape: {nil, 15})   # Dynamic length, 15 columns<br>+   Axon.input(&quot;input&quot;, shape: {nil, 14})   # Dynamic length, 14 columns<br>    |&gt; Axon.dense(48, activation: :tanh)    # Add hyperbolic tangent dense layer with 48 output units<br>    |&gt; Axon.dropout(rate: 0.2)              # Add a low dropout rate to network<br>    |&gt; Axon.dense(48, activation: :tanh)<br>    |&gt; Axon.dense(1, activation: :sigmoid)  # Add single Sigmoid layer with single output unitExecution of this is going much faster. But the accuracy is still 0.072. </pre><p>Evaluating the prediction from the new model and trained_state:</p><pre>  test = df<br>    |&gt; DF.filter(year == 2022)<br><br>  {test_ids, _, test_maps} = test<br>-   |&gt; DF.discard([&quot;team&quot;])<br>+   |&gt; DF.discard([&quot;id&quot;, &quot;team&quot;])<br>    |&gt; DF.to_rows()<br>-   |&gt; Prep.separate(&quot;id&quot;, &quot;rank&quot;)<br>+   |&gt; Prep.separate(&quot;team_x&quot;, &quot;rank&quot;)<br><br>  result = Prep.map_to_tensor(test_maps)<br>    |&gt; Enum.map(&amp; Axon.predict(model, trained_state, &amp;1)<br>      |&gt; Nx.to_flat_list |&gt; List.first)<br>    |&gt; then(&amp; Enum.zip(test_ids, &amp;1))<br>    |&gt; Enum.map(&amp; [elem(&amp;1, 0), elem(&amp;1, 1)])<br>-   |&gt; then(&amp; [[&quot;Id&quot;, &quot;Rank&quot;] | &amp;1])<br>+   |&gt; then(&amp; [[&quot;Team&quot;, &quot;Rank&quot;] | &amp;1])<br>    |&gt; dbg()</pre><p>And we still get a constant for almost all of the teams:</p><pre>[<br>  [&quot;Team&quot;, &quot;Rank&quot;],<br>  [11, 0.44003480672836304],<br>  [11, 0.44003480672836304],<br>  [11, 0.44003480672836304],<br>  [3, 0.44003480672836304],<br>  [1, 0.44003480672836304],<br>  [2, 0.44003480672836304],<br>  [1, 0.44003480672836304],<br>  [2, 0.44003480672836304],<br>  [7, 0.44003480672836304],<br>  [4, 0.44003480672836304],<br>  [0, 0.44003817439079285],<br>  ...<br>]</pre><p>Team 0 (Hanshin) came out ever so slightly higher than everyone else in one of the records. Well, that’s some progress?</p><h3>Remove Zero Data</h3><p>Okay. The most likely problem is that there are a <strong>lot</strong> of records with 0 data. Let’s filter those out and try again.</p><pre>  df = df<br>+   |&gt; DF.filter(games_played &gt; 0)<br>    |&gt; Prep.get_dummies(&quot;team&quot;, &quot;team_x&quot;)<br>    |&gt; Prep.get_dummies(&quot;stats_league&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_pitcher&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_new&quot;)<br>    |&gt; Prep.get_dummies(&quot;is_foreign&quot;)</pre><p>Hmmm. Slightly more variance between records, even on the same team. But there wasn’t an improvement in accuracy and everyone still is ranked around 0.456.</p><pre>[<br>  [&quot;Team&quot;, &quot;Rank&quot;],<br>  [11, 0.45620861649513245],<br>  [11, 0.45620861649513245],<br>  [9, 0.45620861649513245],<br>  [1, 0.45620861649513245],<br>  [1, 0.4562086760997772],<br>  [3, 0.45620861649513245],<br>  [6, 0.45620861649513245],<br>  [0, 0.4562097489833832],<br>  [5, 0.45620861649513245],<br>  [4, 0.45620861649513245],<br>  ...<br>]</pre><h3>Final Touches</h3><p>Well, we still haven’t gotten to an acceptable result. But the final thing I want to do is covert this back to a DataFrame and return the original id and team fields.</p><p>To do that, we’ll first just get the rank results, removing the transformations to add the ID (Team) column back on.</p><pre>result = Prep.map_to_tensor(test_maps)<br>    |&gt; Enum.map(&amp; Axon.predict(model, trained_state, &amp;1)<br>      |&gt; Nx.to_flat_list |&gt; List.first)<br>-   |&gt; then(&amp; Enum.zip(test_ids, &amp;1))<br>-   |&gt; Enum.map(&amp; [elem(&amp;1, 0), elem(&amp;1, 1)])<br>-   |&gt; then(&amp; [[&quot;Team&quot;, &quot;Rank&quot;] | &amp;1])</pre><p>Then let’s build the new DataFrame from the three columns:</p><pre>map_2022 = test |&gt; DF.filter(year == 2022) |&gt; DF.to_series()<br>teams_2022 = S.to_list(map_2022[&quot;team&quot;])<br>ids_2022 = S.to_list(map_2022[&quot;id&quot;])<br>predictions = DF.new(id: ids_2022, team: teams_2022, rank: result)<br><br>Kino.DataTable.new(predictions)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/895/1*BPaH2B-Lhw9OByb34mD5tw.png" /><figcaption>Results Showing Player ID and Team Fields</figcaption></figure><p>That looks much better. And it’ll allow me to look up each player if needed to see why not all players on the same team have different ranks. I’m guessing that it’s evaluating each player’s contribution to the score.</p><h3>Conclusion</h3><p>Well, this isn’t the end of the adventure into Machine Learning. But it is a bit of a dead end for this particular project. It looks like I’m giving the algorithms too much disparate information for it to make any sense out of whatsoever.</p><p>On the bright side, I’ve learned a few more data manipulation techniques to make this all work. And Nx is no longer the magic black box it once was. (Though I will admit that I’m still not sure what the Loop trainer is actually doing under the hood quite yet.) Despite the accuracy remaining quite low throughout the tests, the number of batches to reach the same conclusion decreased as I removed seemingly unnecessary data.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d0b78c6fee53" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>