[ZendTo] upgrade missing sqlite3 schema

Tue Sep 29 11:08:55 BST 2020


On 29/09/2020 08:09, Lieven De Puysseleir wrote:
> 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.
>   
I think that output is just a quirk of SQLite. I've often seen it myself 
too, but it appears to have no effects on the operation of the table. It 
just appears to put the newline before the comma rather than after.

> 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 
> <mailto: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 list
>>     ZendTo at zend.to  <mailto:ZendTo at zend.to>
>>     http://jul.es/mailman/listinfo/zendto  <http://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  <http://www.Zend.To>
>     Twitter: @JulesFM
>
>
>
> -- 
> Lieven De Puysseleir

Jules

-- 
Julian Field MEng CEng CITP MBCS MIEEE MACM

'A committee is a group of the unwilling, chosen from the unfit,
  to do the unnecessary.' - Anon

www.Zend.To
Twitter: @JulesFM

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://jul.es/pipermail/zendto/attachments/20200929/3e90fb1e/attachment.html>