Coming from DBISAM, I have many tables where primary keys have non-required fields.It is not allowed in ElevateDB. To replace the logic to consider these fields as required will produce a lot of work to review my code.

After thinking in a solution, I wrote the code below. The idea behind the conversion from DBISAM is "if there is a non-required field in primary key the routine does not create the primary key but instead create an unique key called "PKY".

I have an inherited component based from TEDBTable and the code below is just to clarify things. I will just put the code inside my inherited component and all the application will be affected.

Someone have another idea ? Or someone see any troubles using the code below ?

Table.Open;
if Table.IndexName = '' then begin
  IsOk := False;
  Table.IndexDefs.Update;
  for nI := 0 to Table.IndexDefs.Count-1 do begin
     if Table.IndexDefs[nI].Name = 'PrimaryKey' then begin
        IsOk := True;
        break;
     end;
  end;
  if not IsOk then begin
     Table.IndexName := 'Pky';
     Table.First;
  end;   
end;

Thanks in advance

Eduardo

Jose


First thoughts::

1. your code isn't creating an index its selecting one (probably a language difference)
2. your code doesn't guarantee that an index called "PKY" exists - what happens if one doesn't
3. it will only work on table open, not on index selection within code
4. what do you want to happen when all if the rows that had null columns now have data
5. you now, possibly, have a table without a primary index
6. how does your approach translate to sql


Roy Lambert

Roy

Let me try to answer your questions:

<<
1. your code isn't creating an index its selecting one (probably a language difference)
>>
The idea is migrating an application from DBISAM

<<
2. your code doesn't guarantee that an index called "PKY" exists - what happens if one doesn't
>>
Every table without primary key certainly have an index called PKY (I will create one in my migration tool)

<<
3. it will only work on table open, not on index selection within code
>>
Yes, I know that, but like I said before, I have an inherited component from EDBTable and I can handle that inside the component.

<<
4. what do you want to happen when all if the rows that had null columns now have data
>>
I do not understand your point

<<
5. you now, possibly, have a table without a primary index
>>
Yes, tables with primary key (in DBISAM) that have non-required fields will produce an unique key that will act like a primary key

<<
6. how does your approach translate to sql
>>
For me does not matter because the indexes I need will be there, primary or unique or regular ones.

Eduardo

José,

I have migrated a couple of applications from DBISAM to EDB and I am right now doing it with another one.
My strategy is to always create a new version of the original (DBISAM) application just to adapt the database structure to the conversion and only after I go ahead with the conversion.

EDB rules are very close to the SQL standards and that's why NULLs are not allowed in Primary Keys - because it's not allowed by the standards for a lot of good reasons.

For string columns (VARCHAR), there is an option in the migrator to automatically convert all the NULLS to empty strings.
In EDB (and standard SQL in general) a NULL string is not the same as an empty string, so the impact must me small in your applications because in DBISAM they are already empty strings.

For other column types you will have to check out the code and adapt it to deal with a value of 'zero', 'false' or whatever is appropriate instead of NULL.

As I said I do all the needed database structure and code changes in the last version of the old DBISAM application before conversion.


--
Fernando Dias
[Team Elevate]

Eduardo,

<< Coming from DBISAM, I have many tables where primary keys have non-required fields.It is not allowed in ElevateDB. To replace the logic to consider these fields as required will produce a lot of work to review my code.

After thinking in a solution, I wrote the code below. The idea behind the conversion from DBISAM is "if there is a non-required field in primary key the routine does not create the primary key but instead create an unique key called "PKY". >>

Yes, that is essentially what the DBISAM migrator does if you enable the ConvertPrimaryToUnique migration parameter, although it's a little less nuanced and doesn't look at the required columns.

You might consider just converting all primary keys to unique keys.  That will give you the same behavior as DBISAM without requiring any conditional logic that may make things harder to decipher later on.

Tim Young
Elevate Software
www.elevatesoft.com

Thank you very much for all suggestions and observations.

Eduardo