Skip to content

Instantly share code, notes, and snippets.

@bittercoder
Created August 28, 2012 08:19
Show Gist options
  • Save bittercoder/3496111 to your computer and use it in GitHub Desktop.
Save bittercoder/3496111 to your computer and use it in GitHub Desktop.
Creating Quartz tables using Migrator.Net
Database.RemoveForeignKey("QRTZ_CRON_TRIGGERS", "FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS");
Database.RemoveForeignKey("QRTZ_SIMPLE_TRIGGERS", "FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS");
Database.RemoveForeignKey("QRTZ_SIMPROP_TRIGGERS", "FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS");
Database.RemoveForeignKey("QRTZ_TRIGGERS", "FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS");
Database.RemoveTable("qrtz_calendars");
Database.RemoveTable("qrtz_cron_triggers");
Database.RemoveTable("qrtz_fired_triggers");
Database.RemoveTable("qrtz_paused_trigger_grps");
Database.RemoveTable("qrtz_scheduler_state");
Database.RemoveTable("qrtz_locks");
Database.RemoveTable("qrtz_job_details");
Database.RemoveTable("qrtz_simple_triggers");
Database.RemoveTable("qrtz_simprop_triggers");
Database.RemoveTable("qrtz_blob_triggers");
Database.RemoveTable("qrtz_triggers");
Database.AddTable("qrtz_calendars",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("CALENDAR_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("CALENDAR", DbType.Binary, Maximums.BlobLength, ColumnProperty.NotNull));
Database.AddTable("qrtz_cron_triggers",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("CRON_EXPRESSION", DbType.String, 120, ColumnProperty.NotNull),
new Column("TIME_ZONE_ID", DbType.String, 120, ColumnProperty.Null));
Database.AddTable("qrtz_fired_triggers",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("ENTRY_ID", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_NAME", DbType.String, 200, ColumnProperty.NotNull),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull),
new Column("INSTANCE_NAME", DbType.String, 200, ColumnProperty.NotNull),
new Column("FIRED_TIME", DbType.Int64, ColumnProperty.Null),
new Column("PRIORITY", DbType.Int32, ColumnProperty.Null),
new Column("STATE", DbType.String, 16, ColumnProperty.NotNull),
new Column("JOB_NAME", DbType.String, 200, ColumnProperty.Null),
new Column("JOB_GROUP", DbType.String, 200, ColumnProperty.Null),
new Column("IS_NONCONCURRENT", DbType.Boolean, ColumnProperty.Null),
new Column("REQUESTS_RECOVERY", DbType.Boolean, ColumnProperty.Null));
Database.AddTable("qrtz_paused_trigger_grps",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey));
Database.AddTable("qrtz_scheduler_state",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("INSTANCE_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("LAST_CHECKIN_TIME", DbType.Int64, ColumnProperty.Null),
new Column("CHECKIN_INTERVAL", DbType.Int64, ColumnProperty.Null));
Database.AddTable("qrtz_locks",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("LOCK_NAME", DbType.String, 40, ColumnProperty.NotNull | ColumnProperty.PrimaryKey));
Database.AddTable("qrtz_job_details",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("JOB_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("JOB_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("DESCRIPTION", DbType.String, 250, ColumnProperty.Null),
new Column("JOB_CLASS_NAME", DbType.String, 250, ColumnProperty.NotNull),
new Column("IS_DURABLE", DbType.Boolean, ColumnProperty.NotNull),
new Column("IS_NONCONCURRENT", DbType.Boolean, ColumnProperty.NotNull),
new Column("IS_UPDATE_DATA", DbType.Boolean, ColumnProperty.NotNull),
new Column("REQUESTS_RECOVERY", DbType.Boolean, ColumnProperty.NotNull),
new Column("JOB_DATA", DbType.Binary, Maximums.BlobLength, ColumnProperty.Null));
Database.AddTable("qrtz_simple_triggers",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("REPEAT_COUNT", DbType.Int32, ColumnProperty.NotNull),
new Column("REPEAT_INTERVAL", DbType.Int64, ColumnProperty.NotNull),
new Column("TIMES_TRIGGERED", DbType.Int32, ColumnProperty.NotNull));
Database.AddTable("qrtz_simprop_triggers",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("STR_PROP_1", DbType.String, 512, ColumnProperty.Null),
new Column("STR_PROP_2", DbType.String, 512, ColumnProperty.Null),
new Column("STR_PROP_3", DbType.String, 512, ColumnProperty.Null),
new Column("INT_PROP_1", DbType.Int32, ColumnProperty.Null),
new Column("INT_PROP_2", DbType.Int32, ColumnProperty.Null),
new Column("LONG_PROP_1", DbType.Int64, ColumnProperty.Null),
new Column("LONG_PROP_2", DbType.Int64, ColumnProperty.Null),
new Column("DEC_PROP_1", DbType.Decimal, ColumnProperty.Null),
new Column("DEC_PROP_2", DbType.Decimal, ColumnProperty.Null),
new Column("BOOL_PROP_1", DbType.Boolean, ColumnProperty.Null),
new Column("BOOL_PROP_2", DbType.Boolean, ColumnProperty.Null));
Database.AddTable("qrtz_blob_triggers",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("JOB_DATA", DbType.Binary, Maximums.BlobLength, ColumnProperty.Null));
Database.AddTable("qrtz_triggers",
new Column("SCHED_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_NAME", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("TRIGGER_GROUP", DbType.String, 200, ColumnProperty.NotNull | ColumnProperty.PrimaryKey),
new Column("JOB_NAME", DbType.String, 200, ColumnProperty.NotNull),
new Column("JOB_GROUP", DbType.String, 200, ColumnProperty.NotNull),
new Column("DESCRIPTION", DbType.String, 250, ColumnProperty.Null),
new Column("NEXT_FIRE_TIME", DbType.Int64, ColumnProperty.Null),
new Column("PREV_FIRE_TIME", DbType.Int64, ColumnProperty.Null),
new Column("PRIORITY", DbType.Int32, ColumnProperty.Null),
new Column("TRIGGER_STATE", DbType.String, 16, ColumnProperty.NotNull),
new Column("TRIGGER_TYPE", DbType.String, 8, ColumnProperty.NotNull),
new Column("START_TIME", DbType.Int64, ColumnProperty.Null),
new Column("END_TIME", DbType.Int64, ColumnProperty.Null),
new Column("CALENDAR_NAME", DbType.String, 200, ColumnProperty.Null),
new Column("MISFIRE_INSTR", DbType.Int32, ColumnProperty.Null),
new Column("JOB_DATA", DbType.Binary, Maximums.BlobLength, ColumnProperty.Null));
Database.AddForeignKey("FK_QRTZ_CRON_TRIGGERS_QRTZ_TRIGGERS","QRTZ_CRON_TRIGGERS",new string[] { "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP" }, "QRTZ_TRIGGERS", new string[] { "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP" },ForeignKeyConstraint.Cascade);
Database.AddForeignKey("FK_QRTZ_SIMPLE_TRIGGERS_QRTZ_TRIGGERS", "QRTZ_SIMPLE_TRIGGERS", new string[] { "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP" }, "QRTZ_TRIGGERS", new string[] { "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP" }, ForeignKeyConstraint.Cascade);
Database.AddForeignKey("FK_QRTZ_SIMPROP_TRIGGERS_QRTZ_TRIGGERS", "QRTZ_SIMPROP_TRIGGERS", new string[] { "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP" }, "QRTZ_TRIGGERS", new string[] { "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP" }, ForeignKeyConstraint.Cascade);
Database.AddForeignKey("FK_QRTZ_TRIGGERS_QRTZ_JOB_DETAILS", "QRTZ_TRIGGERS", new string[] { "SCHED_NAME", "JOB_NAME", "JOB_GROUP" }, "QRTZ_JOB_DETAILS", new string[] { "SCHED_NAME", "JOB_NAME", "JOB_GROUP" }, ForeignKeyConstraint.Cascade);
Database.AddIndex("IDX_QRTZ_T_J", "QRTZ_TRIGGERS", "SCHED_NAME","JOB_NAME","JOB_GROUP");
Database.AddIndex("IDX_QRTZ_T_JG", "QRTZ_TRIGGERS", "SCHED_NAME", "JOB_GROUP");
Database.AddIndex("IDX_QRTZ_T_C", "QRTZ_TRIGGERS", "SCHED_NAME", "CALENDAR_NAME");
Database.AddIndex("IDX_QRTZ_T_G", "QRTZ_TRIGGERS", "SCHED_NAME", "TRIGGER_GROUP");
Database.AddIndex("IDX_QRTZ_T_STATE", "QRTZ_TRIGGERS", "SCHED_NAME", "TRIGGER_STATE");
Database.AddIndex("IDX_QRTZ_T_N_STATE", "QRTZ_TRIGGERS", "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP", "TRIGGER_STATE");
Database.AddIndex("IDX_QRTZ_T_N_G_STATE", "QRTZ_TRIGGERS", "SCHED_NAME", "TRIGGER_GROUP", "TRIGGER_STATE");
Database.AddIndex("IDX_QRTZ_T_NEXT_FIRE_TIME", "QRTZ_TRIGGERS", "SCHED_NAME", "NEXT_FIRE_TIME");
Database.AddIndex("IDX_QRTZ_T_NFT_ST", "QRTZ_TRIGGERS", "SCHED_NAME", "TRIGGER_STATE", "NEXT_FIRE_TIME");
Database.AddIndex("IDX_QRTZ_T_NFT_MISFIRE", "QRTZ_TRIGGERS", "SCHED_NAME", "MISFIRE_INSTR", "NEXT_FIRE_TIME");
Database.AddIndex("IDX_QRTZ_T_NFT_ST_MISFIRE", "QRTZ_TRIGGERS", "SCHED_NAME", "MISFIRE_INSTR", "NEXT_FIRE_TIME", "TRIGGER_STATE");
Database.AddIndex("IDX_QRTZ_T_NFT_ST_MISFIRE_GRP", "QRTZ_TRIGGERS", "SCHED_NAME", "MISFIRE_INSTR", "NEXT_FIRE_TIME", "TRIGGER_GROUP", "TRIGGER_STATE");
Database.AddIndex("IDX_QRTZ_FT_TRIG_INST_NAME", "QRTZ_FIRED_TRIGGERS", "SCHED_NAME", "INSTANCE_NAME");
Database.AddIndex("IDX_QRTZ_FT_INST_JOB_REQ_RCVRY", "QRTZ_FIRED_TRIGGERS", "SCHED_NAME", "INSTANCE_NAME", "REQUESTS_RECOVERY");
Database.AddIndex("IDX_QRTZ_FT_J_G", "QRTZ_FIRED_TRIGGERS", "SCHED_NAME", "JOB_NAME", "JOB_GROUP");
Database.AddIndex("IDX_QRTZ_FT_JG", "QRTZ_FIRED_TRIGGERS", "SCHED_NAME", "JOB_GROUP");
Database.AddIndex("IDX_QRTZ_FT_T_G", "QRTZ_FIRED_TRIGGERS", "SCHED_NAME", "TRIGGER_NAME", "TRIGGER_GROUP");
Database.AddIndex("IDX_QRTZ_FT_TG", "QRTZ_FIRED_TRIGGERS", "SCHED_NAME", "TRIGGER_GROUP");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment