Hello Villas, first al all...thank you thank you thank you! But I didn't
arrived to the end of the game yet. but I think I'm near with your
suggestions.
I followed the "easier solution" and, with a lot of effort, now I have my
MSSQL database filled of my data.
But when I start tha application web2py I have this error:
Error ticket for "ITAsset"Ticket ID
127.0.0.1.2020-08-20.16-37-26.91c5b362-833d-4bdc-afef-3b727fde37a8
<class 'pyodbc.ProgrammingError'> ('42S01', u"[42S01] [Microsoft][ODBC SQL
Server Driver][SQL Server]There is already an object named 'sede' in the
database. (2714) (SQLExecDirectW)")Versione
web2py™ Version 2.14.6-stable+timestamp.2016.05.10.00.21.47
Python Python 2.7.15: C:\Python27\python.exe (prefix: C:\Python27)Traceback
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
Traceback (most recent call last):
File "C:\web2py\gluon\restricted.py", line 227, in restricted
exec ccode in environment
File "c:/web2py/applications/ITAsset/models/db_asset.py"
<http://127.0.0.1:8000/admin/default/edit/ITAsset/models/db_asset.py>, line 12,
in <module>
format='%(nome)s')
File "C:\web2py\gluon\packages\dal\pydal\base.py", line 834, in define_table
table = self.lazy_define_table(tablename,*fields,**args)
File "C:\web2py\gluon\packages\dal\pydal\base.py", line 873, in
lazy_define_table
polymodel=polymodel)
File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 482, in
create_table
self.create_sequence_and_triggers(query, table)
File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1369, in
create_sequence_and_triggers
self.execute(query)
File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1388, in
execute
return self.log_execute(*a, **b)
File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1382, in
log_execute
ret = self.get_cursor().execute(command, *a[1:], **b)
ProgrammingError: ('42S01', u"[42S01] [Microsoft][ODBC SQL Server Driver][SQL
Server]There is already an object named 'sede' in the database. (2714)
(SQLExecDirectW)")
I will attach you db.py and dbasset.py, my models and the sql.log
generated...
I tested with all combination of "migrate" and "fake_migrate" but web2py
try every time to create the table "sede and so the error.
Can you have another suggestion? when I will finish I inveted you yo a
dinner in the restaurant! :-)
thank you
Il giorno mercoledì 19 agosto 2020 12:32:20 UTC+2, villas ha scritto:
>
> *An easier solution for you could be this!*
> Simply specify your created_by/modified_by as extra fields (which should
> side-step self-referencing probem).
> I didn't test it, but it seems like a good idea to me. You can then use
> normal migrations etc.
>
> auth.settings.extra_fields['auth_user']= [
>
> Field('is_active', 'boolean', default=True),
> Field('created_on', 'datetime', default=request.now),
> Field('created_by', 'integer', default=auth.user_id),
> Field('modified_on', 'datetime', update=request.now),
> Field('modified_by', 'integer', update=auth.user_id)
>
> ]
>
> auth.define_tables(... signature=False )
>
> *Otherwise, in answer to your questions...*
> migrate='asset_archive.table'
> Personally, I do not specify the table definition name as the one given
> automatically by web2py is very good. I just use migrate=True/False.
> If you use migrate='asset_archive.table', you are also specifying
> migrate=True
>
> fake_migrate=True
> This is used to recreate the .table file without touching your DB.
> If you manually create and edit the fields in your DB, you may wish to
> recreate the .table definition file (hence fake_migrate).
>
> I am not sure how you are using the archiving etc, but I do know this:
>
> - you can manually create your own DB on-disk structure with whatever
> triggers, indexes etc you require.
> - in web2py you can use define_table to create a web2py meta
> definition (which must be compatible with what is on disk!)
> - you can use fake_migrate if you wish to create a .table definition
> file (which will reflect your web2py define_table)
> - you can use migrate=False to prevent web2py from attempting to
> migrate the physical DB on-disk structure
>
> Using the above, the book explanation should make a little more sense.
>
>
>
>
>
>
>
> On Wednesday, 19 August 2020 09:43:42 UTC+1, Andrea Fae' wrote:
>
>> Hello Villas, unfortunately other problems to this game...
>> I used archiving, and so this type of table definitions
>>
>> db.define_table('asset_archive',
>> Field('current_record', db.asset),
>> db.asset,migrate='asset_archive.table')
>>
>> but I have the same problem regarding FOREIGN KEYS...no way to recreate
>> the tables.
>>
>> Do I have to give up the record archiving? Waht do you think?
>>
>> If you can, please can you explain the meaning of fake_migrate? I never
>> used and in the book is not very well explained.
>>
>> Thank you
>>
>>
>>
>> Il giorno martedì 18 agosto 2020 22:38:50 UTC+2, villas ha scritto:
>>>
>>> I presume therefore that you need those fields. This is the kind of
>>> thing I do to get things working. Please forgive me if I've missed
>>> something.
>>>
>>> - I would create those required fields manually in your database
>>> (without the constraint that causes the problem).
>>> - Set auth.define_tables(migrate = True, fake_migrate=True)
>>> - Run the app. This will create the .table definition file in the
>>> databases dir. You may have had to delete the old .table file. If
>>> necessary delete that.
>>> - Set auth.define_tables(migrate =False) again.
>>>
>>>
>>> Sorry if this seems strange, but it is a work around solution.
>>> I am hoping that someone will eventually fix this contraint problem in
>>> pyDal.
>>>
>>>
>>> On Tuesday, 18 August 2020 20:51:57 UTC+1, Andrea Fae' wrote:
>>>>
>>>> Thank you Villas. I'm using option 2 and now I have the tables without
>>>> "created_by" and "modified_by".
>>>> Now I will try to reset signature=true and see what will happen. If it
>>>> causes the same error I could add but they will not managed automatically
>>>> by web2py, or I'm wrong?
>>>> What do you suggest?
>>>> Thank you for your precious information.
>>>>
>>>> Il giorno lunedì 17 agosto 2020 14:24:25 UTC+2, villas ha scritto:
>>>>>
>>>>> Hi Andrea
>>>>> I was pleased to see you are making progress with the DB connection
>>>>> etc.
>>>>> I can see why mssql does not like the cascade from the same auth_user
>>>>> table. This may be an issue which needs fixing...
>>>>> To get things moving, and this might not be ideal, but I propose you
>>>>> consider either of these options:
>>>>>
>>>>> 1. Create manually your own tables without those contraints. You
>>>>> then set this: auth.define_tables(... migrate=False ) so that
>>>>> pyDal skips creation.
>>>>> 2. Do not include the signature fields with your auth.
>>>>> auth.define_tables(... signature=False )
>>>>>
>>>>> Incidentally, I use option 2 and I therefore do not benefit from the
>>>>> created and modified info, but I could easily remedy this by adding the
>>>>> fields if I wished.
>>>>> Hope this helps.
>>>>>
>>>>>
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/web2py/1400e177-750f-4984-bedf-bbace4f703f6o%40googlegroups.com.
# -*- coding: utf-8 -*-
# -------------------------------------------------------------------------
# This scaffolding model makes your app work on Google App Engine too
# File is released under public domain and you can use without limitations
# -------------------------------------------------------------------------
if request.global_settings.web2py_version < "2.14.1":
raise HTTP(500, "Requires web2py 2.13.3 or newer")
# -------------------------------------------------------------------------
# if SSL/HTTPS is properly configured and you want all HTTP requests to
# be redirected to HTTPS, uncomment the line below:
# -------------------------------------------------------------------------
# request.requires_https()
# -------------------------------------------------------------------------
# app configuration made easy. Look inside private/appconfig.ini
# -------------------------------------------------------------------------
from gluon.contrib.appconfig import AppConfig
# -------------------------------------------------------------------------
# once in production, remove reload=True to gain full speed
# -------------------------------------------------------------------------
myconf = AppConfig(reload=True)
if not request.env.web2py_runtime_gae:
# ---------------------------------------------------------------------
# if NOT running on Google App Engine use SQLite or other DB
# ---------------------------------------------------------------------
import pypyodbc
db = DAL(myconf.get('db.uri'),
pool_size=myconf.get('db.pool_size'),
migrate_enabled=myconf.get('db.migrate'),
check_reserved=['all'])
else:
# ---------------------------------------------------------------------
# connect to Google BigTable (optional 'google:datastore://namespace')
# ---------------------------------------------------------------------
db = DAL('google:datastore+ndb')
# ---------------------------------------------------------------------
# store sessions and tickets there
# ---------------------------------------------------------------------
session.connect(request, response, db=db)
# ---------------------------------------------------------------------
# or store session in Memcache, Redis, etc.
# from gluon.contrib.memdb import MEMDB
# from google.appengine.api.memcache import Client
# session.connect(request, response, db = MEMDB(Client()))
# ---------------------------------------------------------------------
# -------------------------------------------------------------------------
# by default give a view/generic.extension to all actions from localhost
# none otherwise. a pattern can be 'controller/function.extension'
# -------------------------------------------------------------------------
response.generic_patterns = ['*'] if request.is_local else []
# -------------------------------------------------------------------------
# choose a style for forms
# -------------------------------------------------------------------------
response.formstyle = myconf.get('forms.formstyle') # or 'bootstrap3_stacked' or 'bootstrap2' or other
response.form_label_separator = myconf.get('forms.separator') or ''
# -------------------------------------------------------------------------
# (optional) optimize handling of static files
# -------------------------------------------------------------------------
# response.optimize_css = 'concat,minify,inline'
# response.optimize_js = 'concat,minify,inline'
# -------------------------------------------------------------------------
# (optional) static assets folder versioning
# -------------------------------------------------------------------------
# response.static_version = '0.0.0'
# -------------------------------------------------------------------------
# Here is sample code if you need for
# - email capabilities
# - authentication (registration, login, logout, ... )
# - authorization (role based authorization)
# - services (xml, csv, json, xmlrpc, jsonrpc, amf, rss)
# - old style crud actions
# (more options discussed in gluon/tools.py)
# -------------------------------------------------------------------------
from gluon.tools import Auth, Service, PluginManager, Crud
# host names must be a list of allowed host names (glob syntax allowed)
auth = Auth(db, host_names=myconf.get('host.names'))
service = Service()
plugins = PluginManager()
#personalizzazione lunghezza password
#auth.settings.password_min_length = 8
# -------------------------------------------------------------------------
# create all tables needed by auth if not custom tables
# -------------------------------------------------------------------------
# personalizzazione Fae' : username = True per gestire lo username mentre signature = true per gestire i log
auth.settings.extra_fields['auth_cas']= [
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id)
]
auth.settings.extra_fields['auth_event']= [
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id)
]
auth.settings.extra_fields['auth_group']= [
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id)
]
auth.settings.extra_fields['auth_membership']= [
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id)
]
auth.settings.extra_fields['auth_permission']= [
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id)
]
auth.settings.extra_fields['auth_user']= [
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id)
]
auth.define_tables(username=True, signature=False, migrate=False, fake_migrate=True)
#auth.define_tables(username=True, signature=False, migrate='')
# in model db.py after auth.define_tables
# questo passaggio serve per definire la lunghezza minima della password per gli utenti
default_password_validators = [def_val for def_val in db.auth_user.password.requires]
added_password_validators = [IS_LENGTH(minsize=8,error_message=T('Password non valida'))]
password_validators = added_password_validators + default_password_validators
db.auth_user.password.requires = password_validators
#print db.auth_user.password.requires
# -------------------------------------------------------------------------
# configure email
# -------------------------------------------------------------------------
mail = auth.settings.mailer
# personalizzazioni per utilizzo posta elettronica interna
"""mail.settings.server = 'logging' if request.is_local else myconf.get('smtp.server')
mail.settings.sender = myconf.get('smtp.sender')
mail.settings.login = myconf.get('smtp.login')
mail.settings.tls = myconf.get('smtp.tls') or False
mail.settings.ssl = myconf.get('smtp.ssl') or False"""
mail.settings.server = 'smtp.bccsi.bcc.it:25'
mail.settings.sender = 'as...@bccpm.it'
mail.settings.login = None
mail.settings.tls = False
# -------------------------------------------------------------------------
# configure auth policy
# -------------------------------------------------------------------------
auth.settings.registration_requires_verification = False
auth.settings.registration_requires_approval = False
auth.settings.reset_password_requires_verification = True
# -------------------------------------------------------------------------
# Define your tables below (or better in another model file) for example
#
# >>> db.define_table('mytable', Field('myfield', 'string'))
#
# Fields can be 'string','text','password','integer','double','boolean'
# 'date','time','datetime','blob','upload', 'reference TABLENAME'
# There is an implicit 'id integer autoincrement' field
# Consult manual for more options, validators, etc.
#
# More API examples for controllers:
#
# >>> db.mytable.insert(myfield='value')
# >>> rows = db(db.mytable.myfield == 'value').select(db.mytable.ALL)
# >>> for row in rows: print row.id, row.myfield
# -------------------------------------------------------------------------
# -------------------------------------------------------------------------
# after defining tables, uncomment below to enable auditing
# -------------------------------------------------------------------------
# auth.enable_record_versioning(db)
# personalizzazioni Andrea Fae'
# impostazioni autenticazione AD pnbcc
from gluon.contrib.login_methods.ldap_auth import ldap_auth
auth.settings.login_methods.append(ldap_auth(mode='ad',
manage_groups=False,
manage_user=True,
user_firstname_attrib='displayName:1',
user_lastname_attrib='displayName:2',
user_mail_attrib='mail',
db=db,
group_name_attrib='cn',
group_member_attrib='member',
group_filterstr='objectClass=Group',
server='bccsi.net',
logging_level='error',
base_dn='OU=Office365, OU=BCC_202,OU=BCC_Users,DC=bccsi,DC=net'))
# disabilitazione funzioni utenti
auth.settings.actions_disabled.append('register')
auth.settings.actions_disabled.append('retrieve_username')
auth.settings.actions_disabled.append('request_reset_password')
# se l'utente è loggato ed ha registration_id valorizzato allora è un utente di dominio e pertanto non si dà possibilità di cambiare password
#print auth.is_logged_in()
if auth.is_logged_in() and auth.user.registration_id:
auth.settings.actions_disabled.append('change_password')
# -*- coding: utf-8 -*-
crud = Crud(db)
db.define_table('sede',
Field('nome', requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'sede.nome')]),
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id),
singular="Sede",plural="Sedi",migrate='sede.table',
format='%(nome)s')
db.define_table('uo',
Field('nome', requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'uo.nome')]),
Field('sede', 'reference sede'),
Field('telefono'),
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id),
singular="Uo",plural="Uo",migrate='uo.table',
format='%(nome)s')
db.define_table('fornitore',
Field('nome', requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'fornitore.nome')]),
Field('email',requires=[IS_NOT_EMPTY(),IS_EMAIL()]),
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id),
singular="fornitore",plural="fornitori",migrate='fornitore.table',
format='%(nome)s')
# la combinazione tipo-seriale deve essere unica
db.define_table('asset',
Field('tipo',requires=IS_IN_SET(['BA','CD','FW','MO','PC','SC','SE','ST','SW','SL'])),
Field('seriale', requires=IS_NOT_EMPTY()),
Field('nome',requires=IS_NOT_EMPTY()),
Field('uo', 'reference uo'),
Field('dataconsegna', type='date', requires=IS_DATE()),
Field('datadismissione', type='date'),
Field('modello', type='string'),
Field('fornitore', 'reference fornitore'),
Field('comodato', type='boolean', default='False'),
Field('note','text'),
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id),
singular="Asset",plural="Asset",migrate='asset.table',
format='%(tipo)s %(seriale)s')
db.asset.seriale.requires=IS_NOT_IN_DB(db(db.asset.tipo==request.vars.tipo),db.asset.seriale)
db.asset.dataconsegna.label=T("Data consegna")
db.asset.datadismissione.label=T("Data dismissione")
db.define_table('ticket',
Field('asset', 'reference asset'),
Field('fisso', 'boolean'),
Field('anomalia', type='string', requires=[IS_NOT_EMPTY(),IS_LENGTH(100)], widget=SQLFORM.widgets.text.widget),
Field('chiuso', 'boolean'),
Field('risoluzione', type='string', requires=[IS_LENGTH(100)], widget=SQLFORM.widgets.text.widget, default='In attesa'),
Field('is_active', 'boolean', default=True),
Field('created_on', 'datetime', default=request.now),
Field('created_by', 'integer', default=auth.user_id),
Field('modified_on', 'datetime', update=request.now),
Field('modified_by', 'integer', update=auth.user_id),
singular="Ticket", plural="Ticket",migrate='ticket.table',
format='%(asset)s')
"""db.define_table('sede_archive',
Field('current_record', db.sede),
db.sede,migrate='sede_archive.table')
db.define_table('uo_archive',
Field('current_record', db.uo),
db.uo,migrate='uo_archive.table')
db.define_table('asset_archive',
Field('current_record', db.asset),
db.asset,migrate='asset_archive.table')
db.define_table('ticket_archive',
Field('current_record', db.ticket),
db.ticket,migrate='ticket_archive.table')
db.define_table('fornitore_archive',
Field('current_record', db.fornitore),
db.fornitore,migrate='fornitore_archive.table')"""
# servita per settare tutto i campi comodato a false
"""rows = db(db.asset).select()
for row in rows:
row.update_record(comodato='False')"""
# serivta per cancellare tutti i monitor dal DB
#db(db.asset.tipo=='MO').delete()
#db(db.asset.tipo==None).delete()
# servita per settare il fornitore assistenza per scanner
"""query = db.asset.tipo == 'SC'
rows = db(query).select()
for row in rows:
row.update_record(fornitore=4)"""
timestamp: 2020-08-20T16:37:57.805000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:38:58.424000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:39:58.421000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:40:58.414000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:41:58.433000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:42:58.415000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:43:58.423000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
timestamp: 2020-08-20T16:44:58.435000
CREATE TABLE sede(
id INT IDENTITY PRIMARY KEY,
nome VARCHAR(512) NULL,
is_active BIT NULL,
created_on DATETIME NULL,
created_by INT NULL,
modified_on DATETIME NULL,
modified_by INT NULL
);
