Using

pg_dump -U projop -E UTF8 -d projop > projop.dump

instead of the usual

pg_dump --no-owner --clean --disable-dollar-quoting --format=p --file projop.dump

I was able to to restore the db.

Restaurants

Classics

http://www.loscaracoles.es/
Barcelona classic, nice ‘ambiente’, very touristic, prices maybe a bit to high for what they offer, but you get the whole spanish experience, crossing the kitchen when entering the restaurant, always packed with people, hams hanging from the ceiling.

Cervecería Catalana
My favorite tapa place

Senyor Parellada
http://www.senyorparellada.com/
Classic in the born area

“Elegancia”

http://www.rte-olive.com/
Take your beloved one to this on her birthday

http://www.hotelcasafuster.com/restaurante.asp
Take your beloved one to this in case you want to propose - Woddy Allen played the saxophon during his stays in the bar

http://www.ladama-restaurant.com/
Back to the good old days, upper price segment.
(… you have one of the best TROBADORS right across the street)

Modern

http://www.w-barcelona.com/
impressive !!!

Hesperia_Tower-Barcelona_Catalonia
http://www.tripadvisor.es/Hotel_Review-g187497-d630596-Reviews-Hesperia_Tower-Barcelona_Catalonia.html
Michelin * (or ** ??)

Pretty Cool

http://www.laparadeta.com/
make your order at the entrance - all “fish” - big portions - student restaurant

Great value - nice ambiente

www.restaurantetrobador.com
Always a great value for the money and a nice and modern atmosphere, many locations city wide

www.flamantrestaurant.com

www.restaurantbalthazar.com

Feelin’ fishy?

http://www.bcnrestaurantes.com/eng/barcelona.asp?restaurante=carballeria
close to port

http://www.botafumeiro.es/
probably one of the best fish restaurants in BCN - try the percebes !

Sea Views

http://www.canmajo.es/ (views only from terrace)
http://www.aguadeltragaluz.com/

Takin’ a coffee in downtown

www.4gats.com
http://www.bcnrestaurantes.com/barcelona.asp?restaurante=el-gran-cafe

Rustic - outside BCN

www.can-borell.com

Day Trips

Torres
Wine tasting outside of BCN

Freixenet
Cava - nice tour

http://www.barcelona-tourist-guide.com/en/tour/montserrat-spain.html - ca. 50km ausserhalb

This entry moved to http://www.project-open.org/documentation/rfe_Advanced_Financial_Project_Planning

http://stackoverflow.com/questions/1973/what-is-the-best-way-to-avoid-sql-injection-attacks
Golden Rules:

3: Custom-Written Value Sanitation. Avoid writing your own sanitation routines as much as possible except when it’s absolutely the only option remaining (which is very unlikely in any modern language). Input sanitation is a hard problem, and the costs of getting it wrong are huge. It’s best to leave that job to someone else. When you are forced to rely on this method use white-listing rather than black-listing to sanitize input.

2: Framework / Library Based Value Sanitation. Leave the sanitation routines to the domain experts. Sanitation routines in 1st party frameworks and libraries are typically created by the same folks that wrote the DB or the SQL API. They have a much higher chance of knowing, and properly handling, all of the edge cases than you do. An example of this technique would be using php’s mysql_escape_string() function to sanitize values that will be inserted into strings that serve as dynamic SQL statements.

1: Parameter Binding. Instead of constructing a SQL statement as a raw string which includes user data as in-place literal values, create a SQL statement with tokens where the user data would be. Then bind the user supplied data to the appropriate parameters. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement. This can be used in conjunction with library based input sanitation as well.

http://www.tcl.tk/community/tcl2004/Tcl2003papers/cleverly.htm

Luckily for the Tcl programmer who craves laziness, OpenACS provides a higher-level database API on top of ns_db that hides all the mundane house-keeping chores. Additionaly, bind-variable emulation is available which frees the programmer from worrying about always escaping apostrophes in SQL statements. Bind variables are prefaced with a colon.

 set name "Jim O'Connor"
            set salary 40000

            db_dml new_hire {
                insert into employees (name, salary, dept)
                values (:name, :salary, 'Accounting')
            }

http://openacs.com/forums/message-view?message_id=250625
Posted by Dave Bauer on 01/19/05 04:57 PM

In Postgresql the db_* commands extract the “bind” variables and SQL quote them automatically. In the PG driver itself the [] charactaers are escaped as well for safety.

This means that the effective security of PostgreSQL is the same as Oracle.

This is done at the database api layer.

In addition ad_page_contract has filters and validation features to check input. ad_form and the form builder also have validation features for input.

xowiki pages are managed with the OpenACS Content repository.

XOWIKI pages:

For each page there’s an entry in cr_items

