Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

DB API

David E. Wheeler edited this page · 11 revisions
Clone this wiki locally

PGXN Manager Database API

This is the documentation of the PGXN database functions available to the "pgxn" user, which is the database user as which the app will connect to the database. It's generated using gendoc, a simple SQL file parser that looks for function declarations and embedded MultiMarkdown. It is then run through MultiMarkdown.pl to generate HTML for this page. (Alas, the GitHub markup library does not (yet) support MultiMarkdown).

PGXN Manager Database API

Contents

public

insert_user

Parameters
  • IN nickname LABEL
  • IN password TEXT
  • IN full_name TEXT DEFAULT ''
  • IN email EMAIL DEFAULT NULL
  • IN uri URI DEFAULT NULL
  • IN twitter CITEXT DEFAULT NULL
  • IN why TEXT DEFAULT NULL
Returns
BOOLEAN
% SELECT insert_user(
    nickname  := 'theory',
    password  := '***',
    full_name := 'David Wheeler',
    email     := 'theory@pgxn.org',
    uri       := 'http://justatheory.com/',
    twitter   := 'theory',
    why       := 'Because I’m a bitchin’ Pg developer, yo.'
);
 insert_user 
─────────────
 t

Inserts a new user into the database. The nickname must not already exist or an exception will be thrown. The password must be at least four characters long or an exception will be thrown. The status will be set to "new" and the set_by set to the new user's nickname. The other parameters are:

full_name
The full name of the user.
email
The email address of the user. Must be a valid email address as verified by Email::Valid.
uri
Optional URI for the user. Should be a valid URI as verified by Data::Validate::URI.
twitter
Optional Twitter username. Case-insensitive. A leading "@" will be removed.
why
Optional text from the user explaining why she should be allowed access.

Returns true if the user was inserted, and false if not.

change_password

Parameters
  • IN nickname LABEL
  • IN oldpass TEXT
  • IN newpass TEXT
Returns
BOOLEAN
% SELECT change_password('strongrrl', '****', 'whatever');
 change_password 
─────────────────
 t

Changes a user's password. The user must be active, and the old password must match the existing password for the nickname or the password will not be set. The password must be at least four charcters long or an exception will be thrown. Returns true if the password was changed and false if it was not.

update_user

Parameters
  • IN nickname LABEL
  • IN full_name TEXT DEFAULT NULL
  • IN email EMAIL DEFAULT NULL
  • IN uri URI DEFAULT NULL
  • IN twitter CITEXT DEFAULT NULL
Returns
BOOLEAN
% SELECT update_user(
    nickname  := 'theory',
    full_name := 'David E. Wheeler',
    email     := 'justatheory@pgxn.org',
    uri       := 'http://www.justatheory.com/',
    twitter   :- 'theory'
);
 update_user 
─────────────
 t

Update the specified user. The user must be active. The nickname cannot be changed. The password can only be changed via change_password() or reset_password(). Pass other attributes as:

full_name
The full name of the user.
email
The email address of the user. Must be a valid email address as verified by Email::Valid.
uri
Optional URI for the user. Should be a valid URI as verified by Data::Validate::URI.
twitter
Optional Twitter username. A leading "@" wil be removed.

Returns true if the user was updated, and false if not.

log_visit

Parameters
  • IN nickname LABEL
Returns
BOOLEAN
% SELECT log_visit('theory');
 log_visit 
───────────
 t

Log the visit for the specified user. At this point, that just means that users.visited_at gets set to the current time.

set_user_status

Parameters
  • IN setter LABEL
  • IN nickname LABEL
  • IN status STATUS
Returns
BOOLEAN
% SELECT set_user_status('admin', 'strongrrl', 'active');
 set_user_status 
─────────────────
 t

Sets a user's status. The status may be one of "active", "inactive", or "deleted". The nickname of the user who sets the status must be passed as the first argument, and that user must be an administrator or an exception will be thrown. That nickname will be stored in the set_by column. Users cannot change their own status; if setter and nick are the same, an exception will be thrown. Returns true if the status was set, and false if not. If the status is already set to the specified value, the record will not be updated and false will be returned.

set_user_admin

Parameters
  • IN setter LABEL
  • IN nickname LABEL
  • IN set_to BOOLEAN
Returns
BOOLEAN
% select set_user_admin('admin', 'strongrrl', true);
 set_user_admin 
────────────────
 t

Sets a user's administrator flag. The nickname of the user who does so must be passed as the first argument, and that user must be an administrator. Administrators may set their own administrator flags to false. If the administrator flag is already set to the specified value, the record will not be updated and false will be returned.

authenticate_user

Parameters
  • IN nickname CITEXT
  • IN password TEXT
Returns
BOOLEAN
% select authenticate_user('admin', '*****');
 authenticate_user
────────────────
 t

Returns true if the user with the specified nickname exists, is active, and the password matches. Also updates the value of the visited_at column for the user to the current timestamp. Returns false if the nickname and password don't match or the user is not active.

rand_str_of_len

Parameters
  • IN string_length INTEGER
Returns
TEXT
% SELECT rand_str_of_len(12);
 rand_str_of_len 
─────────────────
 i5cvbMF849hp

Returns a random string of ASCII alphanumeric characters of the specified length. Borrowed from Depesz. Used internally by forgot_password() to generate tokens.

forgot_password

Parameters
  • IN nick LABEL
