The Database

The CITY Shop

Database | DBI/SQL

The database on this system is accessed through the DBLib.pm. For the standard shop, all the tables are in flat-file format. Support for DBI is implemented in the release due in a couple days, but even then, the interface in the shop will stay the same. The DBLib.pm will contain a wrapper for DBI which will translate the queries as needed. The object constructor will also make the connections with the database(s), as necessary, allowing the data tables to be spread across different systems.

The reason we included the file system in the standard shop, is that not everybody has a SQL database installed, together with DBI support, and for the most shops out there that have a limited amount of items and hits, the file-based system is so much easier to implement and maintain.

If you open a SQL connection, that will take about 0.2 seconds. That means, 0.2 seconds added to every click, if you don't use mod_perl. In the same 0.2 seconds, you can make at least 2 average file queries, if the files don't have more than, say, 100 records. This delay makes SQL look slow for small shops.

If you plan to use in your shop a database with more than 4000 items, SQL is a better fit for that, alone because one database search in flat file format might take longer than 3 seconds (ymmv). If you are willing to put up with that kind of delay, and your site does not experience high traffic, then you can still stay on a flat-file based system.

If your shop database has a couple hundred items or less, we recommend using the flat-file system over the SQL, since in this case the flat-file system is not only faster, but also easier to implement, maintain, and backup.

Database recommendation and tools

Although the shop implements a generic DBI support, we recommend the use of MySQL, due to its speed and ease of use. Typically a MySQL database software is installed in 10 minutes, the shop is being hooked up in another 2 minutes. Of course, if you want to customize the databases, you do have some work ahead, but it will still be feasible, with basic knowledge.

For the MySQL database management, we recommend DBTools, which you can find at:

http://dbtools.vila.bol.com.br/

The database of the generic shop

We will list below the tables used in this system's database. All of them are described in the ShopSetup.pm using a named hash. This is referenced in the $S->{-DB}, with the generic name of the table. As we mentioned before, they are all in flat-file format, but can be placed later in a SQL database, on a per/table basis.

Here is, for example, the hash describing the discount table:

my $DBDiscount = {
  -TYPE       => 'File',
  -FILE_NAME  => 'discount.db',
  -FILE_PATH  => $Path."Data_files",
  -FIELD_NAME => [qw(ItemID DiscountID DiscountRange DiscountRate)],
  -FIELD_KEY  => 'ItemID',
  -FIELD_TYPE => {'ItemID' => 'auto'},
};

Here is a list of the keys appearing in the hash above, and their description:

-TYPE - is the type of the table, where the "File" is the only value supported right now. When the DBI wrapper will be done, the reference to another type of database will be in the format similar to: DBI::MyDatabaseName, or even ODBC::MyDSNName. We will build a separate structure to describe the database access (host, userid, password).

-FILE_NAME - is typical to the file-based table, and contains only the file name, without path.

-FILE_PATH - is the path to the file described above. It allows us to reference files outside of the shop directory, if we feel the need to use common tables for multiple shops.

-FIELD_NAME - is the array of fields in this table

-FIELD_KEY - contains he name of the field that is the key of this table

-FIELD_TYPE - is an hash of field types, for those fields that are not generic. We chose to keep this as simple as possible, this is why we chose the use of a hash.
The only field that you might need to mention in here is the key field itself, if it is an autoincrement field. Its value needs to be set in that case on "auto".
In previous versions of the DBLib we used to support multiple fields, thus you might see some keys still described as "multiple" in the ShopSetup.pm. Since we had to make extensive changes to the DBLib.pm to support the DBI wrapper, we chose to temporarily remove the support for other field types than the "auto", which is needed for the key field.
We plan to re-implement them, after the DBI wrapper is completed, and also offer support for other field types, like one-way or two-way encrypted fields. Since the DBI does not offer support for this kind of fancy, we will build the translation routines for them in the wrapper.

Other valid keys to a table description hash are:

-CREATE_IF_MISSING - set this value if you want this table/file to be created, with the first write attempt. If not set, the script will exit with an error.

Tables found in the generic shop database

Here is a list of the tables used currently by the system, and described in the ShopSetup.pm, under the named hash %DatabaseList, and referenced in $S->{-DB}:

user

This table contains the user authentication data. The password is saved in clear-text right now. We will implement soon another methods. The user record has also a time stamp of the user's first login, and a flag to show if the user is enabled or not. This last field is not actively used right now.

userbilling, usershipping

These two tables save the billing and the shipping addresses of the users. Every user can have one or more records. The users have access to editing their data in these tables, through the "My Account" interface of the shop.

usercreditcard

As the both tables above, the credit card record contains all the credit card information for one payment mode. Every user can have more than one credit card on file. For the time being, this data is recorded unencrypted in this table.

order

The order table contains all the data referring an order on file. It holds the full billing and shipping addresses that were used for this order, the weight, price, discount, tax, shipping method and cost, as well as a flag of the processed status of this order.

