I have used recently dadabik with views in mysql. Instead of tables, I let
dadabik manage and display views. This is a straight forward application, but
extremely powerful. For example, views can be a join of several tables or even
other views. Views can also apply user-defined functions to fields, etc. I
think it gives dadabik several features which have been suggested in the
"feature request" forum. Admittedly, users need to understand the db to use
them.

There are a two minor modifications to the data structure which would make it
easier to use dadabik in this fashion. Specifically, I propose to have two new
attributes for each table.

1. Primary key. Views don't have a primary key. So it would be good to have the
option to specify which key should act as a primary key. Currently, I hacked
get_unique_field_db to make dadabik work with views.

2. Inserttable. When a view is a join of several tables, one can not just
insert a new record because it logically does not make sense. Therefore, it
would be good to have the option to specify a table in which a new record
will be inserted.

Hi Wolf,
I have never thought to this kind of use of DaDaBIK as a way to increase its flexibility, thanks to having pointed out to me and to all the DaDaBIK community.

As you correctly noticed, in this way probably one of the main problem in implementing a DaDaBIK application on a complex and normalised relational DB, I am referring to the lack of a multiple joins support, can be solved.

Which DBMS do you use? Could you post your get_unique_field_db hack?

As far as your second point is concerned, it is quite difficult to specify which are the table and the fields to use for the insert, have you any ideas?

Bye,

Hi Eugenio,
about separe metadata from db can be insteresting if you develop dadabik to support several databases in the same installation with user privileges for each database/tables but for only one database I think it's better metadata is in the same database, in this way when you port an application only have to worry about one database.

Could be interesting redesign dadabik to use less tables, for example table_list, users/privileges and table_definitions where you define all necessary metadata. This could be done adding a field which identify to what table belongs each field.

Thank for your great work.

Hi Antonio,
yes I think that a single installation of DaDaBIK will support multiple dbs; the fact that you can't export a single db to port an application in my opinion it's the price to pay for to have more flexibility.

About user privileges: I don't know exactly how to implement them, maybe a ID_user/table/function granularity would be fine e.g. the user foo can perform insert on the table bar; any suggestions are welcome.

The last point: yes, the DaDaBIK metadata will be stored in a more organized (and relational) fashion, probably exactly as you have highlighted.

Thanks for your comment.

Bye.

Hi Eugenio,
thinking about privileges and as you point dadabik could have an extra table with privilegies for each user in each database/table. This can be done if you implemente all metadata in one table with a unique id for each combination of database/table/field. You could associate this id with each user and give read/write/deny access even for field, this would give to dadabik a great granularity level.

Regards.

I will publish soon a first version of the db schema I would like to use for DaDaBIK 5 so that we can discuss it here.

Bye.

Hi Eugenio,

yes, I use dadabik with multiple joins and it works like a charm. All my
experience is exclusively with MySql, but I don't see any reason why it
shouldn't work with other db.

The hack I used is really just a hack to get me going, clearly not something touse in the official version. I simply changed get_unique_field_db so it uses a
pre-defined key ("view_id") if there is no primary key in the table. So I
simply added these lines


if ($unique_field_name == "" OR $unique_field_name == NULL ) {
$unique_field_name = "view_id";
}

Of course with this hack the user has to make sure view_id exists in each view.
What I would propose to implement is that in the interface configurator in
admin.php the user can specify by hand a key which acts as primary key. It is
the responsibility of the user to make sure that this key exists and is indeed
unique.

Regarding in which table to insert (and delete) a record, I think it is
impossible (or at least extremely difficult) to figure it out automatically.
But it is quite obvious for the user to know. Because when you construct a
view, you always have to have one table to take the lead. When you insert a
row in that table, your view also will also have a new row. So my proposal for
implementation is again just to add a new field "Parent Table" in the
interface configurator in admin.php where the user can specify an additional
table name. For displaying the list and "details", the normal table name
which could be a view is used. For inserting and deleting, the "parent table"
is used. It is then the responsibility of the user to set "Field present in
the insert/update form?" to "yes" for the right fields. If no "parent table"
is specified, it should default to the normal table name so that dadabik works
as before. Again, just to get me going and test whether this actually works,
I implemented a hack in which I hardcoded that views have to be named
"view_xxx", and for inserting and deleting I just strip out the "view_":

