New database object - Constants [CORE670]

[firebird-automations]

Submitted by: @pcisar

Votes: 21

SFID: 807963#⁠
Submitted By: pcisar

Support constants to be stored within the database using the syntax:CREATE CONSTANT {ObjectName} {Datatype} AS {Value}. This would allow for the constants to be used in other SQL statements like:
CREATE DOMAIN TTypeKind AS SMALLINT DEFAULT {Constant) NOT NULL CHECK( VALUE IN ( {Constant}, {Constant}..));

[firebird-automations]

Commented by: Alice F. Bird (firebirds)

Date: 2004-07-20 01:10
Sender: kevd
Logged In: YES
user_id=934280

Great for use within stored procedures to for greater ease of maintainence.

[firebird-automations]

Commented by: Ain Valtin (ain)

In addition to using them to define domains, I'd expect to be able to use these constants "directly by name" in both PSQL and DML, ie

CREATE CONSTANT C_Min_Year INTEGER AS 1950;

CREATE TRIGGER...
BEGIN
IF(NEW.Year < C_Min_Year)THEN NEW.Year = C_Min_Year;
END

SELECT Year - C_Min_Year FROM T

Now in DML there is possibility for name clash with column name... I quess that for backward compatibility default should be column, but I would actually prefer that exeption is raised so that user is forced to make it clear does s/he refer to the constant or column. For that "namespace" like OLD and NEW could be used, ie

SELECT T\.Year \- CONSTANTS\.C\_Min\_Year FROM T

or perhaps add new namespace for RDB$GET_CONTEXT:
SELECT T.Year - RDB$GET_CONTEXT('CONSTANTS', 'C_Min_Year') FROM T

but this later solution is worse as there is no "compile time check" does the constant really exists.

Of course, ALTER CONSTANT must be implemented too.

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 10694 ] => Firebird [ 15082 ]

[firebird-automations]

Commented by: Philip Williams (unordained)

I would suggest that the namespace of constants be user-picked, but required. All constants would need to be referred to as ConstantSet1.ConstantName1. Naming the namespace would help group constants together (all used as enums in a single CHECK constraint), but keep distinct groups ... distinct. Requiring the namespaces to be distinct object names in the same object namespace as everything else could guarantee that there's never a conflict between a constant namespace and a table name in a query -- select x.stuff - x_constants.stuff from x;

How would that affect the oracle-like "packages" work being done?

[firebird-automations]

Commented by: Ann Harrison (awharrison)

Global variables are often the source of bugs in software. I doubt they'd
be better in databases.

[firebird-automations]

Commented by: Michael Ludwig (milu)

They would be global *constants*, not *variables*, and as such they wouldn't automatically qualify for being the cause of bugs, rather the opposite.

Picture an INTEGER used as a bitvector where you want each bit to have some meaning, and where you want to encapsulate access to this efficient yet fragile structure in a set of stored procedures. Let's imagine two stored procedures (out of, say, 40) that both need to access three of those bits and update two of them. So "MakeConnection" would declare three INTEGERs just to hold the position of the bits in the bitvector. Nicely solved. But now "DropConnection" has to do the same thing. And if you were to create "UpgradeConnection" it might have to do the same thing all over again.

I guess you can see my point. Global CONSTANTs would allow you to define these constants in one place, and one place only. It would be very useful.

[firebird-automations]

Commented by: PizzaProgram Ltd. (szakilaci)

I agree this feature is a must. I would like to use it for fast replicated database id generation.

Currently as workaround I'm using this:

CREATE OR ALTER PROCEDURE "SP_GEN_ORDER_ID"
returns (
id integer)
as
BEGIN
ID = GEN_ID("GEN_ORDER_ID", 1)*100 + GEN_ID("DB_NUMBER",0 );
SUSPEND;
END

But this would be easier:
...
ID = GEN_ID("GEN_ORDER_ID", 1)*100 + DB_NUMBER;

[firebird-automations]

Commented by: Michael Ludwig (milu)

Laszlo,

another possible workaround for the moment is to preprocess your DDL using a makro package such as m4 which fills in the numbers for your constants. Should work fine, especially combined with version control and make, Ant, or a similar build tool.

Michael

[firebird-automations]

Commented by: Tomas Krejzek (respektive)

This feature would be great in procedures and triggers

Mainly for comparing with status fields

if (new.user_status = CONST_USER_STATUS_NEW) then ....

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

description: SFID: 807963#⁠
Submitted By: pcisar

Support constants to be stored within the database
using the syntax:CREATE CONSTANT {ObjectName}
{Datatype} AS {Value}. This would allow for the
constants to be used in other SQL statements like:
CREATE DOMAIN TTypeKind AS SMALLINT DEFAULT {Constant)
NOT NULL CHECK( VALUE IN ( {Constant}, {Constant}..));

=>

SFID: 807963#⁠
Submitted By: pcisar

Support constants to be stored within the database using the syntax:CREATE CONSTANT {ObjectName} {Datatype} AS {Value}. This would allow for the constants to be used in other SQL statements like:
CREATE DOMAIN TTypeKind AS SMALLINT DEFAULT {Constant) NOT NULL CHECK( VALUE IN ( {Constant}, {Constant}..));

[firebird-automations]

Commented by: Valdir Stiebe Junior (ogecrom)

An alternative for the CONSTANTS namespace.

Make the CONSTANT object exists only inside a PACKAGE object.

This way will be possible to separate different constants inside packages. And this lead to cleaner code as each package may have meaningful name.

[firebird-automations]

Commented by: @asfernandes

Yes, Valdir. And then, why it's needed at all, instead of use packaged deterministic functions?

[firebird-automations]

Commented by: @dyemanov

Value of the declared constant is known in advance, value returned by the function is unknown until its first execution. This makes a big difference for the optimizer, e.g. histograms can be used for a constant but not for a function.

[firebird-automations]

Commented by: @asfernandes

If a functions has a single statement RETURN <constant>, I think it could be optimized as well.

[firebird-automations]

Commented by: Valdir Stiebe Junior (ogecrom)

As long as this functions could also be used as default values for parameters, fields and check constrains, indeed there were no use for them. Except for optimization and organizational purposes.
I admit that I've not tested all the FB3 possibilities yet. So functions were out of my mind.