The Blog from Mercian Labels & Security Labels International

Just spotted this on the BBC

 http://news.bbc.co.uk/1/hi/technology/6506027.stm

http://www.dell.com/content/topics/global.aspx/ideastorm/ideasinaction?c=us&l=en&s=gen

One of the problems we have put on the back burner was migrating the directors laptops, currently running XP, to linux.

This would solve it easily.  I tried running ubuntu off disk in demo mode on my HP laptop, and it wouldn’t support some of the drivers, including the wifi.  I didn’t try to fix it, I’m just too busy and lacking in knowledge of Linux.  I just voted for the move on the Dell website instead!

Dell supporting linux is a real godsend for SMEs who just want to buy something that works, no faffing about, and laptops are notorius for being difficult to setup.

I wonder what distribution Dell will use?

Some time ago we tried to write our own timekeeping software to log the working hours of all our staff.  We tried using VBA in conjunction with an access database, but never got it to a working standard.

Now we are back on the hunt for some simple php / mysql type system that will allow staff to clock in / clock out to record their working horus, not project specific, just number of hours a week worked.

This may be our first opportunity to write some code and release it back to the community, but we dont want to waste our efforts if there is an established solution out there.

I’ve looked, but cannt find any basic time keeping software.  If you know of anything, or suggestions of where to look, please feel free to leave a comment.

So far, I’ve googled it, but got nowhere.  Looking on source forge:

http://sourceforge.net/projects/phppunchmachine/

this looks like a possible contender, as long as we rewrite it in english.

any comments?

Well.. we finally decided that we should give the MVC framework process a chance, to see if we could truly benifit from it.

I’m going to be using a simple ish MVC framework and Smarty for the templating system, this seems to be working both logically and easy for us, or fairly easy anyway.

The way im going to do this is have a main page which will control the configuration etc, and then a series of templates for each section (ill come back to this) and then modules for each section too.

What i mean by section is this.. Basically the way I see how our company runs the existing database is based on a few sections(Customers, Orders, Searching, Report).. there is more, but this is just an example, so i’ve decided to have these as our main controlling elements.

So our intranet should work on this basis: http://address/index.php?section=Customer meaning our program knows we only want to be working with whatever is involved with a Customer, the same goes for Orders, Reports and so on, keeping the whole process a lot simpler (or so i think anyway)

With this in mind, ive now got a CustomerModelController which will take aditional parameters defining which action should be performed now we’re only concerned with the customer, for instance Search for Customers.

Our intranet again shows how easy and logical this makes things:

http://address/index.php?section=Customer&action=search_for_users or add_user etc, you get the idea anyway.

These methods allow me to have custom templates for each section, and still keep my code nice and tidy, so i’ve not got 100’s of smaller functions within one big controller.

I’ve tried not to go into too much detail here, so not to confuse people/myself anymore, but if anyone wants further explanations just let me know, and ill go into more detail.

Just want to say thanks to the people who have commented on the MVC ideas so far, definately made an interesting read, cheers!

As mentioned in the previous blog, we’re currently deciding how our new project should flow. By this I mean, do we use the more conventional methods we’re used to (hard coding our php for each individual page) or to take the plunge and go the Model View Controller (MVC) route.

I’d decided a few days ago to look into MVC, although i’d touched on this within Ruby on Rails, i’d never done it within PHP. After some initial research, it seemed that this wasnt as complex and as far fetched as I initially though – therefore worth a look in my opinion. With help from online tutorials and people in the know of MVC frameworks, i worked through and created a simplistic framework that worked with the “Smarty” templating system, which was also then tied in with PEAR::DB for database abstraction.

I created a few simple functions, simply passing data throughout our framework to display customer details, and then change this depending on search critera. Not exactly complex, because this is easy to do with in PHP anyway, but the thing that struck me was, suddenly im not repeating code, and things are beginning to look more logical; in the way that I know exactly where I have to go to edit files, I know exactly what page does what, and the whole process seems to flow in controlled manner. This seemed a plus point for me, and I thought we may be onto a winning idea.

