Kenneth Downs's personal blog

It's only about 3 years late, but we finally have a public demo available for Andromeda, located at http://www.andromeda-project.org/demo

When it comes to calculated values, most of us by reflex think of views. Or, possibly we think of evil de-normalized values supplied by application code.

I have found it incredibly useful to spec out calculated values in terms of base tables, that is, to define them as columns with formulas. This approach does not require that they actually end up as columns (though in Andromeda they do), but the act of defining everything in these terms has a huge advantage. This advantage is simply the fact that the entire specification of table structure, calculations, constraints and security can now be expressed entirely in terms of a single atomic building block: the column.

The question now arises, if I have a view that performs calculations based on values from multiple tables, how would I switch over to using a formula? Don't I need to refer to the other table somehow? The short answer is yes, of course, but the difference is you never bother to specify the JOIN. Here is an example. The column ORDERS.LINES_TOTAL should get the total of the line items. The formula would be easy enough, in Andromeda it happens to be this:

column lines_total:
automation_id: SUM
auto_formula: order_lines.line_final

This works if the system implementing it uses the foreign key between ORDERS and ORDER_LINES to perform the calculation. It might build a view, or, as Andromeda does, put a trigger on ORDER_LINES to update ORDERS when appropriate. In either case, it is the use of the foreign key that allows to specify a simple formula and let some code generator do the rest.

I have found that nearly every calculation can be resolved down to some type of movement of data between one table and another that are joined by a foreign key.

Another example would be an online sports betting system. A bet's value is calculated when the game goes final. But the FINAL column is on the game, not the bet. If we wish to update just the game and have all bets updated automatically, we would specify the FINAL flag column also to be on the bets table. It would need to be updated when the value in the GAMES table was updated:

column final:
automation_id: DISTRIBUTE
auto_formula: games.final

There are quite a bit more, but I will leave them to later entries.

---

Note: Most of Ken's entries are about Andromeda, an open source database application framework that uses Postgres.

I have two projects that have needed ranges, and so I had to nail down some specifics of how these would work. In one case, we had management fees that were effective over different time periods. The time periods could not overlap. Another had to do with quantity-based prices.

There are two requirements that make ranges interesting. The first is that they be non-overlapping. The closest analogy to existing features is the primary key. The primary key serves the function of excluding a second row that would identify the same entity as an existing row. By the same token, preventing range overlaps is the same as excluding a second row that would identify all or part of the range of an existing row. Therefore, we implemented ranges as a switch on the primary key. Two columns in a multi-column primary key can be identified as forming a range, and now our server-side trigger checks for overlaps instead of uniqueness.

Early on I had the idea that it might be allowed for one range to nest inside of another. This makes that inner value a more precise value. In practice I never needed it so I discarded it to keep it out of the way.

Having worked that out, there is a second requirement for ranges, which is that the behavior of a foreign key has to be worked out. After a few permutations I settled on the idea that a foreign key to a range is itself only one value, and the referential integrity says that that value must be within the range of values of a row in the parent table. This allows us to use Andromeda's automation features to FETCH values from parent tables down to child tables.

From there it becomes useful to embellish ranges in a couple of ways. I need to have open-ended ranges, so we decided a null value for the second value in a range means infinity, which gives open ranges. A null value as the first value means -infinity.

Note: Ken's blog entries are usually about Andromeda, an open-source application framework that uses Postgres.

I've been very busy working on new features for Andromeda, our open-source database framework that uses Postgres.

The most recent addition to the server-side is History tables. You can now build two tables and log the changes in one to the other.

When a HISTORY action is specified, it fires on the source table unconditionally for insert, update, and delete. You can specify that the destination columns get a literal value, the OLD value, the NEW value or the Difference. The difference feature is particularly useful for financial journaling. If a user does an INSERT, a difference column will get the new value. If they update, it gets NEW - OLD. If they delete the row it gets -OLD. This lets you track individual changes without complex query gymnastics, while of course still being able to GROUP and SUM to get the value at any particular time, or the changes within a period of time, etc.