projop=# \d cr_items
                                   Table "public.cr_items"
      Column       |          Type          |                   Modifiers
-------------------+------------------------+------------------------------------------------
 item_id           | integer                | not null
 parent_id         | integer                | not null
 name              | character varying(400) | not null
 locale            | character varying(4)   |
 live_revision     | integer                |
 latest_revision   | integer                |
 publish_status    | character varying(40)  |
 content_type      | character varying(100) |
 storage_type      | character varying(10)  | not null default 'text'::character varying
 storage_area_key  | character varying(100) | not null default 'CR_FILES'::character varying
 tree_sortkey      | bit varying            | not null
 max_child_sortkey | bit varying            |

Page content

Page content is stored in table cr_revisions

projop=# \d cr_revisions
                             Table "public.cr_revisions"
     Column     |           Type           |                Modifiers
----------------+--------------------------+-----------------------------------------
 revision_id    | integer                  | not null
 item_id        | integer                  | not null
 title          | character varying(1000)  |
 description    | text                     |
 publish_date   | timestamp with time zone |
 mime_type      | character varying(200)   | default 'text/plain'::character varying
 nls_language   | character varying(50)    |
 lob            | integer                  |
 content        | text                     |
 content_length | integer                  |

Finding content that can’t be found using ’search’ such as links etc.

select
		r.item_id,
		r.title,
		i.name
from
		cr_revisions r,
		cr_items i
where
		r.content like '%en:install_main%' and
		r.revision_id = i.live_revision;

Object Model

Pls. see: http://openacs.org/xotcl/show-object?show_source=0&object=%3A%3Axowiki%3A%3APage&show_methods=1

The package provides the following features:

