81653 - (sql) Add native database support

Status: VERIFIED FIXED

(Core Graveyard :: SQL, enhancement, P3)

Description

[Jan Varga [:janv]]

Comment 1

[Jan Varga [:janv]]

Wee, we should create new component for this.

Comment 2

[Jan Varga [:janv]]

Attachment: packed extensions/database

Comment 3

[Jan Varga [:janv]]

Attachment: build script for linux

Comment 4

[timeless]

Do you have a suggested assignee, owner and description?

Comment 5

[Jan Varga [:janv]]

Database support has been done as part of my thesis.
I've decided to public this extension, so I can be default assignee and owner.

Very short description:
Database extension now supports PostgreSQL and MySQL databases.
Results from database query can be used directly in JS or datasource,
or it can be displayed with outliner or autocomplete widget.

Comment 6

[Jan Varga [:janv]]

Attachment: connections between interfaces

Comment 7

[Jan Varga [:janv]]

*** Bug 105643 has been marked as a duplicate of this bug. ***

Comment 8

[Jan Varga [:janv]]

*** Bug 92757 has been marked as a duplicate of this bug. ***

Comment 9

[Jan Varga [:janv]]

Attachment: proposed interfaces v1.0

Comment 10

[Jan Varga [:janv]]

Attachment: new proposed interfaces

Comment 11

[Don Smith]

How exactly does this work? Will it work crossplatform?

Don

Comment 12

[Rick Parrish]

Yes, it should work out to be cross-platform and - to some extent - cross DB
engine as well. The idea is to create a set of components for each platform.
Craft an application out of XUL and JavaScript. If the client already has the DB
components, then they can run your application.

The application distributables (not including the DB components) tend to be very
small since they are just text.

Think of this as a cross-platform AND cross-db engine alternative to Foxpro,
dBase, Paradox and a number of other database-centric application frameworks.

-rick (wishing he had time to get back to this!)

Comment 13

[Jan Varga [:janv]]

Rick, I still work on this. I redesigned interfaces a bit
To answer previous question...
Yes, this will be XP, it supports PostgreSQL so far
I've managed to compile PostgreSQL's libpq on Linux, Windows and even on Mac OS X

Comment 14

[Jan Varga [:janv]]

Attachment: proposed interfaces v2.0

Comment 15

[Frank Schönheit]

Just dreaming:

Imaginating a system where both OpenOffice.org and Mozilla are installed, it
would be cool if both could interoperate in the database access area.
OpenOffice.org features a comprehensive database access API, as well as
implementations of this API for various database types (JDBC, ODBC, dBase, flat
files, ADO (on windows), Adabas D, Mozilla/LDAP/Outlook address books).
Admittedly no native implementations for MySQL or PostgreSQL (yet).

It would be really great if Mozilla could (upon request and alternatively)
transparently use OpenOffice.org implementations. Means that a Mozilla
application accesses (and works with) the XPCOM interfaces, which wrap to the
OpenOffice.org installation.
This would surely be a benefit for Mozilla, as it would open access to more
databases without re-implementing.

I know that this may be quite a lot of work, but as mentioned: just dreaming :)

Comment 16

[Jan Varga [:janv]]

Hmm, sounds good, but this idea could cause a couple of problems.
- Mozilla itself is quite big, now we would require OpenOffice additionally
- It adds an additional layer and that means degraded performance

But as you said, it can be just optional.
We can add an additional openoffice driver.
This driver would forward to openoffice API.

Now think of a mozdb driver which forwards to openoffice driver which forwards
to ODBC which forwards to PostgreSQL :)
Nice :)

Comment 17

[Frank Schönheit]

well, it certainly should be only one implementation of the respective XPCOM
API, with other implementations accessing databases in whatever other ways.

Not yet sure from the IDLs how the differentiation between for instance a
PostgreSQL and a MySQL connection is made - is it coded in the URI (like it is
in JDBC)? If so, what is the aType parameter in the alias-handling methods of
the nsIDbService for?

In any way, I suppose such a differentiation is necessary, and then
"OpenOffice.org bridge" would just be another type which can or cannot be used.

> - It adds an additional layer and that means degraded performance

for databases not supported directly by Mozilla a degraded performance would be
better than no performance at all :)

> Now think of a mozdb driver which forwards to openoffice driver which forwards
> to ODBC which forwards to PostgreSQL :)

