Serendipity Forums

User and developer community
https://board.s9y.org/

Fresh Install with sqlite3

https://board.s9y.org/viewtopic.php?t=15519

Page 1 of 2

Fresh Install with sqlite3

Posted: Fri Jul 10, 2009 8:39 am
by 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?

Re: Fresh Install with sqlite3

Posted: Fri Jul 10, 2009 10:22 am
by 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

Re: Fresh Install with sqlite3

Posted: Fri Jul 10, 2009 2:50 pm
by 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.

Re: Fresh Install with sqlite3

Posted: Fri Jul 10, 2009 3:20 pm
by 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

Re: Fresh Install with sqlite3

Posted: Fri Jul 10, 2009 3:37 pm
by 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?

Re: Fresh Install with sqlite3

Posted: Fri Jul 10, 2009 5:07 pm
by 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

Re: Fresh Install with sqlite3

Posted: Sun Oct 25, 2009 9:45 pm
by 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

Re: Fresh Install with sqlite3

Posted: Sun Oct 25, 2009 10:46 pm
by 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

Re: Fresh Install with sqlite3

Posted: Sat Nov 28, 2009 12:54 am
by 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. :mrgreen:

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? :D

Re: Fresh Install with sqlite3

Posted: Sat Nov 28, 2009 1:28 pm
by 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

Re: Fresh Install with sqlite3

Posted: Sun Oct 10, 2010 1:10 am
by 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). :oops:

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;
  }
  
  /**

Re: Fresh Install with sqlite3

Posted: Sun Oct 10, 2010 3:59 pm
by 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')";

Re: Fresh Install with sqlite3

Posted: Sun Oct 10, 2010 8:18 pm
by garvinhicking
Hi!

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

Regards,
Garvin

Re: Fresh Install with sqlite3

Posted: Tue Oct 12, 2010 2:38 pm
by garvinhicking
Hi!

Committed!

Regards,
Garvin

Re: Fresh Install with sqlite3

Posted: Wed Oct 13, 2010 3:49 pm
by 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 :? ).
All times are UTC+02:00
Page 1 of 2

Powered by phpBB® Forum Software © phpBB Limited