Change type field

noobies · 2012-03-13 13:18:34

first field type number of the blank was a integer type, then I changed it to a string, but the record in this field the number 0000000001 for example written in the database 1

how to correct this situation?

array81 · 2012-03-13 13:31:01

I think it's not possible change a record type becuase SQLite doesn't allow edit a field. You need add a new record with the new type and a new name.

noobies · 2012-03-13 14:00:27

At the moment I am temporarily using the code:

function GetZeroPaddedString (s: string; l: integer): string;
begin
   Result: = Format ('%.' + IntToStr (l) + 'd', [StrToInt64 (s)]);
end;

  edtBlankNumber.Text: = GetZeroPaddedString (BlankNumber, 12);

it works fine and does not require modifications when updating the database of all clients using the program (each program is running locally)

What a way to change the field type, or the entire database for clients to be the easiest?
or may have to create a new database with the correct structure and import the data?

ab · 2012-03-13 14:02:14

SQLite3 in fact does allow multi-type in a column.
That is, the same column can be an integer for a row, but a string for another row....

The ORM part of the framework, in contrast, expects the column format to match the exact definition of the TSQLRecord field.

So if you change the column type, you'll have to update the database format.
Perhaps the easiest is to create a new record type, as array81 proposed, then convert all existing data to the new format.

In all ORMs, changing field layout is some kind of nightmare. Because all DBs are generally tied to a fixed column layout, and conversion from one format to another has to be done by hand.
So when you define your field layouts, you shall ensure you won't have to change it.

mORMot handle the adding of a field: you could add a new field, then override the corresponding class procedure TSQLRecord.InitializeTable of your record table, which will be called with the new field name specified as parameter.
Or you may check for the file version, as supplied in CreateMissingTables method call, but it is not easy to track it.

xalo · 2018-02-11 17:22:01

I'm agree @ab that is a nigthmare changing TSQLRecord field...

There is any way to check in SQLite3 if some field has multi-type in a column?
I have some weird behabiours in BLOB fields related to TRawUTF8DynArray for some records only, as you stated.

Any suggestion to intercept wich field I could have changed type in the past and solve the issues?

ab · 2018-02-11 17:45:34

You can define a variant published field since some years, and assign a TDocVariant to it.
Then you could store any kind of data in it: object or arrays.
Then it will be stored as JSON text in SQLite3, and native BSON object/array in MongoDB.

See https://synopse.info/files/html/Synopse … l#TITLE_44

xalo · 2018-02-12 15:22:28

Great and perfect suggestion!
This solves the weird results. I've changed TRawUTF8DynArray by variant field type and field name and now works like a charm.

I'm so grateful with you... and with the mORMot,of course! ;-)

Junior/RO · 2018-02-13 16:42:26

Excellent solution, @ab.

Despite being Carnival here in Brazil, today I learned a little more.

mORMot Open Source

#1 2012-03-13 13:18:34

Change type field

#2 2012-03-13 13:31:01

Re: Change type field

#3 2012-03-13 14:00:27

Re: Change type field

#4 2012-03-13 14:02:14

Re: Change type field

#5 2018-02-11 17:22:01

Re: Change type field

#6 2018-02-11 17:45:34

Re: Change type field

#7 2018-02-12 15:22:28

Re: Change type field

#8 2018-02-13 16:42:26

Re: Change type field

Board footer