dtl


Key Modes in DTL

When updating or deleting records, DTL needs a way of mapping the object pointed to by an iterator to a particular row in the database. DTL supports three "key modes" to do this:

1. USE_PK_FIELDS_ONLY: The table has a unique key, and the user supplies exactly which fields make up this unique key.

2. USE_ALL_FIELDS: Use all fields in the view when setting up the "WHERE" criteria for update and delete queries. Some tables don't have a unique key in which case using all fields in the view may be the best available way to determine which row to manipulate. In such instances, multiple rows may be identical and thus more than one row may be updated or removed if more than one row is matched by the query.

3. USE_AUTO_KEY: A unique key for the table is unknown. Query the database to see if it can give us a set of fields that will form a unique key for the table. This unique identifier may be a unique key for the table which the DBMS has information about, or it may be an automatically generated identifier for each row. An example of automatically generated unique key is the ROWID field in Oracle.

IndexedDBView, DynamicDBView, and DBView::select_update_iterator support these key modes.

The valid key modes for a DynamicDBView are USE_ALL_FIELDS and USE_AUTO_KEY. DBView has a key mode of USE_ALL_FIELDS only. The key mode only affects the behavior of select_update_iterator on an update:

1. If the user calls select_update_iterator::SetKey() with a comma-separated list of fields then this sets the key that will be used by the iterator. If the list of fields represents a unique key, then only one row will be affected by any update. If this list of fields is not a uniqe key, then more than one row may be affected by an update.

If the user does not call select_update_iterator::SetKey():

2. Try to find a unique identifier for rows in the table(only DynamicDBViews with a key mode of USE_AUTO_KEY support this). If the database can give us a unique identifier for the view then the row that will be updated is unique. If we cannot find a unique identifier then rule #3 will apply.

3. Use all fields in the view to identify the row. Note that this does not guarantee that the row which will be updated is unique.

Examples

The two examples below show the difference between the use of different key modes using a select_update_iterator.

The first example uses autokeys and thus only the five records we iterate over are actually updated:

// example illustrating use of auto key for select_update_iterator

vector<variant_row> DynamicReadAndUpdateDataAutoKey()
{
 vector<variant_row> results;

 DynamicDBView<> view(
 	 DynamicDBView<>::Args().tables("DB_EXAMPLE").fields("*").handler(AlwaysThrowsHandler())
 );

 DynamicDBView<>::select_update_iterator read_it = view.begin();

 variant_row row = *read_it;
 
 // note that the call to SetKey() is commented out
 // thus, *exactly* the first 5 records in the view will be updated

 // read_it.SetKey("STRING_VALUE");
 

 for ( int i = 0; i<5 && read_it != view.end(); ++i)
 {
  row = *read_it;
  row["STRING_VALUE"] = string("Fizzle");
  row["DOUBLE_VALUE"] = 0.5;
  *read_it = row;
  ++read_it;
 }
}


