Skip to main content

mysql case when then end

Use case

I use a Dovecot IMAP & POP server, against a MySQL user database. In database, the users are stored this way:

id pwd
mihamina@gulfsat.mg $1$HSBxVt/clgdj0L5Fgzm61
mihamina@freedsl.mg $1$7TCTJJMG9cUvAlWuAbvE.
mihamina@rktmb.org $1$H23xVt.bbgdj0L5Fgzm61

On my server I have mutiple IP adresses:

  • A legacy IP (1.2.3.4) where "@rktmb.org" users sometimes log in only with the localpart, sometimes with the full email address
  • A legacy IP (5.6.7.8) where "@freedsl.mg" users sometimes log in only with the localpart, sometimes with the full email address
  • A last IP (7.8.9.0) where users log in with the full email address

In usual condition, on a mail system, all users either all login with the full email address or all with the localpart. But on my alien system, they mix their habits, for legacy reasons.

Therefore:

  • The "mihamina@rktmb.org" user always logs in on the 1.2.3.4 IP but sometimes with "mihamina", sometimes with "mihamina@rktmb.org"
  • The "mihamina@freedsl.mg" user always logs in on the 5.6.7.8 IP but sometimes with "mihamina", sometimes with "mihamina@freedsl.mg"
  • The other users always log in on 7.8.9.0 with their full email address

The SQL query using CASE WHEN THEN END

Dovecot allows to use "%u" as username and "%l" as local IP in SQL queries. This is how I did it:

 SELECT id AS user, pwd AS password  FROM passwdc  WHERE (((id = '%u') OR (id = (                               CASE                                 WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org')                                 ELSE ('%u@freedsl.mg')                               END                              ))) AND en = 1)
or 
 SELECT id AS user, pwd AS password  FROM passwdc  WHERE (((id = '%u') OR (id = (                               CASE                                 WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org')                                 WHEN ("%l" = "5.6.7.8") THEN ('%u@freedsl.mg')                               END                              ))) AND en = 1)

Popular posts from this blog

Undefined global vim

Defining vim as global outside of Neovim When developing plugins for Neovim, particularly in Lua, developers often encounter the "Undefined global vim" warning. This warning can be a nuisance and disrupt the development workflow. However, there is a straightforward solution to this problem by configuring the Lua Language Server Protocol (LSP) to recognize 'vim' as a global variable. Getting "Undefined global vim" warning when developing Neovim plugin While developing Neovim plugins using Lua, the Lua language server might not recognize the 'vim' namespace by default. This leads to warnings about 'vim' being an undefined global variable. These warnings are not just annoying but can also clutter the development environment with unnecessary alerts, potentially hiding other important warnings or errors. Defining vim as global in Lua LSP configuration to get rid of the warning To resolve the "Undefined global vi...
Read more

LazyGit AI Commit Message

Having AI‑generated commit messages directly integrated into LazyGit If you use LazyGit every day, you already know how it turns Git from a chore into something you can actually enjoy. But there is one part of the workflow that still tends to feel a bit tedious: writing good commit messages. In this post, I show how to plug OpenAI models directly into LazyGit using a tiny one‑file BASH script, so you can get AI‑generated commit messages based on your actual diffs, without waiting for external tools to catch up with the new OpenAI Responses API . The result is a minimal, focused tool you can drop into your setup today: lgaicm . It behaves like a mini aichat that does exactly one thing: generate commit messages from Git diffs, optimized for LazyGit. Why AI‑generated commit messages in LazyGit? Commit messages matter. They are the stor...
Read more

CopilotChat GlobFile Configuration

CopilotChat GlobFile Configuration Want to feed multiple files into GitHub Copilot Chat from Neovim without listing each one manually? Let's add a tiny feature that does exactly that: a file glob that includes full file contents . In this post, we'll walk through what CopilotChat.nvim offers out of the box, why the missing piece matters, and how to implement a custom #file_glob:<pattern> function to include the contents of all files matching a glob. Using Copilot Chat with Neovim CopilotChat.nvim brings GitHub Copilot's chat right into your editing flow. No context switching, no browser hopping — just type your prompt in a Neovim buffer and let the AI help you refactor code, write tests, or explain tricky functions. You can open the chat (for example) with a command like :CopilotChat , then provide extra context using built-in functions. That “extra context” is where the magic really happens. Built-in functio...
Read more