I(ve recently started work on a project which involves creating a web-based reporting interface for a fairly old software responsible for managing some access control hardware like electronic door locks for example.
I have chosen CakePHP for the task and all it involves is querying the database for the log records and displaying them on the website. The software is written in C++ and uses MSDE (a scaled-down version of Microsoft SQL Server 7.0 or 2000) and I was able to configure CakePHP so that it successfully connects to the DB.
The problem is that the developer of this software has chosen to store the logs in the database by creating a separate table for each day. So it looks like this:
I am not really familiar with MSDE but I still doubt that it is an acceptable practice to design and maintain a database like this. I do not know how the desktop software manages to parse query all the information when it(s all in this format but my CakePHP application is having some really hard time chewing this up.
I want to stick with MVC for the web application but I don(t know how to implement the DooreventModel in CakePHP so that it gets the information from all the partitioned tables.
I(ve tried implementing some hacks like using
UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_07
UNION ALL SELECT * FROM tbl_DoorEvent_2008_08_08
and this sort of works but when I want to perform WHERE restriction on the whole query or any other SQL operator it doesn(t work.
Also one of the requests was that the web-based application does not add or create any additional databases or tables and only uses the existing ones to display the reports so creating one huge table that includes all of the clustered ones is not really an option.
I just hope that someone will come up with an SQLServer function that will merge all tables into one for me every time I perform a query but I know this is a bit optimistic, so I(m open to all suggestions really.
And please remember that the solution must work with CakePHP and as a single Model in the MVC
posted date: 2009-04-12 06:15:00