Working with union types

A union is a container capable of holding an object of one of a predefined set of types. One possible use of this functionality is for reading and storing input data of a-priori unknown type.

When initializing unions from text format data files only scalar values of basic types are considered. More precisely, integers, reals, and Booleans are assigned to the union; textual values are used to initialize the entity as if it was of the first compatible type of the union (for the union any this is a string). An I/O error will be raised if this type does not support initialization.

Similarly to input from text format files, when reading data from spreadsheets or databases into union entities only scalar values of basic types are considered. Moreover, the resulting type will depend on the type employed within the data source (e.g. many databases use a numeric type for storing Boolean values and some do not distinguish between integer and real values, all these types will therefore result in the Mosel type real when populating entities of type any; the mmsheet.csv driver and diskdata will read the Boolean constants true/false as textual types, whereas they are read with type boolean by the default text driver and the other spreadsheet drivers). Date and time types will be stored as string (exception: the mmsheet.excel driver returns a real for time data unless the cell is formatted as text in the spreadsheet) and need to be transformed applying the suitable format settings (see discussion in Section Handling dates and time)

model "Union handling (ODBC)"
 uses 'mmodbc', 'mmsystem'

 declarations
   L,L2: list of any
   L3: list of text or real
   LS: list of text
 end-declarations

 setparam("SQLverbose",true)

 ! Reading data of different types from a database
 initializations from "mmodbc.odbc:debug;uniondata.sqlite"
   L as "UnionTab"
   L2 as "UnionLst"
   L3 as "UnionTab"
 end-initializations

 write("L orig: ")
 forall(i in L) write (i,": ", i.typeid, "; ")
 writeln
 ! Date and time types are read in textual form
 L(5).date:=date(text(L(5)))
 L(6).time:=time(text(L(6)))
 write("L new:  ")
 forall(i in L) write (i,": ", i.typeid, "; ")
 writeln

 ! Reading into a list defined with a restricted set of types
 write("L3:     ")
 forall(i in L3) write (i,": ", i.typeid, "; ")
 writeln

 ! Textual database types are always read as string
 write("L2:     ")
 forall(i in L2) write (i,": ", i.typeid, "; ")
 writeln

 LS:=sum(i in L) [text(i)]

 initializations to "mmodbc.odbc:debug;uniondata.sqlite"
  ! Writing data of type 'any' to a database table with various different types
   L as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
  ! Writing data of type 'any' into textual fields of a database
   L as "Union2Out"
  ! Writing data of a union type to a database
   L3 as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
  ! Writing text-format data to a database table with various different types
   LS as "UnionOut(IVal,RVal,BVal,SVal,DVal,TVal)"
 end-initializations

 writeln("Output to DB terminated.")

end-model

Assuming that the SQLite database tables have been created with these definitions:

SQLexecute("create table UnionTab (IVal integer, RVal real, BVal boolean, "+
  "SVal varchar(20), DVal date, TVal timestamp(3))")
SQLexecute("insert into UnionTab (IVal,RVal,SVal,BVal,TVal,DVal) values(?1,?2,?3,?4,?5,?6)",
  [ 5, 1.75, "some text", true, '11:25:30', '2021-03-20' ])
SQLexecute("create table UnionLst (UValues varchar(20))")

Then the program above displays the following output (notice the type change for the date and time in the second line):

L orig: 5: 1; 1.75: 2; 1: 1; some text: 3; 2021-03-20: 3; 11:25:30: 3;
L new:  5: 1; 1.75: 2; 1: 1; some text: 3; 2021-03-20: 13; 11:25:30: 14;
L3:     5: 2; 1.75: 2; 1: 2; some text: 11; 2021-03-20: 11; 11:25:30: 11;
L2:     5: 3; 1.75: 3; true: 3; some text: 3; 2021-03-20: 3; 11:25:30: 3;

With data input from text files in default Mosel format or using the mmsheet.xsl/xslx drivers the first line would display as

L orig: 5: 1; 1.75: 2; true: 4; some text: 3; 2021-03-20: 3; 11:25:30: 3;

and the definition of 'L3' needs to comprise the type boolean in order to be able to read the same data:

  L3: list of text or real or boolean

Note that when exporting unions, any non-scalar value or types that do not support conversion to string will result in a NIL value ('?' in text format files) in the generated file.