Support for BIT(1) as boolean?

[Alfonso Serra]

I need to do some rawsql calculations involving booleans and I would like to add boolean support to web2py using 0 or 1 BIT(1) in the database, so i dont have to convert "T" or "F" every time.

My question are:
Where can i find the code that does the conversion from CHAR() "T" to boolean and mysql table creation.

Is it posible to change this behaviour to web2py by default, to something more appropiate than CHAR?

What datatype would be best?

It looks like BIT exposes some problems,
http://www.xaprb.com/blog/2006/04/11/bit-values-in-mysql/

TINYINT(1) stores up to 127, maybe ENUM-1?

Thanks

[Alfonso Serra]

Im sorry i just seen Massimo's answer at the post:
https://groups.google.com/forum/#!searchin/web2py/boolean$20values/web2py/IukqqZF_PPE/Dehg9dKUT58J

import copy

db =DAL()

db._adapter.types = copy.copy(db._adapter.types)

db._adapter.types['boolean']='TINYINT(1)'

db._adapter.TRUE = 1

db._adapter.FALSE = 0

Ill try BIT

[Massimiliano]

Eventually take a look here:

http://www.web2pyslices.com/slice/show/1611/postgresql-custom-adapter

--
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+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Massimiliano

[Alfonso Serra]

Excuse me, one last question.

Is it really necesary?
import copy


db._adapter.types = copy.copy(db._adapter.types)

or modify the adapter right away will work? It looks like it does, but i dont know if theres any implication by skiping copy.

Thanks

[Yan Wong]

With this code, I now find that with the latest version of web2py (2.15.4), I get 

File "applications/XXX/models/db.py", line 46, in <module>

    db._adapter.types = copy.copy(db._adapter.types)

AttributeError: can't set attribute

I have deleted the copy.copy line, so that I am modifying the adapter straight away. I guess this is OK? Could someone confirm?

Cheers

Yan

[Massimo Di Pierro]

what adapter are you using. What is your URI string (without passwords please)?

[Alfonso Serra]

Hi Massimo.

This is the db declaration im using. It works well so far not using copy. Standard MySQL 5.5.24 connection. I have read that using BIT may cause problems on calculations with another fields, so TINYINT(1) (-127, 127) would do for a lack of a better option.

db = DAL("mysql://mysqluser:mypassw@localhost/mydb", pool_size = 10,  check_reserved=False, migrate=True)

    # db._adapter.types = copy.copy(db._adapter.types)

[Yan Wong]

On Monday, 11 September 2017 00:08:00 UTC+1, Massimo Di Pierro wrote:

what adapter are you using. What is your URI string (without passwords please)?

Apologies for the slow reply. Annoyingly, I've found that the solution (without copy) doesn't work at the moment. I'm using the standard adapter for MySQL, so that my appconfig.ini looks like

; db configuration

[db]

uri       = mysql://UN:XXX...@127.0.0.1/db

migrate   = 0

pool_size = 1

When I start web2py, I get:

Version 2.15.4-stable+timestamp.2017.09.02.04.02.22

Database drivers available: sqlite3, imaplib, pyodbc, pymysql, pg8000

And the pymysql version that loads by default seems to be:

Python 2.7.10 (default, Jul 13 2015, 12:18:59) 

[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.57)] on darwin

Type "help", "copyright", "credits" or "license" for more information.

>>> import pymysql

>>> print pymysql.__version__

0.7.9.None

[Yan Wong]

On Monday, 11 September 2017 00:08:00 UTC+1, Massimo Di Pierro wrote:

what adapter are you using. What is your URI string (without passwords please)?

By the way, starting web2py using Rocket now says /usr/local/lib/python2.7/site-packages/pymysql/cursors.py:166: Warning: (3090, u"Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release."), but I guess you know that.

[Yan Wong]

The following seems to work, by the way (modified from https://groups.google.com/forum/#!topic/web2py/TZsLnytjqhA)

        ## allow mysql tinyint

        from gluon.dal import SQLCustomType

        boolean = SQLCustomType(

             type ='boolean',

             native='TINYINT(1)',

             encoder = (lambda x: 1 if x else 0),

             decoder = (lambda x: True if x else False)

        )

then

Field('xxx', type = boolean,...)

[Alfonso Serra]

Thanks Yan, i will use your example if i cant fix this.

I can confirm the problem on Version 2.15.3-stable+timestamp.2017.08.07.12.51.45.

This is dal declaration working fine on previous versions

db = DAL("mysql://myuser:mypassw@localhost/myapp", pool_size = 10,  check_reserved=False, migrate = True)
# db._adapter.types = copy.copy(db._adapter.types)

db._adapter.types['boolean']='TINYINT(1)'
db._adapter.TRUE = 1
db._adapter.FALSE = 0

And to reproduce the problem, create a table with a boolean field and insert a record:

data = {"user": 1, "visible": True}
db.mytable.insert(**data)

C:\Users\User\Desktop\myapp\gluon\contrib\pymysql\cursors.py:166: Warning: (13
66, u"Incorrect integer value: 'T' for column 'visible' at row 1")
  result = self._query(query)
C:\Users\User\Desktop\myapp\gluon\contrib\pymysql\cursors.py:166: Warning: (13
66, u"Incorrect integer value: 'T' for column 'configurable' at row 1")
  result = self._query(query)
6L

I dont remember having this problem before. Is it a bug? What file should i edit to get rid of "T" and "F"?

[Alfonso Serra]

Yan's example worked. But still there are some quirks with the scheduler using connections for long processes.

You are able to change T and F for good editing gluon.packages.dal.dialects.base.py line 36
then use Field(.., type = "boolean") as usual. No adapter change with problems or SQLCustomTypes.

[Massimo Di Pierro]

I have seen that I am trying to get to the bottom of that. The string "NO_AUTO_CREATE_USER" does not appear in web2py, in dal, nor in rocket. My guess is that the error is caused by mysqldb. Do you agree?