hmm. We could add an additional JDBC->ODBC bridge inbetween. We could even write
a generic ODBC driver which forwards to an Mozilla driver, and the use Mozilla's
native implementation for accessing PostgreSQL. :)

Comment 18

[Jan Varga [:janv]]

>Not yet sure from the IDLs how the differentiation between for instance a
>PostgreSQL and a MySQL connection is made - is it coded in the URI (like it is
>in JDBC)? If so, what is the aType parameter in the alias-handling methods of
>the nsIDbService for

mozIDbService is a data source which stores informations about predefined aliases
(something like DSNs on windows). Informations like type, hostname, port,
database name
Type is type of driver (e.g. pgsql, mysql)
Here is a contract ID used to create a PostgreSQL connection:
@mozilla.org/mozdb/connection;1?type=pgsql
This mechanism provides a simple way to  make connections from JS

e.g.
function init() {
  var dbService = Components.classes["@mozilla.org/mozdb/service;1"]
                                .getService(Components.interfaces.mozIDbService);
  try {
    var dbConnection = dbService.getConnection("urn:aliases:mydb");
  }
  catch (ex) {
    alert(dbService.errorMessage);
    window.close();
  }
}

getConnection() will take care of creating the contract ID and prompting for a
login and
password. It will also cache connections. If you don't want cached connections
use getNewConnection().
There is also an UI to manage aliases in the preferences dialog. I'll attach a
screenshot.

// execute a query:
var result = dbConnection.executeQuery(query);

// dump column names;
for (var i = 0; i < result.columnCount; i++)
  dump(result.getColumnName(i));

// get result enumerator
var e = result.enumerate();

// dump values
while (e.next()) {
  for (var i = 0; i < result.columnCount; i++)
    dump(e.getVariant(i));
}

// query this result to RDF data source
var ds = result.QueryInterface(Components.interfaces.nsIRDFDataSource);

// attach it to my tree widget
// values are stored as variants so sorting works right
var tree = document.getElementById("mytree');
tree.database.AddDataSource(ds);
tree.builder.rebuild();

// results are even updateable:
for (var i = 0; i < result.columnCount; i++) {
  var element = document.getElementById(result.getColumnName(i));
  e.setVariant(i, element.value);
}
try {
  e.updateRow()
} catch (ex) {
  alert(e.errorMessage);
}

updateRow() will of course synchronize values with DB
but only of you didn't make a SQL join (only select from one table is supported)

All these methods are implemented except asyncExecuteQuery() and
asyncExecuteUpdate()
In near future I'm going to port asynchronous execution of queries from previous
version.

I know that these interfaces definetely need comments.
I'll try to add them.

Well, it would be really cool to check this code in to share it with other
people who might want to take a look or even contribute.
What do you think ?

Comment 19

[Jan Varga [:janv]]

Attachment: aliases UI

Comment 20

[Frank Schönheit]

still not sure about mozIDbService. I understand it's concept (in fact, this is
what OOo calls a DatabaseContext - an instance to register data sources for an
alias), but not yet the method signatures.

Your example uses "urn:aliases:mydb" as aURI parameter, thus I assume that data
source has been registered with something like
  dbService.addAlias( "urn:aliases:mydb", .... )

Do I understand it right that aName then is the user name, and the other 4
parameters specify the concrete (remote) database to connect to?

I so, to be honest, I think the interface is way too specific. If somebody
decides to implement a "driver" for ODBC or any other concrete database, which
needs more or less settings than "username, hostname, port, database", then the
interface will not suffice. For instance, if somebody would have a JDBC driver,
the mozIDbService will not suffice because there's no chance to pass the driver
class. For other types, other additional settings may be necessary.
In this sense, sorry to say, mozIDbService is not future-proof.

Depending on how far your intentions go, this may or may not be a problem :).


some other comments on the mozIDbService:

I would allow for the caller of getConnection to pass a password (preventing the
authentication dialog this way). Perhaps it's even good to separate
"getConnectionWithAuthentication" from "getConnection" (not sure about the
names, but you get the idea).

An override of the user may also make sense. Even if "urn:aliases:mydb" is
registered for user "xyz", a caller may decide to open a connection to the
database specified by this alias, but for a different user - without
re-registering the data source under a different alias.

> I know that these interfaces definetely need comments.

ehm - yes :)

> Well, it would be really cool to check this code in to share it with other
> people who might want to take a look or even contribute.

Don't know the processes for this in Mozilla. Honestly, I would think that
looking at interfaces and discussing them should happen before checking them in,
but as said, don't really know how Mozilla handles this :)

