|
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; endAnd 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;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. |
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 29 Dec '21, 07:32
question was seen: 666 times
last updated: 30 Dec '21, 04:06
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.
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
and one for address
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; endThe domain data type of this address can then be part of another domain object for example So now I'm able to fill an array as defined by the domain_TFx_OrdProductcreate 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. 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
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.
Well, that's documented behaviour: