» New User
» Forgot Password?
About
» What is hipergate
» Functional Modules
» Benefits
» Architecture
Demo
» Screenshots
»
Demo
Documentation
» Install
» Manuals
» API Reference
» Case Studies
Support
» Forums
» Development Weblog
» SourceForge Tracker
» Commercial Support
Downloads
» License
» Downloads
» Subversion
Partners
» Become a Partner
» Find a Partner
 Private Area
» Authors
|  |
ForumRE: RE: RE: RE: RE: RE: Hipergate 5.5
[Back to Message List]
| Hola Gente!
Estoy feliz por la liberacion de la version 5.5, felicitaciones!
Sin embargo, los problemas que experimento para migrar desde la version 4 siguen siendo los mismos :(
Existe alguna documentacion de como migrar de la version 4 a la 5.x?
En que momento podemos ver este tema??
Saludos cordiales.- |
 reply
|
|
RE: Hipergate 5.5
Author: sergiom | 2010-07-26 06:37:02 |
| No debería resultarte tan complicado.
A ver, primero haz una copia de respaldo de tu base de datos.
Crea una webapp de Tomcat con la versión 5.5 y la conectas a la misma base de datos que ya tengas (en realidad no necesitas hacer nada porque por defecto pillará el mismo hipergate.cnf que la version 4).
Ahora tienes que ejecutar los scripts SQL 400-500.sql y 500-550.sql que hay en WEB-INF/classes/com/knowgate/hipergate/datamodel/upgrade
Para lanzar los scripts SQL puedes usar la herramienta SQL que hay en http://localhost/hipergate/admin/sql.htm poniendo el Delimitador en "GO;"
Una vez ejecutada la actualización del modelo de datos ya puedes entrar con normalidad a la 5.5. | |
reply
|
|
RE: RE: Hipergate 5.5
Author: EIPSISTEMAS | 2010-07-26 07:47:56 |
| Sergio:
Gracias por tu pronta respuesta!
Cuando quiero ejecutar 400-500.ddl (lo edito, copiar y pegar, delimitador GO;)a traves de /admin/sql.htm me da el siguiente mensaje de error:
ERROR: error de sintaxis en o cerca de �INT� Position: 12
y ahi me quedo.
Por otro lado, si intento una instalacion limpia, despues del primer "iniciar" donde levanta los datos del hipergate.cfn me da el siguiente error:
Aviso sobre librerias no encontradas:
ClassNotFoundException javax.media.jai.util.ImagingException sun.jai-jai_core.jar
ClassNotFoundException com.sun.media.jai.codec.ImageCodec sun.jai-jai_codec.jar
, me detecta la version 400 de la base de datos, presiono actualizar de la version 4.0.0 y me aparece el siguiente error:
IOException executeBulk() upgrade/postgresql/500-550.ddl
Bueno, te recuerdo que aca tengo hipergate 4.12, CentOS 5.5, Tomcat 6, java 1.6.0_14-b08 y PostgreSQL 8.3
Por cualquier cosa mi msn es german.basisty@eipsistemas.com.ar
Saludos cordiales. | |
reply
|
|
RE: RE: RE: Hipergate 5.5
Author: sergiom | 2010-07-26 08:02:01 |
| Es un problema del propio script de actualización que a veces delimita con GO; y otras sólo con punto y coma.
Prueba a lanzar este que te adjunto a continuación que es el mismo pero con todos los delimitadores en GO;
Recuerda que primero debes haber lanzado el script de actualziación de la 4.0 a la 5.0 antes de lanzar el de la 5.0 a la 5.5
UPDATE k_version SET vs_stamp='5.5.0'
GO;
ALTER TABLE k_version ADD bo_allow_stats SMALLINT DEFAULT 0
GO;
CREATE SEQUENCE seq_k_adhoc_mailings INCREMENT 1 START 1
GO;
ALTER TABLE k_meetings_lookup ADD tr_ko VARCHAR(50) NULL
GO;
ALTER TABLE k_activities ADD gu_address CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD gu_campaign CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD gu_list CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD gu_writer CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD gu_meeting CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD gu_pageset CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD gu_mailing CHAR(32) NULL
GO;
ALTER TABLE k_activities ADD dt_mailing TIMESTAMP NULL
GO;
ALTER TABLE k_activities ADD tx_subject VARCHAR(254) NULL
GO;
ALTER TABLE k_activities ADD tx_email_from VARCHAR(254) NULL
GO;
ALTER TABLE k_activities ADD nm_from VARCHAR(254) NULL
GO;
ALTER TABLE k_activities ADD url_activity VARCHAR(254) NULL
GO;
ALTER TABLE k_activities ADD nm_author VARCHAR(200) NULL
GO;
ALTER TABLE k_activities ADD pg_activity INTEGER NULL
GO;
ALTER TABLE k_activities ADD id_language CHAR(2) NULL
GO;
ALTER TABLE k_adhoc_mailings ADD bo_urgent SMALLINT DEFAULT 0
GO;
ALTER TABLE k_adhoc_mailings ADD bo_reminder SMALLINT DEFAULT 0
GO;
ALTER TABLE k_pagesets ADD bo_urgent SMALLINT DEFAULT 0
GO;
ALTER TABLE k_contacts ADD url_linkedin VARCHAR(254) NULL
GO;
ALTER TABLE k_contacts ADD url_facebook VARCHAR(254) NULL
GO;
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class) VALUES ('NOPO','NEW OPORTUNITY','com.knowgate.scheduler.events.DoNothing')
GO;
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class) VALUES ('MOPO','MODIFY OPORTUNITY','com.knowgate.scheduler.events.DoNothing')
GO;
ALTER TABLE k_prod_attr ADD format VARCHAR(50) NULL
GO;
ALTER TABLE k_education_degree ADD id_country CHAR(3) NULL
GO;
ALTER TABLE k_contact_education ADD pg_product INTEGER NULL
GO;
ALTER TABLE k_contact_education ADD gu_product CHAR(32)
GO;
CREATE TABLE k_oportunities_attachs
(
gu_oportunity CHAR(32) NOT NULL,
pg_product INTEGER NOT NULL,
gu_product CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
gu_writer CHAR(32) NOT NULL,
CONSTRAINT pk_oportunities_attachs PRIMARY KEY (gu_oportunity,pg_product)
)
GO;
CREATE TABLE k_x_adhoc_mailing_list (
gu_list CHAR(32) NOT NULL,
gu_mailing CHAR(32) NOT NULL,
CONSTRAINT pk_x_adhoc_mailing_list PRIMARY KEY (gu_list,gu_mailing)
)
GO;
DROP FUNCTION k_sp_del_list(CHAR)
GO;
CREATE FUNCTION k_sp_del_list (CHAR) RETURNS INTEGER AS '
DECLARE
tp SMALLINT;
wa CHAR(32);
bk CHAR(32);
BEGIN
SELECT tp_list,gu_workarea INTO tp,wa FROM k_lists WHERE gu_list=$1;
SELECT gu_list INTO bk FROM k_lists WHERE gu_workarea=wa AND gu_query=$1 AND tp_list=4;
IF FOUND THEN
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_contact FROM k_x_list_members WHERE gu_list=bk) AND gu_member NOT IN (SELECT x.gu_contact FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>bk);
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_company FROM k_x_list_members WHERE gu_list=bk) AND gu_member NOT IN (SELECT x.gu_company FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>bk);
DELETE FROM k_x_list_members WHERE gu_list=bk;
DELETE FROM k_x_campaign_lists WHERE gu_list=bk;
DELETE FROM k_x_adhoc_mailing_list WHERE gu_list=bk;
DELETE FROM k_lists WHERE gu_list=bk;
END IF;
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_contact FROM k_x_list_members WHERE gu_list=$1) AND gu_member NOT IN (SELECT x.gu_contact FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>$1);
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_company FROM k_x_list_members WHERE gu_list=$1) AND gu_member NOT IN (SELECT x.gu_company FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>$1);
DELETE FROM k_x_list_members WHERE gu_list=$1;
DELETE FROM k_x_campaign_lists WHERE gu_list=$1;
DELETE FROM k_x_adhoc_mailing_list WHERE gu_list=$1;
UPDATE k_activities SET gu_list=NULL WHERE gu_list=$1;
UPDATE k_x_activity_audience SET gu_list=NULL WHERE gu_list=$1;
DELETE FROM k_lists WHERE gu_list=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
GO;
CREATE FUNCTION k_sp_del_adhoc_mailing (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_x_adhoc_mailing_list WHERE gu_mailing=$1;
DELETE FROM k_adhoc_mailings WHERE gu_mailing=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
GO;
CREATE TABLE k_activity_attachs
(
gu_activity CHAR(32) NOT NULL,
pg_product INTEGER NOT NULL,
gu_product CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
gu_writer CHAR(32) NOT NULL,
CONSTRAINT pk_activity_attachs PRIMARY KEY (gu_activity,pg_product)
)
GO;
DROP PROCEDURE k_sp_del_activity
GO;
CREATE FUNCTION k_sp_del_activity (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_activity_attachs WHERE gu_activity=$1;
DELETE FROM k_x_activity_audience WHERE gu_activity=$1;
DELETE FROM k_activities WHERE gu_activity=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
GO;
CREATE VIEW v_activity_locat AS
SELECT p.gu_product, p.nm_product, p.de_product, c.gu_activity, c.pg_product, c.dt_created, l.dt_modified, l.dt_uploaded, l.gu_location, l.id_cont_type, l.id_prod_type, l.len_file, l.xprotocol, l.xhost, l.xport, l.xpath, l.xfile, l.xoriginalfile, l.xanchor, l.status, l.vs_stamp, l.tx_email, l.tag_prod_locat
FROM k_activity_attachs c, k_products p, k_prod_locats l
WHERE c.gu_product=p.gu_product AND c.gu_product=l.gu_product
GO;
DROP FUNCTION k_sp_del_product(CHAR)
GO;
CREATE FUNCTION k_sp_del_product (CHAR) RETURNS INTEGER AS '
DECLARE
GuAddress CHAR(32);
BEGIN
SELECT gu_address INTO GuAddress FROM k_products WHERE gu_product=$1;
DELETE FROM k_images WHERE gu_product=$1;
DELETE FROM k_x_cat_objs WHERE gu_object=$1;
DELETE FROM k_prod_keywords WHERE gu_product=$1;
DELETE FROM k_prod_fares WHERE gu_product=$1;
DELETE FROM k_prod_attrs WHERE gu_object=$1;
DELETE FROM k_prod_attr WHERE gu_product=$1;
DELETE FROM k_prod_locats WHERE gu_product=$1;
DELETE FROM k_products WHERE gu_product=$1;
IF GuAddress IS NOT NULL THEN
UPDATE k_academic_courses SET gu_address=NULL WHERE gu_acourse=$1;
DELETE FROM k_addresses WHERE gu_address=GuAddress;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
GO;
DROP FUNCTION k_sp_del_acourse(CHAR)
GO;
CREATE FUNCTION k_sp_del_acourse (CHAR) RETURNS INTEGER AS '
DECLARE
GuAddress CHAR(32);
BEGIN
SELECT gu_address INTO GuAddress FROM k_academic_courses WHERE gu_acourse=$1;
DELETE FROM k_x_course_alumni WHERE gu_acourse=$1;
DELETE FROM k_x_course_bookings WHERE gu_acourse=$1;
DELETE FROM k_evaluations WHERE gu_acourse=$1;
DELETE FROM k_absentisms WHERE gu_acourse=$1;
DELETE FROM k_academic_courses WHERE gu_acourse=$1;
IF GuAddress IS NOT NULL THEN
DELETE FROM k_addresses WHERE gu_address=GuAddress;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
GO;
CREATE TABLE k_admission (
gu_admission CHAR(32) NOT NULL,
gu_contact CHAR(32) NOT NULL,
gu_oportunity CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
gu_acourse CHAR(32) NOT NULL,
id_objetive_1 VARCHAR(50) NULL, /*Program in which admission sought 1*/
id_objetive_2 VARCHAR(50) NULL, /*Program in which admission sought 2*/
id_objetive_3 VARCHAR(50) NULL, /*Program in which admission sought 3*/
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,/*admission application date*/
dt_target TIMESTAMP NULL, /*Target date for the admission test*/
is_call SMALLINT NULL, /*Call Meeting (Yes) or Special (No) admission test*/
id_place VARCHAR(50) NULL, /*Place of entrance examinations*/
id_interviewer VARCHAR(50) NULL, /*Name of interviewer*/
dt_interview TIMESTAMP NULL, /*Date of completion of the interview*/
dt_admision_test TIMESTAMP NULL, /*Actual date for the admission test*/
is_grant SMALLINT NULL, /*Grant Request (Yes / No)*/
nu_grant DECIMAL(4,2) NULL, /*The amount or percentage of scholarship*/
nu_interview INTEGER NULL, /*points interview */
nu_vips INTEGER NULL, /*points vips */
nu_nips INTEGER NULL, /*points nips */
nu_elp INTEGER NULL, /*points elp */
nu_total INTEGER NULL, /*points total */
id_test_result VARCHAR(50) NULL, /*Test result (Admitted, admitted conditionally, not supported)*/
CONSTRAINT pk_admission PRIMARY KEY (gu_admission),
CONSTRAINT u_admission UNIQUE (gu_contact,gu_oportunity)
)
GO;
CREATE TABLE k_admission_lookup
(
gu_owner CHAR(32) NOT NULL,
id_section VARCHAR(30) NOT NULL,
pg_lookup INTEGER NOT NULL,
vl_lookup VARCHAR(50) NULL,
tr_es VARCHAR(50) NULL,
tr_en VARCHAR(50) NULL,
tr_de VARCHAR(50) NULL,
tr_it VARCHAR(50) NULL,
tr_fr VARCHAR(50) NULL,
tr_pt VARCHAR(50) NULL,
tr_ca VARCHAR(50) NULL,
tr_gl VARCHAR(50) NULL,
tr_eu VARCHAR(50) NULL,
tr_ja VARCHAR(50) NULL,
tr_cn VARCHAR(50) NULL,
tr_tw VARCHAR(50) NULL,
tr_fi VARCHAR(50) NULL,
tr_ru VARCHAR(50) NULL,
tr_nl VARCHAR(50) NULL,
tr_th VARCHAR(50) NULL,
tr_cs VARCHAR(50) NULL,
tr_uk VARCHAR(50) NULL,
tr_no VARCHAR(50) NULL,
tr_sk VARCHAR(50) NULL,
tr_pl VARCHAR(50) NULL,
tr_vn VARCHAR(50) NULL,
CONSTRAINT pk_admission_lookup PRIMARY KEY (gu_owner,id_section,pg_lookup)
)
GO;
DROP VIEW v_pagesets_mailings
GO;
CREATE VIEW v_pagesets_mailings AS
(SELECT
p.gu_pageset,p.gu_workarea,p.nm_pageset,p.tx_comments,p.path_data,p.dt_created,m.nm_microsite,p.id_status,p.id_language,m.id_app,p.bo_urgent,NULL AS dt_execution
FROM k_pagesets p,k_microsites m WHERE p.gu_microsite=m.gu_microsite OR p.gu_microsite IS NULL)
UNION
(SELECT
a.gu_mailing AS gu_pageset,a.gu_workarea,a.nm_mailing AS nm_pageset,a.tx_subject AS tx_comments ,'Hipermail' AS path_data,a.dt_created,'AdHoc' AS nm_microsite,a.id_status,'' AS id_language,21 AS id_app,a.bo_urgent,a.dt_execution
FROM k_adhoc_mailings a)
GO;
DELETE FROM k_lu_job_commands WHERE id_command='SMS'
GO;
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class) VALUES ('SMS','SEND SMS PUSH TEXT MESSAGE','com.knowgate.scheduler.jobs.SMSSender')
GO;
CREATE TABLE k_urls
(
gu_url CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
url_addr VARCHAR(2000) NOT NULL,
tx_title VARCHAR(2000) NULL,
de_url VARCHAR(2000) NULL,
CONSTRAINT pk_urls PRIMARY KEY(gu_url,gu_workarea)
)
GO;
CREATE TABLE k_job_atoms_clicks
(
gu_job CHAR(32) NOT NULL,
pg_atom INTEGER NOT NULL,
gu_url CHAR(32) NOT NULL,
dt_action TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
id_status SMALLINT DEFAULT 1,
gu_company CHAR(32) NULL,
gu_contact CHAR(32) NULL,
ip_addr VARCHAR(16) NULL,
tx_email VARCHAR(100) NULL
)
GO;
DROP FUNCTION k_sp_del_job(CHAR)
GO;
CREATE FUNCTION k_sp_del_job (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_job_atoms_clicks WHERE gu_job=$1;
DELETE FROM k_job_atoms_tracking WHERE gu_job=$1;
DELETE FROM k_job_atoms_archived WHERE gu_job=$1;
DELETE FROM k_job_atoms WHERE gu_job=$1;
DELETE FROM k_jobs WHERE gu_job=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
GO;
CREATE TABLE k_bulkloads (
pg_bulkload INTEGER NOT NULL,
dt_uploaded TIMESTAMP NOT NULL,
gu_workarea CHAR(32) NOT NULL,
nm_file VARCHAR(254) NOT NULL,
id_batch VARCHAR(32) NULL,
id_status VARCHAR(30) NULL,
dt_processed TIMESTAMP NOT NULL,
nu_lines INTEGER DEFAULT 0,
nu_successful INTEGER DEFAULT 0,
nu_errors INTEGER DEFAULT 0,
CONSTRAINT pk_bulkloads PRIMARY KEY(pg_bulkload),
CONSTRAINT u1_bulkloads UNIQUE(dt_uploaded,gu_workarea,nm_file)
)
GO; | |
reply
|
|
RE: RE: RE: RE: Hipergate 5.5
Author: EIPSISTEMAS | 2010-07-28 04:36:45 |
| Sergio:
Probe el script que me dejaste y tampoco funciono. Te cuento que en realidad yo vengo migrando la instalacion de hipergate desde hace tiempo. Creo que la primera instalacion que puse en marcha fue la 2.algo.
Bueno, la cuestion es que acomode a mano el script 400-500.ddl y 500-550.sql para que funcionen correctamente, y cambie el separador GO por el ; que es el nativo de PostgreSQL. Reemplace el webapp, copie el directorio workareas y lo que estaba bajo storage/domains y voila! Aparentemente esta andando correctamente. Me encanto la nueva biblioteca corporativa. Voy a dejar 2 respuestas mas a este post con los 400-500.sql y 500-550.sql por si le sirve a alguien mas.
Saludos cordiales.- | |
reply
|
|
RE: RE: RE: RE: RE: Hipergate 5.5
Author: EIPSISTEMAS | 2010-07-28 04:41:37 |
| 400-500.sql
Suponiendo que nuestra base de datos se llame hipergate y estemos utilizando el usuario postgres, y la autenticacion para solicitudes locales es trust, ejecutar:
psql -U postgres hipergate < 400-500.sql
=======================================================
UPDATE k_version SET vs_stamp='5.0.0';
INSERT INTO k_classes VALUES(14,'PasswordRecord');
ALTER TABLE k_pageset_pages ADD path_publish VARCHAR(254) NULL;
ALTER TABLE k_oportunities ADD dt_last_call TIMESTAMP NULL;
ALTER TABLE k_newsgroups ADD tx_journal VARCHAR(4000) NULL;
CREATE TABLE k_newsgroup_tags
(
gu_tag CHAR(32) NOT NULL,
gu_newsgrp CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
od_tag SMALLINT DEFAULT 1000,
tl_tag VARCHAR(70) NOT NULL,
de_tag VARCHAR(200) NULL,
nu_msgs INTEGER DEFAULT 0,
bo_incoming_ping SMALLINT DEFAULT 0,
dt_trackback TIMESTAMP NULL,
url_trackback VARCHAR(2000) NULL,
CONSTRAINT pk_newsgroup_tags PRIMARY KEY (gu_tag)
);
CREATE TABLE k_newsmsg_tags
(
gu_msg CHAR(32) NOT NULL,
gu_tag CHAR(32) NOT NULL,
CONSTRAINT pk_newsmsg_tags PRIMARY KEY (gu_msg,gu_tag)
);
DROP FUNCTION k_sp_del_newsgroup (CHAR);
CREATE FUNCTION k_sp_del_newsgroup (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_newsmsg_tags WHERE gu_msg IN (SELECT gu_object FROM k_x_cat_objs WHERE gu_category=$1);
DELETE FROM k_newsmsg_vote WHERE gu_msg IN (SELECT gu_object FROM k_x_cat_objs WHERE gu_category=$1);
DELETE FROM k_newsmsgs WHERE gu_msg IN (SELECT gu_object FROM k_x_cat_objs WHERE gu_category=$1);
DELETE FROM k_newsgroup_subscriptions WHERE gu_newsgrp=$1;
DELETE FROM k_newsgroup_tags WHERE gu_newsgrp=$1;
DELETE FROM k_newsgroups WHERE gu_newsgrp=$1;
DELETE FROM k_x_cat_objs WHERE gu_category=$1;
PERFORM k_sp_del_category ($1);
RETURN 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION k_sp_del_newsmsg (CHAR);
CREATE FUNCTION k_sp_del_newsmsg (CHAR) RETURNS INTEGER AS '
DECLARE
IdChild CHAR(32);
childs REFCURSOR;
BEGIN
OPEN childs FOR SELECT gu_msg FROM k_newsmsgs WHERE gu_parent_msg=$1;
LOOP
FETCH childs INTO IdChild;
EXIT WHEN NOT FOUND;
PERFORM k_sp_del_newsmsg (IdChild);
END LOOP;
CLOSE childs;
UPDATE k_newsmsgs SET nu_thread_msgs=nu_thread_msgs-1 WHERE gu_thread_msg=$1;
DELETE FROM k_x_cat_objs WHERE gu_object=$1;
DELETE FROM k_newsmsg_vote WHERE gu_msg=$1;
DELETE FROM k_newsmsg_tags WHERE gu_msg=$1;
DELETE FROM k_newsmsgs WHERE gu_msg=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
ALTER TABLE k_newsmsgs ADD dt_modified TIMESTAMP NULL;
CREATE SEQUENCE seq_k_webbeacons INCREMENT 1 START 1;
CREATE TABLE k_webbeacons (
id_webbeacon INTEGER NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt_last_visit TIMESTAMP NOT NULL,
nu_pages INTEGER NOT NULL,
gu_user CHAR(32) NULL,
gu_contact CHAR(32) NULL,
CONSTRAINT pk_webbeacons PRIMARY KEY(id_webbeacon)
);
CREATE TABLE k_webbeacon_pages (
id_page INTEGER NOT NULL,
nu_hits INTEGER NOT NULL,
gu_object CHAR(32) NULL,
url_page VARCHAR(254) NOT NULL,
CONSTRAINT pk_webbeacon_pages PRIMARY KEY(id_page),
CONSTRAINT u1_webbeacon_pages UNIQUE (url_page),
CONSTRAINT c1_webbeacon_pages CHECK (LENGTH(url_page)>0)
);
CREATE TABLE k_webbeacon_hit (
id_webbeacon INTEGER NOT NULL,
id_page INTEGER NOT NULL,
id_referrer INTEGER NULL,
dt_hit TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ip_addr INTEGER NULL
);
ALTER TABLE k_users ADD mov_phone VARCHAR(16) NULL;
DROP VIEW v_project_company;
CREATE VIEW v_project_company AS
(SELECT p.gu_project,p.dt_created,p.nm_project,p.id_parent,p.id_dept,p.dt_start,p.dt_end,p.pr_cost,p.gu_owner,p.de_project,p.gu_company,p.gu_contact,e.od_level,e.od_walk,c.nm_legal,COALESCE(d.tx_name,'') || ' ' || COALESCE(d.tx_surname,'') AS full_name, p.id_status, p.id_ref
FROM k_project_expand e, k_contacts d, k_projects p LEFT OUTER JOIN k_companies c ON c.gu_company=p.gu_company
WHERE e.gu_project=p.gu_project AND d.gu_contact=p.gu_contact)
UNION
(SELECT p.gu_project,p.dt_created,p.nm_project,p.id_parent,p.id_dept,p.dt_start,p.dt_end,p.pr_cost,p.gu_owner,p.de_project,p.gu_company,p.gu_contact,e.od_level,e.od_walk,c.nm_legal,NULL AS full_name, p.id_status, p.id_ref
FROM k_project_expand e,
k_projects p LEFT OUTER JOIN k_companies c ON c.gu_company=p.gu_company
WHERE e.gu_project=p.gu_project AND p.gu_contact IS NULL);
ALTER TABLE k_contacts ADD id_batch VARCHAR(32);
ALTER TABLE k_companies ADD id_batch VARCHAR(32);
ALTER TABLE k_academic_courses ADD pr_acourse DECIMAL(14,4) NULL;
ALTER TABLE k_x_course_bookings DROP COLUMN dt_paid;
ALTER TABLE k_x_course_bookings ADD dt_paid TIMESTAMP NULL;
ALTER TABLE k_x_course_bookings ADD id_transact VARCHAR(32) NULL;
ALTER TABLE k_x_course_bookings ADD tp_billing CHAR(1) NULL;
ALTER TABLE k_academic_courses ADD gu_address CHAR(32) NULL;
ALTER TABLE k_academic_courses DROP CONSTRAINT u1_academic_courses;
INSERT INTO k_classes VALUES(66,'EducationInstitution');
INSERT INTO k_classes VALUES(67,'EducationDegree');
CREATE TABLE k_education_institutions (
gu_institution CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
nm_institution VARCHAR(50) NOT NULL,
id_institution VARCHAR(30) NULL,
bo_active SMALLINT DEFAULT 1,
CONSTRAINT pk_education_institutions PRIMARY KEY (gu_institution),
CONSTRAINT u1_education_institutions UNIQUE (gu_workarea,nm_institution)
);
CREATE TABLE k_education_degree (
gu_degree CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
nm_degree VARCHAR(50) NOT NULL,
tp_degree VARCHAR(50) NULL,
id_degree VARCHAR(32) NULL,
CONSTRAINT pk_education_degree PRIMARY KEY (gu_degree),
CONSTRAINT u1_education_degree UNIQUE (gu_workarea,nm_degree)
);
CREATE TABLE k_education_degree_lookup
(
gu_owner CHAR(32) NOT NULL,
id_section VARCHAR(30) NOT NULL,
pg_lookup INTEGER NOT NULL,
vl_lookup VARCHAR(50) NULL,
tr_es VARCHAR(50) NULL,
tr_en VARCHAR(50) NULL,
tr_de VARCHAR(50) NULL,
tr_it VARCHAR(50) NULL,
tr_fr VARCHAR(50) NULL,
tr_pt VARCHAR(50) NULL,
tr_ca VARCHAR(50) NULL,
tr_gl VARCHAR(50) NULL,
tr_eu VARCHAR(50) NULL,
tr_ja VARCHAR(50) NULL,
tr_cn VARCHAR(50) NULL,
tr_tw VARCHAR(50) NULL,
tr_fi VARCHAR(50) NULL,
tr_ru VARCHAR(50) NULL,
tr_nl VARCHAR(50) NULL,
tr_th VARCHAR(50) NULL,
tr_cs VARCHAR(50) NULL,
tr_uk VARCHAR(50) NULL,
tr_no VARCHAR(50) NULL,
tr_sk VARCHAR(50) NULL,
tr_pl VARCHAR(50) NULL,
tr_vn VARCHAR(50) NULL,
CONSTRAINT pk_education_degree_lookup PRIMARY KEY (gu_owner,id_section,pg_lookup)
);
CREATE TABLE k_contact_education (
gu_contact CHAR(32) NOT NULL,
gu_degree CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
bo_completed SMALLINT DEFAULT 1,
gu_institution CHAR(32) NULL,
nm_center VARCHAR(50) NULL,
tp_degree VARCHAR(50) NULL,
id_degree VARCHAR(32) NULL,
lv_degree DECIMAL(3,2) NULL,
ix_degree INTEGER NULL,
tx_dt_from VARCHAR(30) NULL,
tx_dt_to VARCHAR(30) NULL,
CONSTRAINT pk_contact_education PRIMARY KEY (gu_contact,gu_degree),
CONSTRAINT f1_contact_education FOREIGN KEY (gu_degree) REFERENCES k_education_degree(gu_degree)
);
DROP FUNCTION k_sp_del_contact (CHAR);
CREATE FUNCTION k_sp_del_contact (CHAR) RETURNS INTEGER AS '
DECLARE
addr RECORD;
addrs text;
aCount INTEGER := 0;
bank RECORD;
banks text;
bCount INTEGER := 0;
GuWorkArea CHAR(32);
BEGIN
DELETE FROM k_contact_education WHERE gu_contact=$1;
DELETE FROM k_x_duty_resource WHERE nm_resource=$1;
DELETE FROM k_welcome_packs_changelog WHERE gu_pack IN (SELECT gu_pack FROM k_welcome_packs WHERE gu_contact=$1);
DELETE FROM k_welcome_packs WHERE gu_contact=$1;
DELETE FROM k_x_list_members WHERE gu_contact=$1;
DELETE FROM k_member_address WHERE gu_contact=$1;
DELETE FROM k_contacts_recent WHERE gu_contact=$1;
DELETE FROM k_x_group_contact WHERE gu_contact=$1;
SELECT gu_workarea INTO GuWorkArea FROM k_contacts WHERE gu_contact=$1;
FOR addr IN SELECT * FROM k_x_contact_addr WHERE gu_contact=$1 LOOP
aCount := aCount + 1;
IF 1=aCount THEN
addrs := quote_literal(addr.gu_address);
ELSE
addrs := addrs || chr(44) || quote_literal(addr.gu_address);
END IF;
END LOOP;
DELETE FROM k_x_contact_addr WHERE gu_contact=$1;
IF char_length(addrs)>0 THEN
EXECUTE ''DELETE FROM '' || quote_ident(''k_addresses'') || '' WHERE gu_address IN ('' || addrs || '')'';
END IF;
FOR bank IN SELECT * FROM k_x_contact_bank WHERE gu_contact=$1 LOOP
bCount := bCount + 1;
IF 1=bCount THEN
banks := quote_literal(bank.nu_bank_acc);
ELSE
banks := banks || chr(44) || quote_literal(bank.nu_bank_acc);
END IF;
END LOOP;
DELETE FROM k_x_contact_bank WHERE gu_contact=$1;
IF char_length(banks)>0 THEN
EXECUTE ''DELETE FROM '' || quote_ident(''k_bank_accounts'') || '' WHERE nu_bank_acc IN ('' || banks || '') AND gu_workarea='' || quote_literal(GuWorkArea);
END IF;
DELETE FROM k_oportunities_attrs WHERE gu_object IN (SELECT gu_oportunity FROM k_oportunities WHERE gu_contact=$1);
DELETE FROM k_oportunities WHERE gu_contact=$1;
DELETE FROM k_x_cat_objs WHERE gu_object=$1 AND id_class=90;
DELETE FROM k_x_contact_prods WHERE gu_contact=$1;
DELETE FROM k_contacts_attrs WHERE gu_object=$1;
DELETE FROM k_contact_notes WHERE gu_contact=$1;
DELETE FROM k_contacts WHERE gu_contact=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE VIEW v_contact_education_degree AS
SELECT d.gu_workarea, e.gu_contact, e.gu_degree, e.ix_degree, e.tp_degree, e.id_degree, d.nm_degree, e.lv_degree, e.dt_created, e.bo_completed, e.gu_institution, e.nm_center, e.tx_dt_from, e.tx_dt_to
FROM k_contact_education e, k_education_degree d
WHERE e.gu_degree = d.gu_degree;
CREATE TABLE k_oportunities_changelog (
gu_oportunity CHAR(32) NOT NULL,
nm_column VARCHAR(18) NOT NULL,
dt_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
gu_writer CHAR(32) NULL,
id_former_status VARCHAR(50) NULL,
id_new_status VARCHAR(50) NULL,
tx_value VARCHAR(1000) NULL
);
DROP FUNCTION k_sp_del_contact (CHAR);
DROP FUNCTION k_sp_del_company (CHAR) ;
CREATE FUNCTION k_sp_del_company (CHAR) RETURNS INTEGER AS '
DECLARE
addr RECORD;
addrs text;
aCount INTEGER := 0;
bank RECORD;
banks text;
bCount INTEGER := 0;
BEGIN
DELETE FROM k_x_duty_resource WHERE nm_resource=$1;
DELETE FROM k_welcome_packs_changelog WHERE gu_pack IN (SELECT gu_pack FROM k_welcome_packs WHERE gu_company=$1);
DELETE FROM k_welcome_packs WHERE gu_company=$1;
DELETE FROM k_x_list_members WHERE gu_company=$1;
DELETE FROM k_member_address WHERE gu_company=$1;
DELETE FROM k_companies_recent WHERE gu_company=$1;
DELETE FROM k_x_group_company WHERE gu_company=$1;
FOR addr IN SELECT * FROM k_x_company_addr WHERE gu_company=$1 LOOP
aCount := aCount + 1;
IF 1=aCount THEN
addrs := quote_literal(addr.gu_address);
ELSE
addrs := addrs || chr(44) || quote_literal(addr.gu_address);
END IF;
END LOOP;
DELETE FROM k_x_company_addr WHERE gu_company=$1;
IF char_length(addrs)>0 THEN
EXECUTE ''DELETE FROM '' || quote_ident(''k_addresses'') || '' WHERE gu_address IN ('' || addrs || '')'';
END IF;
FOR bank IN SELECT * FROM k_x_company_bank WHERE gu_company=$1 LOOP
bCount := bCount + 1;
IF 1=bCount THEN
banks := quote_literal(bank.nu_bank_acc);
ELSE
banks := banks || chr(44) || quote_literal(bank.nu_bank_acc);
END IF;
END LOOP;
DELETE FROM k_x_company_bank WHERE gu_company=$1;
IF char_length(banks)>0 THEN
EXECUTE ''DELETE FROM '' || quote_ident(''k_bank_accounts'') || '' WHERE nu_bank_acc IN ('' || banks || '') AND gu_workarea='' || quote_literal(GuWorkArea);
END IF;
/* Borrar las oportunidades */
DELETE FROM k_oportunities_changelog WHERE gu_oportunity IN (SELECT gu_oportunity FROM k_oportunities WHERE gu_company=$1);
DELETE FROM k_oportunities_attrs WHERE gu_object IN (SELECT gu_oportunity FROM k_oportunities WHERE gu_company=$1);
DELETE FROM k_oportunities WHERE gu_company=$1;
/* Borrar las referencias de PageSets */
UPDATE k_pagesets SET gu_company=NULL WHERE gu_company=$1;
/* Borrar el enlace con categorías */
DELETE FROM k_x_cat_objs WHERE gu_object=$1 AND id_class=91;
DELETE FROM k_x_company_prods WHERE gu_company=$1;
DELETE FROM k_companies_attrs WHERE gu_object=$1;
DELETE FROM k_companies WHERE gu_company=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION k_sp_del_oportunity (CHAR);
CREATE FUNCTION k_sp_del_oportunity (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_oportunities_changelog WHERE gu_oportunity=$1;
DELETE FROM k_oportunities_attrs WHERE gu_object=$1;
DELETE FROM k_oportunities WHERE gu_oportunity=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
ALTER TABLE k_mime_msgs ADD gu_job CHAR(32) NULL;
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class)
VALUES ('SMS','SEND SMS PUSH TEXT MESSAGE','com.knowgate.scheduler.jobs.SmsSender');
CREATE TABLE k_activities
(
gu_activity CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
tl_activity VARCHAR(100) NOT NULL,
dt_modified TIMESTAMP NULL,
bo_active SMALLINT DEFAULT 1,
gu_address CHAR(32) NULL,
gu_campaign CHAR(32) NULL,
gu_list CHAR(32) NULL,
gu_writer CHAR(32) NULL,
dt_start TIMESTAMP NULL,
dt_end TIMESTAMP NULL,
nu_capacity INTEGER NULL,
pr_sale DECIMAL(14,4) NULL,
pr_discount DECIMAL(14,4) NULL,
id_ref VARCHAR(50) NULL,
tx_dept VARCHAR(70) NULL,
de_activity VARCHAR(1000) NULL,
tx_comments VARCHAR(254) NULL,
CONSTRAINT pk_activities PRIMARY KEY (gu_activity),
CONSTRAINT u1_activities UNIQUE (gu_workarea,tl_activity),
CONSTRAINT c1_activities CHECK ((dt_start IS NULL AND dt_end IS NULL) OR dt_end IS NULL OR dt_end>=dt_start),
CONSTRAINT c2_activities CHECK (nu_capacity>=0),
CONSTRAINT c3_activities CHECK (pr_sale>=0),
CONSTRAINT c4_activities CHECK (pr_discount>=0)
);
CREATE TABLE k_x_activity_audience (
gu_activity CHAR(32) NOT NULL,
gu_address CHAR(32) NULL,
gu_contact CHAR(32) NULL,
gu_list CHAR(32) NULL,
gu_writer CHAR(32) NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt_modified TIMESTAMP NULL,
id_ref VARCHAR(50) NULL,
tp_origin VARCHAR(50) NULL,
bo_confirmed SMALLINT DEFAULT 0,
dt_confirmed TIMESTAMP NULL,
bo_paid SMALLINT DEFAULT 0,
dt_paid TIMESTAMP NULL,
im_paid DECIMAL(14,4) NULL,
id_transact VARCHAR(32) NULL,
tp_billing CHAR(1) NULL,
bo_went SMALLINT DEFAULT 0,
bo_allows_ads SMALLINT DEFAULT 0,
id_data1 VARCHAR(32) NULL,
de_data1 VARCHAR(100) NULL,
tx_data1 VARCHAR(254) NULL,
id_data2 VARCHAR(32) NULL,
de_data2 VARCHAR(100) NULL,
tx_data2 VARCHAR(254) NULL,
id_data3 VARCHAR(32) NULL,
de_data3 VARCHAR(100) NULL,
tx_data3 VARCHAR(254) NULL,
id_data4 VARCHAR(32) NULL,
de_data4 VARCHAR(100) NULL,
tx_data4 VARCHAR(254) NULL,
id_data5 VARCHAR(32) NULL,
de_data5 VARCHAR(100) NULL,
tx_data5 VARCHAR(254) NULL,
id_data6 VARCHAR(32) NULL,
de_data6 VARCHAR(100) NULL,
tx_data6 VARCHAR(254) NULL,
id_data7 VARCHAR(32) NULL,
de_data7 VARCHAR(100) NULL,
tx_data7 VARCHAR(254) NULL,
id_data8 VARCHAR(32) NULL,
de_data8 VARCHAR(100) NULL,
tx_data8 VARCHAR(254) NULL,
id_data9 VARCHAR(32) NULL,
de_data9 VARCHAR(100) NULL,
tx_data9 VARCHAR(254) NULL,
CONSTRAINT pk_x_activity_audience PRIMARY KEY (gu_activity,gu_contact)
);
CREATE FUNCTION k_sp_del_activity (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_x_activity_audience WHERE gu_activity=$1;
DELETE FROM k_addresses WHERE gu_address IN (SELECT gu_address FROM k_activities WHERE gu_activity=$1);
DELETE FROM k_activities WHERE gu_activity=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION k_sp_del_contact (CHAR) RETURNS INTEGER AS '
DECLARE
addr RECORD;
addrs text;
aCount INTEGER := 0;
bank RECORD;
banks text;
bCount INTEGER := 0;
GuWorkArea CHAR(32);
BEGIN
DELETE FROM k_x_activity_audience WHERE gu_contact=$1;
DELETE FROM k_contact_education WHERE gu_contact=$1;
DELETE FROM k_x_duty_resource WHERE nm_resource=$1;
DELETE FROM k_welcome_packs_changelog WHERE gu_pack IN (SELECT gu_pack FROM k_welcome_packs WHERE gu_contact=$1);
DELETE FROM k_welcome_packs WHERE gu_contact=$1;
DELETE FROM k_x_list_members WHERE gu_contact=$1;
DELETE FROM k_member_address WHERE gu_contact=$1;
DELETE FROM k_contacts_recent WHERE gu_contact=$1;
DELETE FROM k_x_group_contact WHERE gu_contact=$1;
SELECT gu_workarea INTO GuWorkArea FROM k_contacts WHERE gu_contact=$1;
FOR addr IN SELECT * FROM k_x_contact_addr WHERE gu_contact=$1 LOOP
aCount := aCount + 1;
IF 1=aCount THEN
addrs := quote_literal(addr.gu_address);
ELSE
addrs := addrs || chr(44) || quote_literal(addr.gu_address);
END IF;
END LOOP;
DELETE FROM k_x_contact_addr WHERE gu_contact=$1;
IF char_length(addrs)>0 THEN
EXECUTE ''DELETE FROM '' || quote_ident(''k_addresses'') || '' WHERE gu_address IN ('' || addrs || '')'';
END IF;
FOR bank IN SELECT * FROM k_x_contact_bank WHERE gu_contact=$1 LOOP
bCount := bCount + 1;
IF 1=bCount THEN
banks := quote_literal(bank.nu_bank_acc);
ELSE
banks := banks || chr(44) || quote_literal(bank.nu_bank_acc);
END IF;
END LOOP;
DELETE FROM k_x_contact_bank WHERE gu_contact=$1;
IF char_length(banks)>0 THEN
EXECUTE ''DELETE FROM '' || quote_ident(''k_bank_accounts'') || '' WHERE nu_bank_acc IN ('' || banks || '') AND gu_workarea='' || quote_literal(GuWorkArea);
END IF;
DELETE FROM k_oportunities_changelog WHERE gu_oportunity IN (SELECT gu_oportunity FROM k_oportunities WHERE gu_contact=$1);
DELETE FROM k_oportunities_attrs WHERE gu_object IN (SELECT gu_oportunity FROM k_oportunities WHERE gu_contact=$1);
DELETE FROM k_oportunities WHERE gu_contact=$1;
DELETE FROM k_x_cat_objs WHERE gu_object=$1 AND id_class=90;
DELETE FROM k_x_contact_prods WHERE gu_contact=$1;
DELETE FROM k_contacts_attrs WHERE gu_object=$1;
DELETE FROM k_contact_notes WHERE gu_contact=$1;
DELETE FROM k_contacts WHERE gu_contact=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION k_sp_del_list (CHAR);
CREATE FUNCTION k_sp_del_list (CHAR) RETURNS INTEGER AS '
DECLARE
tp SMALLINT;
wa CHAR(32);
bk CHAR(32);
BEGIN
SELECT tp_list,gu_workarea INTO tp,wa FROM k_lists WHERE gu_list=$1;
SELECT gu_list INTO bk FROM k_lists WHERE gu_workarea=wa AND gu_query=$1 AND tp_list=4;
IF FOUND THEN
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_contact FROM k_x_list_members WHERE gu_list=bk) AND gu_member NOT IN (SELECT x.gu_contact FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>bk);
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_company FROM k_x_list_members WHERE gu_list=bk) AND gu_member NOT IN (SELECT x.gu_company FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>bk);
DELETE FROM k_x_list_members WHERE gu_list=bk;
DELETE FROM k_x_campaign_lists WHERE gu_list=bk;
DELETE FROM k_lists WHERE gu_list=bk;
END IF;
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_contact FROM k_x_list_members WHERE gu_list=$1) AND gu_member NOT IN (SELECT x.gu_contact FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>$1);
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_company FROM k_x_list_members WHERE gu_list=$1) AND gu_member NOT IN (SELECT x.gu_company FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>$1);
DELETE FROM k_x_list_members WHERE gu_list=$1;
DELETE FROM k_x_campaign_lists WHERE gu_list=$1;
UPDATE k_activities SET gu_list=NULL WHERE gu_list=$1;
UPDATE k_x_activity_audience SET gu_list=NULL WHERE gu_list=$1;
DELETE FROM k_lists WHERE gu_list=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE k_activity_audience_lookup
(
gu_owner CHAR(32) NOT NULL,
id_section VARCHAR(30) NOT NULL,
pg_lookup INTEGER NOT NULL,
vl_lookup VARCHAR(255) NULL,
tr_es VARCHAR(50) NULL,
tr_en VARCHAR(50) NULL,
tr_de VARCHAR(50) NULL,
tr_it VARCHAR(50) NULL,
tr_fr VARCHAR(50) NULL,
tr_pt VARCHAR(50) NULL,
tr_ca VARCHAR(50) NULL,
tr_eu VARCHAR(50) NULL,
tr_ja VARCHAR(50) NULL,
tr_cn VARCHAR(50) NULL,
tr_tw VARCHAR(50) NULL,
tr_fi VARCHAR(50) NULL,
tr_ru VARCHAR(50) NULL,
tr_nl VARCHAR(50) NULL,
tr_th VARCHAR(50) NULL,
tr_cs VARCHAR(50) NULL,
tr_uk VARCHAR(50) NULL,
tr_no VARCHAR(50) NULL,
tr_ko VARCHAR(50) NULL,
tr_sk VARCHAR(50) NULL,
tr_pl VARCHAR(50) NULL,
tr_vn VARCHAR(50) NULL,
CONSTRAINT pk_activity_audience_lookup PRIMARY KEY (gu_owner,id_section,pg_lookup),
CONSTRAINT u1_activity_audience_lookup UNIQUE (gu_owner,id_section,vl_lookup)
);
ALTER TABLE k_invoice_payments ADD bo_active SMALLINT DEFAULT 1;
ALTER TABLE k_invoice_payments ADD id_country CHAR(3) NULL;
ALTER TABLE k_invoice_payments ADD id_authcode VARCHAR(6) NULL;
ALTER TABLE k_invoice_payments ADD dt_paid TIMESTAMP NULL;
ALTER TABLE k_invoice_payments ADD dt_expire TIMESTAMP NULL;
ALTER TABLE k_invoice_payments ADD id_ref VARCHAR(50) NULL;
ALTER TABLE k_invoice_payments ADD id_transact VARCHAR(50) NULL;
ALTER TABLE k_x_course_bookings ADD gu_invoice CHAR(32) NULL;
ALTER TABLE k_x_list_members ADD mov_phone VARCHAR(16) NULL;
CREATE SEQUENCE seq_k_transactions INCREMENT 1 MINVALUE 1 MAXVALUE 999999 START 1;
DROP VIEW v_contact_list;
DROP VIEW v_contact_address_title;
DROP VIEW v_contact_address;
DROP VIEW v_contact_company_all;
DROP VIEW v_contact_company;
DROP VIEW v_member_address;
DROP VIEW v_active_contact_address;
DROP VIEW v_contact_titles;
DROP VIEW v_company_address;
DROP VIEW v_active_company_address;
CREATE VIEW v_active_contact_address AS
SELECT x.gu_contact,a.gu_address,a.ix_address,a.gu_workarea,a.dt_created,a.bo_active,a.dt_modified,a.gu_user,a.tp_location,a.nm_company,a.tp_street,a.nm_street,a.nu_street,a.tx_addr1,a.tx_addr2,a.id_country,a.nm_country,a.id_state,a.nm_state,a.mn_city,a.zipcode,a.work_phone,a.direct_phone,a.home_phone,a.mov_phone,a.fax_phone,a.other_phone,a.po_box,a.tx_email,a.url_addr,a.coord_x,a.coord_y,a.contact_person,a.tx_salutation,a.tx_remarks FROM k_addresses a, k_x_contact_addr x WHERE a.gu_address=x.gu_address AND a.bo_active<>0;
CREATE OR REPLACE VIEW v_contact_company AS
SELECT c.gu_workarea, c.gu_contact, c.dt_modified, c.bo_private, c.gu_writer, y.gu_company, y.nm_legal, y.id_sector, y.tp_company, c.id_status, c.id_ref, c.tx_name, c.tx_surname, c.de_title, c.id_gender, c.dt_birth, c.ny_age, c.sn_passport, c.tp_passport, c.tx_dept, c.tx_division, c.nu_notes, c.nu_attachs, c.tx_comments, c.bo_restricted, c.gu_geozone, c.gu_sales_man
FROM k_contacts c, k_companies y
WHERE c.gu_company = y.gu_company;
CREATE OR REPLACE VIEW v_contact_company_all AS
SELECT c.gu_workarea, c.gu_contact, c.dt_modified, c.bo_private, c.gu_writer, c.gu_company, c.nm_legal, c.id_sector, c.tp_company, c.id_status, c.id_ref, c.tx_name, c.tx_surname, c.de_title, c.id_gender, c.dt_birth, c.ny_age, c.sn_passport, c.tp_passport, c.tx_dept, c.tx_division, c.nu_notes, c.nu_attachs, c.tx_comments, c.bo_restricted, c.gu_geozone, c.gu_sales_man
FROM v_contact_company c
UNION
SELECT c.gu_workarea, c.gu_contact, c.dt_modified, c.bo_private, c.gu_writer, NULL::"unknown" AS gu_company, NULL::"unknown" AS nm_legal, NULL::"unknown" AS id_sector, NULL::"unknown" AS tp_company, c.id_status, c.id_ref, c.tx_name, c.tx_surname, c.de_title, c.id_gender, c.dt_birth, c.ny_age, c.sn_passport, c.tp_passport, c.tx_dept, c.tx_division, c.nu_notes, c.nu_attachs, c.tx_comments, c.bo_restricted, c.gu_geozone, c.gu_sales_man
FROM k_contacts c
WHERE c.gu_company IS NULL;
CREATE OR REPLACE VIEW v_contact_titles AS
SELECT k_contacts_lookup.vl_lookup, k_contacts_lookup.gu_owner, k_contacts_lookup.tr_es, k_contacts_lookup.tr_en, k_contacts_lookup.tr_fr, k_contacts_lookup.tr_de, k_contacts_lookup.tr_it, k_contacts_lookup.tr_pt, k_contacts_lookup.tr_ja, k_contacts_lookup.tr_cn, k_contacts_lookup.tr_tw, k_contacts_lookup.tr_ca, k_contacts_lookup.tr_eu
FROM k_contacts_lookup
WHERE k_contacts_lookup.id_section::text = 'de_title'::text;
CREATE OR REPLACE VIEW v_contact_address AS
SELECT c.gu_workarea, c.gu_contact, c.dt_modified, c.bo_private, c.gu_writer, c.gu_company, c.nm_legal, c.id_sector, c.tp_company, c.id_status, c.id_ref, c.tx_name, c.tx_surname, c.de_title, l.tr_es AS tr_title, c.id_gender, c.dt_birth, c.ny_age, c.sn_passport, c.tp_passport, c.tx_dept, c.tx_division, c.nu_notes, c.nu_attachs, c.tx_comments, b.gu_address, b.ix_address, b.tp_location, b.tp_street, b.nu_street, b.nm_street, b.tx_addr1, b.tx_addr2, (COALESCE(b.tx_addr1, ''::character varying)::text || chr(10)) || COALESCE(b.tx_addr2, ''::character varying)::text AS full_addr, b.id_country, b.nm_country, b.id_state, b.nm_state, b.mn_city, b.zipcode, b.work_phone, b.direct_phone, b.home_phone, b.mov_phone, b.fax_phone, b.other_phone, b.po_box, b.tx_email, b.url_addr, b.contact_person, b.tx_remarks, c.bo_restricted, c.gu_geozone, c.gu_sales_man
FROM v_contact_company c
LEFT JOIN v_active_contact_address b ON c.gu_contact = b.gu_contact
LEFT JOIN v_contact_titles l ON l.vl_lookup::text = c.de_title::text AND l.gu_owner = c.gu_workarea
UNION
SELECT c.gu_workarea, c.gu_contact, c.dt_modified, c.bo_private, c.gu_writer, NULL::"unknown" AS gu_company, NULL::"unknown" AS nm_legal, NULL::"unknown" AS id_sector, NULL::"unknown" AS tp_company, c.id_status, c.id_ref, c.tx_name, c.tx_surname, c.de_title, l.tr_es AS tr_title, c.id_gender, c.dt_birth, c.ny_age, c.sn_passport, c.tp_passport, c.tx_dept, c.tx_division, c.nu_notes, c.nu_attachs, c.tx_comments, b.gu_address, b.ix_address, b.tp_location, b.tp_street, b.nu_street, b.nm_street, b.tx_addr1, b.tx_addr2, (COALESCE(b.tx_addr1, ''::character varying)::text || chr(10)) || COALESCE(b.tx_addr2, ''::character varying)::text AS full_addr, b.id_country, b.nm_country, b.id_state, b.nm_state, b.mn_city, b.zipcode, b.work_phone, b.direct_phone, b.home_phone, b.mov_phone, b.fax_phone, b.other_phone, b.po_box, b.tx_email, b.url_addr, b.contact_person, b.tx_remarks, c.bo_restricted, c.gu_geozone, c.gu_sales_man
FROM k_contacts c
LEFT JOIN v_active_contact_address b ON c.gu_contact = b.gu_contact
LEFT JOIN v_contact_titles l ON l.vl_lookup::text = c.de_title::text AND l.gu_owner = c.gu_workarea
WHERE c.gu_company IS NULL;
CREATE VIEW v_contact_address_title AS
SELECT b.*,l.gu_owner,l.id_section,l.pg_lookup,l.vl_lookup,l.tr_es,l.tr_en,tr_fr,tr_de,tr_it,tr_pt,tr_ja,tr_cn,tr_tw,tr_ca,tr_eu FROM v_contact_address b LEFT OUTER JOIN k_contacts_lookup l ON b.de_title=l.vl_lookup;
CREATE OR REPLACE VIEW v_contact_list AS
SELECT c.gu_contact, (COALESCE(c.tx_surname, ''::character varying)::text || ', '::text) || COALESCE(c.tx_name, ''::character varying)::text AS full_name, l.tr_es, l.tr_en, l.tr_fr, l.tr_de, l.tr_it, l.tr_pt, l.tr_ja, l.tr_cn, l.tr_tw, l.tr_ca, l.tr_eu, d.gu_company, d.nm_legal, c.nu_notes, c.nu_attachs, c.dt_modified, c.bo_private, c.gu_workarea, c.gu_writer, l.gu_owner, c.bo_restricted, c.gu_geozone, c.gu_sales_man
FROM k_contacts c
LEFT JOIN k_companies d ON c.gu_company = d.gu_company
LEFT JOIN k_contacts_lookup l ON l.vl_lookup::text = c.de_title::text AND l.gu_owner = c.gu_workarea
WHERE l.id_section::text = 'de_title'::text OR l.id_section IS NULL;
CREATE OR REPLACE VIEW v_active_company_address AS
SELECT x.gu_company, a.gu_address, a.ix_address, a.gu_workarea, a.dt_created, a.bo_active, a.dt_modified, a.gu_user, a.tp_location, a.nm_company, a.tp_street, a.nm_street, a.nu_street, a.tx_addr1, a.tx_addr2, a.id_country, a.nm_country, a.id_state, a.nm_state, a.mn_city, a.zipcode, a.work_phone, a.direct_phone, a.home_phone, a.mov_phone, a.fax_phone, a.other_phone, a.po_box, a.tx_email, a.url_addr, a.coord_x, a.coord_y, a.contact_person, a.tx_salutation, a.id_ref, a.tx_remarks
FROM k_addresses a, k_x_company_addr x
WHERE a.gu_address = x.gu_address AND a.bo_active <> 0;
CREATE OR REPLACE VIEW v_member_address AS
( SELECT k.gu_company, NULL::"unknown" AS gu_contact, k.dt_created, k.dt_modified, k.gu_workarea, 0::smallint AS bo_private, NULL::"unknown" AS gu_writer, NULL::"unknown" AS tx_name, NULL::"unknown" AS tx_surname, k.nm_commercial, k.nm_legal, k.id_legal, k.id_sector, NULL::"unknown" AS de_title, NULL::"unknown" AS tr_title, k.id_status, k.id_ref, k.dt_founded AS dt_birth, k.id_legal AS sn_passport, k.de_company AS tx_comments, 'C' AS id_gender, k.tp_company, k.nu_employees, k.im_revenue, k.gu_sales_man, k.tx_franchise, k.gu_geozone, NULL::"unknown" AS ny_age, NULL::"unknown" AS tx_dept, NULL::"unknown" AS tx_division, b.gu_address, b.ix_address, b.tp_location, b.tp_street, b.nm_street, b.nu_street, b.tx_addr1, b.tx_addr2, (COALESCE(b.tx_addr1, ''::character varying)::text || chr(10)) || COALESCE(b.tx_addr2, ''::character varying)::text AS full_addr, b.id_country, b.nm_country, b.id_state, b.nm_state, b.mn_city, b.zipcode, b.work_phone, b.direct_phone, b.home_phone, b.mov_phone, b.fax_phone, b.other_phone, b.po_box, b.tx_email, b.url_addr, b.contact_person, b.tx_salutation, b.tx_remarks
FROM k_companies k, v_active_company_address b
WHERE k.gu_company = b.gu_company
UNION
SELECT y.gu_company, c.gu_contact, c.dt_created, c.dt_modified, c.gu_workarea, c.bo_private, c.gu_writer, c.tx_name, c.tx_surname, y.nm_commercial, y.nm_legal, y.id_legal, y.id_sector, c.de_title, l.tr_es AS tr_title, c.id_status, c.id_ref, c.dt_birth, c.sn_passport, c.tx_comments, c.id_gender, y.tp_company, y.nu_employees, y.im_revenue, y.gu_sales_man, y.tx_franchise, COALESCE(c.gu_geozone, y.gu_geozone) AS gu_geozone, c.ny_age, c.tx_dept, c.tx_division, b.gu_address, b.ix_address, b.tp_location, b.tp_street, b.nm_street, b.nu_street, b.tx_addr1, b.tx_addr2, (COALESCE(b.tx_addr1, ''::character varying)::text || chr(10)) || COALESCE(b.tx_addr2, ''::character varying)::text AS full_addr, b.id_country, b.nm_country, b.id_state, b.nm_state, b.mn_city, b.zipcode, b.work_phone, b.direct_phone, b.home_phone, b.mov_phone, b.fax_phone, b.other_phone, b.po_box, b.tx_email, b.url_addr, b.contact_person, b.tx_salutation, b.tx_remarks
FROM k_companies y, v_active_contact_address b, k_contacts c
LEFT JOIN v_contact_titles l ON l.vl_lookup::text = c.de_title::text AND l.gu_owner = c.gu_workarea
WHERE c.gu_contact = b.gu_contact AND y.gu_company = c.gu_company)
UNION
SELECT NULL::"unknown" AS gu_company, c.gu_contact, c.dt_created, c.dt_modified, c.gu_workarea, c.bo_private, c.gu_writer, c.tx_name, c.tx_surname, NULL::"unknown" AS nm_commercial, NULL::"unknown" AS nm_legal, NULL::"unknown" AS id_legal, NULL::"unknown" AS id_sector, c.de_title, l.tr_es AS tr_title, c.id_status, c.id_ref, c.dt_birth, c.sn_passport, c.tx_comments, c.id_gender, NULL::"unknown" AS tp_company, 0 AS nu_employees, 0::double precision AS im_revenue, NULL::"unknown" AS gu_sales_man, NULL::"unknown" AS tx_franchise, c.gu_geozone, c.ny_age, c.tx_dept, c.tx_division, b.gu_address, b.ix_address, b.tp_location, b.tp_street, b.nm_street, b.nu_street, b.tx_addr1, b.tx_addr2, (COALESCE(b.tx_addr1, ''::character varying)::text || chr(10)) || COALESCE(b.tx_addr2, ''::character varying)::text AS full_addr, b.id_country, b.nm_country, b.id_state, b.nm_state, b.mn_city, b.zipcode, b.work_phone, b.direct_phone, b.home_phone, b.mov_phone, b.fax_phone, b.other_phone, b.po_box, b.tx_email, b.url_addr, b.contact_person, b.tx_salutation, b.tx_remarks
FROM v_active_contact_address b, k_contacts c
LEFT JOIN v_contact_titles l ON l.vl_lookup::text = c.de_title::text AND l.gu_owner = c.gu_workarea
WHERE c.gu_contact = b.gu_contact AND c.gu_company IS NULL;
ALTER TABLE k_companies ADD id_bpartner VARCHAR(32) NULL;
ALTER TABLE k_contacts ADD id_bpartner VARCHAR(32) NULL;
ALTER TABLE k_suppliers ADD id_bpartner VARCHAR(32) NULL;
ALTER TABLE k_sales_men ADD id_bpartner VARCHAR(32) NULL;
ALTER TABLE k_bugs_attach ADD pg_bug_track INTEGER NULL;
CREATE SEQUENCE seq_k_bugs_track INCREMENT 1 START 1;
DROP FUNCTION k_sp_del_bug (CHAR);
CREATE FUNCTION k_sp_del_bug (CHAR) RETURNS INTEGER AS '
BEGIN
UPDATE k_bugs SET gu_bug_ref=NULL WHERE gu_bug_ref=$1;
DELETE FROM k_bugs_track WHERE gu_bug=$1;
DELETE FROM k_bugs_changelog WHERE gu_bug=$1;
DELETE FROM k_bugs_attach WHERE gu_bug=$1;
DELETE FROM k_bugs WHERE gu_bug=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE SEQUENCE seq_k_adhoc_mailings INCREMENT 1 MINVALUE 1 START 1;
CREATE TABLE k_adhoc_mailings (
gu_mailing CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
gu_writer CHAR(32) NOT NULL,
pg_mailing INTEGER NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
nm_mailing VARCHAR(30) NOT NULL,
bo_html_part SMALLINT NOT NULL,
bo_plain_part SMALLINT NOT NULL,
bo_attachments SMALLINT NOT NULL,
id_status VARCHAR(30) NULL,
dt_modified TIMESTAMP NULL,
dt_execution TIMESTAMP NULL,
tx_email_from VARCHAR(254) NULL,
tx_email_reply VARCHAR(254) NULL,
nm_from VARCHAR(254) NULL,
tx_subject VARCHAR(254) NULL,
tx_allow_regexp VARCHAR(254) NULL,
tx_deny_regexp VARCHAR(254) NULL,
tx_parameters VARCHAR(2000) NULL,
CONSTRAINT pk_adhoc_mailings PRIMARY KEY (gu_mailing),
CONSTRAINT u1_adhoc_mailings UNIQUE (pg_mailing),
CONSTRAINT u2_adhoc_mailings UNIQUE (nm_mailing)
);
CREATE TABLE k_adhoc_mailings_lookup
(
gu_owner CHAR(32) NOT NULL,
id_section VARCHAR(30) NOT NULL,
pg_lookup INTEGER NOT NULL,
vl_lookup VARCHAR(255) NULL,
tr_es VARCHAR(50) NULL,
tr_en VARCHAR(50) NULL,
tr_de VARCHAR(50) NULL,
tr_it VARCHAR(50) NULL,
tr_fr VARCHAR(50) NULL,
tr_pt VARCHAR(50) NULL,
tr_ca VARCHAR(50) NULL,
tr_gl VARCHAR(50) NULL,
tr_eu VARCHAR(50) NULL,
tr_ja VARCHAR(50) NULL,
tr_cn VARCHAR(50) NULL,
tr_tw VARCHAR(50) NULL,
tr_fi VARCHAR(50) NULL,
tr_ru VARCHAR(50) NULL,
tr_nl VARCHAR(50) NULL,
tr_th VARCHAR(50) NULL,
tr_cs VARCHAR(50) NULL,
tr_uk VARCHAR(50) NULL,
tr_no VARCHAR(50) NULL,
tr_ko VARCHAR(50) NULL,
tr_sk VARCHAR(50) NULL,
tr_pl VARCHAR(50) NULL,
tr_vn VARCHAR(50) NULL,
CONSTRAINT pk_adhoc_mailings_lookup PRIMARY KEY (gu_owner,id_section,pg_lookup),
CONSTRAINT u1_adhoc_mailings_lookup UNIQUE (gu_owner,id_section,vl_lookup)
);
CREATE VIEW v_pagesets_mailings AS
SELECT
p.gu_pageset,p.gu_workarea,p.nm_pageset,p.tx_comments,p.path_data,p.dt_created,m.nm_microsite,p.id_status,p.id_language,m.id_app
FROM k_pagesets p,k_microsites m WHERE p.gu_microsite=m.gu_microsite OR p.gu_microsite IS NULL
UNION
SELECT
a.gu_mailing AS gu_pageset,a.gu_workarea,a.nm_mailing AS nm_pageset,a.tx_parameters AS tx_comments ,'Hipermail' AS path_data,a.dt_created,'AdHoc' AS nm_microsite,a.id_status,'' AS id_language,21 AS id_app
FROM k_adhoc_mailings a;
ALTER TABLE k_jobs DROP CONSTRAINT f7_jobs;
CREATE TABLE k_global_black_list
(
id_domain INTEGER NOT NULL,
gu_workarea CHAR(32) NOT NULL,
tx_email VARCHAR(100) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
tx_name VARCHAR(100) NULL,
tx_surname VARCHAR(100) NULL,
gu_contact CHAR(32) NULL,
gu_address CHAR(32) NULL,
CONSTRAINT pk_global_black_list PRIMARY KEY (id_domain,gu_workarea,tx_email)
);
| |
reply
|
|
RE: RE: RE: RE: RE: RE: Hipergate 5.5
Author: EIPSISTEMAS | 2010-07-28 04:46:46 |
| 500-550.sql
Suponiendo que nuestra base de datos se llame hipergate y estemos utilizando el usuario postgres, y la autenticacion para solicitudes locales es trust, ejecutar:
psql -U postgres hipergate < 500-550.sql
=======================================================
UPDATE k_version SET vs_stamp='5.5.0';
ALTER TABLE k_version ADD bo_allow_stats SMALLINT DEFAULT 0;
DROP SEQUENCE seq_k_adhoc_mailings;
CREATE SEQUENCE seq_k_adhoc_mailings INCREMENT 1 START 1;
ALTER TABLE k_activities ADD gu_meeting CHAR(32) NULL;
ALTER TABLE k_activities ADD gu_pageset CHAR(32) NULL;
ALTER TABLE k_activities ADD gu_mailing CHAR(32) NULL;
ALTER TABLE k_activities ADD dt_mailing TIMESTAMP NULL;
ALTER TABLE k_activities ADD tx_subject VARCHAR(254) NULL;
ALTER TABLE k_activities ADD tx_email_from VARCHAR(254) NULL;
ALTER TABLE k_activities ADD nm_from VARCHAR(254) NULL;
ALTER TABLE k_activities ADD url_activity VARCHAR(254) NULL;
ALTER TABLE k_activities ADD nm_author VARCHAR(200) NULL;
ALTER TABLE k_activities ADD pg_activity INTEGER NULL;
ALTER TABLE k_activities ADD id_language CHAR(2) NULL;
ALTER TABLE k_adhoc_mailings ADD bo_urgent SMALLINT DEFAULT 0;
ALTER TABLE k_adhoc_mailings ADD bo_reminder SMALLINT DEFAULT 0;
ALTER TABLE k_pagesets ADD bo_urgent SMALLINT DEFAULT 0;
ALTER TABLE k_contacts ADD url_linkedin VARCHAR(254) NULL;
ALTER TABLE k_contacts ADD url_facebook VARCHAR(254) NULL;
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class) VALUES ('NOPO','NEW OPORTUNITY','com.knowgate.scheduler.events.DoNothing');
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class) VALUES ('MOPO','MODIFY OPORTUNITY','com.knowgate.scheduler.events.DoNothing');
ALTER TABLE k_prod_attr ADD format VARCHAR(50) NULL;
ALTER TABLE k_education_degree ADD id_country CHAR(3) NULL;
ALTER TABLE k_contact_education ADD pg_product INTEGER NULL;
ALTER TABLE k_contact_education ADD gu_product CHAR(32);
CREATE TABLE k_oportunities_attachs
(
gu_oportunity CHAR(32) NOT NULL,
pg_product INTEGER NOT NULL,
gu_product CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
gu_writer CHAR(32) NOT NULL,
CONSTRAINT pk_oportunities_attachs PRIMARY KEY (gu_oportunity,pg_product)
)
;
CREATE TABLE k_x_adhoc_mailing_list (
gu_list CHAR(32) NOT NULL,
gu_mailing CHAR(32) NOT NULL,
CONSTRAINT pk_x_adhoc_mailing_list PRIMARY KEY (gu_list,gu_mailing)
)
;
DROP FUNCTION k_sp_del_list(CHAR);
CREATE FUNCTION k_sp_del_list (CHAR) RETURNS INTEGER AS '
DECLARE
tp SMALLINT;
wa CHAR(32);
bk CHAR(32);
BEGIN
SELECT tp_list,gu_workarea INTO tp,wa FROM k_lists WHERE gu_list=$1;
SELECT gu_list INTO bk FROM k_lists WHERE gu_workarea=wa AND gu_query=$1 AND tp_list=4;
IF FOUND THEN
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_contact FROM k_x_list_members WHERE gu_list=bk) AND gu_member NOT IN (SELECT x.gu_contact FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>bk);
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_company FROM k_x_list_members WHERE gu_list=bk) AND gu_member NOT IN (SELECT x.gu_company FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>bk);
DELETE FROM k_x_list_members WHERE gu_list=bk;
DELETE FROM k_x_campaign_lists WHERE gu_list=bk;
DELETE FROM k_x_adhoc_mailing_list WHERE gu_list=bk;
DELETE FROM k_lists WHERE gu_list=bk;
END IF;
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_contact FROM k_x_list_members WHERE gu_list=$1) AND gu_member NOT IN (SELECT x.gu_contact FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>$1);
DELETE FROM k_list_members WHERE gu_member IN (SELECT gu_company FROM k_x_list_members WHERE gu_list=$1) AND gu_member NOT IN (SELECT x.gu_company FROM k_x_list_members x, k_lists l WHERE x.gu_list=l.gu_list AND l.gu_workarea=wa AND x.gu_list<>$1);
DELETE FROM k_x_list_members WHERE gu_list=$1;
DELETE FROM k_x_campaign_lists WHERE gu_list=$1;
DELETE FROM k_x_adhoc_mailing_list WHERE gu_list=$1;
UPDATE k_activities SET gu_list=NULL WHERE gu_list=$1;
UPDATE k_x_activity_audience SET gu_list=NULL WHERE gu_list=$1;
DELETE FROM k_lists WHERE gu_list=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION k_sp_del_adhoc_mailing (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_x_adhoc_mailing_list WHERE gu_mailing=$1;
DELETE FROM k_adhoc_mailings WHERE gu_mailing=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE k_activity_attachs
(
gu_activity CHAR(32) NOT NULL,
pg_product INTEGER NOT NULL,
gu_product CHAR(32) NOT NULL,
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
gu_writer CHAR(32) NOT NULL,
CONSTRAINT pk_activity_attachs PRIMARY KEY (gu_activity,pg_product)
)
;
DROP FUNCTION k_sp_del_activity(bpchar);
CREATE FUNCTION k_sp_del_activity (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_activity_attachs WHERE gu_activity=$1;
DELETE FROM k_x_activity_audience WHERE gu_activity=$1;
DELETE FROM k_activities WHERE gu_activity=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE VIEW v_activity_locat AS
SELECT p.gu_product, p.nm_product, p.de_product, c.gu_activity, c.pg_product, c.dt_created, l.dt_modified, l.dt_uploaded, l.gu_location, l.id_cont_type, l.id_prod_type, l.len_file, l.xprotocol, l.xhost, l.xport, l.xpath, l.xfile, l.xoriginalfile, l.xanchor, l.status, l.vs_stamp, l.tx_email, l.tag_prod_locat
FROM k_activity_attachs c, k_products p, k_prod_locats l
WHERE c.gu_product=p.gu_product AND c.gu_product=l.gu_product;
DROP FUNCTION k_sp_del_product(CHAR);
CREATE FUNCTION k_sp_del_product (CHAR) RETURNS INTEGER AS '
DECLARE
GuAddress CHAR(32);
BEGIN
SELECT gu_address INTO GuAddress FROM k_products WHERE gu_product=$1;
DELETE FROM k_images WHERE gu_product=$1;
DELETE FROM k_x_cat_objs WHERE gu_object=$1;
DELETE FROM k_prod_keywords WHERE gu_product=$1;
DELETE FROM k_prod_fares WHERE gu_product=$1;
DELETE FROM k_prod_attrs WHERE gu_object=$1;
DELETE FROM k_prod_attr WHERE gu_product=$1;
DELETE FROM k_prod_locats WHERE gu_product=$1;
DELETE FROM k_products WHERE gu_product=$1;
IF GuAddress IS NOT NULL THEN
UPDATE k_academic_courses SET gu_address=NULL WHERE gu_acourse=$1;
DELETE FROM k_addresses WHERE gu_address=GuAddress;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION k_sp_del_acourse(CHAR);
CREATE FUNCTION k_sp_del_acourse (CHAR) RETURNS INTEGER AS '
DECLARE
GuAddress CHAR(32);
BEGIN
SELECT gu_address INTO GuAddress FROM k_academic_courses WHERE gu_acourse=$1;
DELETE FROM k_x_course_alumni WHERE gu_acourse=$1;
DELETE FROM k_x_course_bookings WHERE gu_acourse=$1;
DELETE FROM k_evaluations WHERE gu_acourse=$1;
DELETE FROM k_absentisms WHERE gu_acourse=$1;
DELETE FROM k_academic_courses WHERE gu_acourse=$1;
IF GuAddress IS NOT NULL THEN
DELETE FROM k_addresses WHERE gu_address=GuAddress;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE k_admission (
gu_admission CHAR(32) NOT NULL,
gu_contact CHAR(32) NOT NULL,
gu_oportunity CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
gu_acourse CHAR(32) NOT NULL,
id_objetive_1 VARCHAR(50) NULL, /*Program in which admission sought 1*/
id_objetive_2 VARCHAR(50) NULL, /*Program in which admission sought 2*/
id_objetive_3 VARCHAR(50) NULL, /*Program in which admission sought 3*/
dt_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,/*admission application date*/
dt_target TIMESTAMP NULL, /*Target date for the admission test*/
is_call SMALLINT NULL, /*Call Meeting (Yes) or Special (No) admission test*/
id_place VARCHAR(50) NULL, /*Place of entrance examinations*/
id_interviewer VARCHAR(50) NULL, /*Name of interviewer*/
dt_interview TIMESTAMP NULL, /*Date of completion of the interview*/
dt_admision_test TIMESTAMP NULL, /*Actual date for the admission test*/
is_grant SMALLINT NULL, /*Grant Request (Yes / No)*/
nu_grant DECIMAL(4,2) NULL, /*The amount or percentage of scholarship*/
nu_interview INTEGER NULL, /*points interview */
nu_vips INTEGER NULL, /*points vips */
nu_nips INTEGER NULL, /*points nips */
nu_elp INTEGER NULL, /*points elp */
nu_total INTEGER NULL, /*points total */
id_test_result VARCHAR(50) NULL, /*Test result (Admitted, admitted conditionally, not supported)*/
CONSTRAINT pk_admission PRIMARY KEY (gu_admission),
CONSTRAINT u_admission UNIQUE (gu_contact,gu_oportunity)
);
CREATE TABLE k_admission_lookup
(
gu_owner CHAR(32) NOT NULL,
id_section VARCHAR(30) NOT NULL,
pg_lookup INTEGER NOT NULL,
vl_lookup VARCHAR(50) NULL,
tr_es VARCHAR(50) NULL,
tr_en VARCHAR(50) NULL,
tr_de VARCHAR(50) NULL,
tr_it VARCHAR(50) NULL,
tr_fr VARCHAR(50) NULL,
tr_pt VARCHAR(50) NULL,
tr_ca VARCHAR(50) NULL,
tr_gl VARCHAR(50) NULL,
tr_eu VARCHAR(50) NULL,
tr_ja VARCHAR(50) NULL,
tr_cn VARCHAR(50) NULL,
tr_tw VARCHAR(50) NULL,
tr_fi VARCHAR(50) NULL,
tr_ru VARCHAR(50) NULL,
tr_nl VARCHAR(50) NULL,
tr_th VARCHAR(50) NULL,
tr_cs VARCHAR(50) NULL,
tr_uk VARCHAR(50) NULL,
tr_no VARCHAR(50) NULL,
tr_sk VARCHAR(50) NULL,
tr_pl VARCHAR(50) NULL,
tr_vn VARCHAR(50) NULL,
CONSTRAINT pk_admission_lookup PRIMARY KEY (gu_owner,id_section,pg_lookup)
);
DROP VIEW v_pagesets_mailings;
CREATE VIEW v_pagesets_mailings AS
(SELECT
p.gu_pageset,p.gu_workarea,p.nm_pageset,p.tx_comments,p.path_data,p.dt_created,m.nm_microsite,p.id_status,p.id_language,m.id_app,p.bo_urgent,NULL AS dt_execution
FROM k_pagesets p,k_microsites m WHERE p.gu_microsite=m.gu_microsite OR p.gu_microsite IS NULL)
UNION
(SELECT
a.gu_mailing AS gu_pageset,a.gu_workarea,a.nm_mailing AS nm_pageset,a.tx_subject AS tx_comments ,'Hipermail' AS path_data,a.dt_created,'AdHoc' AS nm_microsite,a.id_status,'' AS id_language,21 AS id_app,a.bo_urgent,a.dt_execution
FROM k_adhoc_mailings a);
DELETE FROM k_lu_job_commands WHERE id_command='SMS';
INSERT INTO k_lu_job_commands (id_command,tx_command,nm_class) VALUES ('SMS','SEND SMS PUSH TEXT MESSAGE','com.knowgate.scheduler.jobs.SMSSender');
CREATE TABLE k_urls
(
gu_url CHAR(32) NOT NULL,
gu_workarea CHAR(32) NOT NULL,
url_addr VARCHAR(2000) NOT NULL,
tx_title VARCHAR(2000) NULL,
de_url VARCHAR(2000) NULL,
CONSTRAINT pk_urls PRIMARY KEY(gu_url,gu_workarea)
);
CREATE TABLE k_job_atoms_clicks
(
gu_job CHAR(32) NOT NULL,
pg_atom INTEGER NOT NULL,
gu_url CHAR(32) NOT NULL,
dt_action TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
id_status SMALLINT DEFAULT 1,
gu_company CHAR(32) NULL,
gu_contact CHAR(32) NULL,
ip_addr VARCHAR(16) NULL,
tx_email VARCHAR(100) NULL
);
DROP FUNCTION k_sp_del_job(CHAR);
CREATE FUNCTION k_sp_del_job (CHAR) RETURNS INTEGER AS '
BEGIN
DELETE FROM k_job_atoms_clicks WHERE gu_job=$1;
DELETE FROM k_job_atoms_tracking WHERE gu_job=$1;
DELETE FROM k_job_atoms_archived WHERE gu_job=$1;
DELETE FROM k_job_atoms WHERE gu_job=$1;
DELETE FROM k_jobs WHERE gu_job=$1;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE TABLE k_bulkloads (
pg_bulkload INTEGER NOT NULL,
dt_uploaded TIMESTAMP NOT NULL,
gu_workarea CHAR(32) NOT NULL,
nm_file VARCHAR(254) NOT NULL,
id_batch VARCHAR(32) NULL,
id_status VARCHAR(30) NULL,
dt_processed TIMESTAMP NOT NULL,
nu_lines INTEGER DEFAULT 0,
nu_successful INTEGER DEFAULT 0,
nu_errors INTEGER DEFAULT 0,
CONSTRAINT pk_bulkloads PRIMARY KEY(pg_bulkload),
CONSTRAINT u1_bulkloads UNIQUE(dt_uploaded,gu_workarea,nm_file)
); | |
reply
|
|
|