Architectural advice needed on client-side browser scripting

[Scott Bass]

Hi,

First of all, I'm not a web developer, although I am an IT professional,
primarily in the area of ETL programming against various databases.

I'd like to write an application (either Java or .Net) that will create a
"data dictionary" of all (selected) databases, tables, and columns in an
application. I want the output to be saved in dynamic html files viewed in
the browser (of course). The files are not served up from a web server, but
are instead a snapshot of the information at the time they are generated by
the application. They would form part of the overall project documentation.

I want to present the data similar to Javadoc, i.e. upper left frame (list
of databases), lower left frame (list of tables), and right hand frame (list
of columns). (Can the three frames contain multi-select listboxes instead
of static html?). On initial page display, only display the databases in
the upper left frame. Click one (or more) databases, and the list of tables
displays and/or refreshes in the lower left frame. Click one (or more)
tables, and the list of columns displays and/or refreshes in the right hand
frame.

Finally, I'd like to be able to click on an individual column name to filter
on that column name across all tables. This would help to track down column
inconsistencies across tables.

I know Javadoc is static html, and that html is stateless. However, I'm
hoping this is doable, perhaps by embedding the data as XML within the page,
then using Javascript to parse the embedded XML to render the page. I'm not
sure if cookies can be created to maintain state when no web server is
involved. Again, I'm a programmer, just not a web programmer.

Any advice on whether this is 1) even doable, and 2) if so, recommended
architecture, would be fantastic. As would any code snippets I could use as
an example, esp. embedded XML and code that reads that XML to render the
page.

Regards,
Scott

 

[Scott Bass]

Nearly everything is possible with JavaScript, but you may need
a web server, as JavaScript can read from an URL, but probably
not from files, at least not without deep changes to a lot of
security settings in the users' web browsers.

Is a local or intranet web server really a big problem? There
are very small and light web servers available, such as XAMPP
lite, and several others, which you just copy to a server's hard
disk and start, and they run without babysitting.

There may also be static solutions using lots of HTML files, and
I would recommend to investigate them as well. For example, if
you click on one database, another HTML file could be opened
that contains exactly the information and the list boxes for
that particular database. Whether such a solution is feasible or
would lead to an unmanageably high number of files, I cannot
tell, but if it is possible, it may not require JavaScript at
all, just a clever HTML generator.

Hans-Georg

Hi Hans-Georg,

Thanks for the reply. Much appreciated.

The reason I want static files is the documentation may be sent to customers
as part of the project documentation. I don't want them to have to load the
web pages onto a server or install any additional software.

Just to make it clear, I didn't intend for the data to be in an external
file. It would be in the main HTML file, either as a block of
non-displaying XML, or perhaps as an array(ies). The javascript would then
parse this XML or array(ies) to render the page. I then need to figure out
how to maintain state; normally if I choose an item(s) from a listbox, it's
a POST to the server, and the server renders the page. In my scenario,
clicking an item in a listbox serves to filter data in other listboxes.

Javadoc just uses static HTML and doesn't filter anything. Clicking a link
just navigates to an anchor in the other frame. If I have to revert to that
I will but it's not my first preference.

Again, if this is just impossible let me know that as well and I'll rethink
my approach.

Thanks
Scott

 

[VK]

I'd like to write an application (either Java or .Net) that will create a
"data dictionary" of all (selected) databases, tables, and columns in an
application.  I want the output to be saved in dynamic html files viewed in
the browser (of course).  The files are not served up from a web server, but
are instead a snapshot of the information at the time they are generated by
the application.  They would form part of the overall project documentation.

I want to present the data similar to Javadoc, i.e. upper left frame (list
of databases), lower left frame (list of tables), and right hand frame (list
of columns).  (Can the three frames contain multi-select listboxes instead
of static html?).  On initial page display, only display the databases in
the upper left frame.  Click one (or more) databases, and the list of tables
displays and/or refreshes in the lower left frame.  Click one (or more)
tables, and the list of columns displays and/or refreshes in the right hand
frame.

Finally, I'd like to be able to click on an individual column name to filter
on that column name across all tables.  This would help to track down column
inconsistencies across tables.

Yes of course it is perfectly possible. I can post a working sample if
you wish but 2 questions first:

1) Are you absolutely targeted to XML data format? DB::Table::Row
could be dumped out in JSON format which is much more scripting
effective.

2) Do you need to keep filter sets between sessions, so next time you
run the page say the last used filter is already applied?

Overall it should be one front-end app as HTML page with JavaScript
and one text/plain database either in XML or JSON format. - Unless we
are talking about hundreds db with thousands of rows, then loading one
file may slow the system down and per-db file structure.

 

[Scott Bass]

Hi VK (and Hans-Georg),

Thanks for your reply. Much appreciated.

Well, I'm glad to read that this is at least do-able. Once I decide on the
architecture I'll hit the docs.

