Homepage

• worldViewableContent: /users/
• siteAdministratorDirectory: /admin/users/
• userHomepageMaintenance: /homepage/
• dataModel: /doc/sql/homepage.sql
• TclProcs: /tcl/homepage-defs
• userContent: /web/servername/users/ (determined by [ad_parameter ContentRoot users])

0     Introduction

0.1     Abilities

The Homepage Module gives users of the site the ability to publish content on the world-wide-web. The users can create their own heirarchical directory structure to organize their content on the site. The not-so-advanced users and others who want to publish large amounts of content and wish to give their content a consistent look, can create special "managed content" directories in their file system. This would be useful for publishing content like books etc which require a considerable level of consistency between all the parts (chapters in the case of books) of the publication. This in no way hampers the endeavours of advanced html publishers who would like to maintain their own filesystem since they can choose not to use content managed directories and have full control over the look and feel of their web pages.

0.2     Inabilities

The homepage module does not let users to author server side scripts, althought users are free to use client side scripts in html using the <script> tag. The module does not provide users with a database handle and therefore cannot be used to author database backed webpages.

1     User's Manual

1.1     Homepage Maintenance

If the homepage module is enabled in your system, three links should appear in your workspace: "Homepage Maintenance", "Neighborhoods", and "User Homepages". The "Homepage Maintenance" link is for maintining you own homepage and mainting the content you publish. The "Neighbourhoods" page is for browsing through neighbourhoods and viewing their members or joining one of them. You can leave your neighbourhood by clicking on "leave it!" link on the neighbourhoods page (this link appears only when you are the member of a neighbourhood).

1.1.1     Neighbourhoods Page

The neighbourhoods page lets you browse through neighbourhoods. You can join a neighbourhood by clicking on the [join] button besides a neighbourhood's entry in the list. Similarly, the [members] button lets you view all the members of a neighbourhood. As a normal user, you cannot create, modify, delete, move, or rename neighbourhoods. An attempt at doing so will result only in a meaningless error message such as "Insufficient permission to perform requested database access in AddNeighborhood". The neighbourhoods page is located at /homepage/neighbourhoods. Alternatively, there is a link to it from your workspace.

1.1.2     User Homepages

A list of the users who have initialized their webspace (webspace initialization is discussed in 1.1.3) can be seen at /homepage/all. There is a link to it, marked as "User Homepages", at your workspace.

1.1.3     Webspace Initialization

This section discusses using the "homepage maintenance" option for the first time.

1.1.3.1     Root Directory Creation

You need to initialize your webspace before you can use the homepage module for the first time. When you attempt to go to the "Homepage Maintenance" page from your workspace or by visiting /homepage/, a message appears which reads "Your webspace has not been activated yet. Click here to set it up for the first time." Just click on "here" in the "... Click here to ..." and your webspace will be initialized. This process should take about a few hundred microseconds. In this process, the system creats a root directory for you and this is depicted by the consumption of some meagre amount of your quota space (typically 2,048 bytes). Once it is done, you can start maintaining your webpage if you already have a screen name set up.

1.1.3.2     Setting up a screen name

If you have initialized your webspace but do not have a screen name yet, the "homepage maintenance" page /homepage/ displays a message with reads "You have not set up a screen name as yet. Click here to set one up.". You can set up your screen name by clicking on the "here" or alternatively, you can set up your screen name by clicking on the "update" link in your "Basic Information" section in your workspace /pvt/home.

1.1.4     Homepage Maintenance Page

The homepage maintenance page is at /homepage/. You use it to maintain your web content once your webspace has been initialized and you have set up a screen name for yourself.

1.1.4.1     Customizing your Maintenance and Public Pages

You can change the display settings of your maintenance page by clicking on the [display-settings] link in the lower right of the page. There are two types of settings: settings for public pages, and settings for maintenance pages. The settings will affect your public and maintenance pages respectively. Your public pages are the pages shown to the entire world and are available at http://yourdomain.com/users/your_screen_name. Your maintenance page is /homepage/, the page through which you maintain your webspace. You can also customize the way you view your files. The directory listing can be normal or hierarchical. This can be set using the [ normal view | tree view ] buttons at the upper right of the screen. By clicking on a directory, you browse its content (in the normal view), or you browse content rooted at it (in the tree view).

1.1.4.2     Creating Files

