Create Directories Submodule in Jadu Galaxy

The process of adding the Directories module to a Galaxy is as follows:

1. Create and populate tables in the Galaxy DB (e.g.
   ms_greenwich_GALAXY_NUMBER) — see SQL below.

2. Create (or copy) the appropriate 8 frontend scripts (which reference those in
   the parent galaxy) in the galaxy’s /site/scripts/ folder — see
   below:

   directories_index.php
   directory_home.php
   directory_search.php
   directory_az.php
   directory_record.php
   directory_category.php

   optionally:
   directory_record_edit.php
   directory_submit.php

3. .htaccess

   Make sure the .htaccess file includes the correct http redirection settings
   to support readable URLs

   NOTE – the creation of readable URLs in a galaxy rewrites the .htaccess
   file – make sure that # Friendly URLs section is set up after the
   end of the # Directories section.

4. Update the Galaxy

   This republishes the Galaxy by rebuilding its database.

   To get republishing to work (it should take a while, if the CMS page
   reloads quickly it has failed – check the php error log) – You might need
   to give write access to IUSR and IIS_IUSRS on the dir
   galaxy_name/logs/W3SVC_iis-site-num to enable copy access to FTP
   during clustersync operations.

5. Clear the appropriate caches

   Within /jadu/maintenance/cacheBash.php select the following
   cached tables for refreshing:
   JaduModulePages
JaduModules


6. Check admin permissions for your login

   You may need to check
   /GALAXY-NAME/jadu/utils/access_admin.php?adminIDEditing=4 has Directories ticked
   for your Administrator

7. Then should be able to see Directories link in the CMS `Publishing` menu.

8. Once you've created a directory, it will be available under  `/directories`

MS SQL SERVER syntax for directory setup

USE db.name.for.galaxy

# Create Directories Submodule in Jadu Galaxy

IF OBJECT_ID ('dbo.JaduDirectories') IS NOT NULL
    DROP TABLE dbo.JaduDirectories

