Alpha Developers Network

Guest


Author Topic: Active link tables  (Read 1671 times)

0 Members and 1 Guest are viewing this topic.

peter.wayne

  • Guest
Active link tables
« on: July 05, 2009, 01:46:02 PM »
This is something I wrote up for the NY Alpha Users Group on my experience with active link tables. It describes some problems and workarounds in going from dbf tables to MySQL tables.  I hope it will help any of you looking to do the same.
- Peter
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »

Steve Wood

  • Administrator
  • Expert
  • *****
  • Posts: 706
  • Karma: +11/-0
    • AlphaToGo
  • Real Name: Steve Wood
Re: Active link tables
« Reply #1 on: July 05, 2009, 01:59:56 PM »
Speaking of User Groups, Peter can you see this forum and/or any other resource being of use to Alpha User Groups, notwithstanding the fact that there hardly are any. I mean, the does concept of User Group even make sense anymore, as in a local group of bodies that get together over beer and sandwiches, and chat about Alpha? If they still serve a function, then I want to help foster them. What can we do?
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »
---
Steve Wood
www.alphatogo.com

Finian Lennon

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
Re: Active link tables
« Reply #2 on: July 05, 2009, 03:19:11 PM »
Hi Peter:

Thanks for posting this. Although I don't think I'm going to get the go ahead for a conversion to SQL (at least now) I may go back to it as a "stealth" project. BTW, when I installed the latest MySQL recently it defaults to using the INNO db system. I notice that you specified the MyISAM index in your code. Any particular reason for that?

Finian
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »
Finian

Finian Lennon

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
Re: Active link tables
« Reply #3 on: July 05, 2009, 03:37:50 PM »
Steve:

I don't know if user groups make much sense any more. Here in Atlanta we have a regular meeting attendance of 3 and celebrate when it gets, however temporarily, to 4 or 5.

We can always hope that V10 sweeps all before it and attendance jumps dramatically. Either way, what else would I do on the third Tuesday of the month?

Finian
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »
Finian

peter.wayne

  • Guest
Re: Active link tables
« Reply #4 on: July 05, 2009, 04:34:17 PM »
User groups are decreasingly relevant or increasingly irrelevant as there are more and more other resources to obtain information and tips -- like this board and the Alpha message board.  I still pick up a few things at our local user group and I never regret preparing something when it's my turn -- it helps to focus my thoughts.
As for InnoDB vs MyISAM -- initially I chose InnoDB because I thought, hey, it's got to be better because it has transaction logging and rollback capability. Then I realized that to create a transaction, the entire transaction is placed in a temporary file, then read or abandoned if the transaction is incomplete. For most small records that isn't much overhead, but I have used MySQL for storing scanned documents that are, in some cases, 80 pages long and multi-megabytes.  Using InnoDB was a significant slow-down: first, the document is scanned to a blob; then InnoDB makes a temporary copy somewhere; then the temporary copy is read into the database and made permanent; and then the blob is written to the log file so it can be replicated on other computers. MyISAM tables were much faster as there is no temporary storage.
Also, nearly all of my data entry is to single tables. So there is no need to define a transaction. The only situation in which data entry is to more than one table is when I create bills, and I might possibly make those InnoDB tables just so I can have transactions with headers and detail lines, so that there are never any orphans.  I would need to write my own SQL statements for saving the header and details all as one transaction, it would not be possible with active link tables.
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »

Steve Wood

  • Administrator
  • Expert
  • *****
  • Posts: 706
  • Karma: +11/-0
    • AlphaToGo
  • Real Name: Steve Wood
Re: Active link tables
« Reply #5 on: July 05, 2009, 05:29:08 PM »
Thanks, and sorry for redirecting the topic. For your original post, after I think about it awhile, I will create some sort of 'code archive' section here, but more effective than on the Alpha site, categorized or some way to provide effective workarounds and novel ideas. I was thinking about a "code that works" section here or perhaps on the wiki (its coming, but may be 90 days) where we can post examples and syntax that works.
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »
---
Steve Wood
www.alphatogo.com

Finian Lennon

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
Re: Active link tables
« Reply #6 on: July 06, 2009, 02:04:41 PM »
Quote
As for InnoDB vs MyISAM -- initially I chose InnoDB because I thought, hey, it's got to be better because it has transaction logging and rollback capability.
That was my thinking too. I seem to remember reading about some other advantages of INNODb over MyISAM but, right now, can't remember what they are. I'll have to dig back in to Paul DuBois' MySQL book for a refresher.

Thanks for the info.

Finian
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »
Finian

Martin

  • Newbie
  • *
  • Posts: 19
  • Karma: +0/-0
    • http://www.martinwcole.com
Re: Active link tables
« Reply #7 on: July 22, 2009, 05:18:43 AM »
Any word yet on desktop features for V10?
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »

peter.wayne

  • Guest
Re: Active link tables
« Reply #8 on: July 27, 2009, 08:34:13 AM »
Just a word of caution on the use of active link tables. As of this date (7/27/09) there are some significant performance issues with active link tables and MySQL. Kurt at Alpha Software thinks it is related to Alpha's MySQL native driver. In any event, using active link tables has frozen computers in my office. I am temporarily abandoning active link tables entirely and going to direct SQL calls.  This means that I have to use Xdialogs and not forms but until this issue is fixed, I think active link tables and MySQL are not compatible.
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »

peter.wayne

  • Guest
Re: Active link tables
« Reply #9 on: July 28, 2009, 07:18:33 PM »
Follow-up, for any of you thinking of using active link tables: Active Link tables DO work with MySQL. They don't work well with MyISAM tables, but they perform fine with InnoDB tables. MySQL has 2 "engines": the MyISAM engine and the InnoDB engine. The InnoDO engine supports row--level locking, while the MyISAM engine only supports table-level locks. The locks issued by the active link code were apparently sufficient to cause our systems to freeze up. When I changed the table type to InnoDB the problems disappeared.
The InnoDB is the more recent, transaction-processing engine for MySQL.  I assume (though I don't know) that other transaction-processing databases also support row level locking, so if my experience with MySQL is a guide, you shouldn't have a problem with DB2, Oracle, SQL Server, etc.
« Last Edit: December 31, 1969, 04:00:00 PM by Guest »