Returns
TEXT
% SELECT forgot_password('theory');
       forgot_password        
──────────────────────────────
 {G8Gxz,justatheory@pgxn.org}

Creates a password reset token for the specified nickname. The user must be active. The return value is a two-element array. The first value is the token, and the second the email address of the user. The token will be set to expire 1 day from creation. Returns NULL if the token cannot be created (because no user exists for the specified nickname or the user is not ative).

reset_password

Parameters
  • IN tok TEXT
  • IN pass TEXT
Returns
BOOLEAN
% SELECT reset_password('G8Gxz', 'whatever');
 reset_password 
────────────────
 t

Pass in a token and a new password to reset a user password. The token must exist and must not have expired and the associated user must be active. The password must be at least four characters long or an exception will be thrown. Returns true on success and false on failure.

json_key

Parameters
  • IN string TEXT
Returns
TEXT
% SELECT json_key('foo');
 json_key 
──────────
 "foo"

Like json_string(), this function encodes a text value as a JSON string and returns the string. The difference is that NULL is treated as illegal (because it cannot be used as a JSON key) and will thus throw an exception.

json_value

Parameters
  • IN val TEXT
  • IN def TEXT DEFAULT 'null'
Returns
TEXT
% SELECT json_value('foo'), json_value(NULL), json_value(NULL, 'default'),
         json_value(NULL, NULL);
 json_value │ json_value │ json_value │ json_value 
────────────┼────────────┼────────────┼────────────
 "foo"      │ null       │ default    │ 

Encodes a text value as a JSON string. If the string is NULL, the second argument will be used as a fallback. If there is no second argument, it will fall back to "null".

grant_coownership

Parameters
  • IN nick LABEL
  • IN coowner LABEL
  • IN exts TEXT[]
Returns
BOOLEAN
% SELECT grant_coownership('theory', 'strongrrl', ARRAY['pair', 'pgtap']);
 grant_coownership 
───────────────────
 t

Grants co-ownership to one or more extensions. The first argument is the nickname of the uesr inserting the co-owner. Said user must either be and admin or own all of the specified extensions. The second argument is the nickname of the user being granted co-ownership. This name must not be the same name as the owner. The third argument is an array of the names of the extensions to which co-ownership is to be granted.

revoke_coownership

Parameters
  • IN nick LABEL
  • IN coowner LABEL
  • IN exts TEXT[]
Returns
BOOLEAN
% SELECT revoke_coownership('theory', 'strongrrl', ARRAY['pair', 'pgtap']);
 revoke_coownership 
────────────────────
 t

Remove co-ownership permission to the specified extensions. The first argument is the nickname of the user removing co-ownership. Said user must either be and admin, own all of the specified extensions, or be removing co-ownership from itself. The second argument is the nickname of the user being for whom co-ownership is being removed. The third argument is an array of the names of the extensions from which co-ownership is to be removed.

transfer_ownership

Parameters
  • IN nick LABEL
  • IN newowner LABEL
  • IN exts TEXT[]
Returns
BOOLEAN
% SELECT transfer_ownership('theory', 'strongrrl', ARRAY['pair', 'pgtap']);
 transfer_ownership 
────────────────────
 t

Transfer ownership of the specified extensions to a new owner. The first argument is the nickname of the uesr performing the transfer. Said user must either be and admin or own all of the specified extensions. The second argument is the nickname of the user being given ownership. This name must not be the same name as the owner. The third argument is an array of the names of the extensions to which ownership is to be transfered.

is_admin

Parameters
  • IN nick LABEL
Returns
BOOLEAN
SELECT is_admin('username');

Returns true if the named user is an admin, and false if not.

insert_mirror

Parameters
  • IN admin LABEL
  • IN uri URI
  • IN frequency TEXT
  • IN location TEXT
  • IN organization TEXT
  • IN timezone TIMEZONE
  • IN email EMAIL
  • IN bandwidth TEXT
  • IN src URI
  • IN rsync URI DEFAULT NULL
  • IN notes TEXT DEFAULT NULL
Returns
BOOLEAN
% SELECT insert_mirror(
    admin        := 'theory',
    uri          := 'http://kineticode.com/pgxn/',
    frequency    := 'hourly',
    location     := 'Portland, OR, USA',
    bandwidth    := '10MBps',
    organization := 'Kineticode, Inc.',
    timezone     := 'America/Los_Angeles',
    email        := 'pgxn@kineticode.com',
    src          := 'rsync://master.pgxn.org/pgxn/',
    rsync        := 'rsync://pgxn.kineticode.com/pgxn/',
    notes        := 'This is a note'
);
 insert_mirror 
───────────────
 t
(1 row)

Inserts a mirror. The user specified as the first parameter must be an administrator or else an exception will be thrown. All arguments are required except rsync and notes. Returns true on succesful insert and false on failure (probably impossible, normally an exception will be thrown on failure).

update_mirror

Parameters
  • IN admin LABEL
  • IN old_uri URI
  • IN uri URI DEFAULT NULL
  • IN frequency TEXT DEFAULT NULL
  • IN location TEXT DEFAULT NULL
  • IN organization TEXT DEFAULT NULL
  • IN timezone TIMEZONE DEFAULT NULL
  • IN email EMAIL DEFAULT NULL
  • IN bandwidth TEXT DEFAULT NULL
  • IN src URI DEFAULT NULL
  • IN rsync URI DEFAULT NULL
  • IN notes TEXT DEFAULT NULL
