Codoforum related discussions
Sqlite installation

Are there any instructions of how to use it with Sqlite ?

I am using XAMPP and codoforum v4.4.
I tried to use the SQLITE db and get the following error:

Fatal error: Uncaught InvalidArgumentException: Database does not exist. in C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\SQLiteConnector.php:32 Stack trace: 
#0 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\ConnectionFactory.php(59): Illuminate\Database\Connectors\SQLiteConnector->connect(Array) 
#1 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\ConnectionFactory.php(47): Illuminate\Database\Connectors\ConnectionFactory->createSingleConnection(Array) 
#2 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\DatabaseManager.php(127): Illuminate\Database\Connectors\ConnectionFactory->make(Array, 'default') 
#3 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\DatabaseManager.php(63): Illuminate\Database\Data in C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\SQLiteConnector.php on line 32
Are there any instructions of how to use it with Sqlite ? I am using XAMPP and codoforum v4.4. I tried to use the SQLITE db and get the following error: ```` Fatal error: Uncaught InvalidArgumentException: Database does not exist. in C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\SQLiteConnector.php:32 Stack trace: #0 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\ConnectionFactory.php(59): Illuminate\Database\Connectors\SQLiteConnector->connect(Array) #1 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\ConnectionFactory.php(47): Illuminate\Database\Connectors\ConnectionFactory->createSingleConnection(Array) #2 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\DatabaseManager.php(127): Illuminate\Database\Connectors\ConnectionFactory->make(Array, 'default') #3 C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\DatabaseManager.php(63): Illuminate\Database\Data in C:\xampp\htdocs\codoforum-sqlite\sys\vendor\illuminate\database\Illuminate\Database\Connectors\SQLiteConnector.php on line 32 ````

The sql statement at Topic.php:180 seem not valid for sqlite. The 'field' function is not supported.

commenting out the order() , it working again:

               ' t.topic_status=' . Forum::STICKY_CLOSED . ' ) AND ' .....
               //'ORDER BY Field(t.topic_status, ' . Forum::APPROVED_CLOSED.....
               //' . Forum::APPROVED .  ' , ' . Forum::STICKY . ' ) DESC, t.last_.......
               'LIMIT  ' . $num_posts . ' OFFSET ' . $from;

br

The sql statement at Topic.php:180 seem not valid for sqlite. The 'field' function is not supported. commenting out the order() , it working again: ```` ' t.topic_status=' . Forum::STICKY_CLOSED . ' ) AND ' ..... //'ORDER BY Field(t.topic_status, ' . Forum::APPROVED_CLOSED..... //' . Forum::APPROVED . ' , ' . Forum::STICKY . ' ) DESC, t.last_....... 'LIMIT ' . $num_posts . ' OFFSET ' . $from; ```` br

Patch (probably incomplete):

At Topic.php:180 add :

     $qry_sqlite = 'SELECT  p.reputation,p.post_id, p.omessage AS message, p.post_created, r.rid, u.id, ' .
          'u.name as name, u.avatar, c.cat_id, c.cat_img, c.cat_alias, ' . 
          't.topic_id, t.uid, t.title, t.no_posts, t.no_views, ' . 
          't.last_post_time, t.last_post_uid, last_post_id, t.topic_status, ' . 
          't.last_post_name AS last_post_name, t.topic_created ' . 
          'FROM codo_topics AS t ' . 
          'LEFT JOIN codo_posts AS p ON (t.post_id=p.post_id AND p.post_status=1)' . 
          'LEFT JOIN codo_users AS u ON u.id=p.uid ' . 
          'LEFT JOIN codo_categories AS c ON c.cat_id=t.cat_id ' . 
          'LEFT JOIN ' . PREFIX . 'codo_user_roles AS r ON u.id=r.uid AND r.is_primary=1     ' . 
          'WHERE ( ' . ' t.topic_status=' . Forum::APPROVED . ' OR' . 
          ' t.topic_status=' . Forum::APPROVED_CLOSED . ' OR' . 
          ' t.topic_status=' . Forum::STICKY . ' OR' . 
          ' t.topic_status=' . Forum::STICKY_CLOSED . ' ) AND ' . 
          $this->getViewTopicPermissionConditions() . 
          'ORDER BY CASE t.topic_status ' . 
          'WHEN ' . Forum::APPROVED_CLOSED . ' THEN 0 ' . 
          'WHEN ' . Forum::STICKY_CLOSED . ' THEN 1 ' . 
          'WHEN ' . Forum::APPROVED . ' THEN 2 ' . 
          'WHEN ' . Forum::STICKY . ' THEN 3 ' . 'END, ' .
          ' t.last_post_time DESC ' . 
          'LIMIT  ' . $num_posts . ' OFFSET ' . $from;    

     if ($GLOBALS['config']['driver'] === 'sqlite') {
         $qry = $qry_sqlite;
     }

