Alberto Serra Born on March 1st, 1960 Located in: Kiev, Ukraine ...

seasoningalluringΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

358 εμφανίσεις

A
lberto Serra

Born on March 1st, 1960

Located in: Kiev, Ukraine

Email:
berto.d.sera@gmail.com

Skype: berto.d.sera

Google+ Bèrto ëd Sèra

Citizenship: European Union (Italy)

Married, has one daughter.



About me


In terms of basic skills I am:



apt to work alone or in a team;



able to build and train an
ad
-
hoc

team

from scratch by selecting and training its
components;



trained to listen to co
-
workers and internal customers, but aware that I bear responsibility for

my own decisions;



reacting to emergencies by keeping
emotions at bay
, while deploying an added amount of
care and focus;



making
consistent
, well
normalized
,
performance
-
oriented

and
robust

data and code
designs;



provided with
solid experience

that many co
mpanies can use (29+ years in the IT, 13+ in
relational DBMS);



capable of effective customer/stakeholder relations
, as both education and practical
experience taught me that organizations may have unexpressed needs and internal power
balance that must be
taken into account on delivering a successful project;



fluently communicating

in a number of languages;



experienced in managing and creating software
solutions for multi
-
cultural environments
;



easily adapting

to foreign cultures, life
-
styles and languages;



I have a
strong vision

of a DB as a "data+code" object, in which an API can be defined, thus
allowing flexibility (and I have over
13 years of experience with data modelling, triggers and
stored procedures
) to any number of third parties. I can manage d
ata migrations among different
tools and I can often use a
migration/update as a chance to improve

optimization and
performance.


I've now been managing databases that require 24/7~100% uptime for a number of years. I am
ready to learn new technologies if

the size of the assignment makes it economically interesting.


Relocating is fine
, most of my family needs are related to my daughter's instruction (preferably in
English).


Salary target
: depending on location's cost of living and relocation cost.






Technological snapshot


RDMS Technology



Postgresql 8 and 9,



Mysql 5.0 and 5.1 (mostly wit innoDb),



Oracle 7, 8 and 10g (Forms and Report experience with 6i),



Sybase,



PostGis (install/admin/data import),



XML processing and generation in Stored Procedures,



Data migrations and exchange among different DB technologies


Data Modelling



UML modelling,



Oracle Designer



MySQL workbench



SQL Power Architect


Semantic web



soprano



virtuoso



Protégé (ontology designer)


Unix



Linux (mostly Gentoo, Gentoo Hardened and Sa
bayon),



Scripting (mostly bash),



Bind




Apache

(with ssh)



Nginx (with ssh)



PECL
-
APC



Zabbix



Netqmail/Vpopmail/Dovecot/Maildrop/Ezmlm/Spamassassin



Mercurial (and various other reps, SVN, Git, etc)


Programming Languages and frameworks



GNU
-
Smalltalk,



Python,



PHP 5 (with objects),



Ruby on Rails,



C++/Qt 4 (basic level),



Iliad framework,



Hobo


CMSs



Drupal (5/6/7),



Moodle (1/2)




Language Skills


Fluent in
:



Piedmontese (native speaker)



Italian



English



Russian

Can communicate
also in:



French



Catalan



Spanish

Can re
ad
:



Dutch (basic)


Education


1983
-

1984

Communication Theory training

at a private Company. The training was aimed at effective
communication, human resources management, group dynamics and customer relations.


1979
-

1983

Faculty of Economics
, Turin,

Italy

Some College Coursework Completed. I interrupted the process because I had already started my
own software house and I was working 14 hours a day x 7 days a week.



Professional experience


Data Architect/DBA Consultant

2005
-

current. Vox Humanitat
is, Alba, Italy


Data Architect

for an Open Source
distributed textual repository

prepared in co
-
operation with
the University of Bamberg:



Textual and multimedia objects mapping to
PostgreSQL 9
;



Full RDBMS technology encapsulation behind a
REST API
:



Clien
ts are not aware they are querying a database;



XML

based data exchange;



Stored Procedures

assemble/disassemble XML from/onto relational tables;



Constraints

and
trigger level business rules

ensure consistency and allow command
line operations for maintenanc
e;



Hierarchical data layerin
g to:



map the circular nature of human language onto non
-
cyclic structures;



build a fully language agnostic semantic annotation layer;



enable retrieval
-
on
-
demand from remote nodes in the distributed repository;



Graph and Tree st
ructures

mapping:



Trees → data origin (what is translated from what) for translation QA;



Directed and undirected graphs → semantic attribution (classes and relations);



