ArtenSPEAK Freeware

I'm on holiday and it's raining, almost constantly. So I sat down and created ArtenSPEAK. ArtenSPEAK is a simple utilty that speaks text. I have used it to read webpages and emails. Just drag or paste text into ArtenSPEAK and click the 'loudspeaker' icon, sit back and listen :-)

ArtenSPEAK is available for download here.

The window size, position and transparency can be set and is remembered by the application.

This version is for MacOSX only, i'll upload the Windows version when I get home next week.

Enjoy :-)

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 16: More SQL Comments

ArtenSUITE
A Development Journal
Part 16: More SQL Comments

In an earlier article I wrote about how I prefix every SQL statement with the Module, User ID and Date/Time. I do this by calling a function which sets these values and prefixes them as a comment to the beginning of the SQL statement.

The reason I do this is that I can view this information from within the application (by logging to a text file and then using a View SQL Window), but also this information is seen when looking at the statements in the database. Unfortunately from Oracle 10g these SQL comments are removed and not visible from within the database.

The way around this is to use the Hint syntax /*+ ... */. As you are not using valid hints they are ignored by Oracle, but not stripped from the SQL statement so are visible within the database. An example is shown below:

/*+ ArtenBASE / SJC / 1909208-11:00 */ select count(1) from bas_con_main

UPDATE: It turns out that using the above syntax causes each statement to be viewed as 'different' by the Oracle optimiser. Therefore any benefits of bind variables and caching are lost. This method can only be recommended for use during pre-production / development.

It seems there isn't a way (or at least, I haven't yet found one) in 10g to tag or comment the SQL statement in such a way that the comment is accessible from the database, as opposed to just within your application. Hence any logging of SQL statements with their associated comments must use the /* ... */ tags and be done on the client side.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 15: Multi Language Support

ArtenSUITE
A Development Journal
Part 15: Multi Language Support

One of the original design ideas for ArtenSUITE was to develop the application in such a way that conversion to a different (non English) language was straightforward. As it was I got carried away with other (more exciting) things and that idea fell by the wayside. Realising the error of my ways, I started to rectify that today.

The first thing I did was create two modules, mStrings, for storing the constants that will contain all the text that the user will see in message and dialog boxes. The second module mUIText, for storing the text elements for the UI controls, ie: Captions, Names etc. These two modules are stored within a Localisation folder inside my Project.

The constants are named in the following way:

kError = "An Error Occurred ..."
kDBConnectOK = "You Successfully Connected to the Database ..."
etc.

Real examples can be seen in the following screenshot taken from my project:



I then went through my application replacing the text with the name of the appropriate constant. Interestingly enough if you are fairly consistent with your messages you will be surprised how many times you use the same type of message, meaning that the amount of constants required is probably smaller than you imagine.

It's important to keep the formatting and spacing within your code and not move that to constants. For example if your code says something like:

Message = "An Error Has Occurred: " + "Error Number: " + intErrNum

You would make the text into constants but leave the spacing in the Message string. It would look like this:

Message = mStrings.kErrorOccurred + " " +mStrings.kErrorNum + " " + intErrNum

The main reason for this is if you decide to implement another language and send an export of your modules to be translated there is a good chance the spaces will get 'lost' along the way.

So assuming all your text that will be seen by the user is contained inside a couple of modules all you need to do to implement another language is export these modules, get them translated, then re-import them and assign a language to them. Bingo! Multiple Language Support with the added bonus that it makes your code slicker and more maintainable.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 14: Network Messaging

ArtenSUITE
A Development Journal
Part 14: Network Messaging

I’ve built simple network messaging into the ArtenSUITE framework. Using UDP (a connectionless transport) I am able to detect and communicate with all other copies of my software running on the local network. This offers me several advantages.

Licensing
Using this facility I am able to detect how many copies of the software are running concurrently, and compare that to the license owned by the customer. This is a fairly foolproof method of ensuring that license limits are not breached and the customer is then likely to purchase additional licenses if they run into this restriction often.

Messaging
The user can use this facility to send messages directly to another user, or group of users. A sort of simple ‘Group Chat’. My software can also use this mechanism to sent messages to both itself and also other copies of itself running elsewhere on the network. Some Interesting possibilities here.

Commands
Messages are send with an identifier, currently set to 0 this is interpreted as a simple message, and is display as such. I can however send messages with a different identifier which can be processed as a command on the remote machine. For example it could be used to force the remote software to perform a particular action, such as quit :-) This again offers interesting possibilities.


I’m not sure yet how to present this messaging interface to the user, currently I am using the screen shown below:



Any messages received by the application will select the Incoming Tab and display the latest message on the screen. There is no annoying ‘popup’ dialog, although if the speech interface is enabled in the preferences, the message will be announced, if not then a simple beep will be heard by the user.

I haven’t yet created any code to resolve the IP address, I am considering writing the IP Address to the user record at logon and then displaying the user name as logged into the system, rather than resolve the IP Address to the computer name.

There’s lots I can do with this, but the framework is in place :-)

"Never fear the want of business. A man who qualifies himself well for his calling, never fails of employment." - Thomas Jefferson

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 13: Splash Screens and Transparency

ArtenSUITE
A Development Journal
Part 13: Splash Screens and Transparency

