Feedback on the update process (Page 1 of 4)

It doesn't seem to work with sqlite.
From what I have found out the script gets stuck everytime an 'alter table' comes around.

Also the sqlite.php is missing the 'replace' piece in the query_build function.

my setup is
Lighttpd 1.4.18-1
PHP 5.2.5
SQLite 2.8.17
punbb 1.3-rev.1478

Reporting first test : updated a little French database (so with accentuated caracters in it)

- no errors, no warnings..
- the tables have been updated (latin1_general_ci -> utf8_general_ci), except 2 : forums_perms and subscriptions (not a big deal, but why ?).
- the new fields (like first_post_id in topics) and/or new indexes have been created.
- BUT the data HAVE NOT been converted to utf8... (accents remain in latin1 format)

Looked at db_update.php to find an 'obvious' reason... not found anything  : i couldn't say why the utf8_encode() didn't work.

This is what I get:
An error was encountered
The error occurred on line 354 in D:\server\web\pun\include\dblayer\sqlite.php
Database reported: SQL logic error or missing database (Errno: 1).

Which really gives you nothing much, so I added 
define('PUN_SHOW_QUERIES', 1);
inside the db_update.php which gave me it bit more to go on:

Failed query: ALTER TABLE extensions ADD uninstall_note TEXT

The db_update.php calls the add_field function several times and that function in sqlite.php sure does call an ALTER TABLE.

Agreed, for consistency we should have it (since new tables will have it).

My mistake. Of course it support alter table. It's quite limited though. Only rename table and add column.

Mpok: Any information you can provide that would lead us to a fix would be greatly appreciated.

Problem with the sqlite documentation is that it is mostly unclear about which version is applicable to the documentation.

Whoops I think you guys are right. We added SQLite support in 1.2 and after that, I don't believe we've ever had to do an ALTER TABLE.

I other words. In order to support SQLite upgrades from 1.2, we'll have to include our own implementation of ALTER TABLE. Does anyone know of a good one? The only one I found was this one:

http://code.jenseng.com/db/

But it's huge.

Yeah, that's the procedure. However, wrapping code around it to work with any existing table is the tricky part. For example, the tables created must include the full definition of the new table (the old fields and the new field).

There is always an alternative. In this case it is not supporting 1.2->1.3 upgrades for SQLite. I'm not saying this is what we should do, but it's an alternative we need to consider.

So, i've restored my old 1.2 database and tried the update again (this time, looking carefully what occured).

Note : config = MySql 5.0, converting from ISO-8859-1 on a french server.

-> all seems ok until "stage conv_tables" (so the convert_to_utf8() function works fine).
And then, the conversion of the text columns (the 'CHANGE' ... text -> blob -> text) seems somehow to REVERSE all the work done in previous stages (conv_topics, conv_posts, etc...).
This is why when viewing data in phpMyadmin the conversion seems to not have be done (and obviously the display in the forums isn't ok, accentuated characters appear as '?').

I managed to have a correct display in the forums : replacing the double 'CHANGE...' thing in convert_table_utf8() function by a single 'MODIFY... CHARACTER SET utf8'.
Now, my data are well displayed, but i'm really not sure it is a "fix"...

I will try tomorrow another test on another server (MySql 4.1.22), cause i'm thinking the problem could be related to server configuration (default charset used by the server).

Got the same issue with this different server/configuration.

Another pbm (not related to the previous one) :
in stage "conv_users", u start at id=2. Ok.
But in good localizations, the word 'Guest' had been translated (username, password and email).
So the guest user needs to be converted.
I added the following code (beginning of stage "conv_users") :

        // Convert 'Guest' user
        echo 'Converting guest user …<br />'."\n";
        $result = $db->query('SELECT username, password, email FROM '.$db->prefix.'users WHERE id=1') or error(__FILE__, __LINE__);
        $cur_item = $db->fetch_assoc($result);
        if (convert_to_utf8($cur_item['username'], $old_charset) | convert_to_utf8($cur_item['password'], $old_charset) | convert_to_utf8($cur_item['email'], $old_charset))
            $db->query('UPDATE '.$db->prefix.'users SET username=\''.$db->escape($cur_item['username']).'\', password=\''.$db->escape($cur_item['password']).'\', email=\''.$db->escape($cur_item['email']).'\' WHERE id=1') or error(__FILE__, __LINE__);