A brief meeting was then held between myself and Adrian who talked over this aproach; we also consulted Dave (briefly), Jake and Gregg and Senokian for the expertise in MVC Frameworks.

Their opinions were mixed, there were plus points and downsides to the idea we had, the main downside thats stuck in both our heads is the learning curve it may cause to fully understand how this MVC will work, and how we can train someone else if they needed to.

The opinions of Jake and Gregg at Senokian basically stated that it may not be a good idea for us to go down the MVC aproach due to the learning curve that would be needed by myself in order to get upto standard, but if we did decide to go down that route, and i felt comfortable in aiming towards using MVC then there are numerous frameworks we coulc use, such as smarty.php.net.. This basically allows us to create the design quicker, and perform template swaps/alterations all in one place.

This has brought some confusion between us, because Gregg and Jake have a lot more experience with MVC frameworks than me, so I have to take their advice, and have to admit im really in two minds now

My opinion now is to spend a few days trying to work with this framework, and come up with some useful functionality. If i fall into problems then we may need to give the MVC approach a miss, but if things work how we expect them, this may be an area we need to solidly look into.

Our migration to linux based open source hinges on the sucessful implemention of a php – postgresql database to run our CRM.  Now I have done some coding, in fortran and VBa, but I’m a businessman, not a coder.  What coding I do I enjoy, but its not my role really.

An issues came up yesterday what I saw as strategically improtant to the sucess of the migration, and this was the adoption or otherwise of MVC as a clever way of managing duplicate code.  Now I like this idea, but as I lack formal training in software development, I dont have the background to understand this fully.  There are many sides to the argument, and the debate will continue internally today as we look at other options recomended by Jake and Greg at Senokian.

This post is a quick warning to managers, directors, and other business people driving a project like this.  Sometimes your non technical background will let you down, and you cannt speak the same language as the coders becuase you dont have the training, and reading the odd web page about a subject is not enough to get by.

It is likely that on this occasion I may have to make an uninformed decision on a very technical issue.

In a previous comment, Dan Kegal ( a WINE developer) asked what software we would like to run under WINE but dont think we can do.

Easy, here is the list:

1. SAGE line 50 network edition – many, if not most small businesses run SAGE accoutning software, and I dont believe that it will run on a linux platform, but as yet we havent tried it.
2. Graphic design software:  we are a label printer, and as such our graphic design function is vital to us.  The Gimp is just not powerful enough for professional level desgin, colour seperation, dispro, and we expect to have to keep one PC just for design, running Adobe Illustrator, Corel Draw, and maybe photoshop
3. Ad hoc bits of software.  These may, or may not work on wine, aagin we have not tried them, but our conservative assumption is that they wont.  These are mostly SEO pieces of software like the banned by google tool  and seoelite

If anyone has any expereinces of using these I would be grateful for feedback.  When we do get around to trying them, I will post the results here.

Our sales team are the heaviest users of our CRM system, and not used to Ubuntu, Firefox, or indeed an web based databases.  Following good advice from an IBM case study, we are holding a user testing experience at an early stage to get feedback on the user experience.

The success of an Open Source migration to us is heavily dependant on getting buy in from the users, and their involvment at an early stage is, to me, crucial.

Using our existing Acess front end to a SQL database, we dont have the problem of our users forgetting to update/save records they are woking on.  It does it automatically.  This is a weakness of PHP web based interaction with databases, you either have to remember to save the record, or you could loose your edits.

Ths way we are proposing to work around this is using AJAX, so every change we make will save itself once the user moves off the record field.  At least this is the theory – Rich is trying it out as we speak.

Hoping that this blog may interest other businesses looking to migrate to Open Source, I’ve posted this press release.

Just a quick follow-up post here regarding how to achieve auto incrementing on a PostgreSQL field.

