Hi Community,
is there any way when you create an computer or monitor ... to generate an hardware id for this device.
Somethin like this or easier:
BBZ120001 ("BBZ""Year""autogenerated numer")
Best Regards
Mattheus Erb
Actually there is no way for doing this using the UI, but you can do it adding some functions/triggers to the database. Here an example:
CREATE SEQUENCE sampleSequence INCREMENT 1 START 1;
CREATE OR REPLACE FUNCTION sampleFunction() RETURNS trigger AS $$
BEGIN
IF ( TG_OP = 'INSERT' ) THEN
NEW."Code" = 'APP' || (select lpad(nextval('sampleSequence')::varchar, 3, '0'));
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER sampleTrigger
BEFORE INSERT OR UPDATE
ON "SampleClass"
FOR EACH ROW
EXECUTE PROCEDURE sampleFunction(E'\\x');
Best regards.
Hi Community,
is there any way when you create an computer or monitor ... to generate an hardware id for this device.
Somethin like this or easier:
BBZ120001 ("BBZ""Year""autogenerated numer")Best Regards
Mattheus Erb
1 Like
Ok, but if I want to reuse a free ID (for example because an asset had been deleted) is this possible?
Because nextval increment, but doesn't reuse a free ID.
Thanks.
Mario
No, that solution is based on Postgres sequences that do not handle the reuse of free numbers
CMDBuild Team
Previously Tecnoteca wrote:
Actually there is no way for doing this using the UI, but you can do it adding some functions/triggers to the database. Here an example:CREATE SEQUENCE sampleSequence INCREMENT 1 START 1;
CREATE OR REPLACE FUNCTION sampleFunction() RETURNS trigger AS $$
BEGIN
IF ( TG_OP = 'INSERT' ) THEN
NEW."Code" = 'APP' || (select lpad(nextval('sampleSequence')::varchar, 3, '0'));
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER sampleTrigger
BEFORE INSERT OR UPDATE
ON "SampleClass"
FOR EACH ROW
EXECUTE PROCEDURE sampleFunction(E'\\x');
Best regards.
Previously Mattheus wrote:Hi Community,
is there any way when you create an computer or monitor ... to generate an hardware id for this device.
Somethin like this or easier:
BBZ120001 ("BBZ""Year""autogenerated numer")Best Regards
Mattheus Erb
Hi, I'm new to the CMDBuild software. I'm trying to create a Class with an attribute that uses auto incrementation. I've seen this post and sadly can't make sense of it. Any chance you can take me through it at a basic level? I'm used to using MySQL so I'm a bit in the dark.
I have a Class called "Assets" which includes an attribute called "AssetID". This attribute needs to use the format CG00000 and increment to a value of 1 (eg. CG00001, CG00002, etc). I'm not worried about reusing values and I'm happy for each new card to us a new number.
Please help! I have a great system setup and this is the only thing holding me back.
I have to say I'm VERY impressed by the CMDBuild software so far.
Tim
1 Like
You have only to open the Postgres client pgAdmin, open the SQL window and create write a trigger like that we have posted.
You have then to replace the statement:
NEW."Code" = 'APP' || (select lpad(nextval('sampleSequence')::varchar, 3, '0'));
with the statement:
NEW."Code" = 'CG' || (select lpad(nextval('sampleSequence')::varchar, 5, '0'));
and replace the name "SampleClass" with the name "Assets"(and possibly give a different name to simpleSequence, simpleTrigger, simpleFunction).
CMDBuild Team
One issue with the existing solutions proposed here is that they do not automatically apply to any new entities created, so each time you create a new one you have to add the triggers.
This solution is a modification of the earlier solutions which does do this.
It also adds the text CI to the beginning of the ID. If you don't want this then just remove the "'CI' ||" bit from the function.
PLEASE NOTE - this solution modifies one of the core cmdb functions _cm_create_class_triggers in order to achieve this (by adding a line to the functions that are executed on creation of a new class. This means that if you upgrade the CMDB ID generation may cease to work and you will potentially need to adjust the solution to take account of any changes to the core function.
CREATE OR REPLACE FUNCTION _cm_create_id_sequence() RETURNS void as $$
BEGIN
IF NOT EXISTS ( SELECT * FROM pg_catalog.pg_class WHERE relname = 'id_sequence') THEN
CREATE SEQUENCE id_sequence INCREMENT 1 START 1;
END IF;
END;
$$ LANGUAGE PLPGSQL;
SELECT _cm_create_id_sequence()
CREATE OR REPLACE FUNCTION _cm_generate_id() RETURNS trigger AS $$
BEGIN
IF(TG_OP = 'INSERT') THEN NEW."Code" = 'CI' || (select lpad(nextval('id_sequence')::varchar,5,'0'));
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION _cm_add_class_id_trigger(TableId oid) RETURNS void AS $$
BEGIN EXECUTE 'CREATE TRIGGER "_CreateId"
BEFORE INSERT OR UPDATE
ON '|| TableId::regclass ||'
FOR EACH ROW
EXECUTE PROCEDURE _cm_generate_id()
';
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION _cm_create_class_triggers(TableId oid) RETURNS void AS $$
BEGIN
IF _cm_is_superclass(TableId) THEN
RAISE DEBUG 'Not creating triggers for class %', TableId::regclass;
ELSIF _cm_is_simpleclass(TableId) THEN
PERFORM _cm_add_simpleclass_sanity_check_trigger(TableId);
ELSE
PERFORM _cm_add_class_sanity_check_trigger(TableId);
PERFORM _cm_add_class_history_trigger(TableId);
PERFORM _cm_add_class_cascade_delete_on_relations_trigger(TableId);
PERFORM _cm_add_class_copy_name_to_description_trigger(TableId);
PERFORM _cm_add_class_id_trigger(TableId);
END IF;
END;
$$ LANGUAGE PLPGSQL;
Sorry to dig up an old post, just saw this and thought it might be worth adding my 2 cents...
I haven't tested this, but there are built-in (to CMDBuild) stored procedures to copy triggers from the superclass to any new class (and remove them when done). This is a bit of a hack, but the triggers are matched by pattern, specifically:
- _UpdRel_%
- %_fk
- _Constr_%
What one could do is create a trigger with a name that would be matched by the above patterns, but does not conflict with any name that the CMDBuild code might create for itself.
I suggest that if you name the trigger _Constr_SuperClass_Code (replace SuperClass with the name of the SuperClass you attach the trigger to...), that would be roughly in keeping with the intent of the "constraint" triggers, and as "Code" can never be a foreign key, shouldn't conflict with anything that CMDBuild might create.
Previously Marius wrote:One issue with the existing solutions proposed here is that they do not automatically apply to any new entities created, so each time you create a new one you have to add the triggers.This solution is a modification of the earlier solutions which does do this.
I was wondering whether this is still valid in CMDBuild v3 - is there no way to create auto-increment (read-write) attributes? We would very much like to migrate our internal system to track physical products which leave the production line, however we have a spcific requirement to start the sequence with a specific number (lets say this is 123456) and increment the number on each new creation of the card automagivally as a unique number. We , however also need the ‘write’ to change the value as required, but still needs to be checked for uniqueness.