Returns
BOOLEAN
% SELECT udpate_mirror(
    admin        := 'theory',
    old_uri      := 'http://kineticode.com/pgxn/',
    uri          := 'http://pgxn.kineticode.com/',
    frequency    := 'hourly',
    location     := 'Portland, OR, USA',
    bandwidth    := '10MBps',
    organization := 'Kineticode, Inc.',
    timezone     := 'America/Los_Angeles',
    email        := 'pgxn@kineticode.com',
    src          := 'rsync://master.pgxn.org/pgxn/',
    rsync        := 'rsync://pgxn.kineticode.com/pgxn/',
    notes        := 'This is a note'
);
 update_mirror 
───────────────
 t
(1 row)

Updates a mirror. The user specified as the first parameter must be an administrator or else an exception will be thrown. The old_uri parameter must contain the existing URI of the mirror and is required. All other paramters are optional. Returns true on succesful update and false on failure, which will happen if the existing URI cannot be found in the database.

delete_mirror

Parameters
  • IN deleter LABEL
  • IN uri URI
Returns
BOOLEAN
% SELECT delete_mirror('theory', 'http://kineticode.com/pgxn/');
 delete_mirror 
───────────────
 t

Deletes a mirror. The user specified as the first parameter must be an administrator or else an exception will be thrown. Returns true if the specified mirror was deleted and false if not.

munge_email

Parameters
  • IN email EMAIL
Returns
TEXT
% SELECT munge_email('foo@bar.com');
 munge_email 
─────────────
 bar.com|foo

Munges an email address. This is for use in mirrors.json, just to have a bit of obfuscation. All it does is move the username to the end, separated from the domain name by a pipe. So "foo@bar.com" becomes "bar.com|foo".

get_mirrors_json

Returns
TEXT
% SELECT get_mirrors_json();
                get_mirrors_json                 
─────────────────────────────────────────────────
 [                                              ↵
    {                                           ↵
       "uri": "http://example.com/pgxn/",       ↵
       "frequency": "hourly",                   ↵
       "location": "Portland, OR, USA",         ↵
       "organization": "Kineticode, Inc.",      ↵
       "timezone": "America/Los_Angeles",       ↵
       "email": "example.com|pgxn",             ↵
       "bandwidth": "10MBps",                   ↵
       "src": "rsync://master.pgxn.org/pgxn/"   ↵
    },                                          ↵
    {                                           ↵
       "uri": "http://pgxn.example.net/",       ↵
       "frequency": "daily",                    ↵
       "location": "Portland, OR, USA",         ↵
       "organization": "David E. Wheeler",      ↵
       "timezone": "America/Los_Angeles",       ↵
       "email": "example.net|pgxn",             ↵
       "bandwidth": "Cable",                    ↵
       "src": "rsync://master.pgxn.org/pgxn/",  ↵
       "rsync": "rsync://master.pgxn.org/pgxn/",↵
       "notes": "These be some notes, yo"       ↵
    }                                           ↵
 ]                                              ↵

Returns the JSON for the mirrors.json file. The format is an array of JSON objects. All the required fields will be present, and the optional fields "rsync" and "notes" will be present only if they have values.

extension_json

Parameters
  • IN dist TEXT
  • IN version SEMVER
Returns
TABLE ( extension CITEXT, json TEXT )
% SELECT * FROM extension_json('pair', '1.2.0');
 extension │                                 json                                 
───────────┼──────────────────────────────────────────────────────────────────────
 pair      │ {                                                                   ↵
           │    "extension": "pair",                                             ↵
           │    "latest": "testing",                                             ↵
           │    "stable": { "dist": "pair", "version": "1.0.0" },                ↵
           │    "testing": { "dist": "pair", "version": "1.2.0" },               ↵
           │    "distributions": {                                               ↵
           │       "1.2.0": [                                                    ↵
           │          { "dist": "pair", "version": "1.2.0", "status": "testing" }↵
           │       ],                                                            ↵
           │       "1.0.0": [                                                    ↵
           │          { "dist": "pair", "version": "1.0.0" }                     ↵
           │       ],                                                            ↵
           │       "0.2.2": [                                                    ↵
           │          { "dist": "pair", "version": "0.0.1", "status": "testing" }↵
           │       ]                                                             ↵
           │    }                                                                ↵
           │ }                                                                   ↵
           │ 
 trip      │ {                                                                   ↵
           │    "extension": "trip",                                             ↵
           │    "latest": "testing",                                             ↵
           │    "stable": { "dist": "pair", "version": "1.0.0" },                ↵
           │    "testing": { "dist": "pair", "version": "1.2.0" },               ↵
           │    "distributions": {                                               ↵
           │       "0.9.10": [                                                   ↵
           │          { "dist": "pair", "version": "1.2.0", "status": "testing" }↵
           │       ],                                                            ↵
           │       "0.9.9": [                                                    ↵
           │          { "dist": "pair", "version": "1.0.0" }                     ↵
           │       ],                                                            ↵
           │       "0.2.1": [                                                    ↵
           │          { "dist": "pair", "version": "0.0.1", "status": "testing" }↵
           │       ]                                                             ↵
           │    }                                                                ↵
           │ }                                                                   ↵
           │ 

