I just migrate from DBISAM to EDB and am learning the new features and the differences between the two. In addition I have 3 questions.

Update the database at users system: With DBISAM do it like this: Existing database files are not overwritten by the installation process. A particular table holds the version number of the database, the program knows its version number. Now, if the version number of the program is higher, a procedure is called, which contains the definitions of all the current tables. With ALTERTABLE the program updates the database.
Since I  not see through the edb catalog feature, I wonder, if there is a better way under EDB?

As i read, there are no more semaphores locks available under EDB. Is used this to limited the concurrent user access to the db, it is part of my licensing model. What is the substitute for semaphore locks? And works with single user, multiuser (FileServer) and C/S (with edb server) environment?

User rights: Can the privileges i of users be extended? For example, some user should not be able to see certain Fields (fieldvalues, e.g. creditcard numbers) or not be able to do some batchupdates like updating prices with a factor, but should be able updating existing records. Can this be handled by the user of the edb system or i am forced to implement my own user system?

Thank you.

Thomas

>Update the database at users system: With DBISAM do it like this: Existing database files are not overwritten by the installation process. A particular table holds the version number of the database, the program knows its version number. Now, if the version number of the program is higher, a procedure is called, which contains the definitions of all the current tables. With ALTERTABLE the program updates the database.
>Since I not see through the edb catalog feature, I wonder, if there is a better way under EDB?

This should work equally as well under ElevateDB. There have been a few threads on the subject so I'd suggest searching the newsgroups.

>As i read, there are no more semaphores locks available under EDB. Is used this to limited the concurrent user access to the db, it is part of my licensing model. What is the substitute for semaphore locks? And works with single user, multiuser (FileServer) and C/S (with edb server) environment?

Have a look in the extensions newsgroup under EDB User Count. Terry Swiers produced a nice component to do this sort of thing.

>User rights: Can the privileges i of users be extended? For example, some user should not be able to see certain Fields (fieldvalues, e.g. creditcard numbers) or not be able to do some batchupdates like updating prices with a factor, but should be able updating existing records. Can this be handled by the user of the edb system or i am forced to implement my own user system?

As far as I know it only goes down to table level, but you can create Views with a subset of the columns which would effectively allow you to set access to specific columns. Personally though I'd look at implementing your own system since that will allow you to manage access to features of your application that do not come under database control. This is what I've done.

Roy Lambert [Team Elevate]

> Have a look in the extensions newsgroup under EDB User Count. Terry Swiers
> produced a nice component to do this sort of thing.

If you do use the component that I pushed up and have installations with a
large number of users, make sure that you create a separate session to the
database to keep track of the users and set the lockretrycount and
locktimeout properties to zero.  Otherwise you end up with a cumulative
delay for each user that is connected to the system.

--
---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com

Now shipping Atrex 13. For more information go to
http://www.atrex.com/news.asp

Atrex Electronic Support Options:
Atrex Knowledgebase: http://support.atrex.com/KB/root.aspx
Email: support@atrex.com
Newsgroup: news://news.1000years.com/millennium.atrex
Fax: 1-925-829-1851
Phone: 1-925-828-5892 (M-F, 9a-5p Pacific)
---------------------------------------  

Thomas,

<< Update the database at users system: With DBISAM do it like this:
Existing database files are not overwritten by the installation process. A
particular table holds the version number of the database, the program knows
its version number. Now, if the version number of the program is higher, a
procedure is called, which contains the definitions of all the current
tables. With ALTERTABLE the program updates the database.
Since I  not see through the edb catalog feature, I wonder, if there is a
better way under EDB? >>

In EDB, all tables, views, procedures, and functions have a user-defined
version number associated with them:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Object_Versioning

that you can use to determine if a particular object has been updated or
not.

<< As i read, there are no more semaphores locks available under EDB. Is
used this to limited the concurrent user access to the db, it is part of my
licensing model. What is the substitute for semaphore locks? And works with
single user, multiuser (FileServer) and C/S (with edb server) environment?
>>

In EDB, you can just use the TEDBEngine.LicensedSessions property to control
how many sessions can concurrently access a given configuration.

<< User rights: Can the privileges i of users be extended? For example, some
user should not be able to see certain Fields (fieldvalues, e.g. creditcard
numbers) or not be able to do some batchupdates like updating prices with a
factor, but should be able updating existing records. Can this be handled by
the user of the edb system or i am forced to implement my own user system?
>>

Currently, no, it cannot be handled by EDB.  There are plans to allow for
more privilege types, but there are some technical hurdles involved since
there are issues if a database is separated from the configuration where the
custom privilege types are defined.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:


<<In EDB, you can just use the TEDBEngine.LicensedSessions property to control
how many sessions can concurrently access a given configuration. >>

This works with local and remote sessions?
And it works if one application is shut down improperly?

Thomas

Thomas


From memory the answer is no, yes, partially

It doesn't work in file/server mode, it does in client/server and I think in client/server the dead session cleanup will take care of it.

Roy Lambert [Team Elevate]

Task: Updating a database structure at users environment.

If i understood it right: modifying a tables structure is only possible via SQL statement. Altertable as a method of the TDBISAMTable class is no more available in EDB. Right?

I read the manual, did some examples. After that i am not sure how to handle the following situation with EDB.

There are 3 majorversions of the database: 1, 2, 3. Lets talk about a certain table "eg".

in the first version of the table/database the field "notes" is not included.

in the second version the field "notes" was added to the structure of the table and used in application (persistent fields).

