StoredID Database DDL

RDBMS-specific DDL statements for creating the shibpid Table, as used by the Stored ID Data Connector.

Note that principalName represents your local usernames/principals, so you might want to adjust the maximum column size to taste. Same for localId, which represents the value for the sourceAttributeID attribute from the StoredId DataConnector.

MySQL

Nicked straight from the Install Shibboleth 2.4 Identity Provider, Tomcat and Apache guide for the SWITCHaai, Section 6.2.2. "Create users and databases".

CREATE TABLE IF NOT EXISTS shibpid (
  localEntity TEXT NOT NULL,
  peerEntity TEXT NOT NULL,
  principalName VARCHAR(255) NOT NULL DEFAULT '',
  localId VARCHAR(255) NOT NULL,
  persistentId VARCHAR(36) NOT NULL,
  peerProvidedId VARCHAR(255) DEFAULT NULL,
  creationDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deactivationDate TIMESTAMP NULL DEFAULT NULL,
  KEY persistentId (persistentId),
  KEY persistentId_2 (persistentId, deactivationDate),
  KEY localEntity (localEntity(16), peerEntity(16), localId),
  KEY localEntity_2 (localEntity(16), peerEntity(16),
    localId, deactivationDate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Oracle

DDL Statement for the Oracle RDBMS.

CREATE TABLE shibpid
(   
    localEntity VARCHAR2(1024 BYTE) NOT NULL ENABLE,
    peerEntity VARCHAR2(1024 BYTE) NOT NULL ENABLE,
    principalName VARCHAR2(255) NOT NULL ENABLE,
    localId VARCHAR2(255) NOT NULL ENABLE,
    persistentId VARCHAR2(36 BYTE) NOT NULL ENABLE,
    peerProvidedId VARCHAR2(255 BYTE),
    creationDate DATE DEFAULT SYSDATE NOT NULL ENABLE,
    deactivationDate DATE
)

CREATE INDEX persistentId ON shibpid(persistentId, deactivationDate);
CREATE INDEX localentity ON shibpid(localEntity, peerEntity, localId, deactivationDate);

You might want to adjust the columns of type VARCHAR2(1024) to more realistic sizes as this is just the theoretical upper limit from the SAML specs, especially for localEntity which you control (your IdP's entityId). Also, local userids might be significantly shorter, in which case you could also adjust the size of the principalName and localId columns.
Having VARCHAR2(1024) columns with CHAR semantics in an UTF-8 database will also prevent you from creating the latter of the two indices (localentity) and might not improve anything, as Oracle's Cost Based Optimizer supposedly will prefer full-table scans to using indices on such large columns.
Best to ask your local DBA.

PostgreSQL

DDL Statement for the PostgreSQL RDBMS.

 CREATE TABLE shibpid (
    localEntity VARCHAR(1024) NOT NULL,
    peerEntity VARCHAR(1024) NOT NULL,
    principalName VARCHAR(255) NOT NULL,
    localId VARCHAR(255) NOT NULL,
    persistentId VARCHAR(36) NOT NULL,
    peerProvidedId VARCHAR(255) NULL,
    creationDate TIMESTAMP NOT NULL DEFAULT LOCALTIMESTAMP,
    deactivationDate TIMESTAMP NULL DEFAULT NULL
);
CREATE INDEX persistentId ON shibpid(persistentId);
CREATE INDEX persistentId_2 ON shibpid(persistentId, deactivationDate);
CREATE INDEX localentity ON shibpid(localEntity, peerEntity, localId);
CREATE INDEX localentity_2 ON shibpid(localEntity, peerEntity, localId, deactivationDate);

Warning : as the PostgreSQL JDBC Driver doesn't implement query timeouts, you must add queryTimeout="0" in your DataConnector definition. For example :

<!-- StoredID (persistentID)-->
<resolver:DataConnector
        id="storedId"
        xsi:type="dc:StoredId"
        xmlns="urn:mace:shibboleth:2.0:resolver:dc"
        generatedAttributeID="persistentID"
        sourceAttributeID="uid"
        queryTimeout="0"
        salt="type some alphanumeric characters here">
        <resolver:Dependency
                ref="myLDAP" />
        <dc:ApplicationManagedConnection
                jdbcDriver="org.postgresql.Driver"
                jdbcURL="jdbc:postgresql://postgresql-server.domain.tld/shibboleth"
                jdbcUserName="some user"
                jdbcPassword="some password" />
</resolver:DataConnector>

StoredIDDataConnectorDDL

StoredID Database DDL

MySQL

Oracle

PostgreSQL