User defined ontologies:



A semantic class behaves like an “SQL table”,



It can have any

number of attributes (with specialized data types);



Users without any SQL knowledge can define the ontology;



Attributes of the ontology can be queried;



Horizontal data partitioning

based on language (child tables with specialized stemmers);



Designed for
l
arge volumes
:



Full ISO 639
-
3 language set (7,7K+ languages);



Terabytes of textual data;



Terabytes of multimedia data;



Metadata



geotagging (
PostGIS

based)



Some experimental work on
RDF and semantic repositories

(soprano+virtuoso);



P2P architecture

(to be im
plemented as a last step):



Any number of nodes can offer storage for an arbitrary subset of the global content;



Each node can disconnect from the P2P network and get back in sync when possible
(laptops, poorly connected areas, etc.);



If no connectivity is
available syncing can be achieved by other media (DVD etc.);



System administrator and DBA


PostgreSQL:



Backup and recovery (using OS scripts, RSYNC)



High availability (currently under development with WAL forwarding for large nodes)



Database level perfo
rmance tuning



Index maintenance



PostgreSQL installation and patching (from 8.3 to 9.0.4)



Database creation, patching and migration



psql management (over ssh TCP/IP, with signed keys for better security)



Security configuration (access from Unix socket only
, and/or TCP/IP)



Replication (snapshots/materialized views)



Bulk loads and data migrations (a number of databases are being ported to postgreSQL from
MySQL)


MySQL
:



Backup and recovery (using OS scripts, RSYNC)



Database level performance and robustness tun
ing



MySQL installation and patching (up to 5.1.56)



Database creation, patching and migration



Disaster recovery (using binlogs)



mysql management (over ssh TCP/IP, with signed keys for better security)



Security configuration (access from Unix socket only, a
nd/or TCP/IP)



Bulk loads and data migrations (mostly outgoing migrations)


Server
:



Gentoo hardened
server;



Bind
9.7.3

Apache

2.2.17 (with ssh)
PHP

5.3.6
PECL
-
APC

3.1.9;



PostgreSQL

9.04 and
MySQL

5.1.56;



20 active
Drupal

based sites (D6 and D7), 3
Moodle

in
stalls;



Traffic
~1.5mln hits/day
;



Netqmail/Vpopmail/Dovecot/Maildrop/Ezmlm/Spamassassin





Data Architect/DBA and Outsorcing Manager

2001
-

2004 Smileline srl, Moncalieri, Italy


OLTP environment
. The company aimed to be a totally on
-
line business, includ
ing:

1.

E
-
commerce

public interface (with all the usual gear: carts, payment, delivery tracking);

2.

Private administrative interface for stock management, invoicing, statistics, etc.;

3.

Online tools for retailers and sub
-
distributors;

4.

Database on
MySQL

(innoDB);

5.

LAMP
environment with
OO
-
PHP.



Roadmap:



Telecommuting tools

for the administrative staff;



Online catalogue, used and demonstrated to retailers by sales agents;



First direct
on
-
line orders
by mid 2002 (retailers were slow in accepting the loss of direct
human
interaction with sales agents
);



During the following years we concentrated on:



Better user experience for the on
-
line shop;



Flow automation, to cut a number of administrative tasks;



Improving our knowledge about customers' seasonal behaviour.


MySQL



Hosted on a Debian machine



OLTP only (with some preparatory work to generate a DW later on);



Backup and recovery (using OS scripts, FTP)



Database level performance and robustness tuning



MySQL installation and patching (up to 4.1)



Database creation, patch
ing and migration



mysql management (over ssh TCP/IP)



Security configuration (TCP/IP)



Bulk loads and data migrations (mostly regularly incoming loads from external companies, like
catalogues etc.)

Data Architect



The entire project was developed from scratch
, I had total responsibility initially as a Data Architect,
eventually evolving into a DBA only role.

Outsourcing manager



Choice and co
-
ordination of foreign (Ukraine based) contractors


Oracle Consultant

1998
-

2001 Techne srl, Turin, Italy


Support of th
ird parties, including



SIPRA

(biggest advertising industry in Italy at the time ) new ERP subsystem managing
selling strategies for the advertising flow on 6 national TV channels, the entire national
network of movie theatres and some newspapers and magaz
ines.

Details:



Oracle 8i

with
Forms and Report 6i
;



Shared template for Forms based GUI (most of which of my design);



Massive data duplication from Oracle 7 to 8.
DB triggers

ensured consistency between
the two parallel systems, as invoicing remained on O7
;



