Manual Schema Changes

  • Article

This topic describes additional schema differences between Microsoft Commerce Server 2002/2007 and Commerce Server 2009 R2.

Although the Commerce Server Upgrade Wizard upgrades most Commerce Server resources to Commerce Server 2009 R2 automatically, there are some updates you must make manually to complete your schema upgrade.

  • Profiles System Objects

  • Marketing System Objects

  • Profiles System SQL Objects

Profiles System Objects

This section details the schema changes implemented to the Profiles System in Commerce Server 2009 R2.

UserObject

Property

Data type

Description

GeneralInfo.user_live_id

String, searchable, Unique Key

Required for integration with Windows Live logon.

If this property does not exist, an exception will be thrown when an attempt is made to retrieve a UserProfile entity by LiveId, or when an attempt is made to retrieve or modify the LiveId property of a UserProfile entity.

GeneralInfo.default_shopper_list

String

Required to retrieve the default ShopperList for a user.

If this property does not exist, an exception will be thrown if an attempt is made to retrieve or modify the DefaultShopperList property of a UserProfile entity.

GeneralInfo.preferred_shipping_method

String

Required to retrieve the PreferredShippingMethod relationship of the UserProfile entity.

If this property does not exist, an exception will be thrown if a CommerceQueryRelatedItem<ShippingMethod> (PreferredShippingMethod) is executed when retrieving a UserProfile entity.

To completely remove the dependency on this property/relationship, remove the UserProfileShippingMethodsResponseBuilder and UserProfileShippingMethodsProcessor components from ChannelConfiguration.config.

CreditCard

Property

Data type

Description

GeneralInfo.expiration_month

Number, Required

Required to support copying credit card information from a UserProfile entity to a Basket entity.

If this property does not exist, an exception will be thrown when an attempt to execute a CommerceCopyRelatedItem<CreditCard> (PaymentAccount) to copy credit card information from a UserProfile entity to the Payment of a Basket entity.

These replace the GeneralInfo.expiration_date property of the Commerce Server 2007 CreditCard profile schema.

GeneralInfo.expiration_year

Number, Required

Required to support copying credit card information from a UserProfile entity to a Basket entity.

If this property does not exist, an exception will be thrown when an attempt to execute a CommerceCopyRelatedItem<CreditCard> (PaymentAccount) to copy credit card information from a UserProfile entity to the Payment of a Basket entity.

These replace the GeneralInfo.expiration_date property of the Commerce Server 2007 CreditCard profile schema.

TargetingContext

Property

Data type

Description

Channel

String

Required to create marketing content that is targeted to a specific Commerce Server 2009 R2 sales channel.

User_locale

String

Required to create marketing content that is targeted to the locale of a shopper.

User_ui_locale

String

Required to create a marketing context that is targeted to the UI locale of a shopper.

StoreObject

This profile is required for all operations targeting a StoreProfile entity.

Property

Data type

Description

GeneralInfo.store_id

String, PRIMARYJOIN key

Required as the unique Id of the store.

GeneralInfo.store_name

String, required, UNIQUE key

Required to support integration with Bing Maps. Holds the name of the store. If this field does not exist, any attempts to upload stores to Bing Maps and search for nearby stores will throw an exception.

GeneralInfo.preferred_address

String

List of applicable addresses for the Store. This reflects the store information in different languages.

GeneralInfo.address_list

Profile, references Profile Definitions.Address, multi-valued

List of applicable addresses for the store. This reflects the store information in different languages.

GeneralInfo.store_latitude

String

Holds the latitude of the store if available.

GeneralInfo..store_longitude

String

Holds the longitude of the store if available.

ProfileSystem.date_created

DateTime?

Required to support access by Customer and Orders Manager business tool.

ProfileSystem.date_last_changed

DateTime?

Required to support access by Customer and Orders Manager business tool.

ProfileSystem.user_id_changed_by

String

Required to support access by Customer and Orders Manager business tool.

Marketing System Objects

This section details the schema changes implemented to the Marketing System in Commerce Server 2009 R2.

VirtualEarthConfig

This profile is required for all operations targeting a VENearbyStore or VEClientToken entity.

Property

Data type

Description

GeneralInfo.config_id

Number, PRIMARYJOIN key

Required as Entity primary key.

GeneralInfo.account_id

String

Required as part of user's Bing Maps account credentials.

If this field does not exist, an exception will be thrown in any attempt to perform a nearby store query or getting Bing Maps token.

GeneralInfo.password

String, Asymmetric encryption

Required to support Bing Maps integration. This field holds the password value of user's Bing Maps account credentials.

If this field does not exist, an exception will be thrown in any attempt to perform a nearby store query or getting Bing Maps token.

GeneralInfo.data_source_name

String

Required to support Bing Maps integration. This field holds the data source name of user's Bing Maps account.

If this field does not exist, an exception will be thrown when any attempt is made to perform a nearby store query or to get a Bing Maps token.

