Xataface PHP Front-End to MySQL/MariaDB Database

[LHammonds]

WORK-IN-PROGRESS --------- WORK-IN-PROGRESS

Xataface is a great tool to aid in data entry to presenting data from a backend system (employee-facing). There does not seem to be a full example database / web application that shows off all of the most-used features so I will try to showcase a working example. The main reason is so that I have a working reference for the various settings for implementing into new projects...which speeds up development time MUCH faster having a working reference rather than just syntax options.


When this is done, I expect to have a .sql file for the example database (and data) and all the related files/folders to interface with the database.

Xataface website.
Xataface Google Group
Version 2.1.2 latest stable release.

Assumptions

This documentation will need to make use of some very-specific information that will most-likely be different for each person / location. And as such, this information will be noted in this section. They will be highlighted in red throughout the document as a reminder that you should plug-in your own value rather than actually using these "place-holder" values.

Under no circumstance should you use the actual values listed below. They are place-holders for the real thing. This is just a checklist template you need to have answered before you start the install process.

NOTE: "fqdn" is short for Fully-Qualified Domain Name such as mysite.mydomain.com

The root web folder: /var/www/fqdn/
App web folder: /var/www/fqdn/house
Xataface web folder: /var/www/fqdn/xataface
Database Server: 192.168.0.20
Database User Account: houseuser
Database User Password: houseuserpass

Reference articles:
* Data entry with xataface
* Xataface tips blog
* sjhannah projects

[LHammonds]

Server Setup

This article assumes you are using a Linux server with Apache and MariaDB but any web server that supports PHP and a database server that Xataface supports will work.

I will be using two Ubuntu servers, one as an Apache web server and one as a MariaDB server.

Large File Upload Support

PHP limits file size uploads or the timeout value for how long to wait for an upload to occur. Apache defaults to files no larger than 8 MB. So if you want to upload those 10 MB iPhone images, you are going to need to modify the upload limitations to handle your requirements.

The below will modify the upload limit to allow files as large as 2,048 MB to be uploaded.

Code: Select all

sudo vi /etc/php/7.4/apache2/php.ini

Code: Select all

post_max_size = 2058M
upload_max_filesize = 2048M

Reload Apache for changes to the config to take affect:

Code: Select all

sudo systemctl reload apache2

Permission Fix Script
We are going to be making files and folders at various points and to keep from having to manually fix ownership and permissions every time, we are going to have a script help us with that.

Code: Select all

touch /var/scripts/prod/xatafixperms.sh
chown root:root /var/scripts/prod/xatafixperms.sh
chmod 0750 /var/scripts/prod/xatafixperms.sh

Now edit the file and copy/paste this code into it...making sure to set the correct path for the WebDir and XatafaceDir variables.

/var/scripts/prod/xatafixperms.sh

Code: Select all

#!/bin/bash
#############################################################
## Name          : xatafixperms.sh
## Version       : 0.2
## Date          : 2020-09-12
## Author        : LHammonds
## Purpose       : Correct file/folder ownership/permissions
## Compatibility : Ubuntu Server 20.04 LTS
##                 Xataface 2.1.2 - 2.2.5
## Requirements  : run as root
## Run Frequency : Once per day or as often as desired.
## Parameters    : None
## Exit Codes    :
## 0  = Success
## 1  = ERROR: Must be root user
###################### CHANGE LOG ###########################
## DATE       VER WHO WHAT WAS CHANGED
## ---------- --- --- ---------------------------------------
## 2020-09-12 0.2 LTH Creating script (not finished)
#############################################################
User="www-data"
Group="www-data"

function f_fixperms()
{
  WebDir=$1
  echo "Current folder: ${WebDir}"
  echo "Correcting file/folder ownership..."
  chown --recursive ${User}:${Group} ${WebDir}
  echo "Setting default folder permissions..."
  chmod 0755 ${WebDir}
  find ${WebDir} -type d -print0 | xargs -0 --no-run-if-empty chmod 0770
  echo "Setting default file permissions..."
  find ${WebDir} -type f -print0 | xargs -0 chmod 0660
  find ${WebDir} -name '.htaccess' -type f -print0 | xargs -0 --no-run-if-empty chmod 0644
  find ${WebDir} -name '*.png' -type f -print0 | xargs -0 --no-run-if-empty chmod 0444
  find ${WebDir} -name '*.gif' -type f -print0 | xargs -0 --no-run-if-empty chmod 0444
  find ${WebDir} -name '*.jpg' -type f -print0 | xargs -0 --no-run-if-empty chmod 0444
}   ## f_fixperms()