I'm not married to using XML as the internal data store, it's just that it's
so ubiquitous that I thought it would be a good choice. For example, .Net
can easily serialize a DataTable (in this case the INFORMATION_SCHEMA views
in MS SQL Server) with a minimum of fuss. And I was hoping Javascript could
easily parse that XML to load listboxes or arrays. If JSON can be written
out by .Net or Java, I'm happy to use that.

I don't need to save state between launching these web pages in the browser.
But I would like the page to be "interactive" as previously discussed.

If I had an example of non-displaying XML or JSON, and a bit of code to
parse it into loading a listbox, that would be great - I could run with
this.

I may just hard code a sample page - once I deduce the format of that page
;-). A listbox with say two "databases" X 2 "tables" X 3 "columns" with the
desired filtering.

Reverse engineering my .Net (or Java) application to write out the actual
data should then be easy.

Thanks,
Scott

I'd like to write an application (either Java or .Net) that will create a
"data dictionary" of all (selected) databases, tables, and columns in an
application. I want the output to be saved in dynamic html files viewed in
the browser (of course). The files are not served up from a web server,
but
are instead a snapshot of the information at the time they are generated
by
the application. They would form part of the overall project
documentation.

I want to present the data similar to Javadoc, i.e. upper left frame (list
of databases), lower left frame (list of tables), and right hand frame
(list
of columns). (Can the three frames contain multi-select listboxes instead
of static html?). On initial page display, only display the databases in
the upper left frame. Click one (or more) databases, and the list of
tables
displays and/or refreshes in the lower left frame. Click one (or more)
tables, and the list of columns displays and/or refreshes in the right
hand
frame.

Finally, I'd like to be able to click on an individual column name to
filter
on that column name across all tables. This would help to track down
column
inconsistencies across tables.

Yes of course it is perfectly possible. I can post a working sample if
you wish but 2 questions first:

1) Are you absolutely targeted to XML data format? DB::Table::Row
could be dumped out in JSON format which is much more scripting
effective.

2) Do you need to keep filter sets between sessions, so next time you
run the page say the last used filter is already applied?

Overall it should be one front-end app as HTML page with JavaScript
and one text/plain database either in XML or JSON format. - Unless we
are talking about hundreds db with thousands of rows, then loading one
file may slow the system down and per-db file structure.

 

[VK]

I'm not married to using XML as the internal data store, it's just that it's
so ubiquitous that I thought it would be a good choice.  For example, .Net
can easily serialize a DataTable (in this case the INFORMATION_SCHEMA views
in MS SQL Server) with a minimum of fuss.  And I was hoping Javascript could
easily parse that XML to load listboxes or arrays.  If JSON can be written
out by .Net or Java, I'm happy to use that.

Yes, JSON interfaces are ported to nearly any programming platforms
including C# and Java, see http://json.org
At the same time if you already have a working XML data module and you
are comfortable with it then no problem.

I don't need to save state between launching these web pages in the browser.
OK

But I would like the page to be "interactive" as previously discussed.
If I had an example of non-displaying XML or JSON, and a bit of code to
parse it into loading a listbox, that would be great - I could run with
this.
I may just hard code a sample page - once I deduce the format of that page
;-).  A listbox with say two "databases" X 2 "tables" X 3 "columns" with the
desired filtering.

At this stage the actual layout and interface details are not so
relevant. It is more important to define

1) The target UA coverage. Besides the obvious (IE7,8 / FF, ...) do
you need to make it fully functional for IE6 users? Do you need to
make it fully functional for some mobile device auditory?

2) The exact data retrieving and manipulation block schema. Correct me
if I'm wrong and finish:

[10] HTML app open
[20] read entire data dictionary into memory
[30] parse for all databases
[40] list all databases as interactive interface items

[50] on a database chosen parse it for all tables in this database
[60] list all tables in the chosen database as interactive interface
items

[70] on a table chosen parse it for all rows in this table
[80] list all "rows" in the chosen table as interactive interface
items
/* This part [70]-[80] of your explanations is not clear because
* listing all rows in a table effectively means to
* display the table itself. As you don't say "display the table"
* but using other wording I suspect that by "all rows" you
* mean something else. All table's headers as a vertical list?
* Please elaborate on that.
*/

[90] on a "row" chosen add it to the filter
/* Can be only one "filtering row" or as many as needed?
*/

[100] Next step?

[110] Expected results?

[100]

 

[Scott Bass]

Hi VK,

Thanks for the time you've taken to reply. Much appreciated.

See comments below prefixed by SB:. Sorry my newsreader didn't prefix your
text with ">". I must get a better newsreader than Outlook Express

I'm not married to using XML as the internal data store, it's just that
it's
so ubiquitous that I thought it would be a good choice. For example, .Net
can easily serialize a DataTable (in this case the INFORMATION_SCHEMA
views
in MS SQL Server) with a minimum of fuss. And I was hoping Javascript
could
easily parse that XML to load listboxes or arrays. If JSON can be written
out by .Net or Java, I'm happy to use that.

