REQ: SQL-database

[tstirler]

It would be nice to have the posibility of having the entire database on an mySQL-server.

That would make it a lot easier to handle the database more securely.

[Conor]

How would that be more secure than on your hard drive, where it's password protected and can be encrypted? If you have in mind using it as an export option for an online version the database is SQL based so can be imported into MySQL if you so desire by using SQLite to export it directly in SQL format. However you would have to build a page to display the data from the MySQL database.

[Alex]

Not to mention the fact that the application would take a performance hit having to query an external database. And add to that the fact that now you're dealing with a number of other external services (Mysql) that complicate things, you'd have to create users for access to the database, etc.

[Alex]

I was thinking that if you REALLY wanted to do something like this you could set up an export from SQLite to Mysql via a cron job (every night or whatever). Then write your php pages to query that mysql database and display your data in a browser, or just use the Mysql data as you see fit.

Sounds like a lot of work but doable if you really want that Msyql database.

[jicho]

Why not copy the Sqlite to your website and access the database with, for example PHP? You don't need MySQL.

Maybe this is something for you as a starter:
http://www.bruji.com/forum/viewtopic.ph ... 070#p13070

[Alex]

Why not copy the Sqlite to your website and access the database with, for example PHP? You don't need MySQL.

True, but I was thinking that if one is not as familiar with SQLite as with MySql then it might be a pain to learn the subtle differences and might result in a lot of desperate hair pulling

[thajeztah]

if one is not as familiar with SQLite as with MySql then it might be a pain to learn the subtle differences

As a developer I'd always suggest to use a database-abstraction layer, for example adodb (http://adodb.sourceforge.net/). This way you'll write database-independent code and it's even possible to switch to another database without having to re-write your code.

adodb (and the're other database-abstraction layer, this one just comes in mind first) supports both MySQL and SQLite so you can use the same code for both

[jicho]

True, but I was thinking that if one is not as familiar with SQLite as with MySql then it might be a pain to learn the subtle differences and might result in a lot of desperate hair pulling

It is probably more of a pain to export your pedia data to MySQL on a regulair basis And with the reaction of thajeztah there is no excuse not using the sqlite version

[Alex]

That's an interesting suggestion, I'll have to try that one day. When I started playing around with Mysql and PHP I don't know if these abstraction layers existed at all. I certainly don't remember hearing about them back then, but it does sound like a good idea.

Unfortunately, I often found myself having to use Mysql straight from the command line on multiple machines (Solaris, Linux, Mac, etc) and I doubt that the abstraction layer would have helped there. Of course, that was then, things are different now so bring on the abstraction layers!

One problem I have found with using abstraction layers (I'm thinking of PEAR here) is that it is not always installed. So if you get used to it, then find you have to develop a solution for a site without it installed, you're in a world of hurt. This is why I never used PEAR. It was a great idea, but I found that it was seldom installed on the servers out there. And I found that out the hard way: after having spent long hours programming everything locally on my machine only to find out nothing worked when I uploaded it. But, as I said, I'm sure things are different now, just make sure to check the server before you start developing.

[jicho]

Hi Alex,

Pear is more than an extra layer for a database connection. It can be installed on the server, but you can also download is and include in your project. http://adodb.sourceforge.net/ is only for a database connection an needs to be included in your project. Have a look a the adodb site, you'll finds some examples on the homepage. An example says more than 1000 words

[Alex]

Sounds good. I'll take a look when I get a chance. Again, way back when I looked at PEAR, there was no option to include it in your own project, it had to be installed on the server since you could not edit the php.ini that pointed to the include files.

[jicho]

Again, way back when I looked at PEAR, there was no option to include it in your own project, it had to be installed on the server since you could not edit the php.ini that pointed to the include files.

With ini_set and ini_get you can add paths to your include_path For as far as I know it works everywhere.

[thajeztah]

When I started using PHP, I used this approach (php_ini_set) to set include paths, however, performance-wise it's often better to include the path to the file when including files/libraries. Basically, create some 'constants' to your include-directories and put those in a settings-file that you include in every page;

example 1: add path information (absolute path)

Code: Select all

require '/var/www/mysite.com/includes/adodb/adodb.inc.php';
include '/var/www/mysite.com/includes/myclasses/my_class_include.php';

example 2: add relative information (more difficult are paths will be relative to the current file)

Code: Select all

require '../includes/adodb/adodb.inc.php';

example 3: use constants to set paths and use those to include your files

Code: Select all

// put this in a 'settings' file that is included in every script
define('INCLUDE_PATH', '/var/www/mysite.com/includes/');

// then use the constant everywhere
require INCLUDE_PATH . 'adodb/adodb.inc.php';
include INCLUDE_PATH . 'myclasses/my_class_include.php';

Some additional tips:

• Always try to put your actual php-scripts out of your webroot, e.g. not directly accessible via the browser and include them using php
• Download and have a look at some frameworks, like Zend-Framework or CakePHP. Take them as an example or use them. Although frameworks will never be faster than writing code on a 'per project' basis, they offer many advantages and make your code better to read for other users. They will save you from writing a lot of code (e.g. in CakePHP, create a 'model' class for a database-table and call '$model->findAll()' to return all records, without writing a single line of SQL..)