GeneralInfo.ve_token_staging_url

String, required

Required to support Bing Maps integration.

Holds a URL to Staging version of map point API. If this field does not exist, an exception will be thrown when any attempt is made to get a Bing Maps token.

GeneralInfo.ve_token_production_url

String, required

Required to support Bing Maps integration. Holds a URL to production version of map point API.

If this field does not exist, an exception will be thrown when any attempt is made to perform a nearby store query.

GeneralInfo.mp_find_staging_url

String, required

Required to support Bing Maps integration.

Holds a URL to the Staging version of the MapPoint API. If this field does not exist, an exception will be thrown when any attempt is made to perform a nearby store search.

GeneralInfo.mp_find_production_url

String, required

Required to support Bing Maps integration. Holds a URL to the Production version of the MapPoint API.

If this field does not exist, an exception will be thrown when any attempt is made to perform a nearby store search.

ProfileSystem.KeyIndex

Number

Required to support access from Customer and Order manager tool.

ProfileSystem.user_id_changed_by

String

Required to support access from Customer and Order manager tool.

ProfileSystem.date_last_changed

DateTime?

Required to support access from Customer and Order manager tool.

ProfileSystem.date_created

DateTime?

Required to support access from Customer and Order manager tool.

Profiles System SQL

This section describes the SQL changes implemented for the schema changes to the Profiles System in Commerce Server 2009 R2.

Modified Tables

[dbo].[UserObject]

  • Added columns:

    • [u_live_id] [nvarchar] (50) NULL

    • [u_live_id_make_unique]  AS case when u_live_id is null then u_user_id else null end

    • [u_preferred_shipping_method]  [nvarchar] (50) NULL

    • [u_default_shopper_list]  [nvarchar] (50) NULL

  • Added index:

    • UNIQUE NONCLUSTERED INDEX [Idx_UserObject_liveid] ON [dbo].[UserObject]([u_live_id], [u_live_id_make_unique]) ON [PRIMARY]

[dbo].[CreditCards]

  • Added columns:

    • [i_expiration_month] [int] NOT NULL

    • [i_expiration_year] [int] NOT NULL

  • Removed columns:

    • [u_expiration_date] [nvarchar] (12) NOT NULL

Added Tables

[dbo].[StoreObject]

  • Columns:

    • [u_store_id] [nvarchar](50) NOT NULL

    • [u_store_name] [nvarchar](100) NOT NULL

    • [u_preferred_address] [nvarchar](50) NULL

    • [u_addresses] [nvarchar](500) NULL

    • [u_latitude] [nvarchar](20) NULL

    • [u_longitude] [nvarchar](20) NULL

    • [u_user_id_changed_by] [nvarchar](50) NULL

    • [dt_date_last_changed] [datetime] NULL DEFAULT GETDATE()

    • [dt_date_created] [datetime] NULL DEFAULT GETDATE()

  • Indexes:

    • CONSTRAINT [PK_StoreObject] PRIMARY KEY  CLUSTERED ( [u_store_id] ) ON [PRIMARY]

    • UNIQUE NONCLUSTERED INDEX [Idx_StoreObject_name] ON [dbo].[StoreObject]([u_store_name]) ON [PRIMARY]

    • NONCLUSTERED INDEX [Idx_StoreObject_datecreated] ON [dbo].[StoreObject]([dt_date_created]) ON [PRIMARY]

    • NONCLUSTERED INDEX [Idx_StoreObject_datelastchanged] ON [dbo].[StoreObject]([dt_date_last_changed]) ON [PRIMARY]

[dbo].[VirtualEarthConfig]

  • Columns:

    • [i_config_id] [int] NOT NULL

    • [u_account_id] [nvarchar](50) NULL

    • [u_password] [nvarchar](512) NULL

    • [u_data_source_name] [nvarchar](255) NULL

    • [u_ve_token_staging_url] [nvarchar](255) NOT NULL

    • [u_ve_token_production_url] [nvarchar](255) NOT NULL

    • [u_mp_find_staging_url] [nvarchar](255) NOT NULL

    • [u_mp_find_production_url] [nvarchar](255) NOT NULL

    • [i_keyindex] [int] NULL

    • [u_user_id_changed_by] [nvarchar](50) NULL

    • [dt_date_last_changed] [datetime] NULL

    • [dt_date_created] [datetime] NULL

  • Indexes:

    • CONSTRAINT [PK_VirtualEarthConfig] PRIMARY KEY  CLUSTERED ( [i_config_id] ) ON [PRIMARY]

    • NONCLUSTERED INDEX [Idx_VirtualEarthConfig_datecreated] ON [dbo].[VirtualEarthConfig]([dt_date_created]) ON [PRIMARY]

    • NONCLUSTERED INDEX [Idx_VirtualEarthConfig_datelastchanged] ON [dbo].[VirtualEarthConfig]([dt_date_last_changed]) ON [PRIMARY]