SQLite使った時のメモ

memo.md

：参考ページ[ DBIプログラミング ]

ローカルファイルで簡単にRDBつくれるから楽かも。

$dbh->do($insert);
とか

$dbh->prepare($insert);

とかややこい

SQLite_Create.pl

#!/usr/bin/env perl

use strict;
use warnings;

use DBI;
use Data::Dumper;

my $file = "./ip_mac.txt";

my $database = 'test.db';
my $data_source = "dbi:SQLite:dbname=$database";
my $dbh = DBI->connect($data_source);
my $create_table;
my $insert;

my $n =1;


open my $fh , '<', $file
  or die warn $!;

$create_table = "create table list (" .
    "id INTEGER PRIMARY KEY,".
    "ipaddress VARCHAR(16)," .
    "macaddress VARCHAR(16)," .
    "hostname VARCHAR(16)" .
    ");";

$dbh->do($create_table);


while (my $line = <$fh>) {

     my @host = split(/\s+/, $line);
     $insert = "INSERT INTO list (id,ipaddress,macaddress) VALUES($n,\"$host[0]\",\"$host[1]\");";

     $dbh->do($insert);
     $n++;

}

$dbh->disconnect;

SQLite_Insert.pl

#! /usr/bin/env perl

use strict;
use warnings;

use Data::Dumper;

my $file = "./host_mac.txt";

use DBI;
my $database = 'test.db';
my $data_source = "dbi:SQLite:dbname=$database";
my $dbh = DBI->connect($data_source);
my $insert;


open my $fh , '<', $file
  or die warn $!;


while (my $line = <$fh>) {

     my @host = split(/\s+/, $line);
     $insert = "UPDATE list SET hostname = \"$host[0]\" WHERE macaddress = \"$host[1]\" ;";
     $dbh->do($insert);

}

$dbh->disconnect;