The order contents are saved in the field "OrderItems" in a URL-like format. This field contains all the items ordered, by item id, the options associated with them, as well as the individual prices charged for the items and options. This was necessary, since the item prices and the discounts can change with the time, but the order should contain the data available at the ordering time.

Note that the shop saves the orders on an individual basis, in the Logs/orders directory of the shop, as a backup. The credit card or check information is being saved in those files, as they are actually the content of the mail sent to the shop admin, for every placed order.

product

This is the product table. It contains all the fields necessary to describe the product, including the language-specific contents. The options valid for every item are saved in the field "ProductOptions", in a double-colon ("::") separated string.

stock

The stock table contains the quantity of items available for every item that is supposed to have an active stock. Not all the items need to be in this table. If the shop uses the stock table to build an order, and some items were not found in here, the stock processing for those items will be ignored.
There is also a flag field for the backorder: set this field on "1" to allow this item to be backordered, leave it empty or set on "0" to disable backordering of the item.

upsell

This table is used by the upsell/cross-sell mechanism of the shop. The records here are the areas of the shop or the categories that use this feature, and contain the list of item ID's that are to be used for that.
You will see items like "main", "cart", "search", these are the shop areas, the rest are category ID's.
The upsell mechanism is as follows:

The value of the "UpsellMax" determines how many items will be displayed at one time. If the list of items contains more items than the value of "UpsellMax", a random list of items will be selected from the main list, avoiding the upsell of items that are already in the main page view. This process is explained in the code, please see the ShopProductBuilder.pm.

manufacturers

this is a very simple table, meant as an example of how you could build support for a manufacturer page. In a real-life scenario, you might want to add fields with images links, detailed description, and other data refering the manufacturer.

options

The option table contains the data for the options used by the products in the product table. The options ID's are not related in any way to a certain product. The record contains the language-specific content needed to build the option html, as well as the prices, the value selected by default, and the display type. You will define here if the option appears as a select, radio, or checkbox.

Here are as an example, two lines of the table (each line is broken in three, cause of the length):

100015|select||0::5%::10%|
  Warranty|Choose your Warranty|no warranty::3 Year Plan::5 Year Plan|
  Garantie|Garantie|keine::3 Jahres Plan::5 Jahres Plan
100016|select||0::-2::4|
  Color|Choose the Color|Blue::Green::Red|
  Farbe|Waehlen Sie die Farbe|Blau::Gruen::Rot

You can mix fix amounts and percents as you wish, negative values are allowed.

discount

The discount table describes the discounts applied to every item, depending on the quantity ordered. You can customize the code, if you wish, to apply discount for other fields, but the generic shop uses only the ordered quantity as criteria for the discount.

Every discount has an ID, that we will find in the product table, associated to the items. The "DiscountRange" field defines the min/max quantity that enables this discount. The "DiscountRate" contains the fix amount or the percent applied to the discount.

Here is an example:

103|1|1|1::5::10|0::10%::20%|English Description|German description

The first field is the discount ID, followed by the flag that enables the discount, then a field that flags the discount as "one-time" (global), when set on 1. That means, when set, the discounted amount will be applied only once, for any number of products falling in the range.

The following fields are the thresholds for the discount, the actual discount values applied to them, and finally the discount description (interesting for the manager interface).

Other tables

You may see other tables described in the ShopSetup.pm, like "session" and "cart"; these are processed internally by the SessionLib.pm and CartLib.pm and their structure will be explained in detail in the description of those packages.

DBI / SQL

The shop offers support for SQL databases, through the DBI interface. Since the shop's own DBLib.pm module started with a simplified implementation of database access, which has proven itself to be very easy to use and reasonably scalable, we decided to encapsulate the interface to DBI in the DBLib.pm itself, preserving the user interface.

There are a few steps involved in getting up and running with a SQL database. We will give here the example of a MySQL database, but we are confident that anything that will run under DBI, will work the same in The CITY Shop. The only databases that will not work are those that do not implement placeholders in the query building, at this time we are aware only of the DBD::ADO that does not (yet) implement them.

The methods translated in the plugin to DBI do not include fancy like table joins, bind output values, and such, also for the reason of portability. Using the model we provide, you can have a table on a MySQL, a couple more on an Oracle, or Microsoft SQL, and maybe later on you can get your user information from a LDAP database, when we will provide an interface module.

We will provide at a later date, a way for you to pass on a preformatted SQL query, but in the first stage, we chose to keep things simple.

Implementing SQL support

Since setting up a SQL database is not a trivial matter, we chose to offer the following install logistic:

start the shop with flat-files and get it up and running
install SQL, define your database user and password
transfer the tables from the flat files to SQL

You have currently only a one-way migration path to SQL, from the flat files, and this has its reasons. The main reason for it is that the SQL database allows for characters like "\n", "\r", "\t", as well as pure binary content, which is not supported in the case of the flat files. In our flat file model, we convert the newlines, carriage returns, and tabs to spaces, thus slightly affecting the content.

