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.

[firebird-automations]

Commented by: @asfernandes

Ok, but anyway, I like the idea to have constants only inside packages, and not as standalone objects, which may be too ambiguous with column names.

[firebird-automations]

Commented by: Simeon Bodurov (simeon.bodurov)

I need that functionality too. We have a lot of constants. Hundreds of them. And now we use them like variables

CREATE PROCEDURE SOME_PROCEDURE_1 RETURNS (ID INTEGER) DECLARE OPTYPE_GET_FROM_CLIENT SMALLINT = 15; AS BEGIN FOR SELECT ID FROM SOME_TABLE WHERE OPTYPE = :OPTYPE_GET_FROM_CLIENT; INTO :ID; DO SUSPEND; END

and it is repeated is every stored procedure which have to use it. And we use constant names, because it is easier to search by constant name instead of it's value.

I propose that you introduce global constants like first suggestion. With syntax:

CREATE CONSTANT OPTYPE_GET_FROM_CLIENT SMALLINT = 15; ALTER CONSTANT OPTYPE_GET_FROM_CLIENT SMALLINT = 16; DROP CONSTANT OPTYPE_GET_FROM_CLIENT;

Аnd, Adriano, they will not be "too ambiguous with column names", because they are separate objects. It like to say that relation name is in conflict with field name. But this:

CREATE TABLE TEST ( TEST SMALLINT NOT NULL )

is possible not ambiguous syntax, and is not in conflict with anything.

And when you need to use the constant in query, you just put ":" in front of them like this:

CREATE CONSTANT OPTYPE_GET_FROM_CLIENT SMALLINT = 15;

CREATE PROCEDURE SOME_PROCEDURE_1 RETURNS (ID INTEGER) AS BEGIN FOR SELECT ID FROM SOME_TABLE WHERE OPTYPE = :OPTYPE_GET_FROM_CLIENT; INTO :ID; DO SUSPEND; END

[firebird-automations]

Commented by: @dyemanov

Wouldn't functions be enough to emulate constants?

CREATE FUNCTION OPTYPE_GET_FROM_CLIENT RETURNS SMALLINT BEGIN RETURN 15; END

[firebird-automations]

Commented by: @pavel-zotov

Function will be called every time when it's referred. So if we have loop of 1'000'000 iterations with function call inside it then such 1 mil calls can affect on performance. Deterministic function will be called once, but: 1) only if it has no input parameters 2) only during lifetime of current unit (i.e. outer SP or trigger or another function).

Constant should act as literal - it must not involve any actions when we refer to it. IMO.

[firebird-automations]

Commented by: Simeon Bodurov (simeon.bodurov)

I just want to add one more possible syntax to constant usage. Because it is not possible to use ":" in DML, they can be used with fake alias CONSTANT like that:

SELECT FIELD_A, FIELD_B FROM SOME_TABLE WHERE FIELD_A = CONSTANT.THE_CONSTANT;

or when there is name conflict in stored procedure:

CREATE CONSTANT SOME_NAME SAMLLINT = 16;

CREATE PROCEDURE SOME_PROCEDURE RETURNS (ID INTEGER) DECLARE SOME_NAME SMALLINT = 15; AS BEGIN FOR SELECT ID FROM SOME_TABLE WHERE OPTYPE = :SOME_NAME -- references the local variable OR OPTYPE = CONSTANT.SOME_NAME -- references the global constant INTO :ID; DO SUSPEND; END

[firebird-automations]

Commented by: @dyemanov

Simeon, read what Adriano suggested re. package constants: create package CONSTANT, a constant THE_CONSTANT inside and you get what you want.

[firebird-automations]

Commented by: @dyemanov

Pavel: 1) constant functions should never have input parameters 2) they could be optimized for global caching (not per request)

[firebird-automations]

Commented by: Simeon Bodurov (simeon.bodurov)

Hello Dimitry,

I have tried that:

ALTER PACKAGE CONSTANT AS BEGIN FUNCTION OPTYPE_1 RETURNS SMALLINT; FUNCTION OPTYPE_2 RETURNS SMALLINT; END

RECREATE PACKAGE BODY CONSTANT AS BEGIN FUNCTION OPTYPE_1 RETURNS SMALLINT AS BEGIN RETURN 1; END FUNCTION OPTYPE_2 RETURNS SMALLINT AS BEGIN RETURN 2; END END

SELECT CONSTANT.OPTYPE_1(), CONSTANT.OPTYPE_2() FROM RDB$DATABASE

That definitely works! Thank you very much for this suggestion.

[dyemanov]

Let's imagine constants are supported per-package. It appears we have a number of name conflict issues. Inside the package (constant is visible without package name prefix) it conflicts with local variables and field names (if used inside SQL query). The former can be kinda resolved using a colon. The latter can be detected during compile time with error raised, thus forcing the user to prefix the constant with a package name. So far so good. But being prefixed with a package name (<package>.<constant>), it now conflicts with <table>.<field>.

Oracle is smart (or may be dumb, depending on POV) enough to prohibit packages and tables with the same name, but we already support that and disallowing it may cause migration troubles. Does anyone see any good solution to this issue?

[aafemt]

IMHO a simple resolution order can help: when compiler/looper is given a name it is looked among local variables then constant. In a queries a table column is considered first and package constant/variable then. Going through full resolution path with some warning of error on conflict may be also an option.

[dyemanov]

Following your suggestion, <table>.<field> wins. But what if <package>.<constant> must be used instead? There's no alternative syntax to force using a constant with these rules. Use a temporary variable to store a constant to be used in a query? Looks quite annoying.

[mrotteveel]

Following your suggestion, <table>.<field> wins. But what if <package>.<constant> must be used instead? There's no alternative syntax to force using a constant with these rules. Use a temporary variable to store a constant to be used in a query? Looks quite annoying.

If that happens it is easily fixed by using an alias for the table in the query to disambiguate.

[EPluribusUnum]

A parameterless and deterministic package function is technically a constant. Why we need an new way to express constant?

[aafemt]

As a syntax sugar and an intermediate step I supposed. The main target is package variables which cannot be substituted by functions.

[dyemanov]

Native constants are also faster. Deterministic functions are optimized for subsequent executions, but still involve quite enough overhead for the first execution.

[sim1984]

Constants inside a package can significantly improve code readability. For example, we have a function RDB$BLOB_UTIL.SEEK, and it has a MODE parameter, into which you can pass some magic values: 0, 1 or 2. Using constants would make calling such a function more understandable.