/* output looks like:
!!!!!!!!!!!!!!!!!!!!! Begin Example DynamicReadAndUpdateDataAutoKey() !!!!!!!!!!!!!!!!!!!!
INT_VALUE STRING_VALUE DOUBLE_VALUE EXAMPLE_LONG EXAMPLE_DATE ROWID
1 Example 3 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Foozle 7.7 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Bedazzled 12 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Example 11.11 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Example 21.11 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
10 Settled 100.11 -1 NOV-11-1999 AAAu0WAANAAAfeGAAF
11 Example 0 0 OCT-31-2001 AAAu0WAANAAAfeGAAG
12 Mirror Image 31.13 22 DEC-04-1998 AAAu0WAANAAAfeGAAH
6 Find Me 22.22 22 JAN-01-2000 AAAu0WAANAAAfeGAAI
10 Find Me 34.44 41 JAN-01-2000 AAAu0WAANAAAfeGAAJ
6 Jordan 1000.1 1 NOV-11-1999 AAAu0WAANAAAfeGAAK
8 Corwin 99.99 100 JAN-01-2000 AAAu0WAANAAAfeGAAL
17 To find 213 34 NOV-11-1999 AAAu0WAANAAAfeGAAM
18 Duped -12.22 -99 FEB-13-1988 AAAu0WAANAAAfeGAAO
19 Mike -43334 2122 FEB-13-1988 AAAu0WAANAAAfeGAAZ
20 To find 2020.2 2 NOV-11-1999 AAAu0WAANAAAfeGAAd
21 Do not find 22 22 FEB-13-1988 AAAu0WAANAAAfeGAAg
22 To find 443.33 32 DEC-04-1998 AAAu0WAANAAAfeGAAl
23 Who? 0 0 FEB-13-1988 AAAu0WAANAAAfeGAAm
28 To find 11.11 38 DEC-04-1998 AAAu0WAANAAAfeGAAn
29 Prayer 912.22 -3 NOV-11-1999 AAAu0WAANAAAfeGAAo
30 The Reliable, Red Caminus Bouncy Ball 1000.11 10 NOV-11-1999 AAAu0WAANAAAfeGAAp
31 To find 0 110 FEB-13-1988 AAAu0WAANAAAfeGAAq
32 Example 1 109 FEB-13-1988 AAAu0WAANAAAfeGAAs
3 Join Example 33 33 JAN-01-2000 AAAu0WAANAAAfeGAAt
3 Join Example 333 333 NOV-11-1999 AAAu0WAANAAAfeGAAu
1 Find Me 44.44 44 JAN-01-2000 AAAu0WAANAAAfeGAAv
15 Find Me 11 25 DEC-04-1998 AAAu0WAANAAAfeGAAw
3 Find Me 88.88 88 JAN-01-2000 AAAu0WAANAAAfeGAAx
23 To find 54.21 9 FEB-13-1988 AAAu0WAANAAAfeGAAy
28 To find 1.11 1 FEB-13-1988 AAAu0WAANAAAfeGAAz
77 Blah 3.21 2 JUL-07-2000 AAAu0WAANAAAfeGAA0
After update:
1 Fizzle 0.5 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Fizzle 0.5 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Fizzle 0.5 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Fizzle 0.5 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Fizzle 0.5 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
10 Settled 100.11 -1 NOV-11-1999 AAAu0WAANAAAfeGAAF
11 Example 0 0 OCT-31-2001 AAAu0WAANAAAfeGAAG
12 Mirror Image 31.13 22 DEC-04-1998 AAAu0WAANAAAfeGAAH
6 Find Me 22.22 22 JAN-01-2000 AAAu0WAANAAAfeGAAI
10 Find Me 34.44 41 JAN-01-2000 AAAu0WAANAAAfeGAAJ
6 Jordan 1000.1 1 NOV-11-1999 AAAu0WAANAAAfeGAAK
8 Corwin 99.99 100 JAN-01-2000 AAAu0WAANAAAfeGAAL
17 To find 213 34 NOV-11-1999 AAAu0WAANAAAfeGAAM
18 Duped -12.22 -99 FEB-13-1988 AAAu0WAANAAAfeGAAO
19 Mike -43334 2122 FEB-13-1988 AAAu0WAANAAAfeGAAZ
20 To find 2020.2 2 NOV-11-1999 AAAu0WAANAAAfeGAAd
21 Do not find 22 22 FEB-13-1988 AAAu0WAANAAAfeGAAg
22 To find 443.33 32 DEC-04-1998 AAAu0WAANAAAfeGAAl
23 Who? 0 0 FEB-13-1988 AAAu0WAANAAAfeGAAm
28 To find 11.11 38 DEC-04-1998 AAAu0WAANAAAfeGAAn
29 Prayer 912.22 -3 NOV-11-1999 AAAu0WAANAAAfeGAAo
30 The Reliable, Red Caminus Bouncy Ball 1000.11 10 NOV-11-1999 AAAu0WAANAAAfeGAAp
31 To find 0 110 FEB-13-1988 AAAu0WAANAAAfeGAAq
32 Example 1 109 FEB-13-1988 AAAu0WAANAAAfeGAAs
3 Join Example 33 33 JAN-01-2000 AAAu0WAANAAAfeGAAt
3 Join Example 333 333 NOV-11-1999 AAAu0WAANAAAfeGAAu
1 Find Me 44.44 44 JAN-01-2000 AAAu0WAANAAAfeGAAv
15 Find Me 11 25 DEC-04-1998 AAAu0WAANAAAfeGAAw
3 Find Me 88.88 88 JAN-01-2000 AAAu0WAANAAAfeGAAx
23 To find 54.21 9 FEB-13-1988 AAAu0WAANAAAfeGAAy
28 To find 1.11 1 FEB-13-1988 AAAu0WAANAAAfeGAAz
77 Blah 3.21 2 JUL-07-2000 AAAu0WAANAAAfeGAA0
deleted / changed items:
1 Example 3 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Foozle 7.7 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Bedazzled 12 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Example 11.11 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Example 21.11 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
inserted / changed items:
1 Fizzle 0.5 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Fizzle 0.5 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Fizzle 0.5 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Fizzle 0.5 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Fizzle 0.5 21 JAN-01-2000 AAAu0WAANAAAfeGAAE

********************************

*/

