RFE: Database client support

RESOLVED DUPLICATE of bug 81653

Status

()

Core
XPCOM
--
enhancement
RESOLVED DUPLICATE of bug 81653
16 years ago
16 years ago

People

(Reporter: Rick Parrish, Assigned: vidur (gone))

Tracking

Trunk
Points:
---

Firefox Tracking Flags

(Not tracked)

Details

Attachments

(5 attachments)

(Reporter)

Description

16 years ago
Request for enhancement to permit using Mozilla as a
stand-alone database application framework.

Part 1: RDF datasource wrapper
Part 2: Database-aware XUL Dialog widgets 
Part 3: Outliner View Database Component

1. RDF datasource wrapper

Makes a result set look like an RDF datasource. Suppose
an SQL statement like "SELECT * FROM persons" returns the
following data:

Name         Address           City         Country
------------ ----------------- ------------ -------
Joe Cool     100 Main Street   Centerville  USA
Ben Stein    200 Lincoln Lane  Centerville  USA
Ali Gator    300 Swamp Road    Centerville  USA

The above data would be mapped to an RDF datasource
equivalent to the following RDF file:

<rdf:rdf>
    <rdf:Description id="1234"
    db:query="SELECT * FROM persons">
    <rdf:Seq>
        <rdf:li>
        <rdf:Description
            persons:name="Joe Cool"
            persons:street="100 Main Street"
            persons:city="Centerville"
            person:country="USA"/>
        </rdf:li>
        <rdf:li>
        <rdf:Description
            persons:name="Ben Stein"
            persons:street="200 Lincoln Lane"
            persons:city="Centerville"
            person:country="USA"/>
        </rdf:li>
        <rdf:li>
        <rdf:Description
            persons:name="Ali Gator"
            persons:street="300 Swamp Road"
            persons:city="Centerville"
            person:country="USA"/>
        </rdf:li>
    </rdf:Seq>
    </rdf:Description>
</rdf:rdf>

As you can see, the table (or more likely - the database name)
becomes a namespace for a collection of XML tags that represent
each of the columns in the table (or tables).

To me, the biggest appeal here is in being able to take advantage
of an SQL database engine to do what it does best: sorting and
selecting while deferring any last minute formatting to the XUL
templates. As an example of why this mixed approach will work better
- try sorting with a XUL template on a numeric field that isn't padded
with leading zeroes (though this will eventually be fixed no doubt).

2. Database-aware Dialog Widgets

The idea here is simple and doesn't actually require
creating any new XUL widgets. I'd like to create an XPCOM
component that would connect to a database, issue a query,
and retain the result set. A method on this component would
accept a pointer to a XUL window. The component would walk
the DOM sub-tree of this XUL window looking for widgets whose
IDs match the column names of the current result set. Those
that matched (textboxes for example) would have their values
(or labels) set to the contents of the matching field in the
current row of the result set.

The component could also serve as a broadcaster so if the
current record changed - the XUL widgets observing the
component would be automatically updated.

3. Outliner View Database Component

The outliner widget is a very effective and relatively
efficient mechanism for displaying "computed" data. It also
lends itself very handily to displaying tabular data straight
from a database. All that is needed by the outliner widget is
to provide an implementation of the nsIOutlinerView interface.

I've done just that for the mySQL database engine. It's a
tiny DLL (53 kilobytes for the DEBUG version). I'm also
planning an ODBC version. I'll post the code (consider it
LGPL'd) for the mySQL version as an attachment.

Here's what a sample outliner XUL file looks like:

<?xml version="1.0"?>
<?xml-stylesheet href="chrome://navigator/skin/navigator.css"
  type="text/css"?>
<!DOCTYPE window>

<window xmlns:html="http://www.w3.org/1999/xhtml"
  xmlns="http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul"
    align="vertical"
    onload="prep();"
    onclose="mySQL.close();">

<script language="JavaScript">
var mySQL = {};

function prep()
{
  mySQL = Components.classes["@mozilla.org/mySQL/Outliner;1"].createInstance();
  mySQL = mySQL.QueryInterface(Components.interfaces.mozIDataBase);
  if ( mySQL.open("test", "", "") )
  {
    alert("Set BP now!");
    var outliner = document.getElementById('ex-outliner');
    outliner.outlinerBoxObject.view = mySQL.getView("SELECT * FROM sample");
  }
}
</script>

<outliner id="ex-outliner" flex="1">
 <outlinercol id="id" label="ID" flex="1" crop="left"/>
 <outlinercol id="name" label="Name" flex="2" crop="left"/>
 <outlinercol id="address" label="Address" flex="2" crop="left"/>
 <outlinercol id="city" label="City" flex="2" crop="left"/>
 <outlinercol id="state" label="State" flex="1" crop="left"/>
 <outlinercol id="postal" label="Postal" flex="1" crop="left"/>

 <outlinerbody flex="1"/>
</outliner>

</window>