• Provide a single access point for all tasks assigned to an user
• Gives users the possibility to create a sortable list of tasks
• Tasks can added from a list of existing project tasks, forum tasks and workflow tasks
• Let users add arbitrary tasks not managed with ]po[
• Provide users with quick access to often used features such as time sheet tracking and relevant task data.
• Speeds up adminstrative tasks and provides convenience to users working in multitasking environments, such as Help-Desk, Call-Center, Support, etc.

Principal architectural objectives:

• Implementing a RESTful Web Service
• Evaluate Open Source JS libraries for use in ]po[
• Security aspects and error handling when using xmlhttp requests (XHR)

Protoytype

To-Do’s

• Fix Bug: time - logging
• Show err mess when no tasks have been found for project
• Allow to delete tasks from To-Do list by drag&drop them to wastebasket
• Add WF tab (shows all tasks resulting from WF instances)
• Add Forum tab (shows all FORUM tasks)

Developer Documentation

                Table "public.im_gtd_tasks"
       Column       |           Type           | Modifiers
--------------------+--------------------------+-----------
 gtd_task_id        | integer                  | not null
 ref_id             | integer                  | not null
 owner_id           | integer                  |
 created_date       | timestamp with time zone |
 description        | text                     |
 last_modified      | date                     |
 gtd_type_id        | integer                  | not null
 gtd_task_status_id | integer                  | not null
Indexes:
    "im_gtd_tasks_pkey" PRIMARY KEY, btree (gtd_task_id)
Foreign-key constraints:
    "im_gtd_task_type_fk" FOREIGN KEY (gtd_type_id) REFERENCES im_categories(category_id)
    "im_gtd_task_type_status_fk" FOREIGN KEY (gtd_task_status_id) REFERENCES im_categories(category_id)
    "im_gtd_tasks_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES users(user_id)

Content

• General Information
• Tables and views used
• Time sheet Portlets
• Sample Queries

General information

When time sheet form (/intranet-timesheet2/hours/new) is send to the server,

• … ]po[ deletes affected time sheet entries for affected day and user and creates new ones
• … ]po[ updates im_projects::reported_hours_cache

Please note that

• … Paramaters have in impact on values shown in time sheet plugins. In case there’s mismath regarding hours reported, check parameter SyncHoursInterval and SyncHoursP
• If hours don’t sum up correctly on the time sheet component in a sub project, there might be hours logged on the main project that are not considered.

fullquery name: update_timesheet_task

        update im_projects
        set reported_hours_cache = (
                select  sum(h.hours)
                from    im_hours h
                where   h.project_id = :project_id
        )
        where project_id = :project_id

fullquery name: delete_timesheet_costs

      DECLARE
         row RECORD;
      BEGIN
         for row in
                select  cost_id
                from    im_costs
                where   cost_type_id = [im_cost_type_timesheet]
                        and project_id = :project_id
                        and cause_object_id = :user_id_from_search
                        and effective_date = to_date(:julian_date, 'J')
         loop
                PERFORM im_cost__delete(row.cost_id);
         end loop;
         return 0;
      END;

Tables used

• im_timesheet_tasks
• im_hours
• im_costs

im_timesheet_tasks

• Contains all timesheet tasks

projop=# \d im_timesheet_tasks
       Table "public.im_timesheet_tasks"
     Column     |       Type       | Modifiers
----------------+------------------+-----------
 task_id        | integer          | not null
 material_id    | integer          | not null
 uom_id         | integer          | not null
 planned_units  | double precision |
 billable_units | double precision |
 cost_center_id | integer          |
 invoice_id     | integer          |
 priority       | integer          |
 sort_order     | integer          |
 bt_bug_id      | integer          |
Indexes:
    "im_timesheet_tasks_pk" PRIMARY KEY, btree (task_id)
Foreign-key constraints:
    "im_times_tasks_bt_bug_fk" FOREIGN KEY (bt_bug_id) REFERENCES bt_bugs(bug_id)
    "im_timesheet_task_fk" FOREIGN KEY (task_id) REFERENCES im_projects(project_id)
    "im_timesheet_tasks_cost_center_fk" FOREIGN KEY (cost_center_id) REFERENCES im_cost_centers(cost_center_id)
    "im_timesheet_tasks_invoice_fk" FOREIGN KEY (invoice_id) REFERENCES im_invoices(invoice_id)
    "im_timesheet_tasks_material_fk" FOREIGN KEY (material_id) REFERENCES im_materials(material_id)
    "im_timesheet_tasks_uom_fk" FOREIGN KEY (uom_id) REFERENCES im_categories(category_id)

im_costs

A cost item will be created for each time sheet entry

                    Table "public.im_costs"
         Column         |           Type           | Modifiers
------------------------+--------------------------+-----------
 cost_id                | integer                  | not null
 cost_name              | character varying(400)   | not null
 cost_nr                | character varying(400)   | not null
 project_id             | integer                  |
 customer_id            | integer                  | not null
 cost_center_id         | integer                  |
 provider_id            | integer                  | not null
 investment_id          | integer                  |
 cost_status_id         | integer                  | not null
 cost_type_id           | integer                  | not null
 cause_object_id        | integer                  |
 template_id            | integer                  |
 effective_date         | timestamp with time zone |
 start_block            | timestamp with time zone |
 payment_days           | integer                  |
 amount                 | numeric(12,3)            |
 currency               | character(3)             |
 paid_amount            | numeric(12,3)            |
 paid_currency          | character(3)             |
 vat                    | numeric(12,5)            |
 tax                    | numeric(12,5)            |
 variable_cost_p        | character(1)             |
 needs_redistribution_p | character(1)             |
 parent_id              | integer                  |
 redistributed_p        | character(1)             |
 planning_p             | character(1)             |
 planning_type_id       | integer                  |
 description            | character varying(4000)  |
 note                   | character varying(4000)  |
 last_modified          | timestamp with time zone |
 last_modifying_user    | integer                  |
 last_modifying_ip      | character varying(20)    |
Indexes:
    "im_costs_pk" PRIMARY KEY, btree (cost_id)
    "im_costs_cause_object_idx" btree (cause_object_id)
    "im_costs_start_block_idx" btree (start_block)
Check constraints:
    "im_costs_needs_redist_ck" CHECK (needs_redistribution_p = 't'::bpchar OR needs_redistribution_p = 'f'::bpchar)
    "im_costs_planning_ck" CHECK (planning_p = 't'::bpchar OR planning_p = 'f'::bpchar)
    "im_costs_redist_ck" CHECK (redistributed_p = 't'::bpchar OR redistributed_p = 'f'::bpchar)
    "im_costs_var_ck" CHECK (variable_cost_p = 't'::bpchar OR variable_cost_p = 'f'::bpchar)
Foreign-key constraints:
    "im_cost_template_fk" FOREIGN KEY (template_id) REFERENCES im_categories(category_id)
    "im_costs_cause_fk" FOREIGN KEY (cause_object_id) REFERENCES acs_objects(object_id)
    "im_costs_cost_center_fk" FOREIGN KEY (cost_center_id) REFERENCES im_cost_centers(cost_center_id)
    "im_costs_cost_fk" FOREIGN KEY (cost_id) REFERENCES acs_objects(object_id)
    "im_costs_currency_fk" FOREIGN KEY (currency) REFERENCES currency_codes(iso)
    "im_costs_customer_fk" FOREIGN KEY (customer_id) REFERENCES acs_objects(object_id)
    "im_costs_inv_fk" FOREIGN KEY (investment_id) REFERENCES acs_objects(object_id)
    "im_costs_last_mod_user" FOREIGN KEY (last_modifying_user) REFERENCES users(user_id)
    "im_costs_paid_currency_fk" FOREIGN KEY (paid_currency) REFERENCES currency_codes(iso)
    "im_costs_parent_fk" FOREIGN KEY (parent_id) REFERENCES im_costs(cost_id)
    "im_costs_planning_type_fk" FOREIGN KEY (planning_type_id) REFERENCES im_categories(category_id)
    "im_costs_provider_fk" FOREIGN KEY (provider_id) REFERENCES acs_objects(object_id)
    "im_costs_status_fk" FOREIGN KEY (cost_status_id) REFERENCES im_categories(category_id)
    "im_costs_type_fk" FOREIGN KEY (cost_type_id) REFERENCES im_categories(category_id)
Triggers:
    im_costs_project_cache_del_tr AFTER DELETE ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_del_tr()
    im_costs_project_cache_ins_tr AFTER INSERT ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_ins_tr()
    im_costs_project_cache_up_tr AFTER UPDATE ON im_costs FOR EACH ROW EXECUTE PROCEDURE im_cost_project_cache_up_tr()

Views used by ]po[ Time Sheet Management

• im_timesheet_tasks_view

projop=# \d  im_timesheet_tasks_view
            View "public.im_timesheet_tasks_view"
        Column        |           Type           | Modifiers
----------------------+--------------------------+-----------
 task_id              | integer                  |
 material_id          | integer                  |
 uom_id               | integer                  |
 planned_units        | double precision         |
 billable_units       | double precision         |
 cost_center_id       | integer                  |
 invoice_id           | integer                  |
 priority             | integer                  |
 sort_order           | integer                  |
 project_id           | integer                  |
 task_name            | character varying(1000)  |
 task_nr              | character varying(100)   |
 percent_completed    | double precision         |
 task_type_id         | integer                  |
 task_status_id       | integer                  |
 start_date           | timestamp with time zone |
 end_date             | timestamp with time zone |
 reported_hours_cache | double precision         |
 reported_units_cache | double precision         |
View definition:
 SELECT t.task_id, t.material_id, t.uom_id, t.planned_units, t.billable_units, t.cost_center_id, t.invoice_id, t.priority, t.sort_order, p.parent_id AS project_id, p.project_name AS task_name, p.project_nr AS task_nr, p.percent_completed, p.project_type_id AS task_type_id, p.project_status_id AS task_status_id, p.start_date, p.end_date, p.reported_hours_cache, p.reported_hours_cache AS reported_units_cache
   FROM im_projects p, im_timesheet_tasks t
  WHERE t.task_id = p.project_id;

Time sheet Portlets

Timesheet Tasks

Facts about the Time sheet Component:

• In order to speed up page creation, hours are taken from im_projects::reported_hours_cache

Samples

Sample

User (user_id:28358) with profile “Sales” (group_id:473) can’t see Employees:

Principal routine to check permissions

create function acs_permission__permission_p(int4,int4,varchar) returns bool as '
         declare
             permission_p__object_id           alias for $1;
             permission_p__party_id            alias for $2;
             permission_p__privilege           alias for $3;
             exists_p                          boolean;
         begin
           return exists (select 1
                          from
					acs_permissions p,
					party_approved_member_map m,
                            	acs_object_context_index c,
					acs_privilege_descendant_map h
                         where
					p.object_id = c.ancestor_id
                           	and h.descendant = permission_p__privilege
                           	and c.object_id = permission_p__object_id
                           	and m.member_id = permission_p__party_id
                           	and p.privilege = h.privilege
                           	and p.grantee_id = m.party_id);
        end;' language 'plpgsql';

Check pre-condition:

a) Check existence of privilige “View users”
b) Check User Matrix: Sale should be allowed to see Employees

Digging in’

a) Check permission:

select im_object_permission_p(463, 28358, ‘view_users’);
returns ‘f’

b) Check if member is (approved) member of “Sales”

select
	*
from
	group_approved_member_map
where
	member_id = 28358 and
	group_id = 473;

 group_id | member_id | rel_id | container_id |    rel_type
----------+-----------+--------+--------------+----------------
      473 |     28358 |  35900 |          473 | membership_rel
(1 row)

c) Check for corresponding entry in “party_approved_member_map”

select
		m.*
from
		party_approved_member_map m
where
		m.member_id = 28358;

 party_id | member_id |  tag
----------+-----------+-------
      473 |     28358 | 35900
       -2 |     28358 | 35900
      438 |     28358 | 35900
    28358 |     28358 |     0
       -2 |     28358 | 28359
      438 |     28358 | 28359
       -1 |     28358 | 28359
      463 |     28358 | 28361
       -2 |     28358 | 28361
      438 |     28358 | 28361
       -1 |     28358 | 28361
       -1 |     28358 | 35900
(12 rows)

d) Check acs_privilege_descendant_map

select
		m.*
from 

		acs_permissions p,
		party_approved_member_map m
where
		m.member_id = 28358 and
		m.oid = 463;	

tbc.