The second example specifies a user-defined key and records that have STRING_VALUE equal to the value of the field in the row we are updating also get modified:

// example illustrating use of user-specified key for select_update_iterator

vector<variant_row> DynamicReadAndUpdateDataPK()
{
 vector<variant_row> results;

 DynamicDBView<> view(
 	 DynamicDBView<>::Args().tables("DB_EXAMPLE").fields("*").handler(AlwaysThrowsHandler())
 );

 DynamicDBView<>::select_update_iterator read_it = view.begin();

 variant_row row = *read_it;
 
 
 // STRING_VALUE is the field which will determine which fields get updated
 // for each of the 5 updates, some of the other records in the table have
 // STRING_VALUE equal to the value of the row being updated ... so those records will also get modified
 // giving us more than just 5 records updated
 
 read_it.SetKey("STRING_VALUE");
 
 for ( int i = 0; i<5 && read_it != view.end(); ++i)
 {
  row = *read_it;
  row["STRING_VALUE"] = string("Fizzle");
  row["DOUBLE_VALUE"] = 0.5;
  *read_it = row;
  ++read_it;
 }
}


/* output looks like:
!!!!!!!!!!!!!!!!!!!!! Begin Example DynamicReadAndUpdateDataPK() !!!!!!!!!!!!!!!!!!!!
INT_VALUE STRING_VALUE DOUBLE_VALUE EXAMPLE_LONG EXAMPLE_DATE ROWID
1 Example 3 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Foozle 7.7 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Bedazzled 12 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Example 11.11 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Example 21.11 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
10 Settled 100.11 -1 NOV-11-1999 AAAu0WAANAAAfeGAAF
11 Example 0 0 OCT-31-2001 AAAu0WAANAAAfeGAAG
12 Mirror Image 31.13 22 DEC-04-1998 AAAu0WAANAAAfeGAAH
6 Find Me 22.22 22 JAN-01-2000 AAAu0WAANAAAfeGAAI
10 Find Me 34.44 41 JAN-01-2000 AAAu0WAANAAAfeGAAJ
6 Jordan 1000.1 1 NOV-11-1999 AAAu0WAANAAAfeGAAK
8 Corwin 99.99 100 JAN-01-2000 AAAu0WAANAAAfeGAAL
17 To find 213 34 NOV-11-1999 AAAu0WAANAAAfeGAAM
18 Duped -12.22 -99 FEB-13-1988 AAAu0WAANAAAfeGAAN
19 Mike -43334 2122 FEB-13-1988 AAAu0WAANAAAfeGAAP
20 To find 2020.2 2 NOV-11-1999 AAAu0WAANAAAfeGAAQ
21 Do not find 22 22 FEB-13-1988 AAAu0WAANAAAfeGAAR
22 To find 443.33 32 DEC-04-1998 AAAu0WAANAAAfeGAAS
23 Who? 0 0 FEB-13-1988 AAAu0WAANAAAfeGAAT
28 To find 11.11 38 DEC-04-1998 AAAu0WAANAAAfeGAAU
29 Prayer 912.22 -3 NOV-11-1999 AAAu0WAANAAAfeGAAV
30 The Reliable, Red Caminus Bouncy Ball 1000.11 10 NOV-11-1999 AAAu0WAANAAAfeGAAW
31 To find 0 110 FEB-13-1988 AAAu0WAANAAAfeGAAX
32 Example 1 109 FEB-13-1988 AAAu0WAANAAAfeGAAY
3 Join Example 33 33 JAN-01-2000 AAAu0WAANAAAfeGAAZ
3 Join Example 333 333 NOV-11-1999 AAAu0WAANAAAfeGAAa
1 Find Me 44.44 44 JAN-01-2000 AAAu0WAANAAAfeGAAd
15 Find Me 11 25 DEC-04-1998 AAAu0WAANAAAfeGAAg
3 Find Me 88.88 88 JAN-01-2000 AAAu0WAANAAAfeGAAl
23 To find 54.21 9 FEB-13-1988 AAAu0WAANAAAfeGAAm
28 To find 1.11 1 FEB-13-1988 AAAu0WAANAAAfeGAAn
77 Blah 3.21 2 JUL-07-2000 AAAu0WAANAAAfeGAAo
After update:
1 Fizzle 0.5 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Fizzle 0.5 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Fizzle 0.5 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Fizzle 0.5 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Fizzle 0.5 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
10 Settled 100.11 -1 NOV-11-1999 AAAu0WAANAAAfeGAAF
11 Fizzle 0.5 0 OCT-31-2001 AAAu0WAANAAAfeGAAG
12 Mirror Image 31.13 22 DEC-04-1998 AAAu0WAANAAAfeGAAH
6 Find Me 22.22 22 JAN-01-2000 AAAu0WAANAAAfeGAAI
10 Find Me 34.44 41 JAN-01-2000 AAAu0WAANAAAfeGAAJ
6 Jordan 1000.1 1 NOV-11-1999 AAAu0WAANAAAfeGAAK
8 Corwin 99.99 100 JAN-01-2000 AAAu0WAANAAAfeGAAL
17 To find 213 34 NOV-11-1999 AAAu0WAANAAAfeGAAM
18 Duped -12.22 -99 FEB-13-1988 AAAu0WAANAAAfeGAAN
19 Mike -43334 2122 FEB-13-1988 AAAu0WAANAAAfeGAAP
20 To find 2020.2 2 NOV-11-1999 AAAu0WAANAAAfeGAAQ
21 Do not find 22 22 FEB-13-1988 AAAu0WAANAAAfeGAAR
22 To find 443.33 32 DEC-04-1998 AAAu0WAANAAAfeGAAS
23 Who? 0 0 FEB-13-1988 AAAu0WAANAAAfeGAAT
28 To find 11.11 38 DEC-04-1998 AAAu0WAANAAAfeGAAU
29 Prayer 912.22 -3 NOV-11-1999 AAAu0WAANAAAfeGAAV
30 The Reliable, Red Caminus Bouncy Ball 1000.11 10 NOV-11-1999 AAAu0WAANAAAfeGAAW
31 To find 0 110 FEB-13-1988 AAAu0WAANAAAfeGAAX
32 Fizzle 0.5 109 FEB-13-1988 AAAu0WAANAAAfeGAAY
3 Join Example 33 33 JAN-01-2000 AAAu0WAANAAAfeGAAZ
3 Join Example 333 333 NOV-11-1999 AAAu0WAANAAAfeGAAa
1 Find Me 44.44 44 JAN-01-2000 AAAu0WAANAAAfeGAAd
15 Find Me 11 25 DEC-04-1998 AAAu0WAANAAAfeGAAg
3 Find Me 88.88 88 JAN-01-2000 AAAu0WAANAAAfeGAAl
23 To find 54.21 9 FEB-13-1988 AAAu0WAANAAAfeGAAm
28 To find 1.11 1 FEB-13-1988 AAAu0WAANAAAfeGAAn
77 Blah 3.21 2 JUL-07-2000 AAAu0WAANAAAfeGAAo
deleted / changed items:
1 Example 3 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Foozle 7.7 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Bedazzled 12 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Example 11.11 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Example 21.11 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
11 Example 0 0 OCT-31-2001 AAAu0WAANAAAfeGAAG
32 Example 1 109 FEB-13-1988 AAAu0WAANAAAfeGAAY
inserted / changed items:
1 Fizzle 0.5 56 JAN-01-2000 AAAu0WAANAAAfeGAAA
2 Fizzle 0.5 12 DEC-04-1998 AAAu0WAANAAAfeGAAB
3 Fizzle 0.5 99 JAN-01-2000 AAAu0WAANAAAfeGAAC
4 Fizzle 0.5 44 FEB-28-1999 AAAu0WAANAAAfeGAAD
7 Fizzle 0.5 21 JAN-01-2000 AAAu0WAANAAAfeGAAE
11 Fizzle 0.5 0 OCT-31-2001 AAAu0WAANAAAfeGAAG
32 Fizzle 0.5 109 FEB-13-1988 AAAu0WAANAAAfeGAAY

********************************


*/

[DTL Home]

Copyright © 2002, Michael Gradman and Corwin Joy.

Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose is hereby granted without fee, provided that the above copyright notice appears in all copies and that both that copyright notice and this permission notice appear in supporting documentation. Corwin Joy and Michael Gradman make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.

This site written using the ORB. [The ORB]

1