Acronym for this breed of software?

March 27, 2005 11:21 PM

There's a breed of software which you point at a database and which then generates an add/edit/delete web interface for all the tables. (examples a, b) I've seen a four-letter acronym used to refer to it, but I can't come up with it and I'm spending way too much time surfing to find it. Help!
posted by Tubes to computers & internet (10 comments total)

MySQL?
posted by kenchie at 11:44 PM on March 27, 2005

CRUD (Create, Retrieve, Update and Delete).
posted by voon_42 at 11:46 PM on March 27, 2005

Microsoft, on CRUD. I haven't used products that generate this kind of code for quite a few years. It has its place, though, in building a quick single use app. Of course you're better off building your own components to do the same processes, because it helps you learn the tools.
posted by SteveInMaine at 3:43 AM on March 28, 2005

Not a ruby-head, but Ruby on Rails will do that, among other things, if you're willing to buy into the whole platform.
posted by costas at 3:48 AM on March 28, 2005

It's not a web interface, but it's pretty easy to write stored procedures that generate insert, update, delete, whatever statements by looking at the system tables. Th egenerated string can then be "played back" as SQL commands.

e.g., to create a select list for any table named in @parm: select "select " + ( select b.name from syscolumns b, systables a where b.id = a.id and a.name = @parm order by colid )

(Note examples are in Transact-SQL, the Sybase and MSSQL Server dialect.)

A more complicated example generates the SQL required to create views with correct joins, assuming that you've followed a particular column naming convention (id columns are named "id" and columns that join to a table's identity column are named referenced_table_underscore_referenced_column ; note that to preserve space, I've deleted most of the line feeds that would make it easier to read:
create proc gen_view (
@t1 varchar(255), @t2 varchar(255) = null, @t3 varchar(255) = null, @t4 varchar(255) = null, @t5 varchar(255) = null, @t6 varchar(255) = null, @t7 varchar(255) = null) as
begin
create table #gvtabnames ( id int not null identity primary key, corname char(1), tabname varchar( 255 ) )

insert #gvtabnames values ( 'a', @t1 )
insert #gvtabnames values ( 'b', @t2 )
insert #gvtabnames values ( 'c', @t3 )
insert #gvtabnames values ( 'd', @t4 )
insert #gvtabnames values ( 'e', @t5 )
insert #gvtabnames values ( 'f', @t6 )
insert #gvtabnames values ( 'g', @t7 )

create table #gvtemp ( id int not null identity primary key, order1 int, order2 int, output varchar(255), colname varchar( 255 )corname char(1), tabname varchar( 255 ) )

insert #gvtemp select 0, 0, 'create view ' + isnull( @t1 + '_', ' )+ isnull( @t2 + '_', ' ) + isnull( @t3 + '_', ' ) + isnull( @t4 + '_', ' ) + isnull( @t5 + '_', ' ) + isnull( @t6 + '_', ' ) + isnull( @t7 + '_', ' ), null, null, null

insert #gvtemp select 0, 1, 'as select ', null, null, null

insert #gvtemp select c.id, b.colorder, char(9) + c.corname + '.' + b.name + ' as ' + a.name + '_' + b.name + ',', b.name, c.corname, a.name from sysobjects a, syscolumns b, #gvtabnames c where a.id = b.id and a.name = c.tabname and a.type = 'U' order by c.id, b.colorder


insert #gvtemp values ( 100, 0, 'from', null, null, null )
insert #gvtemp select 200, a.id, char(9) + a.tabname + ' ' + a.corname + ',', null, null, null from #gvtabnames a where a.tabname is not null order by a.id
insert #gvtemp values ( 300, 0, 'where', null, null, null )
insert #gvtemp select 400, a.id, char(9) + a.corname + '.' + a.colname + ' = ' + b.corname + '.' + b.colname, null, null, null from #gvtemp a, #gvtemp b where b.colname = 'id' and a.colname = b.tabname + '_' + b.colname

select output from #gvtemp order by order1, order2
return 0
end
end
posted by orthogonality at 5:17 AM on March 28, 2005

was the acronym PHPMA? you might be thinking about PHPMyAdmin.
That seems to fit your criteria, and its also friggan awesome as a database web interface.
posted by Mach5 at 5:55 AM on March 28, 2005

CRUD! Yes, thanks Voon! And Steve, I know it's better to learn how to build 'em yourself, but once you know how it gets pretty boring to do basically the same thing over and over again for different apps.

Ortho - very interesting. I'll be playing with your SQL techniques, I'm sure they'll be handy in the future.
posted by Tubes at 7:23 AM on March 28, 2005

Related question: Anyone aware of good Java based CRUD software? I've brewed my own but would like to see if there are any popular versions out there.
posted by furtive at 8:07 AM on March 28, 2005

furtive - you might try JSPMaker. They have an online demo and a 30-day trial. I've been testing their CFMMaker and it's pretty cool.
posted by Tubes at 8:58 AM on March 28, 2005

I know it's not the answer, but here's another voice for ruby on rails. The 10 minute setup video shows how it works.
posted by seanyboy at 11:53 AM on March 28, 2005