Comment 21

[Jan Varga [:janv]]

>Your example uses "urn:aliases:mydb" as aURI parameter, thus I assume that data
>source has been registered with something like
>  dbService.addAlias( "urn:aliases:mydb", .... )

No, urn:aliases:mydb is the RDF resource
You can Assert any additional properties to this resource.
Thanks for feedback.

The data source is registered as rdf:mozdb and stored in user's profile as
XML/RDF (mozdb.rdf)
Note that addAlias() and others are just helper methods. These methods do all
the RDF magic.
If you need additional properties for your driver, nothing prevents you from
asserting them
to the RDF data source.

I like idea of having getConnectionWithAuthentication() - like JDBC does

>Do I understand it right that aName then is the user name, and the other 4
>parameters specify the concrete (remote) database to connect to?
aName is actually alias name not user name, I should change it

Comment 22

[Frank Schönheit]

> No, urn:aliases:mydb is the RDF resource
> You can Assert any additional properties to this resource.
> ...
> If you need additional properties for your driver, nothing prevents you from
> asserting them to the RDF data source.

Ah! I am obviously missing a lot of knowledge about the RDF system.

> aName is actually alias name not user name, I should change it

Hmm. Then where is this alias name used? From the IDLs. it seems it's only used
for the 4 *Alias methods, but not even for getting a connection. Is there (or do
you plan) an easy way like "getConnection( aliasName )" somewhere?

Comment 23

[Jan Varga [:janv]]

name or alias is just used in aliases UI

Comment 24

[Jan Varga [:janv]]

cc'ing Peter

Comment 25

[Jan Varga [:janv]]

Peter asked me to write a quick note about status of this interface.
So, at this stage almost all methods are implemented. I added async support
recently.
All I need is to add comments to the interface files and code.
It supports PostgreSQL at the moment, but adding support for other SQL databases
shouldn't be difficult.

What it can do at the moment ?
1. define aliases for database connection via mozdb service (like DNS on windows)
2. A connection can be shared in multiple windows
2. synchronous and asynchronous execution of queries and updates (queries are
queued and executed sequentally)
3. result sets are editable, there is no need to write own SQL updates, it's
done automatically
4. result sets implement nsIRDFDataSource interface, so they can be attached to
the tree widget or any other widget like menulist
5. cells in a result set are type based, so sorting works correctly (e.g. ints,
bools, floats, strings)
6. there are 2 abstract objects connection and result which implement all the
generic stuff.
Database specific objects can inherit from these and only implement database
specific methods
like initializing of connection, getting of primary keys, execution of a query,
cancelling of execution and building of column info and rows from native result set.

I successfully built this code on all major platforms (Linux, Windows, Mac OS X)
I used standard Mozilla's build system.

So I think XUL developers can definetily benefit from this component. If we say
that Mozilla is not just a browser but also a platfrom, let's make it a platform
for developing of database applications too.

Here are items which are not yet done:
- add comments to the interfaces and code
- add support for all known databases (MySQL, Oracle, Informix, Sybase, etc.)
- add generic ODBC support or even support for openoffice drivers

Comment 26

[Jan Varga [:janv]]

I forgot to mention that currently there's no plan to support other non-SQL
database systems.

Comment 27

[Jan Varga [:janv]]

I meant DSNs not DNS :)

Comment 28

[Myk Melez [:myk] [@mykmelez]]

This is a great feature that web application developers are going to love.  I
suggest not coupling this to another large application like OpenOffice since it
isn't necessary and would save us a lot of trouble (not to mention OpenOffice
doesn't have a MacOSX version available).

This should be implementable with a very small amount of code (with additional
support provided by similarly small database drivers), which is what it looks
like Jan is doing.

Comment 29

[Jan Varga [:janv]]

Attachment: xpi package for linux

Comment 30

[Jan Varga [:janv]]

Attachment: xpi package for windows

Comment 31

[Jan Varga [:janv]]

Attachment: libpq.dll for windows

Comment 32

[Jan Varga [:janv]]

Attachment: proposed interfaces v2.1

Comment 33

[Peter Trudelle]

nominating for Buffy

Comment 34

[Brian King [:kinger]]

Jan, I think you need to change the MIME type for the recently attached XPIs. I
am not sure what it is for XPI ...

And a guide on the file cotained within and how to use would be nice too.

Comment 35

[Jan Varga [:janv]]

Ok, thanks for the hint
I changed mime types to application/x-xpinstall (thanks timeless for info)