You can deny all access to the history tables except for highly privileged users. Since Andromeda uses SECURITY DEFINER triggers to implement the histories, the less privileged users are just fine.

We have a big new ui coming out, which is now in Alpha. There will be more announcements on that forthcoming, including some requests for help for js or css gurus. If you can't wait for that, the publicly viewable alpha test is http://dhost2.secdat.com/develop.


Note: Ken's blog entries are usually about Andromeda, an open-source application framework that uses Postgres.

Recently I replaced an old DOS based program in a doctor's office with our newer 3-tier browser-based, postres-underneath system.

The cultural jump for the users was significant. While generally speaking I think the browser nowadays does as well or better than those old highly-user-centric apps, there was one big exception. This program was command-based, and had only a few commands, so the users were real wizards. By far the most common thing to do was pull a patient's record, which they did by typing "get ken dow", which would return search results for first name starting with 'ken' and last name starting with 'dow'.

Although Andromeda's lookup features are powerful and flexible, no mouse-based system can possibly keep up with that, and even with keyboard shortcuts there are a lot of keystrokes for something the user considers a no-brainer. So believe it or not I added a command box up at the top left of the Andromeda default template.

It works the same as my customer's old DOS system, except instead of "GET" you type "PAT" for patient, "CAR" for carrier (insurance), "VIS" for visit and so forth. So now they hit, in order, F2, "pat ken dow", and ENTER, and the system sends them to the patients lookup screen and executes the search. The box fully supports Andromeda search tricks like commas, dashes,etc., so to look up a brother-sister family you type "pat k,j downs".

Another customer has requested the obvious extension "new", so you can something like "ven.new" to jump to the vendors screen and add an entry.

Long live the command line!

Note: Ken's blog entries are usually about Andromeda, an open-source application framework that uses Postgres.

Recently I had to seriously nail down the loose ends on our row-level and column-level security, and it caused me to rethink some of the basic ideas in the system we implemented. In particular there were some strange developments when considering the Deny By Default principle.

To start at the beginning, our simpler flavor of row-level security requires the value of a specified column to match the session's value of current_user().

From here let me say we assume that row-level security, a basic feature of Andromeda, is actually turned off by default for all tables. If not, there would be an onerous requirement to grant all rows to all roles on all tables (Especially onerous in situations where row-level security made no sense on the table, or where there were multiple columns it might be based on, or where it was completely absent from the application).

With that in mind, that row-level security is not applied at all by default, what happens when we decide that role X has row-level restrictions on the USER_ID column of the EMPLOYEES table? Are all other roles free of that restriction, since nothing was specified for them?. If so, then we have an unexpected GRANT by default, in that all other roles are now granted access to all rows, which is not likely what the database designer had in mind.

This I found was no good. The answer appeared to be that if a row-level restriction was specified for ANY role, then it had to be assumed for ALL roles, bringing us back to a Deny By Default scenario. But this decision led to the strange case that if a row-level restriction was specified for role X, it meant that roles A,B,C and Y and Z also had the restriction, even though it was not specified for them. This is distasteful, but is far better than the potential consequences of an unexpected GRANT by default.

In the end I found that the Deny By Default made the most sense if I turned the specification around and specified those roles for which a restriction did not apply, and that seemed to be as simple as it could get. So, the key action is to specify that role MANAGERS does not have a row-level restriction on column USER_ID of EMPLOYEES. This action "activates" row-level security on that table and implies that all other roles now have a row-level restriction on that column.

Note: Ken's blog entries are usually about Andromeda, an open source application system that uses Postgres.

Andromeda is an application development and deployment framework that uses Postgres.

We have a new release out with a lot of changes, several bug fixes and new features.

Lately its been all about the UI. We added a lot of visual cues like highlighting the current field and switching background color to yellow when the user changes a value.

I'm very excited that we've begun to truly implement the data dictionary at the UI level now. So if you specify that a column is a FETCH column from another table, then during data entry when you enter a foreign key, such as "Item code", the price column is immediately filled in courtesy an AJAX call.

