OpenEuphoria

1. Finding data in Euphoria databases

• Posted by ZNorQ <znorq at holhaug.com> Jul 17, 2006
• 566 views

I have plans to make use of Euphoria's database.e (EuDB) archive, and I've 
done some minor testing, but I haven't gotten around to really stress test
it.

But I've come as far as to see how it is build up - using 'keys' and 'data'
sections for each record.

Generally I create a key by using the db_table_size()+1 function, usually by
adding some kind of prefix - like "COMP" for "COMPANIES", etc., but I found
out that the database only have a built in search function on the key, and
not the data itself.

This makes me think that in order to find information effectively in typical
fields where people would search for information should really be part of
the key..? AFAIK, keys can't be changed after it's creation (which I see as
a good thing), which again would make it difficult to use since this
information can't be corrected if any punching mistakes where made.

The only way I see to search for information in records data section now, 
is to loop through each record and 'find'ing or 'match'ing each data section
with what-ever one would need to search for.

atom SearchIdx
for cntrec = 1 to db_table_size() do
  if   match("rds", db_record_data()) 
  then SearchIdx = cnt
       exit
  end  if
end for

This is quite slow. Of course, one could limit the search by looking at
the specific sequence element (field) taken from db_record_data() so that
it dosen't search through the whole record, but it would still require the
code to read the whole record before searching for the information.

Question 1;
  Anyone got any experience using large amounts of data? How effective is
  EuDB in finding, inserting and deleting records?

Question 2;
  Finding; What solution do you use?

Kenneth

3. Re: Finding data in Euphoria databases

• Posted by don cole <doncole at pacbell.net> Jul 18, 2006
• 517 views