Optimization on aggregate functions for very large data sets. Cron based
stored
procedures

to prepare snapshots used to accelerate performance.



Frejus tunnel

(one of the two tunnels connecting Italy and France).

The job included:



Oracle 7

disaster reco
very on an unsupported VAX legacy machine;



Reverse engineering of legacy tables and procedures that lacked documentation;



Mapping existing de
-
normalizations to assess the related risk for data consistency;



Data Architect

for the proof of concept of an aut
omated surveillance system for a
network of Italian motorways (
Oracle 8i

based). The system:



Connected a network of traffic sensors and human controllers;



Allowed communication with drivers by means of electronic billboards and SMSs;



Had a PHP based web
interface administrative interface.



Internal education

for young specialists with no previous work experience.


IT Director

1995
-

1998 MAT srl, Venice, Italy

The company designed and produced
automated warehousing systems

for metal bars and
coils. Applic
ations ranged from robot motions to personnel security and warehouse
administration. Most of the work based on
Smalltalk

(VisualWorks), some use of
ODBMSes.


Smalltalk proved very good automation
-
wise, but was far from being performant as an
administrative

tool (back then Pentium I servers were the cutting edge).


Consultant

1992
-

1995 ELEA spa, Ivrea, Italy

Technology teacher

for the Olivetti spa sales structure. Travelled through all Italy to ensure
the sales network was ready to use/sell internet and
windows based technology.


Co
-
owner

1981
-

1992 BA.SE., Biella, Italy

Early PC period. Applications ranging from machine code systems for radio advertising
automation, to administrative systems (Pascal and file system repositories). In this last area
we m
ainly worked for Ermenegildo Zegna SpA, for which we designed an integrated system
managing the whole franchising network on planet wide scale.


Code Examples


Some examples of actual code are added for your consideration.


UML Design example (SQL Power A
rchitect)





Scripts for general database creation


/*


# Copyright (c) 2010 Berto `d Sera for Vox Humanitatis


# X11 license (often called MIT)


#
----------------------------------------------------------------



# Permission is hereby granted,

free of charge, to any person


# obtaining a copy of this software and associated documentation


# files (the "Software"), to deal in the Software without


# restriction, including without limitation the rights to use,


# copy, modify, merge, publ
ish, distribute, sublicense, and/or sell


# copies of the Software, and to permit persons to whom the


# Software is furnished to do so, subject to the following


# conditions:




# The above copyright notice and this permission notice shall be


# included in all copies or substantial portions of the Software.




# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,


# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES


# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND


# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT


# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,


# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING


# FROM, OUT OF OR IN CONNECTION WITH THE SOF
TWARE OR THE USE OR


# OTHER DEALINGS IN THE SOFTWARE.


#
----------------------------------------------------------------



# Version 0.7.0


#
----------------------------------------------------------------
`


# The following script is responsibl
e for DB creation. Please use the


# following structure and naming convention upon expanding the code:


# Each schema is expected to have


# 1) a declarative section


# 2) a code creation section


# 3) a constraint enforcement section


# Any of

them can actually be empty, but if you add a schema they are


# expected to be present, so that actual routines will be added if and


# when they become needed.


#
--------------------------------------------------------------------

*/


DROP ROLE amba
radmin;

CREATE ROLE ambaradmin WITH SUPERUSER LOGIN PASSWORD 'BJ55uzHs';

DROP ROLE ambaradan;

CREATE ROLE ambaradan PASSWORD 'HGH8gjjj';


DROP DATABASE ambaradan;

DROP DATABASE imports;

DROP TABLESPACE ambaradan;

CREATE TABLESPACE ambaradan OWNER ambaradmi
n LOCATION '/home/ambaradan/postgresql';

CREATE DATABASE ambaradan WITH OWNER=ambaradmin ENCODING='UTF8' TABLESPACE=ambaradan;


\
connect ambaradan;

create language plpgsql;

\
i /usr/share/postgresql
-
9.0/contrib/uuid
-
ossp.sql;

\
i /usr/share/postgresql
-
9.0/co
ntrib/hstore.sql; /* Deprecated */

\
i /usr/share/postgresql
-
9.0/contrib/isn.sql;


/* Schemas creation */

CREATE SCHEMA api AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA api IS 'Public API
-

this is all the ambaradan role can access';


CREATE SCHEMA config AU
THORIZATION ambaradmin;

COMMENT ON SCHEMA config IS 'Configuration data and routines';


CREATE SCHEMA metadata AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA metadata IS 'Linguistic independant data container';