I’ve spent a few hours working on the splash screen for my application. What I’ve done now is create an irregularly shaped window, based on my company logo. This can be seen in the screenshot below:



As the image for the splash screen is read from the disk and then rendered in real time, if there is any sort of error it instead opens the original splash screen, kind of a ‘Plan B’ :-)



I’ve also been working with transparency. People tend to have strong views on whether transparent windows are a good thing or a bad thing, whatever, I like to give people choice. Therefore each window within ArtenSUITE can have its own level of transparency set at runtime. This transparency level is then written to the local cache database along with the window size and position, and is then used next time the window is opened. This seems a good solution to me, allowing more windows to be open and viewable by varying the window transparency. The transparency level is set using an screen slider situated at the bottom of each window. An example on MacOSX shown below and below that is an example from Windows Vista:






"See things as they are and write about them. Don’t waste your creative energy trying to make things up. Even if you are writing fiction, write the things you see and know." - Real Live Preacher

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 12: Window Size and Positions

ArtenSUITE
A Development Journal
Part 12: Window Size and Positions

One of the things that really annoys me with a lot of software is that the window size and positions reset to the developers defaults every time the program is run. I have 2 Monitors, a 30’‘ and a 20’‘ and I like to use that space and work with a particular layout. ArtenSUITE has been designed from Day 1 to allow the storing of window size and position, for all windows, stored locally for each user.

For programs I write that use only one window I write and read the window size from a structured text file. For ArtenSUITE however which will have many windows it was necessary to use a different approach.

Each time the program is started it looks for a REALSQL datafile in the program directory. If it doesn’t find one it creates one and within the database it creates the table and index necessary to store the window positions.

REALSQL is a version of SQLite that comes with REALbasic Professional and is ideal for use as a local data store, or cache. Note: REALSQL can also be upgraded to a full blown multi-user database (REAL SQL Server)if an appropriate license is obtained, and the the code you have used to access the local version can be used on the full version without alteration.

The Code to create the Local Database, and also create the Schema and Index is as follows:

Creating the Local Database, Window Size Schema and Index
Dim f as FolderItem
f = GetFolderItem(mCodeBaseGV.pLocalCacheFileName)

dim strCreateWinDatabase as String = "CREATE TABLE csh_win_main (win_name VARCHAR(20) NOT NULL, win_height INTEGER NOT NULL, win_width INTEGER NOT NULL, win_left INTEGER NOT NULL, win_top INTEGER NOT NULL)"
dim strCreateWinIndex as String = "CREATE INDEX win_name ON csh_win_main (win_name)"

if f.exists Then
__'Do Nothing
Else
____'Create Local Database Cache
____dim dbRBLocal as REALSQLDatabase
____dim folTarget as FolderItem
____dim booResult as Boolean

____dbRBLocal = New REALSQLDatabase
____folTarget = GetFolderItem(mCodeBaseGV.pLocalCacheFileName)

____dbRBLocal.DatabaseFile = folTarget
____booResult = dbRBLocal.CreateDatabaseFile

____if booResult = True then
______'Create Schema
______dbRBLocal.SQLExecute(strCreateWinDatabase)
______dbRBLocal.SQLExecute(strCreateWinIndex)
______dbRBLocal.Close
____else
______mCodeBaseStd.sDisplayAndLogErrorMessage(1003,"Local Cache Database NOT Created ..." + EndOfLine + dbRBLocal.ErrorMessage,"Local Cache Database NOT Created ... " + dbRBLocal.ErrorMessage)
____end if
End if

Setting and Getting the Window Size
Each window in ArtenSUITE has two methods, sGetWindowPosition and sSet WindowPosition. The Get is called by the window Open event and the Set is called by the window Close event.

The method sSetWindowPosition accepts the following as parameters:

strWindowName
intHeight
intWidth
intLeft
intTop

The method looks for a record in the local database that matches strWindowName, if a record is found it is updated using the parameters intHeight, intWidth, intLeft and intTop. If a record doesn’t exist for that window then one is created using the parameters as input.

The method sGetWindowPosition accepts the following as parameters:

strWindowName

It then returns the window size and position as integers. If a particular window does not allow resizing, only moving, then the parameters for Height and Width are ignored and only the parameters for Top and Left are used.

"Politeness and consideration for others is like investing pennies and getting dollars back." - Thomas Sowell

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 11: Sending / Logging SQL

ArtenSUITE
A Development Journal
Part 11: Sending / Logging SQL

Like in the previous article about Error and Message Handling, getting a mechanism in place to make it easy and simple to code SQL against the database is well worth the initial effort. Mixing SQL statements in with lines of program logic is messy, difficult to read, difficult to refactor and inelegant. I have in the past often done it that way, so I know :-)

For ArtenSUITE I have taken a much more structured approach.

When creating a SQL Query there are two things you need to do as a developer when working with this method. First you call the method that will contain your query, in this example the query is a function signified by the method starting with an ‘f’. This function will return a boolean. The objective of this SQL Query is to establish if a company record exists in the database. The function call is shown below, highlighted in the program code:

'Check if Company Record Exists, if Not - Probably a New Install
dim booCompanyRecordExists as Boolean = mCodeBaseDB.fSQLChkCompanyRecordExists
if booCompanyRecordExists = True then
__'Allow Logon to Database
__Dim wILogon as wLogon
__wILogon=New wLogon
else
__ ...

