5 posts tagged with "database"

In my RSS feed recently, I came across another great application that lets you create a database model from scratch, i.e. create tables one by one and create the links between them.

However, there's another option that I find really nice: you can get a visual of the tables and relationships of any existing application in a few seconds from an .sql file that you import into drawdb-app.

Let's take a quick look at how it works.

A long time ago, years from now, I needed to expose data from my Joomla site in a simple web page outside Joomla, as an HTML table. This was so that I could link a Microsoft Excel spreadsheet to this table and therefore, in Excel, simply do a Refresh to obtain the most recent data from my Joomla site.

The aim was to find the list of people who had bought software or services from me. Among other things, I needed their first name, family name, billing address, etc. so that I could create an invoice in Microsoft Word using the mail merge functionality (data source=Excel).

Of course, just running a query on your database and show the result as a web page can be really useful.

Several years ago, I had to intervene on an MS Access database that had been created by a person of good will but with little affinity for computers and optimization.

The result: a cumbersome and exasperatingly slow database.

In this article, resurrected from my personal archive, I'm going to present the different stages I went through.

Years ago, I've created a .vbs script that will scan a MS Access database, loop for each table and, for each of them, will get the list of fields.

For each fields, a lot of information will be retrieved like, not exhaustive, his name, size, type, ... and also the shortest and longest value size (for text and memo fields). For instance, if a text field is found, the script will retrieve his size (f.i. 255 chars max) and will examine all records in the table for retrieving, for that field, the smallest size (f.i. 10) and the greatest one (f.i. 50). So, if the max size is 50 and the size has been set to 255, perhaps the MS Access developer can safely modify the max size from 255 to 50.

A recent post on the Joomla French forum was asking for being able to delete tables present in the Joomla database; the ones using a given prefix like old_ or something like that.

Indeed, from time to time, it can be useful to take a look on the list of tables in your database and perhaps, you'll find tables prefixed with f.i. old_ or an old tool you've used years ago (using the correct prefix but with a component you've removed since like joomla_oldcomp).

Years ago, I've written such PHP utility, let's see how to use it.