Forum .LRN Q&A: Datamodel Creation Script Error

Collapse
Posted by Ashem Yadava on
Hi

I have just started programming in dotLRN. And I have encountred the following errors in the database creation script. I would appreciate a lot if some one can take out time and help me.

The errors are -

1. Function inline_0() does not exist.
HINT: No function matches the given name and argument types. You may need to add explicit type casts
2. LINE 182: Syntax error at or near 'room'

I am also attaching the code..

Thanks
Ashem

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

-- chat-auth-create.sql

-- Create privileges for the chat rooms
select acs_privilege__create_privilege('room_create', 'Create a chat room', null);
select acs_privilege__create_privilege('room_modify', 'Modify a chat room', null);
select acs_privilege__create_privilege('room_delete', 'Delete a chat room', null);
select acs_privilege__create_privilege('room_view', 'View chat room details', null);

-- Create privileges dealing with users
select acs_privilege__create_privilege('chat_user', 'Normal chat user', null);
select acs_privilege__create_privilege('user_grant', 'Add user to a chat room', null);
select acs_privilege__create_privilege('user_revoke', 'Delete user from a chat room', null);
select acs_privilege__create_privilege('user_ban', 'Ban user from accessing a chat room', null);
select acs_privilege__create_privilege('user_unban', 'Unban user to access a chat room', null);

-- Create read/write privileges
select acs_privilege__create_privilege('message_read', 'Can read chat message', null);
select acs_privilege__create_privilege('message_write', 'Can write chat message', null);

-- Create privileges dealing with  moderators/professors, etc.
select acs_privilege__create_privilege('room_moderator', 'Chat room moderator', null);
select acs_privilege__create_privilege('room_moderate', 'Moderate a chat room', null);
select acs_privilege__create_privilege('moderator_grant', 'Add moderator to a chat room', null);
select acs_privilege__create_privilege('moderator_revoke', 'Delete moderator from a chat room', null);

-- Create privileges dealing with administrator
select acs_privilege__create_privilege('room_admin', 'God for chat', null);

-- Provide privileges to normal chat users
select acs_privilege__add_child('chat_user', 'message_read');
select acs_privilege__add_child('chat_user', 'message_write');

-- Provide privileges to moderators/professors, etc
select acs_privilege__add_child('room_moderator', 'chat_user');
select acs_privilege__add_child('room_moderator', 'room_moderate');
select acs_privilege__add_child('room_moderator', 'user_ban');
select acs_privilege__add_child('room_moderator', 'user_unban');

-- Provide privileges to chat room administrator/site wide administrator
select acs_privilege__add_child('room_admin', 'room_create');
select acs_privilege__add_child('room_admin', 'room_modify');
select acs_privilege__add_child('room_admin', 'room_delete');
select acs_privilege__add_child('room_admin', 'room_view');
select acs_privilege__add_child('room_admin', 'user_grant');
select acs_privilege__add_child('room_admin', 'user_revoke');
select acs_privilege__add_child('room_admin', 'room_moderator');
select acs_privilege__add_child('room_admin', 'moderator_grant');
select acs_privilege__add_child('room_admin', 'moderator_revoke');
select acs_privilege__add_child('admin', 'room_admin');

-- Create a room object
create function inline_0()
returns integer as '
declare
attr_id acs_attributes.attribute_id%TYPE;
begin
PERFORM

acs_object_type__create_type (
  'room',          -- object_type
  'acs_object',    -- supertype
  'Chat Room',    -- pretty_name
  'Chat Rooms',    -- pretty_plural
  '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();

-- Create table chat rooms
create table chat_rooms (

-- Each room has an unique id
room_id    integer
        constraint chat_rooms_room_id_pk primary key
        constraint chat_rooms_room_id_fk
        references acs_objects(object_id),

-- Pretty name of a room
pretty_name    varchar(50)
        constraint chat_rooms_pretty_name_nn not null,

-- Room description
detail        varchar(150),

-- Whether the room is moderated, default is false
moderated_p    boolean
        default 'f'
        constraint chat_rooms_moderated_p_ck check
        (moderated_p in ('t', 'f')),

-- Whether the room is active, default is true
active_p    boolean
        default 't'
        constraint chat_rooms_active_p_ck check
        (active_p in ('t', 'f'))
);

-- Function room add
create function room__new (integer, varchar, varchar, boolean, boolean, integer, timestamptz, integer, varchar, varchar)
returns integer as '
declare
p_room_id     alias for $1;
p_pretty_name  alias for $2;
p_detail     alias for $3;
p_modertated_p  alias for $4;
p_active_p     alias for $5;
p_context_id     alias for $6;
p_creation_date alias for $7;
p_creation_user alias for $8;
p_creation_ip     alias for $9;
p_object_type  alias for $10;
v_room_id     chat_rooms.room_id%TYPE;

begin
v_room_id := acs_object__new (
null,
'room',
now(),
p_context_id,
p_creation_user,
p_creation_ip
);

insert into chat_rooms (room_id, pretty_name, detail, moderated_p, active_p)
values (v_room_id, p_pretty_name, p_detail, p_moderated_p, p_active_p);
return v_room_id;

end;'

language 'plpgsql';

-- Function room name
create function room__name (integer)
returns varchar as '
declare
p_room_id    alias for $1;
v_pretty_name  chat_rooms.pretty_name%TYPE;

begin
select into v_pretty_name pretty_name from chat_rooms where room_id = p_room_id;
return v_pretty_name;

end;'

language 'plpgsql';

-- Function room modify
create function room__modify (integer, varchar, varchar, boolean, boolean)
returns integer as '
declare
p_room_id    alias for $1;
p_pretty_name    alias for $2;
p_detail    alias for $3;
p_moderated_p  alias for $4;
p_active_p    alias for $5;

begin
update chat_rooms set
pretty_name = p_pretty_name,
detail      = p_detail,
moderated_p = p_moderated_p,
active_p    = p_active_p
where
room_id    = p_room_id;

return 0;

end;'

language 'plpgsql';

-- Function room remove
create function room__remove (integer)
returns integer as '
declare
p_room_id    alias for $1;

begin
delete from chat_rooms where room_id = p_room_id;
PERFORM acs_object__delete(p_room_id);
return 0;

end;'

language 'plpgsql';

Collapse
Posted by Jarkko Laine on
Ashem,

You have to escape the single quotes you use in pl/pgSQL (i.e. inside the inline_0() function). It's done by using two single quotes instead of one. But remember, it's only the case in PL/pgSQL syntax, not in normal SQL.

Here's an example:
acs_object_type__create_type (
  ''room'',          -- object_type
  ''acs_object'',    -- supertype
  ''Chat Room'',    -- pretty_name
  ''Chat Rooms'',    -- pretty_plural
  ''chat_rooms'',    -- table_name
  ''room_id'',      -- id_column
  ''null''          -- name_method
  );

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 Don Baccus on
Also for new packages ... be aware that we've deprecated the practice of creating "foo_read", "foo_write" etc privileges that merely duplicate the semantics of "read" or "write" etc for object of type "foo".

Whenever possible it is best to use the standard privileges (defined in acs-kernel) "read", "write", "create", "edit", and "delete" (I think I've got those right...)

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 😊)

Ashem

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 Ashem Yadava on
Don

Can you please state an example of what you meant by changing foo_read into read, etc.

Currently I am using the privileges as below based on several packages like news, etc.

select acs_privilege__create_privilege('room_create', 'Create a chat room', null);
select acs_privilege__create_privilege('room_edit', 'Edit  a chat room', null);
select acs_privilege__create_privilege('room_delete', 'Delete a chat room', null);
select acs_privilege__create_privilege('room_view', 'View chat room details', null);

select acs_privilege__create_privilege('room_admin', 'Room administrator', null);
select acs_privilege__add_child('room_admin', 'room_create');
select acs_privilege__add_child('room_admin', 'room_edit');
select acs_privilege__add_child('room_admin', 'room_delete');
select acs_privilege__add_child('room_admin', 'room_view');

What do I need to change in this.

Thanks
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 Jade Rubick on
You can just leave out all of the acs_prvilege__ statements.
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
);

Collapse
Posted by Jarkko Laine on
Yeah, just leave those statements out and then require just simple privileges (like "read") for your objects instead of these, unless you really need something that can't be accomplished with standard privileges in OpenACS. News is quite an old package and as such doesn't probably reflect very well the current developer guidelines.
Collapse
Posted by Ashem Yadava on
Thanks guys for all the help. Things are working fine at the moment.

Really appreciate it!!

Ashem 😊