SQLLite - Memory vs. MySQL

[Michael_Morris1]

Sometimes guys you can post something that sets off a spark in someone else so be careful

[QUOTE=Ren;4951636]

$pdo = new PDO('sqlite::memory:', '', '');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE Test(id INTEGER NOT NULL PRIMARY KEY, name TEXT)');

$pdo->exec('INSERT INTO Test(name) VALUES(\\'Rod\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Jane\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Freddy\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Tom\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Dick\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Harry\\')');

$q = $pdo->prepare('SELECT id, name FROM Test ORDER BY name ASC');
$q->execute();
$r = $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

var_dump($r);

[/quote]

Before this example by Ren I didn’t know about ‘sqlite::memory’ as a database. I still don’t know that much but I have modified my database class to accept it’s dsn with no user and password for the purpose of testing. An SQL db that exists entirely in memory has enormous advantages over having an active database for testing since the test can create a fresh database, compose the tables and test data required for the test and then execute the test. More importantly configuration and connectivity issues with the database don’t affect the test at hand although they will of course receive their own tests.

What I’d like to know is this - what are the differences between SQLLite and MySQL - because these differences will obviously control what I can test for and how the tests are written. I’m going to presume SQLLite doesn’t support ENUM and REPLACE INTO and for database portability I don’t use those features of MySQL because they are unique to it.

(Well, that isn’t entirely true - I don’t use them in my framework heart. I might use them in specific projects, but not in the belly of the beast as it were).

Any good online documentation on SQLLlite someone could point me to?

[wwb_99]

Anything wrong with the sqllite site @ http://www.sqlite.org/ . . .

[Michael_Morris1]

That’s a wonderful starting point. Still, what are the major differences with MySQL, particularly ones that could affect Unit Tests. Critically, is SQL Lite persistent between PHP sessions?

[wwb_99]

If it is running in-memory then it persists as long as it is running so probably not but I’ve never really tried what you are doing . . .

[Michael_Morris1]

wwb_99> Not sure if that post adds up to the question of “what are you doing?” but I’ll explain that just in case you are - or anyone else - is wondering. I’m strongly considering using SQLLite in memory mode for my unit tests - these are blocks of code that test the functionality of other blocks of code. For this reason permanence is a drawback, not an asset. After the test runs the data needs to just go away. Much of it will be randomly generated and nonsensical in any event.

Now there are certainly tests I’ll have to run with a real database and against MySQL itself.

Part of the functionality of my framework is a database administration layer similar to PHPMyAdmin, but unlike PHPMyAdmin it tracks a great deal of meta information about the the tables and their relationships to each other. In this sense it is an ORM, and yes I’ve looked at what’s out there but I don’t agree with the tact the existing ORM’s are using of trying to solve every possible data lookup problem. Personally I view data reading as a separate issue entirely from data writing. The first part of my system, the reading section, is very basic because it expects the user to compose their own queries and exists to aid with this as well as provide some post query object composition capability - most difficult of which to write was the tier collation object which, while only 150 lines of active code (190 lines of comments) has to pass through 22 separate tests. What it does is very cool in my opinion, it takes the results from a query like this:

SELECT
  b.id AS id,
  b.serial AS serial,
  b.year AS year,
  o.name AS owner_name,
  t.id AS transaction_id,
  t.date AS transaction_date,
  t.amount AS transaction_amount

And organizes the result into a tiered object

array (
  0 => array (
    'serial' => '',
    'year' => '',
    owner => array (
      'name' => '',
    ),
    'transaction' => array (
      279 => array (
        'amount' => '',
      ),
      290 => array (
        'amount' => '',
      )
    )
  );

The collator object doesn’t need to know the database structure in any way - instead it takes advantage of SQL field name aliases and asks you to underscore delimit the fields to direct it on how things should be collated and that keys are “id”. It has it’s limitations - if it sees only one result it assumes that means an N => 1 relationship unless you gave it keys as part of the query - if that happens it doesn’t make that assumption. It’s possible to get tripped up, but it accomplishes what I set out for it - to handle 90% of the collations I work with day to day. If I need something more specific I’ll write code for that situation by extending the collator.

Back to the question of the thread - the next part I’m writing is the administration and write layer. This layer has two tasks, to compose queries and it is also aware of the HTML 5 form controls that are going to be used to gather the data. It’s not something new to me, but an evolution of a system I wrote the first iteration of some 6 years ago. Testing this layer has always been a bear partially because the code that administrates the tables is itself dynamic - an “eat your own dogfood” approach that makes the system very tight but makes initial building difficult.

A memory database which is set up by the test code from a test sql file solves a lot of the testing problems. But I don’t want to end up writing the tests in SQL Lite and having things fall apart once production starts against a live database. On the flipside, by testing on two SQL systems the chances of painting myself into a “must use X db engine” corner is lessened.

One of the reasons for that is well, bluntly, I don’t trust Oracle. There’s not a reason to migrate yet in my mind, but I’m considering it all the same once more important considerations are handled.