How can i define the Autoincrement to the Port field

Question

In my script i want to declare the port field column as Auto increment with default value of 8080.

#!/bin/bash

echo " --- Enter the Database name ---"
read databasename


echo " --- enter the table name --- "
read table_name

sqlite3 $databasename.db "DROP TABLE IF EXISTS $table_name;"

sqlite3 $databasename.db  "CREATE TABLE IF NOT EXISTS $table_name(cus_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,cus_name TEXT NOT NULL UNIQUE ,cus_domain TEXT UNIQUE, cus_status TEXT NOT NULL,Port INTEGER NOT NULL);"


echo " --- Enter the total number of rows do you want ---"
read cus_count


echo "--- Enter the following details one by one---"

port_num=8080

for((i=1;i<=cus_count;i++))
do
port_number=$port_num

port_number=$((port_number + 1))

echo "port number is $port_number"

echo "enter the $i customer details"

echo "---Enter the customer name---"
read c_name

echo "---Enter the Status(Active/Inactive)---"
read c_status


c_domain="$c_name"

sqlite3 $databasename.db "INSERT OR IGNORE INTO $table_name (cus_name,cus_domain,cus_status, Port) VALUES(\"$c_name\",\"${c_domain,,}\",\"$c_status\",\"$port_number\") ;" 

done

echo " --- Records from the $table_name ---"

sqlite3 $databasename.db "select * from $table_name;"

How can i define the Autoincrement to the Port field ?

Please explain the difference to https://stackoverflow.com/questions/44644895/how-can-i-define-the-insert-query-with-unique-constraint Why did you not edit that question? — Yunnosch, Jun 22 '17 at 04:48
i define the port_num=8080 before the for loop .then inside the for loop i define "port_number=$port_num" "port_number=$((port_number + 1))" but inside the loop the port number does not increment .what can i do for increase the port number value while execute the loop..? — Mahendranatarajan, Jun 22 '17 at 05:17

score 0 · Answer 1 · answered Jun 22 '17 at 07:06

In SQLite, autoincrementing works only for the primary key, and the port number is not the PK, nor should it be.

You have to use your own variable to increment the port number(s). (Which you are already doing.)

The problem is that you are using two variables:

for((i=1;i<=cus_count;i++))
do
port_number=$port_num
port_number=$((port_number + 1))

You are incrementing port_number, but you reset it to the old value (port_num) in every loop iteration.

You should read port_num only once, before the loop; or use a single variable; or write the new value back into the other variable.

score 0 · Answer 2 · answered Jun 22 '17 at 20:39

As elaborated by the other answer, automincrement is not available as a tool to solve your problem.

What you can do
(on almost exactly your table schema, just an additional UNIQUEfor the port column)
is to add a trigger and a "harcoded" dummy line.
What that achieves (with the trigger I propose below, but the details can be adapted to whatever rules apply for the useage of ports in your database):

enter a valid (>8080) and unused port number
it gets entered into the database
enter an invalid (<8080) port number
it gets replaced by the lowest, valid, unused one,
even if there are gaps caused by specific port number picks before
delete a database entry,
the port number gets to be reused later
enter an already used port number
causes a "unique failure" (as does entering an already used customer domain in your setup)
for use in your script, you can skip all the calculations of port numbers
and instead
- allow input of portnumbers (the database will prevent doubles)
- simply always use "1" as portnumber (the database will find suitable ones)

One thing to be kept in mind:
Whenever you read out the list, you need to somehow ignore the dummy line.

Code:

CREATE TRIGGER autoport AFTER INSERT ON cusports
BEGIN 
    UPDATE cusports
    SET port=
        (SELECT MIN(a.port+1)
         FROM  
                (SELECT * FROM cusports WHERE port >=8080)
                AS a
            LEFT JOIN
                cusports AS b
            ON a.port+1 = b.port
         WHERE b.port IS NULL)
    WHERE port<8080;
END;

INSERT INTO cusports(cus_id, cus_name, cus_domain, cus_status, Port)
VALUES(NULL,'dummycusname','dummycusdomain','dummycusstatus',8080);

Details:

I am building on the answer by Strawberry to
Returning the lowest integer not in a list in SQL
(SELECT * FROM cusports WHERE port >=8080) instead of simple cusports,
in order NOT to use a potentially invalid freshly inserted port id
SELECT MIN(a.port+1) looks for the minimal portnumber which is "1 higher than an existing one, without existing itself" (the part in italic is by Strawberry)
WHERE port<8080restricts the updating to only invalid port numbers
(not wanting to mess up existing entries)
the dummy line is used to have a root for Strawberries method of not actually looking for all non-existing numbers, but just list "one higher than existing"