Returns a set of extensions and their JSON metadata for a given distribution version. In the above example, the "pair" and "trip" extensions are both in the "pair 1.0.0" distribution. Each has data indicating its latest stable, testing, and unstable versions (as appropriate) and the distribution details for every released version in descending by extension version number.

utc_date

Parameters
  • IN $1 TIMESTAMPTZ
Returns
TEXT
% select utc_date(now());
       utc_date
──────────────────────
 2011-04-07T22:42:08Z

Returns a string representation of a timestamp with timezone value in UTC and formatted in strict ISO-8601 format, always with the ending "Z" for "Zulu time" (UTC).

dist_json

Parameters
  • IN dist TEXT
Returns
TEXT
% SELECT * FROM dist_json('pair');
                           dist_json
────────────────────────────────────────────────────────────────
 {                                                             ↵
    "name": "pair",                                            ↵
    "releases": {                                              ↵
       "stable": [                                             ↵
          {"version": "0.1.1", "date": "2010-10-29T22:44:42Z"},↵
          {"version": "0.1.0", "date": "2010-10-19T03:59:54Z"} ↵
       ]                                                       ↵
    }                                                          ↵
 }                                                             ↵

Returns a JSON string describing a distribution, including all of its released versions and their dates.

tag_json

Parameters
  • IN dist TEXT
  • IN version SEMVER
Returns
TABLE ( tag CITEXT, json TEXT )
% SELECT * FROM tag_json('pair', '0.1.0');
        tag        │                               json                                
───────────────────┼───────────────────────────────────────────────────────────────────
 key value         │ {                                                                ↵
                   │    "tag": "key value",                                           ↵
                   │    "releases": {                                                 ↵
                   │       "pair": {                                                  ↵
                   │          "stable": [                                             ↵
                   │             {"version": "0.1.1", "date": "2010-10-29T22:44:42Z"} ↵
                   │          ]                                                       ↵
                   │       },                                                         ↵
                   │       "trip": {                                                  ↵
                   │          "stable": [                                             ↵
                   │             {"version": "0.0.1", "date": "2010-09-25T15:48:39Z"} ↵
                   │          ]                                                       ↵
                   │       }                                                          ↵
                   │    }                                                             ↵
                   │ }                                                                ↵
                   │ 
 ordered pair      │ {                                                                ↵
                   │    "tag": "ordered pair",                                        ↵
                   │    "releases": {                                                 ↵
                   │       "pair": {                                                  ↵
                   │          "stable": [                                             ↵
                   │             {"version": "0.1.1", "date": "2010-10-29T22:44:42Z"},↵
                   │             {"version": "0.1.0", "date": "2010-10-19T03:59:54Z"} ↵
                   │          ],                                                      ↵
                   │          "testing": [                                            ↵
                   │             {"version": "0.0.5", "date": "2010-10-10T14:35:18Z"} ↵
                   │          ]                                                       ↵
                   │       }                                                          ↵
                   │    }                                                             ↵
                   │ }                                                                ↵
                   │ 

For a given distribution and version, returns a set of tags and the JSON to describe them. In this example, pair 0.1.0 has two tags. The tag "key value " is associated with pgtap 0.1.1 and trip 0.0.1. The tag "ordered pair", on the other hand, is associcated with three versions of the "pair" distribution, as well.

user_json

Parameters
  • IN nickname LABEL
Returns
TEXT
% SELECT user_json('theory');
                            user_json
───────────────────────────────────────────────────────────────────
 {                                                                ↵
    "nickname": "theory",                                         ↵
    "name": "David E. Wheeler",                                   ↵
    "email": "david@justatheory.com",                             ↵
    "uri": "http://justatheory.com/",                             ↵
    "twitter": "theory",                                          ↵
    "releases": {                                                 ↵
       "pair": {                                                  ↵
          "stable": [                                             ↵
             {"version": "0.1.1", "date": "2010-10-29T22:44:42Z"},↵
             {"version": "0.1.0", "date": "2010-10-19T03:59:54Z"} ↵
          ]                                                       ↵
       },                                                         ↵
       "pgTAP": {                                                 ↵
          "stable": [                                             ↵
             {"version": "0.25.0", "date": "2011-02-02T03:25:17Z"}↵
          ]                                                       ↵
       }                                                          ↵
    }                                                             ↵
 }                                                                ↵

Returns a JSON string describing the given user, including all versions and release dates of the distributions the user owns. The included distribution versions are only the versions owned by the user; if someone else uploaded a different version of the distribution, that version will not be owned by this user and thus not included in the JSON.

tag_stats_json

Parameters
  • IN num_popular INT DEFAULT 56
Returns
TEXT
% select tag_stats_json(4);
                tag_stats_json
──────────────────────────────────────────────
 {                                           ↵
    "count": 212,                            ↵
    "popular": [                             ↵
       {"tag": "data types", "dists": 4},    ↵
       {"tag": "key value", "dists": 2},     ↵
       {"tag": "france", "dists": 1},        ↵
       {"tag": "key value pair", "dists": 1} ↵
    ]                                        ↵
 }                                           ↵

Returns a JSON representation of tag statistics. These include:

  • count: A count of all tags in the database.
  • popular: A list of the most used tags in the system, listed in descending order by the number of uses.

Pass in the optional num_popular parameter to limit the number of tags that appear in the popular list. The default limit is 56.