ZNorQ wrote:
> 
> I have plans to make use of Euphoria's database.e (EuDB) archive, and I've 
> done some minor testing, but I haven't gotten around to really stress test
> it.
> 
> But I've come as far as to see how it is build up - using 'keys' and 'data'
> sections for each record.
> 
> Generally I create a key by using the db_table_size()+1 function, usually by
> adding some kind of prefix - like "COMP" for "COMPANIES", etc., but I found
> out that the database only have a built in search function on the key, and
> not the data itself.
> 
> This makes me think that in order to find information effectively in typical
> fields where people would search for information should really be part of
> the key..? AFAIK, keys can't be changed after it's creation (which I see as
> a good thing), which again would make it difficult to use since this
> information can't be corrected if any punching mistakes where made.
> 
> The only way I see to search for information in records data section now, 
> is to loop through each record and 'find'ing or 'match'ing each data section
> with what-ever one would need to search for.
> 
> }}}
<eucode>
> atom SearchIdx
> for cntrec = 1 to db_table_size() do
>   if   match("rds", db_record_data()) 
>   then SearchIdx = cnt
>        exit
>   end  if
> end for
> </eucode>
{{{

> 
> This is quite slow. Of course, one could limit the search by looking at
> the specific sequence element (field) taken from db_record_data() so that
> it dosen't search through the whole record, but it would still require the
> code to read the whole record before searching for the information.
> 
> Question 1;
>   Anyone got any experience using large amounts of data? How effective is
>   EuDB in finding, inserting and deleting records?
> 
> Question 2;
>   Finding; What solution do you use?
> 
> Kenneth

I've never used EUdb. But I have my own database system for keeping track of
movies for my store. Actually I have six dbs "open regular", "open adult", "open
dvds", "sold regular", "sold adult", and "sold dvds". I know I should have "open
reg dvds" and "open adult dvds" etc.
But at the time I set this up DVD's were a novilty not the norm as they are
today.
When I sell a movie I move it from "open" to "sold". If I buy back a movie I
  move it from "sold" to "open". Each db has a lot of fields starting with "movie
  number". Field number 11 has a variable length field with the history of each
  movie. Date bought, date sold, date rented etc. So it's a pretty extensive db.
  When I start my program I load all the dbs then I create an index by movie
  number. This takes a little while.

for x=1 to 6 do
 for y=1 to length(db[x]) do
  index[x]=append(index[x],db[x][y][1])
 end for
end for

Now I have an index of all my dbs by movie number. From then on when look up a
  movie I look up the movie number in the index which streers to the right db very
  quickly. However in the rare event that I have look up a movie by a string
  (example "Matrix") then I have to search all the fields. This, of course, takes
  longer. I could make an index of each field at start up. But then again this
  would take longer.

I think I see what you mean by pointers. This would entail using
  allocate_string() and keeping track of the pointers that way.

I doubt if this will be of any help to you but as to your question #2, that's
  how I do it.


Don Cole

5. Re: Finding data in Euphoria databases

• Posted by ZNorQ <znorq at holhaug.com> Jul 18, 2006
• 528 views

ChrisBurch2 wrote:
> 
> Hi
> 
> This also replies to the large app question.
> 
> Yes eu is eminently suitable for large apps - but choose your app - its less
> suitable for action based fast response type apps (eg fp shooters), but very
> suitable for database type projects / web apps (this is not a exhaustive list)
> 
> Look at JetVetSQL - lots and lots of lines. It is very easily maintained by
> one person (me), because I've set my own set of organisational / coding rules,
> and everythings as compartmentalised / sub filed as much as possible.
> 
> To Kenneth ZNorq - if you want a _really_ easy database system, for lite apps,
> (usable in CGI apps, although I haven't tried this myself, then use eusqlite.
> One function does EVERYTHING - you do need to know some sql though
> 
> data = sqlite_query_database("Database", "sql_commands")
> 
> Thats it!
> 
> Its fast, working on 40MB + databases, safe - I like it any way.
> 
> Chris

Hey Chris,

This sounds very interesting! I'll sure give it a try! Thanks.

Kenneth

Oh, btw, 'ZNorQ' is not my surname, just a nick... :)

7. Re: Finding data in Euphoria databases

• Posted by ZNorQ <znorq at holhaug.com> Jul 18, 2006
• 552 views

don cole wrote:
> 
> ZNorQ wrote:
> > 
> > I have plans to make use of Euphoria's database.e (EuDB) archive, and I've 
> > done some minor testing, but I haven't gotten around to really stress test
> > it.
> > 
> > But I've come as far as to see how it is build up - using 'keys' and 'data'
> > sections for each record.
> > 
> > Generally I create a key by using the db_table_size()+1 function, usually by
> > adding some kind of prefix - like "COMP" for "COMPANIES", etc., but I found
> > out that the database only have a built in search function on the key, and
> > not the data itself.
> > 
> > This makes me think that in order to find information effectively in typical
> > fields where people would search for information should really be part of
> > the key..? AFAIK, keys can't be changed after it's creation (which I see as
> > a good thing), which again would make it difficult to use since this
> > information can't be corrected if any punching mistakes where made.
> > 
> > The only way I see to search for information in records data section now, 
> > is to loop through each record and 'find'ing or 'match'ing each data section
> > with what-ever one would need to search for.
> > 
> > }}}
<eucode>
> > atom SearchIdx
> > for cntrec = 1 to db_table_size() do
> >   if   match("rds", db_record_data()) 
> >   then SearchIdx = cnt
> >        exit
> >   end  if
> > end for
> > </eucode>
{{{

> > 
> > This is quite slow. Of course, one could limit the search by looking at
> > the specific sequence element (field) taken from db_record_data() so that
> > it dosen't search through the whole record, but it would still require the
> > code to read the whole record before searching for the information.
> > 
> > Question 1;
> >   Anyone got any experience using large amounts of data? How effective is
> >   EuDB in finding, inserting and deleting records?
> > 
> > Question 2;
> >   Finding; What solution do you use?
> > 
> > Kenneth
> 
> I've never used EUdb. But I have my own database system for keeping track of
> movies for my store. Actually I have six dbs "open regular", "open adult",
> "open
> dvds", "sold regular", "sold adult", and "sold dvds". I know I should have
> "open
> reg dvds" and "open adult dvds" etc.
> But at the time I set this up DVD's were a novilty not the norm as they are
> today. 
>   When I sell a movie I move it from "open" to "sold". If I buy back a movie
> I move it from "sold" to "open". Each db has a lot of fields starting with
> "movie
> number". Field number 11 has a variable length field with the history of each
> movie. Date bought, date sold, date rented etc. So it's a pretty extensive db.
> When I start my program I load all the dbs then I create an index by movie
> number.

> This takes a little while.

Well, I don't know how many records, and how much data we are talking about,
but as for me, I'm creating a follow-up database for my work, and that
database could include 10s of thousands of records containing project
information, mechanical- and instrumentation/telecom. equipment, valves,
pipes, documents, etc. etc. Each table could contain MANY fields.

I see EuDB as a powerful tool, but I believe that the search functions
might be improved, unless it's me that haven't understood it's full
potential yet. I guess the "key" section of it is the clue - building
keys using fields that the user would search in, perhaps? But then again,
if those fields where changed at a later stage, the key and the data
wouldn't match anymore, and I would be forced to delete the record and
re-create it. I don't like that solution.

> 
> }}}
<eucode>
> for x=1 to 6 do
>  for y=1 to length(db[x]) do
>   index[x]=append(index[x],db[x][y][1])
>  end for
> end for
> </eucode>
{{{

>   Now I have an index of all my dbs by movie number. From then on when look

So, it is really just the index you have stored in memory, not the db 
itself? I understand this as the movie number (which is probably printed on
the cover of each movie) being the key part of the seach, and that titles
only are used when some dude asks you if you got "Matrix"..?

> up a movie I look up the movie number in the index which streers to the right
> db very quickly. However in the rare event that I have look up a movie by a
> string (example "Matrix") then I have to search all the fields. This, of
> course,
> takes longer. I could make an index of each field at start up. But then again
> this would take longer.
> 
>   I think I see what you mean by pointers. This would entail using
>   allocate_string()
> and keeping track of the pointers that way.
> 
Well, I'm not really looking for pointers as such, my main question was
really just if it where possible to pass pars as reference, and that 
question has been answered - but I'll keep allocate_string() under my
pillow and check up on it now and then and see if I could have a use for
it later.. :D Thanks!



>   I doubt if this will be of any help to you but as to your question #2,
>   that's
> how I do it.
> 
"Roger" that.. Thanks for the feedback, Don. 
> 
> Don Cole

Btw, you still have VHS in your store??

9. Re: Finding data in Euphoria databases

• Posted by ZNorQ <znorq at holhaug.com> Jul 18, 2006
• 549 views

Julio C. Galaret Viera wrote:
> 
> ZNorQ wrote:
> > 
> > I have plans to make use of Euphoria's database.e (EuDB) archive, and I've 
> > done some minor testing, but I haven't gotten around to really stress test
> > it.
> > 
> > But I've come as far as to see how it is build up - using 'keys' and 'data'
> > sections for each record.
> > 
> > Generally I create a key by using the db_table_size()+1 function, usually by
> > adding some kind of prefix - like "COMP" for "COMPANIES", etc., but I found
> > out that the database only have a built in search function on the key, and
> > not the data itself.
> 
> If you create keys that way and at a given moment delete a record, the next
> key you attempt to create will fail with DB_EXISTS_ALREADY.
> 

Yeah, I know, so for now I'm not deleting records, but rather flagging them
deleted. I was thinking of maybe using something like
 

NewKey = db_record_key(db_table_size()) + 1

(of course, it isn't as simple as that, as NewKey would be a sequence, but
it's just to show what my intentions are - to get the keypart of the last
record and add one to that.. As far as I can see, this would always give me
the next id. Unless the records in a table is sorted on something other than
record id / key, this should work fine.. Am I right?

> 
> > 
> > This makes me think that in order to find information effectively in typical
> > fields where people would search for information should really be part of
> > the key..? AFAIK, keys can't be changed after it's creation (which I see as
> > a good thing), which again would make it difficult to use since this
> > information can't be corrected if any punching mistakes where made.
> 
> You will have to delete the old record and insert the updated one.
> 

Jepp, and I really don't like that solution...

> > 
> > The only way I see to search for information in records data section now, 
> > is to loop through each record and 'find'ing or 'match'ing each data section
> > with what-ever one would need to search for.
> > 
> > }}}
<eucode>
> > atom SearchIdx
> > for cntrec = 1 to db_table_size() do
> >   if   match("rds", db_record_data()) 
> >   then SearchIdx = cnt
> >        exit
> >   end  if
> > end for
> > </eucode>
{{{

> > 
> > This is quite slow. Of course, one could limit the search by looking at
> > the specific sequence element (field) taken from db_record_data() so that
> > it dosen't search through the whole record, but it would still require the
> > code to read the whole record before searching for the information.
> >
> 
> Right, too slow.

And don't I know it... Did a test on a million records - had to cancel that, as
I had a better chance of dying of old age than finding the data. (Well, it took
abit long anyway.) Finding the key part didn't take a second though!
(Yes, yes, I know - a million records is a large amount of data... Might'a over-
done that abit.. :) )

> 
> > Question 1;
> >   Anyone got any experience using large amounts of data? How effective is
> >   EuDB in finding, inserting and deleting records?
> 
> 
> I have very large databases and for me are fine. Since my key is only one
> atom,
> finding, inserting and deleting is ok.
> What is really a pain for is when you delete a large number of records all at
> once, so my option is to copy the records I want to keep into a new database,
> skipping those I would delete. But again this is a painfull process:
> rebuilding
> that way a database with 150.000 records takes me 2 hours on a P4 2.66!
> 
Atom as key; You say finding is ok using just an atom as a key part, so this
means that the key is the only field you need to find what you want?

Mass deletion; You do this manually, or using some sort of flagging of the
records
you want to delete?

> 
> > 
> > Question 2;
> >   Finding; What solution do you use?
> > 
> > Kenneth
> 
> It depends on what you want to do. If you are doing very complex queries
> (perhaps
> simultaneously), change to another db system (MySQL, SQLite, etc).
> 
> If your queries are not very complex you can design some type of relational
> database. Split one table into several. Let say you have a table with
> information
> about movies. If you know people will search for titles, create another table
> with movie title as a key and the key from the first table where you store all
> the information about the movie, as the data. That way you can create as many
> tables as indexes you need with a reference to key of the main table.
> 
> JG

Sounds good, already received a link to EuSQLite - which I'll have a look
through.

Thanks, JG for your valuable feedback.

Kenneth

11. Re: Finding data in Euphoria databases

• Posted by don cole <doncole at pacbell.net> Jul 18, 2006
• 536 views

ZNorQ wrote:


> 
> Btw, you still have VHS in your store??

 All kinds. But I think it would be best if you e-mailed me on this.

Don Cole

13. Re: Finding data in Euphoria databases

• Posted by Chris Burch <chriscrylex at aol.com> Jul 18, 2006
• 545 views
• Last edited Jul 19, 2006

Julio C. Galaret Viera wrote:
> 
> Kenneth,
> 
> It seems you see more cons than pros in EDS for what you want to do. May be
> the solution for you is EuSQLite. It hides all the complexity behind only one
> function.
> 
> EDS is very good for what it was designed for and not for a relational
> database.
> 
> EuSQLite allows more flexibility in queries thand EDS but the latter is much
> faster.
> When I began using Euphoria, I did some tests on Windows and EDS by far
> outperforms
> EuSQLite in speed.
> 
> If you are programming on Windows and don't mind a bit complex programming
> then
> I suggest Tsunami Record Manager. Though not a relational database either, its
> speed is incredible and allow multiple indexes. 
> 
> Of course if you have installed MySQL on your machine, this is the best
> solution.
> 
> JG

Hi

I'm actually going to argue sqlite's corner here.

The implication above (and I'm not taking offence), is that sqlite is slow.

The sqlite_query_database() function is not optimised, and opens the database,
performs the query, and closes the database, then returns the query data.

This was deliberate. It creates simplicity of use, when performing single
queries makes no practical difference to readability, and (IMHO) is safer than
leaving the database open.

However, for query intensive processes, and binding and running queries, you can
open the database, perform your queries, then either leave the database open for
more queries, or close it. This is much faster.

For a speed comparison of the most popular databases look here

http://www.sqlite.org/cvstrac/wiki?p=SpeedComparison

In some cases sqlite outperforms MySQL - I haven't tested these myself, and
eusqlite is more than capable of performing these - effectively it passes the
calls off to the external library.

In my case, I use sqlite at three of my surgeries, with multiple users, with 40
Mb ish databases 296693 records, searched in 1-3 seconds, and this is with
opening and closing the database.

The major reason I use sqlite is because of its simplicity to use, which funnily
enough fits in very well with my philosophy for using euphoria. Furthermore its
free, open source, freely distributable, a tiny library, and very powerful.

In my initial research for a database to use with JetvetSQL, I did consider
Tsunami, but after some investigation, discarded it for this purpose, simply
because of the programming complexity you mentioned. I do not have enough
information to question its capability.

Chris

http://members.aol.com/chriscrylex/euphoria.htm
http://uboard.proboards32.com/
http://members.aol.com/chriscrylex/EUSQLite/eusql.html

15. Re: Finding data in Euphoria databases

• Posted by ZNorQ <znorq at holhaug.com> Jul 19, 2006
• 513 views

Greg Haberek wrote:
> 
> Try this for inserting records, it works well and I use it all the
> time. It is slightly better than your method, since you can't
> guarantee that the "record count + 1" key is not already taken. It
> defaults to 1 if no records exist. I suggest using key 0 to store
> column header data. :)

Yeah, I was thinking the same - record 0 (or 1?) is the title section.

> 
> }}}
<eucode>
> -- This function auto-increments the key
> -- for each data element inserted.
> global function db_auto_insert( object data )
> 
>     integer count
>     object last_key, next_key
> 
>     count = db_table_size()
>     if count = 0 then
>         next_key = 1
>     else
>         last_key = db_record_key( count )
>         next_key = last_key + 1
>     end if
> 
>     return db_insert( next_key, data )
> end function
> </eucode>
{{{


Well, db_table_size() + 1 works if you don't delete anything, but then again
we can't live with that. I've now seen that using my second proposed code
for assigning a new key (same principile as yours above) might also be
flawed; If I use keys to link tables together, and I delete the last record
and insert new - I will end up with the same key id as the deleted record
had - and that would be catastrophic for a relationalship type database.
Which kinda brings me to creating an own table that contains the last key
id for each table? - OR - what if I used record 1 (the title) and added an
element in the data section that contained the last used key? Perhaps better,
as I hate to create dummy tables...

> 
> > Question 1;
> >  Anyone got any experience using large amounts of data? How effective is
> >  EuDB in finding, inserting and deleting records?
> 
> It uses a binary search on keys, which is rather fast. It also keeps a
> list of free space between records in the file to make the most
> efficient use of space. Adding a record smaller than the one you just
> remove will not take up any extra space. The only thing it lacks is
> optional encryption or compression, which I've been trying to figure
> out how to add without breaking backward-compatibility. I've no luck
> so far.

Well, we can only hope that the database.e is updated with new functions
in Eu 3.0.. As for encryption, why should that be a hinderance? You can
encrypt the data as you insert it, or..?

> 
> > Question 2;
> >  Finding; What solution do you use?
> 
> First, I try my best to organize the data into smaller tables. Then I
> simply iterate through the keys and evaluating the data. I have yet to
> notice any real issues in searching for data this way. My World
> Distance Calculator uses this method to search through all the ZIP
> codes in the United States and is still pretty efficient.
> 

I do too break down the data as much as possible. Every bit of information
bound to one particular 'object' is kept in one table. If 2 or more tables
have a relationship, I use the key as a reference between them.

For example - 2 tables; TBL_PEOPLE and TBL_COMPANIES
Each person in the people register have it's own unique id - or key.
Same with the companies. Lets say this is the information in each table;

[TBL_PEOPLE]
KEY,     STR_NAME_LAST,    STR_NAME_FIRST, LNK_EMPLOYED_BY (field names)
PPL-001, Haberek,          Greg,           CMP-002
PPL-002, Nilsson,          Kenneth,        CMP-001
                                             ^ link to records in TBL_COMPANY

[TBL_COMPANY]
KEY,     STR_COMPANY_NAME,                     STR_COMPANY_ADDRESS
CMP-001, Aker Kvaerner,                        Stavanger
CMP-002, i-dont-know-what-comp-greg-works-for, nor-do-i-know-its-location


> ~Greg
> 
> 

Thanks for your views, Greg.

Kenneth

17. Re: Finding data in Euphoria databases

• Posted by "Greg Haberek" <ghaberek at gmail.com> Jul 19, 2006
• 528 views

> Well, we can only hope that the database.e is updated with new functions
> in Eu 3.0.. As for encryption, why should that be a hinderance? You can
> encrypt the data as you insert it, or..?

Encryption I can do on the data level, but compression is a bit
harder. Compression operates on large amounts of data, so if I try to
compress data as I insert it, I'm losing potential compression because
I'm only operating on the data for that record. Basicly what I'd like
to see is compression file functions in Euphoria. Then we wouldn't
need to worry about the database compressing itself.

~Greg

19. Re: Finding data in Euphoria databases

• Posted by Bob Elia <bobelia200 at netzero.net> Jul 19, 2006
• 519 views

Julio C. Galaret Viera wrote:
> 
> ZNorQ wrote:
> > 
> > I have plans to make use of Euphoria's database.e (EuDB) archive, and I've 
> > done some minor testing, but I haven't gotten around to really stress test
> > it.
> > 
<SNIP>
> I have very large databases and for me are fine. Since my key is only one
> atom,
> finding, inserting and deleting is ok.
> What is really a pain for is when you delete a large number of records all at
> once, so my option is to copy the records I want to keep into a new database,
> skipping those I would delete. But again this is a painfull process:
> rebuilding
> that way a database with 150.000 records takes me 2 hours on a P4 2.66!
<SNIP>

Julio,

 This issue has come up before; Try this:

include sort.e

global procedure db_delete_records(sequence toDelete)
-- Delete a sequence of record numbers
    -- This could be unnecessary but it prevents corruption of database.
    -- Time to sort is very small in comparison to actual deletion.
    toDelete = sort(toDelete)
    for i = length(toDelete) to 1 by -1 do
        db_delete_record(toDelete[i])
    end for
end procedure

where 'toDelete' is a sequence of record numbers in an EDB table.

On my 1.1 Ghz AMD Athlon it takes 14.7 seconds to delete 4,000 out of 100,000
records chosen at random.  This figure is from a test program where the record
 keys are consecutive integers; I haven't tested it on 'live' data.

  It does leave a large number of entries in the freespace list; I don't know
the long-term impact of that.

Bob

21. Re: Finding data in Euphoria databases

• Posted by Pete Lomax <petelomax at blueyonder.co.uk> Jul 20, 2006
• 532 views

On Wed, 19 Jul 2006 06:16:37 -0700, ZNorQ <guest at RapidEuphoria.com>
wrote:
>Greg Haberek wrote:
>> 
>> compression is a bit
>> harder. Compression operates on large amounts of data, so if I try to
>> compress data as I insert it, I'm losing potential compression because
>> I'm only operating on the data for that record. Basicly what I'd like
>> to see is compression file functions in Euphoria. Then we wouldn't
>> need to worry about the database compressing itself.
>> 
>> ~Greg
>for compression, I see your point. I was intending to making my database
>to function as a file storage as well, and such compression would be a nice
>to have function.
May I ask, why not just store the database on a compressed drive?
(On win98 goto start/programs/accessories/system/drivespace and read
the help)

Pete