Also the basic table maintenance functions are moving over to AJAX, making the system snappier during regular use.

We added "uicols" and "uirows" that control the size of an HTML Textarea for "text" type columns.

Release notes are at:

http://www.andromeda-project.org/pages/cms/Release+Notes

I'm giving a talk this evening at 6:30pm in New York City, introducing database concepts to programmers, at the NYPHP group. An RSVP is required which technically was yesterday, but if you show ID they will let you in, more at http://www.nyphp.org.

Todays release adds to Andromeda ability to specify "value_min" and "value_max" to database definitions.

We have begun our campaign to start using AJAX to give the screens more of a desktop feel, performing calculations automtically w/o a complete page refresh, based on the database specification.

www.andromeda-project.org

I get traffic to the Andromeda website from two identifiable sources (not including search engines and bookmarked or direct access), which are Sourceforge and Planet Postgres.

About two or three months ago Planet Postgres began to outstrip Sourceforge. Last month the numbers went way up and now "the planet" is easily the source of 10-20 times the traffic as Sourceforge. No other identifiable source of traffic is on the radar, even though I generally make announcements on various PHP forums (as Andromeda is written in PHP) and participate in person at many PHP events.

I have to admit this was completely unexpected, but I'm very happy about it I do find myself wondering why database folk take such a disproportionately higher interest than programmer folk. That is a mystery to me.

We love to talk about how fast things change in the world of computers. Likewise, we do well to remember that the more things change, the more they stay the same. At the bottom of many glitzy Web 2.0 systems you will find a faithful and "boring" relational database.

The next wave of changes we are likely to see will probably be more of what we've been seeing for 10 years, more connections, more kinds of devices. It really just boils down to more interfaces. This is comforting to a system architect who knows the foundation database technology will not change, but it raises the very serious question of how to keep up when the interface platforms you are trying to support are always multiplying.

This leads me to ask what details on any particular screen are unique to that screen, because I'd like to leave everything else to library code. In the best case, some type of UI description file, listing key details, could be read by one library that generates HTML/CSS for a screen, and another library that builds simplified code for smaller cell-phone sized screens. If a new type of interface emerges as popular, you write or otherwise obtain a library of glitzy widgets, teach it to read your UI descriptor files, and your assets are protected.

The question is, what does that UI description file look like? What should be in and what should be out? I've been playing around and discovering that, of course, most of the information boils down to simply naming tables and columns. If you've got healthy meta-data, then the simplest description might name a table, half a dozen columns, and then specify whether they are displayed in detail or grid mode. There are quite a few details more than that, of course, which I'll spell out in future entries.

Note: Anything discussed here will likely show up in my Andromeda project, an application framework that uses Postgres.

The Andromeda main site now includes a complete reference to our database description format, listing all of the options available to build a database. These include the obvious columns, table, and indexes, as well as Andromeda extensions like automations, and our cascading security assignments.

http://www.andromeda-project.org/pages/cms/Database+Reference.

Postgres users will recognize some direct transpositions of Postgres features, such as using the automation formula "SEQUENCE" to create an auto-increment column.

This is equivalent to a function reference or API reference. It details all of the options available when writing out a database specification. The publishing of the reference, following up the Introductions and Tutorials, finishes up the first pass of documentation about the database abilities of Andromeda.

Focus will now shift to making equivalently simple data-driven zero code interfaces that are aware of the data dictionary and make use of it.

Andromeda is an application framework that builds Postgres databases.

The latest version of Andromeda now makes use of the YAML format for our database specification files, replacing a home-grown solution used in the past. The database specification file is the heart of an Andromeda application, as it contains a complete description not just of table structures, but of security, constraints, and automations.

Adopting YAML gives us the advantage of using a format that people are already familiar with (and growing more so), plus the advantage of using a freely-available parser, and of course not having to maintain our own parser any more. Further, I was able to increase the level of error-checking and trapping when processing the database spec file.