You can create files by clicking on the "create file" link. You will be prompted for the name, and an empty file with that name will be created. Mind however that to be able to invoke the editor on a file, it should have a .text, , .htm, .txt or anyother extension that represents a mime type of text/*. If you're unsure of what this means, just end the filename in if it's an html file or .text if it's a text file. If you do not know html then, for your own good, I advise you to learn it. It is a very good advice. Take it. Html is very very easy to learn. Meanwhile, you can just name your files ending with .text or just use the "publish content" option. It requires no knowledge of html. The created file is placed in the directory you are currently browsing. Html files have the following format:
<html>
<head>
<title>title-goes-here</title>
</head>
<body>

body-text-goes-here

</body>
</html>

To learn about the things you can do in the body text, read the lower half of section 1.1.4.5.1 of this document

1.1.4.2.1     Index files and generated indices

If a web user requests a file from your webspace which is actually a directory, then the system generates a default index page for it. The format of the index page is such: your portrait (if you uploaded one following the links in your workspace) in the top right, your name as heading, and "webspace at your-system-name" as subheading. Then it displays a listing of files in the directory. If you do not wish to have a generated index, you can create an index file in that directory. By default filenames index, index.htm, and Default.htm will be treated by the system as index files and will be served when a directory is requested by the web browser. The presence of an index file makes it impossible for the web browser to lit the contents of that directory.

1.1.4.3     Uploading Files

You can upload files from your local computer to your homepage webspace by clicking on the "upload file" link. You can either type the full filename on the local filesystem or press the browse button to browse your local filesystem to select the file you want to upload. Please mind your limited quota space before deciding on which files to upload.

1.1.4.4     Creating Folders

Create folders using the "create folder" link. You are prompted for a folder name and a description. Choose a nice description for your folders as these descriptions are displayed alongside the folder name in the generated index pages which the world sees. You can remove, rename, or move folders by using the remove, rename, and move links respectively. A folder which is not empty cannot be deleted.

1.1.4.5     Invoking the Content Manager

Click the "publish content" link to publish managed content on your site. You have to provide four parameters to the content manager: the content type, short name, full name, and subsections. Content type determines the type of your content. It can be anything from "book", "magazine", "newsletter", "encyclopedia", to anything you can come up with. The short name is a short name which describes the content. A specially marked folder with that name is created to store the content. The full name is the full name of your content. For example, if you're publishing your book then this ought to be the complete title of the book etcetra. In subsections, provide what one subsection of the content is called. For a book, this could be "chapter". If the content type is chapter, this could be "section". Please mind that you must provide a singular in this field.

1.1.4.5.1     Managing Content

I will illustrate this with an example. Suppose I click on "publish content" and create content of type "magazine", short name "mobmag", full name "Mobin's Foolish Magazine", and subsections "article". This tells the content manager that I wish to publish a magazine which is composed of articles (multiple level hierarcies in content is possible but not discussed here). The content manager creates a folder "mobmag" (which is marked as a a magazine). When I click on "mobmag" to browse its contents, I see one file in it already, "Introductory Text". You can only edit or remove this file. The web viewer sees the contents of this file on the main magazine page (in this case http://mydomain.com/users/mobin/mobmag/) above the table of contents. To add an article, click on the "add article" link. This adds an empty article which you can then edit. While creating articles, choose meaningful descriptions for them. You can use html in these files if you want. This gives you the ability to show uploaded photos i your web content. The contents of these files are pasted within larger html files when these are served so you should not use the <html>, <title>, <body> etcetra tags. Also, since these files are really html, you will need to escape <, >, and & with &lt, &gt and &amp respectively if any of these are used as text. So you can enclose text within the <h2> and </h2> to make it a second level heading, <b> and </b> to make it bold, <i> and </i> to make it italicised and more importantly, you can use something like <a href=http://whatever address>whatever link name</a> to provide a link to any addess on the web. Also, you can have something like <img src=picture-filename> to display the picture which has the name picture-filename. This way you can upload picture files and then show them in your documents.

1.1.4.5.2     Deleting Managed Directories

Delete all files in a managed directory by using the remove links next to them and then go to the parent directory and then remove the managed directory.

1.2     Sitewide Administration

1.2.1     User Administration

User administration is the same as has been before. Its directory is /admin/users/ However, the homepage module has introduced the concept of user quotas. Also, there had been a long felt need for screen names for users in ACS. With the homepage module, this need exceeded the threshold level and we incorporated them into the main ACS users table. The administration of screen names and user quotas is done via /admin/users/ Thus the homepage module doesn't really have a site wide administration directory of its own. Its administration is mainly done via /admin/users/

1.2.2     Neighbourhood Administration

Administration of neighbourhoods is done at /homepage/neighbourhoods. This page will hereby be referred to as the "neighbourhoods page". If the homepage module is enabled in your system, a link should appear to the neighbourhoods page at your workspace. The neighbourhoods page is essentially for browsing through neighbourhoods, viewing their members or even joining a neighbourhood. Additionally, site wide administrators can create neighbourhoods by pressing the [create-neighborhood] button above the neighbourhoods listing; remove neighbourhoods by pressing the [remove] button besides a neighbourhoods name; change neighbourhood hierarchy by pressing the [move] button and moving neighbourhoods around; or change the name or description of a neighbourhood by pressing the [rename] button besides a neighbourhood name. Administrators should mind the five hundred letter maximum limit on neighbourhood names and a four thousand letter limit on neighbourhood descriptions.

1.2.3     User Content Administration

Situations may arise where an administrator will need to remove user content or take other such actions etcetra. In such a situation, the administrator can go to /admin/users/, search for the appropriate user and click on the "become this user!" link. This allows the administrator to become any user he or she might need to. (Just like the unix "su" command which lets the root swith into other users). This means that now administrators can do anything that a user can do. Administrators could become other users previously (by forcibly changing their password and logging in as them) but this is a much cleaner and acceptable way of doing that. Ofcourse, administrators must not misuse this feature and should use it only to administer when other means are unavailable.

2     Technical Documentation

2.1     User Quota Management

; Maximum quota for a 'normal user' (a lesser mortal who  is not the 
; site wide administrator) of the site in mega-bytes. For example, a 
; '20' corresponds to 20,971,520 bytes. Values need not be  integer. 
; Value is overridden for a  particular user  throught existence  of 
; entry in the users_special_quotas table.
NormalUserMaxQuota=5

; Maximum quota for site wide administrators.  Special quotas can be 
; set by adding rows to  the users_special_quotas table.  This param
; will be used for a site wide administrator only when he/she doesnt
; have an entry in the users_special_quotas table.
PrivelegedUserMaxQuota=20

; Space taken by a directory (in bytes). We need this so that a user 
; cannot crash the system by creating millions of directories.  This 
; will ensure that a directory detracts from his/her quota.
DirectorySpaceRequirement=2048


NormalUserMaxQuota and PrivelegedUserMaxQuota are specified in megabytes... which means that a 5 for example, corresponds to a quota space of 5,242,880 bytes. DirectorySpaceRequirement determines the amount of bytes to detract from the user's available quota for every directory he or she creates. This is useful because directories do take up space on the filesystem and we do not want to give a user the license to harm our system by creating a few hundred thousand directories.

Users can have special quotas if they have an entry in the users_special_quotas table:

-- users have their quotas specified by [ad_parameter PrivelegedUserMaxQuota 
-- users] or [ad_parameter  NormalUserMaxQuota users]  depending on  whether 
-- they are site wide administrators or not. However, some users might  have 
-- special quotas which can be granted  by site  wide administrators.  These 
-- quotas are recorded in the users_special_quotas table. If a  user has  an 
-- entry in this table then the above mentioned parameter values are ignored 
-- and instead max_quota is used as his/her quota space.

create table users_special_quotas (
	user_id			integer primary key references users,
	max_quota		number not null,
	modification_date	date default sysdate not null
);

An entry in this table overrides the default quotas specified by the parameters. Special quotas can be assigned/removed only by site wide administrators using the users admin pages /admin/users/

This dichotomy of user quota values (special quotas in Oracle and default quotas in parameters) results in long sql queries when querying the database from Tcl files for remaining quota space for a user. For example:

select ((decode((select count(*) from
                users_special_quotas
                where user_id=$user_id),
                0, [ad_parameter [ad_decode $admin_p \
                         0 NormalUserMaxQuota \
                         1 PrivelegedUserMaxQuota \
                         NormalUserMaxQuota] users],
                (select max_quota from
                 users_special_quotas
                 where user_id=$user_id))) * power(2,20)) -
      ((select count(*) * [ad_parameter DirectorySpaceRequirement users]
        from users_files
        where directory_p='t'
        and owner_id=$user_id) +
       (select nvl(sum(file_size),0)
        from users_files
        where directory_p='f'
        and owner_id=$user_id)) as quota_left
from dual

Following is the PL/SQL code used in user quota management:

This function returns the maximum quota space available to the user. It takes in the user_id, the default quota for normal users, the default quota for abnormal users, and whether this user is abnormal or not.

create or replace function hp_user_quota_max (userid IN integer, lesser_mortal_quota IN integer, higher_mortal_quota IN integer, higher_mortal_p IN integer)
return integer
IS
	quota_max		integer;
	special_count		integer;
	return_value		integer;
BEGIN
	select count(*) into special_count
	from users_special_quotas
        where user_id=userid;

	IF special_count = 0
	THEN
		IF higher_mortal_p = 0
		THEN
			select (lesser_mortal_quota * power(2,20)) 
			into return_value
			from dual;
			return return_value;
		ELSE
			select (higher_mortal_quota * power(2,20)) 
			into return_value
			from dual;
			return return_value;
		END IF;
	ELSE
		select max_quota into quota_max
		from users_special_quotas
                where user_id=userid;
		select (quota_max * power(2,20)) 
		into return_value
		from dual;
		return return_value;
	END IF;
END;
/
show errors

create or replace function hp_user_quota_max_check_admin (userid IN integer, lesser_mortal_quota IN integer, higher_mortal_quota IN integer)
return integer
IS
	quota_max		integer;
	special_count		integer;
	return_value		integer;
	higher_mortal_p		integer;
BEGIN
	select count(*) into special_count
	from users_special_quotas
        where user_id=userid;

	select count(*) into higher_mortal_p
	from user_group_map ugm
	where ugm.user_id = userid
	and ugm.group_id = system_administrator_group_id;

	IF special_count = 0
	THEN
		IF higher_mortal_p = 0
		THEN
			select (lesser_mortal_quota * power(2,20)) 
			into return_value
			from dual;
			return return_value;
		ELSE
			select (higher_mortal_quota * power(2,20)) 
			into return_value
			from dual;
			return return_value;
		END IF;
	ELSE
		select max_quota into quota_max
		from users_special_quotas
                where user_id=userid;
		select (quota_max * power(2,20)) 
		into return_value
		from dual;
		return return_value;
	END IF;
END;
/
show errors

create or replace function hp_user_quota_used (userid IN integer, dir_requirement IN integer)
return integer
IS
	return_value		integer;
	file_space		integer;
	dir_space		integer;
BEGIN
	select (count(*) * dir_requirement) into dir_space 
        from users_files
        where directory_p='t'
        and owner_id=userid;

	select nvl(sum(file_size),0) into file_space
        from users_files
        where directory_p='f'
        and owner_id=userid;

	return_value := dir_space + file_space;

	return return_value;
END;
/
show errors

create or replace function hp_user_quota_left (userid IN integer, lesser_mortal_quota IN integer, higher_mortal_quota IN integer, higher_mortal_p IN integer, dir_requirement IN integer)
return integer
IS
	return_value		integer;
BEGIN
	select (hp_user_quota_max(userid, lesser_mortal_quota, higher_mortal_quota, higher_mortal_p) - hp_user_quota_used(userid, dir_requirement))
	into return_value
	from dual;

	return return_value;
END;
/
show errors

create or replace function hp_user_quota_left_check_admin (userid IN integer, lesser_mortal_quota IN integer, higher_mortal_quota IN integer, dir_requirement IN integer)
return integer
IS
	return_value		integer;
BEGIN
	select (hp_user_quota_max_check_admin(userid, lesser_mortal_quota, higher_mortal_quota) - hp_user_quota_used(userid, dir_requirement))
	into return_value
	from dual;

	return return_value;
END;
/
show errors

2.2     Filesystem and Access Management

; all user  web content will be rooted  under  this directory.  This
; directory will be the root of  all the web content being published
; by the users of the system.
ContentRoot=/web/acsdev/users/

We do store meta data in the users_files table:

-- We use this sequence to assign values to file_id. The 
-- reason for starting from 2 is that no file is special 
-- enough to have file_id=1, or is there a file that is?

create sequence users_file_id_seq start with 2;

create table users_files (
	file_id			integer primary key,
	-- the maximum filesize in unix is 255 characters (starting from 1)
	filename		varchar(255) not null,
	directory_p		char(1) default 'f', 
	constraint users_dir_ck check(directory_p in ('t','f')),
	file_pretty_name	varchar(500) not null,
	-- this is the number of bytes the files takes up on the file 
	-- system. We will use these values to determine quota  usage 
	-- except where directory_p is true. In that case, we'll  use 
	-- [ad_parameter DirectorySpaceRequirement users] to see  the 
	-- amount of quota space consumed by a directory. Thus, if we 
	-- magically manage to change the file system,  we dont  have 
	-- to update  file_size for  directories here  because it  is 
	-- irrelevent.
	managed_p		char(1) default 'f' check(managed_p in ('t','f')),
	-- this column is used for files created by the content
	-- publishing system which the user cannot rename or move
	modifyable_p		char(1) default 't' check(modifyable_p in ('t','f')),
	file_size		number not null,
	content_type		references users_content_types,
	-- points to the user_id of the user who owns this file.
	owner_id		integer not null references users,
	-- points to the file_id of the directory which contains 
	-- this file. Useful for supporting hierarchical content 
	-- structure.
	parent_id		integer references users_files
);

create index users_files_idx1 on users_files(file_id, parent_id);

create index users_files_idx2 on users_files(parent_id, file_id);

create index users_files_idx3 on users_files(owner_id);

-- returned value is a filename that does not begin with a slash
create or replace function hp_true_filename (filesystem_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select filename from users_files
		where file_id=filesystem_node;
	CURSOR parent_cursor IS
		select parent_id from users_files
		where file_id=filesystem_node;
	fullname	varchar(500);
	parentid	integer;
BEGIN
	OPEN parent_cursor;
	OPEN name_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH name_cursor INTO fullname;
	CLOSE parent_cursor;
	CLOSE name_cursor;
	IF parentid is null
	THEN 
		return fullname;
	ELSE
		return CONCAT(hp_true_filename(parentid), CONCAT('/',fullname));
	END IF;	
END;
/
show errors

create or replace function hp_access_denied_p (fsid IN integer, u_id IN integer)
return integer
IS
	CURSOR owner_cursor IS
		select owner_id from users_files
		where file_id=fsid;
	owner_id	integer;
BEGIN
	OPEN owner_cursor;
	FETCH owner_cursor INTO owner_id;
	CLOSE owner_cursor;
	IF owner_id = u_id
	THEN
		return 0;
	ELSE
		return 1;
	END IF;
END;
/
show errors

-- returned value is a varchar2 which is the sort key
-- Uses the fact that the full filename of each file has
-- to be unique.
create or replace function hp_filesystem_node_sortkey_gen (filesystem_node IN integer)
return varchar2
IS
	CURSOR plsql_is_stupid IS
		select filename, 
                       decode(directory_p,'t','0','1') as dp,
                       parent_id 
                from users_files
		where file_id=filesystem_node;
	fullname	varchar(500);
	parentid	integer;
	dir_p		varchar(1);
	plsql_val	plsql_is_stupid%ROWTYPE;
	discriminator	varchar(5);  -- useful for discriminating between files and directories
BEGIN
	OPEN plsql_is_stupid;
	FETCH plsql_is_stupid into plsql_val;
	dir_p := plsql_val.dp;
	fullname := plsql_val.filename;
	parentid := plsql_val.parent_id;

	IF parentid is null
	THEN 
		return CONCAT(dir_p, fullname);
	ELSE
		return CONCAT(hp_filesystem_node_sortkey_gen(parentid), CONCAT('/', CONCAT(dir_p,fullname)));
	END IF;	
END;
/
show errors

-- returns a filename beginning with a slash, unless the file is user's root
create or replace function hp_user_relative_filename (filesystem_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select filename from users_files
		where file_id=filesystem_node;
	CURSOR parent_cursor IS
		select parent_id from users_files
		where file_id=filesystem_node;
	fullname	varchar(500);
	parentid	integer;
BEGIN
	OPEN parent_cursor;
	OPEN name_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH name_cursor INTO fullname;
	CLOSE parent_cursor;
	CLOSE name_cursor;
	IF parentid is null
	THEN 
		return '';
	ELSE
		return CONCAT(hp_user_relative_filename(parentid) ,CONCAT('/',fullname));
	END IF;	
END;
/
show errors

create or replace function hp_get_filesystem_root_node (u_id IN integer)
return integer
IS
	CURSOR root_cursor IS
		select file_id from users_files
	        where filename=u_id
        	and parent_id is null
	        and owner_id=u_id;
	root_id		integer;
BEGIN
	OPEN root_cursor;
	FETCH root_cursor INTO root_id;
	CLOSE root_cursor;
	return root_id;
END;
/
show errors

create or replace function hp_get_filesystem_node_owner (fsid IN integer)
return integer
IS
	CURSOR owner_cursor IS
		select owner_id from users_files
		where file_id=fsid;
	owner_id	integer;
BEGIN
	OPEN owner_cursor;
	FETCH owner_cursor INTO owner_id;
	CLOSE owner_cursor;
	return owner_id;
END;
/
show errors

create or replace function hp_get_filesystem_child_count (fsid IN integer)
return integer
IS
	CURSOR count_cursor IS
		select count(*) from users_files
		where parent_id=fsid;
	counter		integer;
BEGIN
	OPEN count_cursor;
	FETCH count_cursor INTO counter;
	CLOSE count_cursor;
	return counter;
END;
/
show errors

create or replace function hp_fs_node_from_rel_name (rootid IN integer, rel_name IN varchar2)
return integer
IS
	slash_location		integer;
	nodeid			integer;
BEGIN
	IF rel_name is null
	THEN
		return rootid;
	ELSE
		slash_location := INSTR(rel_name,'/');
		IF slash_location = 0
		THEN
			select file_id into nodeid
			from users_files
			where parent_id=rootid
			and filename=rel_name;
			return nodeid;
		ELSIF slash_location = 1
		THEN
			return hp_fs_node_from_rel_name(rootid, SUBSTR(rel_name,2));
		ELSE
			select file_id into nodeid
			from users_files
			where parent_id=rootid
			and filename=SUBSTR(rel_name,1,slash_location-1);
			return hp_fs_node_from_rel_name(nodeid,SUBSTR(rel_name,slash_location));
		END IF;
	END IF;
END;
/
show errors

2.3     Content Management

create sequence users_type_id_seq start with 2;

create table users_content_types (
	type_id			integer primary key,
	type_name		varchar(200) not null,
	sub_type_name		varchar(200) not null,
	owner_id		integer not null references users,
	-- not used
	sub_type		integer references users_content_types,
	-- not used
	super_type		integer references users_content_types
);

create or replace function hp_top_level_content_title (filesystem_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select file_pretty_name from users_files
		where file_id=filesystem_node;
	CURSOR parent_cursor IS
		select parent_id from users_files
		where file_id=filesystem_node;
	CURSOR managed_p_cursor IS
		select managed_p from users_files
		where file_id=filesystem_node;
	managedp		varchar(1);	
	fullname		varchar(500);
	parentid		integer;
	parent_managedp 	varchar(1);
BEGIN
	OPEN parent_cursor;
	OPEN name_cursor;
	OPEN managed_p_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH name_cursor INTO fullname;
	FETCH managed_p_cursor INTO managedp;
	CLOSE parent_cursor;
	CLOSE name_cursor;
	CLOSE managed_p_cursor;
	IF parentid is null
	THEN 
		return fullname;
	END IF;
	IF managedp = 't'
	THEN
		select managed_p into parent_managedp
		from users_files
		where file_id=parentid;
		
		IF parent_managedp = 'f'
		THEN
			return fullname;
		ELSE
			return hp_top_level_content_title(parentid);
		END IF;
	ELSE
		return fullname;
	END IF;	
END;
/
show errors

create or replace function hp_top_level_content_node (filesystem_node IN integer)
return varchar2
IS
	CURSOR parent_cursor IS
		select parent_id from users_files
		where file_id=filesystem_node;
	CURSOR managed_p_cursor IS
		select managed_p from users_files
		where file_id=filesystem_node;
	managedp		varchar(1);	
	parentid		integer;
	parent_managedp 	varchar(1);
BEGIN
	OPEN parent_cursor;
	OPEN managed_p_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH managed_p_cursor INTO managedp;
	CLOSE parent_cursor;
	CLOSE managed_p_cursor;
	IF parentid is null
	THEN 
		return filesystem_node;
	END IF;
	IF managedp = 't'
	THEN
		select managed_p into parent_managedp
		from users_files
		where file_id=parentid;
		
		IF parent_managedp = 'f'
		THEN
			return filesystem_node;
		ELSE
			return hp_top_level_content_node(parentid);
		END IF;
	ELSE
		return filesystem_node;
	END IF;	
END;
/
show errors

create or replace function hp_onelevelup_content_title (filesystem_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select file_pretty_name from users_files
		where file_id=filesystem_node;
	CURSOR parent_cursor IS
		select parent_id from users_files
		where file_id=filesystem_node;
	CURSOR managed_p_cursor IS
		select managed_p from users_files
		where file_id=filesystem_node;
	CURSOR directory_p_cursor IS
		select directory_p from users_files
		where file_id=filesystem_node;
	managedp		varchar(1);	
	dirp			varchar(1);	
	parentid		integer;
	fullname		varchar(500);
BEGIN
	OPEN name_cursor;
	OPEN parent_cursor;
	OPEN managed_p_cursor;
	OPEN directory_p_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH managed_p_cursor INTO managedp;
	FETCH directory_p_cursor INTO dirp;
	FETCH name_cursor INTO fullname;
	CLOSE parent_cursor;
	CLOSE managed_p_cursor;
	CLOSE directory_p_cursor;
	CLOSE name_cursor;

	IF parentid is null
	THEN 
		return fullname;
	END IF;
	IF managedp = 't'
	THEN
		IF dirp = 't'
		THEN
			return fullname;
		ELSE
			return hp_onelevelup_content_title(parentid);
		END IF;
	ELSE
		return fullname;
	END IF;	
END;
/
show errors

create or replace function hp_onelevelup_content_node (filesystem_node IN integer)
return varchar2
IS
	CURSOR parent_cursor IS
		select parent_id from users_files
		where file_id=filesystem_node;
	CURSOR managed_p_cursor IS
		select managed_p from users_files
		where file_id=filesystem_node;
	CURSOR directory_p_cursor IS
		select directory_p from users_files
		where file_id=filesystem_node;
	managedp		varchar(1);	
	dirp			varchar(1);	
	parentid		integer;
BEGIN
	OPEN parent_cursor;
	OPEN managed_p_cursor;
	OPEN directory_p_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH managed_p_cursor INTO managedp;
	FETCH directory_p_cursor INTO dirp;
	CLOSE parent_cursor;
	CLOSE managed_p_cursor;
	CLOSE directory_p_cursor;
	IF parentid is null
	THEN 
		return filesystem_node;
	END IF;
	IF managedp = 't'
	THEN
		IF dirp = 't'
		THEN
			return filesystem_node;
		ELSE
			return hp_onelevelup_content_node(parentid);
		END IF;
	ELSE
		return filesystem_node;
	END IF;	
END;
/
show errors

2.4     Neighbourhood Management

create sequence users_neighborhood_id_seq start with 2;

create table users_neighborhoods (
	neighborhood_id		integer primary key,
	neighborhood_name	varchar(500) not null,
	description		varchar(4000),
	parent_id		integer references users_neighborhoods on delete cascade
);

-- the system is smart enough to adjust if the root neighborhood
-- has a different neighborhood_id.
insert into users_neighborhoods
(neighborhood_id, 
 neighborhood_name, 
 description, 
 parent_id)
values (1, 
 'Neighborhoods', 
 'Neighborhood RootNode', 
 null);

create or replace function hp_true_neighborhood_name (neighborhood_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select neighborhood_name from users_neighborhoods
		where neighborhood_id=neighborhood_node;
	CURSOR parent_cursor IS
		select parent_id from users_neighborhoods
		where neighborhood_id=neighborhood_node;
	fullname	varchar(500);
	parentid	integer;
BEGIN
	OPEN parent_cursor;
	OPEN name_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH name_cursor INTO fullname;
	CLOSE parent_cursor;
	CLOSE name_cursor;
	IF parentid is null
	THEN 
		return fullname;
	ELSE
		return CONCAT(hp_true_neighborhood_name(parentid), CONCAT(' : ',fullname));
	END IF;	
END;
/
show errors

create or replace function hp_get_neighborhood_root_node return integer
IS
	CURSOR root_cursor IS
		select neighborhood_id 
 		from users_neighborhoods
        	where parent_id is null;
	root_id		integer;
BEGIN
	OPEN root_cursor;
	FETCH root_cursor INTO root_id;
	CLOSE root_cursor;
	return root_id;
END;
/
show errors

create or replace function hp_relative_neighborhood_name (neighborhood_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select neighborhood_name from users_neighborhoods
		where neighborhood_id=neighborhood_node;
	CURSOR parent_cursor IS
		select parent_id from users_neighborhoods
		where neighborhood_id=neighborhood_node;
	fullname	varchar(500);
	parentid	integer;
	root_node	integer;
BEGIN
	OPEN parent_cursor;
	OPEN name_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH name_cursor INTO fullname;
	CLOSE parent_cursor;
	CLOSE name_cursor;

	select hp_get_neighborhood_root_node 
	into root_node
	from dual;

	IF neighborhood_node = root_node
	THEN
		return '';
	END IF;

	IF parentid is null
	THEN
		return '';
	END IF;

	IF parentid = root_node
	THEN 
		return fullname;
	ELSE
		return CONCAT(hp_relative_neighborhood_name(parentid), CONCAT(' : ',fullname));
	END IF;	
END;
/
show errors

-- generates a sort key for this neighbourhood. Can be used in 'connect by'
-- with 'order by'.
create or replace function hp_neighborhood_sortkey_gen (neighborhood_node IN integer)
return varchar2
IS
	CURSOR name_cursor IS
		select neighborhood_name from users_neighborhoods
		where neighborhood_id=neighborhood_node;
	CURSOR parent_cursor IS
		select parent_id from users_neighborhoods
		where neighborhood_id=neighborhood_node;
	fullname	varchar(500);
	parentid	integer;
BEGIN
	OPEN parent_cursor;
	OPEN name_cursor;
	FETCH parent_cursor INTO parentid;
	FETCH name_cursor INTO fullname;
	CLOSE parent_cursor;
	CLOSE name_cursor;
	IF parentid is null
	THEN 
		return '/';
	ELSE
		return CONCAT(hp_neighborhood_sortkey_gen(parentid), CONCAT('/',fullname));
	END IF;	
END;
/
show errors

create or replace function hp_get_nh_child_count (neighborhoodid IN integer)
return integer
IS
	CURSOR count_cursor IS
		select count(*) from users_neighborhoods
		where parent_id=neighborhoodid;
	counter		integer;
BEGIN
	OPEN count_cursor;
	FETCH count_cursor INTO counter;
	CLOSE count_cursor;
	return counter;
END;
/
show errors

create or replace function hp_neighborhood_in_subtree_p (source_node IN integer, target_node IN integer)
return varchar2
IS
	CURSOR parent_cursor IS
		select parent_id from users_neighborhoods
		where neighborhood_id=target_node;
	parentid	integer;
BEGIN
	OPEN parent_cursor;
	FETCH parent_cursor INTO parentid;
	CLOSE parent_cursor;
	
	IF source_node = target_node
	THEN
		return 't';
	END IF;
	
	IF parentid is null
	THEN 
		return 'f';
	ELSE
		IF parentid = source_node
		THEN
			return 't';
		ELSE
			return hp_neighborhood_in_subtree_p(source_node, parentid);
		END IF;
	END IF;	
END;
/
show errors

2.5     Putting it all together

create table users_homepages (
	user_id				primary key references users,
	-- the background colour settings for user's public pages
	bgcolor	 			varchar(40),
	-- the text colour settings for user's public pages
	textcolor			varchar(40),
	-- the colour settings for unvisitied links in user's public pages
	unvisited_link	  		varchar(40),
	-- the colour settings for visitied links in user's public pages
	visited_link	  		varchar(40),
	-- the settings to determine whether the links are underlined or
	-- not in user's public pages
	link_text_decoration  		varchar(40),
	-- the settings to determine whether the links are bold or
	-- not in user's public pages. I have added this because I have
	-- strong preference for bold links when they are not underlined.
	link_font_weight		varchar(40),
	-- font for user's public generated pages
	font_type		  	varchar(40),
	-- the background colour settings for user's maintenance pages
	maint_bgcolor	 		varchar(40),
	maint_textcolor			varchar(40),
	maint_unvisited_link		varchar(40),
	maint_visited_link	  	varchar(40),
	maint_link_text_decoration  	varchar(40),
	maint_link_font_weight		varchar(40),
	maint_font_type			varchar(40),
	neighborhood_id		        integer references users_neighborhoods on delete set null
	-- feature_level		varchar(30),
	-- constraint hp_feature_lvl_ck check(feature_level 'platinum', 'gold', 'silver'),
	-- keywords			varchar(4000)

);

2.6     Features hiterto unsung

2.6.1     File Access Log

We log accesses to users files in the users_files_access_logs table:

create sequence users_access_id_sequence start with 2;

create table users_files_access_log (
	access_id		integer primary key,
	file_id			references users_files on delete set null,
	relative_filename	varchar(500) not null,
	owner_id		references users on delete cascade,
	access_date		date not null,
	ip_address		varchar(50) not null
);