CAcert Code Development list.

[Benny Baumann <benbe AT cacert.org>]

Dear Critical Team,

there's a problem with the first query, which I only noticed due to the
confusion of Dirk when reviewing it. Please do NOT execute those scripts
until this problem is fixed.

Regards,
BenBE.

Am 21.02.2015 um 14:13 schrieb Eva Stöwe:
> Dear Critical Team,
> 
> I ask you to execute the attached scripts
> 
> send_policy_cca_correct_20150221_1.php and
> send_policy_cca_correct_20150221_2.php
> 
> They should initiate a mailing to members who have agreed to the CCA
> when the wrong document was online at the beginning of the year.
> 
> The first one is for members who did not accept another CCA before, we
> cannot assume that they have agreed to the real CCA (even if they
> clicked according checkboxes afterwards, they could assume that they
> would be informed about changes to what they have read). The second
> informs those who previously had accepted the CCA but who may be
> confused because of the wrong version.
> 
> To created those files I did the following changes to the one used for
> the CCA mailing in September. It is attached as
> send_policy_cca_20140916.php.
> 
> The script in that file had the review of the software team, back then
> (and it was already a re-use of the heartbleed-mailing).
> 
> I did the following changes to the script but they should be covered as
> the same changes did not need a review by a second SA last time, when we
> send such a mail:
> 
> 1. I exchanged the SQL-script by new scripts that were reviewed by Benny
> and Dirk (1) and Dirk and Michael (2). The SQL-scripts were tested by
> Benny and Marcus with at least Felix and me as witnesses at last
> software telco. I added the last mail conversation, below.
> 
> 2. I also exchanged the texts used for the mailing. The text were
> reviewed by Policy Group at the policy mailing list and at:
> http://www.cacert.org/policy/CAcertCommunityAgreement.html
> 
> 3. I also exchanged the name of the files used in the script as:
> send_policy_cca20140915_lastid.txt replaced by
> send_policy_cca_correct_20150221_1_lastid.txt or
> send_policy_cca_correct_20150221_2_lastid.txt
> 
> 
> I am not really familiar with PHP, so it would be good if you
> cross-check everything, before execution. It would also be nice to
> inform Support when you send the mail, as they may have to deal with
> possible returns, even as less than 700 users should be affected.
> 
> For completeness: There was also an arbitration case to clarify the
> authoritarian for this mails, it can be found at:
> https://wiki.cacert.org/Arbitrations/a20150114.2
> 
> Thank you for your help.
> 
> -------- Forwarded Message --------
> Subject: Re: CCA mail review
> Date: Sat, 21 Feb 2015 09:53:13 +0100
> From: Eva Stöwe 
> <eva.stoewe AT cacert.org>
> Organization: CAcert
> To: dirk astrath 
> <dirk AT cacert.org>,
>  Benny Baumann 
> <benbe AT cacert.org>,
> 'Michael Tänzer' 
> <michael.taenzer AT cacert.org>
> 
> Hello Dirk, Benny, Michael,
> 
> thank you for your reviews.
> 
> @Dirk, I understand your issue with the "As" but as this does not change
> the result, if not used, I do not think that this should be an issue.
> 
> The queries were tested by Benny (hands on the testserver) together with
> Marcus (looking up account details in the UI), witnessed by the software
> telco at 2015-02-17. At least Felix and me were present.
> 
> 
> I will try to put everything together (the script, the queries and the
> text) and ask critical team for execution. If someone prefers to do
> this, to avoid further complications, I do not mind, as long as the
> execution, is "soon".
> 
> On 21.02.2015 09:43, dirk astrath wrote:
>> Hello,
>>
>> Since you wrote, what you tested, which is working and which one is not
>> working i reviewed this SQL-query (to be exact: the last one of this 
>> email).
>>
>> It took some time to find the differences between these queries and i
>> don't understand the use of "AS ua" and "as u" in the last one since
>> neither "ua" and "u" are used anywhere else in this query.
>>
>> However:
>>
>> The query is OK (as far as i can see, i'm not able to test is currently
>> on my system) ...
>>
>> Kind regards,
>>
>> Dirk Astrath
>>
>> On 17.02.2015 21:14, Eva Stöwe wrote:
>>> Hi,
>>>
>>> I got Benny to test the queries and one of the queries was not working
>>> correctly, but he updated the query and it works now. Both are tested.
>>> The new query has Bennys but only Bennys review at the moment. Can you
>>> please review it, again?
>>>
>>> Old working one:
>>>
>>> SELECT
>>>         users.id,
>>>         users.fname,
>>>         users.lname,
>>>         users.email,
>>>         COUNT(*) AS agreement_count
>>> FROM user_agreements
>>> LEFT JOIN users ON users.id = user_agreements.memid
>>> WHERE        user_agreements.date >= '2015-01-08 14:29:00'
>>> AND        user_agreements.date <= '2015-01-15 10:48:00'
>>> AND        user_agreements.document = 'CCA'
>>> AND         users.id IN (
>>>                 SELECT users.id
>>>                 FROM  user_agreements
>>>                 LEFT JOIN users ON users.id = user_agreements.memid
>>>                 WHERE        user_agreements.date < '2015-01-08 14:29:00'
>>>                 AND        user_agreements.document = 'CCA')
>>> GROUP BY users.id;
>>>
>>>
>>> Old not working :
>>>
>>> SELECT
>>>         users.id,
>>>         users.fname,
>>>         users.lname,
>>>         users.email,
>>>         COUNT(*) AS agreement_count
>>> FROM user_agreements
>>> LEFT JOIN users ON users.id = user_agreements.memid
>>> WHERE        user_agreements.date >= '2015-01-08 14:29:00'
>>> AND        user_agreements.date <= '2015-01-15 10:48:00'
>>> AND        user_agreements.document = 'CCA'
>>> AND         users.id NOT IN (
>>>                 SELECT users.id
>>>                 FROM  user_agreements
>>>                 LEFT JOIN users ON users.id = user_agreements.memid
>>>                 WHERE        user_agreements.date < '2015-01-08 14:29:00'
>>>                 AND        user_agreements.document = 'CCA')
>>> GROUP BY users.id;
>>>
>>>
>>> New :
>>>
>>> SELECT
>>>         users.id,
>>>         users.fname,
>>>         users.lname,
>>>         users.email,
>>>         COUNT(*) AS agreement_count
>>> FROM user_agreements AS ua
>>> LEFT JOIN users AS u ON users.id = user_agreements.memid
>>> WHERE        user_agreements.date >= '2015-01-08 14:29:00'
>>> AND        user_agreements.date <= '2015-01-15 10:48:00'
>>> AND        user_agreements.document = 'CCA'
>>> AND         users.id NOT IN (
>>>                 SELECT user_agreements.memid
>>>                 FROM  user_agreements
>>>                 WHERE        user_agreements.date < '2015-01-08 14:29:00'
>>>                 AND        user_agreements.document = 'CCA')
>>> GROUP BY users.id;
>>>
>>>
>>
>>
>

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature