<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Artyom,<br>
<br>
Thank you! I would buy you a virtual pint if it were possible! We
just upgraded an older release to the latest and encountered this
very issue with the change from Sqlite2 to 3.<br>
<br>
I tried running a standard conversion with "sqlite source.db
.dump|sqlite3 new.db" which succeeded but of course we then had
zero-sized drop-offs in the database. I then found your
instructions and followed them to the letter and it worked
perfectly.<br>
<br>
Thanks again!<br>
<br>
Rich.<br>
<pre class="moz-signature" cols="72">
</pre>
On 24/06/13 12:23, Artyom Aleksandrov wrote:<br>
</div>
<blockquote
cite="mid:CAJtYNb0tEK=EajwLtxR5QAP9c1KZyAG2-ZSPE4rUcsmfJ_v0NQ@mail.gmail.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<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 moz-do-not-send="true"
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 moz-do-not-send="true"
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 moz-do-not-send="true"
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
moz-do-not-send="true"
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
moz-do-not-send="true"
href="tel:%2B44-1904-323811"
value="+441904323811"
target="_blank">+44-1904-323811</a></span><span
style="font-size:small"><br>
Disclaimer: <</span><a
moz-do-not-send="true"
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
moz-do-not-send="true"
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 moz-do-not-send="true" href="mailto:ZendTo@zend.to" target="_blank">ZendTo@zend.to</a>
<a moz-do-not-send="true" 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 moz-do-not-send="true" 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 moz-do-not-send="true" href="mailto:ZendTo@zend.to">ZendTo@zend.to</a><br>
<a moz-do-not-send="true"
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>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
ZendTo mailing list
<a class="moz-txt-link-abbreviated" href="mailto:ZendTo@zend.to">ZendTo@zend.to</a>
<a class="moz-txt-link-freetext" href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto">http://mailman.ecs.soton.ac.uk/mailman/listinfo/zendto</a></pre>
</blockquote>
<br>
</body>
</html>