The second part of your job as as a developer is to write the function. I am using a seperate function or method for almost every SQL Query. It’s surprising how many times we reuse the same SQL, and as I said earlier I don’t like mixing the SQL code with the rest of the business code. (I will soon be adding the passing of parameters to certain SQL methods to cater for similar, but not the same queries) The function fSQLChkCompanyRecordExists is shown below:

dim rec as JavaRecordSetMBS = new JavaRecordSetMBS
dim strQuery as String = mCodeBaseStd.fCreateSQLComment + "SELECT count(*) FROM bas_com_main"
mCodeBaseSTD.sOutputSQLToFile(strQuery)

rec =pDBOracle.SelectSQL(strQuery,TRUE)
if rec<>Nil then 'Query is Valid
__dim intCount as Integer
__while rec.NextRecord
____intCount= val(rec.getString("count(*)"))
__wend
__if intCount > 0 then
____Return True
__else
____Return False
__end if
else 'Query is Invalid
__mCodebaseErr.sInvalidQuery(strQuery)
end if

Basically you can cut and paste your SQL function, rename it, modify slightly it and reuse for the next query. I could have done this differently and written a single method to handle every query and used the SQL string as a parameter. I’ve done that before and this time I decided to do it this way, more code but easier to read and debug without the amount of parameters that are needed to suit all circumstances.

There are a couple of useful things about this function, the first is on line 2 where mCodeBaseStd.fCreateSQLComment is called. This function returns a string which is appended to the beginning of the SQL Query as a comment. The contents of the string are: Module - UserID - DateTime. As this is appended to the beginning of the SQL Query it is available to view and track from within the database, extremely useful when troubleshooting.

Combined with the second interesting feature it makes even more sense. Line 3 is a procedures that outputs every SQL Query to a structured file on the users local computer. It provides a complete session history. When the program is relaunched the file is deleted and a new Session History file is started. Before deleting the file it is copied and stored as ‘Previous_’. Again, very useful when troubleshooting.

I also give the user the ability to view the contents of their own SQL Session History from within the application, using the SQLView window, shown below:



"You should not live one way in private, another in public." - Publilius Syrus

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 10: Errors and Message Handling

ArtenSUITE
A Development Journal
Part 10: Errors and Message Handling

Errors and Message Handling is an important part of your application. I believe it is necessary to create a framework or standard way of dealing with and displaying and logging errors and messages within your application.

General Messages

I have four kinds of messages that I can display to the user, these are INFORMATION, WARNING, STOP and ERROR. Each error message is handled by a method, the explanation for the message is sent as a parameter to the message method. This is shown below via code and then message image:

mCodeBaseStd.sDisplayInformationMessage("You Are Entering a Secure Module, All Actions Will be Logged ...")



The method mCodeBaseStd.sDisplayInformationMessage looks like this:

dim popup as New MessageDialog
dim result as MessageDialogButton
popup.icon = MessageDialog.GraphicNote
popup.Message = mProgramInfo.pProgramName + ": " + "INFORMATION:"
popup.Explanation = strMessage
result = popup.ShowModal

A icon appropriate to an information message is used and the module name is shown before the word ‘INFORMATION’. All Information messages will be displayed identically, the same goes for the other types of message. This gives a clean, consistent look to your application.


Error Messages

Error messages are handled differently. They are displayed to the user in the same way but there is more to them.

1. All My Error Messages have a Message Number


2. Error Message Numbers between 1000 and 2000 are classed as Priority 1, and this is shown to the user


3. Any Error Description or Number retrieved from the system or database is passed to my Error Display Method


4. An appropriate Icon is used to depict an error


5. All Errors are written out to a text file with date and time and module information


6. All Errors are handled by their own unique method, within the module mCodeBaseErr

When the error occurs the error method is called as such:

mCodebaseErr.sCannotConnectToDB("Could Not Connect to Oracle, The Database is Unreachable ... / Error Code: "+str(pDBOracle.Errorcode))



mCodebaseErr.sCannotConnectToDB looks like this:

dim intErrorNumber as Integer = 1001
dim strErrorMessage as String = "Cannot Connect to Database:"
dim strErrorMessageDisplay as String = strErrorMessage + EndOfLine + strMessage
dim strErrorMessageLog as String = strErrorMessage + " " + strMessage
mCodeBaseStd.sDisplayAndLogErrorMessage(intErrorNumber,strErrorMessageDisplay,strErrorMessageLog)

and mCodeBaseStd.sDisplayAndLogErrorMessage looks like this:

dim popup as New MessageDialog
dim result as MessageDialogButton
popup.icon = MessageDialog.GraphicCaution
popup.Message = mProgramInfo.pProgramName + ": " + "ERROR DETECTED:"
if intErrorNumber >=1000 and intErrorNumber <=1999 then
__popup.Explanation = "Number: " + str(intErrorNumber) +" (Priority 1 Error: Contact Support)" + EndOfLine+ strErrorMessageDisplay
else
__popup.Explanation = "Number: " + str(intErrorNumber) + EndOfLine+ strErrorMessageDisplay
end if
result = popup.ShowModal
mCodebaseStd.sOutputErrorToFile(intErrorNumber,strErrorMessageLog)

