1. EDB from a system designer's viewpoint
posted by: shenpen at freemail.hu
Hi!
I'm new to EUPHORIA. I like it very much, because it seems to achieve the
impossible: to be simple and elegant, but still able to get close to the
machine if needed. I'm quite enchanted that only one step under the
elegant EUPHORIA library routines there is usually a machine_proc()
or machine_func() - this is the way things should be in all languages... 
I'm actually more of a system designer (SD) than a programmer, so what I'm
most interested in is the EDB. Well, it seems a bit "untraditional" to me.
So let me ask how traditional relational SD issues are
usually handled in EDB.
1) Keys.
What EDB call "key" is what SD's usually call "primary key" - "the
thing that must unique in all records in a table". Let's suppose you have an
accounting/business software. Invoicing, inventory management etc.
You put the items you sell in a table. Let's suppose just three fields:
Item No., Description,Sales Price. Item No. is uniqe, so it's the (primary) key.
But you should maintain a secondary key (in other words: an index) for the
Description, to be able to quickly show your items on screen in ABC-order.
You don't want to use a sorting algorithm every time - try to sort ten thousand
items in ABC order and you will see why. How to maintain secondary keys
(indexes)
in EDB?
2) Transaction management, commit/rollback features.
Let's suppose you sell something with an invoice. You have two choices:
Your accounting software should:
- insert a record in the Issues Invoces table (and print it)
- insert a record in the Customer Entry table to show the change of
the customer's balance
- insert a record in the Inventory Movement Entry table to show the change
of inventory
- insert records in the General Ledger Entry table
and check many, many things in the way. This 4 inserts and one
printing is called a transaction.
Now, let's suppose some problem happens in the process: the customer is over the
credit limit, you have
no inventory, or someone pulls the plug of the computer off the wall.
In this case, NONE of the above 4 inserts and 1 printing may happen or else you
have
and inconsistent database or the Tax Office takes your head for
printing and invoice but not booking (posting) it in your accounting database.
So you must first put these records and the printing process into some
kind of buffer, check everything, and insert and print them in one step -
it's called commiting (or flushing) the transaction.
Or if some problem occurs, you insert nothing and print nothing - it's called
rolling back the transaction.
How to do it in EDB?
3) Multi-user features - locking.
One of the key points of having a multi-user database is the above-mentioned
transaction management. For example, you may see an inventory of 1 of some item,
but it may be sold by someone else in the middle of your invoice issuing process
if she pressed "Issue" button 0,00000001 seconds earlier. In this case, you must
roll
back the transaction, give an error message etc.
Anothe issue is locking. You issued an invoice, and the software got to the
point of inserting Inventory Movement Entries. The key of this table is
something like "Entry No.", an integer, which must be in strictly growing
order by 1 without any holes, or else the Tax Office will think you deleted
some entries to cheat with the tax. So what you do? Create the records in the
memory of your computer, lock the Inventory Movement Entry table, find the last
record, assign
next Entry No. to your record and insert it, and then unlock the table.
(If you have many users, you even need record-level locking like in MS SQL.
But even table-level locking could be a good compromise for less than 50 users.)
It seems EDB supports only database-level locking - it means while you insert
Inventory Movement Entries to sell stuff, someone else can't post Fixed Asset
depreciations (which has absolutely no effect to the Inventory Movement Entry
table) in the General Ledger Entry table. It means that over 10 users they
will hate the software. Or is there another way in EDB?
bye,
Miklos Hollender
System Designer / Developer
Budapest, Hungary
2. Re: EDB from a system designer's viewpoint
>EDB is great for simple databases, and it's easy to use.
>What you are wanting to do requires something better
>(which will also be harder to use). Something like PostgreSQL
>or Oracle.
>
>
Really, I've always thought calling it Euphoria *Database* was a bit
misleading. It'd be possible to write a better database engine in
Euphoria, but no one has.
3. Re: EDB from a system designer's viewpoint
On Sat, 17 Jan 2004 03:41:23 -0800 (PST), Miklos Hollender wrote:
>1) Keys.
No. You have to maintain separate tables to simulate this.
>2) Transaction management, commit/rollback features.
See next
>3) Multi-user features - locking.
Not really. You could build a set of updates, and lock exclusively
prior to verifying records still exist/still do not exist etc before
applying the updates.
You'd have to write your own log file for rollback though ;-((
There are however some other possibilities (which I have not tested):
EDS/Net by Jonas Temple
Tsunami wrappers by Euman [don't think this has proper locking]
ODBC(3) by Tone Skoda, Matthew Lewis, and Jonas Temple
SQL(3) by Jonas Temple, Matthew Lewis, and Ray Smith
You can find these by searching at
http://www.rapideuphoria.com/contrib.htm
Regards,
Pete
4. Re: EDB from a system designer's viewpoint
----- Original Message -----
From: "Pete Lomax" <petelomax at blueyonder.co.uk>
> There are however some other possibilities (which I have not tested):
> EDS/Net by Jonas Temple
> Tsunami wrappers by Euman [don't think this has proper locking]
Tsunami doesn't apply any locks when a file is opened in multi-user mode until
you insert, update or delete a record...
then a lock is imposed, but only for the few milliseconds it takes to modify the
file and update the file's header.
Locks are not applied during any read operations when in multi-user mode.
> ODBC(3) by Tone Skoda, Matthew Lewis, and Jonas Temple
> SQL(3) by Jonas Temple, Matthew Lewis, and Ray Smith
>
> You can find these by searching at
> http://www.rapideuphoria.com/contrib.htm
>
> Regards,
> Pete
5. Re: EDB from a system designer's viewpoint
- Posted by kbochert at copper.net
Jan 18, 2004
--Alt-Boundary-17567.269269966
Content-description: Mail message body
On Sat, 17 Jan 2004 03:41:23 -0800 (PST), Miklos Hollender wrote:
>> 1) Keys.
>No. You have to maintain separate tables to simulate this.
>> 2) Transaction management, commit/rollback features.
>See next
>> 3) Multi-user features - locking.
>Not really. You could build a set of updates, and lock exclusively
>prior to verifying records still exist/still do not exist etc before
>applying the updates.
>You'd have to write your own log file for rollback though ;-((
>There are however some other possibilities (which I have not
>tested):
>EDS/Net by Jonas Temple
>Tsunami wrappers by Euman [don't think this has proper locking]
>ODBC(3) by Tone Skoda, Matthew Lewis, and Jonas Temple
>SQL(3) by Jonas Temple, Matthew Lewis, and Ray Smith
And don't forget SQLite:
-)Easy interface -- 3 functions, open, close, exec are enough --
about a dozen lines of Eu each.
-)Small -- 181K dll on windows
-)Fast -- about twice as fast as MySql or PostGres
-)Euphoria-ilke -- All fields are variable length, ideal for storing EU
objects
-)Locking performed on entire database -- can be slow if heavily
loaded. (see next point)
-)No separate database server.
>You can find these by searching at
>http://www.rapideuphoria.com/contrib.htm
>Regards,
>Pete
Karl Bochert
--Alt-Boundary-17567.269269966
Content-type: text/html; charset=US-ASCII
Content-transfer-encoding: 7BIT
Content-description: Mail message body
<?xml version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial"><span style="font-size:10pt">On Sat, 17 Jan
2004 03:41:23 -0800 (PST), Miklos Hollender wrote:</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>>
1) Keys.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>No. You
have to maintain separate tables to simulate this.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>>
2) Transaction management,
commit/rollback features.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>See
next</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>>
3) Multi-user features -
locking.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>Not
really. You could build a set of updates, and lock
exclusively</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>prior to
verifying records still exist/still do not exist etc before</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>applying
the updates.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>You'd have
to write your own log file for rollback though ;-((</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>There are
however some other possibilities (which I have not
>tested):</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>EDS/Net by
Jonas Temple</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>Tsunami
wrappers by Euman [don't think this has proper locking]</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>ODBC(3) by
Tone Skoda, Matthew Lewis, and Jonas Temple</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>SQL(3) by
Jonas Temple, Matthew Lewis, and Ray Smith</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">And don't
forget SQLite:</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">
Easy
interface -- 3 functions, open, close, exec are enough --
about a dozen lines of Eu each.</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Small --
181K dll on windows</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Fast --
about twice as fast as MySql or PostGres</span></font></div>
<div align="left"><font face="Arial"><span
style="font-size:10pt">Euphoria-ilke -- All fields are variable length,
ideal for storing EU
objects</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Locking
performed on entire database -- can be slow if heavily
loaded. (see next point)</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">No separate
database server.</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">>You can
find these by searching at</span></font></div>
<div align="left"><font face="Arial"><span
style="font-size:10pt">>http://www.rapideuphoria.com/contrib.htm</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span
style="font-size:10pt">>Regards,</span></font></div>
<div align="left"><font face="Arial"><span
style="font-size:10pt">>Pete</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Karl
Bochert</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span
style="font-size:10pt"> </span></font></div>
--Alt-Boundary-17567.269269966--
6. Re: EDB from a system designer's viewpoint
MySql is not "Commercial Grade". Although good in it's own right,
it is lacking some dbm features. MySql is a "file based" B-Tree
keyed file system with SQL layered on top.
For "Industrial Strength" OSS, there are Postgres and Firebird.
Another good "file based" dbm, is the BerkleyDBM. It has "transaction"
journalization, something that MySql lacks.
see: http://www.sleepycat.com
Pick is the Old PICK Database/operating system. However, the database
engine was latter rolled out as a stand alone product. The direct
heir to PICK database is UniVerse/UniData, now owned by IBM when they
acquired Informix. There is also the MvBase from JBASE. All these can be
considered 4th Generation PICK DBM's, in that they support SQL,
transaction journal's, external Index key files and several other
features.
MaVerick is suppose to be a OSS implementation of the PICK dbm,
but in reality, it is a JAVA API that uses Postgres as the database
engine while simulating MultiValues.
As far as I can find, there is no "real" open-source PICK database
engine anywhere.
sixs wrote:
>
>
> Hello,
> I am wondering about Mysql. Isn't it one that is commercial grade.? Is
> the PICK database the old PICK operating system and database? Is PICk
> open source now or low cost?
> Jvandal
>
--
Ben Duncan Phone (601)-355-2574 Fax (601)-355-2573 Cell (601)-946-1220
Business Network Solutions
336 Elton Road Jackson MS, 39212
"Software is like Sex, it is better when it's free" - Linus Torvalds
