Quick Start | MySQL | Document | Layouts | Searches | Browse | Sharing





MySQL requirements
In order to work with your MySQL database, SQLTouch application requires that your MySQL database fits some requirements. They are just a few. We have added here some recommendation and given you some trick on how to create a MySQL database on your server and how to get the best performance from it.
• Create a database
You can connect SQLTouch only to an existing MySQL database on your server. SQLTouch doesn't create the database for you. So if you don't have yet a database on your server you have to create it.

Usually the providers grant free MySQL databases on your hosting plan. Verify your provider grants that. If so you can easily create a database using the tools your provider features to you. Usually you should login on your host and go the the control panel. Then go to the MySQL area or to the Database area and create your own database from there.

You will be asked to choose a database name, an username and a password. The process will automatically create the database and usually will return you 3 usernames/passwords for each database created. If so, if you chose an username e.g. john, you will get an username/password with read-only privileges, e.g. john_r. An username with read/write privileges, e.g. john_w and an username with full privileges e.g. john himself.

Each username has his own password. Therefore if you login to your database with the username john_r you will be able to read-only the records and will be not able to modify the records nor to delete nor add new records.

If you login to your database with the username john_w you will be able to read, modify, delete and add new records.

If you login to your database with the username john you will be able to read, modify, delete and add new records and furthermore you will be able to modify the database structure, adding and deleting tables and fields. SQLTouch doesn't grant you these options but you can quite do that with other softwares like e.g. the freeware Sequel Pro or on the web, using the myPhpAdmin web software granted by your provider.

Together with the usernames, you should be shown the host address (e.g. dbserver.mydomain.com), the port number (if not specified, usually the 3306).

Once you have created your own database on your server, please be sure it fits the requirements as described here below.
• Requirements

• The MySQL database must exist on your server. If not, please consult the previous chapter "Create a database"

• The 5 parameters you need to define on SQLTouch in order to connect to your database (host, port, username, password and database name) must concern existing objects: the host must exist, the port (usually the 3306) must be available, the user must exist and the remote MySQL database must exists too. If you don't know all of these parameters please ask your provider.

• Please note that the host parameter is something different than your http web site address.


• Structure
• The database must contain at least one table.
• Each table must contain at least one field.
• Each table must contain at least one ID field. You can name this field as you like (even "ID"). This field type must be integer or long integer, with auto-increment and indexed as primary. This way SQLTouch can univoquely identify the record to edit or delete through this field.


• Permissions
• Be sure the user (defined by the username parameter) has enough permissions to select the tables, perform queries on the fields, add or delete records, as expected.
• If you have the root permissions on your server (so this means that you are the IT guy of your company), you can even assign to each user a given set of privileges for each table and for each field. For example you can set a table as not selectable (not searchable) by the users "customer". Or you can set a field to be unmodifiable by a given user. To do that you should grant or not to grant the privileges

SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES

We can't enter into these long long details here, so we invite you to consult the MySQL guidelines.


• Indexes
If you are going to perform a search within a given field, this field must be indexed. The indexes improve the search speed dramatically. So you must always index the fields you will be searching in. You can choose a "simple index" or, if your field must contain an unique value, e.g. a client code, you must choose a "unique index". And if the field is the "unique record identifier" within the table, you must choose a "primary index". As already specified above, a table must always contain a field, integer or long integer, with auto-increment and with a primary index. This field will be considered as the "Record ID" field.


• Text
You can define the text fields with the type varchar, with any length. For example varchar(1024). Please keep in mind that the iPhone is not fast as a Mac at downloading data, so you should always minimize the data to download. You can freely choose the collation (the string encoding) anyway we suggest to use utf8_general_ci.
While on the list view you can actually display a field with multiple rows, on the form layout the text fields display one only row (we will improve that on the next versions). To show the whole content of a long text field on the Form layout, on SQLTouch you should assign a button to that text field (or add a new independent button) and set its action to "Show Long Text" then choose this text field as parameter.


• Numbers
You can define numeric fields using all the integer, decimal and float types. If you choose decimal, you can even specify the number of digits to display after the dot.


• Dates
You can define the date fields using the date, datetime, time and timestamps types.
The date format must always be yyyy-MM-dd (e.g. 2009-12-31).
The time format must always be HH:mm:ss (e.g. 23:59:59).
In case of datetime you should enter a value as yyyy-MM-dd HH:mm:ss


• Images
The image field type must be a Blob (tynyBlob, mediumBlob or longBlob) and the images stored into the field must be jpg, png, gif or tif. To fill the iPhone screen you should use images with size 320 x 480 or 480 x 320. If you are going to show a small thumbnail on the List layout we suggest you to create an extra field called e.g. thumbnail containing a small version of the big image you will show on the Form layout. So don't put on the List layout the big image field with a smaller size. If you do so, SQLTouch will download all the big images in List and will take a long long time to display the results. Instead if you do as we say, SQLTouch will download all the small thumbnails when displaying the list, and will download the single big image only if the user will display that record on the Form view.


• PDF
The pdf fields must be text fields containing the URL of the pdf file.
To show a pdf image on SQLTouch you should assign a button to a field (or add a new independent button) and set its action to "Show PDF" then choose this pdf field as parameter.


• Movies
The movies fields must be text fields containing the URL of the movie file. The movie file should be a QT movie of kind m4v or mp4. To play a movie on SQLTouch you should assign a button to a field (or add a new independent button) and set its action to "Play Movie" then choose this movie field as URL parameter.


• BOOL
The type to define a boolean must be a tinyint with lenght 1. Shortly tinyint(1). If you define it so, SQLTouch will show you a YES/NO text when browsing this field and a YES/NO switch when editing this field.


You can download the demo suite "Demo Products" from our web site. It contains the MySQL database file "sql" we currently use for our "Demo Products" bookmark on the SQLTouch app. You could create a new db on your server then import this sql file to get an exact copy of tables, fields and contents we are using in our demo. Also, the suite contains a php file linked in the SQLTouch document in the field "Banner List" which generates the XML text to send back to SQLTouch.

SqlTouch ©2009 - All rights reserved - Web site: http://www.sqltouch.com