in third version some new fields were added, which are not important for this example. It is only important that in version 3 are fields that are not included in version 1 and 2.

to update a tables structure i use such a statement (shortened to min)

ALTER TABLE "eg"
ADD COLUMN Notes CLOB

A user of majorversion 1 wants to update. My app checks the version and makes the necessary changes. No problem so far as i see.

A user of majorversion 2 wants to update. My app checks the version. But here the ALTER Table statement mentioned above raises an excaeption, cause, as we remember, in majorversion 2 this field already exists.

If this is right and i haven't missed something: I have to lay down all different versions in code and if a update is needed i must go back in version history to see what fields are already there and then decide which fields are to add to come to the current major version.

If i am right, this is a task for some one who has stroke dead father and mother.

In most of my apps (5) i am in major programm version 4 or 5, this means up to 10, 12 maybe 15 versions of the databases. Additionally there are a lot of specialized customer versions of the database.
Until now we talk only about the tables. To use all the features of EDB there are triggers, stored procs, views, functions, which want to be updated. so versionhistory would be a mess.

With DBISAM i make it this way, since years now and there were no problems with the updates ever.

with TableToCreate do
        begin
        DatabaseName:=CreateDatabaseName;
        TableName:='kliefer';
        Exclusive:=True;
        frmDBUpdate.Label3.Caption := TableName;
           begin
           with FieldDefs do
              begin
              Clear;
              Add('KLieferID',ftAutoInc);
              Add('KundenID',ftInteger);
              Add('Lieferadresse',ftMemo);
              end;
           with IndexDefs do
              begin
              Clear;
              Add('','KLieferID',[ixPrimary,ixUnique]);
              Add('KundenId_SX','KundenID');
              end;
           AlterTable(1031,1,0,False,'','',4096,512, -1, '', nil, '','', False);
           end;
        end;

No messing around with any version history. New fields or index will be appended, no drops, no renames. Ok, this solution is not first class, but things have to work in their environment.

I hope that i have missed something with EDB, if not it seems EDB is to good for my needs.

Sorry for long explanation and steeling your time.   Nevertheless thank you for your hints.

BTW: The SQLtoCode extension i tryed. But as i see i need to do a Reverse Engineering of current and old version of database to work properly. If so, i have the versionhistory mess again.

Thomas


One of the MAJOR differences between DBISAM and ElevateDB is the catalog which is pretty easy to query.

Example:

SELECT Name FROM Information.TableColumns WHERE TableName = 'Fred'

You now have a result set showing the columns that already exist in your table. You can use this to test against and dynamically build an ALTER TABLE sql statement. If there are only a couple of columns that might want adding it would probably be easier to test for each one

SELECT Name FROM Information.TableColumns WHERE TableName = 'Fred' AND Name = 'Bloggs'

If RecordCount for the query is non-zero don't add the column.

You could write a small program in Delphi (or SQL/PSM - the script language in ElevateDB) so that you passed in a table name, a stringlist holding column names and definitions and it would do the mods for you.

Triggers etc will be a bit more difficult

Roy Lambert [Team Elevate]

Thomas,

<< This works with local and remote sessions? >>

Yes.

<< And it works if one application is shut down improperly?  >>

With a multi-user, file-sharing architecture, EDB is dependent upon the file
server for clearing the session locks used to determine the licensed session
count, but yes, the session locks should eventually get cleared by the file
server and free up the session slot being used by the application that was
killed on the client.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thomas,

<< A user of majorversion 2 wants to update. My app checks the version. But
here the ALTER Table statement mentioned above raises an excaeption, cause,
as we remember, in majorversion 2 this field already exists. >>

You have to use the catalog information in a script to do what you want:

SCRIPT
BEGIN
  DECLARE TempCursor CURSOR FOR TempStmt;
  DECLARE TableVersion DECIMAL(19,2) DEFAULT 3.00;

  PREPARE TempStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
  OPEN TempCursor USING 'eg';

  IF (ROWCOUNT(TempCursor)=1) THEN
     FETCH FIRST FROM TempCursor ('Version') INTO TableVersion;
     IF (TableVersion=1) THEN
        CLOSE TempCursor;
        PREPARE TempStmt FROM 'ALTER TABLE eg ADD COLUMN Notes CLOB';
        EXECUTE TempStmt;
     END IF;
  ELSE
     CLOSE TempCursor;
     PREPARE TempStmt FROM 'CREATE TABLE eg (MyColumn VARCHAR(20), NOTES
CLOB) VERSION 3.00';
     EXECUTE TempStmt;
  END IF;

END

<< With DBISAM i make it this way, since years now and there were no
problems with the updates ever. >>

And DBISAM did not support triggers, procedures, functions, constraints,
etc.  Add those in and you've got a similar "mess", very quickly.  The only
reason that DBISAM worked easily is because it was feature-restricted in
this respect, and simply punted on those features that EDB fully implements.

<< No messing around with any version history. New fields or index will be
appended, no drops, no renames. Ok, this solution is not first class, but
things have to work in their environment. >>

Yes, and it only worked if you were *adding* fields and indexes, and only
when you added them to the end of the list of fields or indexes.

<< BTW: The SQLtoCode extension i tryed. But as i see i need to do a Reverse
Engineering of current and old version of database to work properly. If so,
i have the versionhistory mess again. >>

You can use the reverse-engineering in the ElevateDB Manager to generate an
update script by specifying a source and target database.  The generated
script isn't the most efficient in the world because it drops all
constraints and then re-adds them when altering tables, but it will work.

--
Tim Young
Elevate Software
www.elevatesoft.com