On Sat, Jun 2, 2012 at 1:25 PM, Sahananda (Jon) Wolfers <
[email protected]> wrote:
Jon,
Please also refer to my follow up to Rick's other post.
> One thing that occurs to me is that ooSQL.cls is not a specific enough
name
> for this class. I hope that in the future there will be similar
frameworks
> for other databases (as the SQLLite people are the first to admit that it
is
> not suitable for multiple client use).
> perhaps ooSQLLiteConst.cls would do as a name or you might think of
> something more suitable.
This can be changed of course. the ooSQL.cls is also needed when using the
functional interface. I named it this because the SQLite constants are all
named:
SQLITE_const
and .ooSQL~ is the same number of letters as the SQLITE_ prefix so that you
have:
SQLITE_NOMEM
.ooSQL~NOMEM
Which I thought would be easy to explain to the "I won't touch objects"
group, or really anyone, by saying exactly replace SQLITE_ with .ooSQL~
Of course, I also was hoping I could convince people to read the SQLite
documentation rather than write a new document that just repeats what the
SQLite documentation says.
The ooSQL class is not currently a mixin class, but originally it was going
to be. This could be changed to:
::class 'ooSQLiteConstants public mixin
and if people wanted to shorten things they could inherit the class in
their programs:
::class 'C' inherit ooSQLiteConstants
say .C~NOMEM
which would produce 7.
> I have made use of two ooRexx designed SQL frameworks, both wrappers for
> rexxSQL
Following what I said in the other post about ooSQLite, currently, not
really being a framework and not having a higher level 'DataBase' object.
> Firstly oRexxSQL which provided two approaches, one allowing a query to
> emulate a stream, and the other allowing cursoring through a result set.
>
> I found it quite confusing with it's miriad methods, and after I found a
way
> to use it to return data settled down to use a couple of classes and
> methods.
ooSQLite does / will also have a myriad of methods because I would like to
be able access all of SQLite's APIs from my Rexx program.
But, 90% of them would not normally be used. You could un-confuse yourself
here by only using the few needed.
Plus, if a higher level abstraction is added as a 'DataBase' object, the
myriad of methods would not be exposed in that object.
> I was happy with that until I saw Lee Peedin's approach, which returned a
> dataset as an array of directory objects. I have since been using an
> adaptation of that and as a user I am very satisfied.
Right now data returned from a SELECT is returned as an array of arrays.
Originally, before I wrote any code, I was thinking there would be a
ResultSet object. I toyed with that. And I tried a 2-dimensional array
and an array of directory objects. (I have used an array of directory
objects in my CSV "databases.")
For what I've coded so far, I found an array of arrays to be the best.
This uses the convention that the first item in the array is an array of
the column names. Array[2] through Array[array~items + 1] are the rows in
the result set.
I'm thinking now that it would be easy enough to add a way for the user to
specify how the results of queries should be returned. An array of arrays
or an array of directory objects, or even possibly a stem of stems.
SQLite doesn't have cursors. But, it does use a lot of callbacks and
actually has a number of different ways of getting the result of a SELECT.
Here again, ooSQLite supports all of those ways. With my thinking that it
is better to let the user pick the method most suitable for them, rather
than providing one method and let the user live with it.
SQLite has a 'prepared statement' which you can use to step through each
row of data from a select. That is actually the method I like the best.
Something like this:
stmt = .ooSQLiteStmt~new(dbConn, 'SELECT * FROM foods ORDER BY name;')
stepRC = stmt~step
if stepRC == .ooSQL~row then do
colCount = stmt~columnCount
header = ''
do i = 1 to colCount
header ||= stmt~columnName(i)~left(20)
end
say header
say '='~copies(80)
do while stepRC == .ooSQL~row
row = ''
do i = 1 to colCount
row ||= stmt~columnText(i)~left(20)
end
say row
stepRC = stmt~step
end
end
Which produces output similar to:
id type_id name
======================================================
86 4 A1 Sauce
213 9 All Day Sucker
212 9 Almond Joy
189 8 Apple
129 7 Apple Cider
190 8 Apple Pie
...
As I mentioned in the other post, this is all a rather low-level
abstraction.
A convenience method is exec() which will return an array of arrays.
Something like:
dbName = 'ooFoods.rdbx'
dbConn = .ooSQLiteDB~new(dbName, .ooSQL~OPEN_READWRITE)
resultSet = dbConn~exec('SELECT * FROM foods ORDER BY name;', .true)
z = printResultSet(resultSet)
ret = dbConn~close
return ret
In the above, resultSet is an array of arrays and printResultSet() is just
code to deal with the data.
> I made a rather odd design decision in my adaptation which I have never
been
> sure whether or not I regretted, the DML methods all return true if there
is
> an error and false if not. This has saved me hundreds of lines of code,
but
> I am unsure that it reads well.
>
> The main methods It provides are connect, disconnect, query (ie select
> queries), execute (ie the other DML queries and DDL queries), commit,
> rollback, and describe. Where a dataset is returned it is at the rows
> attribute. Other main attributes are database, rowcount, details (the
> contents of the status stem passed back by rexxSQL, last_insert_id.
If you've followed what I said in the other post, currently the classes in
ooSQLite are low-level abstractions. There is no higher-level 'Database'
abstraction. I'm thinking of adding a default implementation of a
'DataBase.' This would be along the lines of your adaption above where the
lower-level work is done for you.
--
Mark Miesfeld
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Oorexx-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/oorexx-devel
