2 # -*- encoding: UTF-8 -*-
17 CREATE TABLE UserType (
20 CONSTRAINT PK_UserType
21 PRIMARY KEY (user_type_code),
28 CREATE PROCEDURE UserType_load_master_data()
34 ('PrivilegedUser', '特権ユーザー');
38 CREATE PROCEDURE UserType_list_all_types()
39 -- @DefineConstants(user_type_code)
47 -- @ClassifiedBy(user_type_code)
50 INTEGER AUTO_INCREMENT,
52 PK_User PRIMARY KEY(user_id),
55 CHAR(24) NOT NULL DEFAULT 'User',
56 CONSTRAINT FK_User_user_type_code
57 FOREIGN KEY(user_type_code) REFERENCES UserType(user_type_code),
61 INTEGER NOT NULL DEFAULT 0,
64 -- @KanaKanji @NotBlank
70 INDEX IND_User_nickname (nickname),
73 -- @MailAddress @NotBlank
75 CONSTRAINT UQ_User_mail_address
84 CREATE PROCEDURE User_count_active_users()
85 -- @ReturnsFirst(INTEGER)
93 CREATE PROCEDURE User_list_active_users()
102 CREATE PROCEDURE User_find_by_id(
103 _user_id INTEGER -- @ColumnValue
105 -- @ReturnsFirst(User)
109 WHERE user_id = _user_id;
113 CREATE PROCEDURE User_activate(
114 _mail_address CHAR(128) -- @ColumnValue
116 -- @ReturnsFirst(User)
117 -- @OnNoData(mail_address, 'メールアドレスが誤っているか、まだ登録されていません。')
121 WHERE mail_address = _mail_address
126 WHERE mail_address = _mail_address
131 CREATE PROCEDURE User_register(
132 _name TEXT, -- @ColumnValue
133 _nickname TEXT, -- @ColumnValue
134 _mail_address TEXT, -- @ColumnValue
135 _comment TEXT -- @ColumnValue @Optional
137 -- @ReturnsFirst(User)
138 -- @OnConflict(UQ_User_mail_address, mail_address, 'このメールアドレスは既に登録されています。')
140 SET @comment := COALESCE(_comment, '');
155 WHERE user_id = LAST_INSERT_ID();
159 CREATE PROCEDURE User_edit_comment(
160 _user_id INTEGER, -- @ColumnValue
161 _comment TEXT -- @ColumnValue
163 -- @ReturnsFirst(User)
164 -- @OnNoData(user_id, 'このユーザは登録されていないか、一時的に使用できなくなっています。')
167 SET comment = _comment
168 WHERE user_id = _user_id
173 WHERE user_id = _user_id
178 CREATE TABLE PrivilegedUser (
181 CONSTRAINT PK_PrivilegedUser
182 PRIMARY KEY(user_id),
183 CONSTRAINT FK_PrivilegedUser_user_id
184 FOREIGN KEY(user_id) REFERENCES User(user_id)
189 INTEGER NOT NULL DEFAULT 1
194 CREATE PROCEDURE User_make_privileged(
195 _user_id INTEGER -- @ColumnValue
197 -- @ReturnsFirst(User)
198 -- @OnNoData(user_id, 'このユーザはすでに特権ユーザとなっているか、一時的に使用できなくなっています。')
201 SET user_type_code = 'PrivilegedUser'
202 WHERE user_id = _user_id
204 AND user_type_code = 'User';
206 INSERT INTO PrivilegedUser
209 WHERE user_id = _user_id
211 AND user_type_code = 'PrivilegedUser';
214 FROM User JOIN PrivilegedUser USING (user_id)
215 WHERE user_id = _user_id
220 CREATE PROCEDURE PrivilegedUser_find_by_id(
223 -- @ReturnsFirst(User)
226 FROM User JOIN PrivilegedUser USING (user_id)
227 WHERE user_id = _user_id
229 AND user_type_code = 'PrivilegedUser';
233 CREATE PROCEDURE PrivilegedUser_acquire_top_secret(
234 _user_id INTEGER -- @ColumnValue
236 -- @ReturnsFirst(User)
237 -- @OnNoData('*', 'このユーザには権限がありません。')
239 SELECT *, '王様の耳は、ろばタイプ' AS top_secret
240 FROM User JOIN PrivilegedUser USING(user_id)
241 WHERE user_id = _user_id
248 class TestSQLLoad < Test::Unit::TestCase
252 SQL.sql_eval!('test_hdboo_temp_db', TestSQL::USER_SQL)
253 db = SQL.connect('test_hdboo_temp_db')
257 WHERE user_type_code = 'User';
260 assert_equal 2, Model::UserType.list_all_types.size
262 assert_equal 1, Model::UserType.list_all_types.size
\r end
265 def test_equality_of_record_object
266 SQL.sql_eval!('test_hdboo_temp_db', TestSQL::USER_SQL)
267 hogeo = Model::User.new(
269 :email => 'hogeta.hogeo@example.com'
272 nise_hogeo = Model::User.new(
274 :email => 'nise.hogeo@example.com'
277 assert_equal [1], hogeo.identifier
278 assert_equal [:user_id], hogeo.class.identifier_keys
279 assert hogeo == nise_hogeo
280 assert !hogeo.eql?(nise_hogeo)
281 assert hogeo == {:user_id => 1, :email => 'hogeta.hogeo@example.com'}
282 assert hogeo != {:user_id => 1, :email => ''}
283 assert hogeo.eql?({:user_id => 1, :email => 'hogeta.hogeo@example.com'})
284 assert ({:user_id => 1, :email => 'hogeta.hogeo@example.com'}) == hogeo
285 assert ({:user_id => 1, :email => 'hogeta.hogeo@example.com'}).eql?(hogeo)
286 assert ({:user_id => 1, :email => ''}) != hogeo
289 def test_require_sql!
290 $:.unshift File.join(File.dirname(__FILE__))
291 require_sql 'testmodel/user@test_hdboo_temp_db'
292 assert ::Fuga::Model::User.is_a?(Class)
296 SQL.sql_eval!('test_hdboo_temp_db', TestSQL::USER_SQL)
297 db = SQL.connect('test_hdboo_temp_db')
300 assert Model::User.respond_to?(:list_active_users)
301 active_users = db.transaction {Model::User.list_active_users}
302 assert active_users.empty?
304 assert Model::User.respond_to?(:find_by_id)
305 nobody = db.transaction {Model::User.find_by_id(:user_id => 9999)}
308 nobody = db.transaction {Model::User.find_by_id(9999)}
311 hogeo = Model::User.new(
313 :nickname => 'hogeo',
314 :mail_address => 'invalid..address@example.com'
316 assert_raise(InvalidArgument) {hogeo.validate}
318 hogeo.mail_address = 'hogeta.hogeo@example.com'
319 assert_nothing_raised {hogeo.validate}
321 assert hogeo.respond_to?(:register)
322 assert hogeo.respond_to?(:register!)
324 db.transaction { hogeo.register! }
325 assert_equal 1, hogeo.user_id
326 assert ! hogeo.active?
328 assert_equal 0, db.transaction { Model::User.count_active_users }
329 db.transaction { hogeo.activate! }
331 assert_equal 1, db.transaction { Model::User.count_active_users }
333 all_users = db.transaction { Model::User.list_active_users }
334 assert_equal 1, all_users.size
335 assert all_users[0].is_a?(Model::User)
337 assert_equal '', hogeo.comment
338 db.transaction { hogeo.edit_comment!(:comment => "multi\nline\ndata") }
339 assert_equal "multi\nline\ndata", hogeo.comment
342 #unique constraint violation
343 nise_hogeo = Model::User.new(
345 :nickname => 'nise_hogeo',
346 :mail_address => 'hogeta.hogeo@example.com' #must be unique
349 assert_raise(DataConflict) do
350 db.transaction { nise_hogeo.register! }
\r end
352 assert_raise(NoDataFound) do
354 Model::User.activate(:mail_address => 'nobody@example.com')
357 #initialize master data & load as class constants
358 assert Model::UserType.is_a?(Class)
359 assert Model::UserType.const_defined?('User')
360 assert Model::UserType.const_defined?('PrivilegedUser')
362 assert_equal 'User', Model::UserType::User.user_type_code
363 assert_equal '通常ユーザー', Model::UserType::User.name
365 assert_equal 'PrivilegedUser',
366 Model::UserType::PrivilegedUser.user_type_code
367 assert_equal '特権ユーザー',
368 Model::UserType::PrivilegedUser.name
371 assert Model::PrivilegedUser.is_a?(Class)
372 assert Model::PrivilegedUser < Model::User
374 super_hogeo = Model::PrivilegedUser.new(
377 :nickname => 'hogeo',
378 :mail_address => 'hogeta.hogeo@example.com'
381 assert super_hogeo.is_a?(Model::PrivilegedUser)
382 assert ! hogeo.respond_to?(:acquire_top_secret)
383 assert super_hogeo.respond_to?(:acquire_top_secret)
385 assert_raise(NoDataFound) do
386 db.transaction {super_hogeo.acquire_top_secret!}
\r end
389 assert_not_nil db.transaction {Model::User.find_by_id(:user_id => 1)}
390 assert_not_nil db.transaction {Model::User.find_by_id(1)}
391 assert_nil db.transaction {Model::PrivilegedUser.find_by_id(:user_id => 1)}
392 assert_nil db.transaction {Model::PrivilegedUser.find_by_id(1)}
394 db.transaction { super_hogeo = hogeo.make_privileged }
395 assert super_hogeo.is_a?(Model::PrivilegedUser)
397 assert_not_nil db.transaction {Model::PrivilegedUser.find_by_id(1)}
398 assert super_hogeo.respond_to?(:access_level)
399 assert_equal 1, super_hogeo.access_level
401 db.transaction { super_hogeo.acquire_top_secret! }
402 assert_equal '王様の耳は、ろばタイプ', super_hogeo.top_secret
406 SQL.sql_eval('test_hdboo_temp_db', TestSQL::USER_SQL)
408 assert Model::User.is_a?(Class)
410 hogeo = Model::User.new(
412 :nickname => 'hogeo',
413 :mail_address => 'hogeta.hogeo@example.com',
418 assert hogeo.respond_to?(:mail_address)
419 assert hogeo.respond_to?(:mail_address=)
420 assert_equal 'hogeta.hogeo@example.com', hogeo.mail_address
421 assert hogeo.respond_to?(:nickname)
422 assert hogeo.respond_to?(:nickname=)
423 assert_equal 'hogeo', hogeo.nickname
426 assert hogeo.respond_to?(:is_active)
427 assert hogeo.respond_to?(:is_active=)
428 assert hogeo.respond_to?(:active?)
429 assert_equal 0, hogeo.is_active
430 assert_equal false, hogeo.active?
433 assert hogeo.respond_to?(:count_active_users)
434 assert hogeo.respond_to?(:list_active_users)
437 assert hogeo.class.respond_to?(:count_active_users)
438 assert hogeo.class.respond_to?(:list_active_users)
441 assert hogeo.respond_to?(:invalid?)
442 assert hogeo.respond_to?(:validate)
443 assert !hogeo.invalid?
444 assert_nothing_raised do
448 assert hogeo.validate.is_a?(Model::User)
453 hogeo.is_active = 'treu'
457 assert ! raised_error.nil?
458 assert raised_error.is_a?(InvalidArgument)
459 assert_equal 1, raised_error.messages.size
460 assert_match /treu/, raised_error.messages[:is_active].to_s
462 hogeo.is_active = true
467 SQL.sql_eval!('test_hdboo_temp_db', <<-END_DDL)
474 id INTEGER NOT NULL AUTO_INCREMENT,
479 CREATE PROCEDURE Dummy_parse(csv VARCHAR(1024))
481 SELECT csv_get(csv, pos) AS val
483 WHERE pos <= csv_length(csv)
489 db = SQL.connect('test_hdboo_temp_db')
491 result = db.transaction do
492 CSVTest::Dummy.parse(:csv => 'hoge,fuga,piyo')
494 assert_equal 3, result.length
495 assert_equal 'hoge', result[0]['val']
496 assert_equal 'fuga', result[1]['val']
497 assert_equal 'piyo', result[2]['val']
501 class TestSQLParser < Test::Unit::TestCase
503 ast = SQL::Parser.new.parse(TestSQL::USER_SQL)
505 create_table_statements = []
506 create_procedure_statements = []
508 SQL::Parser::ASTWalker.new\
509 .visit(:CREATE_TABLE) do |statement|
510 create_table_statements << statement
512 .visit(:CREATE_PROCEDURE) do |statement|
513 create_procedure_statements << statement
517 assert_equal 3, create_table_statements.length
518 assert_equal 11, create_procedure_statements.length
520 user_table_source = create_table_statements[0].source.collect do |line|
526 class TestTable < Test::Unit::TestCase
529 sql_module = SQL::Module.new('TestModule.Model')
530 sql_module.elements << Table.new(:User, [
531 Table::Column.new(:id, DataType.new(:INTEGER)),
532 Table::Constraint::PrimaryKey.new(:User_id_PK, [:id]),
533 Table::Column.new(:mail_address, DataType.new(:CHAR, 128)),
534 Table::Column.new(:password, DataType.new(:CHAR, 32))
538 assert TestModule::Model::User.is_a?(Class)