Author image Sivirinov Ivan

NAME

CCCP::SQLiteWrap - wrapper on SQLite (only for nix)

SYNOPSIS

    use CCCP::SQLiteWrap;
    
    my $db_path = '/abs/path/to/my/sqlite/base.db';
    
    my $dbh = CCCP::SQLiteWrap->connect($db_path);
    $dbh->check();
    
    $dbh->create_table(
        
        # description table artist 
        'artist' => {
            
            # fields on table artist
            fields => {
             'artist_id' => 'INTEGER',
             'name' => 'TEXT',
             'photo' => 'BLOB',
             'country'  => 'TEXT',
             'drags' => 'INTEGER'
            },
            
            # meta info about table artist
            meta => {
                default => [
                    'country' => 'Europe',
                    'drags' => 0
                ],
                not_null => [
                   'name'
                ],
                pk => ['artist_id'],
                unique => ['name'],
                index => [
                   ['name'],
                   ['name','country']
                ]
            }
        },

        # another table
        'albums' => {
            fields => {
             'album_id' => 'INTEGER',
             'album_name' => 'TEXT',
             'artist_id' => 'INTEGER',
             'photo' => 'BLOB',
             'style'  => 'TEXT'
            },
            
            meta => {
                not_null => [
                   'album_name'
                ],
                pk => ['album_id'],
                unique => ['album_name'],
                index => [
                   ['style'],
                ]            
            }
        },
        
        # and another table
        'dvd' => {
            fields => {
             'album_id' => 'INTEGER',
             'artist_id' => 'INTEGER',
             'dvd_name' => 'TEXT',         
            },
            
            meta => {
                not_null => [
                   'dvd_name'
                ],
                unique => ['dvd_name'],
                index => [
                   ['dvd_name'],
                   ['album_id','artist_id']
                ]            
            }       
        }
           
    );
    
    $dbh->create_trigger(
        'artist' => {
                'AFTER' => {
                    'DELETE' => [
                        'DELETE FROM dvd WHERE artist_id = OLD.artist_id',
                        'DELETE FROM albums WHERE artist_id = OLD.artist_id'
                    ],
                }
                
        },
        'albums' => {
                'AFTER' => {
                    'DELETE' => ['DELETE FROM dvd WHERE album_id = OLD.album_id'],
                }
        }       
    );
    
    $dbh->db->do('INSERT INTO ....');

IN SQL

    CREATE TABLE IF NOT EXISTS artist (
       'country' TEXT,
       'photo' BLOB,
       'artist_id' INTEGER,
       'name' TEXT NOT NULL UNIQUE,
       'drags' INTEGER,
       PRIMARY KEY ('artist_id')
    );
    CREATE INDEX _0x2ff7833b3b8d4334bc14bb74ac3769fc ON artist('name');
    CREATE INDEX _0x3d77f29481f34489a10b61be3da3602b ON artist('name','country');
    
    CREATE TABLE IF NOT EXISTS albums (
       'album_name' TEXT NOT NULL UNIQUE,
       'album_id' INTEGER,
       'photo' BLOB,
       'artist_id' INTEGER,
       'style' TEXT,
       PRIMARY KEY ('album_id')
    );
    CREATE INDEX _0xf1aa03bc417f467586b88eb676b125fc ON albums('style');
    
    CREATE TABLE IF NOT EXISTS dvd (
       'album_id' INTEGER,
       'artist_id' INTEGER,
       'dvd_name' TEXT NOT NULL UNIQUE
    );
    CREATE INDEX _0xd109df2d70fb4fe88332257e0c327446 ON dvd('dvd_name');
    CREATE INDEX _0xb324a617fe6b466685d3a9a9f0d4addc ON dvd('album_id','artist_id');
    
    DROP TRIGGER IF EXISTS trigger_artist_after_delete_5a7d596032b6cbc214cf64c48eddfad9;
    CREATE TRIGGER IF NOT EXISTS trigger_artist_after_delete_5a7d596032b6cbc214cf64c48eddfad9 
       AFTER DELETE ON artist FOR EACH ROW 
       BEGIN 
           DELETE FROM dvd WHERE artist_id = OLD.artist_id;
           DELETE FROM albums WHERE artist_id = OLD.artist_id; 
       END;
       
    DROP TRIGGER IF EXISTS trigger_albums_after_delete_911a284c6b402e9e87a67669a128d6cd;
    CREATE TRIGGER IF NOT EXISTS trigger_albums_after_delete_911a284c6b402e9e87a67669a128d6cd 
       AFTER DELETE ON albums FOR EACH ROW 
       BEGIN 
           DELETE FROM dvd WHERE album_id = OLD.album_id; 
       END;

DESCRIPTION

This is simple wrapper, for easy create database structure in SQLite. Faster DBI is only DBI ;) therefore you can access to DBI::db over "db" method.

Now, create table, index, trigger in SQLite is very simple.

Package METHODS

connect($abs_path)

Return CCCP::SQLiteWrap object.

Object METHODS

db

Access to DBI::db object.

check

This is very important method. If server is down, while some process write in your SQLite base, after restart, you can get error like "database disk image is malformed". This method re-dump (over sqlite3) your database if needed.

close

Close connect to base.

create_table(table1 => $param1, ..., tableN => $paramN)

Create table. For sqlite version version 3.6.19+ you can add foreign key:

    $dbh->create_table(
        
        ....
                
        'albums' => {
            fields => {
             'album_id' => 'INTEGER',
             'album_name' => 'TEXT',
             'artist_id' => 'INTEGER',
             'photo' => 'BLOB',
             'style'  => 'TEXT'
            },
            
            meta => {
                not_null => [
                   'album_name'
                ],
                pk => ['album_id'],
                unique => ['album_name'],
                index => [
                   ['style'],
                ],
                fk => [
                   'artist_id' => {
                       table => 'artist',
                       field => 'artist_id',
                       on_update => 'CASCADE',
                       on_delete => 'SET DEFAULT' 
                   },
                ]            
            }
        },
        
        ....
           
    );

create_trigger(table1 => $param1, ..., tableN => $paramN)

    $dbh->create_trigger(
        'table_name' => {
                'AFTER' => {
                    'DELETE' => [$query1,...,$queryN],
                    'INSERT' => [...],
                    'UPDATE' => [...]
                },
                'BEFORE' => {
                    ...
                }
        },
        
        ....       
    );

index_exists('table_name' => ['field1',...,'fieldN'])

If index exist return name or 0.

table_exists('table_name')

Bool

create_index('table_name' => [field1,...,fieldN], ...)

Create index for table

redump

Re-init database over dump (which make over sqlite3)

do_transaction(@query)

Like method "do" from DBI over transaction

path

Return path to your database

PACKAGE VARIABLES

$CCCP::SQLiteWrap::OnlyPrint

Default is false. If true, methods "create_table", "create_trigger", "create_index" return raw sql in stdout and nothing do in base.

SEE ALSO

  • DBI

  • http://www.sqlite.org

AUTHOR

Ivan Sivirinov

COPYRIGHT AND LICENSE

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