and I'm going to attach a testcase, in the meantime you can find example code in
comment 18

Aliases can be edited in Preferences->Database support
only "pgsql" type is supported at the moment.

Comment 36

[Jan Varga [:janv]]

Attachment: test.zip

Comment 37

[Jan Varga [:janv]]

Attachment: mozdb.rdf

copy this file to your profile if you have problems with adding test alias

Comment 38

[Johnny Stenback (:jst)]

I just want to make sure that people are aware of the possible security issues
involved in enabling this in default builds. How much thought has gone into the
security model behind this? IMO a separate, and thorough, security review of
this new functionality needs to happen before we ship this to anyone.

Comment 39

[Jan Varga [:janv]]

just a note, you have to always call asyncExecuteQuery() first, otherwise you
will crash.
it's fixed in my tree.
Sorry for SPAM

jst, possible security issues were already mentioned in bugscape bug 17026
I agree, we need such review, I didn't intend to enable this in default builds
w/o design and security reviews.

Comment 40

[Jan Varga [:janv]]

Another note,
you need the latest beta version of PostgreSQL (7.3b2) to work properly

Comment 41

[Jeff Patterson]

Hi Jan,
I've successfully installed the xpi package (for linux) that you provided but am
unable to connect to my postgresql postmaster. When I attempt to add an alias
nothing happens. The alias info window stays up and no alias is added to the db
support window list.

Checking the javascript console I see the following error

    Error: Components.classes['@mozilla.org/mozdb/service;1'] has no
    properties
    Source File: chrome://mozdb/content/aliasDialog.js
    Line: 10

I am running mozilla 1.1 [Mozilla/5.0 (X11; U; Linux i686; en-US;
rv:1.1) Gecko/20020826]

I tried placing the mozdb.rdf file you provided in my
$HOME/.mozilla/jpat/34ugjxxd.slt/ directory (is this what you mean by
'adding it to your profile?) but it didn't help.

Any suggestions? Thanks for your efforts here. Naitive dB support will
go along way toward making mozilla a true application development
platform.

Jeff Patterson

Comment 42

[Jan Varga [:janv]]

First, thanks for your interest.
To your problem...
It's probably a problem with file permisions, please check these files:
components/libmozdb.so should have 755
components/mozdb.xpt 644
components/mozdbpgsql.xpt 644
chrome/mozdb.jar 644

Comment 43

[Jeff Patterson]

Thanks Jan,
The file permissions fixed the original error but now I get:

Error: uncaught exception: [Exception... "Component returned failure code:
0x80570016 (NS_ERROR_XPC_GS_RETURNED_FAILURE) [nsIJSCID.getService]"  nsresult:
"0x80570016 (NS_ERROR_XPC_GS_RETURNED_FAILURE)"  location: "JS frame ::
chrome://mozdb/content/aliasDialog.js :: init :: line 11"  data: no]
as soon as I hit the 'add alias' button. The offending line is 11 in the init
function below

function init() {
 -  10   dbService = Components.classes["@mozilla.org/mozdb/service;1"]
 -  11               .getService(Components.interfaces.mozIDbService);
  
Jeff

Comment 44

[Jan Varga [:janv]]

Jeff, if you still haven't figured out your problem contact me directly by email.

Comment 45

[Samir Gehani]

Nav triage team: nsbeta1-

Comment 46

[Myk Melez [:myk] [@mykmelez]]

Are there updated XPIs that deal with the mozdb.rdf and file permissions problems?

Comment 47

[Jan Varga [:janv]]

I'll post updated xpis later this week, I'm very busy at the moment.
Stay tuned.

Comment 48

[Jan Varga [:janv]]

Attachment: mozdb-20021214-unix.xpi

PI package for Linux, compiled on RedHat 6.2 for Mozilla 1.2.1

Comment 49

[Jan Varga [:janv]]

note that this package only works with PostgreSQL 7.3
will attach a new package with support for previous version

Comment 50

[Jeff Patterson]

Attachment: manifest file for test.xul

to test database support:
1) install the mozdb xpi 
2) download and unzip test.zip from attachments above
3) download contents.rdf and install it in the same directory used in (2)
4) add the following line to installed-chrome.txt (in the mozilla chrome
directory)
content,install,path,<full path to directory with contents.rdf file/> 
5) make sure the line above ends with a '/'
6) create a database in postgresql called test.
7) create a relation in test called states:
create table states (
  code	varchar(2) primary key,
  name	varchar(30)
);
8)populate states with state code/state name pairs. You can import the file
states.txt in the test directory for this purpose using psql or pgaccess
9)re-start mozilla and point to the following url.
chrome://test/content/test.xul

