Archives de catégorie : Non classé

Cloning a row (even if the structure gets modified in the future)

Publié le par
Répondre

A few years ago, I asked this question on StackExchange and got a brilliant answer (by Erwin Brandstetter). I just finally understood how it works. So here is my explanation (to myself mainly). Enjoy!

The goal was to INSERT a row in a table, cloning an existing one while replacing some value (like the primary key) in the new row. I didn’t want to list each and every column so that even if I add new fields to the table, the cloning mechanism would still work.

Here is the response I got:

INSERT INTO contrats
SELECT (sub.subalias).*
FROM (
    SELECT t #= hstore('idcontrat', nextval(pg_get_serial_sequence('contrats','idcontrat'))::TEXT)
             #= hstore('date_saisie', CURRENT_TIMESTAMP::TEXT)
             #= hstore('numero', numero || '-DUP')
             #= hstore('notes', NULL)
       AS subalias
       FROM contrats t
       WHERE idcontrat = :oldidcontrat
) sub
RETURNING idcontrat

First thing to know: when you create a table, PostgreSQL automatically creates a composite type with the same name and all fields in that table (see [here]). As you will discover in this documentation, you reference composite types with (parens).

So how does the query work? There’s an outer query:

INSERT INTO contrats 
SELECT (sub.subalias).* 
FROM (
    <inner_query>
) sub
RETURNING idcontrat.

This outer query is pretty obvious except the (sub.subalias) part.

The inner query is also simpler than it seems. If we didn’t want to change any field value, it would be:

SELECT t AS subalias
FROM contrats t
WHERE idcontrat = :oldidcontrat

Notice that we didn’t write SELECT t.* but just SELECT t. Here, t is the composite type that represents the whole contrats record/row.

Then we use the hstore PostgreSQL extension to do the transformations that we want. This is the syntax we use:

t #= hstore('fieldname',value)

This syntax only works on composite types and replaces the value of the fieldname field with the provided value. That value must be of type TEXT. As you can see, you can apply several transformations at once.

What was bugging me for so long was that in the initial answer I got, the second line was written:

SELECT (subalias).*

And I didn’t understand why that subalias was visible outside the inner query (I thought it was out of the scope).

subalias is just one field (of type contrats) and since it is of a composite type, you have to use the parens around the whole left part of the SELECT. You can’t write sub.(subalias).* but instead (sub.subalias).*

I must confess I asked the Perplexity AI to help me understand this.

Migrating an Ubuntu system to LUKS using TimeShift

Publié le par
Répondre

I will call the current system original and the new, encrypted one new.

Prepare

Create a USB-stick Ubuntu installer (I use Ventoy)

Install timeshift on original system

sudo apt update && apt install timeshift

Now plug in an USB drive with enough capacity

Configure timeshift

Launch timeshift-gtk, configure it for rlogin (default), select your usb drive and make sure you tick your user’s and root’s directory to Include all files (that part of the window might be hidden so expand it to the right to see it completely).

Then create a new snapshot (click on Create).

Reboot and install new system

Install timeshift on new system

launch timeshift and restore. Usually, partitions are as follow :

  • nvme0n1p1 is vfat and maps to /boot/efi
  • nbme0n1p2 is ext4 and maps to /boot
  • nvme0n1p3 is the encrypted block device that contains /

Here the whole system you just installed will be overwritten by the timeshift snapshot. But since your disk was not encrypted before, we will have to tell the system to unlock it upon startup.

Fix startup

Decrypt the encrypted partition using GUI tools (I use KDE/Dolphin and click on the left on the « disk locked » icon. You can do the same manually with commands but I find it easier this way.

Open a terminal. Make sure the correct language is selected.

sudo -i (to become root – since you’re still on the live system, password is usually blank)

lsblk
and copy the luks-xxxxxx-xxxxx-xxxx that shows (with ctrl+shift+C)

dmsetup rename luks-xxxxxx-xxxxx-xxxx root_crypt
The root_crypt name is whatever you want but we will have to reuse it in /etc/crypttab so choose something that will let you identify that disk easily.

Now we mount the disks to /mnt (we will chroot there to setup grub and initramfs

mount /dev/mapper/root_crypt /mnt
mount /dev/nvme0n1p2 /mnt/boot
mount /dev/nvme0n1p1 /mnt/boot/efi

Now we bind-mount various system dirs before chrooting:

for i in /dev /dev/pts /proc /sys /run; do mount -B $i /mnt/$i; done

One last thing before chrooting, get the UUIDs of our disks (you can open another console to make copy/paste easier):

sudo lsblk

Take note of the UUIDs of /boot/efi (nvme0n1p1 in my case). Since it is vfat (I guess) the UUID is small, usually XXXX-YYYY. I will call it <UUID_EFI>
Take also note of the UUID of / (nvme0n1p3 for me). I will call it <UUID_ROOT>

Now switch to the installed system:

chroot /mnt

Edit /etc/crypttab with your editor (nano for example) and add a line:

root_crypt UUID=<UUID_ROOT> none luks,initramfs

Warning: you must add a newline at the end otherwise the line won’t be seen.

Edit /etc/fstab and modify the UUIDs. Warning: the mount points won’t probably be the same so make sure to look at the mount point when adding the UUIDs. If some lines are not used anymore, comment them out by placing a # as the first character of the line.

For / don’t use an UUID but /dev/mapper/root_crypt like so:

/dev/mapper/root_crypt / ext4 defaults 0 1

Now install grub:

grub-install /dev/nvme0n1
update-grub

And update your initramfs:

update-initramfs -k all -c

Reboot and enjoy.

Raw SQL in Ruby/Rails

Publié le par
Répondre

Everyone seems to discourage the use of raw SQL in Ruby applications and while ActiveRecord seems a good solution for general purpose CRUD operations, we have a strong expertise in SQL and we want to use it as much as possible. Here are the bits we have collected so far on the subject. We are new to Ruby/Rails so these might be obvious for some of you. We primarily write this for ourselves.

We are perfectly aware of the « limitations ». We love PostgreSQL and have no intention of being RDBMS-agnostic. At all.

find_by_sql the way we want it

We want named parameters because, despite the overhead, it’s the cleanest way to pass parameters (we don’t have to preserve a strict order which can lead to problems when enhancing the query, counting the « ? », etc). Also, when we use the same parameter several times in the same query, we don’t have to repeat it.

class ClientsController < ApplicationController

	def index
		@clients = Client.find_by_sql(
			<<-SQL
			SELECT idclient,name,email,contacts
			FROM clients 
			ORDER BY nom
			SQL
			)
	end

	def show
		@client = Client.find_by_sql(["	
			SELECT idclient,name,email,contacts 
			FROM clients 
			WHERE idclient = :idclient", 
			{ :idclient => params[:id] } 
		])[0]
	end

For the show method, we have to grab the first element in the array (thus the [0]at the end.