Thanks to the advise from Senokian I was able to create our database with the exacy same functionality as our MSSQL one. The only field we were concerned with was the customerid one, which should increment by 1 each time a record was added.

An example of this is:

CREATE TABLE customers (customerid bigserial not null);

You can either use serial or bigserial depending on your needs.

And that’s all there is to it. After you issue that command, PostgreSQL will automatically create the sequence for you, and deal with everything else that is going on in the background. If you don’t give the sequence a name, in this case it will/should call it customers_customerid_seq

P.S don’t forget if you want to use your table properly with PHP etc, you need to grant permissions on the table, i.e.:

GRANT INSERT ON customers TO username;

MSSQL has the lovely added functionality of Auto Increment. Meaning that when you add a new record to a table, it automatically gets added to the next ID. To clear this up, an example is. Customer A has an ID of 1, we add a new record to this table, and it’s automatically given the ID of 2, so the table is logical in the way it flows. If auto increment was not in place each time we add a new record we would need to get the last ID entered, add one, then manually insert the record with that ID, which is too much hassle for a consistently used database.

The problem arises here; PostgreSQL doesn’t have an auto increment feature!

This doesn’t mean we cant have the functionality of auto increment, it just means we have to work around it.

PostgreSQL has something we call Sequences, which when added to our table can perform the same role as an auto increment field. I’m still currently looking into this, but it doesn’t seem too hard.

The reason for our previous problems now became apparent.

This is down to data types; PostgreSQL has a different data type for some of the fields, such as:

MSSQL — POSTGRESQL
Bin — Boolean
Datetime — Timestamp
Nchar — Char
ntext — Text
nvarchar— Varchar