CREATE SCHEMA data AUTHORIZATION ambaradmin;

CO
MMENT ON SCHEMA data IS 'Linguistic dependant data container (text and multimedia)';


CREATE SCHEMA network AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA network IS 'Network data and management';


CREATE SCHEMA semantics AUTHORIZATION ambaradmin;

COMMENT ON
SCHEMA semantics IS 'Classification';


CREATE SCHEMA template AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA template IS 'The template engine';


CREATE SCHEMA tree AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA tree IS 'Graph data manager';


CREATE SCHEMA histor
y AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA history IS 'Data archive';


CREATE SCHEMA xml AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA xml IS 'Object rendering in XML format';


CREATE SCHEMA exp_KDE AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA exp_KDE IS '
Exports for KDE';


CREATE SCHEMA community AUTHORIZATION ambaradmin;

COMMENT ON SCHEMA community IS 'Community and user management';


SET statement_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET escape_string_warning =

on;

SET check_function_bodies = true;

SET client_min_messages = warning;


/* Declarative section */

\
i database/config/sch_config.sql;

\
i database/metadata/sch_metadata.sql;

\
i database/semantics/sch_semantics.sql;

\
i database/data/sch_data.sql;

\
i databa
se/network/sch_network.sql;

\
i database/tree/sch_tree.sql;

\
i database/history/sch_history.sql;

\
i database/community/sch_community.sql;

\
i database/template/sch_template.sql;


/* Code section */

\
i database/config/sch_config_code.sql;

\
i database/metadata
/sch_metadata_code.sql;

\
i database/semantics/sch_semantics_code.sql;

\
i database/data/sch_data_code.sql;

\
i database/network/sch_network_code.sql;

\
i database/tree/sch_tree_code.sql;

\
i database/history/sch_history_code.sql;

\
i database/community/sch_comm
unity_code.sql;

\
i database/template/sch_template_code.sql;


/* Constraints section */

\
i database/config/sch_config_constraints.sql;

\
i database/metadata/sch_metadata_constraints.sql;

\
i database/semantics/sch_semantics_constraints.sql;

\
i database/data/s
ch_data_constraints.sql;

\
i database/network/sch_network_constraints.sql;

\
i database/tree/sch_tree_constraints.sql;

\
i database/history/sch_history_constraints.sql;

\
i database/community/sch_community_constraints.sql;

\
i database/template/sch_template_con
straints.sql;


Stored Procedure (REST interface)


CREATE OR REPLACE FUNCTION metadata.PUT_object( p_uuid_object uuid DEFAULT public.uuid_nil(),


p_id_object bigint DEFAULT NULL,



p_uuid_node uuid DEFAULT public.uuid_nil(),


p_uuid_region uuid DEFAULT NULL,


p_user text DEFAULT config.default_user(),



p_timestamp timestamp DEFAULT now(),


p_comment text DEFAULT NULL,


p_broadcast boolean DEFAULT FALSE,



p_copyrightable boolean DEFAULT FALSE,


p_networked boolean DEFAULT FALSE,


p_language_dependent boolean DEFAULT FALSE)

RETURNS BIGINT

LANGU
AGE plpgsql

VOLATILE

SECURITY INVOKER

AS $$


/*


# Copyright (c) 2010 Berto `d Sera for Vox Humanitatis


# X11 license (often called MIT)


#
----------------------------------------------------------------



# Permission is hereby granted, free of

charge, to any person


# obtaining a copy of this software and associated documentation


# files (the "Software"), to deal in the Software without


# restriction, including without limitation the rights to use,


# copy, modify, merge, publish, dis
tribute, sublicense, and/or sell


# copies of the Software, and to permit persons to whom the


# Software is furnished to do so, subject to the following


# conditions:




# The above copyright notice and this permission notice shall be


# includ
ed in all copies or substantial portions of the Software.




# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,


# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES


# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE
AND


# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT


# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,


# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING


# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR

THE USE OR


# OTHER DEALINGS IN THE SOFTWARE.


#
----------------------------------------------------------------



# Version 0.7.0


#
----------------------------------------------------------------


# This is one of the four REST commands implem
ented, see


# doc/doc_guidelines.txt


#
----------------------------------------------------------------
` */


DECLARE


PAR_id_object BIGINT;

BEGIN


/* Mutex between local and universal ids */


IF (p_id_object IS NOT NULL) AND (p_uuid_object IS NOT

NULL) THEN


RAISE EXCEPTION 'Attempt to put an object with both local and universal indexes %, %!',
p_id_object, p_uuid_object USING ERRCODE = '20454';


