Fresh Install with sqlite3

[kc0dxf]

I'm trying to do a fresh install with v1.4.1 on Debian Lenny. I'm getting an error on the database extension. I have sqlite3 installed. I created a database (sqlite3 serendipity.db) in the root serendipity directory and inserted a table named serendipity but I still get the error. What should I do? Also, no link to expert mode. Is there a different way to get into expert mode?

[garvinhicking]

Hi!

No, s9y does that for you! No need to do any SQLite stuff! Remove the file, and just point yoru browser to http://yourhost/serendipity/ to see the instaler, there you choose SQLite and s9y creates it.

s9y requires PHP5 for SQLite.

HTH,
Garvin

[kc0dxf]

Yes, I'm using php5. There is no way to select a database. I don't see a way to get into expert mode. There is no link at the bottom. When I delete the serendipity.db, it does the same thing.

[garvinhicking]

Hi!

So when you go to the s9y installation screen, you first see the diagnose results, and at the end you see "Simple installation" and "Expert installation", right?

If those links do not appear, check the diagnose screen, you should see errors and reasons for why there is no link at the bottom.

Clickin on either of them should forward you to a page where you can enter the values for your installation. In the first section you see a database dropdown, you must change that to "SQLite". If no SQLite is available that would mean your PHP5 is compiled without SQLite, and you must recompile it or install the missing module first to be able to use sqlite.

If this all looks odd to you, please make screenshots of what you are seeing.

Regards,
Garvin

[kc0dxf]

At the bottom is an error message, "Due to a problematic diagnostic, you cannot continue with the installation until the above errors are fixed" and then a link to "Recheck Installation." No other links. The only item in red is for database extensions which suggests there are none. I installed sqlite3 from tar file, not apt repository. so sqlite3 binary is in /usr/local/bin/sqlite3. Would this make any difference?

[garvinhicking]

Hi!

Ok, then you must compile and/por enable the PHP5 modules, as you have no DB extensions!

"apt-get install php5-sqlite" might help. This PHP module is completely unrelated to your sqlite3 binary!!

Regards,
Garvin

[coderegard]

s9y using Sqlite3 did not work for me. Here is some info from my experience.

I tried setting up s9y 1.4.1. I have PHP 5.2.4 installed. “php –m” returns a list that contains “pdo_sqlite” and I can read a sqlite3 db file using the simple php test webpage.

However, s9y did not recognize sqlite on the installation page. Instead only mysql and mysqli were recognized.

Looking at the s9y code, I noticed a check for sqlite and SQLlite3 in functions_config.inc.php

==================================
if (extension_loaded('sqlite')) {
$res['sqlite'] = 'SQLite';
}
if (extension_loaded('SQLite3')) {
$res['sqlite3'] = 'SQLite3';
}
==================================

However, when running a simple test locally, sqlite and SQLite3 do not return ‘1’ for my setup. Instead, I get a return of ‘1’ for extension_loaded(‘pdo_sqlite’);

The code that I use to access the sqlite3 db in my simple test uses pdo.

Example:
$dbh = new PDO('sqlite:sqlite3db.sq3');
foreach ($dbh->query('select * from some_table;') as $row){
echo $row[1].'|'.$row[2].'<br />';
}

I have read that sqlite3 requires the use of PDO since php5.1.0
(source: http://www.mediawiki.org/wiki/Manual:SQLite)

In the s9y code, I am seeing references to “sqlite3_open()”, which I am guessing was the pre php5.1 way to open sqlite3 files.

Is sy9 compatible with sqlite3 using php5.1.0 or greater? Am I missing something easy here?

Thanks!
Mike

[garvinhicking]

Hi!

Currently, s9y's db layer is not for PDO-SQLite, only sqlite3 as if compiles from PECL; I wasn't aware that this does not work anymore.

However the s9y DB layer is quite easy, maybe you'd like to have a try creating your custom pdo-sqlite.inc.php layer based on the current sqlite3 one? We could then include your contribution?

Regards,
Garvin

[nth]

Hi there!

I'm in the middle of migrating my home server to a new machine and stumbled on this same issue. Given the (small) size of my db and that the machine is used for more than webserving, I wanted to stick to sqlite. So check the attachment.

I adapted pdo-postgres.inc.php with bits and pieces from sqlite3.inc.php and dropped it back in /include/db and modified /include/functions_config.inc.php from

Code: Select all

            if (extension_loaded('PDO')) &&
        	in_array('pgsql', PDO::getAvailableDrivers())) {
                $res['pdo-postgres'] = 'PDO::PostgreSQL';
            };

to

Code: Select all

            if (extension_loaded('PDO')) {
        	if (in_array('pgsql', PDO::getAvailableDrivers())) {
            	    $res['pdo-postgres'] = 'PDO::PostgreSQL';
            	};
        	if (in_array('sqlite', PDO::getAvailableDrivers())) {
            	    $res['pdo-sqlite'] = 'PDO::SQLite';
            	};
            }

Seems to work ok to me, but then again this is my 4th-ish time in php and a first with PDO and sqlite. Maybe we'll see a tried&tested version of this in 1.5?

