Sphider can work with databases other than MySQL

Come here for help or to post comments on Sphider
Post Reply
User avatar
captquirk
Site Admin
Posts: 299
Joined: Sun Apr 09, 2017 8:49 pm
Location: Arizona, USA
Contact:

Sphider can work with databases other than MySQL

Post by captquirk »

While the PDO version of Sphider was created for users whose host does not support MySQLnd, it has another advantage.

By modifying the database.php file, Sphider can utilize databases other than MySQL. By changing the DSN (Data Source Name), Sphider can operate with:
MySQL
PostgreSQL
SQLite
Microsoft SQL Server
Sybase
Interbase
ODBC
Informix

SEE THE UPDATES BELOW...

There may be others I am not aware of.
No other code changes should be necessary as PDO is supposed to be portable.

UPDATE - 8 July 2017
"No other code changes should be necessary..." HA! Famous last words!
I have been fooling with SQLite, and guess what? It looks like there ARE changes necessary. I finally got the install script kind of running... At least the tables are being created. The query to show the database tables had to be rewritten. Now to trouble shoot all the other problems that are popping up. Now whether or not this is a SQLite specific issue or if it is endemic to all databases is unknown. Admittedly, SQLite IS a very basic database lacking some of the finer points of more sophisticated databases, so that may be the sticking point. We shall see...

