Single SQL updating multiple rows to unique values

Posted in Category • May 28, 2020

This is quick one, hope it is useful. TIL it is possible to update multiple database rows to unique values using a single SQL query. The trick is to use SQL CASE/WHEN for the updating value. This is very useful especially to implement re-ordering items of some sort, for example:

UPDATE "todos"
SET "sort_order" = CASE
  WHEN ("todos"."id" = 1) THEN 0
  WHEN ("todos"."id" = 157) THEN 1
  WHEN ("todos"."id" = 150) THEN 2
  WHEN ("todos"."id" = 158) THEN 3
  WHEN ("todos"."id" = 148) THEN 4
  ELSE "todos"."sort_order"
END
WHERE "todos"."id" IN (1, 157, 150, 158, 148);
Read more »

Database anonymization techniques

Posted in Category • May 23, 2020

Sometime ago, I was tasked with anonymize verb. remove identifying particulars or details from (something, especially medical test results), typically for statistical or other purposes.

a (small) production database before giving it to a partner for functional testing. It is not that I do not trust the partner, but my employer values customer privacy a lot, so anonymization is a a must. In this post I am exploring the techniques that are available to achieve anonymity while preserving many characteristics of the database.

Database anonymization is not a new topic, depending on your purpose and your time and effort budget, you might want to choose one over another.

Having said that, I am not totally sure these techniques are useful with regard to GPDR Following is the official GPDR text. It is too lengthy so I would be very appreciate it if someone provides me with a simple and concise summary. As far as I’m concerned, you would be better off asking users for their consent before storing their data, before even thinking about anonymization.

. My intention is focusing on replicating the production environment as close as possible in the context of functional testing and analytic.

Read more »

Email forwarding with Google Domain and Cloudflare

Posted in Category • May 4, 2020

I am one of those people who would have to use a custom email address with their own domain name. I mean, @gmail.com is not fun, @live.com and @me.com are nicer, but my desired address has already been taken. And like many others, I have already own a domain, so naturally I want to take advantage of that and having a custom domain email address @tungdao.com.

It used to be very simple, most domain name providers do offer email forwarding which you can then point it to your existing inbox and call it a day. However, it gets complicated when you want to throw in CloudFlare CloudFlare provides acceleration and security for your websites. It dose that by proxy all requests to your domain to it own servers, which then send the corresponding request to your own server and does some processing on the response before sending it back to the client. The proxy part involves dynamic routing to different edge servers based on client location and the server availability, the only way they could do that is to control the DNS records of your domain name, that’s why CloudFlare requires you to use their name servers.

, as well as DKMI https://en.wikipedia.org/wiki/DomainKeys_Identified_Mail

and SPF https://en.wikipedia.org/wiki/Sender_Policy_Framework

.

A popular choice was to use Mailgun, as they do provide mail servers which you can create MX records to point to, and create rules to forward incoming emails to their desired inbox. However, as of early 2020, Mailgun changed their plans to exclude the rules/routing functionality needed to do this, so I have to look for a different way to have email forwarding.

Read more »

Adding Mustache template support to Hakyll

Posted in Category • April 18, 2020

Because of the COVID-19 pandemic, I have been staying in for the last few weeks. Suddenly I had some free time so naturally I tried to reboot my blog That seems to be the case for many others as well, I have seen a lot of people restarting their blog and Youtube podcasts, for example Chris Coiyer and Remy Sharp, whom I have been following for a very long time.

. Since Haskell is my go-to programming language now, this time I decided to use Hakyll to build my blog with. In the past I have tried quite a few static site builders like Jekyll and Hugo, however they are not flexible enough to my use, hence I always seem hit their limitation as I want a lot of flexibility and customization, which with Hakyll I can achieve. Sure I will need work through Hakyll’s own rather complex source first but I would not mind it at all, especially compared to the similarly complex, if not more, of Jekyll.

I have been quite satisfied with the power Mainly because of Pandoc. It truly is the universal converter which support the most extensive number of formats as well as a huge number of plugins.

and flexibility of Hakyll Another valued feature of Hakyll is the flexibility with custom routing, instead of requiring the files to be laid out exactly like the resulting site. This feature seems to be inspired by Nanoc.

, while I still think the context mechanism and dependency management is overly complicated, the area where I felt most dissatisfying is Hakyll’s template system. It gets the job done but it feels rather complicated and not quite familiar. That is why last weekend I decided to sit down and give it ago trying to add Mustache template support to Hakyll.

Read more »

Build a web application in Haskell

Posted in Category • November 27, 2017

I’m new to Haskell, however I have plenty experience building apps in Django and Rails, and all of the slides’ issue is very relevant in building production app. The slide make me worried I decide to start building a small, yet product-ready app to explore the Haskell eco system for my self.

Read more »

WooCommerce custom order state

Posted in Category • November 8, 2017

More often than not, the product you sell will involve some kind of operations that you want to track transparently from customer, for example ordering the materials from a supplier, or send it off to a custom shop.

Read more »

Fixing python-build on macOS High Sierra

Posted in Category • September 27, 2017

I upgraded to macOS High Sierra yesterday. The most expected feature is APFS, I’m so excited about it that I did a fresh install from USB rather than a normal in-place upgrade. Everything went well, the installation is super fast, except that High Sierra does not ship with the libssl headers, so pyenv install fails:

Read more »

All posts →

Projects

elixir-dns

One of the very first DNS library for Elixir. The package provides a complete DNS solution including server and client, as well as data structures to construct DNS packets. It is built on-top of Erlang’s internal inet_dns module, which makes it very efficient.

time-locale-vietnamese

My first Haskell package ever. This is a very simple package providing time locale format for the Vietnamese language. In addition to a locale format referenced from the JDK, this package also provides an alternative locale format that are more accurate to native speakers.

svg-easy

SVG Sprite generator. Supercharge your frontend work flow by simplify the process of building optimized SVG Sprite sheets. Built with Haskell.

haskell-cloudflare-ddns

Yet another ddclient clone to work with Cloudflare Dynamic DNS. Because I’m bored and ddclient failed to install on my Mac for reason I can not comprehend (I don’t know Perl).

Bifrost

Menubar application to provide proxy to various services through SSH.

Sometimes I need a Sock5 proxy to get through BS, and I already have some VPS running around. The other time I have some services running on VPS that I want to tunnel to my machine so that I can use them as-if they were running locally, and I’m too lazy to keep a terminal tab open for each of those service.

All projects →

Links

Bookmarks