Michael,
Thank you for confirming my worries and adding reason to it.
I'm not sure if you remember al my questions in the past but with my project
I'm constantly hitting the "impossible", although its fun, it can be
frustrating sometimes.
What I'm doing is something bigger than just an application, I'm building an
web based operating system, with Desktop in a browser and with a development
system for applications.
It is based on Open Source products and build from absolute scratch, there is
much more to it but as I signed a non disclosure agreement with my self, I
must stop here.
Since I Love Python and absolutely hate Javascript, HTML, CSS, I Have put a lot
of effort in doing away with those and replace them with Python, Basically what
I have is an IDE to develop web appellations which in itself is a web
application. Within that you can write Python which is "Compiled" into all the
parts needed by a browser.
It resembles something which is very close to Visual Basic 6.0 if you remember
that one from a developer point of view.
Since I use the Id field for SA in the usual fashion, I struggle where I
reflect Javascript objects with an "id" field.
Everything is generated with Introspection, I subclassed the SA Column to add
properties to the colums. These are non functioning in "run" mode but are used
when compiling to javascript. (the _constuctor returns the real Column, not the
extended one).
the id column is coming from Python classes generated from the sources of
ExtJs, the Id column is tightly bound to communication between browser and
back-end (Since its the recordId).
at the end the Id column will be/is hidden from the developer, but I have tons
and tons of code relying on Id.
Within the IDE a user would and should be able to instance an object with an
"id" doing so in the creation is no problem since python differs between id and
Id (and Id is never set by code)
so :
id = ExtendedSAColumn(Unicode(25), default = ….)
will end up ad {xtype : 'textfield' , id : 'the id' ……}
For "compile time" I have no Issues with overhead
I can rename id to JS_id (or something) but I need to be able to do
Someclass.id = 'the id'
and print Someclass.id (or use the value in some other way)
Since an Application and its components are reflected in the database, almost
all classes which are compiled into javascript will have an id column. adding a
@declared_attr to my mixin en a setter function and map id to JSid adds an id
column to everything and that is not what I want (and messes with my
introspection routines to reflect SA Database object classes)
I must find a way where the id field can be used in the IDE without adding any
special code there, it should be transparent to users of my system.
Thing is, on some objects (maybe most, at least the ones which have to do with
stores and models) I Introspect a Class and not an instance of that Class.
While during "runtime" these python objects are used to query the database and
converted to json to fit into the generated stores and models (as defined by
Extjs). and during runtime all "overhead" is just not there, so translating
JSid into id will be needed on each record, if it has an id property or not.
Is there a way to use declarative and map the id property to a different name?
like:
id = Column(integer, name='JSid'……)
I have something similair to this:
I've left out the filtering of SA Colum arguments as I did with the processing
of NON SA column arguments to __init__
class ExtColum(Column):
def __init__(self, type, *arg ,**kwarg):
#filter out the SA Column properties into "filteredoptions" and:
Column.__init__(self, type_, *arg,**filteredoptions)
def _constructor(self, name, type_ *a, **kw):
column= Column(type,*a, **kw)
column.name = name
return column
in the _constructor I can mess with the name:
def _constructor(self,name, type_ *a,**kw):
column= Column(type,*a, **kw)
if name == "JSid":
column.name = "id"
else:
column.name = name
I think this would fix runtime, as _constructor is called on record load
but how to set the Column name in the __init__
My introspection routines would "see" the "id" column as an ExtColumn but would
store it's value in JSid and not mess with the functionality of SA and or the
database as "jsid" would NOT interfere with Id.
Solving it this way, the overhead is only on ExtColumn based Columns which
saves a lot. Again this would be unusual use of SA for as far as I can see.
reading the docs for the Column object:
The name of this column as represented in the database. This argument may be
the first positional argument, or specified via keyword.
Names which contain no upper case characters will be treated as case
insensitive names, and will not be quoted unless they are a reserved word.
Names with any number of upper case characters will be quoted and sent exactly.
Note that this behavior applies even for databases which standardize upper case
names as case insensitive such as Oracle.
The name field may be omitted at construction time and applied later, at any
time before the Column is associated with a Table. This is to support
convenient usage within the declarative extension.
the last paragraph is the most interesting. testing however makes me unsure
where to put the name. and even more interesting, how to test for the name in
the __init__ ?
for some reason I do not seem to get no reference to name, even after calling
Column.__init__, self.name == None, Am I thinking in the wrong direction?
Martijn
On Mar 5, 2012, at 00:06 , Michael Bayer wrote:
>
> On Mar 4, 2012, at 2:18 PM, Martijn Moeling wrote:
>
>> sqlalchemy.exc.DataError: (DataError) invalid input syntax for integer:
>> "Blablabla"
>> LINE 3: WHERE ide_applicationcontroller."Application" = E'Blablabla...
>> ^
>> 'SELECT ide_controller."Id" AS "ide_controller_Id", ide_controller."Name"
>> AS "ide_controller_Name", ide_controller."onLaunch" AS
>> "ide_controller_onLaunch", ide_controller.id AS ide_controller_id \nFROM
>> ide_controller, ide_applicationcontroller \nWHERE
>> ide_applicationcontroller."Application" = %(param_1)s AND
>> ide_applicationcontroller."Controller" = ide_controller."Id"' {'param_1':
>> u'Blablabla'}
>
>
>>
>>
>> I do not understand where the "E" is coming from, I've check and checked and
>> never reference to "id" anywhere by mistake
>
> the "E" is part of how psycopg2/libpq renders bound parameters before passing
> off to the backend, and is normal.
>
>
>>
>> I can not freely choose my columnnames
>
> OK well I can confirm the use case is not supported at all on SQLAlchemy -
> SQLA is case insensitive in how it deals with result set column names. It's
> been that way since the beginning and nobody has ever asked for it to be
> changed, much less even noticed it, most likely. Had I been making this
> decision today, I would not have chosen the lower() option, as it adds
> performance overhead in any case and is for the vast majority of cases
> completely unnecessary. However, I don't recall if I was coming up against
> DBAPIs that were not giving me the correct casing within cursor.description,
> and I'm not sure if all DBAPIs handle this correctly, so there is some risk
> to changing the behavior.
>
> So I'd point out that this use case you have here is exceedingly unusual.
> Many databases don't support it, not even SQLite, which won't let me create
> such a table, even if I quote both names:
>
> duplicate column name: Id u'\nCREATE TABLE a (\n\t"id" INTEGER NOT
> NULL, \n\t"Id" INTEGER, \n\tPRIMARY KEY ("id")\n)\n\n' ()
>
> nor will the current release of MySQL:
>
> (1060, "Duplicate column name 'Id'") '\nCREATE TABLE a (\n\t`id`
> INTEGER NOT NULL AUTO_INCREMENT, \n\t`Id` INTEGER, \n\tPRIMARY KEY (`id`),
> \n\tUNIQUE (`Id`)\n)\n\n' ()
>
> So generally, mixing overlapping names based on case within relational
> databases is a really bad idea, and is just asking for trouble at every
> stage.
>
>
>> and do not really want to run code for every parameter just to see if there
>> is an id field and put the value in whatever other storage name, that will
>> add unwanted load to my system.
>
> So if your system is truly "generic" and you can't anticipate what existing
> names are present, I'd point out that right off, your approach will not work
> for SQLite or MySQL, which will not allow such a naming convention in any
> case.
>
> The logic we're talking about here would take place at configuration time in
> any case, and would be a completely minuscule check that runs just once per
> class at import time, so there's no "load" issue. Also, what if you were
> given a class that actually had the lowercase name "id" on it already? If
> you have no control over column names, don't you need to check for that name
> already existing? Or are you publishing a restriction that this name is
> "reserved", and in which case why not say that all casing conventions of "id"
> are reserved as well ?
>
>
>> Is this a bug? or do I hit a Feature of SA where it does not matter if id or
>> Id is used and somewhere a .lower() takes care of that. Can I switch it off
>> if so?
>
> The model for the statement compiler and result proxy works on a case
> insensitive model right now as far as how columns are located in result sets,
> I can't even get a plain row back using such a casing convention.
>
> Ticket #2423 (http://www.sqlalchemy.org/trac/ticket/2423) illustrates a patch
> that would allow the behavior to be configurable. As anticipated, it adds
> 7% method call overhead to key performance tests, when the "case insensitive"
> behavior is turned on as it doubles the function call overhead for each
> lower(). Which leads me to want to release it in 0.8 with the default
> behavior reversed, thereby avoiding the double-function call for the vast
> majority of cases that aren't attempting to refer to columns in a
> case-insensitive manner. But I'd have to do a deep sweep across all the
> DBAPIs I can find, because if any of them are lowercasing on their end, then
> the change will be incompatible - those DBAPIs might want to signal that the
> flag turns back on.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
