Issue with Generated SQL by DAP Sync and INIT, BIT default value invalid

jasonmiciano

We're having issues with the SQL generated by the DAP. I was on the 1.3.1, and now using 1.4.0,  to see if that might resolve the issue.

I'm using the python dap module to sync/init  and using the MS SQL Dialect for our canvas data 2 tables and I noticed its having issues executing ALTERS and CREATES. This is keeping sync or any init to occur and is keeping me from updating my data.

Below is the generated sql its getting an error from:

CREATE TABLE "canvas"."groups" (
"id" bigint NOT NULL,
"name" varchar(255),
"deleted_at" datetime2,
"storage_quota" bigint,
"lti_context_id" varchar(255),
"created_at" datetime2 NOT NULL,
"updated_at" datetime2 NOT NULL,
"workflow_state" integer NOT NULL,
"account_id" bigint NOT NULL,
"sis_batch_id" bigint,
"context_id" bigint NOT NULL,
"context_type" integer NOT NULL,
"migration_id" varchar(255),
"group_category_id" bigint,
"sis_source_id" varchar(255),
"is_public" bit,
"wiki_id" bigint,
"max_membership" integer,
"join_level" varchar(255),
"avatar_attachment_id" bigint,
"leader_id" bigint,
"description" varchar(max),
"uuid" varchar(255) NOT NULL,
"default_view" integer,
"non_collaborative" bit CONSTRAINT "df_non_collaborative" DEFAULT FALSE,
CONSTRAINT "pk_canvas_groups" PRIMARY KEY ("id")
);





ALSO: 



ALTER TABLE "canvas"."assignment_groups" ALTER COLUMN "group_weight" double precision;
ALTER TABLE "canvas"."assignments" ALTER COLUMN "points_possible" double precision;
ALTER TABLE "canvas"."content_migrations" ALTER COLUMN "progress" double precision;
ALTER TABLE "canvas"."content_tags" ALTER COLUMN "mastery_score" double precision;
ALTER TABLE "canvas"."grading_periods" ALTER COLUMN "weight" double precision;
ALTER TABLE "canvas"."group_categories" ADD "non_collaborative" bit CONSTRAINT "df_non_collaborative" DEFAULT FALSE;
ALTER TABLE "canvas"."groups" ADD "non_collaborative" bit CONSTRAINT "df_non_collaborative" DEFAULT FALSE;
ALTER TABLE "canvas"."learning_outcome_question_results" ALTER COLUMN "score" double precision;
ALTER TABLE "canvas"."learning_outcome_question_results" ALTER COLUMN "possible" double precision;
ALTER TABLE "canvas"."learning_outcome_question_results" ALTER COLUMN "original_score" double precision;

The error comes from these lines: 

ALTER TABLE "canvas"."groups" ADD "non_collaborative" bit CONSTRAINT "df_non_collaborative" DEFAULT FALSE;

"non_collaborative" bit CONSTRAINT "df_non_collaborative" DEFAULT FALSE,

The issue comes from the default value for bits in sql server has to be 0 or 1, can't be FALSE without the quotes. Can someone confirm this issue and let me know if its being worked on?

sgergely

Hello everyone!

Thank you for the patience, we have managed to hunt down the bug and managed to fix it in one of the dependency libraries that is used by the DAP CLI. 
We will not release a bugfix version for DAP CLI but you can run 

pip install -U pysqlsync

to fix the issue.

Once this package has been updated, your issue should be fixed, initdb and syncdb should work seamlessly for MSSQL for such use cases.