You should get a user/password dialog after which the test widgets load. Under
the tabs 'Sync test' and 'Async test' test that you can query state names by
entering the state code. Under the tab 'Widgets' you should see the enter
relation. Right clicking on an entry allow you to edit the row contents.

Very cool Jan. Great job!

Comment 51

[Jan Varga [:janv]]

Attachment: mozdb-20021214-unix.xpi

PI package for Linux, compiled on RedHat 6.2 for Mozilla 1.2.1
Now it supports PostgreSQL < 7.3 (tested with 7.2.1)
This package also contains the testcase
chrome://mozdb/content/test/test.xul

Comment 52

[Jan Varga [:janv]]

Attachment: mozdb-20021214-win.xpi

Comment 53

[imran.geriskovan]

Why don't you get a mozdev/sourceforge/savannah for this work?
It is a very cool thing. You will probably get
a lot of contributers and users once you begin
to work at a more "accessible" location.

Comment 54

[Jan Varga [:janv]]

Attachment: mozdb-20021222-mac.xpi

Comment 55

[Justin]

Guess I have to ask since I can not find answers anywhere; Where is the source?
And how can someone contribute to this??

Comment 56

[Loren Lang]

I am having problems with mozdb with certains versions of mozilla that are
identical to the problems described in Additional Comment #41 and #43.
When I try to add new aliases in the database preferences the OK button does
nothing.  I see two errors in the javascript console:

Error: uncaught exception: [Exception... "Component returned failure code:
0x80570016 (NS_ERROR_XPC_GS_RETURNED_FAILURE) [nsIJSCID.getService]"  nsresult:
"0x80570016 (NS_ERROR_XPC_GS_RETURNED_FAILURE)"  location: "JS frame ::
chrome://mozdb/content/aliasDialog.js :: init :: line 11"  data: no]

Error: dbService has no properties
Source File: chrome://mozdb/content/aliasDialog.js
Line: 36

before I click on add alias, there were no errors reported.  This is
specifically effecting mozilla 1.0 on win and unix, also netscape 7 on win,
(I have not check it on unix.)  Mozilla 1.2 works fine though.  I checked
the file permissions mentioned in AC #42 and they were set correctly.  I
don't care so much that mozilla 1.0 doesn't work except that netscape 7 is
based on 1.0 and therefore is also failing, though, if need be, I can install
mozilla 1.2 on all the computers at my company, but since some already have
netscape 7.0 I thought that might be a little nicer.

Any insight would be appreciated and if I could also be notified at
[e-mail], too.

Comment 57

[Jan Varga [:janv]]

The latest XPIs are supposed to run on Mozilla 1.2.
Anyway, I have 3 branches of mozdb right now, HEAD, MOZDB_1_2 and MOZDB_1_0
So it's possible to build for Mozilla 1.0, 1.2 and trunk version.
I got some emails requesting source code, well, I'm considering to setup a new
mozdev.org project or ask drivers to use mozilla CVS tree (e.g.
mozilla/extensions/mozdb)
Happy new year to all and thanks for your interest.

Comment 58

[Justin]

It seems that this is quite the popular thing to do with xpcom/xul. A project
was just released on mozdev.org[1], but for mysql only. And I have implemented
similar xpcom interfaces designed to run with mysql,postgre, mssql, etc...[2] 
And I know I have seen interest on the news groups and some other projects.

Interest seems to be high enough to justify standardizing on some generic
database interface and incorporating that into the tree.


1) http://mysqlxpcom.mozdev.org/
2) http://www.openaether.org/src/source/oajabdb/idl/

Comment 59

[Nigel McFarlane]

I couldn't agree with Justin more on fragmented design.

Look at the delay in clean and generic support for DB's in Perl.
That should be avoided or Mozilla will have the same years of delay.
And Perl's DBI/DBD support is still stupidly primitive, alas.
They were implemented by well-meaning folk who took too long to
get organised and never produced a performance-oriented solution.

There are heaps of generic Db connectivity technologies out there,
and most of them are layered protocols. It's the only way. I wa
 there when 4GL's got off the ground and simplistic client-server
protocols are worse than bad coffee. They're enough to connect,
but not enough for any real data management application. Not enough
for any serious use, especially one constrained by the need to
respond to a user quickly.
 
