[ZendTo] upgrade missing sqlite3 schema

[Lieven De Puysseleir]

Hello Jules,
Indeed I did find out that the cronjob did just that and I executed it
manually.
What strikes me a bit odd is the way the schema is printed after the
change. On one machine, I got this schema after the cleanup run:

sqlite> .schema dropoff
CREATE TABLE dropoff (
  claimID             character varying(16) not null,
  claimPasscode       character varying(16),

  authorizedUser      character varying(16),

  senderName          character varying(32) not null,
  senderOrganization  character varying(32),
  senderEmail         text not null,
  senderIP            character varying(255) not null,
  confirmDelivery     boolean default FALSE,
  created             timestamp with time zone not null,
  note                text
, lifeseconds int NOT NULL DEFAULT 0, subject character varying(500)
DEFAULT NULL);
CREATE INDEX dropoff_claimID_index ON dropoff(claimID);
=> there seems to be a missing newline and another newline is before
the ', lifeseconds". That doesn't seem to hamper with the
functionality of zendto in this case

=> I had another system following the same update version path which
didn't have the strange output of the dropoff schema BUT in that case,
there was the previously posted error when making a dropoff and that's
the system where I ran the manually changed export-import of the
sqlite db.


This is the output from the cleanup script, nothing is mentioned on
the change of the schema however it could be the case that the cronjob
of cleanup got there before I ran it manually:

#> /usr/bin/php /opt/zendto/sbin/cleanup.php /opt/zendto/config/preferences.php

Cleanup of ZendTo for preference file:
  /opt/zendto/config/preferences.php

Updating database schema
Gathering expired dropoffs
- Removing [<CLAIMID>] <USER> <NAME> <<User.Name>@<address.example>>

NSSDropbox Warn recipients about dropoffs close to expiry.
Purging orphaned dropoffs:
No orphans found.

Purging old sender verification data:
Purging old request data:


On Mon, Sep 28, 2020 at 6:12 PM Jules <Jules at zend.to> wrote:

> To manually run the DB schema update, run
>     /opt/zendto/sbin/cleanup.php
> as root. The output of that should tell you what it's doing.
>
> Cheers,
> Jules.
>
> On 28/09/2020 14:21, Lieven De Puysseleir via ZendTo wrote:
>
> Dear,
> On one system running zendto 5.17-6 I fail to have the sqlite3 schema
> updating while upgrading to 6.05-4.
> to update I use the zendto repository on sles 15 sp2 and then run the
> script that handles config file changes.
>
> after the update, it's impossible to make dropoffs "unable to add a
> dropoff record to the database". I notice that there are 2 fields missing
> from the dropoff table: "lifeseconds and subject"
>
> So I deleted the file zendto.sqlite, restarted apache2 and zendto created
> a new one with the correct schema.
> Next, I did a dump of the old db to file, found the INSERT lines for the
> dropoff table and added a value for the lifeseconds which (I think)
> corresponds to the max 14days we put as preference when it's not overridden
> in the new version dropoff form. Also added a value empty string '' for the
> subject field.
> Lastly I did a cat of the (edited) dump file, piped it to sqlite3 into the
> newly created empty db file.
> This seemed to work fine, I could see the dropoffs in the interface but
> couldn't see the files when I opened a dropoff.
> I verified that for each dropoff line in the database, the claimid
> corresponded to the name of the folder in /var/zendto where the dropoff
> directory/files resided and it matched.
> The logs don't say anything about this.
> Apache logs indicate that pickup.php is used but again, the php code with
> the nssdrop class stuff is something I can't seem to understand how it
> works. (or if it even matters in this case)
>
> This worked fine on another system upgrading 5.17-6 to 6.05-4. The update
> was done with zypper, the upgrade script run and that was it, everything
> looked fine in the dropoffs.
>
> Any ideas what part of the install script I should run to simulate the
> schema upgrade? I've been looking into cleanup.php but I'm not much of a
> programmer and can't figure how it actually works by calling some class or
> something. (I'm probably wrong here)
>
> Thanks for your help.
>
> --
> Lieven
>
> _______________________________________________
> ZendTo mailing listZendTo at zend.tohttp://jul.es/mailman/listinfo/zendto
>
>
> Jules
>
> --
> Julian Field MEng CEng CITP MBCS MIEEE MACM
>
> The current UK shipping forecast:
> East Forties: Cyclonic becoming northerly 4 or 5. Slight or moderate. Rain,
> fog patches. Moderate or very poor.
> www.Zend.To
> Twitter: @JulesFM
>
>

-- 
Lieven De Puysseleir
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://jul.es/pipermail/zendto/attachments/20200929/0efb4546/attachment-0001.html>