EDB Schema Support (Windows CE 5.0)

Article
09/14/2012

EDB is built on a traditional database engine that supports defining the structure of a database. When you create an EDB database, you define its schema: the number of properties in the database and the data type for each property. You can also define sort orders for the database. By contrast, CEDB does not have the concept of schematization.

Because EDB supports multiple users concurrently accessing the same database, you must ensure that no user has access when schema changes are being made. Therefore, it is more efficient to define the schema of an EDB database before the database is used.

Adding and Removing Properties

After designing the schema for an EDB database, you add properties (columns) to the database. Each property is defined by the CEPROPSPEC structure, which is passed to the CeAddDatabaseProps and CeCreateDatabaseWithProps functions when adding the property.

Properties are removed from an EDB database by using the CeRemoveDatabaseProps function. When a property is added or removed, EDB locks the entire database during the process; therefore, you cannot add or remove a property if any client has opened the database on any thread.

Note An EDB database must contain at least one property; all properties except one can be removed.

Naming Properties

EDB requires that each property have a name that is unique for each database. You can specify a user-friendly name for each property, or you can let EDB automatically generate a property name. You name the properties as they are added to the database using the CEPROPSPEC structure with the CeAddDatabaseProps function, and you read the name information by using the CeGetDatabaseProps function.

Note When writing new properties to a database, the name of a new property may conflict with an existing property name. If a naming conflict occurs, EDB returns an ERROR_ALREADY_EXISTS error.

Property ID Mapping

For each property in an EDB database, EDB maps a CEDB property ID. If you do not specify a name when adding a property, EDB generates a name based on the CEDB property ID. The CEDB property ID consists of an application-defined ID in the hi-word and the CEDB data type value in the lo-word. This ensures that each property name is unique within each database.

Mapping the CEDB property ID to each EDB property means that you can have two properties in the same database that have the same application-defined ID but different data types.

Reading and Writing Property Values

You can read property values using the CeReadRecordPropsEx function. If you specify an invalid property ID when calling the CeReadRecordPropsEx function, EDB returns the CEDB_PROPNOTFOUND flag for that specific property.

Values are written using the CeWriteRecordProps function. If you specify an invalid property ID when calling the CeWriteRecordProps function, the function call fails unless the database is currently empty. If the database contains no data, the CeWriteRecordProps function adds the new property.

Note The value of an EDB property can be NULL. Even if a property is NULL, it still uses the storage required for the property. This differs from CEDB, which does not require any storage if there is no value for a property.

Using Sort Orders

Sort orders arrange the data as they are added to the database. You can specify sort orders by using the SORTORDERSPECEX structure when calling the CeCreateDatabaseWithProps or CeSetDatabaseInfo function. When you create a user-defined sort order, EDB automatically adds any property IDs specified in the sort order to the database. Therefore, the client application does not need to define the property IDs when adding a sort order. For more information on property IDs, see Property ID Mapping.

EDB and CEDB Sort Order Compatibility

The following list describes differences between how EDB and CEDB use sort orders:

Default sort order

EDB does not create a default sort order.

Unique constraints and NULL values

If you set the CEDB_SORT_UNIQUE flag in CEDB, the CEDB_SORT_NONNULL flag is implied, and so all records must include all of the sort properties, and none of the properties can be NULL. In EDB, however, one NULL value is allowed.

For example, if you have a sort order with two properties, and you have specified CEDB_SORT_UNIQUE, then the following table shows the behavior that exists for various values:

Value	EDB behavior	CEDB behavior
"A,B"	Valid (considered unique)	Valid (considered unique)
"A,NULL"	Valid (considered unique)	Not permitted
"NULL,B"	Valid (considered unique)	Not permitted
"NULL,NULL"	Valid (considered unique)	Not permitted

Multiple sort orders

EDB supports up to 16 sort orders, each of which can contain up to 16 properties. CEDB supports only 4 sort orders, each of which can contain up to 3 properties.
Properties in sort orders

EDB allows a property to be used in more than one sort order. This is not possible in CEDB.
Property types in sort orders

While the CEVT_BLOB and CEVT_LPWSTR data types can be included in a sort order, the CEVT_STREAM data type cannot.
White space truncation

If you have a sort order with a unique constraint on a CEVT_LPWSTR (string) data type, EDB truncates any trailing white space in the string value. For example, EDB considers "MyValue" and "MyValue " as the same value. In CEDB, these two strings are considered unique.
New EDB flags

EDB supports two new flags that can be used in the SQLCESORTORDERSPEC::wKeyFlags member:
- CEDB_SORT_NO_POSITIONAL
  
  If you do not need positional access on a sort order, you should specify this flag. By setting this flag, less information is stored in the sort order, saving space. If you use this flag, you must pass NULL for the pdwIndex parameter in the call to the CeSeekDatabaseEx function.
  
  Note If positional access is disabled on a sort order, then only four seek types are supported with the CeSeekDatabaseEx function: CEDB_SEEK_CEOID, CEDB_SEEK_BEGINNING, CEDB_SEEK_CURRENT, and CEDB_SEEK_END.
- CEDB_SORT_PRIMARYKEY
  
  If you need entity integrity for a given sort order, you can specify this flag. Only one CEDB_SORT PRIMARYKEY constraint can be created per database.
  
  Note A primary key is read-only; a user can't write a value to this property.

Share via