Re: Working with the .lua file in migration toolkit
Posted by:
Kshitij
Date: September 21, 2005 09:58AM
Hey mike
I am one of the team members working on this project
the create table definitions from MS SQL are as follows:
-----------------------------------------------------------------------------------
CREATE TABLE [work_item_attribute] (
[work_item_id] [int] NOT NULL ,
[attribute_number] [int] NOT NULL ,
[repeatable_series_index] [int] NOT NULL CONSTRAINT [DF_work_item_attribute_repeatable_series_index] DEFAULT (1),
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[routable_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_routable_flag] DEFAULT (0),
[string_value] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[integer_value] [int] NULL ,
[float_value] [float] NULL ,
[datetime_value] [datetime] NULL ,
[last_update_datetime] [datetime] NOT NULL CONSTRAINT [DF_work_item_attribute_last_update_datetime] DEFAULT (getutcdate()),
[last_update_userid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_work_item_attribute_last_update_user] DEFAULT (user_name()),
[work_item_type_id] [int] NOT NULL ,
[boolean_value] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_boolean_value] DEFAULT (0),
[last_update_device] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[work_item_attribute_guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_work_item_attribute_work_item_attribute_guid] DEFAULT (newid()),
CONSTRAINT [PK_work_item_attribute] PRIMARY KEY NONCLUSTERED
(
[work_item_attribute_guid]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_work_item_attribute_work_item] FOREIGN KEY
(
[work_item_id]
) REFERENCES [work_item] (
[work_item_id]
),
CONSTRAINT [FK_work_item_attribute_work_item_attribute_type] FOREIGN KEY
(
[work_item_type_id],
[attribute_number]
) REFERENCES [work_item_attribute_type] (
[work_item_type_id],
[attribute_number]
)
) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------
CREATE TABLE [work_item] (
[work_item_id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[created_datetime] [datetime] NOT NULL CONSTRAINT [DF_work_item_created_datetime] DEFAULT (getutcdate()),
[last_update_datetime] [datetime] NOT NULL CONSTRAINT [DF_work_item_last_update_datetime] DEFAULT (getutcdate()),
[last_update_userid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_work_item_last_update_userid] DEFAULT (user_name()),
[work_item_type_id] [int] NOT NULL ,
[string_value] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted_datetime] [datetime] NULL ,
[scheduled_start_datetime] [datetime] NULL ,
[scheduled_end_datetime] [datetime] NULL ,
[scheduled_duration] [int] NULL ,
[scheduled_tz_code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[scheduled_tz_stf] [int] NULL ,
[actual_start_datetime] [datetime] NULL ,
[actual_end_datetime] [datetime] NULL ,
[actual_duration] [int] NULL ,
[customer_attribute_number] [int] NULL ,
[work_item_priority_id] [int] NOT NULL ,
[customer_id] [int] NULL ,
[location_id] [int] NULL ,
[status_changed_datetime] [datetime] NULL ,
[current_status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[division_id] [int] NULL ,
[work_item_status_id] [int] NULL CONSTRAINT [DF_work_item_work_item_status_id] DEFAULT (1),
[job_category_guid] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[customer_type_guid] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[inner_label] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[outer_label] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[alert_text] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[company_reference_number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_user_id] [int] NULL ,
[company_landmark_guid] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_work_item] PRIMARY KEY CLUSTERED
(
[work_item_id]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_work_item_company_customer_type] FOREIGN KEY
(
[customer_type_guid]
) REFERENCES [company_customer_type] (
[customer_type_guid]
),
CONSTRAINT [FK_work_item_company_job_category] FOREIGN KEY
(
[job_category_guid]
) REFERENCES [company_job_category] (
[job_category_guid]
),
CONSTRAINT [fk_work_item_company_landmark] FOREIGN KEY
(
[company_landmark_guid]
) REFERENCES [company_landmark] (
[company_landmark_guid]
),
CONSTRAINT [FK_work_item_division] FOREIGN KEY
(
[division_id]
) REFERENCES [division] (
[division_id]
),
CONSTRAINT [FK_work_item_location] FOREIGN KEY
(
[location_id]
) REFERENCES [location] (
[location_id]
),
CONSTRAINT [FK_work_item_user] FOREIGN KEY
(
[current_user_id]
) REFERENCES [user] (
[user_id]
),
CONSTRAINT [FK_work_item_work_item_priority] FOREIGN KEY
(
[work_item_priority_id]
) REFERENCES [work_item_priority] (
[work_item_priority_id]
),
CONSTRAINT [FK_work_item_work_item_status] FOREIGN KEY
(
[work_item_status_id]
) REFERENCES [work_item_status] (
[work_item_status_id]
),
CONSTRAINT [FK_work_item_work_item_type] FOREIGN KEY
(
[work_item_type_id]
) REFERENCES [work_item_type] (
[work_item_type_id]
)
) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------
CREATE TABLE [work_item_attribute_type] (
[work_item_type_id] [int] NOT NULL ,
[attribute_number] [int] NOT NULL ,
[sequence_number] [int] NOT NULL ,
[attribute_datatype_id] [int] NOT NULL ,
[name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[default_string_value] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_update_datetime] [datetime] NOT NULL CONSTRAINT [DF__work_item__last___0DF9F0CA] DEFAULT (getutcdate()),
[last_update_userid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_work_item_attribute_type_last_update_userid] DEFAULT (user_name()),
[population_rule_id] [int] NULL ,
[repeating_attribute_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_repeating_attribute_flag] DEFAULT (0),
[send_to_device_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_send_to_device_flag] DEFAULT (1),
[device_work_list_sequence_number] [int] NULL ,
[status_attribute_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_status_attribute_flag] DEFAULT (0),
[searchable_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_searchable_flag] DEFAULT (0),
[deleted_datetime] [datetime] NULL ,
[required_attribute_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_required_attribute_flag] DEFAULT (0),
[required_for_connector_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_connector_required_flag] DEFAULT (0),
[mobile_screen_attribute_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_mobile_screen_attribute_flag] DEFAULT (0),
[schedule_attribute_flag] [bit] NOT NULL CONSTRAINT [DF_work_item_attribute_type_schedule_attribute_flag] DEFAULT (0),
CONSTRAINT [PK_work_item_attribute_type] PRIMARY KEY CLUSTERED
(
[work_item_type_id],
[attribute_number]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_work_item_attribute_type_work_item_type] FOREIGN KEY
(
[work_item_type_id]
) REFERENCES [work_item_type] (
[work_item_type_id]
)
) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------