如何在sqlite3和perl中创建唯一索引？

我正在尝试将值插入数据库,我的数据库表如图所示

$stmt = qq(CREATE TABLE IF NOT EXISTS INFO 
   (

  DeviceName               TEXT    NOT NULL,
  CurrentStatus            INT     NOT NULL,
  ReportTime        TEXT,
  OldStatus         INT            NOT NULL,
  OldReportTime     TEXT           ););

在插入db之前,我正在为DeviceName列创建一个唯一索引,这样每当一个新条目进入具有相同名称的db时,它就不会被插入而是被替换,

$stmt = qq(CREATE UNIQUE INDEX ree ON INFO(DeviceName));
$stmt = qq(INSERT OR REPLACE INTO INFO(DeviceName, CurrentStatus,ReportTime,OldStatus,OldReportTime)
           VALUES ('$FQDN', $currentstatus, '$currenttime', $oldstatus, '$oldtime' ););
my $rv = $dbh->do($stmt) or die $DBI::errstr;

这些是我添加的几行,用于创建唯一索引并在下一个实体使用相同的DeviceName输入时替换.但是,虽然我遵循了这一点,但数据库仍然会多次存储相同的DeviceName.

 #!/usr/bin/perl
 use DBI;
 use strict;
 my $FQDN= "bubbly.bth.se";
 my $currenttime = "Sat Dec  9 02:07:31 2017";
 my $oldtime = "Sat Dec  9 02:06:31 2017";
 my $currentstatus = "1";
 my $oldstatus = "2";
 my $driver   = "SQLite";
 my $database = "test.db";
 my $dsn = "DBI:$driver:dbname=$database";
 my $userid = "";
 my $password = "";

 my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
     or die $DBI::errstr;
 print "Opened database successfully\n";

 $stmt = qq(CREATE TABLE IF NOT EXISTS INFO 
 (

  DeviceName               TEXT    NOT NULL,
  CurrentStatus            INT     NOT NULL,
  ReportTime        TEXT,
  OldStatus         INT            NOT NULL,
  OldReportTime     TEXT           ););

my $rv = $dbh->do($stmt);
if(my $rv <0) {
print $DBI::errstr;
} else {
print "Table created successfully\n";
    }
$stmt = qq(CREATE UNIQUE INDEX ree ON INFO(DeviceName));
$stmt = qq(INSERT OR REPLACE INTO INFO(DeviceName, 
CurrentStatus,ReportTime,OldStatus,OldReportTime)
           VALUES ('$FQDN', $currentstatus, '$currenttime', $oldstatus, 
'$oldtime' ););
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$dbh->disconnect();

NetSNMP::TrapReceiver::register("all", \&my_receiver) || 
warn "failed to register our perl trap handler\n";
print STDERR "Loaded the example perl snmptrapd handler\n";

这是我正在使用的代码,我的输出

bubbly.bth.se|1|Sat Dec  9 02:07:31 2017|2|Sat Dec  9 02:06:31 2017
bubbly.bth.se|1|Sat Dec  9 02:07:31 2017|2|Sat Dec  9 02:06:31 2017
bubbly.bth.se|1|Sat Dec  9 02:07:31 2017|2|Sat Dec  9 02:06:31 2017
bubbly.bth.se|1|Sat Dec  9 02:07:31 2017|2|Sat Dec  9 02:06:31 2017
bubbly.bth.se|1|Sat Dec  9 02:07:31 2017|2|Sat Dec  9 02:06:31 2017

但我想在数据库中只有一行,如果找到相同的DeviceName,每次都会更新或替换.

注意:变量值只是为了便于理解.

`

你永远不会真正创建索引.

更换

$stmt = qq(CREATE UNIQUE INDEX ree ON INFO(DeviceName));

同

$dbh->do(qq(CREATE UNIQUE INDEX ree ON INFO(DeviceName)));

清理版本:

#!/usr/bin/perl

use strict;
use warnings qw( all );

use DBI qw( );

my $database = "test.db";
my $dsn = "dbi:sqlite:dbname=$database";

my $dbh = DBI->connect($dsn, undef, undef,
   {
      RaiseError => 1,
      PrintError => 0,
      PrintWarn  => 1,
      AutoCommit => 1,
   },
);

$dbh->do(q{
   CREATE TABLE IF NOT EXISTS INFO (
             DeviceName     TEXT  NOT NULL,
             CurrentStatus  INT   NOT NULL,
             ReportTime     TEXT,
             OldStatus      INT   NOT NULL,
             OldReportTime  TEXT
          )
});

$dbh->do(q{ CREATE UNIQUE INDEX ree ON INFO ( DeviceName ) });

my $FQDN          = "bubbly.bth.se";
my $currenttime   = "Sat Dec  9 02:07:31 2017";
my $oldtime       = "Sat Dec  9 02:06:31 2017";
my $currentstatus = "1";
my $oldstatus     = "2";

$dbh->do(
   q{
      INSERT OR REPLACE INTO INFO (
                DeviceName,
                CurrentStatus,
                ReportTime,
                OldStatus,
                OldReportTime
             ) VALUES (
                ?, ?, ?, ?, ?
             )
   },
   undef,
   $FQDN, $currentstatus, $currenttime, $oldstatus, $oldtime
);

$dbh->disconnect();

一直用use warnings qw( all );.

您错误地构建了INSERT语句(注入错误).

你的缩进和间距很差.

你有RaiseError => 1,所以检查DBI方法是否返回错误是没有意义的.

你有RaiseError => 1,所以使用它也很有意义PrintError => 1.