My ultimate goal is to produce a component that will serve
all three - provide RDF datasourcing for XUL templates,
handle populating a XUL dialog, and act as a view for an
outliner widget.

The penalty for using such a component shouldn't be more
that a dozen lines of JavaScript in your XUL chrome files.

Regards,
Rick Parrish
rfmobile@swbell.net
(Reporter)

Comment 1

16 years ago
Created attachment 43955 [details]
myOutliner C++ source code
(Reporter)

Comment 2

16 years ago
Created attachment 43956 [details]
chrome XUL file installer to view/test outliner view component
(Reporter)

Comment 3

16 years ago
To make this work, I had to do the following:

1. have mySQL installed and running on my system
2. create a "sample" table in the test database on mySQL like so:

mySQL> use test;
mySQL> create table sample
mySQL>   (id integer not null,
mySQL>   name varchar(24),
mySQL>   address varchar(24),
mySQL>   city varchar(16),
mySQL>   state varchar(16),
mySQL>   postal varchar(10),
mySQL>   primary key(id) );
mySQL> insert into sample values(1, "Joe Cool",
mySQL>   "101 Main Street", "Houston", "Texas", "77002");
mySQL> insert into sample values(2, "Ben Stein",
mySQL>   "101 Lincoln Lane", "Houston", "Texas", "77025");
mySQL> insert into sample values(3, "Ali Gator",
mySQL>   "101 Swamp Road", "Houston", "Texas", "77005");

3. install the above XUL file as a chrome package in mozilla.
4. copy the XPCOM component files (DLL and XPT) to the
   dist/bin/components folder and register them.
5. run mozilla from a shell prompt using the chrome switch:
   > mozilla -chrome chrome://dboutliner/content

A few caveats are in order: all contract IDs, interface IDs,
and interfaces are certain to change. Example: the badly
named "mozIDataBase" interface uses |char*| instead of
|PRUnichar*| for passing strings - I'll fix that at some
point. The makefile.in file is dubious at best as I haven't
even tried this on Linux yet.

myOutliner.zip is the C++ source for the mySQL outliner view component.

dbOutliner.xpi is the chrome installer ZIP package. If it fails to
install, you may have to resort to just copying the dbOutliner.jar
file contained within to the /bin/chrome folder and hand editing the
installed-chrome.txt and all-packages.rdf files.

To test this on windows, you will need to add the mySQL library folder
to your default path.

ie. set path=C:\mySQL\lib\debug;%PATH%

You can unzip the myOutliner ZIP file to the mozilla/xpcom folder. It
should create a "myOutliner" folder and place the files there.

Regards,
Rick Parrish
rfmobile@swbell.net
Severity: normal → enhancement
Status: UNCONFIRMED → NEW
Ever confirmed: true

Comment 4

16 years ago
I've done someting similar in bug 81653.
It would be useful to join our efforts.

Comment 5

16 years ago
I like part 2 (Database-aware XUL Dialog widgets)
Great idea !


Target Milestone: --- → Future
this doesn't really seem to involve toolkit. perhaps vidur's team might be 
interested in investigating this. not sure what component this should be, picking 
XPCOM for now.
Assignee: trudelle → vidur
Component: XP Toolkit/Widgets: XUL → XPCOM
Target Milestone: Future → ---
(Reporter)

Comment 7

16 years ago
Created attachment 44197 [details]
improved: added XUL dialog support (part 2)
(Reporter)

Comment 8

16 years ago
Created attachment 44198 [details]
improved: added XUL dialog support (part 2)
(Reporter)

Comment 9

16 years ago
I'm talking to Jan Varga about merging our efforts.

Folks tracking this bug should take a look at 
http://bugzilla.mozilla.org/show_bug.cgi?id=81653.

Most important, he's posted source code for an 
implementation of an SQL row set as an RDF 
datasource (doh! beat me to it!).
(Reporter)

Comment 10

16 years ago
Created attachment 46046 [details]
New mozDb *experimental* interfaces with code (no XUL or RDF code yet).
(Reporter)

Comment 11

16 years ago
Currently working by email with Jan Varga to finalize the database specific
interfaces. Current planned implementations are: mySQL, PostgreSQL, ODBC, and an
In-Memory rowset.

Basic idea is the RowSet interface acts as the boundary between the
database and subsequent components that provide mappings from a rowset
to XUL, RDF, and/or AutoComplete.

Once a concensus is established here we'll move on to XUL/RDF mappings.
(Reporter)

Comment 12

16 years ago
FYI: Jan and I are still hashing things out. This is slow going for the moment
since both of us have real jobs. I think we are very close to putting out
mySQL, PostgresSQL, ODBC (win32 and FreeODBC for linux), and in-memory
implementations.

Comment 13

16 years ago

*** This bug has been marked as a duplicate of 81653 ***
Status: NEW → RESOLVED
Last Resolved: 16 years ago
Resolution: --- → DUPLICATE
You need to log in before you can comment on or make changes to this bug.