FUDforum: Bug Reports » Several topics with sqlite

Home » FUDforum Development » Bug Reports » Several topics with sqlite

Show: Today's Messages :: Polls :: Message Navigator

Several topics with sqlite [message #159561]

Thu, 11 June 2009 09:04

icarus

Messages: 52
Registered: May 2005

Karma: 0

Member

Hi!

The support for sqlite is partly broken. It isn't possible to prune topics or to set topics to "read":

(/../index.php:170
/../index.php:177
/../index.php:1265
/../markread.php:37
/.../index.php:1349
) near "FROM": syntax error: HY000
Query: UPDATE fud28_read SET user_id=2, msg_id=t.last_post_id, last_view=1244710953 FROM (SELECT id, last_post_id FROM fud28_thread WHERE forum_id=4 AND last_post_date > 0) t WHERE user_id=2 AND thread_id=t.id
_GET: array ( 't' => 'markread', 'S' => 'c481affee58a2ee137407901703d2d25', 'id' => '4', 'SQ' => '75db9163c00ef442937024842e839152', )
Server Version: 3.3.8
[Referring Page] http://news.piratenpartei-hamburg.de/index.php?t=thread&frm_id=4&S=c481affee58a2ee137407901703d2d25

Report message to a moderator

Re: Several topics with sqlite [message #159571 is a reply to message #159561]

Thu, 11 June 2009 15:54

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

This query makes use of an inline view (or derived table), which is a SELECT statement within the FROM clause of the query. However, I couldn't find anything on the subject over at sqlite.org.

Report message to a moderator

Re: Several topics with sqlite [message #159622 is a reply to message #159571]

Sat, 13 June 2009 07:47

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

I've asked for help on the sqlite mailing list. Pavel Ivanov replied with this alternate syntax that will work on sqlite:

UPDATE fud28_read
SET user_id=2, last_view=1244710953,
      msg_id=(SELECT last_post_id FROM fud28_thread
                    WHERE id = fud28_read.thread_id)
WHERE user_id=2
     AND thread_id in (SELECT id FROM fud28_thread
                                WHERE forum_id=4 AND last_post_date > 0)

The original query works on MySQL, PostgreSQL and Oracle - so, maybe they will support the original syntax in a future release.

Report message to a moderator

Re: Several topics with sqlite [message #159670 is a reply to message #159622]

Mon, 15 June 2009 11:26

icarus

Messages: 52
Registered: May 2005

Karma: 0

Member

Hi!

naudefj wrote on Sat, 13 June 2009 09:47

I've asked for help on the sqlite mailing list. Pavel Ivanov replied with this alternate syntax that will work on sqlite:

Great! Is there a patch for this available?

Michael

Report message to a moderator

Re: Several topics with sqlite [message #159671 is a reply to message #159670]

Mon, 15 June 2009 17:13

naudefj

Messages: 3771
Registered: December 2004

Karma: 28

Senior Member
Administrator
Core Developer

Try this and let me know if it helps:

Index: users.inc.t
===================================================================
RCS file: /forum21/install/forum_data/src/users.inc.t,v
retrieving revision 1.171
diff -u -r1.171 users.inc.t
--- users.inc.t	3 May 2009 18:57:06 -0000	1.171
+++ users.inc.t	15 Jun 2009 17:12:27 -0000
@@ -781,7 +781,7 @@
 
 function user_mark_forum_read($id, $fid, $last_view)
 {
-	if (__dbtype__ == 'mysql') {
+	if (__dbtype__ == 'mysql' || __dbtype__ == 'sqlite') {
 		if ($GLOBALS['FUD_OPT_3'] & 1024) {
 			q('INSERT INTO {SQL_TABLE_PREFIX}read (user_id, thread_id, msg_id, last_view) SELECT '.$id.', id, last_post_id, '.__request_timestamp__.' FROM {SQL_TABLE_PREFIX}thread WHERE forum_id='.$fid.' AND last_post_date > '.$last_view.' ON DUPLICATE KEY UPDATE last_view=VALUES(last_view), msg_id=VALUES(msg_id)');
 		} else {

Report message to a moderator

Previous Topic:	Blank Page - Not Processing PHP pages
Next Topic:	Upgrading script corrupted when create_file_list's php binary is modified

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 20:15:04 GMT 2024

Total time taken to generate the page: 0.03572 seconds