@kelvinst does this library supports using mysql multiple databases instead of PG schemas?
emoragaf
kelvinst
@emoragaf No. Not yet, I have not implemented since we didn’t use MySQL.
Maybe in the future we will have it, but it’s not in our plans to implement it now.
If you (or anyone else) know how to do it, and have time to contribute, please feel free to open a PR and it will be accepted for sure!
0
tomconroy
I’m curious to see an example of this approach, one part that’s not clear is how to create the function inside tenant_migrations. In order to create the id generator function, I’d need to be able to do something like:
execute """
CREATE FUNCTION #{prefix}.id_generator ......
....
tenant_id := #{TenantHelper.tenant_handler(prefix).id};
....
"""
But I can’t see how to find the prefix that’s running on a migration, any ideas?
0
kelvinst
There is the function Ecto.Migration.prefix/0 which is available on all your migrations and returns the prefix of every migration.
Just curious, what is this? 
0
l00ker
Yes. As @kelvinst points out you can use the prefix() function in your migrations. Just change #{prefix} in your code to #{prefix()} and you’re set.
To set the shard_id I’ve been using a private function in the migration file to split the prefix to grab the id from a prefix something like "Tenant_1":
defp shard_id() do
[_, id] = prefix() |> String.split("_")
id
end
BTW - Here is the function to extract the shard_id from a generated id:
...
def extract(item_id) when is_integer(item_id) do
((item_id ^^^ ((item_id >>> 23) <<< 23 )) >>> 10)
end
That function was ripped from Ecto.InstaShard. You may want to have a look around that repo as well. There’s some really interesting things to look at there.
0
l00ker
Here’s the entire migration I put together for the id generator for anyone that might be interested:
defmodule YourModule.Repo.Migrations.CreateSnowflake do
use Ecto.Migration
def up do
execute """
CREATE SEQUENCE #{prefix()}.next_id_seq;
"""
execute """
CREATE OR REPLACE FUNCTION #{prefix()}.next_id(OUT result bigint) AS $$
DECLARE
our_epoch bigint := 1000000000000; -- <-Set this to your epoch
seq_id bigint;
now_millis bigint;
shard_id int := #{shard_id()}; -- Set int value here per shard
BEGIN
SELECT nextval('#{prefix()}.next_id_seq') % 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;
"""
end
def down do
execute """
DROP SEQUENCE #{prefix()}.next_id_seq;
"""
execute """
DROP FUNCTION #{prefix()}.next_id;
"""
end
defp shard_id() do
[_, id] = prefix() |> String.split("_")
id
end
end
4
kelvinst
That was what I suspected. Not a very good solution IMO, but I can’t think in another one for it, so “let it be”. 
0
tomconroy
tomconroy
I’ve spent hours trying to implement a custom type for these primary keys, to no avail, do you have an example? My problem is mostly around associations unable to resolve themselves with cast_assoc, preloading, etc. For now I’m going to leave these as default integer types, but keeping in mind that they won’t work in javascript. Do you have separate modules for encoding your models to JSON? Perhaps it makes sense to convert these to strings there? (since Ecto.Changeset.cast/2 will cast string id inputs with no problem?)
0