Ecere Database Access (EDA)
From EcereWiki
---// This page is a quick draft and it's content may be somewhat inaccurate or incomplete //---
Drivers
- Ecere Database (EDB) [Functional]
- MemoryEDB [Functional]
- SQLite [Functional]
- MySQL Client [Implementation In Progress]
- PostgreSQL Client [Implementation In Progress]
- XML [Planned]
- Firebird [Planned]
- ODBC [Planned]
Draft walkthrough #1
So first thing is the "dbtable"
syntax is
dbtable "TableName"
{
TypeOfField fieldIdentifierForECCode "fieldNameInDatabase";
};
fieldIdentifierForECCode is used to access the field from eC code
(active records, database programming through object oriented classes)
This will generate a class
called "RowTableName"
now you can use this class as sch
RowTableName row { };
you could add an entry to the database (entry = row = record)
row.Add()
then you could say
row.fieldIdentifierForECCode = TypeOfField { bla bla };
You could also do it the more generic way... which would be
Row row { tbl = dbtable("TableName") };
row.Add();
row.SetData(dbfield("TableName", "fieldNameInDatabase"), TypeOfField { bla bla });
that is the NOT active record way.... harder and longer.
Row row { tbl = dbtable("TableName") };
could also be written
Row row { dbtable("TableName") };
- this will add a recode {bla bla} ?
this will add a record in TableName
we defined TableName to have only one field per record, of type TypeOfField
bla bla is just example member assignemnts for TypeOfField
I don't know what TypeOfField is
it could just be an int
so if it it's an int you would do
row.fieldIdentifierForECCode = 1234;
- if i want add a record agian , i use row.add()
yes
- row.fieldIdentifierForECCode = 2345;
exactly.
row is like a "pointer" or "cursor"
Let's browse the database now!
after you added, say 1234 and 2345
now you can browse through it
by doing
row.First();
then, if using Active Records
you can just access it as
- now cursor rewind to first
Jerome
int value = row.fieldIdentifierForECCode;
then you can do
row.Next();
int value2 = row.fieldIdentifierForECCode; // Now 2345
if using Row and not the generated Active Record (AR) RowTableName
then you can use row.GetData(dbfield("TableName", fieldIdentifierForECCode), value);
there is a special kind of Table!
"ID" Tables
often in relational database
you will use an id to point to an entry in another table
So in table A
you have an integer value... which is the ID of an entry in table B
you can use very useful EDA features for this
dbtable "TableA"
{
BEntry entry "entry";
};
dbtable "TableB" BEntry
{
BEntry id "id";
String name "name";
};
like this...
now you get a generated "RowTableA" and "RowTableB", as usual
but you also get a "BEntry" class
which derives of "Id"
(which derives of uint)
- must use "*Entry" ?
no
you can name it anything
in med.ec
I call the table
Medicaments (with an s)
and the Table entry ID class "Medicament" (without an s)
what this can do
Is when you say
RowTableA row { };
row.First();
BEntry entry = row.entry;
now entry is the integer id of the class already
but you can use
String name = entry.name;
directly... You do not need to use a "Row" class
it is like saying
9.name
and gives you access to the fields without using a row class
Now I think Id class is not defined in EDA, it is defined in "idList.ec" in Med
but I think you can use it even if it is not defined...
idList.ec in Med adds extra stuff to it
- you will add it in EDA latter ?
hmm yes not sure
maybe it should be moved to EDA
you can use idList from Med for now
there is also Indexes
Indexes can speed up search
When using ID tables...
It is automatically indexed by id
dbtable "TableB" BEntry
{
BEntry id "id";
String name "name";
};
Automatically makes dbtable("TableB") indexed by "id"
- must use "id" ?
you can use anything
it knows it's the id
it searches for the first "BEntry"
inside the field definitions
type BEntry
BEntry bla "bla";
would work also.
dbtable "TableB" BEntry
{
BEntry bla "Bla";
String name "Name";
};
needs to be the same as the ID class name
specified after TableName
the Find!
so
if you search for an entry
well the find is automatic
with ID tablse
BEntry id = 5;
id.name
you can simply do this hehe
it will find the entry 5, and get the name
But say you want to search for the name "jia"
you could add an index to TableB
dbtable "TableB" BEntry
{
BEntry id "id";
String name "name";
dbindex name;
};
RowTableB row { dbindex("TableB", name) };
row.Find(dbfield("TableB", name), middle, nil, "jia");
now if Find is successful (returns true), you can save the ID
BEntry jiaID = row.id;
you can also do finds without an index, but it is not efficient
you would simply use
RowTableB row { };
dbindex is powerful also
it can index by more than one field...
dbindex id, > name byIDAndDescendingName;
will make an index sorted by ascending id, then by descending name
and you can access it as
dbindex("TableB", byIDAndDescendingName)
and you can have more than one index in a table
- how to have more than one index, you say index is same to the name of table
For ID tables
the default index is the id
but you can add one like I did above
dbtable "TableB" BEntry
{
BEntry id "id";
String name "name";
dbindex name;
};
it has a default index by "id"
and another index by the "name" field...
(when only one field is in the index, and no index name specified, it is the same as the field ("name"))
you can add two
dbtable "TableB" BEntry
{
BEntry id "id";
String name "name";
dbindex name;
dbindex id, > name byIDAndDescendingName;
};
id
is the first field
so
1. Indexed by field (ascending order)
2. Indexed by name (descending order because of '>')
and byIDAndDescendingName is the name of the index
To be used to access the index, as such: dbindex("TableB", byIDAndDescendingName)
i think we covered all the basics of EDA :)
=====================================================================================
you can do
RowTableA row { };
char * name = row.entry.name;
that's why it is very useful
if you have many relational tables linked together
if everything is in an ID table
Assuming you have a table of Persons, and a table of Companies, you can do:
Person person;
char * name = person.company.name;
If you have 2 rows with id = 1
it will return the first one
but you should never have 2 rows with the same ID in an ID table
very bad idea
RowMyTable row { };
row.Add();
row.id = 1;
row.name = "jia";
row.Add();
row.id = 1;
row.name = "jerome";
pritnf("%s\n", MyEntry { 1 }.name); // prints out "jia", I think... first row... but it is really undefinde behavior
no in relational database
ID is managed by the user
it is your responsibility
but EDA manages the sysID
so you can just always do
row.Add();
row.id = row.sysID;
that should work.
- how to delete a record?
row.Delete();
to go through all rows it is very elegant
RowMyTable row { };
while(row.Next())
{
printf("%s\n", row.name);
}
this prints out all the name fields for a row
to update
just make the row point to the record
through a find
or by walking through it
RowMyTable row { };
row.First();
row.name = "new name";
you have to be careful when using "get" on e.g. strings, because it allocates new memory and it must be freed
char * name = row.name; // name must be freed!
delete name; // when no longer needed
also, rows should be deleted when no longer needed...
only at the very end, not after changing record
- i want know how to search without kin , some times i want find a people which named "jia", and his age is "19", now i want know his address
yes you can use Find
you can also use
bool FindMultiple(FieldFindData * findData, MoveOptions move, int numFields)
RowMyTable row { };
FieldFindData findData[2] =
{
{ dbfield("MyTable", name), { s = "jia" } },
{ dbfield("MyTable", age), { i = "19" } },
};
row.FindMultiple(findData, middle, 2);
i = 19, not i = "19" sorry
if you use Find
zouyuanjia
what is that "middle" meaning
you can use 2 Find one after the other
second one, you use "here" for MoveOptions I think
middle means from the center of the index, which is a binary tree
so middle is most efficient, starts at the root of the tree
when using 2 consecutive Find, you need to use "here" for MoveOptions
so that it keeps searching starting from the current row
example:
if(row.Find(dbfield("MyTable", name), middle, nil, "jia") && row.Find(dbfield("MyTble", age), here, nil, 19))
{
printf("Found!\n");
}
you could write
sizeof(findData) / sizeof(FieldFindData)
instead of 2
row.FindMultiple(findData, middle, sizeof(findData) / sizeof(FieldFindData));
the number of items to find
number of fields to search
both will do the same thing
FindMultiple can be more efficient
especially if your table is indexed properly
if you have a
dbindex name, age byNameAndAge;
and then use
RowMyTable row { dbindex("MyTable", byNameAndAge) };
then FindMultiple is very fast.
if the first fields of FieldFindData matches the order of the index, then it can use the index to search
Draft walkthrough #2
it's just a very simple example of using EDA/EDB
and it's got the eda.ec / edb.ec files you'll need
ok
med.ec - that contains the database schema for the application
it defines the tables and the fields to be used for active records
the DB stuff is relatively new to ecere :D
The goal is to spuport multiple data sources
like MySQL, Oracle, ODBC...
all kind of data providers
and SQL and stuff
but right now the query and stuff
there's no sql
but it's pretty neat still
and take a look at med.ec
so if you look at the project
eda.ec - that's Ecere Database Access... The Ecere DB layer basically
edb.ec - that's the Ecere Database Engine.... our minimal embedded relational database provider
(The only driver working so far)
idList.ec and gui.ec are some helper functions for the app...
the Id class in idList.ec is actually important... the concept of an "id" is used by eda , so you'll actually need that
class Id : uint
idList.ec and gui.ec
are more internal detail
the app itself
is form1.ec + med.ec
and if you see everything it does...
it lets you edit all the different kind of things you can enter for each medicine
and has a little test to test out your knowledge
(I did that to help out a girl with her studies)
so say we start at med.ec, line 101
We're defining a table, "Restrictions", and then the following identifier "Restriction" means this will be a table using an ID, and fields in other tables which want to refer to a particular restriction will use the class
"Restriction"
so there's only two fields (records) in a Restriction entry
an id
and a string
the name of the restriction
if you follow in the actual app
You see the Restriction tab button
this does everything
creates the table, set up the fields, the active records
so then
you can create a restriction
Restriction restriction;
and access the name as
restriction.name
String name "name";
String is the data type, name is the active record member name, and "name" is the actual name in the database
note that the last two could be different
so you could rename the active record member for the code, but keep the same field name in the database to keep data compatibility
So if you look a little bit down, at the main table: dbtable "Medicaments" Medicament
you see we define the restriction there of type Restriction
so this refers to the Restriction table automatically
and say we have a medicine
Medicament medicine;
we could go ahead and do: medicine.restriction.name
and it's automtically gonn go through the tables
now if you look at the app.. the way EDA works is you have a DataSource
ds = DataSource { driver = "EDB" }; // specifies to use the EDB driver (only one working so far)
this could be "MySQL" or whatever
and then there's a special keyword: db = database_open(ds, "med");
this opens the database with a file name med.edb (will create it if it doesn't exist)
it's important that database_open be in the same source file as the DB schema, as it sets it up according to the schema defined in the same module file (all the dbtables)
You could have multiple schemas / dbs in different source files...
(all this not is not super clean yet, but it does work great)
all tables in that app have an id field... but it's not required to have one
if you don't have one, you omit the 2nd parameter to dbtable (dbtable "Formes" /* this one: Forme */)
it will be in eda or what not
we actually had an eda project
but we kept fiddling with it in different projects, so each project had their local copy hehe
you can just put it in an "EDA" folder in your project for now
so...
most of the editors in this app etc is all magical with the gui.ec
but you typically access a row
dbtable "Medicaments" Medicament will also automatically build a specialized Row class
"RowMedicaments"
RowMedicaments row { }; // this instantiate a row
and you can access members from there
row.nomGenerique = "acetaminophen";
and you can find ids and stuff
it's all very powerful
You could also use the base row class as such:
Row row { tbl = dbtable("Medicaments"); }
and you can refer to a field from a table as:
dbfield("Formes", name)
you really need to play a bit with it to get the feel :)
no restrictions
performance is fast.
you can have indexes too
when you have tables with ids , as these
there's automatically an id-based index
so that it's efficient to seek to a particular id
but you can also specify index by fields
with the dbindex keyword
dbindex name;
for example if you want an index to search by name
and then you would access it like:
RowMedicaments row { tbl = dbindex("Medicaments", name) };
and you can do a Find on the row:
row.Find(dbfield("Medicaments", name), middle, nil, "acetaminophen");
and although EDB is very efficient and so far has done what we needed very well... I'm no DB expert =)
so those other solutions who invested a lot of efforts into DB research must have done something worth interoperating with :)
DB is not really the focus of ecere. We went into DB mainly to support contract work we're doing :)
play around with the db classes
DataSource, Database, Row, Field, Table
you have all the source code to it ;)
import ecere and eda
and put that class MyApp : GuiApplication
with the two above definitons (ds and db)
and then add tables to it slowly :)
www.ecere.com/edatest.ec
take a look at this too
you can build that with eda.ec and edb.ec
might be :)
that doesn't showcase the active records and schema
it's actually much easier to write dbapps that way :)
Return to Main Page
