[erlang-questions] Mnesia record with composite key and QLC questions

[Steve Davis]

Hi Leon,

Try something like...

find_by_domain(Domain) ->
	F = fun() ->
		qlc:e(qlc:q([U || U = {user, {D, _}, _} <- mnesia:table(user), D =:=
Domain]))
	end,
	mnesia:transaction(F).

...though I would suggest that you reconsider your PK here. Primary
keys should really be "opaque".

Perhaps a better solution is to generate a GUID for each user then use
that as the primary key, and then make their domain and username
attributes of that.
e.g.
-record(user, {guid, username, domain}}.

....which will make this and other searches much more sane.

As for the K/V pairs (profile data?), again maybe think about a single
table referencing each K/V pair to the user by guid then you can
collect their profile properties and search for users with particular
properties by the property key.

Obviously you will need to index certain fields as appropriate to your
app for reasonable performance.

Regards,
Steve



On May 15, 3:32 am, Leon de Rooij <l...@REDACTED> wrote:
> Hi all,
>
> I want to store users in an mnesia database, but want the key not only  
> to be unique on username, but also on domain.
>
> So I defined the user record as follows:
>
> -record(user, {domain_username, params=[]})
>
> where domain_username = {Domain, Username}
> and params = [ {Key1,Value1}, {Key2, Value2}, ... ]
>
> I chose params to be a list because I don't know beforehand how many  
> key/value tuples will be in there.
>
> Is this the correct way to do things ? (Instead of having domain and  
> username as standalone strings (lists) in the record and make it a bag  
> instead of a set, or for the params having a seperate table with  
> relations/foreign keys, like I would do with SQL ?)
>
> It's also not clear to me what is the best way to search through the  
> records:
>
> To find a user based on a param, the following function works:
>
> Param = {"accountcode", "1234"},
> Fun = fun() -> qlc:eval(qlc:q([ U || U <- mnesia:table(user),  
> lists:member(Param, U#user.params) ])) end,
> mnesia:transaction(Fun).
>
> That works, but can this be done more efficient ? (Searching on params  
> won't be done often, but still I'd like to know..)
>
> Also, I'd like to be able to for example get a list of all users in a  
> certain domain, but I can't get it to work yet..
>
> I tried this:
>
> Domain = "test.com",
> Fun = fun() -> qlc:eval(qlc:q([ U || U <- mnesia:table(user),  
> {Domain,_} =:= U#user.domain_username ])) end,
> mnesia:transaction(Fun).
>
> But, having an underscore for username in the tuple to match equality  
> is not allowed (compilation breaks with "variable '_' is unbound").
>
> Then I tried using assignment operator there:
>
> Fun = fun() -> qlc:eval(qlc:q([ U || U <- mnesia:table(user),  
> {Domain,_} = U#user.domain_username ])) end,
>
> which does compile, but it breaks at runtime with:
>
> =ERROR REPORT==== 15-May-2009::08:26:37 ===
> Error in process <0.31.0> with exit value: {undef,
> [{test,get_user_by_domain,["test.com"]},{erl_eval,do_apply,5},
> {shell,exprs,6},{shell,eval_loop,3}]}
>
> ** exited: {undef,[{test,get_user_by_domain,["test.com"]},
>                     {erl_eval,do_apply,5},
>                     {shell,exprs,6},
>                     {shell,eval_loop,3}]} **
>
> Can anyone tell me what is best practice in these cases ?
>
> Thanks and kind regards,
>
> Leon
> _______________________________________________
> erlang-questions mailing list
> erlang-questi...@REDACTED://www.erlang.org/mailman/listinfo/erlang-questions