END IF;



/* Try to pre
-
load object, if possible (this should happen only for system internal
cla
ssification).


We do NOT trust the id_object passed, if present, it gets verified by selecting it. */


IF (p_id_object IS NOT NULL) AND (p_uuid_object IS NULL) THEN


SELECT id_object


FROM metadata.object


WHERE id_object = p_id_object


I
NTO PAR_id_object;


ELSE


/* Try to pre
-
load networked object, if possible */


IF p_uuid_object IS NOT NULL THEN


SELECT id_object


FROM network.network_object


WHERE uuid = p_uuid_object


INTO PAR_id_object;


END IF;



END IF;



/* Main switch between create and update */


IF PAR_id_object IS NULL THEN


SELECT metadata.POST_object( p_uuid_object := p_uuid_object,


p_uuid_node := p_uuid_node,



p_uuid_region := p_uuid_region,


p_user := p_user,


p_timestamp := p_timestamp,


p_comment := p_commen
t,


p_broadcast := p_broadcast,


p_copyrightable := p_copyrightable,


p_networked := p_networked,


p_l
anguage_dependent := p_language_dependent )


INTO PAR_id_object;


ELSE


/* Update the network_object record if necessary (system classification, like licence,


additional linguistic info etc has none). NOTE! If this "object layer" exists,
the


network_object is non
-
deleted by design */


IF PAR_networked THEN


PERFORM network.PUT_network_object( p_id_object := p_id_object,


p_uuid_object := p_uuid_object,



p_uuid_node := p_uuid_node,


p_uuid_region := p_uuid_region,


p_user := p_user,


p_timestamp := p_times
tamp,


p_comment := p_comment,


p_broadcast := p_broadcast );


END IF;



/* Update the object layer */


UPDATE metadata.object


SET


copyrightable

= p_copyrightable,


networked = p_networked,


language_dependent = p_language_dependent


WHERE id_object = PAR_id_object;


END IF;



RETURN PAR_id_object;

END

$$;


/* set ownership and comment */

ALTER FUNCTION metadata.PUT_obj
ect( p_uuid_object uuid,


p_id_object bigint,


p_uuid_node uuid,


p_uuid_region uuid,


p_user text,



p_timestamp timestamp,


p_comment text,


p_broadcast boolean,


p_copyrightable boolean,


p_networked boolean,


p_language_dependent boolean) OWNER TO ambaradmin;


COMMENT ON FUNCTION metadata.PUT_object( p_uuid_object uuid,


p_id_object bigint,



p_uuid_node uuid,


p_uuid_region uuid,


p_user text,


p_timestamp timestamp,



p_comment text,


p_broadcast boolean,


p_copyrightable boolean,


p_networked boolean,


p_
language_dependent boolean)

IS 'REST API PUT function for "object" objects.';



Stored procedure (internal utility with complex data types)


CREATE OR REPLACE FUNCTION semantics.set_add_linguistics( p_id_classified bigint DEFAULT 0,



p_linguistics semantics.linguistic_data[]
DEFAULT NULL )

RETURNS void

LANGUAGE plpgsql

VOLATILE

SECURITY INVOKER

AS $$


/*


# Copyright (c) 2008 Berto `d Sera for Vox Humanitatis


# X11 license (often called MIT)


#
----------------------------------------------------------------



# Permission is hereby granted, free of charge, to any person


# obtaining a copy of this software and associated documentation


# files (the "Software"), to deal in the Software w
ithout


# restriction, including without limitation the rights to use,


# copy, modify, merge, publish, distribute, sublicense, and/or sell


# copies of the Software, and to permit persons to whom the


# Software is furnished to do so, subject to t
he following


# conditions:




# The above copyright notice and this permission notice shall be


# included in all copies or substantial portions of the Software.




# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,


# EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES


# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND


# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT


# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,


# WHETH
ER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING


# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR


# OTHER DEALINGS IN THE SOFTWARE.


#
----------------------------------------------------------------



# Version 0.7.0


#
--
--------------------------------------------------------------


# This function follows the set_smthing utility guideline. See


# details at docs/doc_guidelines.txt


#
----------------------------------------------------------------


# This function is

executed any time a file_descriptor or a


# textual_content are inserted/updated. Linguistic information is


# passed as an array of linguistic_data.


#


# The first element of the array is used by the calling functions


# to state the "prevalent
" linguistic nature of the stored content.


# That first element is stored in dedicated fields for quick indexing


# ans it is ignored by this function.


#


# When content comes in mixed mediums/communicative_systems we add


# elements to the array t
o express what other linguistic pairs are


# present in the content. Such pair are saved in the form of "linguistics"


# type entries in the incidence_matrix.


#


# For example, this comes extremely handy to manage Japanese text that


# employs mixed

scripts or multilingual content in general.


#
----------------------------------------------------------------
` */