with the final line of the method calling another method which outputs the error to the logfile. This method is shown below:

Dim f as FolderItem
Dim t as TextOutputStream
dim strDate as String = mCodeBaseDB.fSQLFetchSystemDateTime
'Open File Stream
f = GetFolderItem(mCodeBaseGV.pErrorLogFileName)
t = f.AppendToTextFile
'Write to the File
t.WriteLine "<ErrorDate>" + strDate + "</ErrorDate>"
t.WriteLine "<ErrorNumber>" + str(intErrorNumber) + "</ErrorNumber>"
t.WriteLine "<ErrorMessage>" + strErrorMessage + "</ErrorMessage>"
'Close the File
t.Close

It may seem that my procedure for dealing with errors is complicated. In use it is actually quite simple. I pay a lot of respect to errors and so each one is given their own number and method within mCodeBaseErr, this is the method that is called when the error occurs. The call to output the error to the screen and subsequently the log file is handled automatically, so once I had setup this structure it has been easy for me to build solid handling of errors into my application.

"Anything that has real and lasting value is always a gift from within." - Franz Kafka

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 9: Startup Sequence

ArtenSUITE
A Development Journal
Part 9: Startup Sequence

I’ve spent a few hours last night and this morning working on refining the startup and database logon procedure for ArtenSUITE. There are several complications and several paths with different outcomes and it is easy to end up with a convoluted, difficult to follow startup procedure. At the moment I am fairly happy with the startup procedure which works like this:

The app.Open Event is automatically called when the application launches. Within this method I have called a startup method which does the following by calling individual methods from within either the mCodeBaseSTD or mCodeBaseDB modules:

Displays a Splash Screen
Registers my MBS Plugins
Sets a property to show which platform the application is currently running on
Sets properties with the pathname to the Preferences file, the Errorlog file and the Sqlsession file
Sets properties containing network information, IP Address, Subnet, MAC Address Etc.
Checks for the existence of the Preferences file, Sets a Boolean property accordingly
if PrefsFileExists = TRUE
__Attempt to connect to the database
__if ConnectedProperty = True
____Enable the Continue Button on the Splash Screen

__if ConnectedProperty = False
____Display an Error Message



____Display the Preferences Window for Input
____Close Application

if PrefsFileExists = FALSE
__Display the Preferences Window for Input
__Close Application

The Splash screen, early version shown below, is displayed and when the ‘Continue’ button is depressed an event is triggered by the control which performs the following actions:



Check if a Default Company Record exists in the Database
If CompanyRecordExists,
__Close the Splash Window and Open the Logon Window for ID and Password Input



If CompanyRecordNOTExists then assume a new company needs setting up and open New Company Input Window
__Close Application

This sequence of events may well be refactored in the future but for now they logical, simple and work well :-)

"Even the most dangerous look less threatening when they are dead." - Laura Moncur

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 8: NLS ...

ArtenSUITE
A Development Journal
Part 8: NLS ...

After installing Oracle it’s important to setup the variables NLS_LANG and NLS_DATE_FORMAT. These variables determine the default settings that Oracle uses for Date formatting, Currency formatting, Sort Orders etc. More information is available here.

If Oracle is installed on a Windows server the NLS_LANG can be as an environment variable using the System Control Panel. If you are in the UK the settings you will want are as follows:

Key: NLS_LANG
Value: ENGLISH_UNITED KINGDOM.WE8ISO8859P1

NLS_DATE_FORMAT has to be set on a session by session basis, so you can either put it in the startup procedure for your application as such:

alter session set NLS_DATE_FORMAT = ‘dd/mm/yyyy’;

or you can implement it as a database trigger:

CREATE OR REPLACE TRIGGER "DATA_LOGON_TRIGGER"
after logon
ON DATABASE
begin
execute immediate
'alter session set nls_date_format = ''ddmmyyyy'' ';
end;
/
ALTER TRIGGER "DATA_LOGON_TRIGGER" ENABLE
/

"The man who has nothing to boast of but his illustrious ancestry is like the potato - the best part under ground." - Thomas Overbury

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 7: Triggers on Delete

ArtenSUITE
A Development Journal
Part 7: Triggers on Delete

For ArtenSUITE I intend to make far greater use of the capabilities of the database than I ever have previously. In the past I have often ignored Triggers and Database Constraints in favour of keeping the rules within my applications. This has been relatively successful, especially when developing software that is going to be used within a controlled environment such as a single corporation.

Software that intends to be unleashed to the masses however needs more control putting into the database where it can stop the Smart Alec with the password and a SQL tool from deleting or modifying data directly, completely bypassing the controls within the application.

So this evening I have been putting some Triggers into my database.

Against each Database Table is a Column called LOCKED. So for example the Main User Table, BAS_USR_MAIN would have a column called USR_LOCKED. If the contents of the LOCKED column equal 1, then the record cannot be deleted. Ever. Under any circumstances. Mostly. This is useful for stopping the Master User ( the Main Admin User that is setup automatically when the system is first used) from deleting his own entry and therefore locking himself out of the system. There are lots of circumstances, in all kinds of tables, where having the LOCKED column be beneficial.

