jhartwell / plsm Goto Github PK
View Code? Open in Web Editor NEWElixir mix task to generate Ecto models from already existing tables
License: MIT License
Elixir mix task to generate Ecto models from already existing tables
License: MIT License
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.
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.
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.
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
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!
Seeing as it is the de facto library for MySQL in Phoenix for a while now, could you please migrate from mariaex
to myxql
?
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 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
** (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
The following are missing:
numeric
-> :decimal
jsonb
-> :map
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.
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
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
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.
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.
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.
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
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
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
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
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
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
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?
Currently there is little to no documentation in the code base, which is not good.
Currently the postgres implementation does not have primary key functionality. This should be added to coincide with the MySql changes
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
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.
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.
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.
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.
jackalcooper@Jackals-Mac ~/e/subway> mix plasm
No database type passed in. Defaulting to MySql
** (UndefinedFunctionError) function :odbc.start/0 is undefined (module :odbc is not available)
:odbc.start()
lib/database/common.ex:4: Plasm.Database.Common.connect/1
lib/plasm.ex:30: Mix.Tasks.Plasm.run/1
(mix) lib/mix/task.ex:294: Mix.Task.run_task/3
(mix) lib/mix/cli.ex:58: Mix.CLI.run_task/2
The string fields are generated with :text
type when :string
should be used. I think this is related to Ecto 3 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.
MySQL 5.6
PLSM 1.1.2
Mix/Elixir 1.4.2
when trying this on a maria DB there was no migration file generated which is maybe as important as the schemas.
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
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.
Ecto does not have type text
. It should be string
I'll make a PR for this.
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.
We should use :naive_datetime instead of Ecto.DateTime
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.
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
.
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
Add the ODBC connection for Sql Server so we can support another database
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.