Missing support for `SET` and `CREATE DOMAIN`

[kianmeng]

I've tried the instructions in the README doc using the SportsDB but got this error.

Steps to reproduce the error.

Prepare sample DB.

$ unzip sportsdb_sample_postgresql.zip
$ sudo -u postgres createdb sportsdb_sample
$ sudo -u postgres psql -d sportsdb_sample -f sportsdb_sample_postgresql_20080304.sql

Dump schema.

$ pg_dump --schema-only --no-owner postgres://dbuser:dbpass@localhost/sportsdb_sample > sportsdb_sample.schema.sql

Create migration files.

$ mix ecto.extract.migrations --sql-file sportsdb_sample.schema.sql                                                                                                 
Compiling 31 files (.ex)                                                                                             
Generated ecto_extract_migrations app
UNKNOWN 8> SET statement_timeout = 0;
UNKNOWN 9> SET lock_timeout = 0;
UNKNOWN 10> SET idle_in_transaction_session_timeout = 0;
UNKNOWN 11> SET client_encoding = 'UTF8';
UNKNOWN 12> SET standard_conforming_strings = on;
UNKNOWN 13> SELECT pg_catalog.set_config('search_path', '', false);
UNKNOWN 14> SET check_function_bodies = false;
UNKNOWN 15> SET xmloption = content;
UNKNOWN 16> SET client_min_messages = warning;
UNKNOWN 17> SET row_security = off;
UNKNOWN 23> CREATE DOMAIN public.primary_id AS integer;
UNKNOWN 26> SET default_tablespace = '';
UNKNOWN 28> SET default_with_oids = false;
** (Protocol.UndefinedError) protocol Enumerable not implemented for nil of type Atom
    (elixir 1.10.3) lib/enum.ex:1: Enumerable.impl_for!/1
    (elixir 1.10.3) lib/enum.ex:141: Enumerable.reduce/3
    (elixir 1.10.3) lib/enum.ex:3383: Enum.reduce/3
    lib/mix/tasks/ecto_extract_migrations.ex:67: Mix.Tasks.Ecto.Extract.Migrations.run/1
    (mix 1.10.3) lib/mix/task.ex:330: Mix.Task.run_task/3
    (mix 1.10.3) lib/mix/cli.ex:82: Mix.CLI.run_task/2

[reachfh]

The error in this case is not from lacking support for SET or CREATE DOMAIN, it was that the code expected schema object types (e.g. custom schemas) to exist which are not used by that db. I added default values and made a new release, and the code now generates migrations for sportsdb. The resulting migrations have an error because the tables don't actually define a primary key, they just have an integer not null field, e.g.

CREATE TABLE public.locations (
    id integer NOT NULL,
    timezone character varying(100),
    latitude character varying(100),
    longitude character varying(100),
    country_code character varying(100)
);

results in

defmodule(Sports.Repo.Migrations.Table.Public.Locations) do
  use(Ecto.Migration)
  def(change) do
    create(table("locations", primary_key: false)) do
      add(:id, :integer, null: false)
      add(:timezone, :"character varying", size: 100)
      add(:latitude, :"character varying", size: 100)
      add(:longitude, :"character varying", size: 100)
      add(:country_code, :"character varying", size: 100)
    end
  end
end

which gives an error:

09:25:01.637 [info]  execute "  ALTER TABLE ONLY public.addresses\n  ADD CONSTRAINT fk_add_loc_id__loc_id FOREIGN KEY (location_id) REFERENCES public.locations(id);\n\n"

09:25:01.645 [debug] QUERY ERROR db=7.9ms
  ALTER TABLE ONLY public.addresses
  ADD CONSTRAINT fk_add_loc_id__loc_id FOREIGN KEY (location_id) REFERENCES public.locations(id);

 []
** (Postgrex.Error) ERROR 42830 (invalid_foreign_key) there is no unique constraint matching given keys for referenced table "locations"

It could be

defmodule(Sports.Repo.Migrations.Table.Public.Locations) do
  use(Ecto.Migration)
  def(change) do
    create(table("locations")) do
      add(:timezone, :"character varying", size: 100)
      add(:latitude, :"character varying", size: 100)
      add(:longitude, :"character varying", size: 100)
      add(:country_code, :"character varying", size: 100)
    end
  end
end

but of course that's different from the source schema.

Probably there should be an option to disable generating the foreign key constraints in this case.

It likely makes sense to add support for CREATE DOMAIN as a pass-through SQL. SET is also possible, but most SET statements seem to be defaults, and it probably makes sense to just manually add migrations for them if needed.

[kianmeng]

The error in this case is not from lacking support for SET or CREATE DOMAIN, it was that the code expected schema object types (e.g. custom schemas) to exist which are not used by that db. I added default values and made a new release, and the code now generates migrations for sportsdb. The resulting migrations have an error because the tables don't actually define a primary key, they just have an integer not null field, e.g.

Is the fixes merged to master branch?

[reachfh]

Yes, they are merged