schema_to_mysql.php not working

Question

schema_to_mysql.php not working

Closed this issue 8 years ago · 12 comments

Hi James

Here is the results for running the program today.

# MySQL script to create database for Canvas Data schema version { DROP DATABASE IF EXISTS canvas_data; CREATE DATABASE IF NOT EXISTS canvas_data; USE canvas_data; SET NAMES utf8; DROP TABLE IF EXISTS versions; CREATE TABLE IF NOT EXISTS versions ( table_name VARCHAR(127) PRIMARY KEY NOT NULL COMMENT 'Name of Canvas Data table', version BIGINT DEFAULT NULL COMMENT 'Latest version downloaded', incremental TINYINT DEFAULT NULL COMMENT 'Incremental (1) or complete (0)?' ) ENGINE = MyISAM DEFAULT CHARSET=utf8 COMMENT = "Used by import script"; INSERT INTO versions (table_name, incremental, version) VALUES ('schema',-1,0);

There is no problem with the schema file fetched from Canvas Data, it seems there is something wrong with the "create_mysql_schema" function. But I am not sure. Hope you may take a look at that when you have time.

Thanks so much for your great work!

Best,
Mu

Answer 1 · 2016-05-03T20:28:56.000Z

Can you provide some more specifics?

I tested it from within Eclipse on Windows and from the command line in Ubuntu. The Windows version had an existing schema.json file (I updated for 1.10.1) and the Ubuntu version downloaded from the Canvas Data site. I tried all four combinations of drop_schema and comments in the $options section. All of them created dumped the mysql content to the console, which I redirected to a file.

I did go ahead and update the actual mysql scripts that are generated inside the mysql folder while I was at it. I hadn't been paying attention, so thanks for alerting me of the change.

Answer 2 · 2016-05-03T20:31:41.000Z

Was that the entire output that you pasted? If so, there was a problem with the schema.json -- it couldn't find one or it wasn't in a recognizable form and it couldn't parse it. The versions table is one I add for my import.sh script to run, but since that's all that it put in there, it's not getting anything from the schema.json file.

Answer 3 · 2016-05-03T20:36:23.000Z

I'm new to GitHub and this is my first issue, so I'm not quite sure how
it works. I replied on GitHub, but since I don't know how it works, I'm
not sure you also get an email notifying you.

After doing lots of testing and not having any issues, I had another
question.

Did you paste the entire output? If so, there was a problem with the
schema.json -- it couldn't find one or it wasn't in a recognizable form
and it couldn't parse it. The versions table is one I add for my
import.sh script to run, but since that's all that it put in there, it's
not getting anything from the schema.json file.

You might want to double check it and make sure it's all there.

James

On 5/3/2016 2:42 PM, Mu wrote:

Hi James

Here is the results for running the program today.

|# MySQL script to create database for Canvas Data schema version {
DROP DATABASE IF EXISTS canvas_data;
CREATE DATABASE IF NOT EXISTS canvas_data;
USE canvas_data;
SET NAMES utf8;
DROP TABLE IF EXISTS versions;
CREATE TABLE IF NOT EXISTS versions (
table_name VARCHAR(127) PRIMARY KEY NOT NULL COMMENT 'Name of Canvas
Data table',
version BIGINT DEFAULT NULL COMMENT 'Latest version downloaded',
incremental TINYINT DEFAULT NULL COMMENT 'Incremental (1) or complete
(0)?'
) ENGINE = MyISAM DEFAULT CHARSET=utf8 COMMENT = "Used by import script";
INSERT INTO versions (table_name, incremental, version) VALUES
('schema',-1,0);
|

There is no problem with the schema file fetched from Canvas Data, it
seems there is something wrong with the "create_mysql_schema"
function. But I am not sure. Hope you may take a look at that when you
have time.

Thanks so much for your great work!

Best,
Mu

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub
#1

Answer 4 · 2016-05-04T20:54:49.000Z

Thanks for the prompt feedback @jamesjonesmath ! That's the entire output. I don't have schema.json there. In the document, you indicate that schema.json is not necessary, if the API key and secret is provided. If it is required, would you mind to tell me how to generate that file?

Moreover, by following your tutorial, I am able to get the download urls for all the tables, but do you have any script that can automatically download those files to a server? Thanks in advance!

Best,
Mu

Answer 5 · 2016-05-04T23:38:28.000Z

schema.json is not required, but the api.php file is if you don't have a schema.json file available.

I think we may be speaking about different things but using language so close to each other that we think the other person is talking about the same thing we are.

You say you don't have a schema.json, but you say that the schema file from Canvas Data is fine. What I'm hearing is "You have a schema.json file that you downloaded using the Canvas Data CLI tool."

If you just took the page https://portal.inshosteddata.com/docs and saved it as schema.json, then it will not work because that's not a JSON file, it's an HTML file that explains what's in the JSON file. I'm talking about the JSON file that is available using the GET /api/schema/latest from the Canvas Data API. However, the Canvas Data CLI will obtain that and save it for you. If there is a file called schema.json

Then you talk about a script that will download the files to a server. That's what the Canvas Data CLI tool does. That's part of why I think we might be talking about different things. If you're using the Canvas Data CLI tool, then you will get all of the files and a schema.json file that you can use with my script to generate MySQL files.

I abandoned my plans to write my own routines in PHP to download files since the Canvas Data CLI does that part and I had other things to do. The CLI will download the files and a schema.json without you having to mess with writing your own code to compute HMAC signatures. The schema_to_mysql.php file takes the schema and converts it into a MySQL script that you can use to import it. If you combine it with the import.sh script in the MySQL folder, then it will even import the information that the CLI downloaded into MySQL for you.

