Print

3.6. Database admin

Kartris has a back end section dedicated to various database related tasks. To access this, go to 'Configuration > Database Admin'.

You may find that some tabs referred to below are not visible when you log in. In this case, the backend.expertmode config setting is probably off. This setting hides some back end features, even to those with 'config' user permissions. You can edit the config setting to turn on expert mode, which will show advanced tabs in the db admin section.

Expert mode also allows you to edit certain field details of config settings and language strings that are normally hidden.

3.6.1. Home screen

From here you can restart Kartris. This works by making a minor change to the web.config file and saving it – ASP.NET will restart a site when this file is changed. Therefore it only works if your site has write permissions to the root. If you need to restart Kartris and this does not work, you can manually change the web.config via FTP.

3.6.2. Clear data

This provides an easy way to clear data related to products, orders or sessions. For each type of deletion, the system knows which tables should be cleared.

Where possible, avoid clearing individual tables directly in the database unless you're absolutely sure you understand the consequences. There are some tables in Kartris (such as config settings and language strings) that should always have data, even in a new install that has no products or customer records.

Deleting data from some tables without understanding the relationships can also leave orphan records behind. For example, version records always link to a parent product. If you delete all data from the products table only, you would end up with many orphan records from versions, language elements (the language specific content of products like descriptions and names), related products, etc.

3.6.3. Run query

This provides an easy way to run a query against the database. You can user SELECT, INSERT, DELETE or UPDATE queries.

SELECT queries will return results in a tabular format.

3.6.4. Data export

From time to time, you may need to export data from Kartris to a file. This might be anything from a customer list, order details, product inventory or statistical data for external analysis. Kartris provides both a built-in export for customers/orders, as well as a flexible export system that lets you write your own custom exports and save them within Kartris.
Above: The saved export controls

3.6.4.1. Orders export

You can specify a date range, the file name you wish to export as, the delimiter (marker) for each field and that indicates string values (non-numeric / text as opposed to numbers). You can optionally include the full order details field (text of the confirmation email to customer stored in the system) and incomplete orders (orders where the status checkbox 'complete' is not yet checked to indicate the order has been fully processed).

The file format is CSV - comma-separated values. This is a text format which spreadsheet programs such as MS Excel and Libre Office Calc can open. If you find data shows up in the spreadsheet with single quotes around values, try exporting again but select a double-quote as the 'string delimiter'.

3.6.4.2. Custom exports

Custom exports are saved reports that you can run to output specific data that you need. If you go to the 'Saved Exports' tab, you can see some pre-written custom exports that come as standard within Kartris - clicking on edit will allow you to see how these are constructed. An export requires the following information:

 

  • Export name (any name will do, it just lets you identify this export in a list)

  • Field delimiter (separator between different data values, normally a comma)

  • String delimiter (the marker around text / non-numeric values - experiment with a single or double-quote and open up the resulting file in your spreadsheet program such as Microsoft Excel - if you see the single or double-quote mark around data, then try using a different delimiter)

  • Database query
The database query is the key part of the export. This uses 'structured query language'  (SQL), which is a standard common language that is largely similar on different types of databases to specify which data is to be exported. A full explanation and grounding in writing SQL queries is beyond the scope of this manual, but there are plenty of books and web sites dedicated to the subject.

Microsoft Access and MS SQL Management Studio also include graphical tools to help you build queries.

Advanced users may also prefer to create stored procedures and then call these from the saved export - our standard 'Products data for data tool' export which is included in a default installation uses this method.

3.6.5. Tools

This gives an overview of your database, and lets you back it up. Note that this fires the backup facility within MS SQL server. It does not enable a remote backup. You should discuss with your hosting provider about the best way to obtain an off-server back up of your MS SQL database.

The page shows the root of the current web, which can help you format the correct path to create the backup file in the correct location within your web in order to let you download it with FTP.

Above: The database tools show you the physical location of the underlying database files, and where a backup will be created.

3.6.6. Full-text search (FTS)

MS SQL has a feature called 'full text search' that significantly improves the performance of text searches across data, especially on very large databases with many records. This feature of MS SQL is installed with the 'advanced services' version, and therefore may not be available on all installations. For this reason, we don't activate it as standard. On smaller sites, the performance different with and without FTS isn't significant. You will probably find it simpler not to use this feature.

But larger sites will get a huge benefit from this; especially when you have tens of thousands of items, or more.

Before attempting to activate this feature, check that your server supports it by expanding your database and looking for the 'full text catalogs' folder, as shown below.
If the feature is supported, clicking the link within the FTS tab in your Kartris back end will create the required stored procedures and activate the feature. Searches in both the front and back ends of Kartris will use FTS. For this reason, even stores with a relatively small number of products but with a large customer/order database may also decide to use FTS to improve performance of back end searches.