package dbi and reverse engineer

[wilcar]

Hello, Very interesting ! Can we use dbi package to make a reverse engineer diagramm ?

   library(DBI)
   inscrits_nn<- dbConnect(odbc::odbc(),
   driver = "MySQL ODBC 5.3 Unicode Driver",
   database = "inscrits_nn",
   uid = "root",
   host = "localhost",
   port = 3306)
   dm <- as.data_model(inscrits_nn)

Message : Error in UseMethod("as.data_model") : no applicable method for 'as.data_model' applied to an object of class "c('MySQL', 'OdbcConnection', 'DBIConnection', 'DBIObject')"

[bergant]

Yes, it is possible to reverse engineer from MySQL. All you have to do is an SQL query that returns all tables, columns and references. See existing queries for SQL Server and Postgres (https://github.com/bergant/datamodelr/tree/master/inst/sql).

Save the results in a data frame and then use as.data_model on this data.

[wilcar]

Thank you for helping. but I need more help !

I ma connecting to local db witrh this code :

   library(DBI)
  inscrits_nn<- dbConnect(odbc::odbc(),
  driver = "MySQL ODBC 5.3 Unicode Driver",
  database = "inscrits_nn",
  uid = "root",
 host = "localhost",
  port = 3306)

Based on your read.me, I am note able to adapt this piece of code from "sqlserver" to my sql db

 sQuery <- dm_re_query("sqlserver")
 dm_northwind <- sqlQuery(con, sQuery, stringsAsFactors = FALSE, errors=TRUE)
odbcClose(con)

[SFrav]

Is there a translation of the SQL Server query to MySQL? I have started to work on it but it’s slow going. For example, sys.all_columns may equate to INFORMLTION_SCHEMA.columns.