Canvas Data Portal: Database Schema

[jared_flaherty]

Does anyone have a database creation schema that we can use so that we don't have to create them from the schema docs?    In particular we need to know about nullable fields and sizes of the double precision and varchar fields, as well as any other possible fields that needs sizes.

Thanks!

[Stef_retired]

 @jared_flaherty , I've shared your question with the Canvas Data​ and Canvas Admins​ groups.

[jared_flaherty]

Thanks!

[James · Accepted solution]

 @jared_flaherty ​,

I have some PHP code written that fetches the latest version of the schema from the API and then writes out code to create MySQL tables from it. It uses the MyISAM engine, which doesn't support foreign keys, although that information is contained in the Schema.

I've got another one that scans a folder for files matching the downloads and generates a BASH script that will gunzip (if necessary) the files and then issue the load command to load the files.

You didn't specify which database language you were looking for, so I'm not sure if that would be helpful or not. In part of my grand schema [sic] of things, I'd like to make the code so that it would support multiple databases, but it might be easier to write it using PDO so it would support whichever databases you have.  I was in a hurry to get it up and working so I could look at the data so it isn't pretty.

Unfortunately, with the holidays, I can't get it to you right away. I'm away until January and don't know when I'll get a chance to work on it.

[jared_flaherty]

Thanks, James!    I appreciate your offer, I have passed it along to our development team that is working on this project.      

Cheers!

Jared

[DanL]

Hi James!

I would be VERY interested in your PHP code for grabbing this.

Is the the Canvas Data Portal API you get it from?  Or the standard Canvas LMS API?

Appreciate anything you can provide!

Thanks!

Dan