Monday, April 25, 2005

Pocket Access - III

Pocket Access files are essentially mounted CEDB volumes with a very specific structure. They all contain four system tables:

  • MSysFields - Contains all the columns of all tables in the database, including the columns of the system tables.

  • MSysTables - Contains all the tables in the database, including the system tables themselves.

  • MSysIndexes - User indexes are all declared here.

  • MSysProcs - Stored procedures?


An empty Pocket Access database has all four tables created and the first two have some information: the system metadata. System tables are all created as type 3 (see below) and none contains a sort order with the exception of MSysFileds which has a sort order (type 0 - undocumented) on column zero.

To create a Pocket Access database, we must create all system tables an fill them with their default values. This process must be done in a very specific order: 1 - Mount a new CEDB volume; 2 - Create the databases and store their OIDs (the preferred order of creation is MSysFields, MSysTables, MSysIndexes, MSysProcs); 3 - Fill the MSysFields database; 4 - Fill the MSysTables database.

Each record on the MSysFields database has 5 columns (names taken from MSysFields itself):

  • TableID - (int) Table OID

  • FieldName - (string) Field name

  • FieldID - (int) Field property id

  • Len - (short) Field length

  • ODBCType - (short) Data type



The MSysTables table contains only 3 columns:

  • TableName - (string) Table name

  • TableID - (int) Table OID

  • TableFlags - (int) Table flags (system tables = 3, user = 0)



MSysIndexes has the following columns:

  • TableID - (int) Table OID

  • IndexName - (string) Index name

  • FieldID - (int) Field property id

  • IndexFlags - (int) CEDB sort order flags

2 comments:

Jan said...

It's interesting...

Is it possible to create Pocket Access file in PC? If we know the format of CDB?

João Paulo Figueira said...

You need the CEDB API in order to create a CDB file. The CEDB database format is not public, so there are only two ways you can create a CDB file on a PC: using an emulator or through RAPI (a Windows CE device is needed, though).