dm_learn_from_db and MySQL

[ericemc3]

looking forward to playing with this within MySQL...

[krlmlr]

I wonder if postgres_learn_query() works well enough for MariaDB? I never tried, would review a pull request.

[ericemc3]

It won't probably work as if, because you won't find in MySQL the same exact column names.
We are (in my company) implementing the same db model with various databases (such as MySQL, Postgres or MsSQL).

Useful requests for MySQL would be something like:

/*foreign keys*/    
SELECT column_name, constraint_name, referenced_table_name, referenced_column_name, table_name, ordinal_position
		FROM information_schema.key_column_usage
		WHERE referenced_table_name IS NOT NULL
		and table_schema = 'myschema'
		order by table_name,ordinal_position
			
/*primary keys*/  			
SELECT column_name, constraint_name, referenced_table_name, referenced_column_name, table_name, ordinal_position
		FROM information_schema.key_column_usage 
		WHERE table_schema = 'myschema'
		AND constraint_name = 'primary'
		
/*column info*/  			
SELECT * FROM information_schema.columns WHERE table_schema = 'myschema'

[noamross]

Would you take a PR for this or is it dependent on your expected changes in #342 and #229?

[krlmlr]

Thanks. Let me take another stab getting the internal structure right, happy to take PRs after that.

[krlmlr]

@noamross: Now that we have an idea what dm_meta() should look like, we could take another stab.

I've found that, across databases, constraint_column_usage is the most inconsistent. It seems that REFERENTIAL_CONSTRAINTS is the thing to use in MySQL/MariaDB?

Another thing that gave me headaches for Postgres are compound keys -- constraint_column_usage seems to lack a crucial column that helps infer compound keys correctly, I worked around with a custom query against the native views.

[krlmlr]

We're very close to an implementation. Do you have a chance to test it in the next few days?

[noamross]

So currently learning constraints is failing for me on a Dolt Database. Dolt has the same information schema as MariaDB/MySQL. But while DoltConnection inherits from MariaDBConnection, src_DoltConnection does not inherit from src_MariaDBConnection. This is because dbplyr::src_dbi creates src_ classes with subclass <- paste0("src_", class(con)[[1]]), ignoring other inheritance. When I debug this and modify the class so is_mariadb() at https://github.com/cynkra/dm/pull/1123/files#diff-44b9c1498a56325c16fa5943d640390ae701cf33675e3c88c0b079708b763a5aR70 results in TRUE, foreign and primary keys are learned correctly.

[noamross]

I'm not sure the best way to fix this. Do you think a PR to dbplyr is best, even though it will take a while to work through the system?

[krlmlr]

Thanks. Would it work if we tweaked is_mariadb() here?

[noamross]

A hack including src_DoltConnection, and src_DoltLocalConnection would work.

[noamross]

Also, for Dolt (but also possibly MySQL/RMariaDB?), the data model returned includes all the tables and columns in information_schema, as well. Perhaps we want to filter out table_schema == "information_schema" by default?

[krlmlr]

Thanks. Self-reflection about information_schema is a feature, I agree we might want to leave it out if schema = NULL .

Happy to review PRs.

[krlmlr]

Dolt hack added in 337de7a.

Should we add Dolt to our CI/CD (and perhaps also to RMariaDB's)?

[noamross]

That would be great. Note that you're as likely to find bugs in Dolt as it's still pretty beta. (But the Dolt team responds fast, e.g. ecohealthalliance/doltr#46 (comment)).

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.