SQLA17 limitation domains not documented

[fvestjens]

Domains can be created in a SQL Anywhere database as a built-in data type. However there is a limitation that is not documented or maybe can be resolved.

We're using domains as a proper way to achieve the generation of jsons from the database.

Example: I create the following domains which are sub sets of tables:

create domain domain_Product row(Name varchar(64),Grade varchar(10),FlashPoint numeric(10,6));
create domain domain_OrdLine row(Products domain_Product,Quantity numeric(10,2),Price numeric(10,2));
create domain domain_Order   row(Number varchar(16),Customer varchar(80),Status integer,Lines domain_OrdLine);

I created 2 functions using a domain data type as a result set. One for fetching product properties

create or replace function GetProductRow(in in_ProductId integer)
returns domain_Product
begin
  declare row_Product domain_Product;
  //
  select row(Name,Grade,FlashPoint)
  into row_Product
  from Product
  where ProductId = in_ProductId;
  //
  return row_Product;
end

And one for fetching properties from OrderLines

create or replace function GetOrderLines(in in_OrderId integer)
returns array of domain_OrdLine
begin
  declare array_OrderLine array of domain_OrdLine;
  //
  select array_agg(row(GetProductRow(ProductId),Quantity,Price))
  into variable array_OrderLine
  from OrderLines
  where OrderId = in_OrderId;
  //
  return array_OrderLine;
end;

So now I'm able to create a proper json for a given order with a variable declaration and a simple select statement:

create or replace variable row_Order domain_Order;
select row(Number
          ,Customer
          ,Status
          ,GetOrderLines("Order".Id)
          )
from "Order"
where OrderId = 1
for json raw

So far so good. Now I want to add an additional property from the Product table to the json result set. For example Packaging. I would then extend the domain_Product with the new property and adjust the GetProductRow function to return this additional property. And I would expect the json to be extended with the new property at Product level with in the OrderLine object.

But the system will generate an Error because the domain_OrdLine and the domain_Order do not have the proper domain_Product definition. So the domain_OrdLine and the domain_Order need to be dropped and created again.

What happens is that a domain creation replaces the domain_Product with the definition of the domain_Product and will not save the domain data type itself as one would expect.

Similar as when a domain is created with product.name%type. If then the variable property in the product table is altered from 80 characters to 120 characters the domain variable is not extended. But that might be something you would expect that would happen.

These limitations are not in the documents. I would like to see it resolved but I guess that won't be possible due to the setup of the domains currently in the database.

[Breck_Carter]

I don't pretend to understand what you are trying to accomplish...

... but maybe I can shed some light on The False Promise Of CREATE DOMAIN:

The CREATE DOMAIN statement does NOT "create a user-defined data type" like the documentation claims. If it did, there would be an ALTER DOMAIN statement, and there isn't.

The CREATE DOMAIN statement DOES create a simple alias name for a base data type. Once that alias name is used to define a database column, you are stuck with the alias name AND its definition: ALTER DOMAIN does not exist, and DROP DOMAIN cannot be used while ANY base column is referencing that CREATE DOMAIN name.

AFAIK you can use ALTER TABLE to change the base column data type to something else; e.g., a different base data type, or a different CREATE DOMAIN name. Once the original CREATE DOMAIN name is not referenced, DROP DOMAIN can be used.

Suggestion: If you cannot find a way to use CREATE DOMAIN to accomplish your goals, perhaps CREATE VIEW will help... it is possible to completely redefine a view without touching the base table.

[fvestjens]

I want to make my scripts more readable.

For example I created a function to return a row from the address table containing address properties and country properties. So I created a domain for Country

create  domain domain_TFx_Country
row(CountryId            usr.Country.CountryId%type
   ,Description          usr.Country.Description%type
   ,CodeFld              usr.Country.CodeFld%type
   ,IsoCountryCode3Digit usr.Country.IsoCountryCode3Digit%type) 

and one for address

create domain domain_TFx_Address
row(AddressId          Address.AddressId%Type
   ,Name               Address.Name%Type
   ,CodeFld            Address.CodeFld%Type
   ,LocationAddress    Address.LocationAddress%Type
   ,LocationPlace      Address.LocationPlace%Type
   ,LocationPostalCode Address.LocationPostalCode%Type
   ,Longitude          numeric(15,5)
   ,Latitude           numeric(15,5)                     

   ,UIRR               Address.UIRR%Type
   ,Country            domain_TFx_Country)

Then there is a function that will fill return an addressd row as defined by the domain data type.