[garvinhicking]

Hi!

Hey, great work. Thanks a lot for sharing. At first glance it looks well, so I just committed it to make it available for future testing

Best regards,
Garvin

[nth]

sqlite3.inc.php emulated num and assoc fetch results so I did it as well, without realizing PDO takes care of this as well, hence this wasn't only unneeded, but also caused trouble in some situations (changes to static pages could not be saved, for example).

Code: Select all

*** pdo-sqlite.inc.php.orig    2009-11-28 14:27:42.000000000 +0200
--- pdo-sqlite.inc.php    2010-10-10 01:35:19.000000000 +0300
***************
*** 39,58 ****
          $newrow[preg_replace('@^.+\.(.*)@', '\1', $i)] = str_replace($search, $replace, $v);
      }
  
!     if ($type == PDO::FETCH_NUM)
!         $frow = array();
!     else
!         $frow = $newrow;
! 
!     if ($type != PDO::FETCH_ASSOC) {
!         $i = 0;
!         foreach($newrow AS $k => $v) {
!             $frow[$i] = $v;
!             $i++;
!         }
!     }
! 
!     return $frow;
  }
  
  /**
--- 39,45 ----
          $newrow[preg_replace('@^.+\.(.*)@', '\1', $i)] = str_replace($search, $replace, $v);
      }
  
!     return $newrow;
  }
  
  /**

[nth]

The following take care of a few other hickups caused by the fact that the db type is no longer 'sqlite3', but 'pdo-sqlite'. It also contains the quicksearch patch here, that seems to still be missing.

Code: Select all

*** include/functions_entries.inc.php.orig    2010-07-25 01:26:43.000000000 +0300
--- include/functions_entries.inc.php    2010-10-10 15:43:25.000000000 +0300
***************
*** 770,778 ****
          } else {
              $cond['find_part'] = "(title ILIKE '%$term%' OR body ILIKE '%$term%' OR extended ILIKE '%$term%')";
          }
!     } elseif ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') {
          // Very extensive SQLite search. There currently seems no other way to perform fulltext search in SQLite
          // But it's better than no search at all :-D
          $cond['group']     = 'GROUP BY e.id';
          $cond['distinct']  = '';
          $term              = serendipity_mb('strtolower', $term);
--- 770,779 ----
          } else {
              $cond['find_part'] = "(title ILIKE '%$term%' OR body ILIKE '%$term%' OR extended ILIKE '%$term%')";
          }
!     } elseif ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
          // Very extensive SQLite search. There currently seems no other way to perform fulltext search in SQLite
          // But it's better than no search at all :-D
+         $term = str_replace( "*", "%", $term );
          $cond['group']     = 'GROUP BY e.id';
          $cond['distinct']  = '';
          $term              = serendipity_mb('strtolower', $term);
***************
*** 863,869 ****
  
      //if * wasn't already appended and if there are none or not enough
      //results, search again for entries containing the searchterm as a part  
!     if (strpos($term, '*') === false) {
          if (! is_array($search)) {
              return serendipity_searchEntries($term.'*', $orig_limit);
          }else if (count($search) < 4){
--- 864,873 ----
  
      //if * wasn't already appended and if there are none or not enough
      //results, search again for entries containing the searchterm as a part  
!     if ((strpos($term, '*') === false) &&
!             ($serendipity['dbType'] != 'sqlite') &&
!             ($serendipity['dbType'] != 'sqlite3') &&
!             ($serendipity['dbType'] != 'pdo-sqlite')) {
          if (! is_array($search)) {
              return serendipity_searchEntries($term.'*', $orig_limit);
          }else if (count($search) < 4){
***************
*** 947,953 ****
      global $serendipity;
  
      // The unique query condition was built previously in serendipity_fetchEntries()
!     if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') {
          $querystring  = "SELECT count(e.id) {$serendipity['fullCountQuery']} GROUP BY e.id";
      } else {
          $querystring  = "SELECT count(distinct e.id) {$serendipity['fullCountQuery']}";
--- 951,957 ----
      global $serendipity;
  
      // The unique query condition was built previously in serendipity_fetchEntries()
!     if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
          $querystring  = "SELECT count(e.id) {$serendipity['fullCountQuery']} GROUP BY e.id";
      } else {
          $querystring  = "SELECT count(distinct e.id) {$serendipity['fullCountQuery']}";
***************
*** 956,962 ****
      $query =& serendipity_db_query($querystring);
  
      if (is_array($query) && isset($query[0])) {
!         if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') {
              return count($query);
          } else {
              return $query[0][0];
--- 960,966 ----
      $query =& serendipity_db_query($querystring);
  
      if (is_array($query) && isset($query[0])) {
!         if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
              return count($query);
          } else {
              return $query[0][0];

Code: Select all

*** include/functions_installer.inc.php.orig    2009-06-02 15:49:26.000000000 +0300
--- include/functions_installer.inc.php    2010-10-10 13:21:43.000000000 +0300
***************
*** 742,748 ****
          $errs[] = sprintf(CANT_EXECUTE_BINARY, 'convert imagemagick');
      }
  
!     if ($_POST['dbType'] == 'sqlite' || $_POST['dbType'] == 'sqlite3') {
          // We don't want that our SQLite db file can be guessed from other applications on a server
          // and have access to our's. So we randomize the SQLite dbname.
          $_POST['sqlitedbName'] = $_POST['dbName'] . '_' . md5(time());
--- 742,748 ----
          $errs[] = sprintf(CANT_EXECUTE_BINARY, 'convert imagemagick');
      }
  
!     if ($_POST['dbType'] == 'sqlite' || $_POST['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
          // We don't want that our SQLite db file can be guessed from other applications on a server
          // and have access to our's. So we randomize the SQLite dbname.
          $_POST['sqlitedbName'] = $_POST['dbName'] . '_' . md5(time());

Code: Select all

*** include/plugin_internal.inc.php.orig    2009-02-10 11:10:36.000000000 +0200
--- include/plugin_internal.inc.php    2010-10-10 13:22:14.000000000 +0300
***************
*** 473,479 ****
  
          echo '<ul class="plainList">' . "\n";
          
!         if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') {
              $dist_sql = 'count(e.id) AS orderkey';
          } else {
              $dist_sql = 'count(DISTINCT e.id) AS orderkey';
--- 473,479 ----
  
          echo '<ul class="plainList">' . "\n";
          
!         if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
              $dist_sql = 'count(e.id) AS orderkey';
          } else {
              $dist_sql = 'count(DISTINCT e.id) AS orderkey';

Code: Select all

*** include/db/db.inc.php.orig    2007-07-12 14:23:05.000000000 +0300
--- include/db/db.inc.php    2010-10-10 13:36:01.000000000 +0300
***************
*** 124,129 ****
--- 124,130 ----
      switch($serendipity['dbType']) {
          case 'sqlite':
          case 'sqlite3':
+         case 'pdo-sqlite':
              $interval = $ival;
              $ts       = time();
              break;

Code: Select all

*** include/admin/category.inc.php.orig    2010-08-26 18:51:33.000000000 +0300
--- include/admin/category.inc.php    2010-10-10 13:38:30.000000000 +0300
***************
*** 73,79 ****
          $remaining_cat = (int)$serendipity['POST']['cat']['remaining_catid'];
          $category_ranges = serendipity_fetchCategoryRange((int)$serendipity['GET']['cid']);
          $category_range  = implode(' AND ', $category_ranges);
!         if ($serendipity['dbType'] == 'postgres' || $serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3') {
              $query = "UPDATE {$serendipity['dbPrefix']}entrycat
                          SET categoryid={$remaining_cat} WHERE entryid IN
                          (
--- 73,79 ----
          $remaining_cat = (int)$serendipity['POST']['cat']['remaining_catid'];
          $category_ranges = serendipity_fetchCategoryRange((int)$serendipity['GET']['cid']);
          $category_range  = implode(' AND ', $category_ranges);
!         if ($serendipity['dbType'] == 'postgres' || $serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
              $query = "UPDATE {$serendipity['dbPrefix']}entrycat
                          SET categoryid={$remaining_cat} WHERE entryid IN
                          (

Code: Select all

*** plugins/serendipity_event_statistics/serendipity_plugin_statistics.php.orig        2009-08-25 22:39:17.000000000 +0300
--- include/plugins/serendipity_event_statistics/serendipity_plugin_statistics.php   2010-10-10 16:49:11.000000000 +0300
***************
*** 247,253 ****
                  $max = time();
                  $min = $max - (15 * 60);

!                 if ($serendipity['dbType'] == 'sqlite') {
                      $max_ts = date('H:i', $max);
                      $min_ts = date('H:i', $min);
                      $q   = "SELECT count(counter_id) AS currentvisitors FROM {$serendipity['dbPrefix']}visitors WHERE day LIKE '" . date('Y-m-d') . "' AND (time BETWEEN '$min_ts' AND '$max_ts')";
--- 247,253 ----
                  $max = time();
                  $min = $max - (15 * 60);

!                 if ($serendipity['dbType'] == 'sqlite' || $serendipity['dbType'] == 'sqlite3' || $serendipity['dbType'] == 'pdo-sqlite') {
                      $max_ts = date('H:i', $max);
                      $min_ts = date('H:i', $min);
                      $q   = "SELECT count(counter_id) AS currentvisitors FROM {$serendipity['dbPrefix']}visitors WHERE day LIKE '" . date('Y-m-d') . "' AND (time BETWEEN '$min_ts' AND '$max_ts')";

[garvinhicking]

Hi!

Great, thanks for the work! I'll check into this the next days and commit it!

Regards,
Garvin

[garvinhicking]

Hi!

Committed!

Regards,
Garvin

[nth]

Thank you!

If you know of any other outstanding issues with pdo-sqlite, please let me know, I'm willing to do further maintenance as it benefits myself as well (I use it on my low-traffic personal blog, so it hardly gets any stress tests from my part - took me almost a year to spot the previous seemingly basic issues ).