[web2py] Re: DAL and schema
this thread is quite old, in the meantime there is already schema support
in web2py with rname. e.g.
db.define_table('mytable',
rname='myschema.mytable')
Alex
On Friday, March 11, 2016 at 8:31:07 PM UTC+1, Ben Wolski wrote:
>
> executing the following query after defining my db in db.py helped me
> query an Oracle db that requires a schema:
>
> db_prod.executesql("ALTER SESSION SET CURRENT_SCHEMA = BDR;")
>
> El viernes, 2 de marzo de 2012, 3:58:35 (UTC-8), Wuwei escribió:
>>
>> Hello,
>> I'm trying to use DAL with a MSSQL db.
>> I need to work with tables from 2 different schemas but I don't find how
>> to specify the schema when I define the table.
>>
>> Can you help me?
>>
>> Thank you.
>>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: DAL and schema
executing the following query after defining my db in db.py helped me query
an Oracle db that requires a schema:
db_prod.executesql("ALTER SESSION SET CURRENT_SCHEMA = BDR;")
El viernes, 2 de marzo de 2012, 3:58:35 (UTC-8), Wuwei escribió:
>
> Hello,
> I'm trying to use DAL with a MSSQL db.
> I need to work with tables from 2 different schemas but I don't find how
> to specify the schema when I define the table.
>
> Can you help me?
>
> Thank you.
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: DAL and schema
On 28 June 2013 01:08, Alex mrauc...@gmail.com wrote: I'd also appreciate schema support for web2py. +1 -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
Yes, the word ACROSS clarifies it. Besides, i've got another problem escaping SQL. I want to add this to the SQL sentence: WHERE b.type LIKE 'L''%' E.g. all types beggining with an L and following with an apostrophe. I've escaped the apostrophe writing it twice, but if filter is user input i need to use a function that escapes any string. DAL does this for us but in this case i cannot use DAL. Can i access to the escaping functions of DAL (glueon)?. Something like: user_escaped = glueon.escaping_function(user_input) sql = sql + WHERE b.type LIKE ' + user_escaped + %' I've tested different escaping function without success, and have no idea how to make use of glueon internal escaping. Best regards, Manuel. El jueves, 27 de junio de 2013 13:53:17 UTC+2, Anthony escribió: On Thursday, June 27, 2013 7:42:54 AM UTC-4, viniciusban wrote: See this: http://web2py.com/books/default/chapter/29/06#Inner-joins He was asking about joining tables across MySQL schemas. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
Hi, I have the same problem with a legacy MySQL database. We have different Schemas and need to use plenty of JOINs of tables from different Schemas. I've couldn't find any solution for this in the documentation, so the only way i've found is writing the SQL of my own, like in: SELECT b.serial, p.name FROM Hardware.boards b INNER JOIN Software.products p ON p.type = b.type Is there something stable in DAL to do this?. Thanks in advance. Best regards, Manuel. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
See this: http://web2py.com/books/default/chapter/29/06#Inner-joins On Thu, Jun 27, 2013 at 7:45 AM, mcamel mca...@gmail.com wrote: Hi, I have the same problem with a legacy MySQL database. We have different Schemas and need to use plenty of JOINs of tables from different Schemas. I've couldn't find any solution for this in the documentation, so the only way i've found is writing the SQL of my own, like in: SELECT b.serial, p.name FROM Hardware.boards b INNER JOIN Software.products p ON p.type = b.type Is there something stable in DAL to do this?. Thanks in advance. Best regards, Manuel. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
On Thursday, June 27, 2013 7:42:54 AM UTC-4, viniciusban wrote: See this: http://web2py.com/books/default/chapter/29/06#Inner-joins He was asking about joining tables across MySQL schemas. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
Sorry, my fault. On Thu, Jun 27, 2013 at 8:53 AM, Anthony abasta...@gmail.com wrote: On Thursday, June 27, 2013 7:42:54 AM UTC-4, viniciusban wrote: See this: http://web2py.com/books/default/chapter/29/06#Inner-joins He was asking about joining tables across MySQL schemas. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
I'd also appreciate schema support for web2py. I'm working on a project with Postgres and different schemas. For now I could solve the problem with adding the schema to the db search path. Since we are creating new tables we can make sure they are unique, for legacy systems it could be problematic when different schemas contain the same table names. real schema support in DAL would be great. At the moment it is possible to define tables but not much more (e.g. db selects put all queried fields in an '_extra' field, validators don't work, etc.) afaik schemas are also used in Oracle and MySQL and are commonly used in larger projects - at least many databases I worked on had multiple schemas. So I think this topic is really important (not just for me). -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL and schema
I made some progress. I was trying to connect to the wrong database. That caused my 'hanging' problem of yesterday. I have succeeded in creating auth_user in a different schema than 'public' using MyDAL but when I try to access it with appadmin it does not know about the schema. The model was in models/db.py - not in a module so there should not be import problems for appadmin. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
[web2py] Re: DAL and schema
On Thursday, 5 April 2012 15:02:49 UTC+2, adesst wrote: @johann, have you tried MyDAL to connect and follow the how to? https://github.com/adesst/web2py I have tried MyDAL without success today. My code: In models/0.py: from applications.toets.modules.mydal import * settings = Storage() settings.migrate = True settings.database_uri = MyDAL(postgres://js:513726.js@localhost:5432/akb, pool_size=15)
[web2py] Re: DAL and schema
Apologies. Here is the remainder of my reply:
In models/db.py (the process hangs in the last line of this code.:
from applications.toets.modules.mydal import *
db.define_table('auth_user',
Field('id','id',
represent=lambda id:SPAN(id,' ',A('view',_href=URL(
'auth_user_read',args=id,
Field('username', type='string',
label=T('Username')),
Field('first_name', type='string',
label=T('First Name')),
Field('last_name', type='string',
label=T('Last Name')),
Field('email', type='string',
label=T('Email')),
Field('password', type='password',
readable=False,
label=T('Password')),
Field('created_on','datetime',default=request.now,
label=T('Created On'),writable=False,readable=False),
Field('modified_on','datetime',default=request.now,
label=T('Modified On'),writable=False,readable=False,
update=request.now),
Field('registration_key',default='',
writable=False,readable=False),
Field('reset_password_key',default='',
writable=False,readable=False),
Field('registration_id', length=64, default=lambda:str(uuid.
uuid4()),
writable=False,readable=False),
format='%(username)s' ,
migrate=False, # make sure to not create the table
table_class=MyTable # notice i use MyTable instead the
original Table, that support schema
)
db.auth_user.set_schema('toets0', migrate=True, _adapter=db._adapter)
In the Postgresql-log:
2012-05-08 12:13:20 SAST LOG: could not receive data from client:
Connection reset by peer
In the end I used Ctrl-C to stop the process - leaving this traceback:
^CTraceback (most recent call last):
File web2py.py, line 20, in module
gluon.widget.start(cron=True)
File /home/js/web2py/gluon/widget.py, line 869, in start
import_models=options.import_models, startfile=options.run)
File /home/js/web2py/gluon/shell.py, line 189, in run
_env = env(a, c=c, import_models=import_models)
File /home/js/web2py/gluon/shell.py, line 131, in env
run_models_in(environment)
File /home/js/web2py/gluon/compileapp.py, line 513, in run_models_in
restricted(code, environment, layer=model)
File /home/js/web2py/gluon/restricted.py, line 205, in restricted
exec ccode in environment
File applications/toets/models/db.py, line 81, in module
db.auth_user.set_schema('toets0', migrate=True, _adapter=db._adapter)
File applications/toets/modules/mydal.py, line 553, in set_schema
_adapter.create_table(self)
File /home/js/web2py/gluon/dal.py, line 770, in create_table
logfile = self.file_open(table._loggername, 'a')
File /home/js/web2py/gluon/dal.py, line 579, in file_open
fileobj = portalocker.LockedFile(filename,mode)
File /home/js/web2py/gluon/portalocker.py, line 121, in __init__
lock(self.file,LOCK_EX)
File /home/js/web2py/gluon/portalocker.py, line 88, in lock
fcntl.flock(file.fileno(), flags)
KeyboardInterrupt
Regards
Johann
Re: [web2py] Re: DAL and schema
On 5 April 2012 15:02, adesst adesantoas...@gmail.com wrote: @johann, have you tried MyDAL to connect and follow the how to? https://github.com/adesst/web2py I have not tried again. Maybe this week. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: DAL and schema
Nice to hear that you would test it. I've tested MyDAL with Postgre 8.4, and works as expected. +adesst -Original Message- From: Johann Spies johann.sp...@gmail.com Sender: web2py@googlegroups.com Date: Tue, 10 Apr 2012 10:02:10 To: web2py@googlegroups.com Reply-To: web2py@googlegroups.com Subject: Re: [web2py] Re: DAL and schema On 5 April 2012 15:02, adesst adesantoas...@gmail.com wrote: @johann, have you tried MyDAL to connect and follow the how to? https://github.com/adesst/web2py I have not tried again. Maybe this week. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
[web2py] Re: DAL and schema
On Thursday, 5 April 2012 01:17:48 UTC+2, Massimo Di Pierro wrote: Can a query involve tables from different schemas? Yes. This query works in Postgresql: select * from toets.wiesdaar left join public.akb_authors on (surname = van) where surname is not null Regards Johann
[web2py] Re: DAL and schema
@johann, have you tried MyDAL to connect and follow the how to? https://github.com/adesst/web2py On Thursday, April 5, 2012 1:46:48 PM UTC+7, Johann Spies wrote: On Thursday, 5 April 2012 01:17:48 UTC+2, Massimo Di Pierro wrote: Can a query involve tables from different schemas? Yes. This query works in Postgresql: select * from toets.wiesdaar left join public.akb_authors on (surname = van) where surname is not null Regards Johann
[web2py] Re: DAL and schema
Massimo,
I think it is very simple to support schemas: you should simply allow a .
(dot) in table name.
So that we can define a table like this:
db.define_table('myschema.mytable', Field(), ...)
This syntax now raises an exception:
SyntaxError: only [0-9a-zA-Z_] allowed in table and field names, receiving
myschema.mytable
In this way generated sql query will be like:
select * from myschema.mytable
which is exactly what I need.
I think that also joins between tables from different schemas will work
well.
Thank you.
Il giorno sabato 24 marzo 2012 00:51:56 UTC+1, Massimo Di Pierro ha scritto:
I am not sure if this can be done or not. If not supported, we can add
support. Can you open a ticket and post an example of how to do it in SQL?
[web2py] Re: DAL and schema
Can a query involve tables from different schemas?
Massimo
On Wednesday, 4 April 2012 16:46:30 UTC-5, Wuwei wrote:
Massimo,
I think it is very simple to support schemas: you should simply allow a .
(dot) in table name.
So that we can define a table like this:
db.define_table('myschema.mytable', Field(), ...)
This syntax now raises an exception:
SyntaxError: only [0-9a-zA-Z_] allowed in table and field names, receiving
myschema.mytable
In this way generated sql query will be like:
select * from myschema.mytable
which is exactly what I need.
I think that also joins between tables from different schemas will work
well.
Thank you.
Il giorno sabato 24 marzo 2012 00:51:56 UTC+1, Massimo Di Pierro ha
scritto:
I am not sure if this can be done or not. If not supported, we can add
support. Can you open a ticket and post an example of how to do it in SQL?
Re: [web2py] Re: DAL and schema
On Tuesday, March 27, 2012 5:35:57 PM UTC+7, spyker wrote: On 26 March 2012 20:22, adesst adesantoasman@... wrote: @Johann, i don't know if you would copy paste the diff i've made into PgAdapter, and do some tests and post the results. Pg and Oracle schema works in the same way. I have downloaded and looked at MyDal and the README but I am not sure which 'diff' you refer to. I have looked at your code buit seems a bit risky for my to jump in before I understand fully what you are doing. I will have to spend some time to work through it. I was hoping that you will open a ticket and that the schema-option may become an official part of DAL. Maybe you are working in that direction. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3) Sorry about the confusing 'diff', you could check here https://github.com/adesst/web2py, have added postgres. And i did post the issue here https://github.com/web2py/web2py/issues/6 Hope it helps.
Re: [web2py] Re: DAL and schema
On 26 March 2012 20:22, adesst adesantoas...@gmail.com wrote: @Johann, i don't know if you would copy paste the diff i've made into PgAdapter, and do some tests and post the results. Pg and Oracle schema works in the same way. I have downloaded and looked at MyDal and the README but I am not sure which 'diff' you refer to. I have looked at your code buit seems a bit risky for my to jump in before I understand fully what you are doing. I will have to spend some time to work through it. I was hoping that you will open a ticket and that the schema-option may become an official part of DAL. Maybe you are working in that direction. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: DAL and schema
I would really appreciate it if we can have that in DAL. I am a Postgresql user. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
[web2py] Re: DAL and schema
@Johann, i don't know if you would copy paste the diff i've made into PgAdapter, and do some tests and post the results. Pg and Oracle schema works in the same way. I currently focusing to dev the MyDAL to support all the joins of Oracle and doing some tests on reference (many/one-to-many) On Mar 26, 2:25 pm, Johann Spies johann.sp...@gmail.com wrote: I would really appreciate it if we can have that in DAL. I am a Postgresql user. Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Re: DAL and schema
I don't know if this going to help. But i've made an extend of DAL, OracleAdapter and Table so they could work with schema/database descriptor. https://github.com/adesst/MyDAL So far what i did is to meet my need, and yes there are lots of work to make my extension work well with inner join and etc. On 03/24/2012 06:51 AM, Massimo Di Pierro wrote: I am not sure if this can be done or not. If not supported, we can add support. Can you open a ticket and post an example of how to do it in SQL? On Friday, 2 March 2012 05:58:35 UTC-6, Wuwei wrote: Hello, I'm trying to use DAL with a MSSQL db. I need to work with tables from 2 different schemas but I don't find how to specify the schema when I define the table. Can you help me? Thank you. -- +adesst
Re: [web2py] Re: DAL and schema
Please open an issue and ask to be mrged to trunk
[web2py] Re: DAL and schema
I am not sure if this can be done or not. If not supported, we can add support. Can you open a ticket and post an example of how to do it in SQL? On Friday, 2 March 2012 05:58:35 UTC-6, Wuwei wrote: Hello, I'm trying to use DAL with a MSSQL db. I need to work with tables from 2 different schemas but I don't find how to specify the schema when I define the table. Can you help me? Thank you.
