| Guide>>Introduction>>The what and why of Xt |
|---|
| Xt is a web app that that was developed to "scratch an itch".
There are numerous MariaDB clients available. They all seem to use similar methods for viewing/editing/creating/deleting records in a database. I've used many of them over the years. Unfortunately, this was not the way I wanted to look at my databases. In the beginning, likely early 2000, I started a web UI I called webquery. It was my attempt to duplicate what I had been using, but making it create results the way I wanted to see them. Many years into the development of webquery I decided that a totally different approach was needed. I thought long and hard about what I wanted before I began again from scratch. I ended up with a web UI I called 3t. 3t is a python program that manipulates databases with 3 tables . The databases and tables had to have a specific definition at the core, but could differentiate from there. It certainly wasn't about to compete with the Internet Movie Database, nor was that ever the idea. I spent another decade adding features and removing bugs. Then, finally, I had something I liked using. Xt started out as a fork of 3t. 3t is limited, but simple. Xt is more robust, but also more complicated. Xt means that it can use X number of tables. Not referring to the Roman Numeral X, but to the variable x. |
| Guide>>Introduction>>Features and limitations |
| The number one feature to implement was simplicity. Unfortunately, with simplicity comes limitation. Xt employs one primary data table that can support unlimited related data tables and several built-in system tables (documentation, locale and configuration tables). In addition, all the data tables enable automatic use of unlimited support tables. Hopefully, Xt hits the best balance between simplicity and completeness.
In theory it should handle any media format browsers can display. In practice the tested ones are only a small fraction of the formats that exist. It's written mostly in python and html, making it easily hacked by novice and expert developers alike. |
| Guide>>Introduction>>How to use the interface |
| Xt offers three selection constraints.
1) Limit the results to a specific item. 2) Limit the results to a specific category. 3) Limit the results to a specific search term. For example: In the item pull down selector, select All Items. In the category pull down select All Categories. Leave the search entry empty. The results will include all records in the data table. There is a shortcut button to the right of the select list that will do the same thing, Limit the results to only those records belonging to a single category by selecting that Category. If a specific Item is selected then the result would only contain data related to that item and belonging to the selected category. Accordingly, any combination of these three (item,category,search) can be used to find the information. They limit the result to information that meets the selected criteria. There is more detail on each of these selection options in the following sections of this guide. |
| Guide>>Introduction>>Customize the Look |
| Only logged in users can change the setting.
The system user's configuration must be done manually by an administrator. Across the top of the screen are three images, all of which can be customized. The color scheme and the navigation icons can also be customized. Changing these settings will not affect any user data. To edit the configuration: Select the far right image for the settings screen. Just below the top three images is a header bar that has four icons on the left. The first icon is to edit the _config table. The second icon is to edit the _category table The third icon is to edit the default images and the fourth icon is to edit the _locale table. Select edit configuration icon: A table of selections will appear. For now lets focus on the appearance selections at the top. The first four selections determine what image is shown in the far right panel, what title will show in that area, where that title will be placed and what image will be used as a background for the various popup dialogs. The top selection is the only write-in selection, the next three are pull down lists to select from. Scroll down to the bottom of the configuration panel, to the row titled 'Theme'. Use the selector to select a theme, the program comes with three themes, the default theme was the one loaded for first start. Select the update button at the right side of the header bar to enable and load the selected theme. Select edit categories icon: Categories can be added or deleted. For now lets edit the 'All_Categories' image, since the one that is there at first startup is a default image. Keep in mind that these images don't need to be big, large images will just slow down the UI. A good size for a Category image is 180x120, so make some available on the local storage. To edit the All_Categories image click on the edit button on the far left of the row. "All_Categories" is a special category, it can't be edited/deleted. It will be auto-generated. Select the browse button (file selector), pick a file and select the update button to finish. The uploaded image will be shown in the first two panels at the top when All is selected. Select edit images icon: Selecting the third icon will show a list of images that are available when picking an image in the configuration editor. Initially, the images seen are the default images stored under the images directory of Xt. The images in this list can be changed by clicking on the Edit icon on the left side. Pick any image that doesn't exceed the size limitation (shown in tool tip). Click the Update button in the header. This UI doesn't allow the addition of images, only updating them. The images shown are stored in the source code under images/defaults. There are sub-dirs named for the current database that will store user changes. This way the selectable images can be database specific. The defaults can always be reloaded. Select edit locale icon: Use the third icon to display the locale table, which can provide other languages for the Xt UI. It's a table of translatable words and phrases used in the Xt UI. The first column from the left is a menu containing all enabled locales. Next is the US English (en_US) strings, which is not editable. The right column is an example column, there at first startup to show the user what to expect. To enable more/less locales select the Add icon at the right side the header bar. Then select the locales to appear in the enabled locales menu. There will be over 100 locales to choose from. Update the menu list to reflect the selected locales by selecting the update button on the right side of the header bar. The new locale menu will show all locales that have been enabled. Select one to edit. Click on the edit button (left side of the header). Edit all the strings for the selected locale. When finished select the update button on the right side of the header bar to save the edits. The newly translated locale will now be available in the configuration menu. |
| Guide>>Introduction>>Tech Manaul |
| The purpose of the Xt is to facilitate the storage of information and to make retrieving any part of that information easy.
The Tech Manual is meant to have information that pertains to the inner workings of Xt such as sql table definitions, software requirements, administration and rules to follow for a successful deployment. The Tech Manual can be accessed via the builtin documentation viewer by selecting the far right image at the top (Settings & Help) and then selecting the document title (far left under the header bar) to toggle between the User Guide and Tech Manual. It can also be accessed from the HTML documentation file under the doc directory of the source code. That HTML file can be downloaded/displayed via the UI by selecting the far left link of the tool bar (bottom bar) of the UI (while viewing the Settings & Help screen). |
| Guide>>Program Views>>Top Section |
| Post installation, the default view will be presented. The screen is divided into three sections: top, middle, and bottom.
The top section is dedicated to selecting information from the selectec DB through a series of selectable limits. From left to right these limiters are: select an item or all items, select a category or all categories and enter text to search for. Using a limiter or combination of limiters will determine what information will be found and displayed. At the top left will be the user controls for Item selection. Selecting the image will open the Item information table related to that image. The up/down icons to the right of the image index the current Item accordingly. Below the image is a drop down selection list containing a list of all the Items. Select an Item to view. The next area to the right is used to select a category. All operations are analogous to Item selection. The area located in the upper right has multiple functions. I'll come back to the that area, for now let's review the search dialog. The simplest use is to type some text into the input field and press the return key. Optionally, clicking on the search icon will perform the search. This will limit the current result set to a subset that contains the text entered. Selecting an Item limits the results to information related to that Item. Next, selecting a category further limits the results to information related to the selected Item that belong to the selected category. Search for a word and the results are further limited to those records that contain that word. By default the search searches the data tables using full-text searching. Among other features this kind of search allows the use of charaters like *, ? and others in the search string. Alternatively, by unchecking the checkbox to the left of the search input field the search becomes a %like% search. Special characters are treated like any other text, but it's a faster search and sometimes can produce more results. The area above the search input has two functions. 1) It is used to display an image. Maybe use an image that represents the information in the DB. A title can be overlaid on top of this image. 2) The image is also a link to the configuration and documentation page. |
| Guide>>Program Views>>Middle Section |
| The largest section of the program window is the middle section. This section is primarily used to display the results that are returned after a selection and/or search has been made. There are three distinctly different views: Item view, Category view and Media view. Each view will have a header, just below the top view, to display tools or data unique to that view.
ITEM VIEW The Item view header will have three icons. At the left will be the Edit and Delete icons. At the right will be the Add or View icon, depending on whether or not there are existing media records related to that Item. All these icons may be ghosted depending on user privileges. All users are allowed to see related media by selecting the View icon (if available) at the left side of the header. However, regardless of privileges, an Item record that has media related to it cannot be deleted until all the related media records have been deleted. The main Item view is a two column table, populated by the editable columns defined in the Item table. Any amount of text information is allowed. In addtion an image representing the Item, to be used in the top section, is selectable in the Item create dialog. The result table will represent the data for a single selected Item. When the All Items is selected the result will be a table containing one Item per row. The columns displayed are configurable. In the All Item display mode there is an extra column on each side of the table. The left hand column will have an icon that will link to the main Item display. The right hand column will have an icon that will link to any media records that are related to that Item (if available). If the user is logged in and there are no related media records, then the right hand column will have an icon linked to the create Media dialog. An Item sort column is configurable, defaulting to the Item table primary column. CATEGORY VIEW Once a Category has been selected, the matching Category data will be displayed. The results are presented in table format. The column names are embedded in the header and depend on the Category table definitions. Columns that are common to all Category tables will be used when All Category is selected. The header columns will display a sort icon for columns that are sorted. In the Category display mode there is an extra column on each side of the table. The left side column will have an icon that links to the main Category record. This view is the same as the single Item view described above except that it contains Category data. All related records will have to be deleted before a data record can be deleted. The right most column will have one of two icons depending on whether or not the record has any related media. An Add icon or a View icon. When the record doesn't have any related media an Add icon is there when a user is logged in (ghosted if not logged in). If it does have related media then an icon linked to the related media will be there. All users are allowed to view media records, no login required. A Category sort column is configurable, defaulting to the Category table primary column. When available, the special CATEGORY column will be sorted first, before the configured Category sort column. SEARCH VIEW Search result columns are dependent the Item and/or Category selected. * When an individual Item/Category is selected, any search is irrelevant . * When an All Item is selected and no Category is selected, only Item columns are displayed in the result table. * When an individual Item is selected and a single Category is selected, only Category columns are displayed in the result table. * When an individual Item is selected and a All Category is selected only Category columns are displayed in the result table. * When All Items and All Categories are selected the result table will be a combination of Item columns on the left and Category columns on the right. The special column CATEGORY will separate these two groups of columns. An Item sort column is configurable, defaulting to the Item table primary column. A Category sort column is configurable, defaulting to the Category table primary column. When available, the special CATEGORY column will be sorted first, before the configured sort column. |
| Guide>>Program Views>>Bottom Section |
| At the very bottom of the program window is the toolbar.
A status message is printed on the left end which doubles as a export link when appropriate data is being displayed. A contact link (configurable) is located at the center of the toolbar. At the far right is a login/logout link. Login using a properly configured MariaDB user account. The status message area will display how many rows the result table contains. The contact link will open an email client in accordance with the system configuration. The login/logout link will display the name of the person who is logged in and will provide a login link when no one is logged in. |
| Guide>>Program Views>>Media |
| When viewing a media record the result section is slightly different than other views.
Like other views there are edit and delete icons on the left. Unlike other views media records can not have records related to them, so the lack of user privileges is the only reason for the delete icon to be ghosted. Media results are not displayed in a typical table format. There are three areas. The left most area is a vertical list of short text fields. The right most area is for an image or filetype icon, if the record has one. The center area is for text, a multi-line or multi-paragraph entry. If no image is present this text area will expand to the right border. In the header, just above the result section, there will be a text reference referring to the record that the media is related to. At the far right is the Add icon, which will allow additional media records to be created (privileges required). When adding a new media record there will be a tooltip for the file selector that will state the size limit for that column. This has nothing to do with the file size allowed by the network or any other restrictions that may apply. The size limit is the maximum size of the MariaDB blob column used. Be aware that large files can be stored, but that they may also reduce the responsiveness of the program. |
| Guide>>Entering Data>>Requirements |
| It's all very well to be able to read descriptions and view media, but being able to enter data will require some extra steps. First, to edit/create data, any user needs update and insert MariaDB privileges for the current database/table.
By default all users are given select privileges on the Xt data tables (read only) without having to login. If a user has a login then that user should have select, update, delete and insert privileges for the current database/table (a MariaDB admin account would be needed to assign the required privileges). With these privileges a user can edit/create/delete records in the following tables. item table cat tables media tables support tables _locale table _category table _config table |
| Guide>>Entering Data>>The edit form |
|
The entry form for all the tables is the same. The header section will have two buttons, one at each side. At the left side will always be a Cancel button. If it's a new record an Insert button will occupy the right side. If editing an existing record it's an Update button. The appearance of these buttons will depend on the theme that has be configured. Cancel Button: will exit the edit form leaving the record unchanged. Insert Button: will exit the edit form and create a new record in the table being edited using the information entered. In the event that an error occurs a notification will appear. Update Button: will exit the edit form and update the record in the table being edited using the information entered. In the event that an error occurs a notification will appear. |
| Guide>>Entering Data>>Support tables |
| In order to view/edit Support tables go to the Settings screen. On the far right hand side of the header is the Support table selection menu. It will list all support tables that are configured and designed correctly. Select the table to view/edit.
All Support tables have similar sql definitions. The support tables are used to provide the Xt UI with selection lists for specific columns in other tables. The have to be configured and designed according to a set of rules which are explained in depth in a later chapter. |
| Guide>>Entering Data>>Date column considerations |
| Date columns require specific values.
MariaDB only accepts the date as YYYY-MM-DY. There is some flexibility as to how that date can be entered, but not much. Accepted values are: 2015-06-15, using any delimiter like 2015.6.15, or no delimiter like 20150615. The year can be shortened to 2 digits and MariaDB will decide the century like this: Year values in the range 00-69 are converted to 2000-2069. Year values in the range 70-99 are converted to 1970-1999. Entering an invalid date MariaDB will save the date as 0000-00-00. From Xt ver 10.0 onward date fields are configured to use the standard HTML date selector. |
| Guide>>Configuration>>Change how Xt shows data |
| If the user has login privileges that user can configure several aspects of what and how Xt displays information using the configuration dialog.
Access the configuration dialog via the top right image. Selecting that image will open the Configuration and Help panels. The configuration dialog is opened by selecting the far left icon in the header bar. The settings in the configuration dialog will not change any data in the data tables, only the _config table will be affected. These settings will only determine how and what information is displayed. Most of the settings are obvious personal preferences. Images have to be in the relevant directories in order to be listed for selection. Multi-selectors are available for selecting multiple column names when that makes sense. These settings are not mandatory, but are for the user to use to tweak the appearance of the program UI and what information is displayed. Program specific values are detailed in subsequent pages and more details can be had in the Tech Manual. To reset the configuration to it's defaults delete the record from the _config table and the program will auto-generate the configuration settings using the hard coded defaults. |
| Guide>>Configuration>>Selecting itemListColumns |
| There is a configuration row that allows the user to select multiple item columns, whose data will comprise the Item selection list.
This selection list is most useful when it's members are unique. As an example let's say the Item list is a list of peoples names. If they are all friends then maybe the list need only have their first names. If there is more than one Sue then select another column, one that will make each Sue unique. Select as many columns as it takes to make all list members unique, while keeping the number of columns selected to a minimum. |
| Guide>>Configuration>>Selecting the itemColumns |
| There is a configuration row that allows the user to select multiple itemTable columns whose data will comprise the all items result table. Select as many columns as is useable, keeping in mind that selecting too many may make them harder to read. |
| Guide>>Configuration>>Selecting the catSortColumn |
| The list of column names to select from will be a list of columns that all category tables share. Consequently, depending on the table configurations the list may be a little short. Because all category tables will be sorted by the same column that column must be shared by all tables.
It's nice to have a date column in all category tables to use as a catSortColumn. If not catSortColumn is selected the fallback is the primary column. |
| Guide>>Configuration>>Selecting the itemSortColumn |
| The column names that are pickable reflect the item table column names that are sortable. If none are, or if a column is not selected then the item table primary column will be the default itemSortColumn. |
| Guide>>Configuration>>The Configuration table |
|
Use the configuration table to configure several aspects of what and how Xt displays information. The settings in the configuration dialog will not change any data in the data tables, no information will be removed or added. These settings will only determine how and what information is displayed. Most of the settings are obvious personal preferences. Images have to be in the relevant directories in order to be listed for selection. Multi selectors are available for selecting multiple column names when that make sense. These settings are not mandatory, but are for the user to use to tweak the appearance of the program UI and response. Program values are detailed in subsequent pages and more details can be found in the Tech Manual. |
| Guide>>Configuration>>The _locale table |
| This table will contain all the strings from the Xt UI in english in the column 'en_US'. The 'en_US' column can not be edited using the Xt UI. There are over 100 locale names offered in the _locale table.
Translations can be added from the _locale table view. If no other languages have previously been enabled, an example locale will be displayed in the left side menu, to show the user what to expect. An Add icon is on the right side of the header bar. Selecting this icon will display a list of all the locales. Multiple locales can be selected. The locales selected will be available for editing from the _locale table view page. They will be in the left side menu, select one to edit it. From the _locale table view, select a locale from the left side menu to view. To edit the translations select the Edit button on the left side of the header bar while viewing the locale to be edited. When finished select the update button. All the locales listed in the left side locale menu will be included in the configuration locale selection, to use a locale select it from the list and update the configuration. Xt has the en_US words statically located in the program code and will fall back to these if the _locale table is not available or fails to load. Consequently, the _locale table does not have to be present to run Xt. |
| Guide>>Documentation>>Documentation |
|
There are three parts to the Documentation. The Tech Manual and the User's Guide are two. The third is an HTML page, it has all the same information. The html version of this documentation is available in the doc directory and can be downloaded from a link in the bottom left corner of the settings/help page. There's lot more. There is an examples folder in the source directory that contains the code to create complete example databases. The source code itself is a wealth of information. There are many commented functions in the source code that give more detailed information. |
| Guide>>Documentation>>The User Guide |
| The purpose of the Xt is to facilitate the storage of information and to make retrieving any part of that information easy.
The User Guide is meant to have information that pertains to using the Xt UI. It explains what the three main areas of the UI are for and how they can be used. It shows what the various buttons, selection lists and links can do. The User Guide can be accessed via the builtin documentation viewer by selecting the far right image at the top (Settings & Help). From there select the document title (far left, under the header bar) to toggle between the User Guide and the Tech Manual. It can also be accessed from the HTML documentation file under the doc directory of the source code. That HTML file can be downloaded/displayed via the UI by selecting the far left link of the tool bar (bottom bar) of the UI (while viewing the Settings & Help screen). |
| Guide>>Documentation>>The Tech Manual |
| The purpose of the Xt is to facilitate the storage of information and to make retrieving any part of that information easy.
The Tech Manual is meant to have information that pertains to the inner workings of Xt such as sql table definitions, software requirements, administration and rules to follow for a successful deployment. The Tech Manual can be accessed via the builtin documentation viewer by selecting the far right image at the top (Settings & Help) and then selecting the document title (far left under the header bar) to toggle between the User Guide and Tech Manual. It can also be accessed from the HTML documentation file under the doc directory of the source code. That HTML file can be downloaded/displayed via the UI by selecting the far left link of the tool bar (bottom bar) of the UI (while viewing the Settings & Help screen). |
| Guide>>Licenses>>Xt license |
| Author: Gary M Witscher
License: Free (as in beer and chicken) W3C Markup Validation: HTML5 W3C CSS Validation: CSS3 |
| Guide>>Licenses>>Other software |
| Mod_Python
Version: >=3.5 License: Apache license 2.0 Url: http://modpython.org ======================= mariadb-connector-python Version: > 1.0.7 License: GPL 2.0 https://mariadb.com/docs/connect/programming-languages/python/install/ ======================= Apache Web Server Version: 2.4 License: Apache license 2.0 Url: https://httpd/apache.org ======================= Python Version: > 3.5 License: GPL Url: https://python.org ======================= URLs are current as of this writing: 2022-08-03. Debian bookworm includes a working mod-python package. |
| Manual>>Introduction>>About the Tech Manual |
| This Tech Manual is focused on the technical aspects of Xt. These are the aspects of Xt that the user can't see, like the logical structure behind the UI, the MariaDB table definitions, the python modules and function libraries, administrative tasks, theme creation and other related subjects. Someone will need to attend to these matters in order to take advantage of the many features that Xt offers. |
| Manual>>Introduction>>Xt Overview |
| Xt can be broken down into four main parts.
1) Python Python makes it all happen, it is the core of Xt. Xt is a python program which collects data, stores it in a MariaDB database and displays it in a browser. 2) MariaDB MariaDB is the backend storage for Xt data. Mariadb-connector-python enables python to send sql to the MariaDB sever. 3) HTML Xt displays it's UI and it's data in a standard web browser using HTML5. 4) Mod-python Mod_python allows python code to be used inside HTML. That code is used to manipulate variables and branch code execution based on their values. |
| Manual>>Introduction>>Directory tree |
|
dir/file structure rootdir (apache DocumentRoot) __ catimages ____ subdir for each db ______ category images __ doc ____ subdir for examples ____ changelog ____ doc_en_US.html __ images ____ subdir for default images ______ default static ui images ____ subdir for each db ______ db specific ui images ____ default.png __ Itemimages ____ subdir for each db ______ image for each item __ js ____doc.js __ log ____ error logs __ style ____ css files __ templates ____ html templates __ themes ____ subdir for each css file except base.css ______ subdir for css images ______ subdir for fileType images ______ theme images __ tmp ____ various temporary files __ INSTALL.TXT __ UPGRADING.TXT __ db.py __ index.py __ kooky2.py __ myFunctions.py __ html5genPy3.py __ testValue.py |
| Manual>>Introduction>>Initial Flow |
| From first startup, initial install.
The dbname and sqlHoat are determined either via the apache.conf or url. A browser cookie is created. Database connection is checked. Config is created and added to the _kooky localeStrings is created and added to _kooky. catImages are provided defaults, writen to disk and db The main screen is displayed showing the All items and All categories result table. |
| Manual>>Introduction>>User Guide |
| The purpose of the Xt is to facilitate the storage of information and to make retrieving any part of that information easy.
The User Guide is meant to have information that pertains to using the Xt UI. It explains what the three main areas of the UI are for and how they can be used. It shows what the various buttons, selection lists and links can do. The User Guide can be accessed via the builtin documentation viewer by selecting the far right image at the top (Settings & Help). From there select the document title (far left, under the header bar) to toggle between the User Guide and the Tech Manual. It can also be accessed from the HTML documentation file under the doc directory of the source code. That HTML file can be downloaded/displayed via the UI by selecting the far left link of the tool bar (bottom bar) of the UI (while viewing the Settings & Help screen). |
| Manual>>Requirements>>Software |
| Xt is a web app. Its' a server/client app that uses the Apache web server and MariaDB as the data backend. The two are glued together by a python script. The details of these components will follow, however in order to use Xt:
A running Apache server and the skills and permissions to configure it. The mod_python Apache module. A running MariaDB server and the skills to administrate it and privileges to design and create databases and tables. Python. Mod_python |
| Manual>>Requirements>>Hardware |
| Not much is required in the way of hardware, almost anything will run the software required. However, there are some limitations that might matter in some cases.
The first and most important is that Xt will not run on a shared hosting service. The reason for that is that I've never found any such services that include mod_python or mariadb-connector-python. Most are php orientated. Running Xt from a dedicated home based server works fine for local access. However, remote access can present some problems for the beginner as the Internet is not as open as one might think. Some internet service providers will block normal web access to home accounts (using port blocking). This can be circumvented by using non-standard ports or https (not typically blocked). As of this writing, Virtual Machines have become all the rage, Xt will run on a dedicated Virtual Machine. These services usually offer full root access to the VM, so all the required software is available. |
| Manual>>Quick Start>>Get me started NOW! |
| These steps assume command line experience and knowledge of
apache and mariadb use and configuration. Stated paths are specific to debian 10, to be adjusted for other installations. 1) Insure that the required software is installed and configured a) Python ver >3.5 b) MariaDB server ver >= 10.1 c) mariadb-connector-python >=1.0.7 d) Apache ver >= 2.4 (compatible with mod_python) e) mod_python ver >= 3.5 2) Download and install the archive from sourceforge a) extract the archive to the apache document root b) chdir to the new folder c) run the command to set required permissions d) chown www-data itemimages images catimages log tmp 3) Install the apache conf file a) chdir to doc/examles/apache b) copy the Xt-example-dbname.conf to /etc/apache/conf-available c) enable the new conf file d) edit the conf file to reflect the server path and port 4) Install the example database a) chdir to doc/examples/exampleDB b) execute the command to import the example DB c) cat *.sql | MariaDB -uroot -p 5) Done, visit the new installation at the configured location. Access to configuration and data editing is controlled through mariadb privileges. A privileged user has been pre-configured so that the various features can be explored and the configuration completed without delay (requires privileges). Below is a sequential list of actions that need to be accomplished before regular use is possible. Most icons and images have tips that will popup when the mouse is over them, to help understand what can be done. 6) Refer to the documentation for specific instructions on how to use Xt. |
| Manual>>MariaDB>>Table overview |
| This section is an introduction to table definitions.
Xt is a python script that uses mariadb-connector-python to manipulate MariaDB table data. Most of that work is done by the dosql function. Update, insert and delete queries are all handled there. The logged in user must have the required MariaDB privileges. If any errors occur a notification dialog will display in the center of the screen. Select queries are run throughout the script by any user with select privileges (by default the user named after the dbname) via the db.py module. There are five 'required' tables that DO NOT store application data. Their names begin with the underline character. _config _kooky _category _doc _locale These five tables are essential to the application. Their definitions should not be modified. The data that they contain can and most likely will be overwritten often. There are individual sections on each of those tables describing their definitions. There are data tables that store the application data. The first table is designed to store the 'item' data. Any number of secondary tables designed to store data related to the item table can be included. They are referred to as Category tables. In addition there must be a Media table for each of the Category tables and the Item table. Media tables are designed to store media related to either the Item table or Category table. An example might be: 1) an author table 2) a table containing the list of books related to each author 3) a media table containing reviews, pictures, audio, or videos related to the author or a book. 4) all data tables must have a FULLTEXT index containing all the char columns for that table. It is assumed: The Item table name will be the same as the dbname The Item table PRI column name will be _dbname Category tables PRI column name will be _cat Media table PRI column names will be _media The Media table name will be 'media_'+categoryTableName Full Text Index names will be tableName All columns will have a default valued defined or NULL More details on table definitions can be found throughout the documentation, specifically the Rules section. In addition there are examples of table definitions under the doc/examples directory. |
| Manual>>MariaDB>>_category table |
| The_category table has these columns:
_id (The primary column) user (owner of this configuration) category (The category name) media (The category image) filename (The last filename selected) Refer to the requiredTables directory under the doc/examples directory for detailed table definitions. The user column is new in rel 12.x. Every login user will have their own _category configuration. Every DB will have it's own _category table. The images stored in this table are written to disk when Xt is first loaded and after the table has been modified. Their location is: serverroot/xt/catimages/dbname/username (Where server root is the directory that apache is configured to serve web pages from.) (Where dbname is the name of the MariaDB DB that the images are from.) The _category.category values (category table name) will comprise the category selection list. Refer to the User Manual for a description of how the data in this table is used by the UI. |
| Manual>>MariaDB>>_config table |
| _config name/value pairs and descriptions
## The id of this record _id (auto created on first login) ## The owner of this configuration user (auto created on first login) ## Text to print on top of the logo image (default=dbname). displayname (any text) ## Location for the displayname (default=MIDDLE). displaynamelocation (Pick from list) ## Image to use as a logo, located in path2serverRoot/3t/images/dbname/ (default provided). displaylogo (Pick from list) ## Image to use as the popup background (default=displaylogo). popupbackground (Pick from List) ## Email contact address (default=root@localhost). emailcontact (any email address) ## Item table column names that will uniquely identify an item (default=first 2 char columns). itemListColumns (mulitPick from list) ## Item table column names that will comprise the All_Items table (default=first 4 char columns). itemColumns (multiPick from list) ## Category table column to sort results by (default=primary column) catSortColumn (Pick from list) ## Item table column to sort results by (default=primary column) itemSortColumn (Pick from list) ## On startup, display the last record entered (YES will enable, NO to disable). lastupdate (YES/NO) ## Select a theme from the list provided (default=default). theme (Pick from list) ## Preferred language. US english is the default (en_US) [only available when a _locale table installed] locale (Pick from a list) |
| Manual>>MariaDB>>_doc_en_US table |
| There can be multiple _doc_language_country tables.
Using the example name , format name each _doc table name according to the language it is written in. The _doc_*_* table has seven columns. Refer to the requiredTables directory under the doc/examples directory for the detailed table definition. _id: the primary column cno: (the index number for the named chapter) pno: (the index number for the named page) category: (either Manual or Guide) chapter: (the name/title of the chapter) page: (the name/title of the page) text: (the text for that page) The rows can be in any order in the table, just so the cno and pno columns are correctly numbered. When the rows are read by Xt it will order the results by cno and then pno ascending. They will also be separated into the two possible categories (User Guide and Tech Manual). Chapter 1 will be first in the list of chapters and page 1 will be first in the page list for that chapter. All chapter 1 rows should have the same chapter name. Any number of chapters is possible and any number of pages per chapter. As mentioned elsewhere in this documentation, there is an HTML version of this documentation in the doc directory. In addition, while using Xt one can download the latest version of the HTML documentation by selecting the EXPORT link at the lower left corner of the Settings page. |
| Manual>>MariaDB>>_locale table |
|
_locale table: _id tinyint(2) Auto Increment en_US varchar(100) [English - United States] ar_AE varchar(100) [Arabic - United Arab Emirates] ar_BH varchar(100) [Arabic - Bahrain] ... The _locale table has over 100 columns, each one a unique locale. The list of locales was created from the locales mariadb plugin: INSTALL SONAME 'locales'; SHOW LOCALES; The first column must always be en_US. The en_US column values are also hard coded in the getLocaleStrings function. If the _locale table doesn't exit (or fails to load) then the builtin locales are used. The _locale table column 'en_US' contains all the translatable strings from the Xt UI. The 'en_US' column can not be edited using the Xt UI. The first row of the _locale table is an exception, it contains either a 1 or 0 flag. When this flag=1 then that locale is enabled, otherwise that locale is disabled. The UI locale page is capable of editing/creating translatable strings. It can also enable/disable locales. Enabled locales appear in the locale UI list of editable locales. If no locales are enabled the UI will enable one locale as an example. Enabled locales appear in the configuration locale selection list. When the locale table is not available the configuration will not have a locale selection input. |
| Manual>>MariaDB>>_kooky table |
| The _kooky table has these columns:
Refer to the requiredTables directory under the doc/examples directory for the detailed table definition. _kookyID: (The primary column) remorteHost: (ip address of remote host) modstamp: (timestamp for each record) kookyData: (One record for each unique browser cookie.) The data in this table can be deleted at will. More will be generated when Xt is loaded. If Xt is displaying erroneous data after an upgrade deleting this data may fix the problem. This table is unrelated to any other table. It stores program data under a user specific id for reuse between page loads. |
| Manual>>MariaDB>>Support tables |
| There can be any number of SupportTables.
SupportTables are text only tables (except the _id is an integer column). SupportTables are meant to provide selection lists for columns in item, cat, or media tables. If a SupportTable has the same name as a column in one of the main tables then that column will present the contents of the SupportTable as a selection list. The selection list will by design contain a concatenation of the text from those SupportTable columns whose names end with a "_" (or if none do then the first text column as a fallback). The idea is to provide a unique selection. For example if the supportTable was a vendor table and there were several Big Stuff stores listed there, then use the name of the store and the city where it's located to have an uique selection. So, both of those column names would need to end in "_". A contanation of the SupportTable text columns will be used as a tooltip when mouseover is done on said column in the main table view. |
| Manual>>MariaDB>>Create required tables |
| There are five required tables that don't store any user generated data. They all start with an underline character (_). Each has it's own purpose which is explained further in a subsequent chapter.
If need be a fresh copy of all five can be imported directly into MariaDB from the doc/examples/requiredTables dir. No user data will be affected. The sql files found there show the table definitions for each of these tables. |
| Manual>>MariaDB>>The systemUser |
| There are example USER.SQL grant files under the doc/example directory that can be used as a template for creating a workable privilege configuration.
Xt requires a default user that has SELECT privileges for all db tables. This 'systemUser' is named after the database name: dbname='rivers', systemUser='rivers'. All SELECT queries will use this username. This user can not write or remove any data with one exception: the _kooky table. The _kooky table will need to be written to and updated by the systemUser. Grant the systemUser SELECT, UPDATE and INSERT privileges to the _kooky table (mysql.tables_piv). |
| Manual>>MariaDB>>Reserved Column Names |
| # the following are reserved names
# # GENERAL RULES # # all systemTable names start with a '_' # the primary column for all systemTables is '_id' # the primary column for the itemTable is '_'+dbname # the primary column for all catTables is '_cat' # the primary column for all supportTables is '_sup' # # SPECIFIC COLUMNS # # 'desc_' required by all tables except systemTables # to store a text description for each record # 'private' supported in media table (PUBLIC/PRIVATE) # to allow a user to store publicly sensitive data # 'date' required by all tables except systemTables and supportTables # to store the date when the record was created # 'filename' required by all tables having a blob column # to store the filename of the selected media file # 'media' required by all tables having a blob column # to store the media data # 'owner' required by all tables except systemTables and supportTables # to store the name of the user that created the record # 'modstamp' required by all tables except systemTables and suportTables # to store the data/time each record was last modified |
| Manual>>Advanced>>Off World |
| Xt AS A REMOTE CLIENT
To run the Xt scripts on server1.com and the mariadb on server2.com. As of rel 11.0-0, this requires that both machines be on the same network. Below is the minimum configuration elements that are needed to accomplish this connection. FILESYSTEM Xt uses LOAD_FILE to save media files to the database. Mariadb requires that the file to be loaded must be stored on the same server that mariadb is running on. As of rel 11 the way this is accomplished is to mount the remotePath on the machine running Xt. There are two configuration variables that control the required paths: remotePath and mountPoint. They are hard coded at the top of myFunctions.getConfig() as "/var/www/tmp/Xtloadfile/" and /mnt/www/tmp/Xtloadfile" APACHE Change sqlhost in Xt.conf to the remote-mariadb-server. For more than one remote-mariadb-server, configure a unique virtual server for each. MARIADB The db to be accessed must have privileges configured so that the client machine has the needed access to that db and it's tables. The database server could be configured like this: USE `MariaDB`; CREATE USER 'dbname'@'clientHost' IDENTIFIED BY 'dbname'; GRANT SELECT on `dbname`.* to 'dbname'@'clientHost'; GRANT SELECT, INSERT, UPDATE on `dbname`. `_kooky` to 'dbname'@'clientHost'; GRANT SELECT, INSERT, UPDATE on `dbname`. `_category` to 'dbname'@'clientHost'; GRANT SELECT, INSERT, UPDATE on `dbname`. `_config` to 'dbname'@'clientHost'; FLUSH PRIVILEGES; CREATE USER 'loginName'@'clientHost' IDENTIFIED BY 'loginPass'; GRANT FILE ON *.* TO 'loginName'@'clientHost'; GRANT SELECT, INSERT, UPDATE, DELETE on `dbname`.* to 'loginName'@'clientHost'; FLUSH PRIVILEGES; ====================== AT MINIMUM (only recommended for testing) ** on the remote mariadb server ** for any database system user ** for viewing records Set the mariadb user/host to % Set the mariadb db/user/host to % with select, insert and update privileges for the database to be accessed. ** on the remote mariadb server ** for any login user ** for viewing and editing records Set the mariadb user/host to % Set the mariadb db/user/host to % with select, insert, update, and delete privileges for the database to be accessed. |
| Manual>>Advanced>>Logs |
| xt has a log directory in it's root directory.
It is used to store log files that contain detailed information about queries that fail. This information can be used to help track down the source of the problem. Logs record errors by date, the newest will be at the top of the log. There can be three logs under the log directory because there are three functions that submit queries to the dbs: db.py - handles all select querys myFunctions.login - handles login attempts myFunctions.doSql - handles all update, insert and delete queries Each will have it's own log. They will only be created for queries that fail, so they may not exists. There is also a mechanism that shows a popup dialog if a query fails, which is no more than a notification. The log will give more details than the notification. The log directory must be writable by the web server user. The dosql.log and db.log have a flag variable (logAllQueries) that can be set to 1 or 0. If it's set to 1 then Xt will write all queries to the appropriate log file, pass or fail. The mareiadb.log will log all login attempts, pass or fail. |
| Manual>>Advanced>>Rules |
| # the following are reserved column names
# their names are used as is, everywhere # all systemTable names start with a '_' # the primary column for the itemTable is '_'+dbname # the primary column for all catTables is '_cat' # the primary column for all mediaTables is '_media' # the primary column for all supportTables is '_sup' # the primary column for all systemTables is '_id' # # 'desc_' required by all tables except systemTables (to store a text description for each record) # 'private' supported in media tables (PUBLIC/PRIVATE) (to allow a user to store publicly sensitive data) # 'date' required by all tables except systemTables and supportTables (to store the date when the record was created) # 'filename' required by all tables having a blob column (to store the filename of the selected media file) # 'media' required by all tables having a blob column (to store the media data) # 'cost' required by all media tables, a float column (to enable the summing of item/category costs) # 'owner' required by all tables except systemTables and supportTables (to store the name of the user that created the record) # 'modstamp' required by all tables except systemTables and suportTables (to store the data/time each record was last modified) All tables except systemTables and supportTables must have a FULLTEXT index (containing all char columns) in order to enable FULLTEXT search, which is the default search type in Xt. |
| Manual>>Advanced>>Using Xtp3 with Xtp2 databases |
| Use Xtp3 scripts to connect to a MariaDB database created for Xt-1.5 (or older)
The older database will need to be altered to be able to accommodate Xtp3. Numerous 'improvements' were made in Xtp3, providing more stability and a code base that is more easily maintained. Because of these changes any database that was created for Xt-1.5 scripts will require several changes. 1) import an updated _kooky table Xt-10.x requires the new definition, it won't write to the old one. 2) import an updated _locale table Xtp3 has changed how locales are handled 3) import an updated _doc table The _doc table was updated for python3 and the numerous added features. 4) remove 'zerofill' in all table definitions, MariaDB-python-connector fails on zerofill columns. 5) update item, media and _category tables read the section titled Rules, many table definitions have new requirements. . |
| Manual>>Advanced>>Private records |
| New in release 11.0-0
Keeping private information PRIVATE in the media table is now possible by using a column named 'private' in the media table. When the user creates a new media entry, if the media table has a 'private' column, 'PRIVATE' or 'PUBLIC' can be selected. Set the MariaDB default in the media_* table definition. To enable this functionality the media table must have a column named 'private'. This column uses a support table to generate a list of selections, also name 'private'. The values can be in any language, but 'PUBLIC' must have an _id=1 and 'PRIVATE' must have an _id=2. The recorded value will be determine by the _id column value. When A 'PRIVATE' media record is viewed by someone other than the owner the text and image parts will not be visible. |
| Manual>>Advanced>>Roles |
| Roles are an optional way to grant privileges.
# Remove all rows from the mysql.db table # that set privileges for 3t or Xt tables. # Do the same for the mysql.table_priv table. # Role privileges are used instead of these # individual table settings. # in 3t and Xt the 'system user' is given # the name of the database, here referred # to as 'db1' and 'user1'. # By definition a 'system user' can view all # public data, but cannot write/edit any data. # A 'Login User' can select,update,insert and # delete any data that they create/own. /*============================*/ /*CREATE ROLES */ /*============================*/ /* ONE ROLE PER PRIVILEGE */ create role if not exits del; create role if not exits ins; create role if not exits upd; create role if not exists sel; /* ROLE HIERARCHY */ grant ins to del; grant upd to ins; grant select to upd; /*============================*/ /* SYSTEM USER DEFINITIONS */ /*============================*/ /* grant 'ins' role to xtuser */ grant ins to 'xt'@'localhost'; grant sel to 'xt'@'localhost'; /*set the default role as the default user */ set default role ins for 'xt'@'localhost'; /* grant role:sel select priv on all xt tables */ grant select on xt.* to sel; /* grant FILE privileges globally */ grant file on *.* to ins; grant file on *.* to upd; /*grant 'siu' priv to role:ins for specific xt tables */ grant select,insert,update on xt._kooky to ins; /*grant select on specific mysql table */ grant select on mysql.roles_mapping to sel; /*============================*/ /* LOGIN USER DEFINITIONS */ /*============================*/ /*grant 'siud' to role:del on all xt tables*/ grant select,insert,update,delete on xt.* to del; /* grant del priv to a login user */ grant del to loginUser@localhost; /*set the default role for a login user*/ set default role 'del' for loginUser@localhost; ======================== MINIMUM GRANTS FOR SYSTEM USER grant ins to 'xt'@'localhost'; grant sel to 'xt'@'localhost'; grant select on xt.* to sel; set default role ins for 'xt'@'localhost'; grant select,insert,update on xt._kooky to ins; grant select,insert,update on xt._config to ins; grant select,insert,update on xt._category to ins; grant select on mysql.roles_mapping to sel; grant file on *.* to ins; grant file on *.* to upd; FLUSH PRIVILEGES; |
| Manual>>Relationships>>Tables talk to each other |
| Xt is written to facilitate access to a relational MariaDB database. Xt is dependent on the definition of the MariaDB tables. There are minimum required tables and columns and definitions that must exist in order for the designed features to function. The script itself will fail to execute if these minimum requirements are not met. Below is a summary of the various tables and how they relate.
For the sake of discussion lets assume we have a database that will track automotive maintenance. Most importantly then we will need a table that contains the make and model of the automobiles we want to track. In Xt this would be called the Item table. Any one vehicle will no doubt have numerous services preformed on it. Information such as date, cost and type of service might be desirable to record. A table to store this data is required and that table must relate to the Item table. In Xt this table is called the Category table. Some services are simple and wont benefit from lengthy enumeration. Other services involve multiple parts and procedures or even several repair shops. In order to accommodate this likelihood a table is needed that relates to the Category table. It should be a table that can store media as well as text. The Media table will need to store multiple records containing text, pictures, video and scanned documents. In Xt this table is the Media table. Any number of Category tables can be created and each must relate to the Item table. Each Category table must have a Media table that is related to it. The relational tree. Item table <----> Category table <-----> MediaTable ^ Media table This is the design that the Xt python script is built around. |
| Manual>>Relationships>>Special case columns |
| Owner column:
All tables, except system tables, must have a column named 'owner'. This column will contain the username for the user that created the record. When ever a user creates a record the user's name is automatically written to this column. The owner field controls who can edit/delete the record. By default, the creator of the record is the only user that will have these privileges. The edit/delete buttons will be ghosted out for other users. Summation column: There is a numeric column (cost) defined in all of the media tables. The sum of that column for all media records under the parent Cat record and for all Cat records under the parent Item record are displayed using a virtual column (sum). This is designed to do things like give a total cost spent for a specific Item or Category over time. Support table column: When one of the tables (excluding system tables) has a column name that is the same as a support table name, then that support table will be used to provide a select list when editing that column. This means that when a record containing one of these columns is edited, that column will display a pick list containing data from the support table. The data comprising that pick list will be determined by the first column in the support table. This column will be displayed in the pick list. The intent is to have unique selections. Values in the first column must be unique. Private table column: As of Xt v11 all Media tables must have a column named 'private' in order for the private feature to work. This column can have one of two values: PUBLIC/PRIVATE. If the private column is PUBLIC then that Media record is considered public and can be viewed by anyone. If the private column is PRIVATE then that Media column is considered private. When private, the text and media fields for that Media record are only visible to the owner. This column uses a support table name private. |
| Manual>>Relationships>>Re-naming columns. |
| When a column name is changed, Xt may refuse to start.
The problem is that the Xt configuration has set column names (saved from the last time it was configured) which may not exist now that some names have been changed outside of Xt. Use an sql client to erase the the row(s) in the _config table and the _kooky table. When Xt restarts it will automatically put valid values in the configuration table and initialize the _kooky table. This initial configuration will by necessity be stark. Use Xt to go over the configuration to add your preferences. Other startup problems are sometimes fixed by removing the _config row(s) and the _kooky rows(s). Both will be automatically generated with new valid data. |
| Manual>>Relationships>>Structure |
|
Main relationships between tables itemtable itemTableName must be the same as dbname must have 1st id col (int) named _itemTableName must have a col (blob) named media catTables catTableName can be any MariaDB friendly text must have 1st id col (int) named _cat must have 2nd col (int) named _itemTableName item mediaTables mediaTablename must be media_itemTablename must have 1st id col (int) named _media must have 2nd col (int) named _itemTablename category mediaTables mediaTablename must be media_catTablename must have 1st id col (int) named _media must have 2nd col (int) named _cat supportTables supportTableName must be named after a column in one of the above tables must have id col (int) named _sup must have 2nd col named supportTableName_ ======================= Make note of the "_" character in these names dbname, itemTableName, catTableName, mediaTableName and supportTableName are variable names, not literal _cat, _media and _sup are literal names, not variable names ======================= An example db (required columns): database='computer' itemTableName='computer' item ID column='_computer' item description='desc_' item image column='media' item owner='owner' item filename='filename' item modstamp='modstamp' item mediaTableName='media_computer' first media ID column='_media' next ID column='_computer' media description='desc_' media media='media' media filename='filename' media private='private' media owner='owner' media modstamp='modstamp' first catTableName='hardware' second cat ID column='_cat' next ID column='_computer' cat description='desc_' cat owner='owner' cat modstamp='modstamp' second catTableName='software' second cat ID column='_cat' next ID column='_computer' cat description='desc_' cat owner='owner cat modstamp='modstamp' first mediaTableName='media_hardware' first media ID column='_media' next media ID column='_cat' media description='desc_' media media='media' media filename='filename' media private='private' media owner='owner' media modstamp='modstamp' second mediaTableName='media_software' second media ID column='_media' next media ID column='_cat' media description='desc_' media media='media' media filename='filename' media private='private' media owner='owner' media modstamp='modstamp' SupportTables are not required, they can provide pick lists for columns in the tables above and tool tips for designated columns if available and correctly configured. first supporTablename='color' first support ID='_sup' next support col='color_' sysTables definitions are static for each version. there are table dumps of the table structures, in the doc/examples dir of the source archive. |
| Manual>>Relationships>>Divide and conquor |
| Categories are a central part of the Xt data scheme. Xt is based on the idea that data sets can are easier to understand when broken down into subsets of related data, Categories.
A common query would necessarily be to search for records in tables that are related to each other in some organizational way. They wouldn't have to share text, but something more general like function or definition. This type of search yields an overview that can be further focused using text matching. To me this is a normal way to approach the query process and so I've built this into Xt. Selecting a category in Xt queries the db table(s) named for that Category, only searches that Category table and it's related Media table. Consequently, only returning results related to that category name. (There is a '_category' system table (discussed elsewhere in this document) that stores the names of the Category tables and the images that represents them. All system tables start with a underline (_) and do not store user data.) |
| Manual>>Extras>>Modify how Xt looks |
| Themes consist of css color schemes and image sets.
Any file in the style directory that ends with .css and is not named base.css will be selectable as a theme in the configuration dialog. The base.css file contains css that applies to all themes and is always loaded first. Default images for buttons and css images are supplied for each .css theme name in the theme directory and are named after the css theme. To create a new css theme copy the contents of the default theme (default.css) to a new .css file under the style directory. Then change the backgrounds, images, colors and fonts in the new theme. Use the configuration dialog to enable the new theme. Be sure to refresh the page after changing any css file. To create navigation images read the list of image names in the themes/README file. Use these names and recommended sizes. There should be a directory named after each css theme file. Copy one of these directories to a new directory that has the same name as the new css file and it will be used for that theme. The names are hard coded into the various modules. Be sure to refresh the page after changing any of the images. To go deeper, edit the base.css file which contains the position and sizes of the various divisions. Save a copy of this file to a file named base.orig in the same directory, just in case. |
| Manual>>Extras>>Windows and Mac |
| Xt won't work with windows as of the time of this writing. Apache no longer maintains mod_python as a module.
Check here for the current mod_python code: https://github.com/grisha/mod_python The site states that mod_python successfully compiles on Mac OS X 10.8.5 |
| Manual>>Extras>>Python everywhere |
|
Xt is a collection of python scripts. The startup file is index.py. All functions branch from this file based on user interaction. Mod_python, written mostly in python, is used to retrieve the url from Apache. The url is parsed for name/value pairs which in turn are used to direct program execution. The HTML page that the user sees is an HTML template based on the selected action. Templates are located in the templates sub-directory. Modpython is used here to insert program variables that contain data to be displayed and to insert python code inside the HTML. This python code is used to branch the HTML based on user interaction. Much of the HTML code is assembled in the various python files and then passed to the designated template. This code is not written out line by line but rather generated by a python module whose function is solely to generate HTML. This module was originally published under the name HTMLgen. That module has been extensively modified for use with Xt. The original module will not provide the needed functionality and so the modified module is has been renamed and is included with xt. mariadb-connector-python is used to submit the MariaDB queries to the MariaDB server. Xt has been tested using aria, myiasm and innodb table types only. Persistent MariaDB connections are not used. Eveytime a query is submitted a connection is opened, the query is run and the connection is closed. See 'Other Software' for required software. |
| Manual>>Extras>>Given enough time |
| Interactive column sorting from result table
Move filetype name, extensions and icon to a support table. Change Titles to something that will work on mobiles Require Login to start Xt, as an option per db (read protect) Named searches (done with a support table) Enable editing of theme images in a support table |