I therefore needed a Trigger to execute BEFORE the row was to be deleted and stop the delete happening if the LOCKED flag was set. This is very easy to accomplish in Oracle. The SQL I used is shown below:



What is more useful about this Trigger is that I have used the RAISE_APPLICATION_ERROR process to pass a user defined error number and message back to the application that attempted to perform the delete. The user defined error numbers can be between -20000 and -20999, the other numbers are reserved by Oracle for their own purposes. My application can check for this error code when a delete is attempted and then display a nicely formatted error message that explains precisely why the record could not be deleted. The message description I have specified in the script is the one that will be seen when an attempt to delete is made from outside the control of my application.

An example of this can be seen in the screenshot below. I logged onto SQLPlus from my Mac and attempted to delete the contents of a table (one record of which) is protected by the Trigger shown earlier. The screenshot shows the results of the attempted delete.



I only have one record in this table at the moment but if there was more than one record the SQL command I issued would have deleted all of the other rows successfully except for the one marked as LOCKED. This is down to my use of the ‘FOR EACH ROW’ statement in the Trigger.

"Self-development is a higher duty than self-sacrifice." - Elizabeth Cady Stanton

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 6: Pricing

ArtenSUITE
A Development Journal
Part 6: Pricing

This is not an article about pricing a software product. This is an article about the design of a flexible and powerful customer/product pricing ‘system’ that I intend to implement within the sales order / invoicing module of ArtenSUITE.

(Table and Column names are examples only)

Having written and used and worked with many MIS / ERP / Finance systems over the years I have never created or seen one that handled pricing in a way that I was 100% happy with. This article covers my current thoughts regarding a pricing ‘system’ that I will tweak and modify mentally for a while before I commit anything to code. 

ArtenSUITE is a long term project and I am only just starting with the design and creation of the base module.  There is no pressing deadline so I have the time to think carefully about each portion of the product and try and make it the best it can be.

Stage 1
Each line of the ORDER_LINE table will be checked against the PRODUCT_PRICE table. The PRODUCT_PRICE table looks like this:

Primary Key
Insert Date
Product Table PK
Start Date
End Date
Price

Using the SYSDATE (or similar) function the PRODUCT_PRICE table is queried to find the price that is currently applicable.  The latest record inserted in which SYSDATE is between Start Date and End Date is used to determine the price.

This means that price adjustments, such as a yearly increase, can be created and stored in the database well in advance and yet not affect any orders until the date arrives for the scheduled pricing update.  

It also means that you could do a ‘Special July Offer - All Prices Slashed’ type sale just by inserting a new set of pricing records with the Start and End dates being the beginning and end of July respectively.

A desirable side effect of this method is that a permanent pricing history is always available. Note: If a record is not found then a value of 0.00 will be inserted as the price.


Stage 2
The DISCOUNT column that is stored against the CUSTOMER record.  This column represents the standard discount that will be applied for this customer for every purchase.

The price of  each entry in the ORDERLINE table will be reduced by the percentage stored against the customer.  This covers the scenario where different customers pay a different price dependent on whether they are an end user, distributor, dealer etc.


Stage 3
This stage covers the scenario whereby you wish to give the customer a further discount either as a percentage or a currency value, based on their turnover.  This turnover can be either rolling X periods, where the period can be either day, week or month or alternatively the turnover can be between a Start and End date, maybe the Start and End of your financial year ? These turnover values could be derived by applying a SUM function on (Invoices - Credit Notes) for example.

This works as follows:  Once the customer discount has been applied the CUSTOMER_TURNOVER_DISCOUNT table is queried.  This table looks similar to this:

Primary Key

Customer PK
Type (1 = Rolling, 2 = Static)
Number of Rolling Periods
Period Type (Day / Week / Month)
Start Date (For Use if Static)
End Date (For Use if Static)
Discount (Positive Discount = %, Negative Discount = Currency Value)
Threshold (Turnover Value)

The system looks first for ROLLING records for the customer.  The latest record inserted is used to calculate the additional discount percentage or value to be applied to the product price. For example the customer may receive an extra 2% discount if their turnover in any given rolling quarter is more than £10000.  The record would look like this:

Primary Key = 123789

Customer PK = 'SJC001'
Type = 1
Number of Rolling Periods = 3
Period Type = Month
Start Date = Null
End Date = Null
Discount = 2.00
Threshold = 10000

The system then looks for STATIC records for the customer.  The latest record inserted is used to calculate the additional discount percentage or value to be applied to the product price. For example the customer may receive an extra 3% discount if their turnover this financial year is more than £150000.  The record would look like this:

Primary Key = 123790

Customer PK = 'SJC001'
Type = 2
Number of Rolling Periods = Null
Period Type = Null
Start Date = ‘01 APR 2008’
End Date = ‘31 MAR 2009’
Discount = 3.00
Threshold = 150000

So you can give your customer a discount incentive for both rolling turnover and static turnover, or just one or the other. The key is flexibility.  Note: If a CUSTOMER_TURNOVER_DISCOUNT record is not found, or the accumulated turnover did not reach the predetermined levels, then no additional discount is applied at this stage.


Stage 4
The final stage is where you may wish to give additional discount to a particular customer when they purchase a particular product, or when they purchase a particular quantity (or more) of a particular product.

This works by querying a Customer / Product Matrix table.  The CUST_PROD_MATRIX table looks like this:

