NAME

SQL::ObjectModel - Unserialized SQL objects, use like XML DOM

COPYRIGHT AND LICENSE

This file is part of the SQL::ObjectModel library (libSOM).

SQL::ObjectModel is Copyright (c) 1999-2003, Darren R. Duncan. All rights reserved. Address comments, suggestions, and bug reports to perl@DarrenDuncan.net, or visit "http://www.DarrenDuncan.net" for more information.

SQL::ObjectModel is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License (GPL) version 2 as published by the Free Software Foundation (http://www.fsf.org/). You should have received a copy of the GPL as part of the SQL::ObjectModel distribution, in the file named "LICENSE"; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.

Any versions of SQL::ObjectModel that you modify and distribute must carry prominent notices stating that you changed the files and the date of any changes, in addition to preserving this original copyright notice and other credits. SQL::ObjectModel is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GPL for more details.

Linking SQL::ObjectModel statically or dynamically with other modules is making a combined work based on SQL::ObjectModel. Thus, the terms and conditions of the GPL cover the whole combination.

As a special exception, the copyright holders of SQL::ObjectModel give you permission to link SQL::ObjectModel with independent modules that are interfaces to or implementations of databases, regardless of the license terms of these independent modules, and to copy and distribute the resulting combined work under terms of your choice, provided that every copy of the combined work is accompanied by a complete copy of the source code of SQL::ObjectModel (the version of SQL::ObjectModel used to produce the combined work), being distributed under the terms of the GPL plus this exception. An independent module is a module which is not derived from or based on SQL::ObjectModel, and which is fully useable when not linked to SQL::ObjectModel in any form.

Note that people who make modified versions of SQL::ObjectModel are not obligated to grant this special exception for their modified versions; it is their choice whether to do so. The GPL gives permission to release a modified version without this exception; this exception also makes it possible to release a modified version which carries forward this exception.

While it is by no means required, the copyright holders of SQL::ObjectModel would appreciate being informed any time you create a modified version of SQL::ObjectModel that you are willing to distribute, because that is a practical way of suggesting improvements to the standard version.

DEPENDENCIES

Perl Version:

5.004

Standard Modules:

Carp

Nonstandard Modules:

none

SYNOPSIS

use strict;
use warnings;

use SQL::ObjectModel;

my $object_model = SQL::ObjectModel->new(); # a root node

$object_model->add_child_nodes( [ map { { 'NODE_TYPE' => 'data_type', 'ATTRS' => $_ } } (
	{ 'name' => 'bin1k' , 'basic_type' => 'bin', 'size_in_bytes' =>  1_000, },
	{ 'name' => 'bin32k', 'basic_type' => 'bin', 'size_in_bytes' => 32_000, },
	{ 'name' => 'str4'  , 'basic_type' => 'str', 'size_in_chars' =>  4, 'store_fixed' => 1, 
		'str_encoding' => 'asc', 'str_trim_white' => 1, 'str_latin_case' => 'uc', 
		'str_pad_char' => ' ', 'str_trim_pad' => 1, },
	{ 'name' => 'str10' , 'basic_type' => 'str', 'size_in_chars' => 10, 'store_fixed' => 1, 
		'str_encoding' => 'asc', 'str_trim_white' => 1, 'str_latin_case' => 'pr', 
		'str_pad_char' => ' ', 'str_trim_pad' => 1, },
	{ 'name' => 'str30' , 'basic_type' => 'str', 'size_in_chars' =>    30, 
		'str_encoding' => 'asc', 'str_trim_white' => 1, },
	{ 'name' => 'str2k' , 'basic_type' => 'str', 'size_in_chars' => 2_000, 'str_encoding' => 'u16', },
	{ 'name' => 'byte' , 'basic_type' => 'num', 'size_in_bytes' => 1, 'num_precision' => 0, }, #  3 digits
	{ 'name' => 'short', 'basic_type' => 'num', 'size_in_bytes' => 2, 'num_precision' => 0, }, #  5 digits
	{ 'name' => 'int'  , 'basic_type' => 'num', 'size_in_bytes' => 4, 'num_precision' => 0, }, # 10 digits
	{ 'name' => 'long' , 'basic_type' => 'num', 'size_in_bytes' => 8, 'num_precision' => 0, }, # 19 digits
	{ 'name' => 'ubyte' , 'basic_type' => 'num', 'size_in_bytes' => 1, 
		'num_unsigned' => 1, 'num_precision' => 0, }, #  3 digits
	{ 'name' => 'ushort', 'basic_type' => 'num', 'size_in_bytes' => 2, 
		'num_unsigned' => 1, 'num_precision' => 0, }, #  5 digits
	{ 'name' => 'uint'  , 'basic_type' => 'num', 'size_in_bytes' => 4, 
		'num_unsigned' => 1, 'num_precision' => 0, }, # 10 digits
	{ 'name' => 'ulong' , 'basic_type' => 'num', 'size_in_bytes' => 8, 
		'num_unsigned' => 1, 'num_precision' => 0, }, # 19 digits
	{ 'name' => 'float' , 'basic_type' => 'num', 'size_in_bytes' => 4, },
	{ 'name' => 'double', 'basic_type' => 'num', 'size_in_bytes' => 8, },
	{ 'name' => 'dec10p2', 'basic_type' => 'num', 'size_in_digits' =>  10, 'num_precision' => 2, },
	{ 'name' => 'dec255' , 'basic_type' => 'num', 'size_in_digits' => 255, },
	{ 'name' => 'boolean', 'basic_type' => 'bool', },
	{ 'name' => 'datetime', 'basic_type' => 'datetime', 'datetime_calendar' => 'abs', },
	{ 'name' => 'dtchines', 'basic_type' => 'datetime', 'datetime_calendar' => 'chi', },
	{ 'name' => 'str1'  , 'basic_type' => 'str', 'size_in_chars' =>     1, },
	{ 'name' => 'str20' , 'basic_type' => 'str', 'size_in_chars' =>    20, },
	{ 'name' => 'str100', 'basic_type' => 'str', 'size_in_chars' =>   100, },
	{ 'name' => 'str250', 'basic_type' => 'str', 'size_in_chars' =>   250, },
	{ 'name' => 'entitynm', 'basic_type' => 'str', 'size_in_chars' =>  30, },
	{ 'name' => 'generic' , 'basic_type' => 'str', 'size_in_chars' => 250, },
) ] );

my $database = SQL::ObjectModel->new( { 'NODE_TYPE' => 'database', 'PARENT' => $object_model } );
my $namespace = SQL::ObjectModel->new( { 'NODE_TYPE' => 'namespace', 'PARENT' => $database } );

my $tbl_person = SQL::ObjectModel->new( { 'NODE_TYPE' => 'table', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'person', 'public_syn' => 'person', 
		'storage_file' => 'person', }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'table_col', 'ATTRS' => $_ } } (
		{
			'name' => 'person_id', 'data_type' => 'int', 'required_val' => 1,
			'default_val' => 1, 'auto_inc' => 1,
		},
		{ 'name' => 'alternate_id', 'data_type' => 'str20' , 'required_val' => 0, },
		{ 'name' => 'name'        , 'data_type' => 'str100', 'required_val' => 1, },
		{ 'name' => 'sex'         , 'data_type' => 'str1'  , 'required_val' => 0, },
		{ 'name' => 'father_id'   , 'data_type' => 'int'   , 'required_val' => 0, },
		{ 'name' => 'mother_id'   , 'data_type' => 'int'   , 'required_val' => 0, },
	) ),
	( map { { 'NODE_TYPE' => 'table_ind', 'ATTRS' => $_->[0], 
			'CHILDREN' => { 'NODE_TYPE' => 'table_ind_col', 'ATTRS' => $_->[1] } } } (
		[ { 'name' => 'primary'        , 'ind_type' => 'unique', }, 'person_id'    ], 
		[ { 'name' => 'ak_alternate_id', 'ind_type' => 'unique', }, 'alternate_id' ], 
		[ { 'name' => 'fk_father', 'ind_type' => 'foreign', 'f_table' => 'person', }, 
			{ 'col' => 'father_id', 'f_col' => 'person_id' } ], 
		[ { 'name' => 'fk_mother', 'ind_type' => 'foreign', 'f_table' => 'person', }, 
			{ 'col' => 'mother_id', 'f_col' => 'person_id' } ], 
	) ),
] } );

my $vw_person = SQL::ObjectModel->new( { 'NODE_TYPE' => 'view', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'person', 'may_write' => 1, 'match_table' => 'person' }, } );

my $vw_person_with_parents = SQL::ObjectModel->new( { 'NODE_TYPE' => 'view', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'person_with_parents', 'may_write' => 0, }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'view_col', 'ATTRS' => $_ } } (
		{ 'name' => 'self_id'    , 'data_type' => 'int'   , },
		{ 'name' => 'self_name'  , 'data_type' => 'str100', },
		{ 'name' => 'father_id'  , 'data_type' => 'int'   , },
		{ 'name' => 'father_name', 'data_type' => 'str100', },
		{ 'name' => 'mother_id'  , 'data_type' => 'int'   , },
		{ 'name' => 'mother_name', 'data_type' => 'str100', },
	) ),
	{ 'NODE_TYPE' => 'view_rowset', 'CHILDREN' => [ 
		( map { { 'NODE_TYPE' => 'view_src', 'ATTRS' => { 'name' => $_, 'match_table' => 'person', }, 
			'CHILDREN' => [ map { { 'NODE_TYPE' => 'view_src_col', 'ATTRS' => $_ } } qw( person_id name ) ] 
		} } qw( self father mother ) ),
		{ 'NODE_TYPE' => 'view_join', 'ATTRS' => { 'lhs_src' => 'self', 
				'rhs_src' => 'father', 'join_type' => 'left', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_join_col', 'ATTRS' => { 'lhs_src_col' => 'father_id', 
				'rhs_src_col' => 'person_id',  } },
		] },
		{ 'NODE_TYPE' => 'view_join', 'ATTRS' => { 'lhs_src' => 'self', 
				'rhs_src' => 'mother', 'join_type' => 'left', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_join_col', 'ATTRS' => { 'lhs_src_col' => 'mother_id', 
				'rhs_src_col' => 'person_id',  } },
		] },
		( map { { 'NODE_TYPE' => 'view_col_def', 'ATTRS' => $_ } } (
			{ 'name' => 'self_id'    , 'expr_type' => 'col', 'src' => 'self'  , 'src_col' => 'person_id', },
			{ 'name' => 'self_name'  , 'expr_type' => 'col', 'src' => 'self'  , 'src_col' => 'name'     , },
			{ 'name' => 'father_id'  , 'expr_type' => 'col', 'src' => 'father', 'src_col' => 'person_id', },
			{ 'name' => 'father_name', 'expr_type' => 'col', 'src' => 'father', 'src_col' => 'name'     , },
			{ 'name' => 'mother_id'  , 'expr_type' => 'col', 'src' => 'mother', 'src_col' => 'person_id', },
			{ 'name' => 'mother_name', 'expr_type' => 'col', 'src' => 'mother', 'src_col' => 'name'     , },
		) ),
		{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 'view_part' => 'where', 
				'expr_type' => 'sfunc', 'sfunc' => 'and', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
					'expr_type' => 'sfunc', 'sfunc' => 'like', }, 'CHILDREN' => [ 
				{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
					'expr_type' => 'col', 'src' => 'father', 'src_col' => 'name', }, },
				{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
					'expr_type' => 'var', 'var_name' => 'srchw_fa', }, },
			] },
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
					'expr_type' => 'sfunc', 'sfunc' => 'like', }, 'CHILDREN' => [ 
				{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
					'expr_type' => 'col', 'src' => 'mother', 'src_col' => 'name', }, },
				{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
					'expr_type' => 'var', 'var_name' => 'srchw_mo', }, },
			] },
		] },
	] },
] } );

my $tbl_user_auth = SQL::ObjectModel->new( { 'NODE_TYPE' => 'table', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'user_auth', 'public_syn' => 'user_auth', 
		'storage_file' => 'user', }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'table_col', 'ATTRS' => $_ } } (
		{
			'name' => 'user_id', 'data_type' => 'int', 'required_val' => 1,
			'default_val' => 1, 'auto_inc' => 1,
		},
		{ 'name' => 'login_name'   , 'data_type' => 'str20'  , 'required_val' => 1, },
		{ 'name' => 'login_pass'   , 'data_type' => 'str20'  , 'required_val' => 1, },
		{ 'name' => 'private_name' , 'data_type' => 'str100' , 'required_val' => 1, },
		{ 'name' => 'private_email', 'data_type' => 'str100' , 'required_val' => 1, },
		{ 'name' => 'may_login'    , 'data_type' => 'boolean', 'required_val' => 1, },
		{ 
			'name' => 'max_sessions', 'data_type' => 'byte', 'required_val' => 1, 
			'default_val' => 3, 
		},
	) ),
	( map { { 'NODE_TYPE' => 'table_ind', 'ATTRS' => $_->[0], 
			'CHILDREN' => { 'NODE_TYPE' => 'table_ind_col', 'ATTRS' => $_->[1] } } } (
		[ { 'name' => 'primary'         , 'ind_type' => 'unique', }, 'user_id'       ],
		[ { 'name' => 'ak_login_name'   , 'ind_type' => 'unique', }, 'login_name'    ],
		[ { 'name' => 'ak_private_email', 'ind_type' => 'unique', }, 'private_email' ],
	) ),
] } );

my $tbl_user_profile = SQL::ObjectModel->new( { 'NODE_TYPE' => 'table', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'user_profile', 'public_syn' => 'user_profile', 
		'storage_file' => 'user', }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'table_col', 'ATTRS' => $_ } } (
		{ 'name' => 'user_id'     , 'data_type' => 'int'   , 'required_val' => 1, },
		{ 'name' => 'public_name' , 'data_type' => 'str250', 'required_val' => 1, },
		{ 'name' => 'public_email', 'data_type' => 'str250', 'required_val' => 0, },
		{ 'name' => 'web_url'     , 'data_type' => 'str250', 'required_val' => 0, },
		{ 'name' => 'contact_net' , 'data_type' => 'str250', 'required_val' => 0, },
		{ 'name' => 'contact_phy' , 'data_type' => 'str250', 'required_val' => 0, },
		{ 'name' => 'bio'         , 'data_type' => 'str250', 'required_val' => 0, },
		{ 'name' => 'plan'        , 'data_type' => 'str250', 'required_val' => 0, },
		{ 'name' => 'comments'    , 'data_type' => 'str250', 'required_val' => 0, },
	) ),
	( map { { 'NODE_TYPE' => 'table_ind', 'ATTRS' => $_->[0], 
			'CHILDREN' => { 'NODE_TYPE' => 'table_ind_col', 'ATTRS' => $_->[1] } } } (
		[ { 'name' => 'primary'       , 'ind_type' => 'unique', }, 'user_id'     ],
		[ { 'name' => 'ak_public_name', 'ind_type' => 'unique', }, 'public_name' ],
		[ { 'name' => 'fk_user', 'ind_type' => 'foreign', 'f_table' => 'user_auth', }, 
			{ 'col' => 'user_id', 'f_col' => 'user_id' } ], 
	) ),
] } );

my $vw_user = SQL::ObjectModel->new( { 'NODE_TYPE' => 'view', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'user', 'may_write' => 1, }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'view_col', 'ATTRS' => $_ } } (
		{ 'name' => 'user_id'      , 'data_type' => 'int'    , },
		{ 'name' => 'login_name'   , 'data_type' => 'str20'  , },
		{ 'name' => 'login_pass'   , 'data_type' => 'str20'  , },
		{ 'name' => 'private_name' , 'data_type' => 'str100' , },
		{ 'name' => 'private_email', 'data_type' => 'str100' , },
		{ 'name' => 'may_login'    , 'data_type' => 'boolean', },
		{ 'name' => 'max_sessions' , 'data_type' => 'byte'   , },
		{ 'name' => 'public_name'  , 'data_type' => 'str250' , },
		{ 'name' => 'public_email' , 'data_type' => 'str250' , },
		{ 'name' => 'web_url'      , 'data_type' => 'str250' , },
		{ 'name' => 'contact_net'  , 'data_type' => 'str250' , },
		{ 'name' => 'contact_phy'  , 'data_type' => 'str250' , },
		{ 'name' => 'bio'          , 'data_type' => 'str250' , },
		{ 'name' => 'plan'         , 'data_type' => 'str250' , },
		{ 'name' => 'comments'     , 'data_type' => 'str250' , },
	) ),
	{ 'NODE_TYPE' => 'view_rowset', 'CHILDREN' => [ 
		{ 'NODE_TYPE' => 'view_src', 'ATTRS' => { 'name' => 'user_auth', 
				'match_table' => 'user_auth', }, 'CHILDREN' => [ 
			( map { { 'NODE_TYPE' => 'view_src_col', 'ATTRS' => $_ } } qw(
				user_id login_name login_pass private_name private_email may_login max_sessions
			) ),
		] },
		{ 'NODE_TYPE' => 'view_src', 'ATTRS' => { 'name' => 'user_profile', 
				'match_table' => 'user_profile', }, 'CHILDREN' => [ 
			( map { { 'NODE_TYPE' => 'view_src_col', 'ATTRS' => $_ } } qw(
				user_id public_name public_email web_url contact_net contact_phy bio plan comments
			) ),
		] },
		{ 'NODE_TYPE' => 'view_join', 'ATTRS' => { 'lhs_src' => 'user_auth', 
				'rhs_src' => 'user_profile', 'join_type' => 'left', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_join_col', 'ATTRS' => { 'lhs_src_col' => 'user_id', 
				'rhs_src_col' => 'user_id',  } },
		] },
		( map { { 'NODE_TYPE' => 'view_col_def', 'ATTRS' => $_ } } (
			{ 'name' => 'user_id'      , 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'user_id'      , },
			{ 'name' => 'login_name'   , 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'login_name'   , },
			{ 'name' => 'login_pass'   , 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'login_pass'   , },
			{ 'name' => 'private_name' , 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'private_name' , },
			{ 'name' => 'private_email', 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'private_email', },
			{ 'name' => 'may_login'    , 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'may_login'    , },
			{ 'name' => 'max_sessions' , 'expr_type' => 'col', 'src' => 'user_auth'   , 'src_col' => 'max_sessions' , },
			{ 'name' => 'public_name'  , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'public_name'  , },
			{ 'name' => 'public_email' , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'public_email' , },
			{ 'name' => 'web_url'      , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'web_url'      , },
			{ 'name' => 'contact_net'  , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'contact_net'  , },
			{ 'name' => 'contact_phy'  , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'contact_phy'  , },
			{ 'name' => 'bio'          , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'bio'          , },
			{ 'name' => 'plan'         , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'plan'         , },
			{ 'name' => 'comments'     , 'expr_type' => 'col', 'src' => 'user_profile', 'src_col' => 'comments'     , },
		) ),
		{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 'view_part' => 'where', 
				'expr_type' => 'sfunc', 'sfunc' => 'eq', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
				'expr_type' => 'col', 'src' => 'user_auth', 'src_col' => 'user_id', }, },
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
				'expr_type' => 'var', 'var_name' => 'curr_uid', }, },
		] },
	] },
] } );

my $tbl_user_pref = SQL::ObjectModel->new( { 'NODE_TYPE' => 'table', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'user_pref', 'public_syn' => 'user_pref', 
		'storage_file' => 'user', }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'table_col', 'ATTRS' => $_ } } (
		{ 'name' => 'user_id'   , 'data_type' => 'int'     , 'required_val' => 1, },
		{ 'name' => 'pref_name' , 'data_type' => 'entitynm', 'required_val' => 1, },
		{ 'name' => 'pref_value', 'data_type' => 'generic' , 'required_val' => 0, },
	) ),
	( map { { 'NODE_TYPE' => 'table_ind', 'ATTRS' => $_->[0], 'CHILDREN' => [ 
			map { { 'NODE_TYPE' => 'table_ind_col', 'ATTRS' => $_ } } @{$_->[1]}
			] } } (
		[ { 'name' => 'primary', 'ind_type' => 'unique', }, [ 'user_id', 'pref_name', ], ], 
		[ { 'name' => 'fk_user', 'ind_type' => 'foreign', 'f_table' => 'user_auth', }, 
			[ { 'col' => 'user_id', 'f_col' => 'user_id' }, ], ], 
	) ),
] } );

my $vw_user_theme = SQL::ObjectModel->new( { 'NODE_TYPE' => 'view', 'PARENT' => $namespace, 
		'ATTRS' => { 'name' => 'user_theme', 'may_write' => 0, }, 'CHILDREN' => [ 
	( map { { 'NODE_TYPE' => 'view_col', 'ATTRS' => $_ } } (
		{ 'name' => 'theme_name' , 'data_type' => 'generic', },
		{ 'name' => 'theme_count', 'data_type' => 'int'    , },
	) ),
	{ 'NODE_TYPE' => 'view_rowset', 'CHILDREN' => [ 
		{ 'NODE_TYPE' => 'view_src', 'ATTRS' => { 'name' => 'user_pref', 'match_table' => 'user_pref', }, 
			'CHILDREN' => [ map { { 'NODE_TYPE' => 'view_src_col', 'ATTRS' => $_ } } qw( pref_name pref_value ) ] 
		},
		{ 'NODE_TYPE' => 'view_col_def', 'ATTRS' => { 'name' => 'theme_name', 
			'expr_type' => 'col', 'src' => 'user_pref', 'src_col' => 'pref_value', }, },
		{ 'NODE_TYPE' => 'view_col_def', 'ATTRS' => { 'name' => 'theme_count', 
				'expr_type' => 'sfunc', 'sfunc' => 'gcount', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_col_def', 'ATTRS' => { 
				'expr_type' => 'col', 'src' => 'user_pref', 'src_col' => 'pref_value', }, },
		] },
		{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 'view_part' => 'where', 
				'expr_type' => 'sfunc', 'sfunc' => 'eq', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
				'expr_type' => 'col', 'src' => 'user_pref', 'src_col' => 'pref_name', }, },
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
				'expr_type' => 'lit', 'lit_val' => 'theme', }, },
		] },
		{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 'view_part' => 'group', 
			'expr_type' => 'col', 'src' => 'user_pref', 'src_col' => 'pref_value', }, },
		{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 'view_part' => 'havin', 
				'expr_type' => 'sfunc', 'sfunc' => 'gt', }, 'CHILDREN' => [ 
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
				'expr_type' => 'sfunc', 'sfunc' => 'gcount', }, },
			{ 'NODE_TYPE' => 'view_part_def', 'ATTRS' => { 
				'expr_type' => 'lit', 'lit_val' => '1', }, },
		] },
	] },
] } );

DESCRIPTION

This Perl 5 object class is intended to be a powerful but easy to use replacement for SQL strings (including support for placeholders), which you can use to make queries against a database. Each SQL::ObjectModel object can represent a non-ambiguous structured command for a database to execute, or one can be a non-ambiguous structured description of a database schema object. This class supports all types of database operations, including both data manipulation and schema manipulation, as well as managing database instances and users. You typically construct a database query by setting appropriate attributes of these objects, and you execute a database query by evaluating the same attributes. SQL::ObjectModel objects are designed to be equivalent to SQL in both the type of information they carry and in their conceptual structure. This is analagous to how XML DOMs are objects that are equivalent to XML strings, and they can be converted back and forth at will. If you know SQL, or even just relational database theory in general, then this module should be easy to learn.

SQL::ObjectModels are intended to represent all kinds of SQL, both DML and DDL, both ANSI standard and RDBMS vendor extensions. Unlike basically all of the other SQL generating/parsing modules I know about, which are limited to basic DML and only support table definition DDL, this class supports arbitrarily complex select statements, with composite keys and unions, and calls to stored functions; this class can also define views and stored procedures and triggers. Some of the existing modules, even though they construct complete SQL, will take/require fragments of SQL as input (such as "where" clauses) By contrast, SQL::ObjectModel takes no SQL fragments. All of its inputs are atomic, which means it is also easier to analyse the objects for implementing a wider range of functionality than previously expected; for example, it is much easier to analyse any select statement and generate update/insert/delete statements for the virtual rows fetched with it (a process known as updateable views).

Considering that each database product has its own dialect of SQL which it implements, you would have to code SQL differently depending on which database you are using. One common difference is the syntax for specifying an outer join in a select query. Another common difference is how to specify that a table column is an integer or a boolean or a character string. Moreover, each database has a distinct feature set, so you may be able to do tasks with one database that you can't do with another. In fact, some databases don't support SQL at all, but have similar features that are accessible thorough alternate interfaces. SQL::ObjectModel is designed to represent a normalized superset of all database features that one may reasonably want to use. "Superset" means that if even one database supports a feature, you will be able to invoke it with this class. You can also reference some features which no database currently implements, but it would be reasonable for one to do so later. "Normalized" means that if multiple databases support the same feature but have different syntax for referencing it, there will be exactly one way of referring to it with SQL::ObjectModel. So by using this class, you will never have to change your database-using code when moving between databases, as long as both of them support the features you are using (or they are emulated). That said, it is generally expected that if a database is missing a specific feature that is easy to emulate, then code which evaluates SQL::ObjectModels will emulate it (for example, emulating "left()" with "substr()"); in such cases, it is expected that when you use such features they will work with any database. For example, if you want a model-specified boolean data type, you will always get it, whether it is implemented on a per-database-basis as a "boolean" or an "int(1)" or a "number(1,0)". Or a model-specified "str" data type you will always get it, whether it is called "text" or "varchar2" or "sql_varchar".

SQL::ObjectModel is intended to be just a stateless container for database query or schema information. It does not talk to any databases by itself and it does not generate or parse any SQL; rather, it is intended that other third party modules or code of your choice will handle this task. In fact, SQL::ObjectModel is designed so that many existing database related modules could be updated to use it internally for storing state information, including SQL generating or translating modules, and schema management modules, and modules which implement object persistence in a database. Conceptually speaking, the DBI module itself could be updated to take SQL::ObjectModel objects as arguments to its "prepare" method, as an alternative (optional) to the SQL strings it currently takes. Code which implements the things that SQL::ObjectModel describes can do this in any way that they want, which can mean either generating and executing SQL, or generating Perl code that does the same task and evaling it, should they want to (the latter can be a means of emulation). This class should make all of that easy.

SQL::ObjectModel is especially suited for use with applications or modules that make use of data dictionaries to control what they do. It is common in applications that they interpret their data dictionaries and generate SQL to accomplish some of their work, which means making sure generated SQL is in the right dialect or syntax, and making sure literal values are escaped correctly. By using this module, applications can simply copy appropriate individual elements in their data dictionaries to SQL::ObjectModel properties, including column names, table names, function names, literal values, bind variable names, and they don't have to do any string parsing or assembling.

Now, I can only imagine why all of the other SQL generating/parsing modules that I know about have excluded privileged support for more advanced database features like stored procedures. Either the authors didn't have a need for it, or they figured that any other prospective users wouldn't need it, or they found it too difficult to implement so far and maybe planned to do it later. As for me, I can see tremendous value in various advanced features, and so I have included privileged support for them in SQL::ObjectModel. You simply have to work on projects of a significant size to get an idea that these features would provide a large speed, reliability, and security savings for you. Look at many large corporate or government systems, such as those which have hundreds of tables or millions of records, and that may have complicated business logic which governs whether data is consistent/valid or not. Within reasonable limits, the more work you can get the database to do internally, the better. I believe that if these features can also be represented in a database-neutral format, such as what SQL::ObjectModel attempts to do, then users can get the full power of a database without being locked into a single vendor due to all their investment in vendor-specific SQL stored procedure code. If customers can move a lot more easily, it will help encourage database vendors to keep improving their products or lower prices to keep their customers, and users in general would benefit. So I do have reasons for trying to tackle the advanced database features in SQL::ObjectModel.

STRUCTURE

A SQL::ObjectModel is implemented as a set of one or more related nodes (that have named attributes) which are organized into a structure resembling a tree, except that the tree can have more than one "root" (a node without parents), and that branches can join with other branches. Only one Perl class is needed currently (called SQL::ObjectModel), with each class object being one node. One node is quite simple and generic in structure, and has only basic rules governing when and how it can link with specific other nodes, based mainly on its "node_type" object property; this property is set only when a node is instantiated and can not be changed afterwards. But that is all it takes to implement the needed features.

Note: It is possible in the future that more classes may be added, some of which may subclass the basic node class, and others which don't. If we end up with an explicit "document" or "container" or "context" class in which all nodes would live, as normal XML DOMs do, then SQL::ObjectModel will probably become that container. Until then, SQL::ObjectModel is the node class itself.

These are the 4 conceptual properties of a SQL::ObjectModel object:

0: node_type (mandatory) - A string with a limited set of allowed values.
0: node_attrs (recommended) - A hash ref whose allowed keys depend on the value of node_type, and whose values can be either a scalar or a reference to another node. Each attribute value may be constrained to a certain data type or to a value from an enumerated list.
0: parent_node (optional) - A reference to another node. The other node can only be of a type which the current node is allowed to be a child of.
0: child_nodes (optional) - An array ref whose elements are references to other nodes. The allowed types of child nodes depend on the type of the current node.

A node could represent a table or a view or a block (procedure) or a DML command or a part of any of those, or another schema object used by one or more of those, or a variety of other things. Each node is related to another by only one of mainly two ways, which is either "B is a part of A" or "B is used by A". In the first scenario, B is a child node of A and A is the parent node of B. In the second scenario, a reference to B is an attribute of A, but neither is a parent or child of the other.

If a SQL::ObjectModel were converted into an XML DOM or an XML Document, then each node would be an XML "tag", with the "tag name" being the "node_type", node attributes being tag attributes, parent-child node relationships being exactly the same in XML, and "joined branches" being specific attribute values in one tag that are the same as specific attributes in a different tag. Also, the multiplicity of SQL::ObjectModel "root" nodes would also be first children of a new single XML root node, or a similar arrangement.

If a SQL::ObjectModel were converted into a data dictionary and stored in a database, then each node would conceptually be one record in the database, and each node attribute would be a field value in that record. Each "node_type" would be a distinct database table having said records, and each table would have different columns, as each node type specifies a different set of node attributes. When two nodes are in a parent-child relationship (B is a part of A), the database record of the child node would have a foreign key constraint on it that references the table of the parent node; this would be the "main" foreign key constraint on the child table. A table would also have foreign key constraints for each "B is used by A" relationship between two nodes, but that would not be the "main" relationship. When you represent a SQL::ObjectModel with a relational database, the so-called "joined branches" are indirect many-to-many table relationships.

You should look at the POD-only file named SQL::ObjectModel::DataDictionary, which came with this distribution. It serves to document all of the possible node types, with attributes, constraints, and allowed relationships with other node types, by way of describing a suitable database schema for storing these nodes in, such as was mentioned in the previous paragraph. As the SQL::ObjectModel class itself has very few properties and methods, all being highly generic (much akin to an XML DOM), the POD of this PM file will only describe how to use said methods, and will not list all the allowed inputs or constraints to said methods. With only simple guidance in ObjectMode.pm, you should be able to interpret DataDictionary.pod to get all the nitty gritty details. You should also look at the tutorial or example files which will be in the distribution when ready, or such sample code here.

Here is a diagram showing just the conceptually high-level node types, grouped in their logical "B is part of A" relationships (which may go through lower level node types that aren't shown here):

+-root (may or may not actually be used; a convenience)
   +-data_type (describes a table column or a view interface or a block variable)
   +-database (contains everything that is "in the database schema"; what you connect to)
   |  +-namespace (akin to an Oracle "schema", or can just be name prefix for following)
   |  |  +-table
   |  |  |  +-trigger (an un-named block that fires on a table event)
   |  |  |     +-block (the body of the trigger)
   |  |  +-view (a named view)
   |  |  |  +-view (a subquery)
   |  |  +-sequence    
   |  |  +-block (a named global procedure, function, or "package")
   |  |     +-block (a block nested or declared inside one of the above)
   |  |     +-view (a cursor declaration)
   |  +-user
   +-command (SQL that is not "part of a schema", although it includes DML for schema)
      +-view (a select statement usually)
      +-block (an anonymous block or set of normal SQL to run in sequence)

BRIEF NODE TYPE LIST

This is a brief list of all the valid types that a SQL::ObjectModel node can be. Descriptions can be found by looking up the corresponding table names in SQL::ObjectModel::DataDictionary, although a more detailed summary is planned to be added here. Note that this list isn't finalized and will be subject to various changes; these will also have corresponding changes in DataDictionary.

SQL OBJECT MODEL ROOT NODE

root

DATA TYPES

data_type

DATABASES AND NAME SPACES

database
namespace

TABLES

table
table_col
table_ind
table_ind_col
trigger

VIEWS

view
view_col
view_rowset
view_src
view_src_col
view_join
view_join_col
view_hierarchy
view_col_def
view_part_def

SEQUENCES

sequence

BLOCKS

block
block_var
block_stmt
block_expr

USERS

user

COMMANDS AND RESULTS

command

SYNTAX

This class does not export any functions or methods, so you need to call them using object notation. This means using Class->function() for functions and $object->method() for methods. If you are inheriting this class for your own modules, then that often means something like $self->method().

CONSTRUCTOR FUNCTIONS AND METHODS

new( NODE_TYPE | { NODE_TYPE[, PARENT][, ATTRS][, CHILDREN] } )

This function creates a new SQL::ObjectModel (or subclass) object/node and returns it. It takes one actual argument, which is either the scalar NODE_TYPE or a hash ref containing up to 4 named arguments: NODE_TYPE, PARENT, ATTRS, CHILDREN. The first argument, NODE_TYPE, is a string which specifies the "node_type" property of the new object. Only specific values are allowed, which you can see in the previous POD section "BRIEF NODE TYPE LIST". A node's type must be set on instantiation and it can not be changed afterwards. The second (optional) argument, PARENT, is an object of this class which will go in the "parent_node" property of the new object; it can be changed later. The third (optional) argument, ATTRS, is a hash ref whose elements will go in the "node_attrs" property of the new object; they can be changed later. The fourth (optional) argument, CHILDREN, is an array ref whose elements will go in the "child_nodes" property of the new object; they can be changed later. The four arguments are processed in the order shown above, with each being stored immediately; this means that elements in CHILDREN can make references to nodes in ATTRS elements and both can make reference to other nodes linked through a new PARENT, this is true even if said items were created as part of the same call to new().

clone([ CLONE ])

This method initializes a new object to have all of the same properties of the current object and returns it. This new object can be provided in the optional argument CLONE (if CLONE is an object of the same class as the current object); otherwise, a brand new object of the current class is used. Only object properties recognized by SQL::ObjectModel are set in the clone; other properties are not changed. Child nodes are deep-copied (a node can only have one parent), but references to nodes in attribute values are copied.

INDIVIDUAL PROPERTY ACCESSOR METHODS

get_node_type()

This method returns the "node_type" scalar property of this object. You can not change this property on an existing node, but you can set it on a new one.

get_parent_node()

This method returns the parent node of this object, if there is one.

set_parent_node( PARENT )

This method allows you to replace this object's parent node with a new node, which is provided by the PARENT argument. If PARENT is not defined, then the existing parent node will simply be unlinked from this object.

node_attribute( KEY[, VALUE] )

This method is an accessor for the "node_attrs" hash property of this object, and allows you to retrieve or set the one hash element that is matched by KEY. If VALUE is defined, the element is set to that value. The current value is then always returned.

get_node_attributes()

This method returns a shallow copy of the "node_type" hash property of this object, which is returned as a hash ref.

set_node_attributes( ATTRS )

This method allows you to set the "node_attrs" hash property of this object; all of the existing hash key/value pairs are replaced with the ones in the ATTRS hash ref argument. If ATTRS is empty, then the "node_attrs" will simply be emptied.

add_node_attributes( ATTRS )

This method allows you to add key/value pairs to the "node_attrs" hash property of this object, as provided in the ATTRS hash ref argument; any like-named keys will overwrite existing ones, but different-named ones will coexist.

get_child_nodes([ NODE_TYPE ])

This method returns a list of this object's child nodes, in a new array ref. If the optional argument NODE_TYPE is defined, then only child nodes of that node type are returned; otherwise, all child nodes are returned. All nodes of the same type are always grouped together, and within those groups they maintain their explicitely defined order, but the groupings themselves are in an effectively random order.

set_child_nodes( CHILDREN )

This method allows you to replace this object's child nodes with a list of new child nodes, which are provided as elements in the CHILDREN array ref argument. If CHILDREN is empty, then the existing child nodes will simply be removed from this object. Any removals is recursive, going to all descendant nodes.

add_child_nodes( CHILDREN )

This method allows you to add new child nodes to this object, which are provided as elements in the CHILDREN array ref argument. The new child nodes are appended to the list of existing nodes; all existing nodes are preserved.

METHODS FOR DEBUGGING

get_all_properties([ NO_EXTRAS ])

This method returns a deep copy of all of the properties of this object as non-blessed Perl data structures. These data structures are also arranged in a tree, but they do not have any circular references. You may be able to take the output of this method and recreate the original objects by passing it to SQL::ObjectModel->new(), although this may not work reliably; you should use clone() if you want something reliable. The main purpose, currently, of get_all_properties() is to make it easier to debug or test this class; it makes it easier to see at a glance whether the other class methods are doing what you expect. The output of this method should also be easy to serialize or unserialize to strings of Perl code or other things, should you want to compare your results easily by string compare (see "get_all_properties_as_string()"). By default, this method puts extra information into the data structure being produced, with hash keys starting in a double-underscore, such as the Perl internal reference number for the source node objects, or a serial number for each object being output, or the perl references for nodes that are being linked to by each object in their attributes; since this information isn't used by new(), and it is bound to change between script invocations, making it unreliable for static testing, you can set the boolean NO_EXTRAS to true and these extras won't be added to the output.

get_all_properties_as_str([ NO_EXTRAS[, NO_INDENTS] ])

This method is a wrapper for get_all_properties([ NO_EXTRAS ]) that serializes its output into a pretty-printed string of Perl code, suitable for humans to read. You should be able to eval this string and produce the original structure. By default, contents of lists are indented under the lists they are in (easier to read); if the optional boolean argument NO_INDENTS is true, then all output lines will be flush with the left, saving a fair amount of memory in what the resulting string consumes. (That said, even the indents are tabs, which take up much less space than multiple spaces per indent level.)

BUGS

This module is currently in alpha development status, meaning that any part of it stands a reasonable chance of being changed in the future, in incompatible ways. Also, the module hasn't been tested as much as I would like, but it has tested the more commonly used areas. All of the code in the SYNOPSIS section has been executed, which tests most internal functions and data.

This module currently throws exceptions whenever it is provided with bad input, which include some details of what the bad input is; it uses Carp::confess to throw the exceptions, as a stack trace should make bad input problems easier to debug, considering that this module uses a fair amount of recursion and wrapper methods. Due to the fact that each individual input element is applied hot once it is validated, these changes will still stick even if other input elements you provided during the same method call were incorrect and generated an exception; exceptions are also generated immediately, with subsequent inputs not being processed. Because of this, if you trap exceptions that this class generates with your own eval block, you should know that both the SQL::ObjectModel node that you were working on and quite possibly several other nodes related to it are now in an inconsistant state. If you continue to use those nodes, or others that are linked to them in any way, the program may crash without the friendly exceptions, as data which this module expects to be consistant (as it would be when there are no exceptions) would not be. I expect to deal with this problem at some time in the future, such as trying to defer the exception throw until I can clean up the state first.

Automatic node linking (to tree branches "beside" the current one) is not currently implemented in certain situations. The problem is due to search paths (an internal constant property) only knowing a single path to search each, when ideally they might try multiple paths. Until this is resolved, you can still link manually to unsearched nodes by providing references to the nodes to link to in your ATTRS inputs (automatic linking is only attempted when an attribute is supposed to be a node ref but given input is a scalar).

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)