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
Comments
Commented by: Alice F. Bird (firebirds) Date: 2004-07-20 01:10 Great for use within stored procedures to for greater ease of maintainence. |
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... 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
or perhaps add new namespace for RDB$GET_CONTEXT: 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. |
Modified by: @pcisarWorkflow: jira [ 10694 ] => Firebird [ 15082 ] |
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? |
Commented by: Ann Harrison (awharrison) Global variables are often the source of bugs in software. I doubt they'd |
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. |
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" But this would be easier: |
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 |
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 .... |
Modified by: Sean Leyne (seanleyne)description: SFID: 807963# Support constants to be stored within the database => SFID: 807963# 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: |
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. |
Commented by: @asfernandes Yes, Valdir. And then, why it's needed at all, instead of use packaged deterministic functions? |
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. |
Commented by: @asfernandes If a functions has a single statement RETURN <constant>, I think it could be optimized as well. |
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. |
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. |
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 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. CREATE CONSTANT OPTYPE_GET_FROM_CLIENT SMALLINT = 15; Аnd, Adriano, they will not be "too ambiguous with column names", because they are separate objects. CREATE TABLE TEST ( 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 |
Commented by: @dyemanov Wouldn't functions be enough to emulate constants? CREATE FUNCTION OPTYPE_GET_FROM_CLIENT |
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. Constant should act as literal - it must not involve any actions when we refer to it. IMO. |
Commented by: Simeon Bodurov (simeon.bodurov) I just want to add one more possible syntax to constant usage. SELECT FIELD_A, FIELD_B or when there is name conflict in stored procedure: CREATE CONSTANT SOME_NAME SAMLLINT = 16; CREATE PROCEDURE SOME_PROCEDURE |
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. |
Commented by: @dyemanov Pavel: |
Commented by: Simeon Bodurov (simeon.bodurov) Hello Dimitry, I have tried that: ALTER PACKAGE CONSTANT AS BEGIN RECREATE PACKAGE BODY CONSTANT AS BEGIN SELECT CONSTANT.OPTYPE_1(), CONSTANT.OPTYPE_2() That definitely works! Thank you very much for this suggestion. |
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 ( 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? |
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. |
Following your suggestion, |
If that happens it is easily fixed by using an alias for the table in the query to disambiguate. |
A parameterless and deterministic package function is technically a constant. Why we need an new way to express constant? |
As a syntax sugar and an intermediate step I supposed. The main target is package variables which cannot be substituted by functions. |
Native constants are also faster. Deterministic functions are optimized for subsequent executions, but still involve quite enough overhead for the first execution. |
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. |
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}..));
The text was updated successfully, but these errors were encountered: