Autonumber

How to implement autonumbering in PostSqlForms?

Say that we want to make a table person with the following attributes:

• id: an integer uniquely identifying the person;

• first_name;

• name;

• street;

• postal_code;

• town;

• country.

How can we make a form in PostSqlForms and have the id automatically assigned when a new person is created?

The answer is different for PostgreSQL and SQLite.

PostgreSQL

In a PostgreSQL database we would create the table as follows:

CREATE TABLE person (
id serial PRIMARY KEY,
first_name text,
name text,
street text,
postal_code text,
town text,
country text);

PostgreSQL will respond with the following notice:

NOTICE: CREATE TABLE will create implicit sequence "person_id_seq" for serial column "person.id"

This means that PostreSQL has also implicitly defined a function nextval('person_id_seq') that will return the next value in the sequence each time it is called.

In pfm_form we add a record for form person:

Attribute	Value
name	person
tablename	person
sqlselect	id, first_name, name, street, postal_code, town, country
sqlfrom	person
sqlorderby	name, first_name
sqllimit
pkey	id
showform	t
view	f
help	The attribute "id" will be automatically assigned when a "person" is added to the database.

In pfm_attribute we define the form's attributes. For attribute id we use the following values:

Attribute	Value
form	person
attribute	id
typeofattrib	taNotQuoted
typeofget	tgReadOnly
sqlselect
nr	1
valuelist	none
default	=SELECT nextval('person_id_seq')

Now, each time we add a person using the form person, that person's id will automatically be set to the value returned by nextval('person_id_seq').

SQLite

In an SQLite database we would create the table as follows:

CREATE TABLE person (
id integer PRIMARY KEY,
first_name text,
name text,
street text,
postal_code text,
town text,
country text);

SQLite considers an integer PRIMARY KEY as an alias for the ROWID which is a unique number that is automatically assigned to every record when it is inserted into the table.

In pfm_form we add a record for form person:

attribute	value
name	person
tablename	person
sqlselect	id, first_name, name, street, postal_code, town, country
sqlfrom	person
sqlorderby	name, first_name
sqllimit
pkey	id
showform	1
view	0
help	The attribute "id" will be automatically assigned when a "person" is added to the database.

In pfm_attribute we define the form's attributes. For attribute id we use the following values:

Attribute	Value
form	person
attribute	id
typeofattrib	taNotQuoted
typeofget	tgReadOnly
sqlselect
nr	1
valuelist	none
default

Now, each time we add a person using the form person, the person's id will be automatically set to the record's ROWID.