CREATE TABLE dbo.JaduDirectories
    (
    id                     INT IDENTITY NOT NULL,
    name                   NVARCHAR (50) DEFAULT ('') NOT NULL,
    description            NVARCHAR (max),
    allowPublicSubmissions SMALLINT DEFAULT ((0)) NOT NULL,
    showSocialBookmarks    SMALLINT DEFAULT ((0)) NOT NULL,
    azFieldID              INT DEFAULT ((0)) NOT NULL,
    adminID                INT DEFAULT ((-1)) NOT NULL,
    adminEmail             NVARCHAR (255) DEFAULT ('') NOT NULL,
    live                   SMALLINT DEFAULT ((0)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryAppliedCategories') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryAppliedCategories

CREATE TABLE dbo.JaduDirectoryAppliedCategories
    (
    id           INT IDENTITY NOT NULL,
    categoryType NVARCHAR (255) DEFAULT ('') NOT NULL,
    categoryID   INT DEFAULT ((1)) NOT NULL,
    refCount     INT DEFAULT ((0)) NOT NULL,
    liveCount    INT DEFAULT ((0)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryCategories') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryCategories

CREATE TABLE dbo.JaduDirectoryCategories
    (
    id           INT IDENTITY NOT NULL,
    categoryType NVARCHAR (255) DEFAULT ('') NOT NULL,
    categoryID   INT DEFAULT ((1)) NOT NULL,
    itemID       INT DEFAULT ((0)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryCategoryTree') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryCategoryTree

CREATE TABLE dbo.JaduDirectoryCategoryTree
    (
    id          INT IDENTITY NOT NULL,
    directoryID INT DEFAULT ((-1)) NOT NULL,
    title       NVARCHAR (50) DEFAULT ('') NOT NULL,
    position    SMALLINT DEFAULT ((-1)) NOT NULL,
    parentID    SMALLINT DEFAULT ((-1)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryCategoryAdverts') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryCategoryAdverts

CREATE TABLE dbo.JaduDirectoryCategoryAdverts
    (
    id            INT IDENTITY NOT NULL,
    categoryID    SMALLINT DEFAULT ((-1)) NOT NULL,
    imageFilename NVARCHAR (50) DEFAULT (NULL),
    title         NVARCHAR (50) DEFAULT ('') NOT NULL,
    url           NVARCHAR (100) DEFAULT (NULL),
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryCategoryFields') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryCategoryFields

CREATE TABLE dbo.JaduDirectoryCategoryFields
    (
    id             INT IDENTITY NOT NULL,
    categoryID     SMALLINT DEFAULT ((-1)) NOT NULL,
    fieldID        NVARCHAR (50) DEFAULT ((-1)) NOT NULL,
    zoneFieldTitle NVARCHAR (50) DEFAULT (NULL),
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryCategoryInformation') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryCategoryInformation

CREATE TABLE dbo.JaduDirectoryCategoryInformation
    (
    id                  INT IDENTITY NOT NULL,
    categoryID          SMALLINT DEFAULT ((-1)) NOT NULL,
    imageFilename       NVARCHAR (50) DEFAULT (''),
    bulletImageFilename NVARCHAR (50) DEFAULT (''),
    description         NVARCHAR (max),
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryCategoryTree') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryCategoryTree

CREATE TABLE dbo.JaduDirectoryCategoryTree
    (
    id          INT IDENTITY NOT NULL,
    directoryID INT DEFAULT ((-1)) NOT NULL,
    title       NVARCHAR (50) DEFAULT ('') NOT NULL,
    position    SMALLINT DEFAULT ((-1)) NOT NULL,
    parentID    SMALLINT DEFAULT ((-1)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryEntries') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryEntries

CREATE TABLE dbo.JaduDirectoryEntries
    (
    id          INT IDENTITY NOT NULL,
    directoryID SMALLINT DEFAULT ((-1)) NOT NULL,
    title       NVARCHAR (255) DEFAULT ('') NOT NULL,
    live        SMALLINT DEFAULT ((1)) NOT NULL,
    modDate     DATETIME DEFAULT ('19700101') NOT NULL,
    userID      INT DEFAULT ((-1)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryEntryAppliedCategories') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryEntryAppliedCategories

CREATE TABLE dbo.JaduDirectoryEntryAppliedCategories
    (
    id           INT IDENTITY NOT NULL,
    categoryType NVARCHAR (255) DEFAULT ('') NOT NULL,
    categoryID   INT DEFAULT ((1)) NOT NULL,
    refCount     INT DEFAULT ((0)) NOT NULL,
    liveCount    INT DEFAULT ((0)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryEntryCategories') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryEntryCategories

CREATE TABLE dbo.JaduDirectoryEntryCategories
    (
    id           INT IDENTITY NOT NULL,
    categoryType NVARCHAR (255) DEFAULT ('') NOT NULL,
    categoryID   INT DEFAULT ((1)) NOT NULL,
    itemID       INT DEFAULT ((0)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryEntryMetadata') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryEntryMetadata

CREATE TABLE dbo.JaduDirectoryEntryMetadata
    (
    id          INT IDENTITY NOT NULL,
    itemID      SMALLINT DEFAULT (NULL),
    creator     NVARCHAR (max),
    contributor NVARCHAR (max),
    publisher   NVARCHAR (max),
    rights      NVARCHAR (max),
    source      NVARCHAR (max),
    status      NVARCHAR (max),
    coverage    NVARCHAR (max),
    created     DATETIME DEFAULT ('19700101') NOT NULL,
    modified    DATETIME DEFAULT ('19700101') NOT NULL,
    valid       DATETIME DEFAULT ('19700101') NOT NULL,
    expired     DATETIME DEFAULT ('19700101') NOT NULL,
    format      NVARCHAR (255) DEFAULT (NULL),
    language    NVARCHAR (255) DEFAULT (NULL),
    subject     NVARCHAR (max),
    description NVARCHAR (max),
    CONSTRAINT PK_JaduDirectoryEntryMetadata_id PRIMARY KEY (id)
    )

CREATE INDEX itemID
    ON dbo.JaduDirectoryEntryMetadata (itemID)


IF OBJECT_ID ('dbo.JaduDirectoryEntryValues') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryEntryValues

CREATE TABLE dbo.JaduDirectoryEntryValues
    (
    id      INT IDENTITY NOT NULL,
    entryID INT DEFAULT ((-1)) NOT NULL,
    fieldID SMALLINT DEFAULT ((-1)) NOT NULL,
    value   NVARCHAR (max),
    PRIMARY KEY (id)
    )

CREATE INDEX entryID
    ON dbo.JaduDirectoryEntryValues (entryID)


IF OBJECT_ID ('dbo.JaduDirectoryEntryVersions') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryEntryVersions

CREATE TABLE dbo.JaduDirectoryEntryVersions
    (
    id          INT IDENTITY NOT NULL,
    objectID    SMALLINT DEFAULT ((-1)) NOT NULL,
    category    NVARCHAR (50) DEFAULT ('') NOT NULL,
    object      NVARCHAR (max),
    modDate     DATETIME DEFAULT ('19700101') NOT NULL,
    liveVersion SMALLINT DEFAULT ((0)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryFieldDefaultValues') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryFieldDefaultValues

CREATE TABLE dbo.JaduDirectoryFieldDefaultValues
    (
    id               INT IDENTITY NOT NULL,
    directoryFieldID SMALLINT DEFAULT ((-1)) NOT NULL,
    value            NVARCHAR (255) DEFAULT ('') NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryFields') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryFields

CREATE TABLE dbo.JaduDirectoryFields
    (
    id                INT IDENTITY NOT NULL,
    directoryID       SMALLINT DEFAULT ((-1)) NOT NULL,
    title             NVARCHAR (50) DEFAULT ('') NOT NULL,
    fieldTypeID       SMALLINT DEFAULT ((-1)) NOT NULL,
    mandatory         SMALLINT DEFAULT ((0)) NOT NULL,
    defaultVisibility SMALLINT DEFAULT ((1)) NOT NULL,
    position          SMALLINT DEFAULT ((0)) NOT NULL,
    displayTitle      TINYINT NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryFieldTypes') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryFieldTypes

CREATE TABLE dbo.JaduDirectoryFieldTypes
    (
    id               INT IDENTITY NOT NULL,
    name             NVARCHAR (30) DEFAULT ('') NOT NULL,
    numDefaultValues SMALLINT DEFAULT ((1)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryMetadata') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryMetadata

CREATE TABLE dbo.JaduDirectoryMetadata
    (
    id          INT IDENTITY NOT NULL,
    itemID      SMALLINT DEFAULT (NULL),
    creator     NVARCHAR (max),
    contributor NVARCHAR (max),
    publisher   NVARCHAR (max),
    rights      NVARCHAR (max),
    source      NVARCHAR (max),
    status      NVARCHAR (max),
    coverage    NVARCHAR (max),
    created     DATETIME DEFAULT ('19700101') NOT NULL,
    modified    DATETIME DEFAULT ('19700101') NOT NULL,
    valid       DATETIME DEFAULT ('19700101') NOT NULL,
    expired     DATETIME DEFAULT ('19700101') NOT NULL,
    format      NVARCHAR (255) DEFAULT (NULL),
    language    NVARCHAR (255) DEFAULT (NULL),
    subject     NVARCHAR (max),
    description NVARCHAR (max),
    CONSTRAINT PK_JaduDirectoryMetadata_id PRIMARY KEY (id)
    )

CREATE INDEX itemID
    ON dbo.JaduDirectoryMetadata (itemID)


IF OBJECT_ID ('dbo.JaduDirectorySettings') IS NOT NULL
    DROP TABLE dbo.JaduDirectorySettings

CREATE TABLE dbo.JaduDirectorySettings
    (
    id           INT IDENTITY NOT NULL,
    directoryID  INT NOT NULL,
    fieldID      INT NOT NULL,
    valueType    NVARCHAR (30) NOT NULL,
    settingGroup NVARCHAR (30) NOT NULL,
    name         NVARCHAR (30) NOT NULL,
    value        NVARCHAR (255) DEFAULT (NULL),
    editable     SMALLINT DEFAULT ((1)) NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryUserEntries') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryUserEntries

CREATE TABLE dbo.JaduDirectoryUserEntries
    (
    id          INT IDENTITY NOT NULL,
    directoryID SMALLINT DEFAULT ((-1)) NOT NULL,
    title       NVARCHAR (255) DEFAULT ('') NOT NULL,
    modDate     DATETIME DEFAULT ('19700101') NOT NULL,
    userID      INT DEFAULT ((-1)) NOT NULL,
    liveEntryID INT NOT NULL,
    PRIMARY KEY (id)
    )


IF OBJECT_ID ('dbo.JaduDirectoryUserEntryValues') IS NOT NULL
    DROP TABLE dbo.JaduDirectoryUserEntryValues

CREATE TABLE dbo.JaduDirectoryUserEntryValues
    (
    id          INT IDENTITY NOT NULL,
    userEntryID INT DEFAULT ((-1)) NOT NULL,
    fieldID     SMALLINT DEFAULT ((-1)) NOT NULL,
    value       NVARCHAR (max),
    PRIMARY KEY (id)
    )

INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_list.php', 'Directories', -1, 0, 0, 4,0)
DECLARE @iddir INT
SELECT @iddir = (SELECT id FROM JaduModulePages WHERE page_url = '/directoryBuilder/directory_list.php')
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_details.php', 'Directory Details', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_overview.php', 'Directory Overview', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_fields.php', 'Directory Fields', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_categories.php', 'Directory Taxonomy', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_records_list.php', 'Directory Records', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_record_details.php', 'Directory Records', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_record_import.php', 'Directory Record Import', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/category_details.php', 'Category Details', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/category_overview.php', 'Category Overview', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/category_fields.php', 'Category Fields', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_export.php', 'Directory Export', @iddir, 0, 0, 1,1)
INSERT INTO dbo.JaduModulePages (module_id, page_url, name, parent_id, workflow, adminAccessOnly, position, pageGroup) VALUES (1, '/directoryBuilder/directory_settings.php', 'Directory Settings', @iddir, 0, 0, 1,1)

SET IDENTITY_INSERT JaduDirectoryFieldTypes ON
INSERT INTO JaduDirectoryFieldTypes (id, name, numDefaultValues) VALUES 
(1, 'Text Box', '1'),
(2, 'Text Area', '1'),
(3, 'Select Box', '100'),
(4, 'Radio Buttons', '100'),
(5, 'Image', '0'),
(6, 'Google Map', '0');
-- add further types here, or in the CMS later
SET IDENTITY_INSERT JaduDirectoryFieldTypes OFF

Frontend php scripts

The 8 frontend scripts within the Galaxy’s own file structure simply
reference the parent Galaxy e.g.:
<?php

include_once('JaduConstants.php');
include_once(GALAXIES_PUBLIC_HTML.'site/scripts/directories_index.php');

?>

.htaccess

Add the following lines to
/jadu/microsites/datasets/Master_Dataset/public_html/.htaccess – this has been
done and will apply to galaxies created after the event (or to the specific
galaxy, changing /Master/ to /GalaxyName/ – this will need to be done for
galaxies created before the above change):

# Directories
RewriteRule ^/?directories/([0-9]+)/[^/?/]*/?$ /Master/site/scripts/directories_index.php?categoryID=$1 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/a_to_z/([a-zA-Z])/categoryInformation/[0-9]+/?$ /Master/site/scripts/directory_az.php?directoryID=$1&startsWith=$2&categoryInformationID=$3 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/a_to_z/([a-zA-Z])/?$ /Master/site/scripts/directory_az.php?directoryID=$1&startsWith=$2 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/a_to_z/?$ /Master/site/scripts/directory_az.php?directoryID=$1 [QSA,NC,L]
RewriteRule ^/?directory_record/([0-9]+)/[^/?/]*/edit/userentry/?$ /Master/site/scripts/directory_record_edit.php?userRecordID=$1 [QSA,NC,L]
RewriteRule ^/?directory_record/([0-9]+)/[^/?/]*/edit/?$ /Master/site/scripts/directory_record_edit.php?recordID=$1 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/[^/?/]*/submit/?$ /Master/site/scripts/directory_submit.php?directoryID=$1 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/[^/?/]*/categoryInformation/([0-9]+)/search/?$ /Master/site/scripts/directory_search.php?directoryID=$1&categoryInfoID=$2 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/[^/?/]*/search/?$ /Master/site/scripts/directory_search.php?directoryID=$1 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/.+/category/([0-9]+)/categoryInfo/([0-9]+)/?$ /Master/site/scripts/directory_category.php?directoryCategoryID=$2&categoryInfoID=$3 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/.+/category/([0-9]+)/?$  /Master/site/scripts/directory_category.php?directoryCategoryID=$2 [QSA,NC,L]
RewriteRule ^/?directory/([0-9]+)/[^/?/]*/?$ /Master/site/scripts/directory_home.php?directoryID=$1 [QSA,NC,L]
RewriteRule ^/?directories/?$ /Master/site/scripts/directories_index.php [QSA,NC,L]
RewriteRule ^/?directory_record/([0-9]+)/[^/?/]*/?$ /Master/site/scripts/directory_record.php?recordID=$1 [QSA,NC,L]
RewriteRule ^/?directory_record/([0-9]+)/.+/category/([0-9]+)/.+/?$ /Master/site/scripts/directory_record.php?recordID=$1&categoryID=$2 [QSA,NC,L]

Ends