f_fixperms /var/www/fqdn/xataface
f_fixperms /var/www/fqdn/house
f_fixperms /var/www/fqdn/garage
f_fixperms /var/www/fqdn/attic

Database Creation

>> INSERT GITHUB LINK <<

This example database will be called House and created as follows from the database console (be sure to substitute your own made-up credentials):

Code: Select all

CREATE DATABASE house CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'houseuser'@'%' IDENTIFIED BY 'houseuserpass';
GRANT ALL ON house.* TO 'houseuser'@'%';
FLUSH PRIVILEGES;

In a production environment, you would want to restrict the % usage as noted here.

Table Creation

The example house database will need to have these tables created:

Code: Select all

CREATE TABLE `tbl_box` (
  `Box_ID` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  `Name` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Name of the box',
  `Active` int(11) NOT NULL DEFAULT 1 COMMENT 'Active? Box still exist?',
  `DateCreated` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  `DateModified` datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY (`Box_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Container holding items';
CREATE TABLE `tbl_user` (
  `User_ID` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  `Username` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Username',
  `Password` varchar(32) COLLATE utf8_bin NOT NULL COMMENT 'Password',
  `Role` enum('READ ONLY','NO ACCESS','ADMIN','USER') COLLATE utf8_bin NOT NULL DEFAULT 'NO ACCESS' COMMENT 'Role-based permission for xataface',
  `DateCreated` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  `DateModified` datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY (`User_ID`),
  UNIQUE KEY `Username` (`Username`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Xataface Credentials';
CREATE TABLE `tbl_family` (
  `Family_ID` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  `FirstName` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'First Name',
  `LastName` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Last Name',
  `Birthday` date DEFAULT NULL COMMENT 'Birthday',
  `Filename` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'Filename of uploaded image',
  `Filename_mimetype` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'Type of file uploaded',
  `DateCreated` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  `DateModified` datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY (`Family_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Family Members';
CREATE TABLE `tbl_skill` (
  `Skill_ID` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  `Name` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Name of skill',
  `Value` int(11) DEFAULT NULL COMMENT 'Value of skill',
  `DateCreated` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  `DateModified` datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY (`Skill_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='List of Skills';
CREATE TABLE `tbl_familyskill` (
  `Family_ID` smallint(6) NOT NULL COMMENT 'Associated Family Member',
  `Skill_ID` smallint(6) NOT NULL COMMENT 'Associated Skill',
  PRIMARY KEY (`Family_ID`,`Skill_ID`),
  KEY `tbl_familyskill_ibfk_2` (`Skill_ID`),
  CONSTRAINT `tbl_familyskill_ibfk_1` FOREIGN KEY (`Family_ID`) REFERENCES `tbl_family` (`Family_ID`) ON DELETE CASCADE,
  CONSTRAINT `tbl_familyskill_ibfk_2` FOREIGN KEY (`Skill_ID`) REFERENCES `tbl_skill` (`Skill_ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Family Skills';
CREATE TABLE `tbl_asset` (
  `Asset_ID` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  `Box_ID` smallint(6) DEFAULT NULL COMMENT 'Related box if in storage',
  `Name` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'Name of asset',
  `Description` varchar(300) COLLATE utf8_bin DEFAULT NULL COMMENT 'Description',
  `Active` int(11) NOT NULL DEFAULT 1 COMMENT 'Active?  Still exist?',
  `DateCreated` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  `DateModified` datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY (`Asset_ID`),
  KEY `Box_ID` (`Box_ID`),
  CONSTRAINT `tbl_asset_ibfk_1` FOREIGN KEY (`Box_ID`) REFERENCES `tbl_box` (`Box_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Assets in and around the house';
CREATE TABLE `tbl_image` (
  `Image_ID` smallint(6) NOT NULL AUTO_INCREMENT COMMENT 'Index',
  `Asset_ID` smallint(6) DEFAULT NULL COMMENT 'Associated asset',
  `Name` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'Name of image',
  `Description` varchar(300) COLLATE utf8_bin DEFAULT NULL COMMENT 'Description',
  `Filename` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT 'Filename of uploaded image',
  `Filename_mimetype` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'Type of file uploaded',
  `DateCreated` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'Date record created',
  `DateModified` datetime DEFAULT NULL COMMENT 'Date record last modified',
  PRIMARY KEY (`Image_ID`),
  KEY `Asset_ID` (`Asset_ID`),
  CONSTRAINT `tbl_image_ibfk_1` FOREIGN KEY (`Asset_ID`) REFERENCES `tbl_asset` (`Asset_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Uploaded Images';

Some Example Data

Here are some example data to insert into the house database:

Code: Select all

INSERT INTO `tbl_user` VALUES (1,'admin',MD5('SetPasswordHere'),'ADMIN','2020-09-09 12:37:04',NULL),(2,'user',MD5('SetPasswordHere'),'USER','2020-09-09 12:37:04',NULL),(3,'viewer',MD5('SetPasswordHere'),'READ ONLY','2020-09-09 12:37:24',NULL),(4,'noaccess',MD5('SetPasswordHere'),'NO ACCESS','2020-09-09 12:37:24',NULL);

INSERT INTO `tbl_family` VALUES (1,'Jon','Doe','1971-09-08','doe-jon.png','image/png','2020-09-07 17:13:44',NULL),(2,'Jane','Doe','1976-09-08','doe-jane.png','image/png','2020-09-07 17:13:52',NULL),(3,'Billy-Jean','Doe','2006-09-08','doe-billy-jean.png','image/png','2020-09-07 17:14:05',NULL),(4,'Jack','Doe','2003-09-08','doe-jack.png','image/png','2020-09-07 17:14:13',NULL);

INSERT INTO `tbl_skill` VALUES (1,'Garbage Collection',1,'2020-09-07 17:08:47',NULL),(2,'Washing Dishes',2,'2020-09-07 17:09:18',NULL),(3,'Washing Clothes',3,'2020-09-07 17:09:30',NULL),(4,'Cooking',6,'2020-09-07 17:09:44',NULL),(5,'Dusting',1,'2020-09-07 17:10:03',NULL),(6,'Driving',3,'2020-09-07 17:10:20',NULL),(7,'Vehicle Mechanic',8,'2020-09-07 17:10:52',NULL),(8,'Plumbing Repair',8,'2020-09-07 17:11:25',NULL),(9,'Mowing Grass',4,'2020-09-07 17:11:44',NULL);

INSERT INTO `tbl_box` VALUES (1,'001',1,'2020-09-08 12:40:54','2020-09-08 07:40:54'),(2,'002',1,'2020-09-08 12:41:07','2020-09-08 07:41:07'),(3,'003',1,'2020-09-08 12:41:14','2020-09-08 07:41:14'),(4,'004',1,'2020-09-08 12:41:19','2020-09-08 07:41:19'),(5,'005',1,'2020-09-08 12:41:25','2020-09-08 07:41:25');

INSERT INTO `tbl_asset` VALUES (1,NULL,'XBOX','Original XBOX and assorted games.',NULL,NULL,1,'2020-09-09 01:47:58','2020-09-08 20:47:58'),(2,NULL,'Playstation','Original Playstation and assorted games.',NULL,NULL,1,'2020-09-09 01:48:25','2020-09-08 20:48:25'),(3,1,'Dreamcast','Dreatmcast and assorted games.',NULL,NULL,1,'2020-09-09 01:48:48','2020-09-08 20:48:48'),(4,2,'Wii','Wii, remote and assorted games.',NULL,NULL,1,'2020-09-09 01:49:32','2020-09-08 20:49:32');

INSERT INTO `tbl_image` VALUES (1,3,'Dreamcast','Dreamcast gaming console','console-dreamcast.jpg','image/jpeg','2020-09-09 02:05:35',NULL),(2,2,'Playstation','Playstation gaming console','console-original-playstation.jpg','image/jpeg','2020-09-09 02:05:56',NULL),(3,4,'Wii','Wii gaming console.','console-wii.jpg','image/jpeg','2020-09-09 02:06:08',NULL),(4,1,'XBOX','XBOX gaming console','console-original-xbox.jpg','image/jpeg','2020-09-09 02:06:23',NULL);

INSERT INTO `tbl_familyskill` VALUES (1,6),(1,7),(1,8),(1,9),(2,2),(2,3),(2,4),(2,5),(2,6),(3,2),(3,5),(4,1),(4,3),(4,9);

Xataface Download and Install

On the web server, download the latest stable code from the GitHub repository (which is 2.1.2 at the time of this writing):

Code: Select all

wget https://github.com/shannah/xataface/releases/download/2.1.2/xataface-2.1.2.tar.gz --output-document /tmp/xataface.tar.gz

Extract to your website folder, rename the folder and correct file ownership/permissions:

Code: Select all

tar -xzvf /tmp/xataface.tar.gz -C /var/www/fqdn/
cd /var/www/fqdn
mv xataface-2.1.2 xataface
/var/scripts/prod/xatafixperms.sh

In your web site configuration, make sure you prevent access to .ini files and that you cannot put the full path to your conf.ini into your browser and retrieve the contents which would expose your database credentials. In Apache, you could use the following directive to enable .htaccess:

Code: Select all

    <Directory /var/www/fqdn>
      AllowOverride All
    </Directory>

Or you could add the entries directly in the configuration file:

Code: Select all

<FilesMatch "\.ini$">
    <IfModule mod_authz_core.c>
        Require all denied
    </IfModule>
</FilesMatch>

[LHammonds]

Optional Extensions

Xataface functionality can be extended by creating or using modules.

Module Install

To install a module, download the archive and extract it. Move (and rename if necessary) the folder to <xataface folder>/modules/

If installing the ajax_upload module, make sure the folder is called "ajax_upload" and it's php file is located here: <xataface folder>/modules/ajax_upload/ajax_upload.php

Make sure the file/folder ownership/permissions are correct.

Enable the module by adding it to the conf.ini. Example:

/var/www/fqdn/house/conf.ini

Code: Select all

[_modules]
modules_g2=modules/g2/g2.php
modules_ajax_upload=modules/ajax_upload/ajax_upload.php
modules_depselect=modules/depselect/depselect.php

Take notice of the module line format:

modules_<MODULE-NAME>=<MODULE-NAME>/<MODULE-NAME>.php

depselect module.
sendgrid module.
calendar module.
Amazon S3 module.
htmlreports module.
reCAPTCHA module.
comments module.
duration_selector module.
ShoppingCart module.
checkbox_filters module.
pdfreports module.
Filemaker module.
ckeditor module. (FCKeditor and tiny_mce is built-in to the lib folder)
auth-ldap module.
auth-http module.
datepicker module. (jscalendar is built-in to the lib folder)
Email module.
tm module. (Translation Memory)
tagger module.
excel module.
ical module.
ajax_form module.
ajax_upload module.

[LHammonds]

Let's use the skeleton files to initially populate our application folder.

Code: Select all

mkdir -p /var/www/fqdn/house
cp /var/www/fqdn/xataface/site_skeleton/* /var/www/fqdn/house/.
cp /var/www/fqdn/xataface/site_skeleton/.htaccess /var/www/fqdn/house/.
chown --recursive www-data:root /var/www/fqdn/house
chmod 0755 /var/www/fqdn/house
chmod 0644 /var/www/fqdn/house/*
chmod 0664 /var/www/fqdn/house/.htaccess

Since this is not a Microsoft IIS server, we will not need the config file that is specific for it.

Code: Select all

rm /var/www/fqdn/house/Web.config

Now edit the main configuration to include the various configuration settings for the app. Keep in mind that there is very little you have to specify to get a working app but this is an example showing many settings to make it easy to reference things you might want.
/var/www/fqdn/house/conf.ini

Code: Select all

; File:   conf.ini
; Description:
; ------------
; This file contains configuration information for the dataface framework.
; Configuration options include such things as database connection information
; tables to be displayed in the menu, and behavioral and display characteristics
; of the dataface application (like whether to display the menu).

[_database]
driver = mysqli
host = "192.168.0.20"
user = "houseuser"
password = "houseuserpass"
name = "house"

[_auth]
users_table = tbl_user
username_column = Username
password_column = Password
allow_register = 0
session_timeout = 999999999

[_tables]
tbl_family = "Family"
tbl_user = "Users"
tbl_asset = "Assets"
tbl_skill = "Skills"
tbl_box = "Box"
tbl_image = "Images"

title="Household"
;default_browse_action = edit
default_browse_action = view

[history]
;History setting to enable auditing of data changes.
enabled = 0
;enabled = 1

[_prefs]
;; disable_master_detail prevents a split-window view.
disable_master_detail = 1
;; hide_record_view_logo will prevent images in records being displayed
;; like a logo.  This option disables the logo application-wide.
;hide_record_view_logo = 1

[_modules]
modules_g2=modules/g2/g2.php
;modules_depselect=modules/depselect/depselect.php
;modules_ckeditor=modules/ckeditor/ckeditor.php

Now edit the main web page so it knows where to find xataface. Also notice I added 3 debug lines at the top and if uncommented will allow errors to be visible on the web browser. This is only recommended during troubleshooting. You can get the hidden errors from the sites apache log.
/var/www/fqdn/house/index.php

Code: Select all

<?php
//ini_set('display_startup_errors', 1);
//ini_set('display_errors', 1);
//error_reporting(-1);
/**
 * File: index.php
 * Description:
 * -------------
 *
 * This is an entry file for this Dataface Application.  To use your application
 * simply point your web browser to this file.
 */
require_once '/var/www/fqdn/xataface/public-api.php';
df_init(__FILE__, '/xataface')->display();

INI to PHP

Another thing you can do to secure your settings is to convert the .ini files to .php in such a way that they can still be read by the INI import but not reveal anything if that URL was used directly. Here is how you can do that and separate the credentials into their own files:

/var/www/fqdn/house/conf.ini

Code: Select all

__include__="cfg-creds.php,cfg-other.php"

/var/www/fqdn/house/cfg-creds.php

Code: Select all

;<?php
;die();
;/*

; File: cfg-creds.php
; Description:
; ------------
; This file contains configuration information for database connectivity and web site user credentials.

[_database]
driver = mysqli
host = "192.168.0.20"
user = "houseuser"
password = "houseuserpass"
name = "house"

[_auth]
users_table = tbl_user
username_column = Username
password_column = Password
allow_register = 0
session_timeout = 999999999

;*/
;?>

/var/www/fqdn/house/cfg-other.php

Code: Select all

;<?php
;die();
;/*

[_tables]
tbl_family = "Family"
tbl_user = "Users"
tbl_asset = "Assets"
tbl_skill = "Skills"
tbl_box = "Box"
tbl_image = "Images"

title="Household"
;default_browse_action = edit
default_browse_action = view

[history]
;History setting to enable auditing of data changes.
enabled = 0
;enabled = 1

[_prefs]
;; disable_master_detail prevents a split-window view.
disable_master_detail = 1
;; hide_record_view_logo will prevent images in records being displayed
;; like a logo.  This option disables the logo application-wide.
;hide_record_view_logo = 1

[_modules]
modules_g2=modules/g2/g2.php
;modules_depselect=modules/depselect/depselect.php
;modules_ckeditor=modules/ckeditor/ckeditor.php

;*/
;?>

[LHammonds]

Web site structure

Now lets create the necessary files and folders to customize the interface that is presented.

Code: Select all

cd /var/www/fqdn/house
mkdir templates_c
mkdir -p tables/tbl_asset
mkdir -p tables/tbl_box
mkdir -p tables/tbl_family/Filename
mkdir -p tables/tbl_image/Filename
mkdir -p tables/tbl_skill
mkdir -p tables/tbl_user

Asset Table

/var/www/fqdn/house/tables/tbl_asset/fields.ini

Code: Select all

[__global__]
  visibility:browse = visible
  visibility:csv = visible
  visibility:find = visible
  visibility:list = visible
  visibility:update = hidden
[fieldgroup:Primary]
  section:order = 1
  order = 1
  label = "Primary Fields"
[fieldgroup:Secondary]
  section:order = 2
  order = 2
  label = "Secondary Fields"
[fieldgroup:Other]
  section:order = 3
  order = 3
  label = "Other Fields"
[Asset_ID]
  group = Other
  widget:type = static
  widget:label = "Index"
  visibility:find = hidden
[Name]
  group = Primary
  order = 1
  widget:label = "Name"
  widget:description = "Name of the box"
  validators:required = 1
  validators:required:message = "Name is required"
  widget:atts:size = 30
  widget:atts:maxlength = 30
  widget:focus = 1
[Box_ID]
  group = Primary
  order = 2
  widget:label = "Associated box"
  widget:type = select
  vocabulary = listBox
  visibility:browse = hidden
  visibility:find = hidden
  visibility:list = hidden
[Description]
  group = Primary
  order = 3
  widget:label = "Description"
  widget:description = "Description of the asset"
  widget:type = textarea
  widget:atts:size = 300
  widget:atts:maxlength = 300
  validators:required = 0
[Model]
  group = Primary
  order = 4
  widget:label = "Model"
  widget:description = "Model number"
  validators:required = 0
  widget:atts:size = 50
  widget:atts:maxlength = 50
[Serial]
  group = Primary
  order = 5
  widget:label = "Serial"
  widget:description = "Serial number"
  validators:required = 0
  widget:atts:size = 50
  widget:atts:maxlength = 50
[Active]
  group = Primary
  order = 6
  widget:label = "Active?"
  widget:description = "Is asset currently active or exist?"
  widget:type = checkbox
  vocabulary = IntegerYesNo
[DateCreated]
  group = Other
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  group = Other
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

/var/www/fqdn/house/tables/tbl_asset/relationships.ini

Code: Select all

[boxes]
tbl_box.Box_ID = "$Box_ID"
[images]
tbl_image.Asset_ID = "$Asset_ID"

/var/www/fqdn/house/tables/tbl_asset/tbl_asset.php

Code: Select all

<?php
class tables_tbl_asset {
  function valuelist__IntegerYesNo(){
    return array(
      0=>'No',
      1=>'Yes'
    );
  }
}
?>

/var/www/fqdn/house/tables/tbl_asset/valuelists.ini

Code: Select all

[listBox]
__sql__ = "SELECT Box_ID, Name FROM tbl_box WHERE Active = 1 ORDER BY Name ASC"

Box Table

/var/www/fqdn/house/tables/tbl_box/fields.ini

Code: Select all

[__global__]
  visibility:browse = visible
  visibility:csv = visible
  visibility:find = visible
  visibility:list = visible
  visibility:update = hidden
[fieldgroup:Primary]
  section:order = 1
  order = 1
  label = "Primary Fields"
[fieldgroup:Secondary]
  section:order = 2
  order = 2
  label = "Secondary Fields"
[fieldgroup:Other]
  section:order = 3
  order = 3
  label = "Other Fields"
[Box_ID]
  group = Other
  widget:type = static
  widget:label = "Index"
  visibility:find = hidden
[Name]
  group = Primary
  order = 1
  widget:label = "Name"
  widget:description = "Name of the box"
  validators:required = 1
  validators:required:message = "Name is required"
  widget:atts:size = 30
  widget:atts:maxlength = 30
  widget:focus = 1
[Active]
  group = Primary
  order = 2
  widget:label = "Active?"
  widget:description = "Is box currently active or exist?"
  widget:type = checkbox
  vocabulary = IntegerYesNo
[DateCreated]
  group = Other
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  group = Other
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

/var/www/fqdn/house/tables/tbl_box/relationships.ini

Code: Select all

[assets]
tbl_asset.Box_ID = "$Box_ID"

/var/www/fqdn/house/tables/tbl_box/tbl_box.php

Code: Select all

<?php
class tables_tbl_box {
  function valuelist__IntegerYesNo(){
    return array(
      0=>'No',
      1=>'Yes'
    );
  }
}
?>

Family Table

/var/www/fqdn/house/tables/tbl_family/fields.ini

Code: Select all

[__global__]
  visibility:browse = visible
  visibility:csv = visible
  visibility:find = visible
  visibility:list = visible
  visibility:update = hidden
[fieldgroup:Primary]
  section:order = 1
  order = 1
  label = "Primary Fields"
[fieldgroup:Secondary]
  section:order = 2
  order = 2
  label = "Secondary Fields"
[fieldgroup:Other]
  section:order = 3
  order = 3
  label = "Other Fields"
[Family_ID]
  group = Other
  widget:type = static
  widget:label = "Index"
  visibility:find = hidden
[FirstName]
  group = Primary
  order = 1
  widget:label = "First Name"
  widget:description = "First Name"
  validators:required = 1
  validators:required:message = "First Name is required"
  widget:atts:size = 30
  widget:atts:maxlength = 30
  widget:focus = 1
[LastName]
  group = Primary
  order = 2
  widget:label = "Last Name"
  widget:description = "Last Name"
  validators:required = 1
  validators:required:message = "Last Name is required"
  widget:atts:size = 30
  widget:atts:maxlength = 30
[Birthday]
  group = Primary
  order = 3
  widget:label = "Birthday"
  widget:type = calendar
  validators:required = 0
  visibility:browse = hidden
  visibility:list = hidden
[Filename]
; http://xataface.com/documentation/how-to/how-to-handle-file-uploads
; NOTE: Files uploaded with the same name as existing files will overwrite
;       the existing files.
  group = Primary
  order = 4
  widget:label = "Image"
  widget:description = "Browse to pick image. It will upload when record saved. If deleting record, remove the image first."
  Type = container
  widget:type = file
  allowed_extensions = png,jpg,jpeg,gif
; savepath can be used to save images to a different location.
;  savepath = /var/www/fqdn/house/uploads/images
; url is the path from the document root to the savepath.
;  url = /house/uploads/images
  validators:required = 0
  widget:atts:size = 100
  widget:atts:maxlength = 100
  visibility:list = hidden
[Filename_mimetype]
  widget:label = "mimetype"
  widget:type = hidden
  visibility:browse = hidden
  visibility:csv = hidden
  visibility:find = hidden
  visibility:list = hidden
  visibility:update = hidden
[FamilySkills]
  group = Primary
  order = 5
  widget:label = "Skills"
  widget:description = "Select skills related to this family member"
  widget:type = checkbox
  transient = 1
  relationship = skills
[DateCreated]
  group = Other
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  group = Other
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

/var/www/fqdn/house/tables/tbl_family/relationships.ini

Code: Select all

[skills]
tbl_skill.Skill_ID = tbl_familyskill.Skill_ID
tbl_familyskill.Family_ID = "$Family_ID"

/var/www/fqdn/house/tables/tbl_family/valuelists.ini

Code: Select all

[listBox]
__sql__ = "SELECT Box_ID, Name FROM tbl_box WHERE Active = 1 ORDER BY Name ASC"

[LHammonds]

Image Table

/var/www/fqdn/house/tables/tbl_image/fields.ini

Code: Select all

[__global__]
  visibility:browse = visible
  visibility:csv = visible
  visibility:find = visible
  visibility:list = visible
  visibility:update = hidden
[fieldgroup:Primary]
  section:order = 1
  order = 1
  label = "Primary Fields"
[fieldgroup:Secondary]
  section:order = 2
  order = 2
  label = "Secondary Fields"
[fieldgroup:Other]
  section:order = 3
  order = 3
  label = "Other Fields"
[Image_ID]
  group = Other
  widget:type = static
  widget:label = "Index"
  visibility:find = hidden
[Asset_ID]
  group = Primary
  order = 1
  widget:label = "Asset"
  widget:description = "Related asset, if applicable"
  validators:required = 0
  widget:type = select
  vocabulary = listAssets
  visibility:find = hidden
[Name]
  group = Primary
  order = 2
  widget:label = "Name"
  widget:description = "Name of image"
  validators:required = 1
  validators:required:message = "Name is required"
  widget:atts:size = 50
  widget:atts:maxlength = 50
  widget:focus = 1
[Description]
  group = Primary
  order = 3
  widget:label = "Description"
  widget:description = "Description of image"
  validators:required = 0
  widget:type = textarea
  widget:atts:size = 300
  widget:atts:maxlength = 300
[Filename]
; http://xataface.com/documentation/how-to/how-to-handle-file-uploads
; NOTE: Files uploaded with the same name as existing files will overwrite
;       the existing files.
  group = Primary
  order = 4
  widget:label = "Image"
  widget:description = "Browse to pick image. It will upload when record saved. If deleting record, remove the image first."
  Type = container
; NOTE: Only use ajax_upload if you have installed / enabled the module.
;  widget:type = ajax_upload
  widget:type = file
  allowed_extensions = png,jpg,jpeg,gif
; savepath can be used to save images to a different location.
;  savepath = /var/www/fqdn/house/uploads/images
; url is the path from the document root to the savepath.
;  url = /house/uploads/images
  validators:required = 0
  widget:atts:size = 100
  widget:atts:maxlength = 100
[Filename_mimetype]
  widget:label = "mimetype"
  widget:type = hidden
  visibility:browse = hidden
  visibility:csv = hidden
  visibility:find = hidden
  visibility:list = hidden
  visibility:update = hidden
[DateCreated]
  group = Other
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  group = Other
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

/var/www/fqdn/house/tables/tbl_image/valuelists.ini

Code: Select all

[listAssets]
__sql__ = "SELECT Asset_ID, Name FROM tbl_asset WHERE Active = 1 ORDER BY Name ASC"

Skills Table

/var/www/fqdn/house/tables/tbl_skill/fields.ini

Code: Select all

[__global__]
  visibility:browse = visible
  visibility:csv = visible
  visibility:find = visible
  visibility:list = visible
  visibility:update = hidden
[fieldgroup:Primary]
  section:order = 1
  order = 1
  label = "Primary Fields"
[fieldgroup:Secondary]
  section:order = 2
  order = 2
  label = "Secondary Fields"
[fieldgroup:Other]
  section:order = 3
  order = 3
  label = "Other Fields"
[Skill_ID]
  group = Other
  widget:type = static
  widget:label = "Index"
  visibility:find = hidden
[Name]
  group = Primary
  order = 1
  widget:label = "Name"
  widget:description = "Name of the skill"
  validators:required = 1
  validators:required:message = "Name is required"
  widget:atts:size = 30
  widget:atts:maxlength = 30
  widget:focus = 1
[Value]
  group = Primary
  order = 2
  widget:label = "Value"
  widget:description = "Relative value unit (numeric), 1 = little work, 10 = max effort"
  validators:required = 1
  widget:type = select
  vocabulary = listValues
[DateCreated]
  group = Other
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  group = Other
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

/var/www/fqdn/house/tables/tbl_skill/valuelists.ini

Code: Select all

[listValues]
1 = 1
2 = 2
3 = 3
4 = 4
5 = 5
6 = 6
7 = 7
8 = 8
9 = 9
10 = 10

User Table

/var/www/fqdn/house/tables/tbl_user/fields.ini

Code: Select all

[__global__]
  visibility:browse = visible
  visibility:csv = visible
  visibility:find = visible
  visibility:list = visible
  visibility:update = hidden
[User_ID]
  widget:type = static
  widget:label = "Index"
  visibility:browse = hidden
  visibility:csv = hidden
  visibility:find = hidden
  visibility:list = hidden
  visibility:update = hidden
[Username]
  widget:label = "Username"
  widget:description = "Username for Xataface Login"
  widget:atts:size = 30
  widget:atts:maxlength = 30
  validators:required = 1
  validators:required:message = "Username is required"
[Password]
  encryption = md5
  widget:label = "Password"
  widget:description = "Stored password is encrypted. If editing, you have to re-type it"
  widget:type = password
  widget:atts:size = 30
  widget:atts:maxlength = 30
  validators:required = 1
  validators:required:message = "Password is required"
  visibility:browse = hidden
  visibility:update = hidden
  visibility:find = hidden
  visibility:list = hidden
  visibility:csv = hidden
; Notice the lack of the [Role] field.  It pulls it
; correctly straight from the database schema.
[DateCreated]
  date_format = %Y-%m-%d
  widget:type = static
  timestamp = insert
[DateModified]
  date_format = %Y-%m-%d
  timestamp = update
  widget:type = static

[LHammonds]

Visual Customization

Reference Articles:

• Getting Started: Changing the look and feel
• Xataface Customization Tutorial

Custom Logo

Let's create a logo image and name it "logo.png" and in this example, the logo happens to be 183 pixels wide by 50 pixels high.

Now modify (or create) an Application Delegate file here: /var/www/fqdn/house/conf/ApplicationDelegate.php

Modify it to add a custom CSS stylesheet in the root of your application folder.

Code: Select all

<?php
class conf_ApplicationDelegate {
  public function beforeHandleRequest(){
    Dataface_Application::getInstance()
      ->addHeadContent(
        sprintf('<link rel="stylesheet" type="text/css" href="%s"/>',
          htmlspecialchars(DATAFACE_SITE_URL.'/style.css')
        )
      );
  }
}
?>

Now create the CSS stylesheet we referenced above and include the width and height dimensions:

/var/www/fqdn/house/style.css

Code: Select all

#xf-logo {
  background-image: url(images/logo.png);
  background-repeat: no-repeat;
  width: 183px;
  height: 50px;
  padding: 0;
  margin:0;
}

.login-prompt-wrapper {
  width: 80%;
  border: 5px solid #b3b7cb;
  margin: 3em auto;
  padding: 30px;
  border-radius: 30px;
  background-image: url(images/logo.png);
  background-repeat: no-repeat;
  background-position: bottom right;
}

The relative paths mentioned in the stylesheet is relative to the stylesheet's location. In this case, it is in the root of the application folder. We are referencing an "image" folder which means it needs to be a sub-folder where the stylesheet is located. Create the images folder in your app directory. Example: /var/www/fqdn/house/images/logo.png

Upload the logo.png file to the images folder.

[LHammonds]

Web Error Log

The web error log will typically hold all the information you need to track down problems.

On Ubuntu using Apache, the log file will be located here: /var/log/apache2/fqdn-error.log

To see the errors displayed live while browsing, use the tail command to view the end of the log but keep it continually open...such as:

Code: Select all

tail -f /var/log/apache2/fqdn-error.log

Browser Error Output

If you find it easier to have the web errors displayed on your browser, you can enable these settings in your index.php file but it is not a good idea exposing this debug information on a production environment:

Code: Select all

ini_set('display_startup_errors', 1);
ini_set('display_errors', 1);
error_reporting(-1);

You can disable it by turning the lines into comments by adding two forward slashes such as the following:

Code: Select all

//ini_set('display_startup_errors', 1);
//ini_set('display_errors', 1);
//error_reporting(-1);

General Query Log

The General Query Log can be enabled on MySQL/MariaDB to capture all SQL commands but make sure you disable it soon after enabling it. You do not want to leave it running continuously or it will fill up your drive and cause degraded performance until it no longer responds due to a full drive.

Log into your MySQL/MariaDB console as root.

Check the current settings:

Code: Select all

SHOW VARIABLES LIKE "general_log%";

+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| general_log      | OFF             |
| general_log_file | srv-mariadb.log |
+------------------+-----------------+

Turn on the log:

Code: Select all

SET global general_log = ON;

Perform your test actions on your website.

Now turn off the log:

Code: Select all

SET global general_log = OFF;

Since the above example did not specify a full path, the log will be with the database files.
On Ubuntu Server, that will like be /var/lib/mysql/

So for this example, the log would be /var/lib/mysql/srv-mariadb.log

When done reviewing it, you can safely delete the log file.