On Nov 30, 2006, at 7:20 AM, ameri nese wrote:
> FoxPro is very good, I think, at the following:
> 1. Foxpro functions and SQL mix freely. Using pysqlite2, we have to
> register functions. That's fine, but one of the annoying things about
> writing your SQL inside of quotes is that there is no code completion,
> i.e. wait till the program runs before you made some small little
> error (maybe you don't always write sqlite SQL and you are confused by
> dialect differences). Does Dabo help out with registering the
> functions? Does it check my SQL? If it does, does it know to look at
> the registered functions as part of the SQL syntax?
Dabo is database-agnostic, so no, it doesn't do any syntax checking
of SQL.
What sort of functions do you need? Something more complex than the
ones built into SQLite, I assume. In that case, you also have the
option of running a simple query, and then massaging the data
locally. The Dabo bizobj has a 'replace' command, with the following
syntax:
bizobj.replace(field, valOrExpr, scope=None)
'field' is the name of the field in the data set to replace.
'valOrExpr' can either be a literal value, or, if prefixed by an
equals sign, a dynamic expression, such as a function call. 'scope'
is a filtering expression to limit the replace to records that match
the passed scope value; if it isn't passed, all records are affected.
So what I tend to do is rather than construct some unwieldy SQL
statement, I retrieve the fields I want, and then write Python
functions that do the complex stuff on the resulting data set. So
here's an example:
def someComplexStuff(val):
val = val.strip()
if val.startswith("Z"):
val = val[2:].lower()
elif len(val) > 14:
...
...
return val
biz.requery()
biz.replace("myField", "=someComplexStuff(myField)")
> 2. Foxpro is excellent at displaying data for editing. I regularly
> have tables of tens of thousands of rows, but it doesn't croak because
> the table is big. How does Dabo's fare?
Dabo grids can handle millions of rows (assuming you have sufficient
RAM), since the grid is based on a virtual table, rather than
actually constructing all the cells at once. Scrolling and searching
is very fast.
> I can do things easily like
> add a column or move a column to the front (though it doesn't save
> this for me and I think it should). I'd like to edit data right in
> the table just like in Foxpro or an Excel file because I often get
> dirty data and have to clean it up.
I don't know about adding a column visually, although you could
certainly write code to do that. All you really need is:
grid.ColumnCount += 1
newCol = grid.Columns[-1]
newCol.DataField = "myNewField"
If you are using bizobjs (you *should* be!), forms by default check
for changes before they close. You can also save changes at any time
by calling form.save().
> 3. I need good internationalization support. I would prefer warnings
> when the data cannot be all displayed properly in my local code page
> (if I'm on windows). I'd like unicode support (I expect this is built
> into Dabo given that it's written in Python).
Unicode support is indeed built into Python, but to be honest, there
are probably some places where the Dabo code doesn't handle unicode
values properly. For example, one of our Russian users recently found
a bug in the way our grids handled incremental search when using
Cyrillic characters. We were able to track that down and correct it.
So while our unicode support may not be perfect, we're getting
pretty close!
> It's not like Foxpro is perfect. There are a couple features
> regarding viewing and editing that I'd like to be able to use:
>
> 1. I'd like to write selects and edit that data and have it save back
> to the original table too. (Without having to write more SQL)
That's simple. Just call form.save() or bizobj.save() (depending on
the level your code is working on) to save any changed records.
> 2. I'd like to click on a column heading and sort based on a row.
> Click again and get order by desc.
...and then click a third time to remove the sort? Yep, that's how
Dabo grids work. And, as mentioned above, they also have incremental
search on any column, even if the grid isn't sorted on that column.
> I don't want another table or a
> view or a cursor or anything. I just want to edit the results and
> have the results saved directly in my original table. If the sort
> might take a long time, maybe pop up a dialog progress box and allow
> me to cancel. (Foxpro has a nice progress bar for queries).
Sorting is based on Python lists, and is pretty fast. I just tried
sorting the 43,000-record zipcode demo table, and it took less than a
second.
> So what do you think? Can Dabo help me out here? If not, could I
> write a database viewer in Dabo that could allow me to easily do this?
I think that what you describe is perfect for Dabo.
> I much prefer writing Python to writing anything besides SQL in
> Foxpro, and overall it is my preferred programming language. I'm
> really interested in Dabo because I think Microsoft gets a bad rap
> these days, but unlike the authors of Dabo, some people don't realize
> how much convenience some Microsoft products can give you.
Imagine having those conveniences without all the other crap that
they force on you, such as restrictive and expensive licensing,
unconscionable EULAs, etc. That's "where we want to go today". ;-)
> International support in open source products can be a joke and a
> decent IDE can be a great help. Again, one of the most annoying
> things about writing SQL inside of Python are the little mistakes you
> make--SQL dialects are varied and you may have to deal with more than
> one. Well, I'd greatly appreciate any help.
The bizobj/cursor classes also contain what we call the SQL builder,
where you build your SQL using simple Python method calls. You have
to know the fields and table names, but you don't need database-
specific syntax, since our backend wrappers handle that for you. So
you can do something like:
biz.setFrom("zipcodes")
biz.addField("ccity")
biz.addField("cstate")
biz.addField("czip")
biz.addWhere("ccity = 'Chicago')
biz.addSortBy("czip")
biz.setLimitClause(20)
biz.requery()
This will create the correct SQL to return the first 20 records
where the city name is 'Chicago', sorted by zipcode, and it will work
with MySQL, Firebird, PostgreSQL, or SQLite, even though the syntax
for each backend would be slightly different.
-- Ed Leafe
-- http://leafe.com
-- http://dabodev.com
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
