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….
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.