Primary Key
Customer PK
Product PK
Discount (Positive Discount = %, Negative Discount = Currency Value)
Quantity

The system checks for the existence of matrix records that match the customer and the product.  If it finds one (or more) it checks the quantity and selects the appropriate additional discount from the record with the highest quantity that is less than or equal to the quantity entered on the order line.

If a CUST_PROD_MATRIX record is not found then no additional discount is applied at this stage.


And thats it - so far.   It covers most pricing eventualities that I can recall at the moment.   It is at this stage theoretical and has not yet been implemented in code, although it will be and thankfully that is the easy bit :-)

Any feedback, queries etc. please feel free to leave a comment.

"After silence, that which comes nearest to expressing the inexpressible is music." - Aldous Huxley

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE Part 5: More Database and Table Design

ArtenSUITE
A Development Journal
Part 5: More Database and Table Design

No programming today. Working entirely on database design and creating tables, foreign key constraints, indexes etc. So far I have 17 tables designed and setup, these are some of the core tables that will be needed by most business applications that I create as part of ArtenSUITE. The screenshot below shows the 17 tables:



Comments

As you can see I have used the Oracle ‘Table Comment’ feature to put a store a plain english description of the table in the database. This is done so rarely, and many times when working with legacy Oracle systems I have *really* wished more developers/DBA’s took advantage of this feature. Properly commented tables and columns mean there is often no need to refer to a data dictionary or technical document to understand the database structure of a system you have never seen before.

To use the comment feature in Oracle you use the following syntax:

SQL> comment on table TABLE is ‘TABLE DESCRIPTION’;

Comments can also be applied to views and even the columns in tables. Comments are applied to views using exactly the same syntax as tables (shown above). To apply a comment to a particular table column, use the following:

SQL> comment on column TABLE.COLUMN is ‘COLUMN DESCRIPTION’;

Security

Access to most facilities within ArtenSUITE will be able to be restricted to particular personnel. The design of this is one that I have used very successfully in the past. A table exists which contains the following information (select columns only shown)

        Security Action ID
        Security Action Description
        User Primary Key

Each section of the program that needs to be secure has a ‘Security Action ID’ assigned to it. When the menu is selected or the button is clicked to perform the restricted action a call is made to a function with the parameters being the User Primary Key and the Security Action ID:

        call check_if_access_allowed(User Primary Key, Security Action ID)
        if return 1
                enter the method, access allowed
                ...

the function that performs the check looks like this:

        sub check_if_access_allowed
                select security_pk from security_table where user_pk = [User Primary Key] and security_action_id = [Security Action ID]
                if row returned from select
                        return 1
                else
                        Show Message “Action to [Security ID] - Not Granted. Please see the System Administrator for Access ...“
                        return 0
                end if
        end sub

To grant a particular user access to a particular action it is only necessary to create (or duplicate and edit) a security record. As a developer it is only necessary to prefix every ‘action’ with a security id and a call to the security procedure. If a customer needs a particular action to be secured in future they can enter the security record themselves.

Oracle XE and Oracle SQL Developer

I used the ‘Application Express’ interface for many of my tasks today and it is quite good. Easy and pleasant to use. I couldn’t however get the ‘Edit Triggers’ to work and so I switched to Oracle SQL Developer. After getting the latest version I was surprised just how much this product has improved recently. The last version I used was ... iffy to say the least. It has been tidied up, made faster and slicker. I like.

Archivelog Mode

One of the first things anybody should do with an Oracle database is switch on Archivelog mode. There is not really any good reason to run with Archivelog switched off (especially in a production environment). I’ve never had a problem with this before but with Oracle XE I couldn’t get the SQLPlus command line client to work as expected. After consulting the instructions I was doing what was expected but no luck. In the end I had to logon from the windows Command Shell as such:

        c:\>sqlplus /nolog

        SQL>connect sys/letmein@127.0.0.1:1521/XE as sysdba

I was then able to do the following to enable archivelog mode:

        SQL>shutdown immediate;
        SQL>startup mount;
        SQL>alter database archivelog;
        SQL>alter database open;

and that was that sorted.

Customer Tables

For my customer table designs I have a main customer table and a few additional tables that hold extra information such as bank details, notes, addresses etc. It is normalised but not to an excessive extent, 3NF give or take :-) A screenshot is shown below: (The diagram comes from Application Express and is pretty bad as it doesn’t even show the direction of the links. Anybody know any good diagramming software for Oracle ?)



Summary

That’s it for a week or so, vacation beckons :-) As always feel free to leave your comments and opinions.

"The radical of one century is the conservative of the next. The radical invents the views. When he has worn them out the conservative adopts them." - Mark Twain

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE: Part 4: Primary Keys

ArtenSUITE
A Development Journal
Part 4: Primary Keys

I had an email this morning from someone who asks me why I appear to be using surrogate keys rather than natural keys in my database design. A surrogate key is a key that is totally unrelated to the data in the table whereas a natural key would relate directly to the data in a table.

An example of a surrogate key:

Table: Customer
Surrogate Primary Key Value: 7878900
Customer Name Value: Bilbo Enterprises

An example of a natural key:

Table: Customer
Surrogate Primary Key Value: BILBO01
Customer Name Value: Bilbo Enterprises