user_stats_json

Parameters
  • IN num_prolific INT DEFAULT 56
Returns
TEXT
% select user_stats_json();
                                   user_stats_json
──────────────────────────────────────────────────────────────────────────────────────
 {                                                                                   ↵
    "count": 256,                                                                    ↵
    "prolific": [                                                                    ↵
       {"nickname": "theory", "name": "David E. Wheeler", "dists": 3, "releases": 4},↵
       {"nickname": "daamien", "name": "damien clochard", "dists": 1, "releases": 2},↵
       {"nickname": "umitanuki", "name": "Hitoshi Harada", "dists": 1, "releases": 1}↵
    ]                                                                                ↵
 }                                                                                   ↵

Returns a JSON string with user statistics. These include:

  • count: A count of the number of users in the database.
  • prolific: A list of the most prolific users, measured by the number of distributions they've released. A count of the number of releases is also included, though it does not determine prolificness.

Pass in the optional num_prolific parameter to limit the number of user that appear in the prolific list. The default limit is 56.

extension_stats_json

Parameters
  • IN num_recent INT DEFAULT 56
Returns
TEXT
% select extension_stats_json();
                             extension_stats_json
───────────────────────────────────────────────────────────────────────────────
 {                                                                            ↵
    "count": 125,                                                             ↵
    "recent": [                                                               ↵
       {                                                                      ↵
          "extension": "countnulls",                                          ↵
          "abstract": "Simple function to count the number of NULL arguments",↵
          "ext_version": "1.0.0",                                             ↵
          "dist": "countnulls",                                               ↵
          "version": "1.0.0",                                                 ↵
          "date": "2011-03-15T16:44:26Z",                                     ↵
          "user": "alexk",                                                    ↵
          "user_name": "Alexey Klyukin"                                       ↵
       },                                                                     ↵
       {                                                                      ↵
          "extension": "pgtap",                                               ↵
          "abstract": "Unit testing for PostgreSQL",                          ↵
          "ext_version": "0.25.0",                                            ↵
          "dist": "pgTAP",                                                    ↵
          "version": "0.25.0",                                                ↵
          "date": "2011-02-02T03:25:17Z",                                     ↵
          "user": "theory",                                                   ↵
          "user_name": "David E. Wheeler"                                     ↵
       },                                                                     ↵
       {                                                                      ↵
          "extension": "pg_french_datatypes",                                 ↵
          "abstract": "french-centric data type",                             ↵
          "ext_version": "0.1.1",                                             ↵
          "dist": "pg_french_datatypes",                                      ↵
          "version": "0.1.1",                                                 ↵
          "date": "2011-01-30T16:51:16Z",                                     ↵
          "user": "daamien",                                                  ↵
          "user_name": "damien clochard"                                      ↵
       }                                                                      ↵

Returns a JSON string containing extension statitics. These include:

  • count: A count of the number of extensions in the database.
  • recent: A list of recently-released extensions sorted in reverse chronlogical order.

Pass in the optional num_recent parameter to limit the number of extensions that appear in the recent list. The default limit is 56.

dist_stats_json

Parameters
  • IN num_recent INT DEFAULT 56
Returns
TEXT
% select dist_stats_json(3);
                                dist_stats_json
───────────────────────────────────────────────────────────────────────────────
 {                                                                            ↵
    "count": 92,                                                              ↵
    "releases": 345,                                                          ↵
    "recent": [                                                               ↵
       {                                                                      ↵
          "dist": "countnulls",                                               ↵
          "version": "1.0.0",                                                 ↵
          "abstract": "Simple function to count the number of NULL arguments",↵
          "date": "2011-03-15T16:44:26Z",                                     ↵
          "user": "alexk",                                                    ↵
          "user_name": "Alexey Klyukin"                                       ↵
       },                                                                     ↵
       {                                                                      ↵
          "dist": "pgTAP",                                                    ↵
          "version": "0.25.0",                                                ↵
          "abstract": "Unit testing for PostgreSQL",                          ↵
          "date": "2011-02-02T03:25:17Z",                                     ↵
          "user": "theory",                                                   ↵
          "user_name": "David E. Wheeler"                                     ↵
       },                                                                     ↵
       {                                                                      ↵
          "dist": "pg_french_datatypes",                                      ↵
          "version": "0.1.1",                                                 ↵
          "abstract": "french-centric data type",                             ↵
          "date": "2011-01-30T16:51:16Z",                                     ↵
          "user": "daamien",                                                  ↵
          "user_name": "damien clochard"                                      ↵
       }                                                                      ↵
    ]                                                                         ↵
 }                                                                            ↵

Returns a JSON string containing distribution statitics. These include:

  • count: A count of the number of distributions in the database.
  • releases: A count of all releases of all distributions in the database.
  • recent: A list of recently-released distributions sorted in reverse chronlogical order.

Pass in the optional num_recent parameter to limit the number of distributions that appear in the recent list. The default limit is 56.

summary_stats_json

Returns
TEXT
% select summary_stats_json();
  summary_stats_json
────────────────────────
 {                     ↵
     "dists": 92,      ↵
     "releases": 345,  ↵
     "extensions": 125,↵
     "users": 256,     ↵
     "tags": 112,      ↵
     "mirrors": 8      ↵
 }                     ↵

Returns a JSON string containing basic statistics about the system. These include:

  • dists: Number of distributions.
  • releases: Number of releases of distributions.
  • extensions: Number of extensions.
  • users: Number of users.
  • mirrors: Number of mirrors.

Pass in the optional num_to_list parameter to limit the number of objects that will appear in the recent/popular/prolific lists in the results. The default limit is 56.

all_stats_json

Parameters
  • IN num_to_list INT DEFAULT 56
Returns
TABLE ( stats_name TEXT, json TEXT )
% select all_stats_json();
 stats_name   │                                 json
──────────────┼─────────────────────────────────────────────────────────────
 dist         | {                                                          ↵
              │    "count": 92,                                            ↵
              │    "releases": 345,                                        ↵
              │    "recent": [                                             ↵
              │       {                                                    ↵
              │          "dist": "pair",                                   ↵
              │          "version": "0.0.1",                               ↵
              │          "abstract": "Ordered pair",                       ↵
              │          "date": "2011-03-15T16:44:26Z",                   ↵
              │          "user": "theory",                                 ↵
              │          "user_name": "David Wheeler"                      ↵
              │       },                                                   ↵
              │       {                                                    ↵
              │           "dist": "pg_french_datatypes",                   ↵
              │           "version": "0.1.1",                              ↵
              │           "abstract": "french-centric data type",          ↵
              │           "date": "2011-01-30T16:51:16Z",                  ↵
              │           "user": "daamien",                               ↵
              │           "user_name": "damien clochard"                   ↵
              │       }                                                    ↵
              │    ]                                                       ↵
              │ }                                                          ↵
 extension    │                                                            ↵
              │ {                                                          ↵
              │    "count": 125,                                           ↵
              │    "recent": [                                             ↵
              │       {                                                    ↵
              │          "extension": "pair",                              ↵
              │          "abstract": "Ordered pair",                       ↵
              │          "ext_version": "0.0.1",                           ↵
              │          "dist": "pair",                                   ↵
              │          "version": "0.0.1",                               ↵
              │          "date": "2011-03-15T16:44:26Z",                   ↵
              │          "user": "theory",                                 ↵
              │          "user_name": "David Wheeler"                      ↵
              │       },                                                   ↵
              │       {                                                    ↵
              │          "extension": "pg_french_datatypes",               ↵
              │          "abstract": "french-centric data type",           ↵
              │          "ext_version": "0.1.1",                           ↵
              │          "dist": "pg_french_datatypes",                    ↵
              │          "version": "0.1.1",                               ↵
              │          "date": "2011-01-30T16:51:16Z",                   ↵
              │          "user": "daamien",                                ↵
              │          "user_name": "damien clochard"                    ↵
              │       }                                                    ↵
              │    ]                                                       ↵
              │ }                                                          ↵
              │                                                            ↵
 user         │ {                                                          ↵
              │    "count": 256,                                           ↵
              │    "prolific": [                                           ↵
              │       {"nickname": "theory", "dists": 3, "releases": 4},   ↵
              │       {"nickname": "daamien", "dists": 1, "releases": 2},  ↵
              │       {"nickname": "umitanuki", "dists": 1, "releases": 1} ↵
              │    ]                                                       ↵
              │ }                                                          ↵
              │                                                            ↵
 tag          │ {                                                          ↵
              │    "count": 212,                                           ↵
              │    "popular": [                                            ↵
              │       {"tag": "data types", "dists": 4},                   ↵
              │       {"tag": "key value", "dists": 2},                    ↵
              │       {"tag": "france", "dists": 1},                       ↵
              │       {"tag": "key value pair", "dists": 1}                ↵
              │     ]                                                      ↵
              │ }                                                          ↵
              │                                                            ↵
 summary      │ {                                                          ↵
              │    "dists": 92,                                            ↵
              │    "releases": 345,                                        ↵
              │    "extensions": 125,                                      ↵
              │    "users": 256,                                           ↵
              │    "tags": 112,                                            ↵
              │    "mirrors": 8                                            ↵
              │ }                                                          ↵
              │                                                            ↵

Returns a table of all the system stats. The first column, stats_name, contains the name of the statistics file. The second column contains the statistics in JSON format.

add_distribution

Parameters
  • IN nick LABEL
  • IN sha1 TEXT
  • IN meta TEXT
Returns
TABLE ( template TEXT, subject TEXT, json TEXT )
% SELECT * FROM add_distribution('theory', 'ebf381e2e1e5767fb068d1c4423a9d9f122c2dc6', '{
    "name": "pair",
    "version": "0.0.01",
    "license": "postgresql",
    "maintainer": "theory",
    "abstract": "Ordered pair",
    "tags": ["ordered pair", "key value"],
    "provides": {
        "pair": {
            "file": "pair.sql.in",
            "version": "0.02.02",
            "abstract": "A key/value data type"
        },
        "trip": {
            "file": "trip.sql.in",
            "version": "0.02.01",
            "abstract": "A triple data type"
        }
    },
    "release_status": "testing"
}');

 template  │   subject    │                                 json                                 
───────────┼──────────────┼──────────────────────────────────────────────────────────────────────
 meta      │ pair         │ {                                                                   ↵
           │              │    "name": "pair",                                                  ↵
           │              │    "abstract": "Ordered pair",                                      ↵
           │              │    "version": "0.0.1",                                              ↵
           │              │    "maintainer": "theory",                                          ↵
           │              │    "date": "2011-03-15T16:44:26Z",                                  ↵
           │              │    "release_status": "testing",                                     ↵
           │              │    "user": "theory",                                                ↵
           │              │    "sha1": "ebf381e2e1e5767fb068d1c4423a9d9f122c2dc6",              ↵
           │              │    "license": "postgresql",                                         ↵
           │              │    "provides": {                                                    ↵
           │              │       "pair": {                                                     ↵
           │              │          "file": "pair.sql.in",                                     ↵
           │              │          "version": "0.2.2",                                        ↵
           │              │          "abstract": "A key/value data type"                        ↵
           │              │       },                                                            ↵
           │              │       "trip": {                                                     ↵
           │              │          "file": "trip.sql.in",                                     ↵
           │              │          "version": "0.2.1"                                         ↵
           │              │          "abstract": "A triple data type"                           ↵
           │              │       }                                                             ↵
           │              │    },                                                               ↵
           │              │    "tags": ["ordered pair", "key value"]                            ↵
           │              │ }                                                                   ↵
           │              │ 
 dist      │ pair         │ {                                                                   ↵
           │              │    "name": "pair",                                                  ↵
           │              │    "releases": {                                                    ↵
           │              │       "testing": ["0.0.1"]                                          ↵
           │              │    }                                                                ↵
           │              │ }                                                                   ↵
           │              │ 
 extension │ pair         │ {                                                                   ↵
           │              │    "extension": "pair",                                             ↵
           │              │    "latest": "testing",                                             ↵
           │              │    "testing":  { "dist": "pair", "version": "0.0.1" },              ↵
           │              │    "versions": {                                                    ↵
           │              │       "0.2.2": [                                                    ↵
           │              │          { "dist": "pair", "version": "0.0.1", "status": "testing" }↵
           │              │       ]                                                             ↵
           │              │    }                                                                ↵
           │              │ }                                                                   ↵
           │              │ 
 extension │ trip         │ {                                                                   ↵
           │              │    "extension": "trip",                                             ↵
           │              │    "latest": "testing",                                             ↵
           │              │    "testing":  { "dist": "pair", "version": "0.0.1" },              ↵
           │              │    "versions": {                                                    ↵
           │              │       "0.2.1": [                                                    ↵
           │              │          { "dist": "pair", "version": "0.0.1", "status": "testing" }↵
           │              │       ]                                                             ↵
           │              │    }                                                                ↵
           │              │ }                                                                   ↵
           │              │ 
 user      │ theory       │ {                                                                   ↵
           │              │    "nickname": "theory",                                            ↵
           │              │    "name": "",                                                      ↵
           │              │    "email": "theory@pgxn.org",                                      ↵
           │              │    "releases": {                                                    ↵
           │              │       "pair": {                                                     ↵
           │              │          "testing": ["0.0.1"]                                       ↵
           │              │       }                                                             ↵
           │              │    }                                                                ↵
           │              │ }                                                                   ↵
           │              │ 
 tag       │ ordered pair │ {                                                                   ↵
           │              │    "tag": "ordered pair",                                           ↵
           │              │    "releases": {                                                    ↵
           │              │       "pair": {                                                     ↵
           │              │          "testing": [ "0.0.1" ]                                     ↵
           │              │       }                                                             ↵
           │              │    }                                                                ↵
           │              │ }                                                                   ↵
           │              │ 
 tag       │ key value    │ {                                                                   ↵
           │              │    "tag": "key value",                                              ↵
           │              │    "releases": {                                                    ↵
           │              │       "pair": {                                                     ↵
           │              │          "testing": [ "0.0.1" ]                                     ↵
           │              │       }                                                             ↵
           │              │    }                                                                ↵
           │              │ }                                                                   ↵
           │              │                                                                     ↵
 stats     | dist         | {                                                                   ↵
           │              │    "count": 92,                                                     ↵
           │              │    "releases": 345,                                                 ↵
           │              │    "recent": [                                                      ↵
           │              │       {                                                             ↵
           │              │          "dist": "pair",                                            ↵
           │              │          "version": "0.0.1",                                        ↵
           │              │          "abstract": "Ordered pair",                                ↵
           │              │          "date": "2011-03-15T16:44:26Z",                            ↵
           │              │          "user": "theory",                                          ↵
           │              │          "user_name": "David Wheeler"                               ↵
           │              │       },                                                            ↵
           │              │       {                                                             ↵
           │              │           "dist": "pg_french_datatypes",                            ↵
           │              │           "version": "0.1.1",                                       ↵
           │              │           "abstract": "french-centric data type",                   ↵
           │              │           "date": "2011-01-30T16:51:16Z",                           ↵
           │              │           "user": "daamien",                                        ↵
           │              │           "user_name": "damien clochard"                            ↵
           │              │       }                                                             ↵
           │              │    ]                                                                ↵
           │              │ }                                                                   ↵
 stats     │ extension    │                                                                     ↵
           │              │ {                                                                   ↵
           │              │    "count": 125,                                                    ↵
           │              │    "recent": [                                                      ↵
           │              │       {                                                             ↵
           │              │          "extension": "pair",                                       ↵
           │              │          "abstract": "Ordered pair",                                ↵
           │              │          "ext_version": "0.0.1",                                    ↵
           │              │          "dist": "pair",                                            ↵
           │              │          "version": "0.0.1",                                        ↵
           │              │          "date": "2011-03-15T16:44:26Z",                            ↵
           │              │          "user": "theory",                                          ↵
           │              │          "user_name": "David Wheeler"                               ↵
           │              │       },                                                            ↵
           │              │       {                                                             ↵
           │              │          "extension": "pg_french_datatypes",                        ↵
           │              │          "abstract": "french-centric data type",                    ↵
           │              │          "ext_version": "0.1.1",                                    ↵
           │              │          "dist": "pg_french_datatypes",                             ↵
           │              │          "version": "0.1.1",                                        ↵
           │              │          "date": "2011-01-30T16:51:16Z",                            ↵
           │              │          "user": "daamien",                                         ↵
           │              │          "user_name": "damien clochard"                             ↵
           │              │       }                                                             ↵
           │              │    ]                                                                ↵
           │              │ }                                                                   ↵
           │              │                                                                     ↵
 stats     │ user         │ {                                                                   ↵
           │              │    "count": 256,                                                    ↵
           │              │    "prolific": [                                                    ↵
           │              │       {"nickname": "theory", "dists": 3, "releases": 4},            ↵
           │              │       {"nickname": "daamien", "dists": 1, "releases": 2},           ↵
           │              │       {"nickname": "umitanuki", "dists": 1, "releases": 1}          ↵
           │              │    ]                                                                ↵
           │              │ }                                                                   ↵
           │              │                                                                     ↵
 stats     │ tag          │ {                                                                   ↵
           │              │    "count": 212,                                                    ↵
           │              │    "popular": [                                                     ↵
           │              │       {"tag": "data types", "dists": 4},                            ↵
           │              │       {"tag": "key value", "dists": 2},                             ↵
           │              │       {"tag": "france", "dists": 1},                                ↵
           │              │       {"tag": "key value pair", "dists": 1}                         ↵
           │              │     ]                                                               ↵
           │              │ }                                                                   ↵
           │              │                                                                     ↵
 stats     │ summary      │ {                                                                   ↵
           │              │    "dists": 92,                                                     ↵
           │              │    "releases": 345,                                                 ↵
           │              │    "extensions": 125,                                               ↵
           │              │    "users": 256,                                                    ↵
           │              │    "tags": 112,                                                     ↵
           │              │    "mirrors": 8                                                     ↵
           │              │ }                                                                   ↵
           │              │                                                                     ↵

Creates a new distribution, returning all of the JSON that needs to be written to the mirror in order for the distribution to be indexed. The nickname of the uploading user must be passed as the first argument. The SHA1 of the distribution file must be passed as the second argument. All other metadata is parsed from the JSON string, which should contain the complete contents of the distribution's META.json file. The required keys in the JSON metadata are:

name
The name of the extension.
version
The extension version string. Will be normalized by clean_semver().
license
The license or licenses.
maintainer
The distribution maintainer or maintainers.
abstract
Short description of the distribution.

See the PGXN Meta Spec for the complete list of specified keys.

With this data, add_distribution() does the following things:

  • Parses the JSON string and validates that all required keys are present. Throws an exception if they're not.

  • Creates a new metadata structure and stores all the required and many of the optional meta spec keys, as well as the SHA1 of the distribution file, the release date, and the user's nickname.

  • Normalizes all of the version numbers found in the metadata into compliant semantic version strings. See SemVer->normal for details on how non-compliant version strings are converted. Versions that cannot be normalized will trigger an exception.

  • Specifies that the provided extension is the same as the distribution name and version if no "provides" metadata is present in the distribution metadata.

  • Validates that the uploading user is owner or co-owner of all provided extensions. If no one is listed as owner of one or more included extensions, the user will be assigned ownership. If the user is not owner or co-owner of any included extensions, an exception will be thrown.

  • Inserts the distribution data into the distributions table.

  • Inserts records for all included extensions into the distribution_extensions table.

  • Inserts records for all associated tags into the distribution_tags table.

Once all this work is done, add_distribution() returns a relation with the following columns:

template
Name of a mirror URI template.
subject
The subject of the metadata to be written, such as the name of a distribution, extension, user, tag, or statistics.
json
The JSON-formatted metadata for the subject, which the application should write to the fie specified by the template.

get_distribution

Parameters
  • IN dist TEXT
  • IN version SEMVER
Returns
TABLE ( template TEXT, subject TEXT, json TEXT )

Returns all of the metadata updates to be stored for a given distribution. The output is the same as for add_distribution(), but the distribution must already exist in the database. Useful for reindexing a distribution or re-generating metadata files. If the distribution or its version do not exist, no rows will be returned.

update_distribution

Parameters
  • IN nick LABEL
  • IN sha1 TEXT
  • IN meta TEXT
Returns
TABLE ( template TEXT, subject TEXT, json TEXT )

Exactly like add_distribution(), with the same arguments and rules, but updates an existing distribution, rather than creating a new one. This may be useful of the format of the generated META.json file changes: just call this method for all existing distributions to have then reindexed with the new format.

Note that, for all included extensions, nick must have ownership or no one must, in which case the user will be given ownership. This might be an issue when re-indexing a distribution containing extensions that the user owned at the time the distribution was released, but no longer does. In that case, you'll probably need to grant the user temporary co-ownership of all extensions, re-index, and then revoke.

Something went wrong with that request. Please try again.