UPDATE - 10 July 2017
By making a few code improvements in PDO Sphider and removing MySQL specific instances of coding with functional equivalent code (for example, a couple instances of sql statements used "&&" instead of the standard "AND", using PHP to get the current date/time instead of relying on MySQL's NOW() function), MOST of the code indeed IS portable! The exceptions are certain pieces of code which, out of necessity, have to be database specific. At his point, that looks like install.php, db_main.php, and db_backup.php. Update_rollup.php MIGHT be affected. At his point, except for the backup and restore functions (which are progressing), PDO is working with SQLite. It is very possible that with a bit more work, small packages may be provided to make PDO Sphider compatible with SQLite and PostgreSQL. Beyond that, we shall see.
timp
Posts: 5
Joined: Thu Oct 19, 2017 6:29 am

Re: Sphider can work with databases other than MySQL

Post by timp »

I finally got registered, although I can't connect from my home computer at present.

You might want to look at http://xpdo.org. I think it may smooth the differences more.

I am curious as to why you are creating multiple versions. It seems like that would create a lot of extra work, making sure they are 'in sync'.

Tim
User avatar
captquirk
Site Admin
Posts: 299
Joined: Sun Apr 09, 2017 8:49 pm
Location: Arizona, USA
Contact:

Re: Sphider can work with databases other than MySQL

Post by captquirk »

...why you are creating multiple versions. It seems like that would create a lot of extra work, making sure they are 'in sync'.
That is a good question. It IS a lot of work trying to keep them in sync! So why do it?

L-o-n-g answer:

I started out using 1.3.6 of the original Sphider. It served me well for the limited use I required (single site, no categories, didn't use the backup tool, etc.). Then I upgraded my site and started having problems... warnings about deprecated code and such. I looked into it and realized that there would come a point in time when it would just quit working altogether.

That led to my first set of modifications, which was to upgrade the deprecated MySQL extension to MySQLi. This worked well, so I started to investigate just what Sphider was capable of. The code was a tangled mess and I found little problems here and there. Whether these problems were native to Sphider or just a consequence of my upgrade, I really don't know. But I began fixing them as I found them, cleaning up the code as I went. I also shared my improvements. I then moved on to addressing some of the inherent security problems, and thus came prepared statements. This required another extension, MySQLnd. All seemed to be going well until I started getting reports that the new version just wouldn't work for some people.

It turns out, not ALL hosting companies make the MySQLnd extension available, especially when the user has the increasingly common shared hosting packages. Essentially, some of these companies offer MySQLnd only with more expensive hosting packages. Like it or not, that is the reality, and I can understand it from a business perspective. These same companies very commonly DO offer the PDO extension to those customers using shared hosting. So the PDO version was born.

QUESTION: Why not just make Sphider PDO only? Well, what I call "vanilla" is actually quite efficient and straight forward. It is easy to maintain, although not every one can use it. PDO, while more versatile, does come with overhead. Anything that comes as a "one size fits all" is going to actually be "one size works fairly well for most but will never be a perfect fit for any". But, supposedly, PDO DOES have the advantage of portability. I would have to say both "vanilla" and the MySQL/MariaDB PDO are here to stay.

If you read enough of the Sphider forum for the original Sphider, you will see questions/requests for SQLite support, and I believe even one for PostgreSQL. So, out of curiosity, I tested it for portability. Low and behold, it was close, but no cigar. PDO would actually be quite portable PROVIDED your SQL was all ANSI SQL. Spider is MOSTLY ANSI compliant, but not 100%, and it only takes one deviation to spoil the entire package. While a majority of the Sphider SQL is ANSI, there is more than just a single instance which is not, but is quite specific to the database being used. For example, when looking at the Database tab in Sphider, what you see is actually the results of queries to the structural tables of the database. And the structure and contents of those tables varies from database to database. There is just no practical way to write a single generic query that would work with two different databases, much less all of them. (MySQL and MariaDB being the sole exception.) Another issue is that PostgreSQL (and probably some others) have a different way of accessing the last insert id. PostgreSQL will not accept back-ticks (`) while both MySQL and SQLite like them. They aren't required but certainly do go a long way in avoiding some "misunderstandings" concerning table names. When you get into prepared statements, error handling becomes more critical because there are error statements and two flavors of error codes. One error code is a PDO error code and the other is driver (i.e., database) specific. Since Sphider does check for error codes, trying to figure out WHICH error code proved to be a major pain. "Vanilla" Sphider was easy, but each of the PDO's (MySQL, SQLite, and PostgreSQL) are all different.

SQL statements with limits are syntactically different between MySQL and PostgreSQL, although SQLite is like MySQL. Backup and restore routines (at least ones that work with some efficiency) have differences as well between each database.

What all this means is that there is an evident demand for both "vanilla" and PDO (MySQL/MariaDB) and they will continue. PDO variations for SQLite and PostgreSQL exist because there has been expressed (in the past) some interest in each and, being retired, I had the time and patience to provide them. As to whether or not I will continue to develop them remains to be seen. I'll monitor the download stats, and if they aren't being used, well, there they will be if anyone does want them. Just don't look for the latest features. Being PHP 7.1 compatible should at least make them viable for the foreseeable future. (I REALLY hope no one cares, because they are a real pain. The only reason they got this far is that I got curious and have trouble letting go when I find a problem!) And don't go looking for any other variations such as Cubrid or Firebird unless you want to make them yourself. I lost enough sleep with SQLite and PostgreSQL as it is.
timp
Posts: 5
Joined: Thu Oct 19, 2017 6:29 am

Re: Sphider can work with databases other than MySQL

Post by timp »

I downloaded all the version 2.0 varieties to see what is different.

I think you may have created quite a 'rod for your back'.

I use BeyondCompare for comparing text files. I haven't got far because there are so many sections that look like they are functionally identically but flow differently. It's very hard for me to determine where the code is actually different between the versions.

The little I have looked at looks like it could be covered by functions that work differently depending on the target database. This is quite easy to implement if object oriented code is used. (I can't see anything that looks object oriented, but I could be wrong)

I hope my comments are constructive. I am grateful to find that someone has put the work into getting Sphider up to date and working in the latest environments. I am using one of the other versions and had to make changes to get it to work.
User avatar
captquirk
Site Admin
Posts: 299
Joined: Sun Apr 09, 2017 8:49 pm
Location: Arizona, USA
Contact:

Re: Sphider can work with databases other than MySQL

Post by captquirk »

Many of the differences are very subtle within the PDO family. The vanilla vs MySQL DB PDO is more radical as the SQL syntax is very different. The MySQL/SQLite/PostgreSQL differences are more subtle. I did look at OOP (really only one small module is currently OOP), but because of different database syntax requirements, that really wouldn't simplify much. I suppose one COULD write a routine to determine WHICH database was being used, then option out the OOP routines. (The only reason there even are SQLite or PostgreSQL versions is that there have been inquires before. If actual demand for these is as low as expected, 2.0 may be the end of the line for them.)

What WOULD help is to make ALL of the SQL ANSI, then write special functions (which would add additional overhead and slow efficiency) for the areas in which ANSI SQL is insufficient for the task at hand. When it comes to the base tables of each given database, there is no ANSI workaround and there is no non-SQL method for retrieving the information. Db_main.php and db_backup.php are forever destined to be unique to the respective databases.

OOP is very advantageous for large systems in which objects and methods are used in multiple places for similar operations. For Sphider, it seems silly to design an object that gets used once. It is really a pretty simple system. That doesn't mean OOP is out of the question, just not high on my list of priorities.
timp
Posts: 5
Joined: Thu Oct 19, 2017 6:29 am

Re: Sphider can work with databases other than MySQL

Post by timp »

Although much of what I write these days is not OOP, because it's buried inside a CMS, I've been using it for years. (I was shocked to find that new graduates out of university hadn't even studied the topic, at the time).

I would tend towards having an object that represents the database, possibly the methods would be little more than wrappers around each call to the database.

I had a look a xPDO, which advertises as a 'light' wrapper around PDO. It didn't look very light, but maybe it's trying to be too generic. One thing about it did attract my attention - it also works for pre-PDO versions of PHP - so it's possible to have a class that covers both. I gather PDO has been available since about PHP 5.1, so there shouldn't be all that many sites without it these days.

I'm not fully retired, so I couldn't promise to create such a thing for Sphider. I probably need to study the existing code more first, anyhow. Which is the 'primary' version? Is it the MySQL without PDO version?

Tim
User avatar
captquirk
Site Admin
Posts: 299
Joined: Sun Apr 09, 2017 8:49 pm
Location: Arizona, USA
Contact:

Re: Sphider can work with databases other than MySQL

Post by captquirk »

I consider the MySQL version without PDO as the primary version. It is the most directly descended variation of the original by Ando Saabas. It is also the version I personally use.

The practical strategy would be to have a single version of Sphider using PDO, as you are correct that PDO is widely available. But I am retired, set in my ways, and probably not very practical anymore! PDO is a one-size-fits-all approach and thus there is some additional overhead. The (original) non-PDO approach is more direct and efficient. And, as it turns out, because Sphider does, in some instances, present SQL needs beyond ANSI, PDO isn't really one-size-fits-all. Thus, the minor variations between MySQL, SQLite, and PostgreSQL.

If I really had my way, there would only be the non-PDO version. Sphider could be implemented solely using the MySQLi extension, but that would leave a lot of security vulnerabilities. The standard answer is to use prepared statements, which while native to PDO, requires the MySQLnd extension for non-PDO use. I have found that many websites are using shared hosting. That makes sense from a cost perspective, but hosting companies are also businesses and there is a motivation for profit. VPS is more profitable. One way to drive business from shared hosting to VPS is to offer incentives to clients to make such a move. Removing MySQLnd support from shared hosting is one incentive some (but certainly not all) hosting companies use. It is just for that reason I even produced the PDO version. It will likely remain as there does seem to be a demand for its use (MySQL flavor), as there is for the non-PDO. PostgreSQL and SQLite flavors, not so much. They exist because 1) there was a request, and 2) I wanted to see if I could do it.

Just a note on OOP, I have never received any formal training in OOP, although I have an understanding and appreciation for its capabilities. I cut my programming teeth converting SPS code to Autocoder because the machines were getting these fantastic new add-ons called tape drives! I then moved on to a new project using COBOL-65 (pre-ANSI). I later spend the bulk of my time using C on SVR3/4 UNIX systems. By the time OOP became common place, I had already moved on to project management and consulting. I have been dabbling with OOP, not for Sphider, but for my database driven personal website. Maybe sometime I will see if OOP has a place in Sphider, but for now it just seems a lot of work to produce an assembly line for a production run of one unit.
Post Reply