Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

New database object - Constants [CORE670] #1036

Open
firebird-automations opened this issue Sep 17, 2003 · 31 comments
Open

New database object - Constants [CORE670] #1036

firebird-automations opened this issue Sep 17, 2003 · 31 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Commented by: @asfernandes

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

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Member

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
Copy link
Contributor

aafemt commented Feb 14, 2024

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
Copy link
Member

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
Copy link
Member

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
Copy link

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

@aafemt
Copy link
Contributor

aafemt commented Feb 14, 2024

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

@dyemanov
Copy link
Member

dyemanov commented Feb 14, 2024

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

@sim1984
Copy link

sim1984 commented Feb 15, 2024

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

6 participants