def test(database,
user='******',
password='******',
host='127.0.0.1',
charset='UTF8',
port=3050,
pagesize=16384):
dbmod.create_database(
"create database '%(database)s' page_size %(pagesize)s user '%(user)s' "
"password '%(password)s' DEFAULT CHARACTER SET %(charset)s" % {
'user': user,
'password': password,
'host': host,
'database': database,
'charset': charset,
'port': port,
'pagesize': pagesize
})
create_table = '''
Create Table table1 (
ID Integer,
sort Integer NOT Null
);
'''
create_trigger = '''CREATE TRIGGER BIU_Trigger FOR table1
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
if (new.sort IS NULL) then
begin
new.sort = 1;
end
end
'''
conn = dbmod.connect(host=host,
database=database,
user=user,
password=password)
cur = conn.cursor()
cur.execute(create_table)
cur.execute(create_trigger)
conn.commit()
# fails with fdb, passes with kinterbasdb
cur.execute('insert into table1 (ID, sort) values(1, ?)', (None, ))
def _create_database(self, test_database_name, verbosity):
self._check_active_connection(verbosity)
params = self._get_creation_params(database=test_database_name)
connection = Database.create_database(**params)
connection.execute_immediate("CREATE EXCEPTION teste '';")
connection.commit()
connection.close()
def main():
con = fdb.create_database(dsn='U:/DiabloDataBase/diablodb.fdb',
user='******')
cur = con.cursor()
for key in Tables:
cur.execute("CREATE TABLE {}".format(Tables[key]))
con.commit()
def _createDb(self):
scriptFile = Path(self._dbScriptPath)
script = ''
with scriptFile.open() as f:
script = f.read()
script = script.replace(_DB_NAME_PLACEHOLDER, self._dbPath).split('@')
for operation in script:
if 'CREATE DATABASE' in operation.upper():
fdb.create_database(sql=operation)
else:
if not self._connected():
self._connect()
self._connection.cursor().execute(operation)
if self._connected():
if self._connection.main_transaction.active:
self._connection.commit()
self._disconnect()
def _create_database(self, test_database_name, verbosity):
self._check_active_connection(verbosity)
params = self._get_connection_params(database=test_database_name)
connection = Database.create_database("""
CREATE DATABASE '%(database)s'
USER '%(user)s'
PASSWORD '%(password)s'
DEFAULT CHARACTER SET %(charset)s;""" % params)
connection.execute_immediate("CREATE EXCEPTION teste '';")
connection.commit()
connection.close()
def _create_database(self, test_database_name, verbosity):
self._check_active_connection(verbosity)
params = self._get_connection_params(database=test_database_name)
connection = Database.create_database("""
CREATE DATABASE '%(database)s'
USER '%(user)s'
PASSWORD '%(password)s'
DEFAULT CHARACTER SET %(charset)s;""" % params)
connection.execute_immediate("CREATE EXCEPTION teste '';")
connection.commit()
connection.close()
def test(database,
user='******',
password='******',
host='127.0.0.1',
charset='UTF8',
port=3050,
pagesize=16384):
dbmod.create_database(
"create database '%(database)s' page_size %(pagesize)s user '%(user)s' "
"password '%(password)s' DEFAULT CHARACTER SET %(charset)s" % {
'user': user,
'password': password,
'host': host,
'database': database,
'charset': charset,
'port': port,
'pagesize': pagesize
})
create_table = '''
Create Table table1 (
ID Integer,
d1 Date
);
'''
conn = dbmod.connect(host=host,
database=database,
user=user,
password=password)
cur = conn.cursor()
cur.execute(create_table)
conn.commit()
# fails with fdb, passes with kinterbasdb
cur.execute('insert into table1 (ID, d1) values(1, ?)', ('2010-01-01', ))
def db_create():
if request.method == 'GET':
_reg = {
'db_alias': '',
'db_server': '',
'db_path': '',
'db_user': '',
'db_pass': '',
'db_role': '',
'dialect': '',
'charset': ''
}
return render(tpl='db_register', reg=_reg, ftyp='create')
else:
prms = request.POST
if prms['db_path'] in ['BASE_PATH', '', '.', './']:
prms['db_path'] = appconf.basepath
sql = """
create database
'{db_server}:{db_path}/{db_name}'
user '{db_user}'
password '{db_pass}'
page_size {db_page_size}
DEFAULT CHARACTER SET {charset}""".format(**prms)
appconf.con[prms.db_alias] = fdb.create_database(
sql=sql,
connection_class=fdb.ConnectionWithSchema,
)
register_ddl(prms.db_alias)
appconf.db_config[prms.db_alias] = {
'db_server': prms.db_server,
'db_path': prms.db_path + '/' + prms.db_name,
'db_user': prms.db_user,
'db_pass': prms.db_pass,
'db_role': prms.db_role,
'dialect': 3, #todo! prms.dialect,
'charset': prms.charset
}
with open('%s/dbconfig.ini' % appconf.basepath, 'w+', encoding='utf-8') as f:
appconf.db_config.write(f)
redirect('/db/list')
def check_database():
try:
connect = fdb.connect(host=HOST,
port=int(PORT),
database=os.path.join(PATH, DATABASE),
user=USER,
password=PASSWORD)
print('Connected in %s.' % (DATABASE))
except:
connect = fdb.create_database(host=HOST,
port=int(PORT),
database=os.path.join(PATH, DATABASE),
user=USER,
password=PASSWORD)
print('Create database %s.' % (DATABASE))
return connect
def __init__(self, fnam, create=False):
"""Return a jupiter object with file connection to *fnam*
it assumes that any string that goes into the connection is utf8 formatted"""
self.fnam = fnam
if os.path.exists(fnam):
self.con = fdb.connect(dsn=fnam,
user='******',
password='******',
charset='utf8')
else:
if create:
if os.path.exists(fnam):
raise ('File: {} already exists!'.format(fnam))
print('Creating database: {}'.format(fnam))
self.con = fdb.create_database(
"create database '{}' user 'sysdba' password 'masterkey'".
format(fnam))
else:
raise ('File: {} doent exist!'.format(fnam))
self.cur = self.con.cursor()
def export(self, **options):
'''
Exports the data into a Firebird Embedded file-like stream.
Arguments:
options (dict): The exporting options
Returns:
io.BytesIO: A Firebird Embedded file-like stream
Raises:
ExportError: When data fails to export
'''
sql_options = dict(options, dialect='firebird')
sql_data = SqlExporter(self._data).export(**sql_options)
fdb_data = io.BytesIO()
fdb_file = tempfile.mktemp()
fdb_con = fdb.create_database(
"CREATE DATABASE '{}' USER '{}' PASSWORD '{}'" \
.format(fdb_file, 'sysdba', 'masterkey'),
sql_dialect=3
)
fdb_cursor = fdb_con.cursor()
in_trans = False
for stmt in sql_data.read().rstrip(';').split(';'):
if stmt.startswith('INSERT') and not in_trans:
fdb_con.begin()
in_trans = True
else:
fdb_con.commit()
in_trans = False
fdb_cursor.execute(stmt)
fdb_data.write(File(fdb_file).readBytes())
fdb_data.seek(0)
return fdb_data
def encode(self, data: dict, **options) -> BinaryFileStream:
"""
Encodes the data into a Firebird Embedded file-like stream.
Arguments:
data: The data to encode
**options: The encoding options
Returns:
A Firebird Embedded file-like stream
Raises:
geodatabr.encoders.EncodeError: If data fails to encode
"""
try:
sql_data = super().encode(data, **dict(self.options, **options))
fdb_file = tempfile.mktemp()
fdb_con = fdb.create_database(
"CREATE DATABASE '{}' USER 'sysdba' PASSWORD 'masterkey'".
format(fdb_file),
sql_dialect=3)
fdb_cursor = fdb_con.cursor()
in_trans = False
for stmt in sql_data.read().rstrip(';').split(';'):
if stmt.startswith('INSERT') and not in_trans:
fdb_con.begin()
in_trans = True
else:
fdb_con.commit()
in_trans = False
fdb_cursor.execute(stmt)
return BinaryFileStream(File(fdb_file).readBytes())
except Exception:
raise EncodeError
import fdb
import Initialize
InitialParameters=Initialize.getParam()
DBLocation=InitialParameters[1]
DBName=InitialParameters[6]
UserName=InitialParameters[7]
Password=InitialParameters[8]
DBPath=DBLocation+DBName
CreateComment="create database '"+str(DBPath)+"' user '"+str(UserName)+"' password '"+str(Password)+"'"
print('DatabasePath :',DBPath)
con = fdb.create_database(CreateComment)
print("Database (",DBName,") created Sucessfully .")
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
cur.execute("recreate table consumercomplaint (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)")
con.commit()
print("Table ConsumerComplaint created Sucessfully .")
cur.execute("create unique index unique_ComplaintID on consumercomplaint(ComplaintID)")
con.commit()
print("Uniqie Index on ConsumerComplaint(ComplaintID)created Sucessfully .")
cur.execute("recreate table consumercomplaint_staging (DateReceived varchar(100),Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(50),ZIPCode varchar(100),SubmittedVia varchar(100),DateSentCompany varchar(100),CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(10),ConsumerDisputedSts varchar(10),ComplaintID int)")
con.commit()
print("Table consumercomplaint_staging created Sucessfully .")
cur.execute("recreate view ConsumerComplaintView as select DateReceived,Product,SubProduct,Issue,SubIssue,ConsumerComplaint,CompanyPublicResponse,Company,State,ZIPCode,SubmittedVia,DateSentCompany,CompanyResponseConsumer,TimelyResponseSts,ConsumerDisputedSts,ComplaintID from consumercomplaint")
con.commit()
print("View ConsumerComplaintView created Sucessfully .")
cur.execute("recreate table consumercomplaint_copy (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)")
con.commit()
def prepare_db():
try:
conn = fdb.connect(dsn=database,
user='******',
password='******',
fb_library_name=fbClient)
conn.drop_database()
except Exception as e:
print(e)
conn = fdb.create_database("create database '" + database +
"' user 'sysdba' password 'masterkey'",
fb_library_name=fbClient)
cursor = conn.cursor()
metadata = ("create table t (i int, v int)",
"create table log (i int, tn int, sn int, o int, n int)",
"insert into t values (1,0)", "insert into t values (2,0)",
"insert into t values (3,0)", "insert into t values (4,0)",
"insert into t values (5,0)", '''
create trigger bu_t for t before update
as
declare cn integer;
declare tn integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
in autonomous transaction do
insert into log values(old.i, :tn, :cn, old.v, new.v);
end
''', '''
create trigger bd_t for t before delete
as
declare cn integer;
declare tn integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
in autonomous transaction do
insert into log values(old.i, :tn, :cn, old.v, null);
end
''', '''
create procedure p_upd ()
as
declare cn integer;
declare tn integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
in autonomous transaction do
insert into log values(null, -:tn, :cn, null, null);
update t set v=1 where v=-1 or i in (2, 3);
end
''', '''
create procedure p_s returns (v int)
as
declare cn integer;
declare tn integer;
declare i integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
for select i, v from t where v=-1 or i in (2, 3) into :i, :v do
begin
in autonomous transaction do
insert into log values(:i, -:tn, :cn, :v, null);
suspend;
end
end
''', '''
create procedure p_slock returns (v int)
as
declare cn integer;
declare tn integer;
declare i integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
for select i, v from t where v=-1 or i in (2, 3) with lock into :i, :v do
begin
in autonomous transaction do
insert into log values(:i, -:tn, :cn, :v, null);
suspend;
end
end
''', '''
create procedure p_nos
as
declare cn integer;
declare tn integer;
declare i integer;
declare v integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
for select i, v from t where v=-1 or i in (2, 3) with lock into :i, :v do
begin
in autonomous transaction do
insert into log values(:i, -:tn, :cn, :v, null);
end
end
''', '''
create procedure p_cur
as
begin
for select * from t where v=-1 or i in (2, 3) as cursor cur do
begin
update t set v=1 where current of cur;
end
end
''', 'create view vt as select * from t',
'create view trig_v as select distinct * from t where i>0', '''
create trigger bu_trig_v for trig_v before update
as
declare cn integer;
declare tn integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
in autonomous transaction do
insert into log values(old.i, -:tn, :cn, old.v, new.v);
update t set v=new.v where i=old.i;
end
''', '''
create trigger bd_trig_v for trig_v before delete
as
declare cn integer;
declare tn integer;
begin
cn = null; --rdb$get_context('SYSTEM', 'SNAPSHOT_NUMBER');
tn = CURRENT_TRANSACTION;
in autonomous transaction do
insert into log values(old.i, -:tn, :cn, old.v, null);
delete from t where i=old.i;
end
''')
for q in metadata:
cursor.execute(q)
conn.commit()
import fdb
import Initialize
InitialParameters = Initialize.getParam()
DBLocation = InitialParameters[1]
DBName = InitialParameters[6]
UserName = InitialParameters[7]
Password = InitialParameters[8]
DBPath = DBLocation + DBName
CreateComment = "create database '" + str(DBPath) + "' user '" + str(
UserName) + "' password '" + str(Password) + "'"
print('DatabasePath :', DBPath)
con = fdb.create_database(CreateComment)
print("Database (", DBName, ") created Sucessfully .")
con = fdb.connect(database=DBPath, user=UserName, password=Password)
cur = con.cursor()
cur.execute(
"recreate table consumercomplaint (DateReceived date,Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(5),ZIPCode varchar(10),SubmittedVia varchar(20),DateSentCompany date,CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(5),ConsumerDisputedSts varchar(5),ComplaintID int)"
)
con.commit()
print("Table ConsumerComplaint created Sucessfully .")
cur.execute(
"create unique index unique_ComplaintID on consumercomplaint(ComplaintID)")
con.commit()
print("Uniqie Index on ConsumerComplaint(ComplaintID)created Sucessfully .")
cur.execute(
"recreate table consumercomplaint_staging (DateReceived varchar(100),Product varchar(100),SubProduct varchar(100),Issue varchar(100),SubIssue varchar(100),ConsumerComplaint varchar(5000),CompanyPublicResponse varchar(5000),Company varchar(100),State varchar(50),ZIPCode varchar(100),SubmittedVia varchar(100),DateSentCompany varchar(100),CompanyResponseConsumer varchar(100),TimelyResponseSts varchar(10),ConsumerDisputedSts varchar(10),ComplaintID int)"
)
con.commit()
full_path = os.getcwd() + os.sep + fb_path
sql_select_all = "select * from CLIENTS"
# FIELD LENGHT makes error -maybe because there is no entries in db
#sql_select_struct = "SELECT RDB$FIELD_NAME, RDB$DESCRIPTION, RDB$DEFAULT_VALUE, RDB$NULL_FLAG, RDB$FIELD_LENGTH FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME='CLIENTS';"
sql_select_struct = "SELECT RDB$FIELD_NAME, RDB$DESCRIPTION, RDB$DEFAULT_VALUE, RDB$NULL_FLAG FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME='CLIENTS';"
sql_create_db = "create database '%s' user 'sysdba' password 'masterkey'" % fb_path
sql_create_table_1 = "create table CLIENTS (ID_client INTEGER NOT NULL, COMPANY VARCHAR(20), NAME VARCHAR(20))"
sql_create_table_2 = "create table ITEMS (ID_item INTEGER NOT NULL, NAME VARCHAR(50), PRIZE INTEGER NOT NULL, CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"
# full_path is not seen because script is run as dos user - only connection to db is done as firebird user
#if os.path.exists(full_path):
con = None
cur = None
try:
con = fdb.create_database(sql_create_db)
#con = fdb.connect(dsn=fb_path, user='******', password='******')
cur = con.cursor()
cur.execute(sql_create_table_1)
con.commit() # commit is a part of table creation, without it, table will not be created
#cur.execute(sql_create_table_2)
#con.commit() # commit is a part of table creation, without it, table will not be created
data = cur.execute(sql_select_struct)
print data.fetchall()
except fdb.DatabaseError as e:
print "[0] %s" % str(e.args[0])
print "[1] %s" % str(e.args[1])
print "[2] %s" % str(e.args[2])
