The tables in the `mysql` database use MyISAM only because they had to be
stored somehow and MyISAM offers a reliable set of features useful for
keeping configuration metadata. ISAM is one of the most basic database
design models and with the replacement of MySQL's own ISAM engine with
MyISAM, that table type became the logical respository of the actual
system information. I do not think that the different storage engines
refer to those tables very often, anyway. I think that most of the
processing that needs those tables (mostly permissions checking) happen
BEFORE the engine-specific calls are made. However, I have not stepped
through the code all of the way so I cannot say this for certain.
Anything special your engine can do will need extensions to the SQL parser
in order to allow the user to specify what they would like to do. There
are many examples of how to make new commands understandable by the SQL
parser. If I understand just part of your structure correctly, you are
clustering and replicating clusters (groups) of database servers into one
larger virtual server. There could be a lot of code you can re-use from
the NDB engine for this purpose.
The parallels between your engine and the NDB engine are numerous (but NOT
identical). I think that looking over their implementation may give you
some ideas of how you could implement your DDE/DDS server system. You use
3-phase commits, NDB uses 2-phase commits, you do distributed
transactions, NDB does distributed transactions, your data can physically
exist in a number of different computers so can NDB data. As I say, there
is probably a lot you can learn by dissecting their code.
Make sure you also read through how to build your own engine and look at
some of the sample engines to see examples of handling the
optimizer-to-engine interface calls.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Fabricio Mota" <fabricio.mota@stripped> wrote on 11/12/2005 11:25:43 PM:
> Ok, Shawn, I agree.
> But as I've noticed, the tables used in database MySQL seems to be of
MyISAM
> type. So, I don't know if is a good idea to modify the MyISAM engine
only
> for that.
> Do you think could it be a good idea to develop another specific engine
only
> for managing this three tables?
> Or do you think it could be better to increment the SQL92 language with
> specific commands for DDE management? Such as
>
> create dde_group my_dde_group;
> drop dde_group my_dde_group;
>
> create dde_server 'mysql://server1.mysql.com:3306' as 1;
> drop dde_server 1;
>
> subscribe database Database1 from dde_server 1 into my_dde_group;
> unsubscribe database Database1 from dde_server 1 from my_dde_group;
>
> And query their data as a virtual table, or a view.
> Tell me your opinion
>
> Fabricio
> -----Mensagem original-----
> De: SGreen@stripped [mailto:SGreen@stripped]
> Enviada em: sabado, 12 de novembro de 2005 15:47
> Para: Fabricio Mota
> Cc: internals@stripped; Sanja Byelkin
> Assunto: Re: RES: RES: System Tables
>
>
>
>
> "Fabricio Mota" <fabricio.mota@stripped> wrote on 11/12/2005 09:46:07
AM:
>
> > Hi Sanja,
> >
> > My idea is to perform a distributed data system (DDS) to this
engine. A
> DDS
> > will be composed by at least one group. Each group may involve many
> MySQL
> > servers, and each server may belong to more than one group.
> >
> > So, the best way I've seen is to manage it into 3 system tables:
> DDE_SERVER,
> > DDE_GROUP and DDE_SERVER_GROUP.
> > For example: to subscribe a server into a group, I must: a) insert
the
> > server into DDE_SERVER table, if not exists; b) insert a relation
> between
> > the new server and the existent group in DDE_SERVER_GROUP. So, all
the
> group
> > multicast communication will be proceeded to synchronize all
servers.
> >
> > Sanja, may I send the doc spec to you? (it's better shown in page
11).
> >
> > Thank you again
> >
> > Fabricio
> >
> > -----Mensagem original-----
> > De: Sanja Byelkin [mailto:sanja@stripped]
> > Enviada em: sabado, 12 de novembro de 2005 07:09
> > Para: Fabricio Mota
> > Cc: internals@stripped
> > Assunto: Re: RES: System Tables
> >
> >
> > Hi, Fabricio!
> >
> > On Fri, Nov 11, 2005 at 11:13:45PM -0300, Fabricio Mota wrote:
> > > Ok, I'll study these codes.
> > >
> > > But I need to create 3 system tables in mysql database. These
tables
> > should
> > > manage persistent data, and make specific actions (specific code
> routines)
> > > when user try to insert, update or delete data into or from them.
Do
> you
> > > think these codes you adviced me could answer these (new)
questions?
> >
> > I am not sure that it is right decision to add something to mysql
> > database. If you describe why you need that tables in mysql maybe I
Will
> > agree with you, but now I have bad strong feeling about adding
> > something to mysql database.
> >
> > To perform some actions on changing some special tables you have 2
> > options:
> > 1) use triggers.
> > 2) prohibit access direct access to that tables and use special
> > commands to update them (like CREATE/DROP PROCEDURE)
> >
> > [skip]
> >
> > --
> > __ ___ ___ ____ __
> > / |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
<sanja@stripped>
> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> > /_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
> > <___/ www.mysql.com
> >
> >
>
> The way the MySQL product is designed, everyhing specific to a method
of
> managing tables is to be managed within that particular "engine". If
you
> need 3 tables to track who is in a group (etc) then your engine needs to
> keep up with that information internal to itself (and isolate the rest
of
> MySQL from needing to ignore the activity of your engine.)
>
> Those tables in the mysql database are general to ALL of the database
> engines (MyISAM, InnoDB, BDB, Archive, Blackhole, etc) While those
> structures specific to certain engines (row-level locking and
> multi-versioning for InnoDB, data partitioning and two-phase commits for
> NDB, etc) are maintained within the engines themselves.
>
> Does that make sense?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
