perl - 如何在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           ););

在这里，在插入数据库之前，我为DeviceName列创建了唯一索引，这样，只要新条目以相同的名称进入数据库，它就不会被插入而是被替换，

$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非常有意义。

关于perl - 如何在sqlite3和perl中创建唯一索引？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47725437/