Forum .LRN Q&A: Re: Datamodel Creation Script Error

Collapse
Posted by Ashem Yadava on
Jarkko

Many thanks for your help. It worked like a charm. And did u mean to put null too in two single quotes or you did it by mistake.

Now I have got more problems :( I am sorry to bother you. Whenever I run the create script it gives me error: duplicate key violates unique constraint "acs_privilege_pk"
in the context of acs_privilege__create_privilege
and acs_privilege__add_child

It gives this for all the create and add child privileges.
However when I ran it for the very first time, it created it all but gave errors about the function inline_0() that you solved.

Q1 - So can you please tell me what is this duplicate key error?

There are couple more syntax errors, and I am trying to find out whats wrong. DO you think plpgsql is case sensitive. That is if I write PERFORM or perform is it the same or different.

Many thanks..
Ashem

Collapse
Posted by Jarkko Laine on
Ashem,

No, I didn't mean to put null in quotes, I just duplicated the existing quotes.

As for Q1, you should make a drop script that corresponds to your create script, which will then drop the privileges etc. that you've created (using "counterfunctions", e.g. acs_privilege__drop_privilege). Now you created those privs the first time you run the script and are trying to re-create them on consecutive runs which obviously leads to duplicate key errors.

Collapse
Posted by Ashem Yadava on
Jarkko

I do have a drop script that drops the privilege and child. Ideally once u have dropped the functions, table, etc.. it should allow u to run the create script again without any errors.. but some how its showing the duplicate key error.

I have checked when I run the create script the table gets created with all the fields..and on runnin the drop script it gets removed.

I think its not able to run cleanly because of the syntax errors I am getting. 1 error in both create and drop file.

I will try to debug and will ask you if I need some more help. Thanks a bunch for your help. Can you give me your mail id, if its not a problem so that I can send you my create/debug script if I am not able to solve the problem at all. (BUT THAT WOULD BE THE LAST RESORT)

Ashem

Collapse
Posted by Jarkko Laine on
Ashem,

Does your drop script have an acs_privilege__remove_child for every acs_privilege__add_child in the creation script, acs_privilege__drop_privilege for every acs_privilege__create_privilege, etc? It's not enough to drop tables and functions, you have to call these functions, too.

You can get my email address by clicking my name on this page and then doing the same again on the next page. But you can cut'n'paste the scripts here, too, if they're not huge. That way more people than just me can try to help you.

Collapse
Posted by Ashem Yadava on
Jarkko

The drop script had a syntax error, I fixed it. So its running fine without any errors. The create script is also able to create the privileges, table, etc successfully.

However, I get the following error while running the create script. This is the only error now.

function acs_object_type__create_type("unknown","unknown","unknown","unknown","unknown","unknown", "unknown") does not exist.

FOLLOWING IS THE CONCERNING CODE

-- Create a room object
create function inline_0()
returns integer as '
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
  /*I have also tried with
  PERFORM acs_object_type__create_type(
  */
attr_id :=  acs_object_type__create_type (
  ''room'',          -- object_type
  ''Chat Room'',    -- pretty_name
  ''Chat Rooms'',    -- pretty_plural
  ''acs_object'',    -- supertype
  ''chat_rooms'',    -- table_name
  ''room_id'',      -- id_column
  null              -- name_method
  );

-- Create attributes for object room
-- Attribute: pretty name
attr_id := acs_attribute__create_attribute (
  ''room'',          -- object_type
  ''pretty_name'',  -- attribute_name
  ''string'',        -- datatype
  ''Room Name'',    -- pretty_name
  ''Room Names''    -- pretty_plural
  );

-- Attribute: detail
attr_id := acs_attribute__create_attribute (
  ''room'',          -- object_type
  ''detail'',        -- attribute_name
  ''string'',        -- datatype
  ''Detail'',        -- pretty_name
  ''Details''        -- pretty_plural
  );

-- Attribute: moderated
attr_id := acs_attribute_create_attribute (
  ''room'',          -- object_type
  ''moderated_p'',    -- attribute_name
  ''boolean'',        -- datatype
  ''Moderated'',      -- pretty_name
  ''Moderated''      -- pretty_plural
  );

-- Attribute: active
attr_id := acs_attribute_create_attribute (
  ''room'',          -- object_type
  ''active_p'',      -- attribute_name
  ''boolean'',      -- datatype
  ''Activated'',    -- pretty_name
  ''Activated''      -- pretty_plural
  );

return 0;

end;'

language plpgsql;

select inline_0();
drop function inline_0();

-----------------------------

Many thanks for the unending help 😊)

Ashem

Collapse
Posted by Jade Rubick on
I haven't looked over everything carefully, but I'm sure that you can't have null for the name method.

You can look at project-manager or other applications for examples of this.

Collapse
Posted by Jarkko Laine on
Jade,

maybe it's different because it's content_type__create_type, but at least the package developer guide seems to be setting name_method as null:

select content_type__create_type(
    'mfp_note',                    -- content_type
    'content_revision',            -- supertype
    'MFP Note',                    -- pretty_name,
    'MFP Notes',                  -- pretty_plural
    'mfp_notes',                  -- table_name
    'note_id',                    -- id_column
    null                          -- name_method
);