FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum Development » Bug Reports » Several topics with sqlite
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Several topics with sqlite [message #159561] Thu, 11 June 2009 05:04 Go to next message
icarus is currently offline  icarus   Germany
Messages: 52
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user

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
Re: Several topics with sqlite [message #159571 is a reply to message #159561] Thu, 11 June 2009 11:54 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3631
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
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.
Re: Several topics with sqlite [message #159622 is a reply to message #159571] Sat, 13 June 2009 03:47 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3631
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
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.
Re: Several topics with sqlite [message #159670 is a reply to message #159622] Mon, 15 June 2009 07:26 Go to previous messageGo to next message
icarus is currently offline  icarus   Germany
Messages: 52
Registered: May 2005
Karma: 0
Member
add to buddy list
ignore all messages by this user

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
Re: Several topics with sqlite [message #159671 is a reply to message #159670] Mon, 15 June 2009 13:13 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3631
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
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 {
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
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) ] [ RSS ]

Current Time: Tue Dec 12 01:36:53 EST 2017

Total time taken to generate the page: 0.00639 seconds