| Guide>>Introduction>>The what and why of 3t |
|---|
| 3t is a web app that I developed to "scratch an itch". There are countless applications available that can read and write to various data storage backends.Most of these are networkable and several are crossplatform, but almost all of the applications I tried required a mastery that was unique to each application. Skills learned in one application UI were almost never applicable in another application UI. In addition to this difficulty, no single application had all the features I wanted. Running an application inside a web browser solved much of the first problem and programming it myself enabled a possibility for me correct the second problem. Thus 3t was born. |
| Guide>>Introduction>>Features and limitations |
| The number one feature I hoped to implement was simplicity and that unfortunately ended up being the greatest limitation. 3t employs three tables to handle all the primary data. That's what keeps the database design simple and by inference the user interface easy to use. Hopefully, I've hit the best balance between simplicity and completeness.
In theory it should handle any media format that your browser can display. In practice I've only tested the ones I use, which is only a small fraction of the formats that exist. Its written mostly in python and html, making it easily hacked by novice and expert developers alike. So if there's a feature you'd like to have, dive into the code and see if you can bring it to life. |
| Guide>>Introduction>>How to use the interface |
| 3t 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. Limit the results to only those records belonging to a single category by selecting that category. If you also have a specific item 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 you want. 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. If you are interested in the underlying mechanics of this process there is more detail in the Tech Manual. The source code is also available if you like reading python. |
| Guide>>Introduction>>Customize the Look |
| Only logged in users can change the settings. Make sure you have logged in. (The example database login is username: albert password: albert.)
Across the top of the screen you will see three images, all of which are easy to customize. The color scheme and the navigation icons are also easily customizable. To customize the UI: Click on the far right image which will bring you to 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 configuration table. The second icon is to edit the category table, the third icon is to edit the available UI images and the fourth icon is to edit the locale table. Select the 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. Make your selections. Scroll down to the bottom of the configuration panel until you see the row titled 'Theme'. Use the selector to select a theme, the program comes with three themes, the default theme was the one you saw when you started 3t. Now click on the update button at the right side of the header bar. The contents of these pick lists and the themes themselves are configurable as well, but you must have file level privileges, read more about this in the Tech Manual. Select the category icon: If you are using the example database you will only see one row. More categories can be added. For now lets edit the "All" image, since the one that is there is just a default image used when no image has been selected. 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 160x120, so make some available on your local storage. To edit this image click on the edit button on the far left of the row. "All" is a special category, you can't edit the category name and you can't delete the category. It will be auto-generated if you try, returning to the default image. Click on the browse button (file selector), pick a file and select the update button to finish. The image you upload will be shown in the first two panels at the top. Select some UI images: Selecting the third icon will show a list of images that are available when picking an image in the configuration editor. Initially, the images you see are the default images stored under the images directory of 3t. The images in this list can be changed by clicking on the Edit icon on the left side. Pick any image from your computer that doesn't exceed the size limitation (shown on the far left). Click the Update button in the header. This UI doesn't allow the addition of images, only updating them. Select a new locale: You can use the fourth icon to display the locale table, which can provide other languages for 3t. It's a table of words and phrases used in the 3t UI. The first column is in US English (en_US) which is not editable. The table to the right will be populated by the languages that have been enabled. Enabling a language and selecting it will present that language in the left table. Click on the Edit button to edit that language. Save your edit by clicking on the Update button. Click on the + sign to enable more languages. All enabled languages will be selectable in the configuration screen under the title Locale. |
| Guide>>Introduction>>Tech Manual |
| Click/Tap on the documentation title to switch between User Guide and Tech Manual
The Tech Manual provides more detailed information about how 3t works and what it takes to get it running. |
| Guide>>Program Views>>Top Section |
| Post installation you will be presented with the default view. The screen is devided into three sections: top, middle, and bottom.
The top section is dedicated to selecting information from your 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 the one you'd like 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. You can see where we are going with this. 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 is a fulltext search, only columns defined in fulltext indexes are searched. However, fulltext boolean characters can be used. In addition there is a check box just to the left of the search input field. Checking this box will disable fulltext searching and enable a '%%' search. The search will match any record that has the entered text IAW mariadb rules for '%%' searches. All characters are literal. The area above the search input has two functions. 1) It is used to display an image. This image can be anything you like. I 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. Here you will be able to read this guide as well as the Tech Manual. Additionally, you can configure program variables, categories and support tables. The details of this page are revealed elsewhere in this guide. |
| 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 you've made a selection in the upper section. There are three distinctly different views: Itemview, Data view and Media 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 icon or the View icon depending on whether or not there are existing associated media records. All these icons may be ghosted for non-privileged users. The Delete icon will be also ghosted if media records are linked to the Item. Any record that has media associated with it cannot be deleted until all the related media records have been deleted. The Item view data table is a two column table, populated by name-value pairs. The first column is the name column, the second column is the value column. The information displayed here is determined by the table definition. Any amount of information can be included with an Item definition. Once a Category has been selected or a Search has been initiated the resulting data will be displayed in this section. The data results are presented in a simple table format. The column names are across the top. In the data display mode there is an extra column on each side of the table. These columns display different icons depending on the state of the record in that row. In the left side column there will be an edit icon, just below that will be a delete icon. Both will be ghosted if the person logged in doesn't have appropriate privileges. Additionally, the delete icon will be ghosted if there are any associated media records, regardless of user privileges. All associated records will have to be deleted before a data record can be deleted. If you have "All Items" selected in the Item selector the left column will only have one icon, a link to that Item detail. The right most column will have one of two icons depending on whether or not the record has any associated media. If it doesn't have any an Add icon is there. If it does have associated media then an icon linked to that media will be there. |
| 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. You can login using a mariaDB user account.
The status message area will show you how many rows the result table contains. The contact link will open an email client in accordance with your 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. They all have tooltips to help you understand what they do as do many other sections in the UI. |
| 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 tool bar with Edit and Delete buttons. The area to the right of the tool bar is a list of short text fields that will typically display Mariadb char columns, the record owner name, date if any and linked support table data. The right most area is for an image if the record has one. The center area is for text, a multi-line or multi-paragraph entry. If no image is present this large text area will expand nearly to the right border.
In the header, just above the result section, there will be a caption 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 when logged in. 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 limit reflects the limit set by the table definition. If you try to save a image that is larger than that limit it won't be save and an error message will popup. Be aware that large files can be stored, but that they may also reduce the responsiveness of the program. The header also has a button on the far left, this button will cause the UI to go back (which doesn't always work). |
| Guide>>Program Views>>Advanced Search |
| Some times searching on simple text is not enough to get all the data you are looking for. In that case you might want to use the search modifiers that are available in 'Boolean Mode'. 3t usies FULLTEXT column searching.
The checkbox to the left of the search text entry can be used to de-select 'Boolean Mode'. By default, 'Boolean Mode' is always enabled. 'Boolean Mode' is automatically re-enabled after each search. If boolean mode is deselected the current search will be performed using '%LIKE%'. '%LIKE%' will search for the exact characters you enter whether as a whole word or part of a word. This can produce more results, although with less relevance. Here is a list of mariaDB modifiers for searching in 'Boolean Mode'. A more complete discussion of their uses and syntax can be found at mariaDB.com. (+,-,<,>,(),~,*,") + The word is mandatory in all rows returned. - The word cannot appear in any row returned. < The word that follows has a lower relevance than other words. > The word that follows has a higher relevance than other words. () Used to group words into sub-expressions. ~ The word following contributes negatively to the relevance * Indicating zero or more characters at the end of a word. " Anything enclosed in the double quotes is taken as a whole. When searching in 'Boolean Mode', searches are NOT returned in order of relevance and the 50% limit doesn't apply. Stopwords and word minimums still apply as usual. For example: By default, the search word minimum length is 4 characters. So searching for 'ann' may not return any results while searching for 'ann*' in 'Boolean Mode' will. 'ann*' matches 'anne', 'annie' and many more words. |
| Guide>>Entering Data>>Privileges |
| It's all very well to be able to read descriptions and view media, but suppose you'd like to enter some data. First you will need update and insert sql privileges for the database and table you wish to edit.
Typically any user is given select privileges on the 3t tables (read only) without having to login. If you have a login then you should have select, update, delete and insert privileges for 3t tables. Users with a login can also edit some system tables. The _cagtegory, _config and _locale tables all have custom editing dialogs that can be accessed in the Configuration page. Support tables can also be edited from there. |
| Guide>>Entering Data>>The edit form |
| You can create new records as well as update existing records in Item, Data and Media tables. The entry form for all these 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 you're creating a new record an Insert button will occupy the right side, if editing an existing record you will see an Update button at the right side. 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>>Date column considerations |
| As of release 7 there is a date picker provided for all date columns. The following information will still apply to those wanting to enter the date via the keyboard.
Date columns require specific values. MariaDB only accepts the date as YYYY-MM-DY. There is some flexibility as to how you enter that date, but not much. Accepted values: 2015-06-15 using any delimiter like 2015.6.15 or no delimiter like 20150615. You can shorten the year 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. If you enter an invalid date mariaDB will save the date as 0000-00-00. |
| Guide>>Entering Data>>Support tables |
| Support tables (including the Category table) have exactly the same editing forms. In order to access them you will have to go to the about screen. The Category table can be accessed in the header area via the second icon from the left. To the right of that will be a button to access the configuration images and then a button to access the _locale table. On the far right hand side of the header you will find the Support table selection dropdown menu. All supportTables will be listed here. Select the table you'd like to view/edit. The procedure for doing so is the same that as is used to edit the other tables.
Much more information can be found on support tables in other sections of this document. |
| Guide>>Configuration>>Change how 3t shows data |
| You can configure several aspects of what and how 3t 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. It's important to set reasonable values IAW the configuration explanations. When you do the information displayed should make sense IAW program designs. 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 tweek the appearance of the program ui. Program significant values are detailed in subsequent pages and more details can be had in the Tech Manual. |
| Guide>>Configuration>>Background Images |
| There are two places in 3t where background images can be used. One is for the about box in the upper right corner and the other is for the popup dialog box. On a fresh installation a default image is provided.
These background images can be selected from the configuration dialog. The dialog will display a drop down list of image names. The images are stored under images/dbname. This feature is designed to provide ten images to choose from. The about page has a tool in it's header (third from the left) which can be used to select images from your computer to populate the background image select list. If there are no images at that location, 3t will use the images under images/default. The user can then update them with new images from the image tool ui. Most common image formats will work. The image tool doesn't allow the user to delete or add images, they can only update the images listed. |
| Guide>>Configuration>>Selecting itemcolumns |
| There is a configuration row that allows the user to select multiple Itemtable 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 your Item list is a list of peoples names. If they are all your friends then maybe the list need only have their first names. If there is more than one Sue then you should select another column, one that will make each Sue unique. Select as many columns as it takes to make all list members unique. |
| Guide>>Configuration>>More considerations |
| In addition to selecting the columns that will appear in the Item selection list there are several other configurable column lists that will affect the data displayed.
As of release 7 an Item column can be selected that will be used to sort all item result tables. The direction of the sort is hard coded. A triangle character will be placed at the start of the sort column, indicating the direction of the sort. As of release 7 an Category column can be selected that will be used to sort all category result tables. The direction of the sort is hard coded. A triangle character will be placed at the start of the sort column, indicating the direction of the sort. The columns that will be displayed for an item result can be selected via the configuration 'itemColumns'. The columns that will be displayed for a category result can be selected via the configuration 'catSearchColumns'. Many results will contain columns that have data that has been combined from multiple sources. This data can be displayed with or without a separator bar inserted between the sources if so desired. Select NO or YES using the configuration selectors 'catSeparator' and 'itemSeparator'. The 'lastupdate' configuration will cause 3t to display the latest media record when it starts up if it is set to YES. The 'theme' and 'locale' selectors have been discussed earlier. The list that they present for selections depends on the files available to 3t. These lists are mutable in that more or less themes/locales can be listed as they are made available. |
| Guide>>Configuration>>The _locale table |
| This table will contain all the strings from the 3t UI in english in the column 'en_US'.
The 'en_US' column can not be edited using the 3t UI. More translations can easily be added from the _locale table view. If no other languages have previously been selected one default additional locale will be displayed in the right side menu, just as a placeholder. On the right side of the header bar you will see an add icon (it's appearance will depend on the selected theme). Clicking on this icon will display a select list of all the offered locales. You can select multiple locales. The locales that you select will be available for editing from the _locale table view page. They will be in the right side menu, click on one to edit/enable it. From the _locale table view select a locale from the right side menu that you would like to view/edit. If you would like to edit the translations click on the Edit button on the left side of the header bar while viewing the locale you'd like to edit. Translate as many of the fields as you like and click the update button. All the locales listed in the right side locale menu will be included in the config locale selection, to enable any of them select that locale from the list and update the config. The current locale selection is composed of the mariadb locale list, offering over on hundred locales. More can be added if you want to edit the code to add them. The present format must be adhered to, if you expect usable results. 3t 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 3t. |
| Guide>>Documentation>>Documentation Overview |
| Of course what you are reading now is part of the documentation. The Tech Manual and the User's Guide comprise the online documentation. You'll be glad to know that there's lots 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. In addition there is an html version of this documentation available in the doc directory and that same html document can be downloaded from a link in the bottom left corner of the about page.
|
| Guide>>Documentation>>The User Guide |
| You are reading the User Guide right now.
|
| Guide>>Documentation>>The Tech Manual |
| To switch to the Tech Manual click/tap on the documentation title just above the chapter listing.
The Tech Manual covers how such technologies as sql, python, html and others that were used to make 3t. It will also give greater detail regarding requirements, themes, categories and relationships. |
| Guide>>License>>3t license |
| Author: Gary M Witscher
License: Free (as in beer and chicken) W3C Markup Validation: HTML5 W3C CSS Validation: CSS3 (validation as time permits) |
| Guide>>License>>Other software |
| Mod_Python
Version: 3.5.x 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.xx 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. Although I've always preferred to use versions from Debian Stable they are sometimes not available. Debian bookworm includes a working mod-python package. If your system is older you will have to compile your own mod-python from git. |
| Manual>>Introduction>>More than a User Guide |
| This Tech Manual is focused on the technical aspects of 3t. These are the aspects of 3t 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 have a functional 3t db, while from a user's point of view these subjects are of no interest what so ever. |
| Manual>>Introduction>>3t Overview |
| 3t can be broken down into three parts.
1) Python Python makes it all happen, it is the core of 3t. 3t is a python program which collects data, stores it and displays it in a browser. 2)mariaDB mariaDB is the backend storage for 3t data. mariaDB-connector-Python enables python to send sql to the sql sever. 3)HTML5 3t displays it's UI and it's data in a standard web browser using HTML. Mod-python allows python code to be used inside HTML. That code is used to manipulate variables, branch based on their values, and to call functions and modules that process user input. |
| Manual>>Introduction>>Directory tree |
| dir/file structure
rootdir (like /var/www/3t) --catimages (category images) ----one subdir for each db ----default image files --doc (docs and db files) ----examples ------apache ------examplDB --------one subdir for each db ------required-tables --images (general ui images) ----one subdir for each db ----defaults ----filetypes --itemimages (images for items) ----one subdir for each db --js --log --style (store css files) --templates (store html templates) --themes ----one subdir for each theme --tmp (for temperary files) |
| Manual>>Introduction>>Logs |
| 3t has a log directory in it's root directory. It is used to log queries that fail in the hope that knowing the query will help track down the source of the problem. Also if somehow someone or something is able to inject a query into the program, chances are it will fail at least once and the log will show what ip it came from and the query that was attempted.
There can be more than one log under the log directory because there is more than one function that submits queries to the dbs. Each will have it's own log. They will only be created for queries that fail, so they may not exist. 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 you more details. The log directory must be writable by the web server user. |
| Manual>>Introduction>>User Guide |
| Click/Tap on the documentation title to switch between User Guide and Tech Manual The User Guide provides more hands on instructions on how use and configure 3t. |
| Manual>>Requirements>>Software |
| 3t 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 3t you will need:
A running Apache server and the skills and permissions to configure it. A running mariaDB server and the skills to administrate it. You will privileges to design and create databases and tables. In particular you will need these software packages with these minimum versions: Python ver > 3.5 MariaDB server ver > 10.5.x mariadb-connector-python > 1.0.7 Apache ver > 2.4.x mod_python ver > 3.5.x |
| 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 you will not likely be able to run this program on a shared hosting service. The reason for that is that I've never found any such services that offer mod_python or mariaDB-connector-python. Most are php orientated. If you try to run 3t from a dedicated home based server you will find that local access is quite easy. 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 your home account (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, 3t will run on a dedicated Virtual Machine. These services usually offer full root access to the VM so you'll be able to install all the software you need. |
| Manual>>Quick Start>>Forget the rules! |
| There's always a Quick Start section for those who just want to get going. I've always been one of those people myself. I liked to get a feel for a program first before I had to learn anything. This section is for people with similar needs. Others may want to skip this section and jump to the mariaDB section where they can begin at the beginning.
That said, given the architecture of this application, this quick start may take more time then most other quick starts. I'll attempt to make it as quick as possible, step by step. 1) I must assume you have running Apache and mariaDB servers and that you're using linux. 2) Install some software if not already installed: ----- python > v3.5 ----- mod_python > 3.5.x ----- You msy have to manually load the python_module in apache2. ----- In debian it's loaded when installed, module name is 'python' ----- mariadb-connector-python (>1.0.7) 3) Unpack the 3t directory into your web server's root directory. I will assume that you will use localhost. Read the INSTALL.TXT file in the root directory. Make sure the following directories under 3t are writable by the user that Apache runs as: catimages images itemimages log tmp. 4) Go to the doc/examples directory and follow the instructions in the README file to import the required tables. There are additional instructions in the examplesDB directory for importing an example DB. 5) Visit 3t at: http://localhost/3t. No data will have been inserted into the main tables, can't hurt to give it a try. Have a look around. Hows that, in way less than ten steps you're up and running 3t? |
| Manual>>Quick Start>>Apache configuration |
| Look in the doc/examples/apache directory for example apache conf files. |
| Manual>>MariaDB>>Table overview |
| This section is about how this application uses MariaDB as well as some details regarding the table definitions. At some point I'm assuming you will want to design your own databases that will work with 3t. In that event you will need some information.
3t is a python script that uses python to manipulate mariaDB table data. Most of that work is done by the dosql function. Insert, update, 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 passed to the db module, all users must have select privileges. There are five required tables that DO NOT store application data. Their names begin with the underline character. Technically, 3t will run without the _doc and _locale tables. Sql dumps of these tables can be found under the doc/examples directory. _config _kooky _category _doc _locale These 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 three tables that DO store the application data. The item table is designed to store the primary data. The cat table is designed to store secondary data related to the primary table and the media table is designed to store media related to either the item or cat table. An example might be and author database: (1 item table) containing a picture of the author, author names, url and any other useful information. (2 cat tiable) containing the names of books related to each author and category that they belong to. (3 media table) containing reviews, pictures, audio, or videos files related to an author or a book. There are individual sections on each of these tables describing their definitions. It is assumed: ----The item table name will be the same as the dbname. ----The item table PRI column name will be the same as the _dbname. ------(note the beginning underline, it is required) ----cat and media table PRI column names must be _tableName ------where 'tableName' is the name of the cat/media table ------(note the beginning underline, it is required) The media table name will be 'media'. The three 3t tables use fulltext searching Full Text Index names will be tableName All columns will have a default value defined The cat table will need a column that will be used to display a list of category names to choose from. The recommended name for this column is 'category'. This is what 3t will look for. |
| Manual>>MariaDB>>_category table |
| The_category table has these columns:
_id (The primary column) category (The category name column) image (The category image column) filename (The last filename selected column) Refer to the required-tables directory under the doc/examples directory for the detailed table definition. Every DB will have it's own _category table. The images stored in this table are written to disk when 3T is first loaded and after the table has been modified. Their location is: server-root/3t/catimages/dbname/ (Where server root is the directory that apache is configured to serve web pages from.) (Where dbname is the name of the DB that the images are from.) 3T will load these images from disk when started and after the table has been modified. The category names will comprise the category selection list. The category names are used to compose select sql queries. These queries limit the search results to records that contain the selected name in the catTable's category column. Modifying the data in this table will not modify any data in any other table of any other DB. If over time a category is removed while some records remain that are using that category, a text search will be the only way to find that record. The UI will offer currently available category names when that record is being edited, assigning the first one available by default. 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
## Text to print on top of the logo image (default=dbname). displayname (any text) ## Location for the displayname (default=MIDDLE_SMALL). displaynamelocation (Pick from list) ## Image to use as your logo (default.img). 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) ## Hostname for the sql server (default=localhost). selectedHost (example.com) ## Category table column containing the category names (no default). catColumn (Pick from list) ## Item table column to sort results by (default=itemIDfield) catSortColumn (Pick from list) ## Category table column to sort results by (default=catIDfield) catSortColumn (Pick from list) ## Item list column names (default=first 2 char columns). itemListColumns (mulitPick from list) ## Item column names for All items (default=first 4 char columns). itemColumns (multiPick from list) ## Category column names displayed in results (default=None). catSearchColumns (multiPick from list) ## A separator is placed between concatenated item columns itemseperator (yes/no) ## A separator is placed between concatenated cat columns catseperator (yes/no) ## On startup, display the last record entered (default=NO). lastupdate (yes/no) ## Select a theme from the list provided (default=default). theme (Pick from list) ## Pick your preferred language (default=en_US). 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 usable colomns. 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 3t 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 3t one can download the latest version of the documentation (by default the db version) by selecting the EXPORT link at the lower left corner of the about page. |
| Manual>>MariaDB>>_locale table |
| This table will contain all the strings from the 3t UI in english in the column 'en_US'.
The 'en_US' column can not be edited using the 3t UI More translations can easily be added from the _locale table view. If no other languages have previously been selected one default additional locale will be displayed in the right side menu, just as a placeholder. On the right side of the header bar you will see an add icon (it's appearance will depend on the selected theme). Clicking on this icon will display a select list of all the offered locales. You can select multiple locales. The locales that you select will be available for editing from the _locale table view page. They will be in the right side menu, click on one to edit/enable it. From the _locale table view select a locale from the right side menu that you would like to view/edit. If you would like to edit the translations click on the Edit button on the left side of the header bar while viewing the locale you'd like to edit. Translate as many of the fields as you like and click the update button. All the locales listed in the right side locale menu will be included in the config locale selection, to enable any of them select that locale from the list and update the config. The current locale selection is composed of the mariadb locale list, offering over on hundred locales. More can be added if you want to edit the code to add them. The present format must be adhered to, if you expect usable results. 3t 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 3t. |
| 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) kookyData: (The cached data. One record for each unique browser cookie.) remorteHost: (ip address of remote host) modstamp: (timestamp for each record) The data in this table can be deleted at will. More will be generated when 3T is loaded. If 3T 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 that can be reused between page loads. |
| Manual>>MariaDB>>Create required tables |
| Refer to the required-tables directory under the doc/examples directory for the detailed table definitions. |
| Manual>>MariaDB>>Table privileges |
| The script will not start the first time if the system user doesn't have select, update and insert privileges to the , _category, _config and _kooky tables. At the same time the system user should only have select privileges to all other tables. This allows users to view the data without being logged in and only logged in users can edit the data tables. No required tables (those starting with a '_' can be accessed via the UI by a user that is not logged in.
Other privilege schemes are possible and may be desired. Here is an example of the privileges assigned during the installation of an example db. Where 'author@localhost' is the system user and 'albert@localhost' is a login user. The '_category' and '_config' privileges are only used for initial startup of a new install. They can be removed once there is a login user configured. USE `mysql` CREATE USER 'author'@'localhost' IDENTIFIED BY 'author'; GRANT SELECT on `author`.* to 'author'@'localhost'; GRANT SELECT, INSERT, UPDATE on `author`. `_kooky` to 'author'@'localhost'; GRANT SELECT, INSERT, UPDATE on `author`. `_category` to 'author'@'localhost'; GRANT SELECT, INSERT, UPDATE on `author`. `_config` to 'author'@'localhost'; FLUSH PRIVILEGES; CREATE USER 'albert'@'localhost' IDENTIFIED BY 'albert'; GRANT SELECT, INSERT, UPDATE, DELETE on `author`.* to 'albert'@'localhost'; FLUSH PRIVILEGES; |
| Manual>>MariaDB>>Reserved Column Names |
|
# the following are reserved column names # all system table names start with a '_' # the primary column for all sysTableNames is '_id' # the primary column for the itemTableNames is '_'+dbname # the primary column for the catTableNames is '_'+tablename # the primary column for all supportTableNames is '_id' # # dbname the name of the database, required in the item table # to be used as the primary column of the item table ('_dbname') # 'private' supported in media table only ('PUBLIC'/'PRIVATE') # to allow a user to store publicly sensitive data # 'date' required by all tables not in sysTableNames or supportTableNames # 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 not in sysTableNames or supportTableNames # to store the name of the user that created the record # 'modstamp' required by the _kooky table and all 3tTableNames # to store the data/time each record was last modified |
| Manual>>Advanced DB>>Rules |
| These rules may be stipulated one or more times throughout this document. However, they are strict rules for 3tp3. Here they are all one place. The rules stated here override rules stated elsewhere if a conflict arises.
TABLE ID COLUMN MUST BE THE FIRST COLUMN, CAN'T BE ZEROFILL 3 MAIN TABLES, THE ID MUST BE '_tablename' SUPPORT TABLES, THE ID MUST BE '_id' THE WORD 'All' CAN'T BE USED AS A CAT OR ITEM NAME NO SUPPORT FOR BLOB OR TEXT COLUMNS IN CAT TABLE MARIADB DEFAULTS ARE SUPPORTED FOR CHAR COLUMNS ONLY NO SUPPORT FOR 'ZEROFILL' IN TABLE DEFINITIONS 'owner' CHR COLUMN IS REQUIRED FOR ALL 3T MAIN TABLES 'date' DATE COLUMN IS REQUIRED FOR ALL 3T MAIN TABLES 'filename' CHR COLUMN IS REQUIRED FOR THE MEDIA TABLE 'category' CHR COLUMN IS REQUIRED FOR THE CAT TABLE 'media' BLOB COLUMN IS REQUIRED FOR ALL BLOB TYPES The hope is that by adhering to these rules the code is easier to maintain and the table structure is simpler and more consistent. . |
| Manual>>Advanced DB>>3t or MariaDB off-world |
| 3T AS A REMOTE CLIENT
If you would like to run the 3t scripts on server1.com and the mariadb on server2.com you can. As of rel 11.0-0, this requires that both machines be on the same network. Below you will find the minimum configuration elements that are needed to accomplish this connection. FILESYSTEM 3t 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 only way to accomplish this is to mount the remotePath on the machine running 3t. 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/3tloadfile/" and /mnt/www/tmp/3tloadfile" APACHE Change sqlhost in 3t.conf to the remote-mariadb-server. If you need to access various remote-mariadb-servers then configure a unique virtual server for each. MARIADB The db that you hope to access 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 `mysql`; 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 DB>>3tp3 with 3t-7.x dbs |
| Use 3tp3 scripts to connect to a mariadb database created for 3t-7.x (or older)
The older database will need to be altered to be able to accommodate 3tp3. Numerous 'improvements' were made in 3tp3, providing more stability and a code base that is more easily maintained. Because of these changes any database that was created for 3t-7.x scripts will require several changes. 1) import an updated _kooky table 3t-10.x requires the new definition, it won't write to the old one. 3t-7.x writes to either _kooky definition 2) import an updated _locale table 3tp3 has changed several _locale texts 3) import an updated _doc table the _doc table was updated for python3 4) remove 'zerofill' in all table definitions, python connector fails on zerofill columns 5) confirm that item, media and _category table's blob fields are named 'media' If you want to be able to use the 3t-7.x scripts on databases structured for 3tp3. 1) change all '_kookyID' strings in the 3t-7.x kooky2 module to '_id'. . |
| Manual>>Relationships>>Tables talk to each other |
| 3t is written to facilitate access to a relational mariaDB database. 3t is dependent on the structure of the tables. There are minimum required tables, 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 3t 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 3t this table is called the Data 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 Data table.It should be a table that can store media as well as text. You will need to store pictures, video and scanned documents. In 3t this table is the Media table. So, you can see the relational tree. Item table <----> Data table ^------- Media table ------^ In addition the Media table relates directly to the Item table so that specific information related to the Item but not related to any Data record can be stored. This is the design that the 3t python script is built around. |
| Manual>>Relationships>>Special case columns |
| Owner column:
By default the three main tables should have a column named 'owner'. By default this column will contain the username for the user that was logged in when a record was created. This is automatic, requiring no extra input from the user. 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 or if no user is logged in. Currently, anyone can view any record. Multiple usernames can be manually inserted into any 'owner' column, enabling multiple users to have edit/delete privileges for that record. Multiple usernames can be set as the default value for a 'owner' column and thereby automatically entered with each new record created. Usernames must be separated by a space and they must not contain any spaces. In order to insert multiple usernames into a record or table an appropriate sql client (command line or gui) must be used, 3t doesn't provide an interface to accomplish this. Summation column: If there is a numeric column defined in the Data table (other than the id cols) then the sum of that column for all records in that table will be listed in the Item view using the name of that column. This is designed to do things like give a total cost spent for a specific item over time. Support table column: As mentioned elsewhere, if one of the main 3t tables has a column named the same as a support table then that table will be treated as a data table for that column. This means that when a record containing one of these columns is edited, that column will display a picklist 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 picklist. The intent is to have unique selections. Values in the first column must be unique. Refer to the section on support tables for more information. |
| Manual>>Relationships>>Using column names. |
| Both the item table and the data table can be configured to display their columns in special ways by having the column names end in a '_'.
When a table has multiple column names that end with a '_' 3t will concatenate the data in those columns into one column for display purposes. This feature can be useful for saving horizontal space when you want to display lots of columns. There are two settings in the _config table that augment this feature: itemSeparator and catSeparator. Because this joining of columns sometimes makes it difficult to read. Setting these to YES will insert a separator ('|') between the joined columns to assist in readability. However, you must be careful when renaming columns. If you rename one or more columns, you could find that 3t will no longer start. The problem is that the 3t configuration has set column names (saved from the last time it was configured) which may not exist now that some names have been changed using an sql client. The easiest method to avoid any problems is to delete the _config record before trying to access 3t. When 3t restarts it will automatically put valid column names in the configuration. Use 3t to go over the configuration to make sure it's what you want. |
| Manual>>Relationships>>Support tables |
| There can be any number of SupportTables.
SupportTables are text only tables (except the_id 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 first column in a support table must be an '_id column and the second must be named the same as the table itself. The idea is to provide a unique selection. For example if the supportTable is a vendor table and you had several Big Stuff stores listed there, then you would need the name of the store and the city where it's located to have an unique selection. So,column1='Name of Store' plus 'Location of Store'. Column1 will always need to be unique. The support table columns can also be used to display a popup tooltip for that column in the results display. To enable this add an "_" character to the end of the support table column name(s) . This will cause the contents of the first column to be highlighted as a link in the result table. When the user's mouse hovers over this column a tootip will popup containing the contents of all columns properly named. For example, using the above analogy, the column will show 'Big Stuff New York' and the tooltip will show '1234 N Main St 223-990-5678' from the 'address_' and 'phone_' columns of the support table (if they are properly named). You can use an exampleDB stored under the doc directory to better understand how this works. |
| Manual>>Relationships>>Divide and conquer |
| Categories are a central part of the 3t data scheme. All data sets can be broken down into subsets of related data. Categories.
A common query would necessarily be to search for records in a table that are related to each other in some organizational way. They wouldn't have to share text, but something more general, categorically alike. This type of search yields an overview that can be further limited using text matching. This concept is built into 3t. Selecting a category queries the data, returning those rows whose category column matches the selected category. If an item is also selected, then the rows returned are limited to records that are related to the selected item. Finally, the results can be further limited by entering search text. The 3t catTable must have a category column. It must be named 'category', That column will get it's value from the '_category' system table, a support table that stores the category information. The '_category' table will also contain an image to represent each category in the UI category selector. Categories can be added and removed by any login user via the category config dialog. While the itemTable is the primary table that all records point to, the catTable along with the '_category' table provide the essential function of dividing up the data into manageable groups. This makes searching for information intuitive, quick and easy. |
| Manual>>Relationships>>Private Media |
| 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 and a support table named 'private'. When the user creates a new media entry, if the media table has a 'private' column, one can select 'PRIVATE' or 'PUBLIC'. Accordingly, when A 'PRIVATE' media record is viewed by someone other than the owner the text and image parts will not be visible. To enable this functionality the media table must have a column named 'private' varchar(10). The database must have a supportTable named 'private' containing an '_id' int(4) column and one other column named 'private' varchr(10). The 'private' table must have two values ['PUBLIC','PRIVATE']. These values can be in any language you like, but 'PUBLIC' must have an _id=1 and 'PRIVATE' must have an _id=2. The media view will determine what is public or private by the _id column value of the selected record in the private support table. Refer to the document page on Support Tables for general information about support tables. |
| Manual>>Extra>>Python everywhere you look |
| Users don't need to know anything about python to use 3t, but admins and hackers that know a little python, css, html and sql can add functionality, themes and color schemes to 3t without much trouble. 3t consists of around 10,000 lines of python not including the imported modules. there's plenty of fun to have.
3t is a python script. The startup file is index.py. All functions branch from this file based on user interaction. Mod-python is used to marry python to html via Apache. The html page that the user sees is main4.html. It is a template located in the templates directory. Mod-python 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 main4.html. 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. It has been extensively modified for use with 3t. The original module will not provide the needed functionality and so the modified module and some of it's required extensions are included with 3t. Python is used to submit the sql queries to the mariaDB server. 3t has been tested using aria, myiasm and innodb table types only. Persistent mariadb connections are not used. Evey time a query is submitted a connection is opened, the query is run and the connection is closed. |
| Manual>>Extra>>Make 3t look the way you want |
| Themes consist of css color schemes (as of ver 3.0) and image sets (as of ver 3.4).
A full theme consists of two parts. One is a css file to control color and decoration. The second is a theme directory containing navigation images. 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. Default images are supplied for each css theme name in the theme directory. If you want to create a new theme, copy the contents of the default theme (default.css) to a new .css file under the style directory. Then change the background and foreground colors as you like. Of course backgrounds can also be images. Be sure to refresh your page after changing any css file. To create your own navigation images, copy the themes/default directory to a new directory named after a css file. Read the list of image names in the themes/README file. Your images should use these names and recommended sizes. There should be a theme directory named after each css theme file. When a theme is selected in the configuration, it enables both the css file and the theme images directory. Be sure to refresh your page after changing any of the images. If you want to go deeper you can edit the base.css file which contains mostly the position and sizes of the various divisions. |
| Manual>>Extra>>Sure it works with Windows |
| I have run 3t on a Windows based server without knowing anything about Windows. It has been a very long time since I have tried a windows install.
In reality I ignored Windows as much as I could. I installed Apache, python, mod-python, python-Mysql, and MySQL all from their web sites. The configuration was the same. |
| Manual>>Extra>>Given enough time |
| 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 3t, as an option per db (read protect) Named searches (done with a support table) Enable editing of theme images in a supportTable |