CRUD for an array of arrays of integers

Phoenix Forum Questions / Help
1

Hi there. I need to enable CRUD for a PostgreSQL array column and am hoping someone can advise if the approach I have in mind is suitable, and if so help with how to go about it. I’m relatively new to non-trivial programming and Elixir and am struggling to see how to achieve it though I think it should be relatively simple for someone who’s used to parsing and modifying structured data.

Bacground

XML data like the following is accepted by the HTTP API:

<data>
  <SingleTemperatureOffset>
    <Temperature>10</Temperature>
    <Offset>0</Offset>
  </SingleTemperatureOffset>
  <SingleTemperatureOffset>
    <Temperature>100</Temperature>
    <Offset>10</Offset>
  </SingleTemperatureOffset>
</data>

The temperature offsets are stored in a PostgreSQL integer[] array column called offsets. Example value:

{{10,0},{100,10}}

There may be a varying number of inner arrays, and each should contain two integers.

The database table creation migration’s column:

add :offsets, {:array, :smallint}, [null: false]

The model’s schema field:

field :offsets, {:array, {:array, :integer}}

As of yesterday, the storage of the temperature offsets as above is working. (Yay!)

Enabling CRUD

I’m now trying to provide for creation and editing of such temperature offsets in the HTML admin area. (Other fields such as text and simple numbers are working fine.)

As part of the admin flow, API submissions such as the above are listed with a link to a “new” action/page (which saves a different but similar model) in which the form inputs’ values are populated with the submitted data. In the case of the offsets input, this results in, for example:

�d

In the new action in the controller:

IO.inspect(changeset.changes.offsets)
IO.inspect(changeset.changes.offsets, char_lists: :as_lists)

This shows:

[[10, 0], 'd\n']
[[10, 0], [100, 10]]

I’m thinking that a text input accepting (UTF-8 string?) values such as either {{10,0},{100,10}} or [[10,0],[100,10]] would be fine, and hopefully relatively simple to achieve as opposed to other approaches involving multiple form inputs.

To obtain a value such as IO.inspect's output (when it’s set to output as lists), could I use Erlang’s io_lib:format (which I haven’t got my head round yet)? If so, how could I get the resultant string from the controller to the form input value? My “new” action/function as it stands (ToSubmission is the model containing user submissions and To is the model for admin-created or approved entries):

def new(conn, %{"from_sub" => from_sub_id}) do
  to_submission = Repo.get(ToSubmission, from_sub_id)
  changeset = To.changeset(%To{}, to_submission |> Map.from_struct)

  render(conn, "new.html", [changeset: changeset, new_from_to_sub: to_submission])
end

When an admin user submits a form containing such an offsets value, how could I parse and check for conformance to the “any number of lists each containing two integers” requirement?

Edit: I’ve found out that there’s a Kernel.inspect/2 function which can be used to create a string including the array’s square brackets. In the controller:

offsets = changeset.changes.offsets
IO.inspect(offsets, [char_lists: :as_lists, label: "list"])
    
