def sqlschema(schema,
driver,
text_index=True,
user=None,
set_owner=False,
skip_relations=PURE_VIRTUAL_RTYPES,
skip_entities=()):
"""Return the database SQL schema as a list of SQL statements, according to the given parameters.
"""
from cubicweb.server.schema2sql import schema2sql
from cubicweb.server.sources import native
if set_owner:
assert user, 'user is argument required when set_owner is true'
stmts = list(native.sql_schema(driver))
dbhelper = db.get_db_helper(driver)
if text_index:
stmts += dbhelper.sql_init_fti().split(';') # XXX
stmts += schema2sql(dbhelper,
schema,
prefix=SQL_PREFIX,
skip_entities=skip_entities,
skip_relations=skip_relations)
if dbhelper.users_support and user:
stmts += sqlgrants(schema, driver, user, text_index, set_owner,
skip_relations, skip_entities)
return stmts
def setUp(self):
self.cnx = MockConnection(())
try:
self.helper = get_db_helper('mysql')
except ImportError as exc:
raise unittest.SkipTest(str(exc))
self.helper._cnx = self.cnx
def tearDownModule(*args):
global schema, config
schema = config = None
unregister_base_type('BabarTestType')
helper = get_db_helper('sqlite')
helper.TYPE_MAPPING.pop('BabarTestType', None)
helper.TYPE_CONVERTERS.pop('BabarTestType', None)
def source_cnx(source, dbname=None, special_privs=False, interactive=True):
"""open and return a connection to the system database defined in the
given server.serverconfig
"""
from getpass import getpass
dbhost = source.get('db-host')
if dbname is None:
dbname = source['db-name']
driver = source['db-driver']
dbhelper = get_db_helper(driver)
if interactive:
print('-> connecting to %s database' % driver, end=' ')
if dbhost:
print('%s@%s' % (dbname, dbhost), end=' ')
else:
print(dbname, end=' ')
if dbhelper.users_support:
if not interactive or (not special_privs and source.get('db-user')):
user = source.get('db-user', os.environ.get('USER', ''))
if interactive:
print('as', user)
password = source.get('db-password')
else:
print()
if special_privs:
print('WARNING')
print('the user will need the following special access rights '
'on the database:')
print(special_privs)
print()
default_user = source.get('db-user', os.environ.get('USER', ''))
user = input('Connect as user ? [%r]: ' % default_user)
user = user.strip() or default_user
if user == source.get('db-user'):
password = source.get('db-password')
else:
password = getpass('password: '******'db-extra-arguments')
extra = extra_args and {'extra_args': extra_args} or {}
cnx = get_connection(driver,
dbhost,
dbname,
user,
password=password,
port=source.get('db-port'),
schema=source.get('db-namespace'),
**extra)
try:
cnx.logged_user = user
except AttributeError:
# C object, __slots__
from logilab.database import _SimpleConnectionWrapper
cnx = _SimpleConnectionWrapper(cnx)
cnx.logged_user = user
return cnx
def setUpModule(*args):
register_base_type('BabarTestType', ('jungle_speed',))
helper = get_db_helper('sqlite')
helper.TYPE_MAPPING['BabarTestType'] = 'TEXT'
helper.TYPE_CONVERTERS['BabarTestType'] = lambda x: '"%s"' % x
global schema, config
loader = CubicWebSchemaLoader()
config = devtools.TestServerConfiguration('data-schemaserial', __file__)
config.bootstrap_cubes()
schema = loader.load(config)
def run(self, args):
"""run the command with its specific arguments"""
from cubicweb.server.utils import crypt_password, manager_userpasswd
appid = args[0]
config = ServerConfiguration.config_for(appid)
sourcescfg = config.read_sources_file()
try:
adminlogin = sourcescfg['admin']['login']
except KeyError:
print('-> Error: could not get cubicweb administrator login.')
sys.exit(1)
cnx = source_cnx(sourcescfg['system'])
driver = sourcescfg['system']['db-driver']
dbhelper = get_db_helper(driver)
cursor = cnx.cursor()
# check admin exists
cursor.execute("SELECT * FROM cw_CWUser WHERE cw_login=%(l)s",
{'l': adminlogin})
if not cursor.fetchall():
print("-> error: admin user %r specified in sources doesn't exist "
"in the database" % adminlogin)
print(" fix your sources file before running this command")
cnx.close()
sys.exit(1)
if self.config.password is None:
# ask for a new password
msg = 'new password for %s' % adminlogin
_, pwd = manager_userpasswd(adminlogin,
confirm=True,
passwdmsg=msg)
else:
pwd = self.config.password
try:
cursor.execute(
"UPDATE cw_CWUser SET cw_upassword=%(p)s WHERE cw_login=%(l)s",
{
'p': dbhelper.binary_value(crypt_password(pwd)),
'l': adminlogin
})
sconfig = Configuration(options=USER_OPTIONS)
sconfig['login'] = adminlogin
sconfig['password'] = pwd
sourcescfg['admin'] = sconfig
config.write_sources_file(sourcescfg)
except Exception as ex:
cnx.rollback()
import traceback
traceback.print_exc()
print('-> an error occurred:', ex)
else:
cnx.commit()
print('-> password reset, sources file regenerated.')
cnx.close()
def __init__(self, source_config, repairing=False):
try:
self.dbdriver = source_config['db-driver'].lower()
dbname = source_config['db-name']
except KeyError as e:
raise ConfigurationError(
'missing some expected entries in sources file (do you have '
'a db-driver and a db-name keys?), error: %s' % e)
dbhost = source_config.get('db-host')
port = source_config.get('db-port')
dbport = port and int(port) or None
dbuser = source_config.get('db-user')
dbpassword = source_config.get('db-password')
dbencoding = source_config.get('db-encoding', 'UTF-8')
dbextraargs = source_config.get('db-extra-arguments')
dbnamespace = source_config.get('db-namespace')
self.dbhelper = logilab_database.get_db_helper(self.dbdriver)
self.dbhelper.record_connection_info(dbname, dbhost, dbport, dbuser,
dbpassword, dbextraargs,
dbencoding, dbnamespace)
self.sqlgen = SQLGenerator()
# copy back some commonly accessed attributes
dbapi_module = self.dbhelper.dbapi_module
self.OperationalError = dbapi_module.OperationalError
self.InterfaceError = dbapi_module.InterfaceError
self.DbapiError = dbapi_module.Error
self._binary = self.dbhelper.binary_value
self._process_value = dbapi_module.process_value
self._dbencoding = dbencoding
if self.dbdriver == 'sqlite':
self.cnx_wrap = SqliteConnectionWrapper
self.dbhelper.dbname = abspath(self.dbhelper.dbname)
else:
self.cnx_wrap = ConnectionWrapper
if not repairing:
statement_timeout = int(
source_config.get('db-statement-timeout', 0))
if statement_timeout > 0:
def set_postgres_timeout(cnx):
cnx.cursor().execute('SET statement_timeout to %d' %
statement_timeout)
cnx.commit()
postgres_hooks = SQL_CONNECT_HOOKS['postgres']
postgres_hooks.append(set_postgres_timeout)
def _cleanup_steps(self, source):
# 1/ delete namespace if used
db_namespace = source.get('db-namespace')
if db_namespace:
yield ('Delete database namespace "%s"' % db_namespace,
self._drop_namespace, True)
# 2/ delete database
yield ('Delete database "%(db-name)s"' % source, self._drop_database,
True)
# 3/ delete user
helper = get_db_helper(source['db-driver'])
if source['db-user'] and helper.users_support:
# XXX should check we are not connected as user
yield ('Delete user "%(db-user)s"' % source, self._drop_user,
False)
def system_source_cnx(source,
dbms_system_base=False,
special_privs='CREATE/DROP DATABASE',
interactive=True):
"""shortcut to get a connextion to the instance system database
defined in the given config. If <dbms_system_base> is True,
connect to the dbms system database instead (for task such as
create/drop the instance database)
"""
if dbms_system_base:
system_db = get_db_helper(source['db-driver']).system_database()
return source_cnx(source,
system_db,
special_privs=special_privs,
interactive=interactive)
return source_cnx(source,
special_privs=special_privs,
interactive=interactive)
def sql_drop_all_user_tables(driver_or_helper, sqlcursor):
"""Return ths sql to drop all tables found in the database system."""
if not getattr(driver_or_helper, 'list_tables', None):
dbhelper = db.get_db_helper(driver_or_helper)
else:
dbhelper = driver_or_helper
stmts = [dbhelper.sql_drop_sequence('entities_id_seq')]
# for mssql, we need to drop views before tables
if hasattr(dbhelper, 'list_views'):
stmts += [
'DROP VIEW %s;' % name
for name in filter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION,
dbhelper.list_views(sqlcursor))
]
stmts += [
'DROP TABLE %s;' % name
for name in filter(_SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION,
dbhelper.list_tables(sqlcursor))
]
return stmts
def sqlgrants(schema,
driver,
user,
text_index=True,
set_owner=True,
skip_relations=(),
skip_entities=()):
"""Return a list of SQL statements to give all access privileges to the given user on the
database.
"""
from cubicweb.server.schema2sql import grant_schema
from cubicweb.server.sources import native
stmts = list(native.grant_schema(user, set_owner))
if text_index:
dbhelper = db.get_db_helper(driver)
# XXX should return a list of sql statements rather than ';' joined statements
stmts += dbhelper.sql_grant_user_on_fti(user).split(';')
stmts += grant_schema(schema,
user,
set_owner,
skip_entities=skip_entities,
prefix=SQL_PREFIX)
return stmts
def setUpClass(cls):
register_base_type('BabarTestType', ('jungle_speed', ))
helper = get_db_helper('sqlite')
helper.TYPE_MAPPING['BabarTestType'] = 'TEXT'
helper.TYPE_CONVERTERS['BabarTestType'] = lambda x: '"%s"' % x
super(SchemaDeserialTC, cls).setUpClass()
from yams import register_base_type
from logilab.database import get_db_helper
from logilab.database.sqlgen import SQLExpression
_NUMERIC_PARAMETERS = {'scale': 0, 'precision': None}
register_base_type('Numeric', _NUMERIC_PARAMETERS)
# Add the datatype to the helper mapping
pghelper = get_db_helper('postgres')
def pg_numeric_sqltype(rdef):
"""Return a PostgreSQL column type corresponding to rdef
"""
return 'numeric(%s, %s)' % (rdef.precision, rdef.scale)
pghelper.TYPE_MAPPING['Numeric'] = pg_numeric_sqltype
def helper(self):
from logilab.database import get_db_helper
return get_db_helper('postgres')
def setUp(self):
self.helper = get_db_helper('sqlserver2005')
self.cnx = MockConnection(())
self.helper._cnx = self.cnx
def run(self, args):
"""run the command with its specific arguments"""
check_options_consistency(self.config)
automatic = self.get('automatic')
appid = args.pop()
config = ServerConfiguration.config_for(appid)
source = config.system_source_config
dbname = source['db-name']
driver = source['db-driver']
helper = get_db_helper(driver)
if driver == 'sqlite':
if os.path.exists(dbname) and (automatic or ASK.confirm(
'Database %s already exists. Drop it?' % dbname)):
os.unlink(dbname)
elif self.config.create_db:
print('\n' + underline_title('Creating the system database'))
# connect on the dbms system base to create our base
dbcnx = _db_sys_cnx(source,
'CREATE/DROP DATABASE and / or USER',
interactive=not automatic)
cursor = dbcnx.cursor()
try:
if helper.users_support:
user = source['db-user']
if not helper.user_exists(cursor, user) and (
automatic
or ASK.confirm('Create db user %s ?' % user,
default_is_yes=False)):
helper.create_user(source['db-user'],
source.get('db-password'))
print('-> user %s created.' % user)
if dbname in helper.list_databases(cursor):
if automatic or ASK.confirm(
'Database %s already exists -- '
'do you want to drop it ?' % dbname):
cursor.execute('DROP DATABASE "%s"' % dbname)
else:
print('you may want to run "cubicweb-ctl db-init '
'--drop %s" manually to continue.' %
config.appid)
return
createdb(helper, source, dbcnx, cursor)
dbcnx.commit()
print('-> database %s created.' % dbname)
except BaseException:
dbcnx.rollback()
raise
cnx = system_source_cnx(source,
special_privs='CREATE LANGUAGE/SCHEMA',
interactive=not automatic)
cursor = cnx.cursor()
helper.init_fti_extensions(cursor)
namespace = source.get('db-namespace')
if namespace and ASK.confirm('Create schema %s in database %s ?' %
(namespace, dbname)):
helper.create_schema(cursor, namespace)
cnx.commit()
# postgres specific stuff
if driver == 'postgres':
# install plpythonu/plpgsql languages
langs = ('plpythonu', 'plpgsql')
for extlang in langs:
if automatic or ASK.confirm('Create language %s ?' % extlang):
try:
helper.create_language(cursor, extlang)
except Exception as exc:
print('-> ERROR:', exc)
print('-> could not create language %s, '
'some stored procedures might be unusable' %
extlang)
cnx.rollback()
else:
cnx.commit()
print(
'-> database for instance %s created and necessary extensions installed.'
% appid)
print()
if automatic:
CWCTL.run([
'db-init', '--automatic', '--config-level', '0', config.appid
])
elif ASK.confirm('Run db-init to initialize the system database ?'):
CWCTL.run([
'db-init', '--config-level',
str(self.config.config_level), config.appid
])
else:
print('-> nevermind, you can do it later with '
'"cubicweb-ctl db-init %s".' % config.appid)
def _drop_namespace(self, source):
db_namespace = source.get('db-namespace')
with db_transaction(source, privilege='DROP SCHEMA') as cursor:
helper = get_db_helper(source['db-driver'])
helper.drop_schema(cursor, db_namespace)
print('-> database schema %s dropped' % db_namespace)
def init_repository(config,
interactive=True,
drop=False,
vreg=None,
init_config=None):
"""Initialise a repository database by creating tables and filling them
with the minimal set of entities (ie at least the schema, base groups and
a initial user)
"""
from cubicweb.repoapi import get_repository, connect
from cubicweb.server.repository import Repository
from cubicweb.server.utils import manager_userpasswd
from cubicweb.server.sqlutils import sqlexec, sqlschema, sql_drop_all_user_tables
from cubicweb.server.sqlutils import _SQL_DROP_ALL_USER_TABLES_FILTER_FUNCTION as drop_filter
# configuration to avoid db schema loading and user'state checking
# on connection
config.creating = True
config.consider_user_state = False
config.cubicweb_appobject_path = set(('hooks', 'entities'))
config.cube_appobject_path = set(('hooks', 'entities'))
# only enable the system source at initialization time
repo = Repository(config, vreg=vreg)
repo.bootstrap()
if init_config is not None:
# further config initialization once it has been bootstrapped
init_config(config)
schema = repo.schema
sourcescfg = config.read_sources_file()
source = sourcescfg['system']
driver = source['db-driver']
with repo.internal_cnx() as cnx:
sqlcnx = cnx.cnxset.cnx
sqlcursor = cnx.cnxset.cu
execute = sqlcursor.execute
if drop:
helper = database.get_db_helper(driver)
dropsql = sql_drop_all_user_tables(helper, sqlcursor)
# We may fail dropping some tables because of table dependencies, in a first pass.
# So, we try a second drop sequence to drop remaining tables if needed.
# Note that 2 passes is an arbitrary choice as it seems enough for our usecases
# (looping may induce infinite recursion when user have no rights for example).
# Here we try to keep code simple and backend independent. That's why we don't try to
# distinguish remaining tables (missing privileges, dependencies, ...).
failed = sqlexec(dropsql,
execute,
cnx=sqlcnx,
pbtitle='-> dropping tables (first pass)')
if failed:
failed = sqlexec(failed,
execute,
cnx=sqlcnx,
pbtitle='-> dropping tables (second pass)')
remainings = list(
filter(drop_filter, helper.list_tables(sqlcursor)))
assert not remainings, 'Remaining tables: %s' % ', '.join(
remainings)
handler = config.migration_handler(schema,
interactive=False,
repo=repo,
cnx=cnx)
# install additional driver specific sql files
handler.cmd_install_custom_sql_scripts()
for cube in reversed(config.cubes()):
handler.cmd_install_custom_sql_scripts(cube)
_title = '-> creating tables '
print(_title, end=' ')
# schema entities and relations tables
# can't skip entities table even if system source doesn't support them,
# they are used sometimes by generated sql. Keeping them empty is much
# simpler than fixing this...
schemasql = sqlschema(schema, driver)
failed = sqlexec(schemasql, execute, pbtitle=_title)
if failed:
print(
'The following SQL statements failed. You should check your schema.'
)
print(failed)
raise Exception(
'execution of the sql schema failed, you should check your schema'
)
sqlcursor.close()
sqlcnx.commit()
with repo.internal_cnx() as cnx:
# insert entity representing the system source
ssource = cnx.create_entity('CWSource', type=u'native', name=u'system')
repo.system_source.eid = ssource.eid
cnx.execute('SET X cw_source X WHERE X eid %(x)s', {'x': ssource.eid})
# insert base groups and default admin
print('-> inserting default user and default groups.')
try:
login = sourcescfg['admin']['login']
pwd = sourcescfg['admin']['password']
except KeyError:
if interactive:
msg = 'enter login and password of the initial manager account'
login, pwd = manager_userpasswd(msg=msg, confirm=True)
else:
login, pwd = source['db-user'], source['db-password']
# sort for eid predicatability as expected in some server tests
for group in sorted(BASE_GROUPS):
cnx.create_entity('CWGroup', name=group)
admin = create_user(cnx, login, pwd, u'managers')
cnx.execute(
'SET X owned_by U WHERE X is IN (CWGroup,CWSource), U eid %(u)s',
{'u': admin.eid})
cnx.commit()
repo.shutdown()
# re-login using the admin user
config._cubes = None # avoid assertion error
repo = get_repository(config=config)
# replace previous schema by the new repo's one. This is necessary so that we give the proper
# schema to `initialize_schema` above since it will initialize .eid attribute of schema elements
schema = repo.schema
with connect(repo, login, password=pwd) as cnx:
with cnx.security_enabled(False, False):
repo.system_source.eid = ssource.eid # redo this manually
handler = config.migration_handler(schema,
interactive=False,
cnx=cnx,
repo=repo)
# serialize the schema
initialize_schema(config, schema, handler)
# yoo !
cnx.commit()
repo.system_source.init_creating()
cnx.commit()
repo.shutdown()
# restore initial configuration
config.creating = False
config.consider_user_state = True
# (drop instance attribute to get back to class attribute)
del config.cubicweb_appobject_path
del config.cube_appobject_path
print('-> database for instance %s initialized.' % config.appid)
def tearDownClass(cls):
unregister_base_type('BabarTestType')
helper = get_db_helper('sqlite')
helper.TYPE_MAPPING.pop('BabarTestType', None)
helper.TYPE_CONVERTERS.pop('BabarTestType', None)
super(SchemaDeserialTC, cls).tearDownClass()
def setUp(self):
self.cnx = MockConnection(())
self.helper = get_db_helper('sqlite')
class FakeSource(object):
dbhelper = get_db_helper('sqlite')
def __init__(self, uri):
self.uri = uri
def setUp(self):
self.helper = get_db_helper('postgres')
self.cnx = MockConnection(())
self.helper._cnx = self.cnx