Yes, JSON interfaces are ported to nearly any programming platforms
including C# and Java, see http://json.org
At the same time if you already have a working XML data module and you
are comfortable with it then no problem.

I don't need to save state between launching these web pages in the
browser.
OK

But I would like the page to be "interactive" as previously discussed.
If I had an example of non-displaying XML or JSON, and a bit of code to
parse it into loading a listbox, that would be great - I could run with
this.
I may just hard code a sample page - once I deduce the format of that page
;-). A listbox with say two "databases" X 2 "tables" X 3 "columns" with
the
desired filtering.

At this stage the actual layout and interface details are not so
relevant. It is more important to define

1) The target UA coverage. Besides the obvious (IE7,8 / FF, ...) do
you need to make it fully functional for IE6 users? Do you need to
make it fully functional for some mobile device auditory?

SB: The application will consist of two parts:

1) a .Net (probably) application that will look much like I've described: 2
panels on the left, one big panel on the right. It will retrieve DataSets
from various ODBC databases (SQL Server, SAS, perhaps others) and display
the data in a DataGrid. The data retrieved will be the "dictionary"
metadata maintained by the respective databases. Clicking in the panels
will simply filter the retrieved data - adding a where condition, if you
will.

2) The application will have a "snapshot" or "export" button that will save
the current state (i.e. selected data) to external files - a disconnected
layer, if you will. It is this part of the application that generated my
initial post. How should I write out this "snapshot" data, and then parse
it to render a browser page?

The target audience for the exported files will be internal users within my
organization, and customer organizations for which we do coding work. So a
rather limited audience. I'm happy to support a limited subset of browsers,
say IE7/8 and FF, as well as require a particular browser framework/plugin,
such as Silverlight, Flex, whatever.

2) The exact data retrieving and manipulation block schema. Correct me
if I'm wrong and finish:

SB: See above, plus addtional comments below...

[10] HTML app open SB: Yes
[20] read entire data dictionary into memory SB: not really, the "snapshot"
will have written out this data as static data and embedded in the files.
[30] parse for all databases SB: Yes, of the static data
[40] list all databases as interactive interface items SB: Yes

[50] on a database chosen parse it for all tables in this database SB:
Yes, essentially adding a where condition to the DataSet (where database
in...)
[60] list all tables in the chosen database as interactive interface SB:
Yes
items

[70] on a table chosen parse it for all rows in this table SB: Yes, similar
to the tables query (where table in ...)
[80] list all "rows" in the chosen table as interactive interface items SB:
Yes, I envision a DataGrid of table name, column name, type, length, label,
etc (desired column attributes).
/* This part [70]-[80] of your explanations is not clear because
* listing all rows in a table effectively means to
* display the table itself. As you don't say "display the table"
* but using other wording I suspect that by "all rows" you
* mean something else. All table's headers as a vertical list?
* Please elaborate on that.
*/

SB: Not the *data* in the table, but the *columns* in the table as a
vertical list. One column per row in the list.

So now in the RH panel, I've listed all the columns in one or more tables.
A cool thing is if I can have a rich browser interface, i.e. a DataGrid, I
can then sort on column name (initial display would be sorted by table name,
then column name). But I then want to be able to filter on all columns in
all tables where name = "foo".

So say my chosen user interface is a clickable column name (could also be a
text entry field and a submit button). Clicking the name would then filter
on all columns named foo. I could then look for length/label/attribute
inconsistencies between tables.

And perhaps add more "search" functionality, but that's tangential to the
initial "I need architectural advice" query.

Finally, perhaps both parts of the application could be a browser/web app,
but I must be able to snapshot the data into static files (with a dynamic
UI) where I am disconnected from both the database and the web server.
However, I'd rather not deal with browser security settings that might limit
writing files to the local hard drive.

[90] on a "row" chosen add it to the filter
/* Can be only one "filtering row" or as many as needed?
*/

SB: Clicking a row is "special" - I would expect it to retrieve all
matching columns for all tables in the selected databases (i.e. ignore some
of the filtering previously set in the 2 LH panels).

[100] Next step?

[110] Expected results?

[100]

SB: As an aside, both your and Hans-Georg's replies led me to Google a few
things (i.e the search "Javascript vs. Silverlight vs. Flex"), which led me
to this page:

http://bubblemark.com/

From this page I deduce that *clearly* there are a number of technologies I
could use to approach my problem. The whole point of my post was guidance
as to 1) "is it possible" (a rich browser side application) - the answer is
"yes", and 2) suggested architecture/technology to use.

I'm on a .Net kick at the moment, but have done some work with Javascript in
the distant past (more minor maintenance than full blown development).
Given my limited web application skill set, I'm leaning toward hitting the
books on Javascript, Silverlight, and WPF as potential technologies to use
for my problem statement, but welcome additional feedback from anyone that
may further guide my study.

Apologies again if this post is hard to follow as to who said what. I
really must get a better PC-based newsreader!!!

Kind Regards,
Scott