offsets_string = ~s(#{inspect(offsets, [char_lists: :as_lists])})
IO.inspect(offsets_string, [label: "string"])

Gives:

list: [[10, 0], [100, 10]]
string: "[[10, 0], [100, 10]]"

So I think that that part of the problem is solved, and I now need to work out how to get this string to be used as the form input’s default value in the ‘new’ page.

Regarding parsing the string when creating/updating, I think that Code.eval_string/3 could then be used to create the nested list from such a string? The admin user can be trusted not to be malicious, but I suppose not evaluating user-submitted data as code would be better! Perhaps some recursive pattern matching? I’ll have a play.

2

Well that is about the easiest possible code injection exploit possible. ^.^

Never ever use Code.eval_*/*, ever, even if you know what you are doing you should probably still not use it, and in this case it is absolutely not ever a good idea to use it.

There are plenty of parsers out there, plus if you can change the submitted format to be json or something then even better (it might already be, the example you gave certainly is).

3 Likes
3

@OvermindDL1, thanks for confirming that evaluating that input is not a good idea. :slight_smile:

I seem to have made a little progress, and now have some further questions please.

Converting string to list

I’m currently doing this in the controller:

def create(conn, %{"to" => to_params}) do
  # transform user's string description of offsets to a list
  data = Map.update!(to_params, "offsets", fn x ->
    [[10, 0], [100, 10]] # TODO: make list dynamic by parsing input string
  end)
  changeset = To.changeset(%To{}, data)

  case Repo.insert(changeset) do
    # ...
  end
end

Whether I use a JSON library or parse the string manually, is doing it in the controller, transforming the params before creating the changeset, a sensible approach, or is this the kind of thing that should be done as part of the model’s changeset function? I looked at doing the latter but haven’t managed yet; I’d need to learn more about Ecto and changesets.

Edit: I’ve now got a version working with the change made in the model changeset pipeline:

def changeset(struct, params \\ %{}) do
  struct
  |> change(offsets: deserialise_offsets(params["offsets"]))
  # other casts and validations...
end

def deserialise_offsets(offsets) when is_nil(offsets), do: nil
def deserialise_offsets(offsets) do
  [[10, 0], [100, 10]] # TODO: make dynamic based on offsets string
end

I’d be interested to hear whether it’s generally deemed better to have such transformations done in the model or the controller in Phoenix.

View/template

I’m currently converting the list to a string in a view helper function:

def serialise_offsets(data) do
  ~s(#{inspect(data, [char_lists: :as_lists])})
end

In the template , using the :value option and input_value/2 function:

<%= text_input f, :offsets, [value: input_value(f, :offsets) |> serialise_offsets] %>

With the exception of seeing nil when there isn’t a temperature offset list to turn into a string, which I think I can correct by using when and returning "" with the view helper function, this seems to be working as intended.

Is this way of handling the list-to-string conversion/serialisation for the form input okay, or is there a better/cleaner way I should be looking at? Should this serialisation be done elsewhere, before the view?

And if I end up using a JSON library to parse the form input into a list anyway, would it be better to also use it for this instead of inspect/2?

Many thanks.

4

I try to keep my controllers simple and clean, no schemas, no models, nothing in it but accessing a library function and putting the input into a view call. :slight_smile:

The library functions I like to make modules for specific purposes (kind of like contexts in Phoenix 1.3) and all ‘work’ is in those, this lets me use the work elsewhere such as API’s other systems, channels, etc, plus easier to test, much easier to test.

I’d also parse before giving to the changeset, I try to keep changeset’s only for their specific purposes of putting into a schema, no real conversions or anything.

Inspect might surprise you at times since it is built for showing an elixir’ized output, I’d use json as it is much more uniform and identifiable for web work. Phoenix comes with Poison as a dependency so… :slight_smile:

1 Like
5

@OvermindDL1, many thanks for the guidance and outline of where to put what. I’ll try to arrange things as you describe.

So would a module, used by my controller, to perform topic-specific (temperature offsets in this case) conversions, (de)serialisations using Poison, etc., go in lib/my_app/my_module.ex? (Phoenix 1.2 at the moment.)

6

If you want, though I’ve always tried to split them up based on functionality (or what phoenix 1.3 calls contexts), just as long as similar concerns are in the same place, logic should not really be in controllers except for conversion from/to the interface only. :slight_smile:

7

So this could entail, for example, a module for temperature conversions, another module for (temperature offsets) (de)serialisation, etc., with each being in a lib/my_app/{module}.ex module file? And another module to bring this varying functionality together that provides the temperature offsets-related logic a temperature offsets controller needs to call? (This being preferred over having one module that does everything for temperature offsets?)

I did some searching on where to place app functionality (as opposed to the app’s web interface functionality), and I’m still somewhat insecure about good structural practice. I haven’t started looking at Phoenix 1.3 yet, though I will watch the keynote video and see how much I can understand. :slight_smile: Anyway, it seems like lib is the place to put this kind of thing rather than web.

Thanks for putting up with my confused questioning. Are there any good articles on project structure I should read that relate to Phoenix 1.2?

8

Really the way that Phoenix 1.3 does it can still be done in 1.2 (and is how I’ve been doing it since 1.1), should look at it. :slight_smile:

9

I haven’t looked into contexts or restructured yet but I do have the decoding and encoding with Poison working. If invalid JSON is submitted from the form it’s marked as invalid and the invalid value is pre-filled on the subsequent new/edit admin page. (Woo!)

I do have one question: I found that serialising the list of lists of integers (e.g., [[10,0],[100,13]]) via my view helper function results in invalid JSON being shown in the HTML text input within double quotes/speech marks: “[[10,0],[100,13]]”

Before submission:

After submission:

When an existing (valid) entry is loaded into the edit page, this doesn’t happen. Adding |> String.trim(~S(")) to my view helper function solves that, but I wonder if there’s something I’m missing that’s leading to this being necessary?

What I think are the relevant bits of code are below. Any improvements/pointers welcome! Thanks.

Just as a recap, the basic data flow for storing temperature offsets is, as I understand it:

Admin HTML form → Poison decoding to list of lists → Ecto changeset → PostgreSQL integer array

Controller

def create(conn, %{"to" => params}) do
  data = prepare_params(params)
  changeset = To.changeset(%To{}, data)
  # ...
end

def update(conn, %{"id" => id, "to" => params}) do
  data = prepare_params(params)
  to = get_to(id)
  changeset = To.changeset(to, data)
  # ...
end

defp prepare_params(params) do
  params |> decode_tos
end

defp decode_tos(params) do
  case Poison.decode(params["offsets"]) do
    {:ok, offsets_decoded} ->
      Map.update!(params, "offsets", fn x -> offsets_decoded end)
    {:error, _invalid_json} ->
      params
  end
end

View helper module

def serialise_offsets(data) when is_nil(data), do: nil
def serialise_offsets(data) do
  data
  |> Poison.encode!
  |> String.trim(~S("))
end

Form template

Using value option with input_value function and my serialise_offsets function:

<%= text_input f, :offsets, [value: input_value(f, :offsets) |> serialise_offsets] %>
<%= error_tag f, :offsets %>
10

As an alternative that you wouldn’t need to parse json but have to implement a few things in javascript. If you had a couple fields named like this

    <%= text_input f, :temp, name: "temp_params[][temp]" %>
    <%= text_input f, :temp, name: "temp_params[][offset]" %>

Your controller would have the params like this

Parameters: %{"temp_params" => [%{"temp" => "10"}, %{"offset" => "0"}, %{"temp" => "100"}, %{"offset" => "10"}]}

The problem with this approach is that you have to use javascript to duplicate the fields, the plus side is that you dont have to worry about invalid json from the client.

1 Like
11

Some days ago I posted a question and got an answer that might help You.

Here was the question Ecto and map with tuple keys

Jose Valim told me to look at Ecto.Type

In short, You can cast your data before saving, and uncast after loading. So, no need to modify controller or so… data is serialized at type level.

Does it looks like a solution for You?

This is an example type…

defmodule Whatever.Coordinates do
@behaviour Ecto.Type
def type, do: :map

structure to database

def cast(map) when is_map(map) do
new_map = map
|> Enum.map(fn({key, value}) ->
{coordinate_to_move(key), to_string(value)}
end)
|> Enum.into(%{})
{:ok, new_map}
end
def cast(_), do: :error

database to structure

def load(map) when is_map(map) do
new_map = map
|> Enum.map(fn({key, value}) ->
{move_to_coordinate(key), String.to_atom(value)}
end)
|> Enum.into(%{})
{:ok, new_map}
end
def load(_), do: :error

def dump(map) when is_map(map), do: {:ok, map}
def dump(_), do: :error

“ab” => {x, y}

defp move_to_coordinate(move) do
array_of_index = move
|> to_charlist
|> Enum.map(& &1 - 97)
{Enum.at(array_of_index, 0), Enum.at(array_of_index, 1)}
end

{x, y} => “ab”

defp coordinate_to_move(coordinate) do
[elem(coordinate, 0) + 97, elem(coordinate, 1) + 97]
|> to_string
end
end

In my schema, I use
field :coordinates, Coordinates

And now, everything is transformed just before db actions.

I hope this helps

NB:
I tried to save array of array of any, but got ** (ArgumentError) nested lists must have lists with matching lengths. Be careful if your inner array can vary in size.

1 Like
12

Thanks for this alternative. For now I’d like to avoid JS in the browser, but I’ll keep this in mind.

I did briefly look at Ecto.Type before I got the controller code doing what I wanted it to, but it seemed rather scary. It does seem like it would be nice to have the “dumping” and “loading” done at the type level, so I think I should have a go. Thanks.

In my case, the inner arrays should all contain two integers, but there can be a varying number of inner arrays. So I think I should be okay in that regard.

Going back to the controller and view helper code I currently have, I think the unwanted wrapping double quotes appear in the text input after submitting invalid JSON because I have Poison attempting to encode it, and I suppose its behaviour when being fed something that isn’t actually structured JSON is to wrap in “…”?

13

It was scary, I didn’t even knew it was possible to do so. But after going through examples, very logical.

It is really powerful because You can reuse these custom types anywhere you want.

14

I have tried to implement the suggestion by @kokolegorille instead of using the controller approach outlined by @BrightEyesDavid but it is not working.

The input for some field (called acl) takes the form of "[11,14]" which I want to store as an integer array in DB.

I have tried this type definition but getting 500 error:

defmodule ACL do
  use Ecto.Type
  def type, do: {:array, :integer}

  # Provide custom casting rules.
  def cast(acl) do
    {:ok,Jason.decode!(acl)}
  end

  # Everything else is a failure though
  def cast(_), do: :error
end

The Jason.decode!(acl) code works very fine in the controller solution, but not in the type definition. Any idea how to make this work in a type definition? Thank you.

15

I have managed to make it work like this:

defmodule ACL do
  use Ecto.Type
  def type, do: {:array, :integer}

  # Provide custom casting rules.
  def cast(acl) when is_binary(acl) do
    IO.puts("casting..")
    {:ok, Jason.decode!(acl)}
  end

  # Everything else is a failure though
  def cast(_), do: :error

  # loading data from the database
  def load(acl) when is_list(acl) do
    IO.puts("loading..")
    {:ok, acl}
  end

  # dumping data to the database
  def dump(acl) when is_list(acl) do
    IO.puts("dumping..")
    {:ok, acl}
  end

  def dump(_), do: :error
end

Any further comments are welcome.

1 Like
16

You can delegate the dump/load implementations to the ones ecto uses like that:

def dump(value), do: Ecto.Type.dump(type(), value)
def load(value), do: Ecto.Type.load(type(), value)
2 Likes