Test with output (SQLite commandline tool, after setting up trigger and dummy line on an empty table):

sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080 

sqlite> insert into cusports values (null, 'cusa', 'doma', 'stata', 8081);
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081

sqlite> insert into cusports values (null, 'cusb', 'domb', 'statb', 8083);
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081
3           cusb          domb            statb           8083
sqlite> -- note the 8082 gap and the following invalid '1'
sqlite> insert into cusports values (null, 'cusc', 'domc', 'statc', 1);
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081
3           cusb          domb            statb           8083
4           cusc          domc            statc           8082
sqlite> insert into cusports values (null, 'cusd', 'domd', 'statd', 1);
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081
3           cusb          domb            statb           8083
4           cusc          domc            statc           8082
5           cusd          domd            statd           8084
sqlite> delete from cusports where port=8083;
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081
4           cusc          domc            statc           8082
5           cusd          domd            statd           8084
sqlite> insert into cusports values (null, 'cuse', 'dome', 'state', 1);
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081
4           cusc          domc            statc           8082
5           cusd          domd            statd           8084
6           cuse          dome            state           8083

sqlite> insert into cusports values (null, 'cuse', 'dome', 'state', 1);
Error: UNIQUE constraint failed: cusports.cus_domain
sqlite> insert into cusports values(null, 'cusf', 'domf', 'statf', 8083);
Error: UNIQUE constraint failed: cusports.Port
sqlite> select * from cusports;
cus_id      cus_name      cus_domain      cus_status      Port
----------  ------------  --------------  --------------  ----------
1           dummycusname  dummycusdomain  dummycusstatus  8080
2           cusa          doma            stata           8081
4           cusc          domc            statc           8082
5           cusd          domd            statd           8084
6           cuse          dome            state           8083

Complete .dumpfor reference (some hopefully correct newlines for readability):

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE cusports(
    cus_id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
    cus_name TEXT NOT NULL UNIQUE,
    cus_domain TEXT UNIQUE,
    cus_status TEXT NOT NULL,
    Port INTEGER NOT NULL UNIQUE);
INSERT INTO cusports(cus_id,cus_name,cus_domain,cus_status,Port)
VALUES(1,'dummycusname','dummycusdomain','dummycusstatus',8080);
INSERT INTO cusports(cus_id,cus_name,cus_domain,cus_status,Port)
VALUES(2,'cusa','doma','stata',8081);
INSERT INTO cusports(cus_id,cus_name,cus_domain,cus_status,Port)
VALUES(4,'cusc','domc','statc',8082);
INSERT INTO cusports(cus_id,cus_name,cus_domain,cus_status,Port)
VALUES(5,'cusd','domd','statd',8084);
INSERT INTO cusports(cus_id,cus_name,cus_domain,cus_status,Port)
VALUES(6,'cuse','dome','state',8083);
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence(name,seq) VALUES('cusports',7);
CREATE TRIGGER autoport AFTER INSERT ON cusports
    BEGIN
        UPDATE cusports
        SET port=
            (SELECT MIN(a.port+1)
             FROM
                    (SELECT * FROM cusports WHERE port >=8080)
                    AS a
                LEFT JOIN
                    cusports AS b
                ON a.port+1 = b.port
             WHERE b.port IS NULL)
        WHERE port<8080;
    END;
COMMIT;

it displays an error meassage "Error: no such table: main.cusports" — Mahendranatarajan, Jun 23 '17 at 04:31
And what is the table called in your database? You probably have to use the corresponding variable from your script, or change the script to use the same fixed table name. I am working in pure SQLite. — Yunnosch, Jun 23 '17 at 04:37
If you use a fixed table name, then what is this part `read table_name` and this part `CREATE TABLE IF NOT EXISTS $table_name(...` doing? — Yunnosch, Jun 23 '17 at 05:07
If you can decide on a fixed table name, e.g. "cusports" then replace the occurrences of "$table_name" by "cusports" and delete the "read table_name". The user will then not be asked for the table name and the fixed one will be used. — Yunnosch, Jun 23 '17 at 05:14

How can i define the Autoincrement to the Port field

2 Answers2