Partager via

Commerce Server 2009 Schema Changes

  • Article

This topic details the schema differences between the Commerce Server 2009 out-of-the-box Default Site and the Commerce Server 2007 Starter Site.

Profiles System Objects

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

UserObject

Property

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 if an attempt is made to retrieve a UserProfile entity by LiveId, or if 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

Type

Description

GeneralInfo.expiration_month

Number, Required

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

If these properties do not exist, an exception will be thrown if 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 CS2007 CreditCard profile schema.

GeneralInfo.expiration_year

Number, Required

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

If these properties do not exist, an exception will be thrown if 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 CS2007 CreditCard profile schema.

TargetingContext

Property

Type

Description

Channel

String

Required to create marketing content that is targeted to a specific Commerce Server 2009 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 new profile is required for all operations targeting a StoreProfile entity.

Property

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 Virtual Earth. Holds the name of the store. If this field does not exist, any attempts to upload stores to Virtual Earth 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 detail the schema changes implemented to the Marketing System in Commerce Server 2009.

VirtualEarthConfig

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

Property

Type

Description

GeneralInfo.config_id

Number, PRIMARYJOIN key

Required as Entity primary key.

GeneralInfo.account_id

String

Required as part of user's Virtual Earth account credentials.

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

GeneralInfo.password

String, Asymmetric encryption

Required to support Virtual Earth integration. This fieled holds the password value of user's Virtual Earth account credentials.

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

GeneralInfo.data_source_name

String

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

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

GeneralInfo.ve_token_staging_url

String, required

Required to support Virtual Earth integration.

Holds a URL to Staging version of map point API. If this field does not exist, an exception will be thrown in any attempt for getting virtual earth token.

GeneralInfo.ve_token_production_url

String, required

Required to support Virtual Earth integration. Holds a URL to production version of map point API.

If this field does not exist, an exception will be thrown in any attempt to perform a near by store search.

GeneralInfo.mp_find_staging_url

String, required

Required to support Virtual Earth integration.

Holds a URL to Staging version of map point API. If this field does not exist, an exception will be thrown in any attempt to perform a near by store search.

GeneralInfo.mp_find_production_url

String, required

Required to support Virtual Earth integration. Holds a URL to production version of map point API.

If this field does not exist, an exception will be thrown in any attempt to perform a near by 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.

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]