Create a database manually

​

Create a blank database

1. Connect to the PostgreSQL server using an account with CREATEDB+CREATEROLE or SUPERUSER privileges:
   psql -h srvname -U postgres
   
2. Create a user account with a predefined password and the right to log in to the server (here and in later steps, rolename is your user name):
   CREATE ROLE rolename WITH ENCRYPTED PASSWORD 'rolepwd' LOGIN;
   
3. Create a new database (here and in later steps, dbname is your database name):
   CREATE DATABASE dbname;
   
4. Grant the user the rights to connect to the database and to create temporary tables:
   GRANT CONNECT, TEMPORARY ON DATABASE dbname TO rolename;
   
5. Prioritize the fc schema over the public schema:
   ALTER DATABASE dbname SET search_path = "$user", fc, public;
   
6. Connect to the newly created database:
   \c dbname
   
7. Create an fc schema and specify the user you created as its owner:
   CREATE SCHEMA fc AUTHORIZATION rolename;
   
8. Create an ltree extension in the database:
   CREATE EXTENSION IF NOT EXISTS ltree;
   
9. Disconnect from the PostgreSQL server.

​

Initialize and configure the database

1. Run the schema description script for your database:
   • Microsoft SQL Server: Description.sql
   • Oracle: Description_Oracle.sql
   • PostgreSQL: Description_PostgreSQL.sql
   By default, these scripts are in the C:\inetpub\wwwroot\FlexiCapture12\Server folder on the computer where the Application Server is installed.
2. Run the data initialization script for your database:
   • Microsoft SQL Server: DBInitFill.sql
   • Oracle: DBInitFill_Oracle.sql
   • PostgreSQL: DBInitFill_PostgreSQL.sql
3. Provide a unique identifier for the database. Microsoft SQL Server:
   INSERT INTO dbo.Settings( Name, Value, ProjectId, BatchTypeId, UserId, Workstation, RoleId, ProcessingStage) VALUES ('DatabaseGUID', NEWID(), NULL, NULL, NULL, NULL, NULL, NULL )
   
   PostgreSQL:
   INSERT INTO Settings (Name, Value)
   VALUES ('DatabaseGUID', upper(md5(clock_timestamp()::text)::uuid::text));
   
4. Add the first user. Microsoft SQL Server:
   INSERT INTO principal (Name, FullName, EMail, PasswordHash, PasswordReset) values (N'Login', N'FullName', N'email', '', 0)
   
   PostgreSQL:
   INSERT INTO Principal (Name, FullName, Email, PasswordHash, PasswordReset) values ('Login', 'FullName', 'email', '', false);
   
   Where:
   • Login — The login the user will use. Specify the login of the Windows user that will open the Administration and Monitoring Console when connecting to the database.
   • FullName — The full name of the user as displayed in their profile.
   • Email — The user’s email address.
5. Specify the system administrator roles for the new user. Microsoft SQL Server:
   INSERT INTO principalpermission (PrincipalId, RoleType, IsAllowed) values (1, 10, 1);
   
   PostgreSQL:
   INSERT INTO principalpermission (PrincipalId, RoleType, IsAllowed) values (1, 10, true);
   
6. Connect to the newly created database using the Administration and Monitoring Console.