Automatic numbering

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Automatic numbering

Cecil Westerhof-5
I only want to store a date with a record. But it is possible that more as
one record will be inserted, so I want to use another field to use as an
index. So that the first gets an one, the second a two, etc.
Is this possible, or do I just have to check if there is already a date and
fetch the highest index and increase this with one?

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic numbering

Simon Slavin-3
On 5 Jul 2018, at 3:22am, Cecil Westerhof <[hidden email]> wrote:

> I only want to store a date with a record. But it is possible that more as
> one record will be inserted, so I want to use another field to use as an
> index. So that the first gets an one, the second a two, etc.
> Is this possible, or do I just have to check if there is already a date and
> fetch the highest index and increase this with one?

There's no magical shortcut.

I would create an index on (theDate, dateEventNumber).  Then do

    BEGIN
        SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
        [ in your code see whether you got NULL back, substitute 0 ]
        INERT INTO MyTable ... ?1 + 1
    COMMIT

You can combine the two commands into one more complicated thing, but I'd do that only if I was sure nobody would ever have to figure out why my code wasn't working.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic numbering

Cecil Westerhof-5
2018-07-05 5:37 GMT+02:00 Simon Slavin <[hidden email]>:

> On 5 Jul 2018, at 3:22am, Cecil Westerhof <[hidden email]> wrote:
>
> > I only want to store a date with a record. But it is possible that more
> as
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber).  Then do
>
>     BEGIN
>         SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
>         [ in your code see whether you got NULL back, substitute 0 ]
>         INERT INTO MyTable ... ?1 + 1
>     COMMIT
>
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
>

​OK, thank you. I am going to play with it.​

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic numbering

David Raymond
I believe this trigger below should work for you. If you insert with the sequence number null, then it'll make it one more than the current max, or 1 if the table's empty. If you explicitly give it a sequence number then the trigger doesn't fire and your specific sequence number either goes in or fails like normal.

I put in the "select raise(ignore);" bit so that the original insert won't fail with the not null constraint failure. That might cause issues if there get to be more triggers on the table though.

https://www.sqlite.org/lang_createtrigger.html
"When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would have been executed are abandoned. No database changes are rolled back. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step."



create table foo
(
  date text not null,
  sequence int not null,
  something1,
  something2,
  something3,
  primary key (date, sequence)
);

create trigger foo_insert_trg
before insert on foo
for each row
when new.date is not null and new.sequence is null
begin
insert into foo values (
  new.date,
  coalesce((select max(sequence) from foo where date = new.date), 0) + 1,
  new.something1,
  new.something2,
  new.something3
);
select raise(ignore);
end;


sqlite> delete from foo;

sqlite> insert into foo values (date(), null, 'A', 'A', 'A');--empty case

sqlite> insert into foo values (date(), null, 'B', 'B', 'B');--with something in there

sqlite> select * from foo;
QUERY PLAN
`--SCAN TABLE foo
date|sequence|something1|something2|something3
2018-07-05|1|A|A|A
2018-07-05|2|B|B|B

sqlite> insert into foo values (date(), 1, 'C', 'C', 'C');--giving it an existing sequence number
Error: UNIQUE constraint failed: foo.date, foo.sequence

sqlite> insert into foo values (date(), 7, 'D', 'D', 'D');--giving it a specific new sequence number

sqlite> insert into foo values (date(), null, 'E', 'E', 'E');--should continue past the new high sequence number

sqlite> select * from foo;
QUERY PLAN
`--SCAN TABLE foo
date|sequence|something1|something2|something3
2018-07-05|1|A|A|A
2018-07-05|2|B|B|B
2018-07-05|7|D|D|D
2018-07-05|8|E|E|E



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Cecil Westerhof
Sent: Thursday, July 05, 2018 12:00 AM
To: SQLite mailing list
Subject: Re: [sqlite] Automatic numbering

2018-07-05 5:37 GMT+02:00 Simon Slavin <[hidden email]>:

> On 5 Jul 2018, at 3:22am, Cecil Westerhof <[hidden email]> wrote:
>
> > I only want to store a date with a record. But it is possible that more
> as
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber).  Then do
>
>     BEGIN
>         SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
>         [ in your code see whether you got NULL back, substitute 0 ]
>         INERT INTO MyTable ... ?1 + 1
>     COMMIT
>
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
>

​OK, thank you. I am going to play with it.​

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic numbering

Cecil Westerhof-5
In reply to this post by Simon Slavin-3
2018-07-05 5:37 GMT+02:00 Simon Slavin <[hidden email]>:

> On 5 Jul 2018, at 3:22am, Cecil Westerhof <[hidden email]> wrote:
>
> > I only want to store a date with a record. But it is possible that more
> as
> > one record will be inserted, so I want to use another field to use as an
> > index. So that the first gets an one, the second a two, etc.
> > Is this possible, or do I just have to check if there is already a date
> and
> > fetch the highest index and increase this with one?
>
> There's no magical shortcut.
>
> I would create an index on (theDate, dateEventNumber).  Then do
>
>     BEGIN
>         SELECT MAX(dateEventNumber) FROM MyTable WHERE theDate = ?1
>         [ in your code see whether you got NULL back, substitute 0 ]
>         INERT INTO MyTable ... ?1 + 1
>     COMMIT
>
> You can combine the two commands into one more complicated thing, but I'd
> do that only if I was sure nobody would ever have to figure out why my code
> wasn't working.
>

​I went for the following solution:
    UPDATE selectRandom
    SET    lastUsed    = DATE('now', 'localtime')
    ,      lastUsedIdx = (
        SELECT IFNULL(MAX(lastUsedIdx), 0)
        FROM   selectRandom
        WHERE  lastUsed = DATE('now', 'localtime')
    ) + 1
    WHERE  description = :teaToBrew

I would think that is not to convoluted.

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic numbering

Simon Slavin-3
On 7 Jul 2018, at 12:04pm, Cecil Westerhof <[hidden email]> wrote:

> ​I went for the following solution:
>    UPDATE selectRandom
>    SET    lastUsed    = DATE('now', 'localtime')
>    ,      lastUsedIdx = (
>        SELECT IFNULL(MAX(lastUsedIdx), 0)
>        FROM   selectRandom
>        WHERE  lastUsed = DATE('now', 'localtime')
>    ) + 1
>    WHERE  description = :teaToBrew
>
> I would think that is not to convoluted.

Elegant.  I understood it.  Though I was primed with what you're trying to do.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Automatic numbering

Cecil Westerhof-5
2018-07-07 14:57 GMT+02:00 Simon Slavin <[hidden email]>:

> On 7 Jul 2018, at 12:04pm, Cecil Westerhof <[hidden email]> wrote:
>
> > ​I went for the following solution:
> >    UPDATE
> ​​
> ​​
> selectRandom
> >    SET    lastUsed    = DATE('now', 'localtime')
> >    ,      lastUsedIdx = (
> >        SELECT IFNULL(MAX(lastUsedIdx), 0)
> >        FROM   selectRandom
> >        WHERE  lastUsed = DATE('now', 'localtime')
> >    ) + 1
> >    WHERE  description = :teaToBrew
> >
> > I would think that is not to convoluted.
>
> Elegant.  I understood it.  Though I was primed with what you're trying to
> do.
>

​I drink a lot of different sorts of tea. Often I have between 20 and 30
different types. To make the choice easier I wrote a little ​
​application​

​that only shows a few to select from. The longer ago that I drank a tea,
the greater the change it is shown in the list.​ I also display the latest
tea I drank. (I have several chai teas, but you should not drink it more as
once in three days.) To show them in the correct order (not important, but
nice to have) I wanted this index. For that I use:
    SELECT   *
    FROM     teaInAndOutStock
    ORDER BY LastUsed    DESC
    ,        lastUsedIdx DESC
    LIMIT    :limitNr

teaInAndOutStock is a view on selectRandom.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users