def down():
database_name = 'student_source'
with SqlServer(auto_commit=True) as server:
q = wk(server)
s = fc(server)
if server.get_database_id(database_name):
server.just_exec("""
alter database %s
remove file course_3
""" % (database_name,))
print("""
alter database %s
remove file course_3
""" % (database_name,))
server.just_exec("""
alter database %s
remove filegroup Course_3
""" % (database_name, ))
print("""
alter database %s
remove filegroup Course_3
""" % (database_name, ))
def main():
database_name = 'student_source'
with SqlServer() as server:
has_database_flag = (server.get_database_id(database_name) is not None)
with SqlServer(database_name if has_database_flag else None) as server:
q = wk(server)
s = fc(server)
s(server.show_database_cursor())
q("""select * from sys.databases where name = '%s'""" %
(database_name, ))
q("""select * from sys.database_files""")
q("""select * from sys.filegroups""")
id = server.get_database_id(database_name)
if id:
q("""select file_id, file_guid, type_desc, name, physical_name,
size, max_size, growth, is_percent_growth
from sys.master_files where database_id = %d """ % (id, ))
from sql_server import SqlServer
from sql_func import fc, wk
import pymssql
if __name__ == '__main__':
database_name = 'student_source'
with SqlServer(database_name, auto_commit=True) as server:
q = wk(server)
server.drop_procedure("sel_course_check")
with open('sel_course_check.sql', encoding='utf-8') as f:
proc = f.read()
server.just_exec(proc)
q("""execute sel_course_check 'g0940211'""")
server.drop_procedure("show_tea")
with open('show_tea.sql', encoding='utf-8') as f:
proc = f.read()
server.just_exec(proc)
q("""execute show_tea""")
with open('alter_show_tea.sql', encoding='utf-8') as f:
proc = f.read()
server.just_exec(proc)
q("""execute show_tea""")
# 删除存储过程
server.drop_trigger("check_teachers_dept_id")
with open('check_teachers_dept_id.sql', encoding='utf-8') as f:
trigger = f.read()
def up():
database_name = 'student_source'
with SqlServer(auto_commit=True) as server:
q = wk(server)
q("""select * from sys.databases where name = '%s'""" % (database_name,))
server.just_exec("""
if exists(select name from sys.databases where name='student_source')
drop database student_source;
""")
print("""
if exists(select name from sys.databases where name='student_source')
drop database student_source;
""")
q("""select * from sys.databases where name = '%s'""" % (database_name,))
server.just_exec("""
CREATE DATABASE student_source
ON PRIMARY
(
NAME='stu_main',
FILENAME='/home/sql/main.mdf',
SIZE=8MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
),
FILEGROUP Course1
(
NAME = 'course_1',
FILENAME='/home/sql/c1.ndf',
SIZE=8MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
),
FILEGROUP Course2
(
NAME = 'course_2',
FILENAME='/home/sql/c2.ndf',
SIZE=8MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
)
LOG ON
(
NAME = 'logger',
FILENAME='/home/sql/c2.ldf',
SIZE=8MB,
MAXSIZE=1GB,
FILEGROWTH=10%
)
""")
print("""
CREATE DATABASE student_source
ON PRIMARY
(
NAME='stu_main',
FILENAME='/home/sql/main.mdf',
SIZE=8MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
),
FILEGROUP Course1
(
NAME = 'course_1',
FILENAME='/home/sql/c1.ndf',
SIZE=8MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
),
FILEGROUP Course2
(
NAME = 'course_2',
FILENAME='/home/sql/c2.ndf',
SIZE=8MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
)
LOG ON
(
NAME = 'logger',
FILENAME='/home/sql/c2.ldf',
SIZE=8MB,
MAXSIZE=1GB,
FILEGROWTH=10%
)
""")
q("""select * from sys.databases where name = '%s'""" % (database_name,))