jhartwell / plsm Goto Github PK

The following are missing:

1. numeric -> :decimal
   2. jsonb -> :map

Hi there, thanks for this great library. When I try use it in a new Phoenix project I get a dependency mismatch issue.

I was wondering if it would be possible to upgrade the Mariaex dependency to 0.9.1.
I am totally new to Phoenix and Elixir so happy to open a PR, but feel it might be safer if an experienced person updates it.

Currently only MySql is supported and we need to expand that. We should use the ODBC drivers so we can easily resuse code as well as just needed the same type of driver for each database

** (RuntimeError) Connect raised a ArgumentError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

:erlang.binary_to_integer/1
lib/mariaex/protocol.ex:160: Mariaex.Protocol.normalize_port/1
(elixir) lib/keyword.ex:832: Keyword.update!/4
(elixir) lib/keyword.ex:836: Keyword.update!/4
lib/mariaex/protocol.ex:72: Mariaex.Protocol.connect/1
(db_connection) lib/db_connection/connection.ex:135: DBConnection.Connection.connect/2
(connection) lib/connection.ex:622: Connection.enter_connect/5
(stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

Last message: nil
State: Mariaex.Protocol
** (EXIT from #PID<0.91.0>) an exception was raised:
** (RuntimeError) Connect raised a ArgumentError error. The exception details are hidden, as
they may contain sensitive data such as database credentials.

    :erlang.binary_to_integer/1
    lib/mariaex/protocol.ex:160: Mariaex.Protocol.normalize_port/1
    (elixir) lib/keyword.ex:832: Keyword.update!/4
    (elixir) lib/keyword.ex:836: Keyword.update!/4
    lib/mariaex/protocol.ex:72: Mariaex.Protocol.connect/1
    (db_connection) lib/db_connection/connection.ex:135: DBConnection.Connection.connect/2
    (connection) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3

Currently there is little to no documentation in the code base, which is not good.

If you have a column that is a primary key and also has a unique constraint that column will be exported twice in the output. We don't need to worry about the unique constraint so it will need to be ignored

I got a runtime error that I tracked down to a table with MySQL mediumtext columns. I got past it by deleting the table as I didn't need in for my new PG DB.

The type was coming through into here as :none

https://github.com/jhartwell/Plsm/blob/master/lib/io/export.ex#L6

Which then failed to match any of the map_type signatures:

https://github.com/jhartwell/Plsm/blob/master/lib/io/export.ex#L12

I didn't dig deeper but suppose mediumtext is uknown in the type mappings and coming through as :none.

• Create a MySQL table with a bit field (e.g. rails boolean)
• Run plsm
• The bit field will be omitted

MySQL 5.6
PLSM 1.1.2
Mix/Elixir 1.4.2

when trying this I used it with maria DB.
the generated separate config file had the use line missing use Mix.Config.
the generated schema files messed up the primary_key option (which should be done differently anyway).
however it should be primary_key: false not :primary_key false

Currently the default config leaves the type: keyword out of the database section. This can cause confusion for those not using MySQL as there won't be a clear way to actually use Plsm with other database vendors

While using models I have had a hard time actually being able to use the :utc_datetime fields in an Ecto query (such as a where query filtering on dates). In order to remedy this I have had to convert my :utc_datetime fields over to Ecto.DateTime. This is going to be added as a feature now

Ecto does not have type text. It should be string

I'll make a PR for this.

I'm not sure how to fix this. It seems like there isn't a pattern match for a boolean field.

I'm using Postgres 13.x and have PostGIS enabled. Will it work for geo fields?

Using PostgreSQL...
** (FunctionClauseError) no function clause matching in Plsm.IO.Export.map_type/1

    The following arguments were given to Plsm.IO.Export.map_type/1:

        # 1
        :boolean

    Attempted function clauses (showing 7 out of 7):

        defp map_type(:decimal)
        defp map_type(:float)
        defp map_type(:string)
        defp map_type(:text)
        defp map_type(:map)
        defp map_type(:date)
        defp map_type(:integer)

    lib/io/export.ex:12: Plsm.IO.Export.map_type/1
    lib/io/export.ex:8: Plsm.IO.Export.type_output/1
    lib/io/export.ex:63: anonymous fn/2 in Plsm.IO.Export.prepare/2
    (elixir 1.13.3) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
    lib/io/export.ex:62: Plsm.IO.Export.prepare/2
    (elixir 1.13.3) lib/enum.ex:1593: Enum."-map/2-lists^map/1-0-"/2
    (elixir 1.13.3) lib/enum.ex:1593: Enum."-map/2-lists^map/1-0-"/2
    lib/plsm.ex:16: Mix.Tasks.Plsm.run/1

If you have a varchar column that is more than 255 characters wide you will have issues when using the :string type for a field. This can be changed by using the size option but we need to pass that in

One thing that I noticed is the four spaces in the generated models, following the Elixir style guide, two spaces are preferred. Also, the lib code is using 4 spaces too.
I think we can start changing the Ecto models first and then the entire source code. What do you think?

https://github.com/christopheradams/elixir_style_guide#source-code-layout

We should use :naive_datetime instead of Ecto.DateTime

Table names like MyAwesomeTable are converted to module names like MyApp.MyNameSpace.Myawesometable as Plsm assume that the table name must be snake case my_awesome_table. Plsm should convert these table names with CamelCase to module names with respective camel case like this. MyApp.MyNameSpace.MyAwesomeTable

I'll submit a PR

The generated schema file creates:

|> cast(params, :a, :b, :c)

While the current API requires:

|> cast(params, [:a, :b, :c])

Also, an import is needed:

import Ecto.Changeset

I can knock up a quick PR for this if you want?

When using ecto, the application will require the following setup done anyway, for example in dev.exs:

config :foobar, Foobar.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "example",
  password: "example",
  database: "foobar",
  hostname: "127.0.0.1",
  port: 5432

As you can see, all the options for database config will already be set here, and if options aren't set (ie port), we could assume that they are the defaults. We could read these settings.

Alternatively, we could use the config.exs instead of creating a Plsm.configs, as this is the "standard" way Elixir does things.

Up to you of course, but would love some discussion around this.

was trying this with a maria DB.
all integer columns were not created in the schema modules.

also note that all foreign keys and primary keys did not get create properly but maybe only because they are integer based.

When following the guidelines, the generated schemas contain field definitions with the :primary_key atom, like this:

field :created_date, :naive_datetime, :primary_key false

Which doesn't compile. What does compile is

field :created_date, :naive_datetime, primary_key: false

Although I have no idea if that was the intended output- I have never seen schemas with primary_key: false on all their fields.

when trying this on a maria DB there was no migration file generated which is maybe as important as the schemas.

Tried to run Plsm on the Fishbowl inventory system MySQL database.

➜ mix plsm                                 
Using MySql...
** (FunctionClauseError) no function clause matching in Plsm.IO.Export.map_type/1    
    
    The following arguments were given to Plsm.IO.Export.map_type/1:
    
        # 1
        :none
    
    Attempted function clauses (showing 7 out of 7):
    
        defp map_type(:decimal)
        defp map_type(:float)
        defp map_type(:string)
        defp map_type(:text)
        defp map_type(:map)
        defp map_type(:date)
        defp map_type(:integer)
    
    lib/io/export.ex:12: Plsm.IO.Export.map_type/1
    lib/io/export.ex:8: Plsm.IO.Export.type_output/1
    lib/io/export.ex:63: anonymous fn/2 in Plsm.IO.Export.prepare/2
    (elixir 1.12.2) lib/enum.ex:2385: Enum."-reduce/3-lists^foldl/2-0-"/3
    lib/io/export.ex:62: Plsm.IO.Export.prepare/2
    (elixir 1.12.2) lib/enum.ex:1582: Enum."-map/2-lists^map/1-0-"/2
    (elixir 1.12.2) lib/enum.ex:1582: Enum."-map/2-lists^map/1-0-"/2
    lib/plsm.ex:16: Mix.Tasks.Plsm.run/1

Any ideas?

Here's my version info.

➜ elixir -v
Erlang/OTP 24 [erts-12.0.3] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:1] [jit]

Elixir 1.12.2 (compiled with Erlang/OTP 22)

➜ mix deps
* connection 1.1.0 (Hex package) (mix)
  locked at 1.1.0 (connection) 722c1eb0
  ok
* db_connection 2.4.1 (Hex package) (mix)
  locked at 2.4.1 (db_connection) ea36d226
  ok
* decimal 2.0.0 (Hex package) (mix)
  locked at 2.0.0 (decimal) 34666e9c
  ok
* myxql 0.4.5 (Hex package) (mix)
  locked at 0.4.5 (myxql) 40a6166a
  ok
* plsm 2.4.0 (Hex package) (mix)
  locked at 2.4.0 (plsm) 071b79fb
  ok
* postgrex 0.15.13 (Hex package) (mix)
  locked at 0.15.13 (postgrex) 3ffb76e1
  ok
* telemetry 1.0.0 (Hex package) (rebar3)
  locked at 1.0.0 (telemetry) 73bc09fa
  ok

Currently there are no tests and that is a problem as we are relying on contributors to set up their database and test. Now, connecting to a database is going to be difficult to test as well as the query but the output is easy to test using mocks.

With ecto, if the primary key is id, we don't need this as id is default, so don't add @primary_key {:id, :integer, []}.

Currently the postgres implementation does not have primary key functionality. This should be added to coincide with the MySql changes

This is a two-parter, in the first case by default every :id field clashes with the autogenerated field. From docs:

By default, a schema will automatically generate a primary key which is named id and of type :integer.

I fixed this for my case by creating a local Schem module:

defmodule My.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key false
    end
  end
end

This turns the autogeneration off, in addition all of the generated schemas need to use My.Schema.

The second part is the primary keys that aren't called :id have the primary key override with @primary_key setting. I had to manually delete those in order to compile.

Currently the models are not able to be updated in the database because of lack of changeset function on the model. This can be fixed with a generic changeset function that can be tweaked rather than relying on the user to create one from scratch

We need to be able to find the foreign keys and make the belongs_to association. It is undecided what we should do for the referenced table as it could be has_one or has_many and it is something we may not be able to deduce from the table

Seeing as it is the de facto library for MySQL in Phoenix for a while now, could you please migrate from mariaex to myxql?

Add the ODBC connection for Sql Server so we can support another database

I ended up writing the following post-processing script to fix them:

https://gist.github.com/rugyoga/1ab523d2d6988afb0bff77e649dbf66b

Two are the same issues as @sashman has reported.

The string fields are generated with :text type when :string should be used. I think this is related to Ecto 3 changes,

I was attempting to use PLSM(master branch) this evening for a Elixir project I am working on. I was trying to generate models from my PostgresDB but can't seem to get past this error message that PLSM is putting out.

I've attached the output of it below.

I added an " |> IO.inspect()" the line before the error so I could try and see what was failing. I've also added my config that I'm using.

using elixir 1.5.2

config

config :plsm,
module_name: "test",
destination: "outputpath",
server: "localhost",
port: "5432",
database_name: "friends",
username: "postgres",
password: nil,
type: :postgres

--execution
$ mix plsm

Compiling 1 file (.ex)

Using PostgreSQL...

["defmodule test.SchemaMigrations do\n use Ecto.Schema\n\n@primary_key {:version, :integer, []}\n schema "schema_migrations" do\n field :version, :integer\n field :inserted_at, Ecto.DateTime\n\n end\n def changeset(struct, params \\ %{}) do\n struct\n |> cast(params, :version, :inserted_at)\n end\nend\n",

"defmodule test.Organizations do\n use Ecto.Schema\n\n schema "organizations" do\n field :id, :integer\n field: business_name, :text\n field: address, :text\n field :created_at, Ecto.DateTime\n field :updated_at, Ecto.DateTime\n field: thank_you_content, :text\n field: business_name_slug, :text\n field :monthly_organization_review_quota, :integer\n field :locations_count, :integer\n field :review_invitations_count, :integer\n field :partner_id, :integer\n field :location_limit, :integer\n field: default_password, :text\n field :client_success_id, :integer\n field: logo, :text\n field: first_day_of_week, :text\n field :facebook_enabled_at, Ecto.DateTime\n field :all_locations_mode_enabled_at, Ecto.DateTime\n field :kafka_enabled_at, Ecto.DateTime\n field :all_locations_view_enabled_at, Ecto.DateTime\n\n end\n def changeset(struct, params \\ %{}) do\n struct\n |> cast(params, :id, :business_name, :address, :active, :created_at, :updated_at, :thank_you_content, :business_name_slug, :send_welcome_email, :monthly_organization_review_quota, :archived, :direct_review, :test, :locations_count, :review_invitations_count, :partner_id, :use_org_name_in_cobra, :invitation_csv_report_enabled, :location_limit, :bulk_user_upload_enabled, :bulk_invite_upload_enabled, :default_password, :text_analytics_reporting_enabled, :client_success_id, :logo, :first_day_of_week, :facebook_enabled_at, :all_locations_mode_enabled_at, :kafka_enabled_at, :all_locations_view_enabled_at)\n end\nend\n",

"defmodule test.Products do\n use Ecto.Schema\n\n schema "products" do\n field :id, :integer\n field: name, :text\n\n end\n def changeset(struct, params \\ %{}) do\n struct\n |> cast(params, :id, :name)\n end\nend\n",

"defmodule test.People do\n use Ecto.Schema\n\n schema "people" do\n field :id, :integer\n field: first_name, :text\n field: last_name, :text\n field :age, :integer\n\n end\n def changeset(struct, params \\ %{}) do\n struct\n |> cast(params, :id, :first_name, :last_name, :age)\n end\nend\n"]

** (ArgumentError) argument error

:erlang.apply("defmodule test.SchemaMigrations do\n  use Ecto.Schema\n\n@primary_key {:version, :integer, []}\n  schema \"schema_migrations\" do\n    field :version, :integer\n    field :inserted_at, Ecto.DateTime\n\n  end\n  def changeset(struct, params \\\\ %{}) do\n    struct\n    |> cast(params, :version, :inserted_at)\n  end\nend\n", :name, [])

lib/plsm.ex:19: anonymous fn/2 in Mix.Tasks.Plsm.run/1

(elixir) lib/enum.ex:1270: Enum."-map/2-lists^map/1-0-"/2

(mix) lib/mix/task.ex:301: Mix.Task.run_task/3

(mix) lib/mix/cli.ex:75: Mix.CLI.run_task/2

Please i encountered this error when i ran mix plsm

sing MySql...
** (KeyError) key :rows not found in: %Mariaex.Error{action: nil, connection_id: #PID<0.168.0>, mariadb: %{code: 1064, message: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'reads' at line 1"}, message: nil, reason: nil, tag: nil}
    lib/database/mysql.ex:48: Plsm.Database.Plsm.Database.MySql.get_columns/2
    lib/plsm.ex:15: anonymous fn/1 in Mix.Tasks.Plsm.run/1
    (elixir) lib/enum.ex:1294: Enum."-map/2-lists^map/1-0-"/2
    (elixir) lib/enum.ex:1294: Enum."-map/2-lists^map/1-0-"/2
    lib/plsm.ex:15: Mix.Tasks.Plsm.run/1
    (mix) lib/mix/task.ex:314: Mix.Task.run_task/3
    (mix) lib/mix/cli.ex:80: Mix.CLI.run_task/2

Please how can i get this to work? Thank you.

What are your thoughts on allowing :id, :created_at, :updated_at to be cast, which means they can be changed? Generally I don't do this, as these fields should never be changed by the user, IMHO.

Check the tables for a primary key and if it exists we need to put the primary key attribute on the schema and then remove that field from the body of the schema. If we don't remove the primary key field from the body of the schema we will get errors.

I saw this connecting to MySQL:

** (stop) an exception was raised:
    ** (FunctionClauseError) no function clause matching in :inet_tcp.getserv/1
        (kernel 8.0.2) inet_tcp.erl:55: :inet_tcp.getserv("3306")

Fixed my updating port in the config:

port: "3306",

to

port: 3306,

I guess the config generator needs to be updated.

It is a pain to have to manually create the destination folder if it doesn't already exist. The user should be prompted if the folder doesn't exist and asked if they want to create it.

Hello, thank you for the great on this project.

I'm trying to use it in a project, but it throws dependency errors:

When using {:plsm, "~> 2.3.2"}

Failed to use "postgrex" (version 0.15.11) because
  ecto_sql (version 3.7.0) requires ~> 0.15.0 or ~> 1.0
  plsm (version 2.3.2) requires ~> 0.14.3
  mix.lock specifies 0.15.11

When using {:plsm, "~> 2.4"}

Failed to use "myxql" (version 0.5.1) because
  ecto_sql (version 3.7.0) requires ~> 0.4.0 or ~> 0.5.0
  plsm (version 2.4.0) requires ~> 0.4.4
  mix.lock specifies 0.5.1

I'm a noob in elixir and phoenix. Any help would be appreciated!

I got ** (Mix) The task "plsm.config" could not be found and ** (Mix) The task "plsm" could not be found when running the mix task in a sub directory rather than the root directory of my mix project. You said The model files are generated in the folder that you are running from. so I'm not sure it's a bug or I misunderstood.

Is it possible to pass a schema/prefix to the library if my Postgres tables are in a schema different from the default 'public' one? For ecto itself, I'm able to do this by setting a prefix, following the instructions in the Ecto docs:

### config/dev.exs
use Mix.Config

# Configuring postgres schema to use for all queries
query_args = ["SET search_path TO dev", []]

# Configure your database
config :my_app, MyApp.Repo,
  username: username,
  password: password,
  database: database,
  hostname: "localhost",
  show_sensitive_data_on_connection_error: true,
  pool_size: 10,
  after_connect: {Postgrex, :query!, query_args}

But not sure how to do something similar with Plsm.

I started sketching some code to use PostgreSQL and if try to compile you'll get a compile error. That seems caused by the last commit.
ed70d23

** (CompileError) lib/plsm.ex:14: Plsm.Database.MySql.__struct__/1 is undefined, cannot expand struct Plsm.Database.MySql

Currently the output formatting has been changed so that it no longer lines up. This should be fixed so that we generate a file that follows best practices when it comes to spacing.

Hi. I see in plsm code that there is case:

 case configs.database[:type] do
      :mysql -> IO.puts "Using MySql..."; Plsm.Database.create %Plsm.Database.MySql{}, configs
      :postgres -> IO.puts "Using PostgreSQL..."; Plsm.Database.create %Plsm.Database.PostgreSQL{}, configs
      _ -> IO.puts "Using default database MySql..."; Plsm.Database.create %Plsm.Database.MySql{}, configs
    end

but I can't find any documentation about that. How I can use plsm for postgres ?
Add type: "postgres" or type: :postgres to Plsm.config wont work.