Although you can do a lot of fancy with a SQL database, keep in mind the purpose of the shop, and filter your data, whether your database implementation supports it or not, for the sake of portability.

Before you start

Ask your ISP to set you up a database on the local machine, and provide you with an user ID and password, with full access to that database. You will also ask to have the DBI interface installed in the Perl directory, along with the DBD module for your SQL database. This is also a step that your ISP should perform. If this is your own machine, you can install the DBI/DBD modules, using the CPAN interface.

Start the shop with flat files

Install the shop the regular way, testing all the functions, place orders, use the database manager, get familiar with the basic functionality of the shop. We recommend not to rename the "city" shop to anything else, since it is easier to get support using the standard version.

Define your database structure

Open the ShopDBSetup.pm, and look through the example of the shop tables. They all point to the database "city" and are defined in the global hash %TableConfig. If you choose to use a different database, named for example: "mydb", please update here the hash keys, like:

%TableConfig = (
  'mydb.product' => {
    -TABLE_DESCRIPTION => qq~
      ProductID VARCHAR(16) NOT NULL,
      ...

This library is used by the setupdb.cgi, to create the tables in the database(s), and transfer the data from the flat files to the SQL. Feel free to experiment and keep as a backup the flat files, until you are sure that you are up and running, and everything works.

Update your shop setup file

Open the ShopSetup.pm and scroll down to the end of the file.

First thing you have to do, is to update the database definition hash, as in the example:

$self->{-DATABASES} = {
  'mydb' => {
    'mydb' => {
      -DB_NAME   => 'mydb',
      -DB_TYPE   => 'dbi:mysql',
      -DB_PARAMS => {
        PrintError  => 0, # we handle the errors in the DBLib.pm
        RaiseError  => 0,
        LongReadLen => 128 * 16384,
      },
    -DB_HOST            => '',
    -DB_PORT            => '',
    -USER_ID            => 'myuserid',
    -USER_PASSWORD      => 'mypassword',
    -DEFAULT_TABLE_TYPE => 'MyISAM',
  },
};

Then, you will find next the $DBITableMap assignment, where all the shop table names are translated to SQL table names. You need to update here too, the correct database name, like in the example:

my $DBITableMap = $self->{-DBI_TABLE_MAP} = {
  'categories' => 'DBI.mydb.categories',
  'upsell'     => 'DBI.mydb.upsell',
  ...

Then, scroll up a bit, in the main hash definition, right before the end, there is a key holding an array reference of tables to be migrated. In our example we migrated almost all the tables:

 -DB_TABLES_IN_TRANSITION => [qw(categories upsell usercreditcard          
  orderdata options discount states accountdata product order coupons
  accountuserdata manufacturers promotions manageruser userbilling 
  user processing countries paymentdata usershipping stock)],

If you plan keeping some tables in flat file format, just remove their names from the array. We have to point here, that these names are shop table names, and not the database table names.

Transfer the tables to SQL

Run the setupdb.cgi, with the parameter shop set to your shop name:

http://www.mydomain.com/cgi-bin/store/setupdb.cgi?&shop=city

This script will create the tables and transfer the data from the flat files, to the SQL database. Before creating the tables, the eventually existing tables will be deleted, therefore once you are done with it, remove the setupdb.cgi from your script root!

Discussion on method and performance issues

We choose this method to migrate to SQL because in most cases all you get from your ISP is a database user and a password, some times not even set up correctly. In some cases, you might have quite an experience, until you are all set up.

Asking the ISP to set your tables is not an option, since you might want to change that or play with it until you get where you want, so you need full control on your database.

If you run SQL, we recommend installing, or require to have installed mod_perl, since this is the only way your script will be fast. Here is an example, how this timing is calculated:

In the case of standard perl, a typical click results in querying about 4 files, which will take some 0.3 seconds, added to the 1 to 2 seconds to load the Perl interpreter. If you use DBI/SQL, you do not open files, but you open a database connection instead, which takes about the same amount of time. We are safe then to assume that the server side of things will put a load on your web server for 1.3 till 2.3 seconds, with every click.

If you install mod_perl, the script modules are loaded, parsed, and kept in memory for the whole time the web server is loaded. If you are uploading stuff and testing it, we recommend to use the non-mod_perl until your shop is stable, cause in order to make the shop aware of the changes, you need to restart the web server, most of the times your ISP will not smile at you for that. On the bright side of things, your time per click gets down considerably. For example, the flat-file model with 50 items (demo shop) will take 0.2 seconds, while the SQL model will take 0.062 seconds(62 miliseconds). If you have a flat file database with over 1000 items, your timing for flat files will skip over the 1 second mark, while the SQL model will still stay around 0.08 seconds (80 miliseconds).

It is safe to assume, that if you use mod_perl and SQL, you can provide at least 15 clicks per second, competing easily with other professional ecommerce solutions. Of course, this timing depends on the machine parameters, and could be worse if you share one machine with other processor-intensive applications. Our benchmarks were reported on a 800MHz PIII running Apache and MySQL on Win2000, but we saw better results on BSDi or Linux.