[mysql_fields] at tagSwap.net: Open Source Lasso Code

[mysql_fields]

Description

Link: [mysql_fields]

Author: Jason Huck

Category: Database

Version: 8.5.x

License: Public Domain

Posted: Feb. 05, 2008

Updated: Feb. 15, 2008

More by this author...

This custom type runs a "SHOW FIELDS IN ..." query against the database and table specified in the enclosing inline and returns the results as a map, where the key for each item is the field name and the value is a map of the type, null, key, default, and extra values. The values for an individual field can be returned by calling it directly by name. The value of an individual setting for a given field can be returned by passing it as a param. See below for examples. The tag also includes an alias for "mysql_columns." Must be used within an inline and only works with MySQL datasources.

Parameters

none

Sample Usage

inline( ...params... );
    // return all settings for all fields
    mysql_fields;

    // return settings for a single field
    mysql_fields->first_name;

    // return a single setting for a single field
    mysql_fields->first_name( -type);
/inline;

Source Code

Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74

define_type(
	'fields',
	-namespace='mysql_',
	-prototype,
	-description='For MySQL, returns details about the table used in the current SELECT inline.'
);
	local('data' = map);

	define_tag('oncreate');
		local('query') = action_statement;

		// remove whole line comments
		#query = string_replaceregexp(
			#query, 
			-find='\\s*#.*', 
			-replace=''
		);
		
		#query->trim&removetrailing(';');
		
		// remove LIMIT
		#query = string_replaceregexp(
			#query, 
			-find='(?im)\\sLIMIT\\s+\\d+(,\\d+)?\\s*$', 
			-replace=''
		);

		if(#query->beginswith('SELECT') && lasso_datasourceismysql(database_name));
			local('sql' = '
			   DROP TEMPORARY TABLE IF EXISTS TEMP_COLUMNFINDER;
			   
			   CREATE TEMPORARY TABLE TEMP_COLUMNFINDER
			   ' + #query + '
			   LIMIT 0;
			   
			   SHOW FULL COLUMNS FROM TEMP_COLUMNFINDER;\				
			');
		
			inline( -sql=#sql);
				rows;
					local('tmp') = map(
					   'type' 		= field('Type'),
					   'collation'	= field('Collation'),
					   'null'		= field('Null'),
					   'key'		= field('Key'),
					   'default'	= field('Default'),
					   'extra'		= field('Extra'),
					   'privileges'	= field('Privileges'),
					   'comment'	= field('Comment')
					);

					self->'data'->insert(field('Field') = #tmp);
				/rows;
			/inline;
		/if;
	/define_tag;

	define_tag('_unknowntag');
		local('field') = @self->'data'->find(tag_name);
	
		if(#field->isa('map'));
			!params->size ? return(#field);
			local('key') = params->first->removeleading('-')&;
			return(#field->find(#key));
		/if;
	/define_tag;

	define_tag('onconvert');
		return(self->'data');
	/define_tag;
/define_type;

namespace_import('mysql_fields','mysql_columns');

Comments

02/15/2008, Jason Huck
Updated
This tag has been updated to work with complex queries by creating a temp table based on the original query. It also returns additional information about each column. Thanks to Bil Corry for a few enhancements.