One of reasons I use a surrogate key is that if Bilbo retires to Rivendell and leaves his business to Frodo, Frodo may well change the name of the company. So with a natural key the data now looks like this:

Table: Customer
Surrogate Primary Key Value: BILBO01
Customer Name Value: Frodo Enterprises

As BILBO01 is a primary key and may be related to other tables using this primary key it may not be straightforward to change. You therefore now have information that looks inconsistent, it offends my sense of order :-)

There are many advantages to natural keys (especially to do with reducing the complexity of some queries), but for me I prefer using surrogates. It’s a hotly debated subject. Research both and use what you feel comfortable with. Do everything else right and it makes no difference in the long run.

"Far and away the best prize that life offers is the chance to work hard at work worth doing." - Theodore Roosevelt

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business

ArtenSUITE: Part 3: Database Design

ArtenSUITE
A Development Journal
Part 3: Database Design

I spent most of today working on the database structures and design issues, deciding on what conventions I will be following regarding naming, table designs etc etc.

For example, as all the modules are designed to support multi companies, I have build a foreign key into each table which links to the company record. Access to the data from within the application modules will purely be based on the context of the company you wish to work with, rather than logging into a seperate database as is commonly done. Global constants will be stored with a record linked to the company record.

I also want to support simple features that users consider necessary but are not seen often in commercial applications. An example would be allowing the same contacts record to be used against both customers and suppliers, and then allowing that contact record to be moved to another customer or supplier if the individual changes jobs. This is a common requirement but is not widely supported.

Jeff Atwood recently blogged about database structures and whilst I agree with him to a certain extent, I will say that I much prefer to start with a solid normalised database design and only denormalise wherever *absolutely* necessary. This can lead to tables that look complicated. However I believe that creating appropriate views can help the database structure become much more meaningful when it’s time to report or query the data. I also disagree that normalised databases have performance issues ... of course there are times where a normalised database will perform slower due to the joins, but on the whole the quality and reliability of the database should make up for any performance shortcomings. Besides, there is normalisation and there is normalisation. I like to think that my database designs are optimum for the type of application that I am developing.

Other decisions I made today were to do with the data storage types for the database columns. For example each table will have a primary key, this primary key will be tied to an Oracle Sequence so that it is guaranteed to be unique and not null. I will use a Number type with a scale of 14 and precision of 0 to store this value. Customer and Supplier codes will be Varchar2 12, company names and address lines will be Varchar2 50, currency values will be Number with a scale of 12 and a precision of 2.

Tables will be named PRIMARYMODULE_TYPE_SUBTYPE for example the customer address table would be named BAS_CUS_ADDR. Where BAS means that the table is primarily used and maintained within the module BASE. Primary columns will named XXX_PK, customer primary key will therefore be CUS_PK. There are lots more rules and conventions but you get the general idea :-)

An idea I have implemented here is one that I have carried over into every database design for the last 15 or so years. It is probably not necessary nowadays but - belt and braces ! I have created an additional column in every table called (for example) CUS_999. This contains a copy of the primary key. The idea here is that this can be used to rebuild the relationships between the tables if the primary key every got corrupted. This has saved my bacon several times over the years ! It can’t do any harm and disk storage is cheap.

So lots of thinking and designing today. In addition I got to play more with Oracle XE. Really, if you have never tried it you should, it’s great. It’s simple to use, has a cracking browser based interface for setting up and managing the database and it installs quickly and simply. This does not feel like ‘traditional’ Oracle. I’m very impressed.

Aside from an hour or so trying to solve my Java on Windows issue (this is a real pain, I know the solution is simple and I’m just being stupid) the only other coding I have done has been to create a properties window for ArtenBASE. This is a useful view for the main properties of the application, especially when developing. Below is a screenshot of the properties window.



That’s enough for today. Tomorrow is my last day working before my holiday (yep, another one) this time I am staying in the country so will have iPhone, MacBook Pro and Vodafone Broadband Card so although I won’t be able to get much work done I can at least answer my emails.

Feel free to comment if you have any suggestions, observations, criticisms etc :-)

"Millions long for immortality who don't know what to do with themselves on a rainy Sunday afternoon." - Susan Ertz

ArtenSUITE: Part 2: Day One

ArtenSUITE
A Development Journal
Part 2: Day One

So yesterday I made a start on the ArtenBASE application which will be the central ‘hub’ for the ArtenSUITE applications. Within my REALbasic project I’ve organised it into folders as such:

        Codebase (core modules and classes, db access, autoupdate etc.)
        Pictures (images)
        Forms (windows)
        Modules (application specific modules and classes)
        Menus (application menu objects)

I have a standard module called mProgramInfo that contains properties that relate to the application: build, version, author etc. I modify these and import my standard ‘About’ window which uses these values. My about window for ArtenBASE looks like this:



I’ve created the methods needed to logon to Oracle and these are called automatically when the program is opened. I am using the Java Plugin from MBS to access the Oracle database. The logon method for Oracle is shown below:

'Requires the Oracle Java Driver Resident in the Program Directory
'(ojdbc14.jar)
'Requires the MBS REALbasic Java Plugin.rbx to be in the Plugins Directory of the IDE
'Requires the MBS REALbasic Main Plugin.rbx to be in the Plugins Directory of the IDE