function strip_view($name){
if (substr($name, 0, 5) == "view_"){
$name = substr($name, 5, strlen($name)-5);
} // end if
return $name;
} // end function strip_view


Using dadabik with views is very powerful (I still keep discovering nice ways
of doing things), but it also needs some experience on how to set up things.
So if you go this route and implement this, I am willing to write a short
tutorial how to exploit views with dadabik. I think the necessary modification
to dadabik are very minor, and the cost/benefits are enormous.

Wolf

Hi Wolf,
for the pk issue, yes, I think that a new field is the right choice and I can implement it in one of the next release.

Regarding the insert/update operations, I think it is not possible to implement a solution that always works; look at this simple example:

albums_tab (ID_album, ID_genre, title_album)
genres_tab (ID_genre, desc_genre)

create view albums_view as SELECT title_album, desc_genre FROM albums_tab inner join genres_tab on albums_tab.ID_genre = genres_tab.ID_genre

for DaDaBIK this view has two fields: title_album and desc_genre; how can I modify DaDaBIK to allow an insert on the albums_tab table?

Bye.

Hello again,

> for DaDaBIK this view has two fields: title_album and desc_genre; how can I
> modify DaDaBIK to allow an insert on the albums_tab table?

I agree that if you construct your view like this, dadabik cannot know about
the "hidden" key ID_genre and therefore there is no sensible solution for
insert. But I can't think of any reason why one would actually want to
construct the view the like this. I think one always wants to include a
descriptive key in the main table to select you entry, because this is the key
you want to be able to edit.

In your example, I assume the desc_genre is a descriptive key like "pop",
"classic", etc. And on insert you want the user to be able to select one of
those. So the solution would be to use "desc_genre" instead of "ID_genre" to
join the table, i.e.

revise albums_tab:
albums_tab (ID_album, desc_genre, title_album)

create view albums_view as SELECT title_album, desc_genre FROM albums_tab inner join genres_tab on albums_tab.desc_genre = genres_tab.desc_genre

And then setup dadabik so desc_genre only takes values from genres_tab. Now
whenever you insert a new record in albums_tab, there will be a new row in the
view. It is the responsibility of the user to construct the views
accordingly.

Now in this simple example, there is of course no benefit from using views
because dadabik already can join two tables. The benefit using view comes if
you have several tables, or want to show the results of arbitrary function,
possibly computing a value based on information taken from several tables.

To illustrate something a little bit more useful, take the three tables:
albums_tab (ID_album, ID_genre, title_album, price)
genres_tab (ID_genre, desc_genre, discount_rate)
ratings_tab (ID_genre, user_rating)

and create a view:

CREATE VIEW albums_view AS SELECT *, price*discount_rate/100. as discount FROM albums_tab left join (genres_tab left join ratings_tab using(desc_id)) using(desc_genre);

