Hello Ronnie,
I agree with "Roll your own".
I just added new PK fields with the only purpose for journaling
and nothing else so far. With following code You can convert
a database.
I do it in 2 steps. Very first time I create a new datafile and let the
structure
add the fields. This should be done with v14 or 15. After that you can open
any
datafile (v11, 12, 13) with that structure file and conversion will be done
automatically.
I implemented 2 methods. First method I call from On Startup and On Startup
Server.
SYS_PrimaryKey_v14
// ----------------------------------------------------
// User name (OS): Magnus Torell
// Date and time: 14-12-29, 01:01:43
// ----------------------------------------------------
// Method: SYS_PrimaryKey_v14
// Description
// Method is tested and has to be run as follows:
// Open the structure in v14 with an EMPTY datafile
// This will convert the structure and add primary keys to all tables
// Done
// Now any other Valentis/MultiControl database in v11, v12, v13 can be
just opened and all PKs will be filled and indexed
// None of the automatic conversion dialogs should show up since the
structure is already converted and PKs added
// Upon opening of databases first you confirm that the will be updated to
v14.
// Then the data will automatically be filled with PK values (UUID) and
// Finally the new fields will be indexed
// Either before or after the indexing the PKs will be validated by v14
mechanism
// DON'T forget to enable journaling after conversion is done.
// This method could just lie around till a reasonable amount of time.
// Maybe could be adjusted to If (Application version == "14@")
// Parameters
// ----------------------------------------------------
// NB TRIGGERS. Each save will take longer time if triggers are on.
// In the triggers may be calls to interprocess vars that is not yet
initialized Be careful.
// For writer there is a ◊FileArray{}{} which is not yet initialized here
in beginning in On StartUp.
// Noted by: Magnus Torell (15-10-31)
// Followed up on this TRIGGER Part and did not understand why triggers
should be off.
// OK Maybe faster, but no harm seemed to be happening
C_TEXT($statement_t;$tableName_t)
C_LONGINT($curTable_l;$numTables_l)
C_BOOLEAN($b_RunSQL)
If (Application version>="14@")
$b_RunSQL:=True
C_LONGINT($fields_max)
C_LONGINT($field_number)
$curTable_l:=1 // Table 1 = Object
$fields_max:=Get last field number(Table($curTable_l))
$pk_fieldname_t:="pk_"+String($curTable_l;"0000") //New syntax for
primary
keys: "pk_xxxx" where xxxx stands for table number in format "0000"
For ($field_number;1;$fields_max)
If (Is field number valid($curTable_l;$field_number))
$fieldname:=Field name($curTable_l;$field_number)
If ($fieldname=$pk_fieldname_t) //Check if there
already exists a field
with syntax "pk_xxxx"
$b_RunSQL:=False
End if
End if
End for
If ($b_RunSQL) // ie. we have not found field pk_0001 in first table
(Object table)
$numTables_l:=Get last table number
For ($curTable_l;1;$numTables_l)
SYS_PrimaryKey_Create ($curTable_l)
End for
End if
End if
and second method
SYS_PrimaryKey_Create
// ----------------------------------------------------
// User name (OS): Magnus Torell
// Date and time: 15-07-02, 19:56:28
// ----------------------------------------------------
// Method: SYS_PrimaryKey_Create
// Description
//
//
// Parameters
// ----------------------------------------------------
C_LONGINT($curTable_l;$1)
C_TEXT($statement_t;$tableName_t)
C_LONGINT($curTable_l;$numTables_l)
C_BOOLEAN($b_RunSQL)
$curTable_l:=$1
$statement_t:=""
If (Is table number valid($curTable_l))
$b_RunSQL:=True
C_LONGINT($fields_max)
C_LONGINT($field_number)
$fields_max:=Get last field number(Table($curTable_l))
$pk_fieldname_t:="pk_"+String($curTable_l;"0000") //New syntax for
primary
keys: "pk_xxxx" where xxxx stands for table number in format "0000"
For ($field_number;1;$fields_max)
If (Is field number valid($curTable_l;$field_number))
$fieldname:=Field name($curTable_l;$field_number)
If ($fieldname=$pk_fieldname_t) //Check if there
already exists a field
with syntax "pk_xxxx"
$b_RunSQL:=False
End if
End if
End for
If ($b_RunSQL) //If there is no field already created
$tableName_t:=Table name($curTable_l)
$statement_t:=$statement_t+"ALTER TABLE ["+$tableName_t+"] "
$statement_t:=$statement_t+"ADD "+$pk_fieldname_t+" UUID
AUTO_GENERATE
PRIMARY KEY;"
Begin SQL
EXECUTE IMMEDIATE:$statement_t;
End SQL
// INDEX MUST BE SET IN ORDER FOR TRIGGERS TO WORK WITH
SAVING OF
RECORDS FROM TABLES THAT HAVE BEEN ASSIGNED PKs
C_POINTER($MyUUIDptr)
If (Is field number valid($curTable_l;$fields_max+1))
$MyUUIDptr:=Field($curTable_l;$fields_max+1)
SET INDEX($MyUUIDptr->;Default index type) // assuming
default is
best(?)
End if
End if
End if
Hope this helps
Best Regards
Magnus Torell
--
Sent from: http://4d.1045681.n5.nabble.com/4D-Tech-f1376241.html
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**********************************************************************