This wasn’t the end of the world, it just meant that each field in our database that would need changing, would need to be checked to see if it met the constraints of the PostgreSQL data time. One other thing to note is, a lot of these fields don’t allow NULLS, which is also the same for int (int is the same data type in both databases but MSSQL allows NULLS, PostgreSQL definitely doesn’t.

The quick fix for this was to change every NULL value (that we could) into a 0 (zero) or another number that would tie into our database. Maybe not the best solution, but these fields weren’t being used properly any way (hence the NULLS).

2 days later after changing multiple fields and multiple records in MSSQL, I was ready to try again. I ran the script, got all the information into the .txt file and ran the file.

No Errors. A quick SELECT COUNT(*) from customers; showed 20000 records. SUCCESS!

As already mentioned in a previous blog, we want to migrate our database from MSSQL to PostgreSQL and the first step of this was to see if this was even possible.

We decided on a fairly straightforward table (customers), that had many fields and many records (20000), but little in the way of constraints.

There are a few guides online showing the easy steps to go from a typical MSSQL database to the advantageous PostgreSQL, one of them being (http://www.postgresql.org/docs/techdocs.29)

Basically the steps in that guide explain how to use the MSSQL Enterprise Manger tool, to firstly create a script of your table, and then change all invalid data types, and then how to create a dump of data and export it to PostgreSQL.

This is all very self-explanatory, if the above process works; but as per usual, not everything goes to plan!

When trying to export our data, we had the unfriendly error message (EXCEPTION_ACCESS_VIOLATION) and that was the end of that.

Research into this error message suggested the need to upgrade or MSSQL to SP4, but it already was!

Further research began to show that other people had been having the same issues, and that there was not much of a solution present.

At this stage, I now considered looking for another method to migrate our table, there were a few easy and complex ways to do it, such as running scripts on our database, or using other extraction tools, and I tried a coupe of different methods but the same error message stopped us in our tracks.

I then decided to try an alternative way, by using a custom PHP script. I made the script to read each record from our MSSQL database, and to add each INSERT statement created to a .txt file. This seemed to work fine, until I tried to run the file on our Linux server to add the 20000 records. The first time I ran it only 8 records were entered, this is a big gap compared to 20000!

A quick post on PHP.  Compared to ASP, its great.  Even this Blog is runs on open source PHP code, and it dosnt break.  I love it.

.htaccess rewriting is a cool feature of linux/unix hosting as well.

These 2 articles caught my interest this week.

•  The UK opposition political party wants to use open source software for central and local government IT: http://news.bbc.co.uk/1/hi/uk_politics/6430069.stm  Nice idea, I doubt that they have costed the changeover costs though, which would be immense.  Does anybody have an estimated methodology for calculating a ROI on migrating to Open Source?
• Dell are proposing to offer new PCs with open source preinstalled:  http://www.dellideastorm.com/article/show/61771 and http://www.theregister.co.uk/2007/02/28/dell_linux_computers/

I cannt help but think that within 5 years Microsoft will be a classic MBA strategy course case study as a company that abused a monopoly position and lost huge amounts of customer base as custoemrs revolted.  Ubuntu is necessary for this transformation, lets hope that the Open Source movement gains credability with these 2 proposed moves.

Well I thought that this would be easy, but its not.  Rich has been working on this for a few days, and it seems that there are 2 migration routes:

1. use the MS SQL download tool, that dosnt work very well
2. write a PHP script that imports every parameter from the MSSQL database into a variable, and write it into PostgreSQL.

The problem is, that PostgreSQl dosnt like null fields, and its a bit of a game to import. 

Just for your information, it takes time.

WE thought a lot about how to provide ourselves with a redundant system in case of theft or breakdown of our main server in house, and we decided to go with 2 copies of the database, syncronising every 5 minutes.

However, Dave over at Senokian came up with a  good idea, have 3 copies, 1 main one internally and 2 copies on our remote dedicated server in the fasthosts server farm.  the idea is that 1 remote copy syncronises every 5 minutes, and the other every hour.  This means that is we correct 1 copy, we have a fighting chance of recovering our data from the ird 1 hour copy.

Good Idea Dave, thanks.

Now all we have to do is to do a dump of the SQL data into PostgreSQL which Rich is working on at the moment.

First problem with the software infrastructure we had planned is that we wanted a fully syncronised copy of our mission critical database on our work server and our dedicated web server.  This was as a backup and as redundant capacity in case of internal server hardware failure.

Our Fasthosts dedicated server uses Fedora Core 5, and as of February 2007 the Fedora legacy project has been shut down, leaving no security updates available.  The problem is that Senokian believe that Postgresql wont work on Fedora Core 5, and really we should run a support operating system with security updates.

We have about 2 months now to sort this out, and its currently with fasthosts.  I await their response.

There is so much to learn when planning a new IT infrastructure.  In 1999 we were doing all our quoting by hand with a calculator.  Then we started using Excel to automate it.  Then it was ACCESS with it JET database system. Then it was SQL with an ACCESS front end viua ODBC. Now we are moving to POSTGRESQL. We do use MySQL for web based database stuff and SEO becuase its fast as a web server, but not the mission critical CRM system.

Its all about relaibiity and scalability.  As our business has grown, we outgrew individual XLS sheets very quickly, and ACCESS cannt deal relably with many concurrent users.  MS SQL isn’t bad, but it still does weird things sometimes and we loose data. Basically, I dont have long term confidence in it. 

There is also this whole issue of ACID compliance. Jake from Senokian pointed out that http://tweakers.net/reviews/657 shows some of the advantages of PostgreSQL over mySQL).  Jon from Clocksoft also talked about PostgreSQL in an initial conversation.  One problem we have had is data being partially changed, but not fully.  ACID compliant databases change everything or nothing, so if there is a hardwware or netorking error you dont get half changed instructions.  POSTGRESQL also comes from a more professional stable.

In terms of open source databases, MYSQL will probably also do the job as it can run in ACID compliant mode, but I’m no expert.  We have taken Senokian’s advice on this, and fingers crossed we wont have to change infrastructures again for a good few years.  I just hope that PostgreSQL lives up to expectations of being a very relaible platform.

OK, so its raining.  But thats not an excuse for IT kit to play silly beggars.  Yet again, for no apparant reason our work legacy windows file server is refusing vpn connections this morning.  nothing changed since yesterday, but today it wont work.  I know its on, as its serving web pages, but no remote desktop or vpn.

ARHH!!  This is what bugs me about the big M.  It breaks when nobody does anything.  This is why I want to go to a more stable open source IT infrastructure.  OK, its more difficult to configure initially, but when its configured, its v reliable.

We get about 4000+ spam emails a day, which is a serious headache.  Since we moved to a secure dedicated server, we have struggled to get an email system configured hat worked well for us.

The target was near zero spam to our email accounts, near zero false positives on incomgin legitimate emails and zero viruses.

Finally, it has just been cracked, after about 3 months of effort from Rich (our IT guy) and Tom, a 2nd line software support engineer from Fasthosts.

Getting the Spamassassin working wasnt too bad, it took some fiddling with the config files to enable us to see the right headers, but it works v v well now.  We set the default trip score to 3, then lowered it to 4, (1 being catch everything, 15 being is “allow all the crap through”!).

The anti virus was a different ballgame.  Clam AV seems to be the open source market leader, and works well as far as we can see.

The problem is integrating the 2.  You have to use AMASVISD which by all accounts given to me is a pain to install and configure.  Tom from Fasthosts has been a star in taking his spare time to sort this out over the past 2 months, and now finally we are clear from incoming viruses by email.

Thanks Tom!

our migration plan is as follows

• February 2007 – senokian sets up a test development network with a single server and a single client that runs all the software we want.
• March to May we (well, Rich, our IT manager) re-writes our mission critical VBA / ACCESS front end systems in php and tests the whole thing with an offline copy of our database, doing in house training with demo email accounts etc.
• May / June 2007, we complete our in house training, and move across to the new system
• WE migrate all the clients to dual boot ubuntu until we are happy all clients are cool with the new OS
• switch off all big M packages permanently apart from our design PC that needs to run fancy graphics packages.
• continue development and refinement for the following 6 months

We have a major project that starts in July / August, so I want the major rewrite done by then.

Fingers crossed..

Well I’m nearly up to date with my posting now.

To start the project off we listed a whole set of software that we wanted to use, bought a cheap development server and client, and gave them to Senokian to do all the initial configuration.

The operating system was pretty easy to choose.  I liked Ubuntu when Jono Bacon was teaching it at Openadvantage, and I went out and bought the official Ubuntu book in November 2006. We settled on the Dapper Drake version, as it was guarenteed to be supported for years, which is imporant to us (see later posts!!!).  I had a look at the other distributions of linux, but gave up.  Ubuntu works, and I feel its going to be come the most commonly used distro, and thats good enough for me. 

I’m a businessman interested in IT, not a techy, and as we want to make the Thick clients as easy to maintain as possible, Ubuntu suits us.

The open source software we have initially specified with Senokian is as follows:

SOFTWARE

Operating system – Ubuntu with QOS service and RAID 1 configuration if possible, DHCP DNS , VPN over PPTP to support remote working and a “remote desktop” function

Email, calendaring and personal task management – web based on the server using Zimbra with email to fax function ability to take emails from our existing pop3 box system on our dedicated server

Website Hosting – Apache

Database backend – PostgreSQL File sharing - SAMBA

Well, it wasnt easy to look into this whole open source thing. Have YOU tried finding any decent info or case studies on SMEs moving away from the big M to open source.

I used these in our internal discussions

How a UK network has moved to open source software:
http://www.theregister.co.uk/Design/page/linux_case_study_orwell_high_school.html
SMEs moving to open source
http://www.siriusit.co.uk/index.php/news/4/92
Buyers guide to OSS for SMEs
http://reviews.zdnet.co.uk/buyersguides/linux/

The BIG problem was that we are only a company of 20 people, and although we have a good IT manager (Rich) with a software engineering degree, it was too big a jump for a business critical resource without proven, professional help.

So, we went looking for help.

The guys at OPENADVANTAGE are GREAT, and after a training course on PHP/MYSQL using Ubuntu and VOIP using ASTERIX/TRIXBOX I really had enough confidence to look seriously at moving into open source. Without those courses, and the advice from Jono Bacon and Paul Cooper then I doubt we would be doing this.

Through my own web research and contacts, I contacted a few companies about the support I would need.

http://www.intercellsolutions.com/ – looking over their website I didnt get the impression they had enought speciality or focus in open source

http://www.palepurple.co.uk/ – I formed the impression that these guys were more into tuition than transition, and I think I talked to them. Professional, just not the focus I was looking for.

Hauraki Systems Ltd – Don was very helpful, but it quickly becaame clear that their focus at the time was more specific, larger contract support, and that it may have been difficult to resource our project.

http://www.senokian.com/ Impressed. From first phone call , first meeting, everything, these guys were pretty much what we were looking for. Absolute focus on open source, a decent sized team of 10 people, flexible, knowledgeable, access to the MD when needed, just the ticket. We looked seriously at their EGS product that is very good, but really it would take more customisation to write our own modules to interface with this than what we could do by ourselves. Good product, just not for us. We negotiated over xmas 2006, and signed a support contract for 12 months in January.Off we went!

For the record, we run the following

CLIENTS (x13) 

• W2K/XP Operating System
• A SWYX voip soft phone client
• An IE web browser
• Email using Microsoft exchange
• Access to our SQL database using a Access 2000 database client (major application)
• Word and excel files for ad hoc functions (rarely used)
• Shared fax (rarely used)
• Network printer access
• image viewing software (rarely used)
• PDF viewer and writer
• graphic design software for 1 client

 SERVERS (x2)

• 1 x W2K Server
• network file storage
• backup routines
• DHCP
• DNS
• A network Email function using Exchange and Exchangepop3
• SAGE accounting systems for 2 users
• shared fax
• SQL server for our database back end
• Terminal services for remote desktop
• Website hosting
• Antivirus
• Active directory / local domain
• CD burning software
• Ftp client access
• VPN access
• Website design software

WEBSERVER

Our external dedicated server is hosted in an external data centre for reliability purposes and includes:

·        Fedora 5 Linux OS

·        Apache web server

·        MySQL database for website

·        Html / PHP used for website pages

·        SpamAssassin anti spam software

·        CLAM AV anti virus

This was our benchmark when we started this project in October 2006

I just have to quickly post this one.  Whilst I am the MD of Mercian Labels, all these posts are my own opinions.  Also, we operate in a market with a major online marketing presence, so at some stages I’ll have to withhold some details for our own commercial reasons.  I hope this want detract from the overall content, but please forgive me when I have to do this.

I’m just going to crack on with this first post, as if it is planned too much it isnt going to work out.

 After many years of viruses, software problems and perpetual maintenance issues, Mercian Labels had a pretty big outages in late 2006 when some form of virus killed our server.  It cost us a lot, and was a major pain for our sales and production team.  As MD of the company, one of my roles is the IT, and in conjunction with my business partner Hugo, we decided to investigate our options.

 Our hardware is getting old, the software too, and we needed a major replacement of the whole network (costly) in the medium term.  We’d heard about open source software, and it looked interesting.  Principally, we run a database in house based on an ACCESS  front end, and SQL back end, along with the usual email and web browser stuff.  What we wanted was a new hardware infrastructure that would completely replace what we had with open source.

Main reasons:

• the high cost of a  whole new set of software licenses
• vulnerability of microsoft systems to viruses and spyware
• unreliability and speed of the existing software
• desire to program our own database system front end (essential for our business)

I want this blog to document our journey this year, so that other SMEs can learn from our mistakes on what to do that works.

So here goes….