Now, all of that said, I was able to duplicate the results you're getting, along with some undefined index errors. It looks like you have not specified your API key and API secret in a way that the system can find it. You can either put it into the file itself on lines 34 and 35 or you can put it into the environment as CD_API_KEY and CD_API_SECRET (which happen to be the same environment variables the Canvas Data CLI tool use). I've tested the environment variable portion on Ubuntu, but I have not tested it on Windows or on a Mac.

So, it appears that you're not getting your API information into the code and that's what is causing the problem. But there could be some better error checking done on my part to make sure people are putting in the values.

Answer 6 · 2016-05-05T16:59:17.000Z

Hi James,

Sorry for the confusion. There is no "schema.json", but I did put the API key & secret in the file. And I've added var_dump($schema) to make sure the API is working.

if (! isset( $schema )) { if (empty( $cd_api_key ) || empty( $cd_api_secret )) {
    die( 'Must specify Canvas Data API and Secret before running' );
  }
  require (__DIR__ . '/api.php');
  $CDAPI = new CanvasDataAPI( $cd_api_key, $cd_api_secret );
  $schema = $CDAPI->get_schema_latest();
}

var_dump($schema);

Here is my configuration.

$schema_name = 'canvas_data';
$schema_file = 'schema.json';
$cd_api_key = getenv( 'CD_API_KEY' ) !== FALSE ? getenv( 'CD_API_KEY' ) : '9fxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
$cd_api_secret = getenv( 'CD_API_SECRET' ) !== FALSE ? getenv( 'CD_API_SECRET' ) : '30xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
$options = array ( 
    'drop_schema' => TRUE, 
    'comments' => TRUE 
);
$output_filename = 'create_canvas_data.sql';

Here are the results.

# MySQL script to create database for Canvas Data schema version {
DROP DATABASE IF EXISTS canvas_data;
CREATE DATABASE IF NOT EXISTS canvas_data;
USE canvas_data;
SET NAMES utf8;
DROP TABLE IF EXISTS versions;
CREATE TABLE IF NOT EXISTS versions (
  table_name VARCHAR(127) PRIMARY KEY NOT NULL COMMENT 'Name of Canvas Data table',
  version BIGINT DEFAULT NULL COMMENT 'Latest version downloaded',
  incremental TINYINT DEFAULT NULL COMMENT 'Incremental (1) or complete (0)?'
) ENGINE = MyISAM DEFAULT CHARSET=utf8 COMMENT = "Used by import script";
INSERT INTO versions (table_name, incremental, version) VALUES
  ('schema',-1,0);

I have manually created the "schema.json", and everything works fine.

$schema = $CD->get_schema_latest();
$fp = fopen('./schema.json', 'w');
fwrite($fp, $schema);
fclose($fp);

Therefore, in my case, it seems the program only works when the "schema.json" is in place. Please let me know if there is anything else I can provide.

Answer 7 · 2016-05-05T17:04:35.000Z

Also, for "import.sh", is there anyway to exclude a table (such as, requests)?

Answer 8 · 2016-05-05T18:08:30.000Z

Make sure you're using the latest versions -- including the api.php file
I updated in April.

Sometimes, Canvas Data was returning an error message instead of an
empty object and other times it was returning an empty object when the
fetching failed. Yesterday I added a check for that and today (just now)
I added an extra check to make sure the schema is an array. It should
die if either of those fail.

I'm surprised by you saying it works when you save the schema.json file.
The get_latest_schema() function returns a PHP structure, but the file
that this script is expecting to read needs to be a JSON file. When I
run your code I get a warning that reflects that.

PHP Warning: fwrite() expects parameter 2 to be string, array given in
/tmp/schema_to_mysql.php

You may not have warnings enabled on your site, but unless you force the
api.php to return the content as JSON, the results of
get_latest_schema() are not the same as what's in the schema.json file.

Everything you're saying is consistent with having something in $schema,
but not the right thing. Hopefully those extra checks I added in the
last 24 hours will catch that.

Answer 9 · 2016-05-05T18:22:48.000Z

Regarding the import.sh script: You can skip the requests by moving them out of the folder it's scanning. But then you need to move them back in before the next sync if you're using the Canvas Data CLI tool. Alternatively, if there are just a few that you want to use, you could copy those to a separate folder. You could also create a folder that contains soft links to the folders you want to process and then make that the base folder.

If you don't want to go that router, you can also remove them by modifying the find statement. Before the -printf clause, add ! -name "requests" to exclude anything matching requests. You can add additional statements, like ! -name "submission", if you want to exclude other ones.

There are lots of things you could do, but the script was intended to be a quick (not comprehensive) way to get the data into MySQL so people could focus on the analyzing and not the data obtaining.

Answer 10 · 2016-05-06T20:46:53.000Z

@jamesjonesmath everything works now, thanks much for your advice!!!

For "import.sh", I have to use ! -name "requests*" to exclude the requests tables.

Thanks again and again!

Best,
Mu

Answer 11 · 2016-05-08T15:41:32.000Z

I just noticed that the * I had put in there had gotten swallowed up by markdown code. I went back to my comment and escaped it so the * shows up. I use a * both before and after the requests.

Glad you got it working.

Answer 12 · 2022-11-16T08:01:54.000Z

@jamesjonesmath everything works now, thanks much for your advice!!!

For "import.sh", I have to use ! -name "requests*" to exclude the requests tables.

Thanks again and again!

Best, Mu

is the import.sh, file working you as I am not able extract files and store in tables some issue coming like -
sysadmin@praveen-laptop:/var/www/html/canvancement/canvas-data/mysql$ sudo bash import.sh
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Processing ../../../dataFiles/account_dim/account_dim-00000-42a92ed7
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Processing ../../../dataFiles/assignment_dim/assignment_dim-00000-e3214611

can you please help or share working copy of import.sh file.