If the ball is rolling on Mozilla support for RDBMS's then a
set of layers should be the first design goal. A typical
set goes like this (bottom first):

1. Generic reliable connection-oriented protocol (eg TCP/IP)

2. Generic message passing protocol to support synchronous
   operations (like basic SQL), with room to support asynchronous
   queries, out-of-band breaks and all the other junk that SQL
   systems throw at you. No SQL semantics in this layer.

3. Raw driver-like interface for ODBC, MySQL, whatever, built on
   the bare metal of the database's client API. No embedded SQL.

4a. Common high-level, but still flat API that supports query set caching,
   cursors, bulk operations, piggybacked requests and other
   technologies that a real db client needs.

   If you can't do a select and have the db interface cache rows
   on the client side in a way that's tunable, then what you've got is 
   an endless stream of fetch operations back and forth, and no
   backwards navigation through the result set. The client-side
   cursor-enabled result sets cache is as important to DB clients as
   Mozilla's cache is to a web browser.

   If the RDF fact store could be enhanced with cursors, and it
   proved efficient, then it could implement this result sets cache.

4b. JavaBean or PowerObject style connection and query objects,
   the final frozen interfaces.

When I examined the available source code offerings, none of this
structure was in place, much less a portable and generic solution.
These simple interfaces will en-trance the early adopters but
deeply frustrate serious users. All these implementations are excellent
input to a better coordinated design, though.

Comment 60

[Rick Parrish]

Justin - this project has been around for 20+ months. The forces behind the two
other projects you mentioned either have their own agendas (patent pending?!),
wanted to do something for fun and experience, or were unaware of the ongoing
efforts here. Have you looked at the IDL interfaces included here? Please take a
look and tells us what you think.

Nigel - nice plan - looks ambitious. At this point in time, I'd be happy with
being able to talk to the native DB drivers (which already handle transport
issues) from inside mozilla - including standalone DBs running on the local
machine or even in the same process as mozilla. The code that Jan is working on
will support that. At one point mySQL, PostgreSQL, and ODBC (on win32) were
supported. That code can be dusted off and merged back in to the tree. The
biggest limiting factor is a lack of contributors. Personally, I am in favor of
a more modest, more easily attainable goal.

OTOH, some of the features you mention would be nice to have. Could you draft a
rough design and maybe include some sketchy IDL interfaces to kick off a
discussion/peer review of ideas?

In the meantime, I'll support the project in direction it is currently headed.

Jan - one BIG request; to better facilitate discussions like this we need our
own newsgroup or email list. Could you bug someone at netscape.com to set this up?

Regards,
Rick Parrish

Comment 61

[Nigel McFarlane]

Re: request for some design roughs: can do, but in very serious
deadline city at the moment.

If someone high-profile could send me a brief email saying:

"Thank you for your proposed contribution to Mozilla,
this is a noble cause and a technical opportunity that could
significantly impact Mozilla's use in the commercial
environment. We're enthusiastic about progressing
such a contribution if we can."

then I can wave it at someone(s) near me in an attempt
to bring this work forward.

Pleae spare a moment for such a message if you can. TIA.

Comment 62

[Loren Lang]

Is the source available?

I've seen several posts asking for the sources,
but no direct reply, so is the source publicly
available somewhere or do we just have to get
you to send a a link or something like.

Thanks

Comment 63

[Jan Varga [:janv]]

Attachment: source code

compiles with the trunk

Comment 64

[Jan Varga [:janv]]

I agree with Rick, Nigel's plan looks nice, but I'm not sure if we have
resources to do it at the moment and I'm not sure if we need all the layers.

I took look at the other projects...

1. mysqlxpcom
Looks very simple, just a wrapper around the MySQL client lib.
We can join forces here, e.g. maintain mysql part of the mozdb.

2. oajabdb
Very similar to mozdb, although it could be more integrated with mozilla.
e.g. store connection info in mozilla's profile.

I'm also missing a way to display SQL results in XUL widgets, except generating
content in JS.

So once I get an approval to check in an initial version of mozdb, we can start 
to co-operate.

Comment 65

[Jan Varga [:janv]]

to compile source code you need:
configure --enable-extensions=default,mozdb

set also these variables
export MOZ_ENABLE_PGSQL=1
export MOZ_PGSQL_INCLUDES=/usr/local/pgsql/include
export MOZ_PGSQL_LIBS=/usr/local/pgsql/lib

