SQLite upgrade fails

[emanuele45]

At least here due to issues with indexes and their names in particular.
See http://flylib.com/books/en/4.112.1.60/1/ for details.
In summary:

CREATE TABLE {$db_prefix}log_activity (
  date date NOT NULL default '0001-01-01',
  hits int NOT NULL default '0',
  topics smallint NOT NULL default '0',
  posts smallint NOT NULL default '0',
  registers smallint NOT NULL default '0',
  most_on smallint NOT NULL default '0',
  PRIMARY KEY (date)
);

creates an index named (smf_log_activity autoindex 1), during the upgrade there is the command:
$smcFunc['db_remove_index']($db_prefix . 'log_activity', $db_prefix . 'log_activity_hits');
that ends up running the query:
PRAGMA index_info((smf_log_activity autoindex 1))
obviously wrong.

What's the best way to fix it?
I tried with $smcFunc['db_escape_string']($row['name']) but no luck.
2nd test has been with strtr($row['name'], array('(' => '\'(', ')' => ')\'')) and "of course" worked.

Now the question is: is it enough put the strtr there or better have it somewhere else too?

[Spuds]

What's the best way to fix it?

🚽

😃

[emanuele45]

👼

[sicommnend]

I am just waking up, sleep typing lol. Have you tried sqlite_escape_string()?

[emanuele45]

I tried with $smcFunc['db_escape_string'] that is the equivalent of sqlite_escape_string but no luck... :(

[Oldiesmann]

Is this still an issue? I don't have an sqlite install that I can test this with.

[emanuele45]

I didn't do anything to fix it, so...yes I'm pretty sure it's still an issue.

[ghost]

Just to get my head around this before I look at it, is the issue the double brackets in the PRAGMA call when executing?

[emanuele45]

Technically the issue is that when installing SQLite creates a wrong name for the index I think, then it requires a some trick to remove it.

The basic fix is probably the one I provided above (even though someone with more SQLite knowledge may give advices), the best fix would be to remove the "broken" indexes and replace them with proper names...or something like that.

[Oldiesmann]

I don't think there's much we can do about the "autoindex" indexes, as they're automatically generated when the primary key isn't an integer (see http://readlist.com/lists/sqlite.org/sqlite-users/7/37230.html). I don't even know if we can actually remove those either.

[tinoest]

Unfortunately escape_string uses the underlying c function sqlite3_mprintf with the %q operator I believe.

http://www.sqlite.org/c3ref/mprintf.html is the documentation on the function , it doesn't escape the ( or ) characters so passing it to that function won't rectify the issue. Only way that I can think of is a preg_replace or a strtr .

Hope that is of some use...

[Oldiesmann]

Closing per #1916