Before:

     $ans = $this->db->query($qry);

At SpamFilter.php:18 add:

     //disable spam , as it tries to connect to mySQL database 
     $this->use = false;
           if (!$this->use) {
           return;
      }
Patch (probably incomplete): At Topic.php:180 add : $qry_sqlite = 'SELECT p.reputation,p.post_id, p.omessage AS message, p.post_created, r.rid, u.id, ' . 'u.name as name, u.avatar, c.cat_id, c.cat_img, c.cat_alias, ' . 't.topic_id, t.uid, t.title, t.no_posts, t.no_views, ' . 't.last_post_time, t.last_post_uid, last_post_id, t.topic_status, ' . 't.last_post_name AS last_post_name, t.topic_created ' . 'FROM codo_topics AS t ' . 'LEFT JOIN codo_posts AS p ON (t.post_id=p.post_id AND p.post_status=1)' . 'LEFT JOIN codo_users AS u ON u.id=p.uid ' . 'LEFT JOIN codo_categories AS c ON c.cat_id=t.cat_id ' . 'LEFT JOIN ' . PREFIX . 'codo_user_roles AS r ON u.id=r.uid AND r.is_primary=1 ' . 'WHERE ( ' . ' t.topic_status=' . Forum::APPROVED . ' OR' . ' t.topic_status=' . Forum::APPROVED_CLOSED . ' OR' . ' t.topic_status=' . Forum::STICKY . ' OR' . ' t.topic_status=' . Forum::STICKY_CLOSED . ' ) AND ' . $this->getViewTopicPermissionConditions() . 'ORDER BY CASE t.topic_status ' . 'WHEN ' . Forum::APPROVED_CLOSED . ' THEN 0 ' . 'WHEN ' . Forum::STICKY_CLOSED . ' THEN 1 ' . 'WHEN ' . Forum::APPROVED . ' THEN 2 ' . 'WHEN ' . Forum::STICKY . ' THEN 3 ' . 'END, ' . ' t.last_post_time DESC ' . 'LIMIT ' . $num_posts . ' OFFSET ' . $from; if ($GLOBALS['config']['driver'] === 'sqlite') { $qry = $qry_sqlite; } Before: $ans = $this->db->query($qry); At SpamFilter.php:18 add: //disable spam , as it tries to connect to mySQL database $this->use = false; if (!$this->use) { return; }

Another issue when deleting a Thread:

<b>Fatal error</b>:  Uncaught Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 near &quot;n&quot;: syntax error (SQL: DELETE n, t  FROM  codo_notify n INNER JOIN codo_notify_text t ON n.nid=t.id  WHERE n.type = :type AND t.status_link = 4) in C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php:555
Stack trace:
#0 C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php(362): Illuminate\Database\Connection-&gt;run('DELETE n, t  FR...', Array, Object(Closure))
#1 C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php(319): Illuminate\Database\Connection-&gt;affectingStatement('DELETE n, t  FR...', Array)
#2 [internal function]: Illuminate\Database\Connection-&gt;delete('DELETE n, t  FR...', Array)
#3 C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Capsule\Manager.php(284): call_user_func_array(Array, Array)
#4 D:\_la in <b>C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php</b> on line <b>555</b><br />
"
Another issue when deleting a Thread: ```` &lt;b&gt;Fatal error&lt;/b&gt;: Uncaught Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 near &amp;quot;n&amp;quot;: syntax error (SQL: DELETE n, t FROM codo_notify n INNER JOIN codo_notify_text t ON n.nid=t.id WHERE n.type = :type AND t.status_link = 4) in C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php:555 Stack trace: #0 C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php(362): Illuminate\Database\Connection-&amp;gt;run(&#039;DELETE n, t FR...&#039;, Array, Object(Closure)) #1 C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php(319): Illuminate\Database\Connection-&amp;gt;affectingStatement(&#039;DELETE n, t FR...&#039;, Array) #2 [internal function]: Illuminate\Database\Connection-&amp;gt;delete(&#039;DELETE n, t FR...&#039;, Array) #3 C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Capsule\Manager.php(284): call_user_func_array(Array, Array) #4 D:\_la in &lt;b&gt;C:\xampp\htdocs\forum\sys\vendor\illuminate\database\Illuminate\Database\Connection.php&lt;/b&gt; on line &lt;b&gt;555&lt;/b&gt;&lt;br /&gt; &quot; ````

