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.

SourceForge Logo

This site written using the ORB. [The ORB]