BEGIN


/* Walk the added linguistic information array if added info is stated


When no added info is present the function will simpl
y exit */


IF array_upper(p_linguistics,1) > 1 THEN


FOR i IN (array_lower(p_linguistics,1)+1) .. array_upper(p_linguistics,1) LOOP


/* Generates a new 'linguistics' row in incidence_matrix */


PERFORM semantics.POST_incidence_matrix( NULL,


NULL,


NULL,


NULL,


NULL,



NULL,


'linguistics'::semantics.classification_type,


p_id_classified,


p_linguistics[i].medium,



p_linguistics[i].communicative_system );


END LOOP;


END IF;



RETURN;

END

$$;


/* Set ownership and comment */

ALTER FUNCTION semantics.set_add_linguistics( p_id_classified bigint,



p_linguistics semantics.linguistic_data[] ) OWNER TO
ambaradmin;


COMMENT ON FUNCTION semantics.set_add_linguistics( p_id_classified bigint,


p_linguistics semantics.linguistic_data[] )

IS
'Set the additional linguistic information for a given content unit, after erasing the previous
one (if any was recorded).';


Trigger creation


CREATE OR REPLACE FUNCTION semantics.trigger_incidence_matrix_RBIU()

RETURNS trigger

LANGUAGE plpgsql

VOLATILE

S
ECURITY INVOKER

AS $$

/*


# Copyright (c) 2008 Berto `d Sera for Vox Humanitatis


# X11 license (often called MIT)


#
----------------------------------------------------------------



# Permission is hereby granted, free of charge, to any person


# obtaining a copy of this software and associated documentation


# files (the "Software"), to deal in the Software without


# restriction, including without limitation the rights to use,


# copy, modify, merge, publish, distribute, sublicense, and
/or sell


# copies of the Software, and to permit persons to whom the


# Software is furnished to do so, subject to the following


# conditions:




# The above copyright notice and this permission notice shall be


# included in all copies or subs
tantial portions of the Software.




# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,


# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES


# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND


# NONINFRINGEMENT
. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT


# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,


# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING


# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR


# OTHER D
EALINGS IN THE SOFTWARE.


#
----------------------------------------------------------------



# Version 0.7.0


#
----------------------------------------------------------------
` */


DECLARE


PAR_type semantics.class_type;


PAR_flaguuid uuid;


PAR_owner_region bigint;


PAR_clf_region bigint;


PAR_abstract boolean;


PAR_copyrightable boolean;

BEGIN


/* GENERAL SECTION


=============== */



/* retrieve the class type */


SELECT


cl.type,


cl.abstract


FROM
class as cl


WHERE cl.id_object = NEW.id_classifier


INTO


PAR_type,


PAR_abstract;



/* retrieve the classified object licensing state */


SELECT ob.copyrightable


FROM metadata.object as ob


WHERE ob.id_object = NEW.id_classified


INTO PAR_copyrightable;



/* verify that this class can be used in classification */


IF (COALESCE(PAR_abstract,true)) THEN


RAISE EXCEPTION 'Class % is either not a class or abstract!', NEW.id_classifier USING ERRCODE =
'20301';


END IF;



/*

verify that the target object can be classified */


IF (SELECT (COUNT(*)=1) FROM incidence_matrix WHERE id_object=NEW.id_classified) THEN


RAISE EXCEPTION 'Object % is a classification. You cannot classify a classification!',
NEW.id_classified USIN
G ERRCODE = '20302';


END IF;



/* BRANCH ON CLASSIFICATION VS RELATIONS


===================================== */


IF PAR_type = 'relation'::semantics.class_type THEN


/* RELATIONS


========= */



/* verify that relations have correc
t format */


IF NEW.id_classified2 IS NULL THEN


RAISE EXCEPTION 'Class % is a relation, but second member is NULL!', NEW.id_classifier USING
ERRCODE = '20303';


ELSE


/* verify that the second target object can be classified */


IF
(SELECT (COUNT(*)=1) FROM incidence_matrix WHERE id_object=NEW.id_classified2) THEN


RAISE EXCEPTION 'Object % (the second member) is a classification. You cannot classify a
classification!', NEW.id_classified2 USING ERRCODE = '20304';


ELSE



/* Everything is okay. Force mandatory values for a relation */


