Tool for generating schema for existing mysql database

Questions / Help
1

Hi,

I have 200 tables with MySQL Database. I interested to rebuild my application with elixir and phoenix, but I couldn’t find any tools to generate my schema. I have tried to use ecto.load and ecto.dump but it is not generated the schema, even thought I can work with the query.

I want to use schema, if anyone can recommend any tools please.

thanks

2

I am not sure it is possible because You don’t have enough information.

It is ok to go from schema, to migration, to sql… but not really the other way around.

For example, from key, assoc_id, You cannot tell wether it is has_one or has_many.

Or special foreign keys, like owner_id pointing to users table. That would be difficult to guess.

3

I’ve created ecto_generator which will dump the schema without relationships. I tried to add that command to ecto but was denied in pull request: https://github.com/elixir-ecto/ecto/pull/1722

4 Likes
4

I’ve used plsm in the past for a fairly large project to great success.

2 Likes
5

this is error with erl 20

this is the error message:

23:02:11.699 [debug] QUERY OK db=15.0ms
SELECT COLUMN_NAME, DATA_TYPE, CASE WHEN `COLUMN_KEY` = 'PRI' THEN '1' ELSE NULL END AS primary_key FROM information_schema.columns WHERE table_name= 'act_status' and table_schema='worknhol_db' []
** (MatchError) no match of right hand side value: {:error, :enoent}
    lib/mix/tasks/ecto.dump.schema.ex:194: Mix.Tasks.Ecto.Dump.Schema.write_model/2
    (elixir) lib/enum.ex:737: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:737: Enum.each/2
    (elixir) lib/enum.ex:737: Enum."-each/2-lists^foreach/1-0-"/2
    (elixir) lib/enum.ex:737: Enum.each/2
    (mix) lib/mix/task.ex:314: Mix.Task.run_task/3
    (mix) lib/mix/cli.ex:80: Mix.CLI.run_task/2
    (elixir) lib/code.ex:677: Code.require_file/2
6

this not working with phoenix 1.3

7

I’ve used schemaspy for documentation.

Graphical Database Schema Metadata Browser

It basically generates a html with nice UML’s for your schema.

A bash script could be like:

#!/bin/bash

# usage: ./convert_schema.sh data_dev output

build=schemaSpy_5.0.0.jar
driver=postgresql-42.1.1.jre6.jar
schema=public
host=localhost
dbType=pgsql
dbName=$1
user=postgres
password=postgres
outputDir=$2

java -jar $build -host $host -t $dbType -s $schema -cp $driver -db $dbName -u $user -p $password -o $outputDir
2 Likes