Now you can insert a row into albums_tab. And then the view (e.g. in the
dadabik's "details page") gives the discount computed based on the price of the
album and the discount rate for the genres, and also includes the rating of the
genre from a third table.

Wolf

Hi,
I don't understand you first example: in a normalized db it's usual to use an ID (ID_genre) as FK to "link" a table (genres_tab); in your example is desc_genre also the PK of genres_tab?

> in your example is desc_genre also the PK of genres_tab?

yes, that's right. The only reason why I proposed to use desc_genre is because
this is what the user sees, i.e. is used to select the genre. It is better to do
this on something descriptive rather than some key like ID_genre. If you don't
mind selecting the ID_genre instead, you could simply modify your original
example by including ID_genre in the view.

Theoretically you could even use your original example, if for example ID_genre
is automatically created. One could say create a trigger which assigns ID_genre
= 1 if title_album starts with "Bach". Then you can again insert a record in
albums_tab without ID_genre, and it will show up in the view.

All the examples have in common that the view has a parent table, and the rows
to insert in the parent table are a subset of the columns in the view. I think
this covers pretty much all useful applications.

ciao, W

Hi,
look at this example, it is common and linear as db design approach:

albums_tab (ID_album, ID_genre, title_album)

genres_tab (ID_genre, desc_genre)

albums_tab.ID_album is PK auto increment

albums_tab.ID_genre is FK

genres_tab.ID_genre is PK auto increment

e.g.
ID_album ID_genre title_album
1 1 Made in Japan
2 2 Check Your Head

ID_genre desc_genre
1 rock
2 rap
3 jazz

of course the application, for the album insert procedure, should display the description of the genre but insert, instead, the ID; the same for the view, that requires a join.

This is a very common example in which it is impossible to use a view for the insert operation, because I need a field for the user interface (desc_genre) and another one (ID_genre) for the sql insert and update commands. Note that the example is very basic and in this case a simple select_single DaDaBIK field type would have been enough.

Hi,

this is again your original example, right? Maybe I missed your point: I fully
agree that you need the select_single here, but this doesn't prevent you from
using a view if you want additional functionalities. I am not suggesting in
any way that the single_select is obsolete if dadabik can be used with views.
What I am saying is that one can do additional things.

The view I would create in your example:

create view albums_view as SELECT title_album, desc_genre, ID_genre FROM albums_tab left join genres_tab using (ID_genre)

Now you configure:
albums_tab is the "parent table"
title_album and desc_genre are visible in list and details
title_album and ID_genre are used for insert and edit
ID_genre uses single_select

So yes, the view has to be designed with the dadabik application in mind, and
it is possible to create views which cannot be used.

Wolf

OK Wolf, now it's clear.

It seems to me from your previous messages that, regarding the example, you assume that the db designer has to modify the tables design (for example using desc_genre in albums_tab instead of ID_genre) to get the correct results, and it was not a good approach.

The user can, instead, keep the table design and create views adding all the fields he needs both for the results and the insert/update views and then enable the correct ones by the interface configurator.

So there are views that you can't manage (at the insert/update level) using DaDaBIK, but, as you noticed, if you create the views with DaDaBIK in mind you can get the correct behaviour.

It seems to me a very smart solution, thanks for this talk.

Eugenio.

Thanks Eugenio this is so great!!! :)

Consider this for a great, great filemanager, it will increase the possibilities within Drupal a lot, with very limited amount of work and you already go in the Tiny direction:
http://p4a.crealabsfoundation.org/tinyfck<br />
All the best.

Hi,
could you explain better which are the additional features that tinyfck can provide to DaDaBIK? How the file manager is different from the Operating system file manager that DaDaBIK users use to upload files?

What about Drupal??

Bye,

Hey,

are you going to release a version that works without magic_quotes_gpc in the future?

They're going to remove magic_quotes_gpc in PHP6.

Sorry for responding much so late :)

Tiny Editor is now used by many CMS Systems it is multiplattform. I have adapted my dadabik to use it so the forms are also accessible with the Mac and Linux.

The Filemanager is interesting because apart from the uploading features (that are probably covered) you can also select and position uploaded files from within the rich text editor. This makes for great CMS functionality that was not covered priviously.

The workflow involves less clicks and is more logical. Lots of content can be edited out of the richtexteditor instead of switching between form elements.

I mixed the names (drupal dadabik). Sorry!

Dadabik is wonderful for managing local dbs just for my family (movies, books, etc.) and I used it for years to do that. When I got a new computer with Vista preinstalled, Dadabik wouldn't run on it so I switched to a SQLite and Ruby setup. I've since abandoned the Vista (too slow) for Linux, but I've gotten used to SQLite's low overhead and simplicity. I miss Dadabik's great ease of use and access and I check back here regularly looking for a version that supports SQLite as that would match the very light weight use I have.
Thanks for a great product.