NEW.type := 'user'::semantics.classification_type;


END IF;


END IF;


ELSE


IF NEW.type = 'linguistics'::semantics.classification_type THEN


/* ADDED LINGUISTI
C INFORMATION


============================ */



/* verify that we have the correct format */


IF NEW.id_classified2 IS NULL THEN


RAISE EXCEPTION 'On Added Linguistic Information for % Medium information is NULL!',
NEW.id_classif
ied USING ERRCODE = '20305';


END IF;



/* Verify that classifier is a communicative_system */


IF PAR_type = 'communicative_system'::semantics.class_type THEN


RAISE EXCEPTION 'On Added Linguistic Information for % id_classifier % is

not a
communicative_system!', NEW.id_classified, NEW.id_classifier USING ERRCODE = '20306';


END IF;



/* Verify that classified2 is a medium */


IF NOT (SELECT COUNT(*)=1 FROM v_medium WHERE id_object = NEW.id_classified2) THEN


RAI
SE EXCEPTION 'On Added Linguistic Information for % id_classified2 % is not a medium!',
NEW.id_classified, NEW.id_classifier USING ERRCODE = '20307';


END IF;



/* Verify that classified is a mediated object */


IF NOT (SELECT COUNT(*)=1 FRO
M metadata.object WHERE id_object=NEW.id_classified AND mediated)
THEN


RAISE EXCEPTION 'Attempt to enter Added Linguistic Information on % id_classified, which is
not a mediated object!', NEW.id_classified USING ERRCODE = '20308';


END IF;




/* Verify that the linguistic pair is legal. This is a centralized check,


if the pair is not legal the function will raise an exception */


PERFORM test_legal_linguistic_info(NEW.id_classified, (NEW.id_classifier,
NEW.id_classified2)::sem
antics.linguistic_data);


ELSE


/* CLASSIFICATIONS


=============== */



/* force mandatory values for a normal classification */


NEW.id_classified2 := NULL;



/* if this is a copyright verify that we are classifying with a

license */


IF (NEW.type='copyright'::semantics.classification_type) AND (PAR_type <>
'license'::semantics.class_type) THEN


RAISE EXCEPTION 'Class % is not a license!', NEW.id_classifier USING ERRCODE = '20309';


END IF;



/* if th
is is a copyright verify that we are classifying something that can be copyrighted */


IF (NEW.type='copyright'::semantics.classification_type) AND NOT PAR_copyrightable THEN


RAISE EXCEPTION 'Attempt to license object %, which is not subject
to copyright!',
NEW.id_classified USING ERRCODE = '20310';


END IF;



/* if this is a copyright verify that we can add the license if there are already other
licenses pending */


IF (NEW.type='copyright'::semantics.classification_type) AND
NOT
test_legal_licence(NEW.id_classified, NEW.id_classifier) THEN


RAISE EXCEPTION 'License % cannot be applied to object % as it clashes with the existing
licenses for the object!', NEW.id_classifier, NEW.id_classified USING ERRCODE = '20311';



END IF;


END IF;


END IF;



RETURN NEW;

END

$$;


/* set ownership and comment */

ALTER FUNCTION semantics.trigger_incidence_matrix_RBIU() OWNER TO ambaradmin;


COMMENT ON FUNCTION semantics.trigger_incidence_matrix_RBIU() IS 'Trigger function';


XML

data rendering


CREATE OR REPLACE FUNCTION api.xml_classification_set(p_id_object bigint, p_type
ambaramant.ambclftype)

RETURNS xml

LANGUAGE plpgsql

IMMUTABLE

STRICT

AS $$


/*


# Copyright (c) 2008 Berto `d Sera for Vox Humanitatis


# X11 license (oft
en called MIT)


#
----------------------------------------------------------------


# Permission is hereby granted, free of charge, to any person


# obtaining a copy of this software and associated documentation


# files (the "Software"), to deal in th
e Software without


# restriction, including without limitation the rights to use,


# copy, modify, merge, publish, distribute, sublicense, and/or sell


# copies of the Software, and to permit persons to whom the


# Software is furnished to do so, subj
ect to the following


# conditions:



# The above copyright notice and this permission notice shall be


# included in all copies or substantial portions of the Software.



# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,


# EXPRESS OR

IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES


# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND


# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT


# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,


# WHETHER
IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING


# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR


# OTHER DEALINGS IN THE SOFTWARE.


#
----------------------------------------------------------------


# Version 0.6.0


#
----------
------------------------------------------------------
` */


DECLARE


