How to create unique index in sqlite3 and perl?

Question

I am trying to insert values into the database, My database table is as shown

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

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

Here before inserting to the db I am creating a unique index for DeviceName column, so that whenever a new entry comes into the db with same name it is not inserted rather gets replaced,

$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;

these are the few lines which i added to create a unique index and replace if the next entity enters with the same DeviceName. But though i followed this, the databases is still getting stored with the same DeviceName multiple times.

 #!/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";

this is the code i am using, my output

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

But i wanted to have only one row in the database which updates or replaces every time if it finds the same DeviceName.

NOTE: Variable values are taken just for easy understanding.

`

ikegami · Accepted Answer · 2017-12-09 06:54:40Z

6

You never actually create the index.

Replace

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

with

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

Cleaned up version:

#!/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();

Always use use warnings qw( all );.
You incorrectly build your INSERT statement (injection bug).
You have poor indenting and spacing.
You have RaiseError => 1, so it makes no sense to check if the DBI methods return an error.
You have RaiseError => 1, so it makes much sense to also use PrintError => 1.

edited Dec 9, 2017 at 6:54

answered Dec 9, 2017 at 6:49

ikegami

391k17 gold badges291 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harshavardhan Reddy Over a year ago

Hi, thanks for the reply, here in your solution the code is just checking if the DeviceName is same and if it is same it just doesnot create a row, but in my case i wanted to Update the table with newly coming information, everytime only the DeviceName is same but the other columns get changed as the other column is time, the time gets changed every time, so when ever a need information comes into database need to check the DeviceName if same update else INSERT,

Harshavardhan Reddy Over a year ago

In the description i have mentioned a note: the variable values are taken just for easy understanding. which means except the name the other colums changes, sometimes DeviceName also changes then we need to insert else update. Thanks In Advance

Collectives™ on Stack Overflow

How to create unique index in sqlite3 and perl?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related