<div dir="ltr"><div><div>Hi,<br></div>I also got the same bug. Here is my short soluthion for correct migrate from sqlite2 to sqlite3.<br><a href="http://mailman.ecs.soton.ac.uk/pipermail/zendto/2013-June/002138.html" target="_blank">http://mailman.ecs.soton.ac.uk/pipermail/zendto/2013-June/002138.html</a><br>
<br></div><div><br><br>Step by step migration:<br><br></div><div># Stop service<br></div><div># Backup your database<br></div><div>$ cp zendto.sqlite zentdo.sqlite_bkp<br></div><div>$ sqlite zentdo.sqlite_bkp</div><div>
<br># Remember last rowid for check it after migration.<br>SELECT rowid,* from dropoff;<br><br># Create new temporary table with PK (private key)<br>CREATE TABLE dropoff_tmp (<br> claimID character varying(16) not null,<br>
claimPasscode character varying(16),<br> <br> authorizedUser character varying(16),<br> <br> senderName character varying(32) not null,<br> senderOrganization character varying(32),<br> senderEmail text not null,<br>
senderIP character varying(255) not null,<br> confirmDelivery boolean default FALSE,<br> created timestamp with time zone not null,<br> note text,<br> PK INTEGER PRIMARY KEY ASC<br>
);<br></div><div>
<br># Copy table context to new table with PK.<br>INSERT INTO dropoff_tmp select *,rowid from dropoff;<br><br># Convert sqlite2 to sqlite3<br>$ sqlite zendto.sqlite_bkp .dump | sqlite3 zendto.sqlite3<br>$ sqlite3 zendto.sqlite3<br>
<br># Replace orig table (broke) by tmp one.<br>DROP table dropoff;<br>CREATE TABLE dropoff (<br> claimID character varying(16) not null,<br> claimPasscode character varying(16),<br> <br> authorizedUser character varying(16),<br>
<br> senderName character varying(32) not null,<br> senderOrganization character varying(32),<br> senderEmail text not null,<br> senderIP character varying(255) not null,<br> confirmDelivery boolean default FALSE,<br>
created timestamp with time zone not null,<br> note text<br>);<br><br>INSERT INTO dropoff (claimID,claimPasscode,authorizedUser,senderName,senderOrganization,senderEmail,senderIP,confirmDelivery,created,note,rowid) select * from dropoff_tmp;<br>
<br># Check that you have the same last rowid<br>SELECT rowid,* from dropoff;<br><br># Drop tmp table<br>DROP table dropoff_tmp;<br></div><div><br><br></div><div># Change you sqlite db, start service and finish ckeck<br><br>
<br></div><div>Good luck. =)<br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Mar 12, 2013 at 4:49 PM, Jules <span dir="ltr"><<a href="mailto:Jules@zend.to" target="_blank">Jules@zend.to</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
Mike,<br>
<br>
Well done for finding this one. This was a problem I inherited from
the original UDel Dropbox package. I didn't really like the way they
had done it either, but changing it would have meant quite a few
changes to the source, which at that point wasn't well enough
organised to be able to make the change reliably and consistently.
However, the source is in a rather better state now, so it should be
possible, the problem being issuing an upgrade that requires
everyone to upgrade their SQLite database file as well.<br>
<br>
There are almost certainly people on this list who know far more
about database design than I do (that's not hard!) and could tell me
the best way to do this; not only how to put in a proper counter but
also how to migrate existing databases from their current (old UDel
Dropbox) method to this new counter.<br>
<br>
So I'm open to all useful suggestions!<br>
<br>
Jules.<div><div class="h5"><br>
<br>
<div>On 12/03/2013 11:58, Mike Brudenell
wrote:<br>
</div>
</div></div><blockquote type="cite"><div><div class="h5">
<div dir="ltr">Dear all,
<div><br>
</div>
<div>After a lot of brain-ache here we have found the problem
and it seems to be related to upgrading from SQLite v2 to v3
along with an assumption(/bug?) within ZendTo…</div>
<div><br>
</div>
<div>This support page describes the tables and their elements
used by ZendTo:</div>
<blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px">
<div><a href="http://www.zend.to/technical.php" target="_blank">http://www.zend.to/technical.php</a></div>
</blockquote>
<div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">Note the magical element <b>rowId</b>
which <i>"is referenced from several other tables and gives
the unique number of each complete dropoff".</i></div>
<div class="gmail_extra"><i><br>
</i></div>
<div class="gmail_extra">Although described here, rowId is not
a field of the table created by ZendTo, but is instead a
hidden auto-incrementing value maintained by SQLite itself.
It starts off with the value 1 and auto-increments for each
insertion.</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">When the v2 database is dumped out
the hidden rowId field is not included for each record. So
when the dumped data is re-imported into SQLite v3 to get a
database in the new format the rowId starts at 1 again.</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">This means each imported record gets
a different rowId from what it had before.</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">Unfortunately ZendTo uses the rowId
to tie the data in various tables together and, to do this,
stores it within some records. But because each record now
has a brand new rowId in the v3 database the linkage is
broken, and results in dropoffs showing as containing no
files.</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">(It's a bit like renumbering
a BASIC program but the renumber command not updating the
line numbers within program's GOTO statements!)</div>
<div class="gmail_extra">
<br>
</div>
<div class="gmail_extra">A solution would be for ZendTo to
generate its own unique key number for each record in the
tables and use this to link them together rather than the
auto-generated one.</div>
<div class="gmail_extra">
<br>
</div>
<div class="gmail_extra">(Or there may be some other way we've
not been able to find of exporting the data such that it
includes the hidden rowId values?)</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">
We've worked around it by updating the hidden rowId values
in the new v3 database to match the ones from the old v2
database, which has made all the files in the dropoffs
spring back into existence. (We just hope these entries will
be expired/removed before the auto-incrementing counter
works its way up there again!)</div>
<div class="gmail_extra"><br>
</div>
<div class="gmail_extra">Cheers,</div>
<div class="gmail_extra">Mike B-)<br>
<br>
<div class="gmail_quote">On 12 March 2013 09:44, Mike
Brudenell <span dir="ltr"><<a href="mailto:mike.brudenell@york.ac.uk" target="_blank">mike.brudenell@york.ac.uk</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div dir="ltr">Hi, all!
<div><br>
</div>
<div>We're in the middle of upgrading ZendTo our live
DropOff Service from 4.08-something to the latest
4.11-9, and from Ubuntu 10.4 LTS to 12.04 LTS.</div>
<div><br>
</div>
<div>We had no problems when we tried did this same
upgrade on our test server a few weeks back. However
today we've been left with a ZendTo that has lost
sight of the contents of the dropoffs that existed
within it.</div>
<div><br>
</div>
<div>Specifically, logging in as an Admin and showing
all the dropoffs lists them with their dropoff IDs,
but shows 0 bytes against each. Clicking on any of
the dropoffs lists no files as being present.</div>
<div><br>
</div>
<div>Uploading a new file into the service enters it
properly, its dropoff shows the bytecount and the
filenames are listed within the dropoff.</div>
<div><br>
</div>
<div>We're confident that we went through the same
upgrade procedure, including exporting the SQLite 2
database and importing it into SQLite 3 to
convert/upgrade it, so are rather baffled.</div>
<div><br>
</div>
<div>Can anyone shed any light/have anything to try to
relink the dropoffs' files (which still exist on
disk) into their corresponding dropoffs?</div>
<div><br>
</div>
<div>Cheers,</div>
<div>Mike B-)<span><font color="#888888"><br clear="all">
<div><br>
</div>
-- <br>
<font size="1"><font face="'arial narrow',
sans-serif"><span style="font-size:small">IT
Services, The University of York,
Heslington, York YO10 5DD, UK<br>
Tel: <a href="tel:%2B44-1904-323811" value="+441904323811" target="_blank">+44-1904-323811</a></span><span style="font-size:small"><br>
Disclaimer: <</span><a href="http://www.york.ac.uk/docs/disclaimer/email.htm" target="_blank"><span style="font-size:small">http://www.york.ac.uk/docs/disclaimer/email.htm</span></a><span style="font-size:small">></span></font><br>
</font>
</font></span></div>
</div>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<font size="1"><font face="'arial narrow', sans-serif"><span style="font-size:small">IT Services, The University of
York, Heslington, York YO10 5DD, UK<br>
Tel: +44-1904-323811</span><span style="font-size:small"><br>
Disclaimer: <</span><a href="http://www.york.ac.uk/docs/disclaimer/email.htm" target="_blank"><span style="font-size:small">http://www.york.ac.uk/docs/disclaimer/email.htm</span></a><span style="font-size:small">></span></font><br>
</font>
</div>
</div>
</div>
<br>
<fieldset></fieldset>
<br>
</div></div><pre>_______________________________________________
ZendTo mailing list
<a href="mailto:ZendTo@zend.to" target="_blank">ZendTo@zend.to</a>
<a href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto" target="_blank">http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto</a></pre>
<br>
<pre cols="72">Jules
--
Julian Field MEng MBCS CITP CEng
'Ever since the dawn of civilization, people have craved for an
understanding of the underlying order of the world: why it is as
it is, and why it exists at all. But even if we do find a complete
theory of everything, it is just a set of rules and equations. What
is it that breathes fire into the equations, and makes a universe
for them to describe?' - Stephen Hawking
<a href="http://www.Zend.To" target="_blank">www.Zend.To</a>
Twitter: @JulesFM
PGP footprint: EE81 D763 3DB0 0BFD E1DC 7222 11F6 5947 1415 B654
</pre>
</blockquote>
</div>
<br>_______________________________________________<br>
ZendTo mailing list<br>
<a href="mailto:ZendTo@zend.to">ZendTo@zend.to</a><br>
<a href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto" target="_blank">http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto</a><br></blockquote></div><br></div>