PAR_default_license xml;


PAR_classification xml;


PAR_superseed boolean;

BEGIN


/* Standard typed classification set rendering */



/* Identify regional copyright
if needed */


IF p_type='copyright' THEN


SELECT


re.superseed_license,


api.xml_default_license(no.id_region)


FROM


ambaranet.network_object as no,


ambaranet.region as re


WHERE


no.id_object = p_id_object
AND


re.id_object = no.id_region


INTO


PAR_superseed,


PAR_default_license;


END IF;



SELECT xmlagg( api.xml_classification(im.id_object) )


FROM ambaramant.incidence_matrix as im


WHERE


im.type = p_type AND


im.id_class
ified = p_id_object


INTO PAR_classification;



/* Add default regional licensing info, unless the region has stated that


individual object licenses totally superseed the regional default */


IF (p_type='copyright' AND NOT PAR_superseed) OR (p_type
='copyright' AND PAR_classification IS
NULL) THEN


PAR_classification := xmlconcat(PAR_default_license,PAR_classification) ;


END IF;



RETURN PAR_classification;

END

$$;



Smalltalk class declaration


"===============================================
=======================

|

| Ambaradan.PersistentObject class definition

|


======================================================================"


"======================================================================

| Copyright (c) 2008 Berto 'd Ser
a for Vox Humanitatis <berto.d.sera@gmail.com>

| X11 license (often called MIT)

|
----------------------------------------------------------------


| Permission is hereby granted, free of charge, to any person

| obtaining a copy of this software and asso
ciated documentation

| files (the 'Software'), to deal in the Software without

| restriction, including without limitation the rights to use,

| copy, modify, merge, publish, distribute, sublicense, and/or sell

| copies of the Software, and to permit pe
rsons to whom the

| Software is furnished to do so, subject to the following

| conditions:

| The above copyright notice and this permission notice shall be

| included in all copies or substantial portions of the Software.

| THE SOFTWARE IS PROVIDED 'A
S IS', WITHOUT WARRANTY OF ANY KIND,

| EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES

| OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND

| NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT

| HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY,

| WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING

| FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR

| OTHER DEALINGS IN THE SOFTWARE.


=======================================================
==============="



Ambaradan.AmbaradanLocalizedWidget subclass: PersistentObject [


<category: 'Datamodel'>


<comment: 'version 0.6.0


Abstract layer providing persistency to all objects. As the class is built on top of


Ambaradan.AmbaradanL
ocalizedWidget, all its subclasses are also buildable.' >




PersistentObject class [



isAbstract [


<category: 'testing'>


^true


]



tag [


"identifier for the XML query"


<category: 'persistency'>


^s
elf subclassResponsibility


]



retriever: aUuid [


"Query used to retrieve a copy of the object from the database, based on UUID"


<category: 'persistency'>


^self subclassResponsibility


]



emerge: aSymbol [



"Retrieve the object from the database based on its uuid (received as symbol). aSymbol may
sometimes be


aString (as in the ResultSet class)."


<category: 'instance creation'>


^((self new) application gateway) get: aSymbol ofType:

self


]



from: anXmlFragment [


<category: 'instance creation'>


^self subclassResponsibility


]



initialize [


"Registering the uuids of the text strings used by the GUI."


<category: 'initialization'>



super initialize.


self


guiAdd: #yes
-
> 'yes';


guiAdd: #no
-
> 'no'.


]



]



dictionaryForAttributes: aCollection [


<category: 'utils'>


| dic |


dic := Dictionary new.


(
aCollection collect: [ :each | dic add: (Association key: (each expandedName) value: (each
value)) ]).


^dic


]



isRetrieved [


<category: 'status'>


^self subclassResponsibility


]



isLoaded [


<category: 'status'>


^self subclassResponsibility


]



isErased [


"An erased appears as loaded in the GUI, but not retrieved. Infact there


is nothing to be retrieved from the network, as the object is erased."


<category: 'status'>


(self isLoaded
) & (self isRetrieved not)


]



full [


"answer myself as a fully loaded object"


<category: 'status'>


self isLoaded


ifFalse: [^self emerge]


ifTrue: [^self]


]



publish [


"make me persistent. Answer with q
uery result"


<category: 'database'>


^self subclassResponsibility


]



erase [


"erase me from the database. Answer with query result"


<category: 'database'>


^self subclassResponsibility


]



booleanProperty: aBoolea
n [


<category: 'building'>


aBoolean


ifTrue: [ ^self localize: #yes ]


ifFalse: [ ^self localize: #no ]


]



]