Re: [sqlite] XML translation inquiry
I've been away and busy and never had a chance to respond to this. I decided to continue the response as a sqlite-users thread (as opposed to just responding directly to andrew) in case others wish to listen-in or respond. i am developing a brand new application (manager) that is 'constrained' only in that it must manage remote legacy appliances the speak xmlrpc. also, the new manager will be a web-based app running in tomcat. since xml is all over the place (legacy product API, tomcat cfg, product cfg, roles and responsibility cfg, etc...), it seemed conceivable to me that it could be helpful (read: simpler, more maintainable) to hide and persistent/db semantics from the application and let it only view data as xml and/or internal objcts (beans/servlets/etc...). since the new product development is still in the 'conception' stage - even the object hierarchy/relationships are not fully defined - at this moment, i have enormous flexibility wrt architecture. I am also trying to quantify/understand the trades-offs between object .vs relational db mgmt. (the mgmt app will not be an embedded application, but will run on, say, a linux server. the appliances it manages are embedded apps and will in fact use SQLite (in later releases) as will new embedded product dev't.) I am not all that db savvy wrt these issues, so i will listen to any offered advise, esp in the way of guidelines, that will help me make the right architectural decision. Tia, - rich Andrew Piskorski wrote: On Thu, Apr 15, 2004 at 02:37:13PM -0400, rich coco wrote: the goal would be to abstract out of the application the entire DB schema and allow the application to work exclusively with XML - and the generated methods - wrt not only data representation and transport but data storage and retrieval too. (ie, the application need And WHY would you want to do that exactly? To me it sounds like an excellent strategy to recommend to a competitor. please tell me more...as in why it's a bad idea. It's not that I know it to be a bad idea - I don't. Perhaps it's actually a very nice idea. But without knowing more, it sounds like a good way to make things a lot more complicated than they need to be for no gain. If you're using a RDBMS, why not just use the RDBMS? Generating some sort of limited XML format for sending over the wire to some remote application, sure I can see the value of that. But converting your entire relational schema to XML for internal use in your own program? Why? What can that possibly buy you over just using the relational schema directly? I mean, heck, the relational model was invented for a reason, why would you want to transform it to a hierarchal XML model and have your program manipulate that? And if (for some reason I can't fathom) what you really want is some sort of tree-structured XML schema, then why not use an native object or XML database or persistent storage API intended for that, rather than repeatedly converting between the RDBMS and XML? If you tell me you're sure there is some good reason I may be prepared to believe you, but offhand I can't think of one myself. -- rich coco [EMAIL PROTECTED] 781.736.1200 x165 Starbak Inc. 29 Sawyer Rd. Waltham, MA 02453 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] XML translation inquiry
please tell me more...as in why it's a bad idea. performance? are the in-memory translations really that cpu intensive? what if the data sets are never very large? thanks, - rich Andrew Piskorski wrote: On Thu, Apr 15, 2004 at 01:47:01PM -0400, rich coco wrote: the goal would be to abstract out of the application the entire DB schema and allow the application to work exclusively with XML - and the generated methods - wrt not only data representation and transport but data storage and retrieval too. (ie, the application need And WHY would you want to do that exactly? To me it sounds like an excellent strategy to recommend to a competitor. But if you really want to do it, Red Hat CCM/RHEA (open source, in Java) does include some sort of object-relational mapping layer: http://rhea.redhat.com/ -- rich coco [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] XML translation inquiry
this is a bit off topic i suppose, but here goes... does anyone know of an open-source translation toolkit that ultimately would generate code to map XML to/from SQL? Or maybe i am not phrasing the question properly. What I am looking for is a translation layer between the use of relational technology for storing and processing data and the hierarchical representation of the data - as XML in particular - for things like presentation and transport. I understand that Microsoft has a product called the Web Services Toolkit, which is specific to MS SQL Server that does this kind of thing. i am wondering if their may exist a generic open-source framework: * for specifying the relationship between an XML Schema and a RDBMS table schema * importing such a relationship (using whatever grammar) and generating class definitions that implement the desired translation layer. the goal would be to abstract out of the application the entire DB schema and allow the application to work exclusively with XML - and the generated methods - wrt not only data representation and transport but data storage and retrieval too. (ie, the application need not care about databases schemas and the specifics regarding data persistence, etc. google-ing hasn't helped me here, but that may be because i'm not google-ing smartly. i am open to suggestions as to where else I might post such a query. tia, - rich -- rich coco [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] ENUM semantics
I have a pre-existing sql file that I'd like to import
into SQLite. (eg, via the .read command of the sqlite
command line utility).
this sql file creates a bunch of tables.
Used in many places are column definitions like this:
replay enum('none','public','private') default NULL,
SQLite apprarently does not support the enum keyword?
(It's not listed in the 'Datatypes in SQLite' man page).
or does it?
The error message I get for statments like the above is:
SQL error: near "'none'": syntax error
Hmmm...if ENUM was not supported, I would have expected
the error to be 'SQL error: near "enum"...'
I've tried using double-quotes instead of single-quotes...
same error.
Anyway, how *does* one define the ENUM semantic in SQLite?
Do I have to write a user-defined function? (ugh!)
Tia,
- rich
--
rich coco
[EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] mySQL to SQLite conversion question
I see that the auto_increment keyword is not supported by SQLite: addressid bigint(20) unsigned NOT NULL auto_increment, . . . PRIMARY KEY (addressid) I understand that I have to move the 'unsigned' keyword (before 'bigint') but I do not know how to get the auto_increment semantic under SQLite. I found in the 'Datatypes in SQLite' manual page (in Section 2.0) the comment: "INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT." Is the following the proper re-write to get the precise semantic I am seeking? addressid INTEGER PRIMARY KEY NOT NULL Can I then omit the subsequent "PRIMARY KEY (addressid)" line? Is "NOT NULL" no longer needed? Can someone recommend a good SQL book (I am not a DB guy)? There are so many out there and I'd like to purchase just one, so I need to get it right the first time. I found on-line manuals by DB engine providers - eg, http://www.mysql.com/doc/en/SQL_Syntax.html - but I do not know when I may be looking at specialized extentions (eg, mySQL's REGEXP and auto_increment keywords). I'd like to have my own reference. Tia, - rich -- rich coco [EMAIL PROTECTED] 781.736.1200 x165 Starbak Inc. 29 Sawyer Rd. Waltham, MA 02453 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] moving from mySQL to SQLite - A guidance request
I have a legacy app that uses mySQL as the embedded RDBMS. The app makes explicit invocations to mysql library functions. There is no ODBC layer being used at all. I wish to replace mySQL with SQLite. Since I am bothering to do this, I wish to introduce an ODBC layer. I have learned about the existence of a SQLite-ODBC library (http://www.ch-werner.de/sqliteodbc/html/index.html) and that's a good thing. Just today I learned about a MySQL-ODBC bridge!!! (http://www.iodbc.org/index.php?page=mysql2odbc/index). This is a *very* good thing in that it seems I may not have to modify existing source code (with db calls spread all over the place). Or *is* it a 'very good thing'? Am I working too hard at avoiding work? That is, should I not use a the mySQL-ODBC bridge and hand-modify the existing source base to make ODBC call directly? maybe refactoring it to better encapsulate DB operations? What about existing SQL statements (explicit arguments to mysql function calls) that use SQL keywords/contructs (eg, REGEXP) not supported by SQLite? Is there a best-practices way of doing this kind of transition from one DB to another? advice welcome. -- rich coco [EMAIL PROTECTED] 781.736.1200 x165 Starbak Inc. 29 Sawyer Rd. Waltham, MA 02453 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
