Skip to content

DB API

Jump to bottom Edit New page
David E. Wheeler edited this page Sep 7, 2010 · 13 revisions

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).

Public Schema Functions

change_password

Parameters
  • IN nick 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.

log_visit

Parameters
  • IN nick 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_admin

Parameters
  • IN setter LABEL
  • IN nick LABEL
  • IN setto 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.

forgot_password

Parameters
  • IN nick LABEL
Returns

TEXT

% SELECT forgot_password('theory');
       forgot_password        
──────────────────────────────
 {G8Gxz,[email protected]}

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).

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 NUMERIC
  • IN def TEXT DEFAULT 'null'
Returns

TEXT

% SELECT json_value(1.2), json_value(NULL::int),
         json_value(NULL::int, 'default'), json_value(NULL::int, NULL);
 json_value │ json_value │ json_value │ json_value 
────────────┼────────────┼────────────┼────────────
 1.2        │ null       │ default    │

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

add_distribution

Parameters
  • IN nick LABEL
  • IN sha1 TEXT
  • IN meta TEXT
Returns

TEXT

% SELECT add_distribution('theory', 'ebf381e2e1e5767fb068d1c4423a9d9f122c2dc6', '{
    "name": "pair",
    "version": "0.0.01",
    "license": "postgresql",
    "maintainer": "theory",
    "abstract": "Ordered pair",
    "provides": {
        "pair": { "file": "pair.sql.in", "version": "0.02.02" },
        "trip": { "file": "trip.sql.in", "version": "0.02.01" }
    },
    "release_status": "testing"
}');
                       add_distribution                                                                                                                                                   
─────────────────────────────────────────────────────────────
 {
   "maintainer": "theory",
   "owner": "theory",
   "sha1": "ebf381e2e1e5767fb068d1c4423a9d9f122c2dc6",
   "version": "0.0.1",
   "name": "pair",
   "license": "postgresql",
   "provides": {
     "pair": { "version": "0.2.2", "file": "pair.sql.in" },
     "trip": { "version": "0.2.1", "file": "trip.sql.in" }
   },
   "abstract": "Ordered pair",
   "release_status": "testing"
 }

Creates a new distribution, returning the JSON to be used in the metadata file for the distribution. The nickname of the uploading user (owner) 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 hte optional meta spec keys, as well as the SHA1 of the distribution file and the owner's nickname.

  • Normalizes all of the version numbers found in the metadata into compliant semantic version strings. See clean_semver() for details on how non-compliant version strings are converted.

  • 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 exentions into the distribution_extensions table.

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

  • Returns the index metadata as a JSON string. If any argument is NULL, returns NULL, in which case the distribution will not have been added.

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.

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.

insert_mirror

Parameters
  • IN creator LABEL
  • 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 contact 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 insert_mirror(
    'theory',
    uri          := 'http://kineticode.com/pgxn/',
    frequency    := 'hourly',
    location     := 'Portland, OR, USA',
    bandwidth    := '10MBps',
    organization := 'Kineticode, Inc.',
    timezone     := 'America/Los_Angeles',
    contact      := '[email protected]',
    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).

munge_email

Parameters
  • IN email EMAIL
Returns

TEXT

% SELECT munge_email('[email protected]');
 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 "[email protected]" becomes "bar.com|foo".

by_extension_json

Parameters
  • IN dist TEXT
  • IN version SEMVER
Returns

TABLE ( extension CITEXT, json TEXT )

% SELECT * FROM by_extension_json('pair', '1.0.0');
 extension │                                     json                                     
───────────┼──────────────────────────────────────────────────────────────────────────────
 pair      │ {                                                                           ↵
           │    "stable": "1.0.0",                                                       ↵
           │    "testing": "1.2.0",                                                      ↵
           │    "releases": {                                                            ↵
           │       "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      │ {                                                                           ↵
           │    "stable": "0.9.9",                                                       ↵
           │    "testing": "0.9.10",                                                     ↵
           │    "releases": {                                                            ↵
           │       "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.

by_tag_json

Parameters
  • IN dist TEXT
  • IN version SEMVER
Returns

TABLE ( tag CITEXT, json TEXT )

% SELECT * from by_tag_json('pgtap', '0.0.1');
   tag   │                  json                  
─────────┼────────────────────────────────────────
 schema  │ {                                     ↵
         │    "tag": "schema",                   ↵
         │    "releases": {                      ↵
         │       "pair": {                       ↵
         │          "stable": ["1.0.0"],         ↵
         │          "testing": ["1.2.0", "0.0.1"]↵
         │       },                              ↵
         │       "pgtap": {                      ↵
         │          "testing": ["0.0.1"]         ↵
         │       }                               ↵
         │    }                                  ↵
         │ }                                     ↵
         │ 
 testing │ {                                     ↵
         │    "tag": "testing",                  ↵
         │    "releases": {                      ↵
         │       "pgtap": {                      ↵
         │          "testing": ["0.0.1"]         ↵
         │       }                               ↵
         │    }                                  ↵
         │ }                                     ↵
         │

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

Add a custom footer
Add a custom sidebar
Clone this wiki locally