create function TFx_Address_row(in in_AddressId integer)
returns domain_TFx_Address
begin
 declare row_Address domain_TFx_Address;
 //
 select 
   ROW(Adr.AddressId
      ,Adr.Name
      ,Adr.CodeFld
      ,Adr.LocationAddress
      ,Adr.LocationPlace
      ,Adr.LocationPostalCode 
      ,round(Pla.X / 10000000.0,5)
      ,round(Pla.Y / 10000000.0,5)
      ,Adr.UIRR
      ,TFx_CountryObject_row(Pla.CountryId))
 into variable obj_Address
 from usr.Address Adr
 join usr.Place Pla on Pla.Id = Adr.PlaceId 
 where Adr.Id = in_AddressId;
 //
 return row_Address;
end

The domain data type of this address can then be part of another domain object for example

create domain domain_TFx_OrdProduct
row(Id                OrdProduct.OrdProductId%type
   ,CustomerAddress   domain_TFx_Address
   ,CustomerReference OrdProduct.CustomerReference%type
   ,LoadingAddress    domain_TFx_Address
   ,LoadingReference  OrdProduct.LoadingReference%type
   ,DeliveryAddress   domain_TFx_Address
   ,DeliveryReference OrdProduct.DeliveryReference%type
   ,....)

So now I'm able to fill an array as defined by the domain_TFx_OrdProduct

create variable array_OrdProduct array(16) of domain_TFx_OrdProduct;
--
select 
  array_agg(
   row(OP.Id
      ,TFx_Address_row(OP.CustomerId)
      ,OP.CustomerReference
      ,usr.TFx_Address_row(OP.LoadingAddressId)
      ,OP.LoadingReference
      ,usr.TFx_Address_row(OP.DeliveryaddressId)
      ,OP.DeliveryReference 
      ,....)
      )
into array_OrdProduct
from OrdProduct OP
where OrderId = 123456;

Then that can array can be used to generate a json that needs to be send through http to an API endpoint.

select array_OrdProduct as OrderProducts for json raw

This will construct the following json

[{"OrderProducts:[
{"Id": 13,
 "CustomerAddress": {
   "Id": 1,
   "Name": "Barry Callebaut Belgium N.V.",
   "CodeFld": "BARLEB",
   "LocationAddress": "Aalstersestraat 122",
   "LocationPlace": "Lebbeke",
   "LocationPostalCode": "9280",
   "Longitude": 4.116810,
   "Latitude": 50.99222,
   "UIRR": null,
   "Country": {
     "CountryId": 21,
     "Description": "Belgium",
      "CodeFld": "BE",
      "IsoCountryCode3Digit": "BEL"}
   },
 "CustomerReference": "736",
 "LoadingAddress": {...},
 "LoadingReference": "736",
 "DeliveryAddress": {...},
 "DeliveryReference": "736",
 ...
 }]
}] 

In the json the LoadingAddress and DeliveryAddress object have the same format as the CustomerAddress.

So this is how we have setup the generation of large complex json data in the database. The scripts are well readable and the generate json has the correct property names.

I hope it is clear what we try to accomplish

[VolkerBarth]

The CREATE DOMAIN statement DOES create a simple alias name for a base data type.

Well, it can do more than this optionally, namely to specify (non-)nullability, defaults and/or check constraints. (I'm sure you are fully aware of that.:))

We use domains for common columns like IDs/primary keys, say to specify NOT NULL DEFAULT AUTOINCREMENT, or for general columns with DEFAULT TIMESTAMP or LAST USER semantics. IMHO, domains help to be consistent for such common cases.

Nevertheless, there's no ALTER DOMAIN, as you point out, and I think it's important to understand that any later changes to the actual columns have to be done via ALTER TABLE directly. So it's helpful to get the CREATE DOMAIN specification right in the beginning...

---

That being said, I have no clue to Frank's issue.

[VolkerBarth]

Similar as when a domain is created with product.name%type. If then the variable property in the product table is altered from 80 characters to 120 characters the domain variable is not extended. But that might be something you would expect that would happen.

Well, that's documented behaviour:

Once the data types have been derived and the object (variable, column, and so on) is created, there is no further link or dependency to the object referenced in the %TYPE and %ROWTYPE attribute. However, in the case of procedures that use %TYPE and %ROWTYPE to define parameters and return types, the procedure can return different results if the underlying referenced objects change. This is because %TYPE and %ROWTYPE are evaluated when the procedure is executed, not when it is created.