Installing eZ Newsletter in an existing site

Tuesday 31 July 2007 8:56:54 am

Hi,

I'm tyring to install eZ Newsletter in an existing site. The database is postgres.

First, I try to install manually the packages, and convert mysql queries into postgres syntax. The newsletter_editor site works, I can send mails, but links in mails don't work : the site 'fre', 'eng' or 'gre' show a standard page for editing my newsletter, after login.

Then I try to use a normal newsletter install, and just edit site.iniS to use the database of the principal site. I have exactly the same problem. If I switch back and use the normal database, all works.

What can I do ??

Thanks.

Wednesday 01 August 2007 3:52:58 am

HI,

> first it would be interresting which customizations you needed to do for postgres. Would be nice when you could send that info to me.

Ok, just wait I found them back (I can't do this today)

For your solution: It's solved the problem of wrongs URL in mails, thanks, but when I test I changed the url to have the right ez Publish instance.

So it's fix one of a minor problem, but not the problem with false designs for the 'fre/gre/eng' site.

Regards,

Wednesday 01 August 2007 1:30:08 pm

I added a task for writing this documentation to the issue tracker [1], you might want to subscribe to it. Its planned to be ready at the end of the month.

[1] http://issues.ez.no/IssueView.php?Id=11219

Wednesday 01 August 2007 11:54:21 pm

Here is my SQL code who <i>should</i> work :

CREATE SEQUENCE "ezmailimport_id_s" ;

CREATE TABLE  "ezmailimport" (
  "id" integer DEFAULT nextval('"ezmailimport_id_s"') NOT NULL,
  "mail_id"   int NOT NULL default '0',
  "attachment_filename"   text,
  "original_filename"   text,
  primary key ("id")
);

BEGIN;


CREATE TABLE ezx_approve2_event (
 workflowevent_id INT4 NOT NULL DEFAULT '0',
 workflowevent_version INT4 NOT NULL DEFAULT '0',
 selected_sections varchar(255) NOT NULL DEFAULT '',
 approve_users varchar(255) NOT NULL DEFAULT '',
 approve_groups varchar(255) NOT NULL DEFAULT '',
 approve_type INT4 NOT NULL DEFAULT '0',
 selected_usergroups varchar(255) NOT NULL DEFAULT '',
 allow_add_approver INT4 NOT NULL DEFAULT '0',
 num_approve_users INT4 NOT NULL DEFAULT '0',
 require_all_approve INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY( workflowevent_id, workflowevent_version ) );

--
-- Sequences for table EZX_APPROVE_STATUS
--

CREATE SEQUENCE ezx_approve_status_id_s;

CREATE TABLE ezx_approve_status (
 id INT4 DEFAULT nextval('ezx_approve_status_id_s'),
 step INT4 NOT NULL DEFAULT '0',
 contentobject_id INT4 NOT NULL DEFAULT '0',
 contentobject_status INT4 NOT NULL DEFAULT '0',
 approve_status INT4 NOT NULL DEFAULT '0',
 active_version INT4 NOT NULL DEFAULT '0',
 locked_version INT4 NOT NULL DEFAULT '0',
 locked INT4 NOT NULL DEFAULT '0',
 collaborationitem_id INT4 NOT NULL DEFAULT '0',
 locked_user_id INT4 NOT NULL DEFAULT '0',
 started INT4 NOT NULL DEFAULT '0',
 ended INT4 NOT NULL DEFAULT '0',
 workflowprocess_id INT4 NOT NULL DEFAULT '0',
 event_position INT4 NOT NULL DEFAULT '0',
 required_num_approvers INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY( id, step ) );

--
-- Sequences for table EZX_APPROVE_STATUS_USER_LINK
--

--CREATE SEQUENCE ezx_approve_status_user_link;
CREATE SEQUENCE ezx_approve_status_user_link_s;

CREATE TABLE ezx_approve_status_user_link (
 id INT4 DEFAULT nextval('ezx_approve_status_user_link_s'),
 approve_id INT4 NOT NULL DEFAULT '0',
 approve_status INT4 NOT NULL DEFAULT '0',
 approve_role INT4 NOT NULL DEFAULT '0',
 hash char(32) NOT NULL DEFAULT '',
 message_link_created INT4 NOT NULL DEFAULT '0',
 user_id INT4 NOT NULL DEFAULT '0',
 action INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY( id ) );

--
-- Sequences for table EZX_APPROVE_STATUS_USER_LINK
--
SELECT SETVAL('ezx_approve_status_user_link_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezx_approve_status_user_link));

--
-- Sequences for table EZX_APPROVE_STATUS
--

SELECT SETVAL('ezx_approve_status_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezx_approve_status));

COMMIT;


BEGIN;

CREATE TABLE ezlabel (
 id INT4 NOT NULL,
 owner_id INT4 NOT NULL DEFAULT '0',
 creator_id INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 name varchar(255) NOT NULL DEFAULT '',
 PRIMARY KEY (id)

);

CREATE TABLE ezlabel_object (
 label_id INT4 NOT NULL DEFAULT '0',
 contentobject_id INT4 NOT NULL DEFAULT '0',
 creator_id INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY (label_id,contentobject_id)

);


COMMIT;


BEGIN;

--
-- Sequences for table EZSUBSCRIPTION_GROUP
--

CREATE SEQUENCE ezsubscription_group_id_s;

;
CREATE TABLE ezsubscription_group (
 id INT4 DEFAULT nextval('ezsubscription_group_id_s'),
 status INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 creator_id INT4 NOT NULL DEFAULT '0',
 name varchar(255) NOT NULL DEFAULT '',
 description text NOT NULL,
 subscriptionlistid_list text NOT NULL,
 PRIMARY KEY (id,status)

);

;

--
-- Sequences for table EZSUBSCRIPTION_LIST
--

CREATE SEQUENCE ezsubscription_list_id_s;

CREATE TABLE ezsubscription_list (
 id INT4 DEFAULT nextval('ezsubscription_list_id_s'),
 name varchar(255) NOT NULL DEFAULT '',
 url_type INT4 NOT NULL DEFAULT '0',
 url varchar(255) NOT NULL DEFAULT '',
 description text NOT NULL,
 allow_anonymous INT4 NOT NULL DEFAULT '0',
 login_steps INT4 NOT NULL DEFAULT '1',
 require_password INT4 NOT NULL DEFAULT '1',
 auto_confirm_registered INT4 NOT NULL DEFAULT '1',
 auto_approve_registered INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 creator_id INT4 NOT NULL DEFAULT '0',
 related_object_id_1 INT4 DEFAULT '0',
 related_object_id_2 INT4 DEFAULT '0',
 related_object_id_3 INT4 DEFAULT '0',
 status INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY (id,status)

);

;
CREATE TABLE ez_newsletter_subscription (
 newsletter_id INT4 NOT NULL DEFAULT '0',
 status INT4 NOT NULL DEFAULT '0',
 subscription_id INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY (newsletter_id,status,subscription_id)

);

;

--
-- Sequences for table EZNEWSLETTERTYPE
--

CREATE SEQUENCE eznewslettertype_id_s;

CREATE TABLE eznewslettertype (
 id INT4 DEFAULT nextval('eznewslettertype_id_s'),
 name varchar(255) DEFAULT NULL,
 subscriptionlist_list_id INT4 DEFAULT '0',
 contentclass_list varchar(255) DEFAULT '',
 inbox_id INT4 DEFAULT '0',
 sender_address varchar(255) DEFAULT '',
 description text NOT NULL,
 defaultsubscriptionlist_id INT4 DEFAULT '0',
 allowed_output_formats varchar(255) DEFAULT '',
 allowed_designs varchar(255) DEFAULT NULL,
 digest_settings INT4 DEFAULT '0',
 related_object_id_1 INT4 DEFAULT '0',
 related_object_id_2 INT4 DEFAULT '0',
 related_object_id_3 INT4 DEFAULT '0',
 article_pool_object_id INT4 DEFAULT '0',
 status INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 send_date_modifier INT4 NOT NULL DEFAULT '0',
 creator_id INT4 NOT NULL DEFAULT '0',
 personalise INT4 NOT NULL DEFAULT '1',
 Pretext text NOT NULL,
 Posttext text NOT NULL,
 PRIMARY KEY (id,status)

);

;

--
-- Sequences for table EZSENDNEWSLETTERITEM
--

CREATE SEQUENCE ezsendnewsletteritem_id_s;

CREATE TABLE ezsendnewsletteritem (
 id INT4 DEFAULT nextval('ezsendnewsletteritem_id_s'),
 newsletter_id INT4 NOT NULL DEFAULT '0',
 subscription_id INT4 NOT NULL DEFAULT '0',
 send_status INT4 NOT NULL DEFAULT '0',
 send_ts INT4 NOT NULL DEFAULT '0',
 hash varchar(32) DEFAULT '',
 bounce_id INT4 NOT NULL DEFAULT '0',
 object_read_ids text,
 object_print_ids text,
 PRIMARY KEY (id)

);

;

--
-- Sequences for table EZSUBSCRIPTION
--

CREATE SEQUENCE ezsubscription_id_s;

CREATE TABLE ezsubscription (
 id INT4 DEFAULT nextval('ezsubscription_id_s'),
 version_status INT4 NOT NULL DEFAULT '0',
 subscriptionlist_id INT4 DEFAULT '0',
 email varchar(255) DEFAULT '',
 hash varchar(255) DEFAULT '',
 status INT4 DEFAULT '0',
 vip INT4 DEFAULT '0',
 last_active INT4 NOT NULL DEFAULT '0',
 output_format varchar(255) DEFAULT '',
 creator_id INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 confirmed INT4 NOT NULL DEFAULT '0',
 approved INT4 NOT NULL DEFAULT '0',
 removed INT4 NOT NULL DEFAULT '0',
 user_id INT4 DEFAULT '0',
 bounce_count INT4 DEFAULT '0',
 contentobject_id INT4 DEFAULT NULL,
 PRIMARY KEY (id,version_status)

);

;


--
-- Sequences for table EZ_BOUNCEDATA
--

CREATE SEQUENCE ez_bouncedata_id_s;

CREATE TABLE ez_bouncedata (
 id INT4 DEFAULT nextval('ez_bouncedata_id_s'),
 newslettersenditem_id INT4 NOT NULL DEFAULT '0',
 address varchar(255) DEFAULT '',
 bounce_count INT4 NOT NULL DEFAULT '0',
 bounce_type INT4 NOT NULL DEFAULT '0',
 bounce_arrived INT4 NOT NULL DEFAULT '0',
 bounce_message TEXT DEFAULT '' NOT NULL,
 PRIMARY KEY (id)

);

;


--
-- Sequences for table EZNEWSLETTER
--

CREATE SEQUENCE eznewsletter_id_s;

CREATE TABLE eznewsletter (
 id INT4 DEFAULT nextval('eznewsletter_id_s'),
 name varchar(255) DEFAULT NULL,
 hash varchar(255) DEFAULT NULL,
 output_format varchar(255) DEFAULT '',
 template_to_use varchar(255) DEFAULT '',
 send_date INT4 NOT NULL DEFAULT '0',
 send_status INT4 NOT NULL DEFAULT '0',
 contentobject_id INT4 NOT NULL DEFAULT '0',
 contentobject_version INT4 NOT NULL DEFAULT '0',
 newslettertype_id INT4 NOT NULL DEFAULT '0',
 category varchar(255) DEFAULT '',
 preview_email varchar(255) DEFAULT '',
 recurrence_type varchar(255) NOT NULL DEFAULT '',
 recurrence_value varchar(255) NOT NULL DEFAULT '',
 object_relations text,
 status INT4 NOT NULL DEFAULT '0',
 created INT4 NOT NULL DEFAULT '0',
 creator_id INT4 NOT NULL DEFAULT '0',
 pretext text NOT NULL,
 posttext text NOT NULL,
 preview_mobile varchar(255) DEFAULT '',
 PRIMARY KEY (id,status)

);

;


--
-- Sequences for table EZSUBSCRIPTIONUSERDATA
--

CREATE SEQUENCE ezsubscriptionuserdata_id_se;

CREATE TABLE ezsubscriptionuserdata (
 id INT4 DEFAULT nextval('ezsubscriptionuserdata_id_se'),
 email varchar(255) DEFAULT '',
 firstname varchar(255) DEFAULT '',
 name varchar(255) DEFAULT '',
 password varchar(255) DEFAULT '',
 hash varchar(255) DEFAULT '',
 mobile varchar(255) DEFAULT '',
 PRIMARY KEY (id)

);

;


--
-- Sequences for table EZROBINSONLIST
--

CREATE SEQUENCE ezrobinsonlist_id_s;

CREATE TABLE ezrobinsonlist (
 id INT4 DEFAULT nextval('ezrobinsonlist_id_s'),
 value varchar(255) NOT NULL DEFAULT '',
 type INT4 NOT NULL DEFAULT '0',
 global INT4 NOT NULL DEFAULT '0',
 PRIMARY KEY (id)

);


INSERT INTO ezsite_data VALUES ('eznewsletter-version', '1.5.0');
INSERT INTO ezsite_data VALUES ('eznewsletter-release', '1');


--
-- Sequences for table EZROBINSONLIST
--

SELECT SETVAL('ezrobinsonlist_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezrobinsonlist));

--
-- Sequences for table EZSUBSCRIPTIONUSERDATA
--

SELECT SETVAL('ezsubscriptionuserdata_id_se',(select case when max(id)>0 then max(id)+1 else 1 end from ezsubscriptionuserdata));

--
-- Sequences for table EZ_BOUNCEDATA
--

SELECT SETVAL('ez_bouncedata_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ez_bouncedata));

--
-- Sequences for table EZNEWSLETTERTYPE
--

SELECT SETVAL('eznewslettertype_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from eznewslettertype));

--
-- Sequences for table EZSUBSCRIPTION_GROUP
--

SELECT SETVAL('ezsubscription_group_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezsubscription_group));

--
-- Sequences for table EZSUBSCRIPTION
--

SELECT SETVAL('ezsubscription_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezsubscription));

--
-- Sequences for table EZSENDNEWSLETTERITEM
--

SELECT SETVAL('ezsendnewsletteritem_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezsendnewsletteritem));

--
-- Sequences for table EZSUBSCRIPTION_LIST
--

SELECT SETVAL('ezsubscription_list_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from ezsubscription_list));

--
-- Sequences for table EZNEWSLETTER
--

SELECT SETVAL('eznewsletter_id_s',(select case when max(id)>0 then max(id)+1 else 1 end from eznewsletter));

COMMIT;

Wednesday 08 August 2007 12:01:03 am

Ah yes the sections I forgot to mention. Thanks for the feedback.