Forum OpenACS Q&A: Holes in the acs_object_id sequence

I'd check to make sure that the sequence isn't being created that way.  As to how that could cause 600-number gaps, each PG back-end would cache 100 sequences so several backends could easily cause gaps of this sort.

I couldn't agree more.

But I don't blame anyone for my mess. I knew full well when I started out that I was running on an unsupported version, and that there wouldn't be upgrade scripts for me.

But since 4.5, upgrade scripts should be mandatory, and definitely part of the test plan.

/Lars

(1) Program a package, let's call it "ID Remapper".  The package uses
    a .vuh to respond to any URL comming in and uses a lookup table
    to figure out the appropriate redirector.  ID Remapper should be
    configurable so you can make it respond to bboard URLs, news URLS
    etc.  You should be able to specify the last part of the URL as
    well as the name of the relevant form variable to look at.

(2) Mount an instance of URL Remapper to every site_node you used to
    have on the old site.

(3) Migrate the old site in such a way that you never use the same
    URLs as in the old site.  Store the id-to-id mapping that will be
    used as a lookup tible for the "URL Remapper"

BTW it has occured to me on a several occasions before that although
it is a nice idea to have just one site-wide sequence of objects
for internal use (although I am one of those who would largely
reduce number of things we call objects), it is perhaps a bad idea to expose that sequence
to the outside world.  Lars's painful experience proves that.  As
OpenACS sites mature it will be increasingly important to be able
to move things around - merge two sites into one, split a site into
two etc.  Scripts that upgrade from one version to another won't
solve this problem.

Maybe it wouldn't be a bad idea to use one sequence per app. instance
solely for the purpose of URLs for referencing content - a composite
candidate key consisting of package_id and the "local" object_id.

As new
pieces of content don't get created so often, we don't really need
database sequences for that, just a database cell storing the last
used ID per app. instance and object type.

That way, if I create a new forum and post a message in it it will
become the message_id number 1 in that forum and will be retrievable
with /forums/foo/one-thread?message_id=1

If the whole forum has to be migrated to another instance of ACS 4
it would be trivial to write a redirector that wouldn't need an
id-to-id lookup table.

On a related note, alternative to upgrade scripts would be a convention that every application has to have
an export and import feature (to XML I suppose).  That could be used
both for migrations (possibly even to and from other systems) and
upgrades.

I agree with you completely that each package instance should use its own numbering scheme that starts from 1. This is exactly what I did for the bug-tracker. Each bug has both a bug_id, which is the object_id for acs_objects, and a bug_number, which is not a primary key, but is unique when combined with the package_id (which happens to be called project_id in this table). Only the bug_number is exposed in URLs.

Would people agree that this should be the recommended practice?

I'd also very strongly support XML-based export/import scripts, although I know that it's going to be even more painful to get people to keep those in sync than it already is with the upgrade scripts.

Alas, neither of these things are likely to happen any time soon.

/Lars

I would argue that XML is extremely well accepted, esp. within the Java community. But that's not really too important here. We recently completed a complex data migration from a non-relational sql server schema to an ACS object-oriented oracle schema. We used XML as an intermediate representation for the data. The major advantage was that all data was loaded thru our api. The major disadvantage was a serious performance penalty. If people are interested, I'd be more than happy to share more details of what we did.

With any serious data migration, where serious means having enough data that you're worried about the performance of the one time migration, we really need to consider native tools for doing the migration. Oracle provides a loader utility which is much more efficient than loading data through SQL.

For testing upgrade scripts, the problem is extremely difficult and has two distinct pieces:

  1. Is the structure of the upgraded schema correct?
  2. Did all my data make it to the right place?

The first question is much easier to test. For Oracle, I wrote a pl/sql script awhile ago which compares most of the objects in two different schemas. This package ensures that the same objects exist in both schemas and then does different validation based on the type of the object:

  INDEX (existence check only)

  LOB (existence check only)

  PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE (line-by-line check)

  SEQUENCE (check for identical next values)

  TABLE (check for same columns, same constraints, same number of rows)

  TRIGGER (check for same type, table, when_clause, text)

  VIEW (check for same number of rows, same text)

I'm not sure if this script ever made it into ACS, but I'd be more than happy to dig it up if somebody thinks it will be useful. I found it very useful to validate that a version 1.0 schema + an upgrade script is equivalent to a version 1.1 schema.

The second point (validating that the data made it) is much more difficult - I don't know of any good tools/ways to automate even parts of this validation.

Jon,

What should ns_export and ns_import do? I am very interested in looking into some general facility like this. I have a new data format that offers all the advantages of XML for data representation, but can parsed very fast, faster than a CSV format.

Also, has anyone discovered a format for representing an SQL data model, besides SQL, that can then be translated into multiple SQL dialects? It would probably need to cover sequences, views, etc. I don't have much hope there is such a thing, but just thought I'd ask.