Comment 66

[Robert Kaiser]

Jan:
is your Comment #65 what we'll need in the future, or is it already checked in
and available when building with that options?

Comment 67

[Jan Varga [:janv]]

Nothing has been checked in yet.
Just unzip the source package in extensions directory
These sources already use mozilla's build system, so just rerun configure with
mozdb added to the --enable-extensions option.

Don't forget to set the variables before building to include pgsql support
If you also want to include the test UI set MOZDB_ENABLE_TESTS=1.

Hmm it seems I forgot to attach a patch for allmakefiles.sh
I'll attach it soon.

Comment 68

[Jan Varga [:janv]]

Attachment: allmakefiles.diff

Comment 69

[Jan Varga [:janv]]

I landed the latest version on the trunk!
Thanks to Brendan for approval.
Please be aware of one change ...
I've renamed it from 'mozdb' to 'sql', due to possible interference with
existing db module.

Basically I've done:
s/DB/SQL
s/Db/Sql
s/db/sql

I've updated also the testcase (chrome://sql/content/test/test.xul)
Just use 'sqlalias' instead of 'dbalias'

Comment 70

[David Baron :dbaron: (⌚️UTC-4, no longer working on Mozilla)]

You may want to add sql to the list of all extensions in configure.in
(MOZ_EXTENSIONS_ALL) so that it will be built with --enable-extensions=all and
you'll be protected from bustage by being built on many of the tinderboxes. 
Note, however, that you should make sure you expect to build on all of those
tinderboxes first. :-)

Comment 71

[Jan Varga [:janv]]

Yeah that's a good idea, but I should probably wait until we drop support for
Mac Classic, since I didn't add XML files for Code Warrior.

Comment 72