What it all comes down to is the ability to type out in a text editor huge data sets without resorting to the horrors of XML. There is virtually no overhead for tagging, as YAML makes use of indentation, dashes, and colons to encode structure. We use only a subset of the features, using little more than indentation and colons to encode structure. The files are easily writable and readable by both people and machines, a real triumph that the YAML folks should be proud of (http://www.yaml.org).

The tutorials and Introduction on the website have been updated to reflect the new format.

http://www.andromeda-project.org

Andromeda is a freely available database application framework written in PHP that builds PostgreSQL Databases.

In recent weeks we announced that we were working on support for Windows, and that new releases would have continuing improvements.

At this time, with the help of user feedback, we have vastly improved our installation instructions and (we hope) found and removed all significant glitches that stand in the way of day-to-day development with Andromeda on Windows. Andromeda can now be used to develop on Windows.

The remaining issues rest with version control and source control, which work well in Linux but not at all yet on Windows. These are not necessary for developing an application, and application code can still be copied at any time from a dev node to a production node.

The Andromeda homepage is http://www.andromeda-project.org

Andromeda is a database application toolset that targets PostgreSQL, and is written in PHP. In the past couple of weeks we have made a few changes to get it running on Windows. It is fully functional on a single workstation, but source and version control still need some code changes.

The project home page is

http://www.andromeda-project.org

with a special page just for Windows installs:

http://www.andromeda-project.org/pages/cms/Running+On-Windows

Hats off to the Postgres team for such a painless Postgres install on Windows!

If a description of a database is going to be complete, it must include automated values. If the automated columns are specified precisely (rather than in a vague natural language), then the spec can be used to build code that implements them.

In this entry I'd like to propose an answer to the basic question, "What do those specs look like?" What kind of automations can we support and how do we specify them? This is not about implementation, triggers vs. views or what kind of views, but about specification, stating where the values come from and what they attach to.

Consider the basic English statement that a customer may not exceed their credit limit. If we translate this into a spec, we know we have two numbers, a credit limit, and some quantity I'll call "exposure" that is the customer's total balance. It seems clear enough that we are saying the one cannot exceed the other, but where exactly did the "exposure" come from?

I would suggest first that "exposure" is in fact a property of a customer, and so must be specified as a column in the customers table. As far as the formula for "exposure", we will push the problem back by proposing a simple expression. "exposure" = "orders_total" + "invoices_total". This leaves us needing to define the "orders_total" and "invoices_total", which it should be clear are also properties of the customer table.

There is no mystery in defining "invoices_total" as SUM("orders.open"), taking for granted that the "open" amount of orders is itself determined by the status of the order ("invoices_total" follows the same basic idea). This simple example actually reveals the shape of all non-trivial calculations, because it does two crucial things. First it assumes a mechanism for copying values from table to table, and second, it reveals a scheme for building complex calculations through composition.

First lets look at composition. If we want to convert complex program code to database specifications, we must not only be able to reduce the code to its most elemental formulas, but we must also rebuild that complexity using nothing but individual column definitions. Clearly the first of two sine qua nons is to be able to express one calculated column in terms of another.

The second sine qua non is the ability to specify a meaningful way to copy values from one table to another. This turns out to be the foreign key. In simple cases of one foreign key between two tables, we can specify a variety of operations. At SDS we give the names "FETCH" and "DISTRIBUTE" to mechanisms for copying data from parent to child, and the familiar functions SUM, COUNT, MIN, etc. are used to aggregate data to parents. There are others as well, but all of them are variations or modifications of these basic operations.

So it turns out that if we want to be able to specify calculations that go beyond simple row extensions, all we have to do is follow the foreign key. This should come as no surprise. After all, if we specify a database with the correct keys, then the user's requests for calculated values and constraints ought to find easy expression by copying values from table to table along foreign keys and building complexity through composition.


The topics discussed on Ken's blog usually are related to the Andromeda project, http://www.andromeda-project.org, a postgres-based PHP database application toolset.