On Mar 5, 2012, at 6:52 AM, Martijn Moeling wrote:
> I think I've got it working correctly.
>
>
> in my mixin I now do:
>
> @declared_attr
> def id(self):
> return ExtColumn('JSid',Unicode(255), default = None)
>
> so the id property is actually stored in the DB Column 'JSid'
> Since my introspection looks at the python class, it takes the name from the
> class definition and put's 'id' in the json.
>
> the only change is 'JSid' in the definition and no extra overhead is added.
>
> This is nice since whilst solving this I found out that I have a few more of
> these (i.e. Name and name)
> Since I use PostgreSQL I got away with this….
yeah I'm surprised you didn't know about the "attribute =
Column("someothername", ) calling form ? I point it out in several places
in the docs, it has its own section:
http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#naming-columns-distinctly-from-attribute-names
and additionally
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative.html#defining-attributes
.
Glad you got it working but also I will be looking into removing that lower()
logic by default in 0.8 since it is usually wasteful.
>
> Martijn
>
> On Mar 5, 2012, at 11:55 , Martijn Moeling wrote:
>
>> 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.
>
>
> --
> 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.