Hi,

It's great that you are trying to run codoforum with sqlite but we did not actually build it for sqlite so you may get many more errors as you keep testing it specially the search queries.

Let me know if you need any help with the queries though.

Hi, It&#039;s great that you are trying to run codoforum with sqlite but we did not actually build it for sqlite so you may get many more errors as you keep testing it specially the search queries. Let me know if you need any help with the queries though.
Necessity is the mother of all inventions!

Hi @admin ,

actually I am trying to provide you with some testing feedback (and/or corrections), with the hope to intergrate/fix any of these in next release.

  • My opinion is that sqlite is the way to go for small/or-medium deployments, since it offers more portability/backup than mysql.

  • I have replace the incorrect SQL statement with:
    At Notifier.php : public function unlinkNotify($type, $statusLink) :

if ($GLOBALS['config']['driver'] === 'sqlite') {

        //Seems that the statement is not the same ( it does not delete from different tables).
        $q = "DELETE FROM codo_notify "
            ."WHERE nid IN ("
            ."SELECT nid FROM codo_notify n "
            ."INNER JOIN codo_notify_text t ON n.nid=t.id  "
            ."WHERE n.type = :type AND t.status_link =" . $statusLink . " );";
         }

The above statement does not seem like 1:1 with the SQL. If you or someone can provide a valid one would be great smile

  • So far , I am able to post/delete/reply and get notifications without any problems.
  • I did notice that the database is not cleared correctly (if at all):

    1. Write some sample threads/posts with some "@admin notification" on it.
    2. Delete all Threads/posts from the forum
    3. Check the database (either SQL or SQLite), the notification tables (as well as the thread/post tables) are not deleted.

      Is this a feature or bug ?

regards.

Hi @admin , actually I am trying to provide you with some testing feedback (and/or corrections), with the hope to intergrate/fix any of these in next release. - My opinion is that sqlite is the way to go for small/or-medium deployments, since it offers more portability/backup than mysql. - I have replace the incorrect SQL statement with: At Notifier.php : public function unlinkNotify($type, $statusLink) : ```` if ($GLOBALS[&#039;config&#039;][&#039;driver&#039;] === &#039;sqlite&#039;) { //Seems that the statement is not the same ( it does not delete from different tables). $q = &quot;DELETE FROM codo_notify &quot; .&quot;WHERE nid IN (&quot; .&quot;SELECT nid FROM codo_notify n &quot; .&quot;INNER JOIN codo_notify_text t ON n.nid=t.id &quot; .&quot;WHERE n.type = :type AND t.status_link =&quot; . $statusLink . &quot; );&quot;; } ```` The above statement does not seem like 1:1 with the SQL. If you or someone can provide a valid one would be great :) - So far , I am able to post/delete/reply and get notifications without any problems. - I did notice that the database is not cleared correctly (if at all): 1. Write some sample threads/posts with some &quot;@admin notification&quot; on it. 2. Delete all Threads/posts from the forum 3. Check the database (either SQL or SQLite), the notification tables (as well as the thread/post tables) are not deleted. Is this a feature or bug ? regards.
145
5
3
live preview
enter atleast 10 characters
WARNING: You mentioned %MENTIONS%, but they cannot see this message and will not be notified
Saving...
Saved
With selected deselect posts show selected posts
All posts under this topic will be deleted ?
Pending draft ... Click to resume editing
Discard draft