'This Function Should be Called as Follows:
'mCodeBaseDB.OpenOracleDatabase("jdbc:oracle:thin:@//HostIPAddress:PortNumber/ORACLESID","username","password",ShowMessageInteger)

dim db as JavaDatabaseMBS
dim f as FolderItem

proDBConnector = "ojdbc14.jar"

f=GetFolderItem(proDBConnector)
if not f.Exists then
mCodebaseStd.DisplayStopMessage("Connector File Not Found in Application Directory ..."+EndOfLine+"Connector Name: "+proDBConnector)
Return
end if

db=new JavaDatabaseMBS(f,"oracle.jdbc.driver.OracleDriver")
db.Host=strHostName
db.UserName = strUserName
db.Password = strPassWord

if db.Connect then
mProgramInfo.pDBConnectStatus = True
db.AutoCommit = FALSE
if intShowMessage=1 then
mCodebaseStd.DisplayInformationMessage("Successfully Connected to Oracle ...")
end if
else
mProgramInfo.pDBConnectStatus = False
mCodebaseStd.DisplayStopMessage("Could Not Connect to Oracle ..."+EndOfLine+"Error Message: "+db.ErrorString+EndOfLine+"Error Code: "+str(db.Errorcode))
Return
end if


The first time the program is opened these methods will fail as the server logon details are undefined. With Oracle all database objects are owned by a user, in this case the user will always be ARTEN and the password will be encrypted and hard coded into the application. The Host IP and the Database Name are all that is specific to a particular environment. To define these details the preferences window is used:



Assuming the database connection was successfull the user will see the logon window. This is where they logon to the application database using a name and password stored in a (not yet created) table on the Oracle server. The logon window is shown below:



All screenshots are shown from the Mac OSX version as I am having a problem with the Java classes on Windows .... It’s one of those stupid problems I am sure and I will be glad to finally get to the bottom of it !

Anyway - that’s all folks for Day one of my new development. Today I’m going to spend some time trying to solve this Windows/Java problem and also take care of creating some of those database tables :-)

"You can't ever be really free if you admire somebody too much." - Tove Jansson

ArtenSUITE: Part 1: Introduction

ArtenSUITE
A Development Journal
Part 1: Introduction

Most of the work I do involves custom/bespoke software development with some database and consultancy work thrown in. I’m recently decided to also develop and sell commercial software via my website at www.artenscience.co.uk. I have a couple of applications available now, a third nearly complete and a dozen or so further ideas to look at when time allows.

Idea

One of the ideas I have is to build a suite of business products that run independently or together. The main application will contain the customers, prospects, suppliers and users schema and screens as well as the other bits that can be used by the other applications. This module will be needed, and supplied with the purchase of any of the other modules. The development name for this central application is ArtenBASE.

The other modules will be specific applications such as MRP, Stock Control, CRM, Orders, Charting, Helpdesk, Mailshot, Team Management, Time Tracking, Credit Management etc. Each module will be multi-company and where financial information is used, multi-currency with consolidation available at several levels.

I can also use ArtenBASE as a starting point for many of my custom developments with the advantage to me of development speed and proven reliability and the advantage to the customer of development speed and access to the other modules.

These will not be huge complicated applications. I will be focusing on providing easy to use and reliable software without too much complexity. I’m not trying to write SAP. An IT Department will not be needed to support my software. Each module will have a set of clearly defined interfaces so that additional functionality can be build separately, should it be needed. Should anybody else want to build software that interfaces to mine then the API will be made available.

Technology

I would like this application to run on both the Windows platforms and Mac OSX. I have therefore chosen REALbasic as my development environment. For my back end database I have chosen Oracle. Oracle XE is FOC and when/if the customer outgrows it’s (disk and Memory) limitations there is a seamless transition (for a fair exchange of money) to the higher end Oracle editions. I have many years experience of Oracle, having written a full Financial Accounting System / MRP System etc. in the past which was based on Oracle 8i. The newer versions of Oracle are so much better and I look forward to getting to know them better.

Reporting

Standard reports will be built into each individual module. The SQL used to generate the reports will be made available so that enhanced reports can be created using a standard SQL Query tool, or ArtenQUERY (available soon) can be used.

Data Access

I am tempted to use stored procedures for the majority of database access. The downside to this is that it will make the software more difficult to port to another database system in the future. I don’t expect to port it, but you never know ! I have yet to make my final decision on this.

Timescale

This development will be happening alongside my other work and personal commitments. I would therefore, tentatively, hope to have version 1.0 of ArtenBASE available within a few weeks and by Christmas I would like to have 2 or 3 more modules available.

Why Tell the World?

Why Not ? This blog will help me by providing a mechanism for me to monitor my progress and thoughts. I don’t imagine anybody is going to read this and suddenly decide to drop their own plans and try and emulate my ideas ! There are many good ideas but they are not profitable until executed. I intend to pursue this and develop a suite of products that can compete on their own merits. Besides, I don’t intend to tell *everything* :-)

I’m going to continue to blog about my experiences as I undertake this development. Hopefully someone will find it interesting and / or useful :-)

"You don't need to outdo the competition. It's expensive and defensive. Underdo your competition. We need more simplicity and clarity." - Jason Fried