The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.

NAME

PQL::Cache - A mini in memory database using pure perl and PQL - PerlQueryLanguage - to store hashes and objects.

PQL is similar to DBIx::Class API, so switching to DBIx and SQL databases is possible.

It uses pure Perl (no compilation needed), so you can run it from a USB stick.

VERSION

This documentation refers to version 0.800 of PQL::Cache

SYNOPSIS

use PQL::Cache qw(:all);

Create Cache

  my $cache = PQL::Cache->new();
  $cache->set_table_definition
	('person',
	 {
		 keys    => ['ID'],
		 columns => [prename => surname => birth => gender => 'perl_level']
	 });

Insert

  $cache->insert(person => {
	ID       => $person_id++,
	prename  => 'Ralf',
	surname  => 'Peine',
	gender   => 'male',
	birth    => '1965-12-29',
	location => 1,             # cannot be searched!
	perl_level => 'CPAN',
  });

Select

  my $result = $cache->select
	(what  => 'all',
	 from  => 'person',
	 where => [perl_level => 'beginner']
        );

  my $first_matching_person = $result->[0];

  $result = $cache->select
	(what  => 'all',
	 from  => 'person',
	 where => [ perl_level => {like => 'founder.*'}
		   ]
         );

  $result = $cache->select
	(what  => 'all',
	 from  => 'person',
	 where => [ prename => [Larry => Damian => 'Audrey']
			]
        );

  $result = $cache->select
        (what  => 'all',
         from  => 'person',
         where => [ birth      => { like => '^196'},
                    perl_level => { like => 'founder'},
                                         ]]
        );

  $result = $cache->select
        (what  => 'all',
         from  => 'person',
         where => [ birth => { ge => '1960' },
		    birth => { le => '1960' },
	 ]);

  # and is default
  $result = $cache->select
        (what  => 'all',
         from  => 'person',
         where => [ gender  => 'male',
                    like => { surname => '.a.'}
                        ]
          );

  $result = $cache->select
        (what  => 'all',
         from  => 'person',
         where => [ obj => sub { $_->{birth} le '1960' }]
        );

  $result = $cache->select
	(what  => 'all',
	 from  => 'person',
	 where => [ obj => sub { $_->get_birth() ge '1960' }]
	);

  # result rows as hashes
  $result = $cache->select
      (what  => [qw (prename surname)],
       from  => 'person',
       where => [ gender => 'male',
                ]
      );

  my $first_result_hash = $result->[0];

Update

my $read_person->{surname} = 'AnotherName';

Update whole index for searching after changing some objects

$cache->update_index('person');

Waits for implementation:

$cache->update_index('person', $read_person);

Delete

Same where conditions as select().

my $deleted_rows = $cache->delete
      (what  => 'all',
       from  => 'person',
       where => [prename => 'FORREST L.']
      );

DESCRIPTION

PQL::Cache implements a small in memory database using pure Perl. It stores also objects in tables, not only hashes.

You can define tables and columns. You can insert, select and delete objects. You get back objects or hashes containing columns by select(...);

It can be dumped, like a simple hash containing objects.

PQL - Perl Query Language - is something similar to SQL and uses DBIx::Class API. So you have a little more checking by compiler and you can switch to a real database if RAM is expended or you want to save data to persistent storage.

How it works

Data Storage

Data, that are objects or hashes, are stored in a simple arrays. There are no copies created, just the references are stored. The index in the array is used in all other internal storage to reference the object/hash.

Regex string matches

The values for all columns defined are stored in an extra array as one string per object/hash, to start fast regex searches for multiple columns in single grep statement. If data changes, its string in this array has to be updated, thats the update method is for.

Keys

Every column defined as key column gets its own hash with value of column as key. These key columns should contain different values for every or nearly every stored object. It is used to fast preselect one or some objects. The key column hash contains an array for every key value, that contains the indexes of the matching objects. This needs also to be updated if key column of objects/hashes changes.

Current no change control

There is currently no control, if an object or hash has changed. This has to be checked by the user.

No persistence, no transactions

This is a cache, there is no persistence implemented. To get persistence, use real databases and DBIx or store contents into files. Or just dump the whole cache, if all stored objects can be dumped.

Use cases

I used this cache to read in files, tables out of spreadsheets or whatever and then search after data like a small database.

It is very comfortable for testing, a dumped Cache is loaded in seconds and then more than 1000 selects per second are possible. Show me a database that is that fast!

It may also be interesting, to use it as cache for a repository using DBIx::Class.

Memory And Performane

Tables with 100,000.0 entries and more are possible and can be managed without problems. It depends only on RAM for your perl process. RAM needed may be up to 3 times more than for an array of original objects. That depends on number of columns and, more expensive, on number of key columns used.

Try example/performance.pl to find out max entries and speed on your machines. On mine 1 Mio objects are possible to handle.

More description will follow up.

Missing Features

Using of object get-subs for column values waits for implementation. Currently only direct access per hash key is supported.

LICENSE AND COPYRIGHT

Copyright (c) 2014 by Ralf Peine, Germany. All rights reserved.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.6.0 or, at your option, any later version of Perl 5 you may have available.

DISCLAIMER OF WARRANTY

This library is distributed in the hope that it will be useful, but without any warranty; without even the implied warranty of merchantability or fitness for a particular purpose.