[Christian :Biesinger (don't email me, ping me on IRC)]

is there any documentation how to use this sql stuff?

Comment 73

[Jan Varga [:janv]]

There's no documentation at the moment, but there is a demonstrative testcase in
tests directory.

Comment 74

[ArronM (:paper)]

I'm not understanding why we are rolling our own database access components
instead of just wrapping existing standards, like CLI ("ISO/IEC 9075-3:1995
Call-Level Interface" or the "X/Open CAE Specification")

From the sounds of it, with CLI we wouldn't have to write code for each database
type.  Plus developers who know how to code in CLI (ie. and Microsoft ODBC
Coder) would be at home and after learning XUL, could easily write an Database
App using Mozilla.

Is there a reason for re-inventing the wheel?

Perhaps I'm just repeating what's said in Comment #59, but I get the feeling the
issue wasn't seriously looked into.  Rolling our own components is easy from the
start, but eventually it will need more and more functionality, and we'll need
to support more and more database types (ie. a maintainer for each database
type).  After a while, our implementation will probably look similar to the
other generic database implementaters out there, except ours won't be a
standard.. it'll be different and just another thing for a developer to re-learn.

Comment 75

[Jan Varga [:janv]]

Hmm, is CLI cross platform ? is it just standard or implementation ?

We don't have to 'reimplement' code for each database, we use libraries provided
by database systems (e.g. libpq)

Comment 76

[Jan Varga [:janv]]

Nice, they want to pay CHF 312,00 for this standard.

Comment 77

[ArronM (:paper)]

There are plenty of books and documentation sites on ISO/IEC 9075:1999 (usually
called SQL99)

Postgres supports a lot of SQL99.  The list can be seen here:
http://developer.postgresql.org/docs/postgres/features.html

So I would assume it's XP.

A quick google search for documentation comes up with 
http://www.student.math.uwaterloo.ca/~cs448/db2_doc/html/db2l0/frame3.htm#
which is IBM's implementation of CLI, which includes implementation of SQL99
(they call it SQL3)

Comment 78

[Jan Varga [:janv]]

I understand that more experienced developers want powerful and standard based
implementation with at least 5 layers etc.
The problem is that we didn't have anything real, so I decided to check in what
we have. Current implementation is quite working and I know people which use it
to build their db application.
Everybody is talking about what we should do and how to do it, but nobody has
offered a help (except Brian, he will help me with documentation).
There are already 3 other projects at mozdev.org. We need something to start
with and then evolve, otherwise we will end up with 5 or more similar projects.
I wonder if somebody actually took look at the code.
If you haven't noticed I work on this in my free time.

Comment 79

[ArronM (:paper)]

Jan, looking at the code you did and seeing some things missing caused me to
wonder if there was an existing way out there.  Okay, I actually noticed a lot
of things missing, but don't take that as me knocking your code.. I realize it's
just a start.

And I totally agree about getting something started, otherwise nothing will
happen.  If it wasn't for you starting this up, I wouldn't be looking into this
or posting on this bug today! :)

I'll look into this a bit more and see if I have anything to contribute.

Comment 80

[Jan Varga [:janv]]

Well, I would adapt a standard which includes IDL files, like w3c standard for
DOM. This CLI interface looks very "C like", that is, no object hierarchy etc.

Main goal of this project is to call SQL queries from JS, so we can't use
CLI,ODBC or JDBC directly. We need IDL interfaces.

Comment 81

[Jan Varga [:janv]]

If you miss something or you know how to do it better, please comment in this bug.

I forgot to add that Mozilla is cool platform to build XP application, but it
uses sligtly different philosophy (XUL/JS for UI, C++ for backed). Plain ODBC
like implementation would be not very useful. We need something well integrated
with this platform. I mean integration with XUL/RDF, requests for async queries,
observers, services, etc.

I don't think it's a big deal to have own interfaces until we find something
standard based. XUL developers have to learn many new technologies anyway.

Comment 82

[Jan Varga [:janv]]

FYI, there is now a new bugzilla component "SQL: Native database support"
Marking as fixed.

Comment 83

[Andy Edmonds]

Niceness!  What's the status on mysql in addition to postgres?  

Comment 84

[Jan Varga [:janv]]

There is a bug filed targeted for 1.5alpha, but it may be done sooner.
Bug 196576.

Comment 85

[Owen Marshall (Not reading bugmail)]

Verified FIXED. All new issues need to be filed in new bugs.

Comment 86

[Jan Varga [:janv]]

FYI, there are new packages for mozilla 1.4 at http://www.mozilla.org/projects/sql/
Sorry for SPAM

Comment 87

[Owen Marshall (Not reading bugmail)]

*bugspam*

Comment 88

[Valia V. Vaneeva]

Attachment: patch for adding sqlite support

1. In order to build the sql extension with SQLite support you have to install
SQLite of version 3.0.2 or higher and Mozilla of version 1.8a3.
2. Then apply the patch attached.
3. Follow Jan's build instructions (http://www.mozilla.org/projects/sql/) but
don't forget to add
	export MOZ_ENABLE_SQLITE=1
	export MOZ_SQLITE_INCLUDES=<Your SQLite directory>/include (the file
sqlite3.h should be here)
	export MOZ_SQLITE_LIBS=<Your SQLite directory>/lib (the libsqlite3
library should be here)
4. You can test how it works using standard sqltest, of course, only if tests
are enabled. The alias definition in 'Preferences' should be as follows:
	Name: sqltest
	Type: sqlite
	Hostname: (doesn't matter)
	Port: (doesn't matter)
	Database: full path to the database file, e.g. /Users/fattie/states
	Priority: (doesn't matter)
5. Notes: I'm not smart enough yet to check the database file permissions but
I'm going to fix it this week. I don't also think my code will stand any
serious review, but, probably, it's not a bad start still.

Comment 89

[Jan Varga [:janv]]

Nice work, we should probably file a new bug for that.

Comment 90

[Valia V. Vaneeva]

(In reply to comment #89)
> Nice work, we should probably file a new bug for that.
Thanks!
But I think first I should rewrite it a bit.

Comment 91

[Valia V. Vaneeva]

A bug for SQLite3 support is opened:
http://bugzilla.mozilla.org/show_bug.cgi?id=254886

Comment 92

[sirsean]

There doesn't seem to be much activity here any more, but I can't find where it
has relocated to (if it has) so I'll just post here. The XPI install links
aren't working, and any others I've been finding are also non-functional. I
would very much like to be able to download and install this extension. Thank you.

Comment 93

[shintaro.okuda]

Any update to support Oracle ?

Comment 94

[Jan Varga [:janv]]

Sorry, I have no plans to add oracle support in near future.
I'm currently working with Neil Deakin on a sql component for enhanced xul
templates he is developing.

Comment 95

[Biju]

Whether this XPI works with firefox?

Comment 96

[gen2]

Jan, Valia...anyone home? Don't ask me why there isn't
a thundering herd demanding mysql (etc) connectivity from firefox.
This is an exceedingly important project.
Please don't die....snif...

There are those who thing sqlite "solves" the issue. It does not.
Vote this sucker up, peeples!