Class: Kbam

Inherits:
Object
  • Object
show all
Defined in:
lib/kbam.rb,
lib/kbam/version.rb

Constant Summary collapse

SELECT =

query_types

0
INSERT = 
1
UPDATE = 
2
VERSION = 
"0.4.5"
@@client = 
nil
@@sugar = 
false
@@verbose = 
true

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(login_credentials = nil) ⇒ Kbam

REVIEW/FIXME: remove instance connect?! create instance connection –> for multiple database connections maybe add settings –> allow separate client connections for each instance use? override default class connection if exists –> class fallback



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
 
# File 'lib/kbam.rb', line 29

def initialize( = nil)

	if  != nil
		warning "avoid connecting to the database for each instance"
		connect()
	end

	# query credentials
	@selects   = Array.new
	@from      = ""
	@wheres    = Array.new
	@or_wheres = Array.new
	@group     = ""
	@havings   = Array.new
	@orders    = Array.new
	@limit     = 1000
	@offset    = 0
	@query     = "" #raw query
	@as        = "t"
	@into      = ""
	@insert    = {}
	@insert_ignore = "" # will create a INSERT IGNORE statement 

	# meta data
	@last_query = nil
	@result     = nil
	@is_nested  = false
	@no_count   = false
	@query_type = SELECT
	#@count_query = nil

	# offer block syntax
	if block_given?
		yield self
	end

end

Instance Attribute Details

#is_nested ⇒ Object

Returns the value of attribute is_nested.



10
11
12
 
# File 'lib/kbam.rb', line 10

def is_nested
  @is_nested
end

#no_count ⇒ Object

Returns the value of attribute no_count.



12
13
14
 
# File 'lib/kbam.rb', line 12

def no_count
  @no_count
end

Class Method Details

.clear ⇒ Object

REVIEW: double check usage



122
123
124
 
# File 'lib/kbam.rb', line 122

def self.clear
	@@wheres = Array.new
end

.connect(login_credentials = nil) ⇒ Object

explicitly connect to database



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/kbam.rb', line 75

def self.connect( = nil)
	if  != nil

		if @@client == nil
			if .is_a?(String) &&  =~ /.+\.yml/
				 = YAML.load_file()
			end

			@@client = Mysql2::Client.new()
		else
			warning "you are already connected!"
		end
	else
		error "missing database credentials"
	end
end

.escape(string = nil) ⇒ Object Also known as: esc

escapes string uses native mysql2 client



111
112
113
 
# File 'lib/kbam.rb', line 111

def self.escape(string = nil)
	Mysql2::Client.escape string.to_s
end

.replace(string = nil, values = nil) ⇒ Object

FIXME: add nil checking



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
 
# File 'lib/kbam.rb', line 150

def self.replace(string = nil, values = nil)
	i = 0
	last_value = nil

	replaced_string = string.gsub(/\?/) do |match|
		if i > 0
			if values[i] != nil
				last_value = replacement = self.class.sanatize values[i]
			else
				replacement = last_value
			end
		else
			unless values[i] != nil
				puts "missing argument!"
			else
				last_value = replacement  = self.class.sanatize values[i]
			end
		end

		i += 1
		replacement
	end

	return replaced_string
end

.sanatize(item = nil) ⇒ Object

FIXME: add symbole to string

and decimal numbers

escapes variable and adds backticks for strings



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/kbam.rb', line 130

def self.sanatize(item = nil)
	if item != nil
		if item.is_a?(Integer)
			item = item.to_i
		else
			item.to_s.strip!
			if item =~ /\A\d+\Z/
				item = item.to_i
			else
				item = "'#{Mysql2::Client.escape(item)}'"
				#item = "'#{item}'"
			end
		end
	else
		warning "can't sanatize nil class"
	end
	return item
end

.sanatize_field(dirty_string) ⇒ Object

for sanatizing fields, tables



685
686
687
688
689
690
691
692
 
# File 'lib/kbam.rb', line 685

def Kbam.sanatize_field(dirty_string)
	unless dirty_string.respond_to? :to_s
		raise ArgumentError,
			"Cannot convert #{dirty_string.class} into a String"
	end

	'`' << dirty_string.to_s.gsub(/[^a-zA-Z0-9_]/, '') << '`'
end

.sanatize_field!(dirty_string) ⇒ Object 



678
679
680
681
682
 
# File 'lib/kbam.rb', line 678

def Kbam.sanatize_field!(dirty_string)
	dirty_string.replace(Kbam.sanatize_field(dirty_string))

	nil
end

.sugar? ⇒ Boolean

check if has syntax sugar

Returns:

  • (Boolean) 


93
94
95
 
# File 'lib/kbam.rb', line 93

def self.sugar?
	return @@sugar
end

.sugar_please! ⇒ Object

sets sugar REVIEW: add .no_sugar! if possible or instance dependet sugar?



104
105
106
107
 
# File 'lib/kbam.rb', line 104

def self.sugar_please!
	@@sugar = true
	require 'kbam/sugar.rb'
end

.verbose(value) ⇒ Object 



97
98
99
 
# File 'lib/kbam.rb', line 97

def self.verbose(value)
	@@verbose = value
end

Instance Method Details

#as(table_name = nil) ⇒ Object

setter and getter AS



232
233
234
235
236
237
238
239
 
# File 'lib/kbam.rb', line 232

def as(table_name = nil)
	if table_name != nil
		@as = table_name
		return self
	else
		return @as
	end
end

#clear ⇒ Object

What is this? remove?



430
431
432
 
# File 'lib/kbam.rb', line 430

def clear
	@@wheres = Array.new
end

#compose_from ⇒ Object 



730
731
732
733
734
735
736
 
# File 'lib/kbam.rb', line 730

def compose_from
	unless @from == ""
		"\nFROM\n   #{@from}"
	else
		error('No table specifiyed')
	end
end

#compose_group ⇒ Object 



738
739
740
741
742
743
744
 
# File 'lib/kbam.rb', line 738

def compose_group
	unless @group == ""
		"\nGROUP BY\n   #{@group}"
	else
		""
	end
end

#compose_having ⇒ Object 



771
772
773
774
775
776
777
 
# File 'lib/kbam.rb', line 771

def compose_having
	unless @havings.empty?
		return "\nHAVING\n   #{(@havings * ' AND ')}"
	else
		return ""
	end
end

#compose_limit ⇒ Object 



787
788
789
790
791
792
793
 
# File 'lib/kbam.rb', line 787

def compose_limit
	unless @limit == nil
		return "\nLIMIT #{@limit}"
	else
		return "\nLIMIT 1000"
	end
end

#compose_offset ⇒ Object 



795
796
797
798
799
800
801
 
# File 'lib/kbam.rb', line 795

def compose_offset
	unless @offset == nil || @offset == 0
		return "\nOFFSET #{@offset}"
	else
		return ""
	end
end

#compose_order ⇒ Object 



779
780
781
782
783
784
785
 
# File 'lib/kbam.rb', line 779

def compose_order
	unless @orders.empty?
		return "\nORDER BY\n   #{(@orders * ', ')}"
	else
		return ""
	end
end

#compose_query ⇒ Object 



803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
 
# File 'lib/kbam.rb', line 803

def compose_query

	# join the query fragments
	query_string = [
		compose_select,
		compose_from,
		compose_where,
		compose_group,
		compose_having,
		compose_order,
		compose_limit,
		compose_offset
	] * ' '

	unless is_nested
		if @@verbose
			log(query_string, "query")
		end
	end

	@last_query = query_string

	return query_string

end

#compose_select ⇒ Object

Query composers #



715
716
717
718
719
720
721
722
723
724
725
726
727
728
 
# File 'lib/kbam.rb', line 715

def compose_select
	select_string = "\nSELECT\n   "
	unless is_nested || @no_count
		select_string += "SQL_CALC_FOUND_ROWS "
	end

	unless @selects.empty?
		select_string += "#{(@selects * ', ')}"
	else
		select_string += "*"
	end

	return select_string
end

#compose_where ⇒ Object 



746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
 
# File 'lib/kbam.rb', line 746

def compose_where
	unless @wheres.empty?

		where_string = "\nWHERE\n   "

		i = 0
		@wheres.each do |w|
			if i > 0
				if w.sql_where_type == "or"
					where_string += "\n     OR "
				else
					where_string += "\n     AND "
				end
			end
			where_string += "#{w}"
			i += 1
		end

		return where_string

	else
		return ""
	end
end

#connect(login_credentials) ⇒ Object

DEPRECATED? explicitly connect to database



181
182
183
 
# File 'lib/kbam.rb', line 181

def connect()
	self.connect()
end

#count ⇒ Object Also known as: length

FIXME:



628
629
630
631
632
633
634
635
 
# File 'lib/kbam.rb', line 628

def count
	if @result != nil
		return @result.count
	else
		warning "Can't count for empty result"
		return nil
	end
end

#each ⇒ Object

if instance method then interferes with Array.each implementation!



606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
 
# File 'lib/kbam.rb', line 606

def each

	#puts "OBJECT TYPE: #{self.class}"

	if self.class == Kbam

		items = get

		if items != nil
			items.each do |item|
				yield item
			end
		else
			puts "Empty result"
		end

	else
		puts "No Kbam object found!"
	end
end

#error(message) ⇒ Object

interrupts execution creates error output



834
835
836
837
838
839
840
841
842
 
# File 'lib/kbam.rb', line 834

def error(message)
	error_message = " ERROR: #{message}! "
	error_length = error_message.length
	puts ("="*error_length).colorize( :color => :red, :background => :white )
	puts error_message.colorize( :color => :red, :background => :white )
	puts ("="*error_length).colorize( :color => :red, :background => :white )

	Kernel::raise error_message
end

#execute ⇒ Object

REVIEW/DEPRECATED ? FIXME: query auto execute? –> get? FIXME: needs response/callback!



202
203
204
205
206
207
208
 
# File 'lib/kbam.rb', line 202

def execute
	if @@verbose
		log(@query, "raw sql")
	end

	@@client.query(@query)
end

#field_sanatize(field) ⇒ Object

FIXME: change to class method sanatize field



665
666
667
668
669
670
671
672
673
674
675
676
 
# File 'lib/kbam.rb', line 665

def field_sanatize(field)
	field = field.to_s.strip
	#matches word character and between 2 and 64 character length
	if field =~ /\A[\w\.]{2,64}\Z/

		field.sub!(/\./, "`.`")

		return "`#{field}`"
	else
		error("invalid field name")
	end
end

#from(from_string = nil) ⇒ Object 



326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
 
# File 'lib/kbam.rb', line 326

def from(from_string = nil)

	if from_string.class.name == "Kbam"
		from_string.is_nested = true
		@from = "(#{from_string.compose_query}\n   )AS #{from_string.as}"
	else
		if from_string != nil && from_string.to_s.strip! != ""
			@from = from_string
		else
			error "missing table"
		end
	end

	return self
end

#get(format = "hash") ⇒ Object Also known as: fetch 



583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
 
# File 'lib/kbam.rb', line 583

def get(format = "hash")

	format = format.to_s

	case format
	when "json"
		#puts "FETCHING JSON"
		@result = to_json(@@client.query(compose_query))

	when "array"
		@result = @@client.query(compose_query, :as => :array)
	else
		@result = @@client.query(compose_query)
	end

	return @result
end

#get_wheres ⇒ Object

DEPRECATED



420
421
422
 
# File 'lib/kbam.rb', line 420

def get_wheres
	@wheres
end

#group(group_string = nil) ⇒ Object 



342
343
344
345
346
347
348
349
350
 
# File 'lib/kbam.rb', line 342

def group(group_string = nil)
	if group_string != nil && group_string != ""
		@group = group_string
	else
		warning "group method is empty"
	end

	return self
end

#having(string, *value) ⇒ Object

having API



494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
 
# File 'lib/kbam.rb', line 494

def having(string, *value)


	values = *value.to_a

	having_statement = replace(string, values)

	if string =~ /\s+or\s+/i

		having_statement	= "(#{having_statement})"
	end

	if having_statement != ""
		@havings.push having_statement
	end

	return self
end

#ignore ⇒ Object 



241
242
243
244
 
# File 'lib/kbam.rb', line 241

def ignore
	@insert_ignore = "IGNORE"
	return self
end

#insert(value_pair) ⇒ Object 



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/kbam.rb', line 246

def insert(value_pair)
	@query_type = INSERT

	columns = ""
	values  = ""
	column_count = 0

	value_pair.each do |key, value|

		if column_count != 0
			values += ", "
			columns += ", "
		end

		columns += "#{field_sanatize(key.to_s)}"
		values  += "#{self.class.sanatize(value)}"

		column_count += 1
	end

	@insert = " (#{columns}) VALUES (#{values})"

	return self
end

#into(table) ⇒ Object

used together with ‘insert’



295
296
297
298
299
300
301
302
303
 
# File 'lib/kbam.rb', line 295

def into(table)
	if @query_type === INSERT
		@into = "INSERT #{@insert_ignore} INTO #{field_sanatize(table)} "
	elsif @query_type === UPDATE
		@into = "UPDATE #{field_sanatize(table)}"
	end

	return self
end

#limit(limit_int = nil) ⇒ Object 



523
524
525
526
527
528
529
530
531
532
 
# File 'lib/kbam.rb', line 523

def limit(limit_int = nil)

	if limit_int != nil
		@limit = limit_int.to_i
	else
		@limit = nil
	end

	return self
end

#log(message, title = nil) ⇒ Object

prints debug info / log



855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
 
# File 'lib/kbam.rb', line 855

def log(message, title = nil)
	if title == nil
		title = "log"
	end
	color_width = 80
	title_length = title.length

	fill = color_width - title_length

	log_message = "#{message}"

	puts ("-"*color_width).colorize( :color => :black, :background => :white )
	puts "#{title.upcase}:#{" " * (fill - 1)}".colorize( :color => :black, :background => :white )
	puts ("-"*color_width).colorize( :color => :black, :background => :white )
	puts log_message.colorize( :color => :black, :background => :white )
	puts ("-"*color_width).colorize( :color => :black, :background => :white )
end

#offset(offset_int = nil) ⇒ Object 



534
535
536
537
538
539
540
541
542
543
 
# File 'lib/kbam.rb', line 534

def offset(offset_int = nil)

	if offset_int != nil
		@offset = offset_int.to_i
	else
		@offset = nil
	end

	return self
end

#or_where(string, *value) ⇒ Object Also known as: where_or, or

where API



435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
 
# File 'lib/kbam.rb', line 435

def or_where(string, *value)

	values = *value.to_a

	if string.class.name == "Kbam"

		where_string = ""

		i = 0
		string.get_wheres.each do |w|
			if i > 0
				if w.sql_where_type == "or"
					where_string += " OR "
				else
					where_string += " AND "
				end
			end
			where_string += "#{w}\n   "
			i += 1
		end

		puts "NESTED WHERE: #{where_string}"

		if where_string =~ /\s+AND\s+/i

			where_string	= "(#{where_string})"
		end
		where_string.set_sql_where_type("or")
		@wheres.push where_string
		string.clear

	else

		if string.sql_prop != nil && string.sql_value != nil
			or_where_statement = "`#{self.class.escape string.to_s}` #{string.sql_prop} #{self.class.sanatize string.sql_value}"
		elsif string !~ /\?/ && values.length == 1
			or_where_statement = "`#{self.class.escape string.to_s}` = #{self.class.sanatize values[0]}"
		else
			or_where_statement = replace(string, values)
		end

		if string =~ /\s+AND\s+/i

			or_where_statement	= "(#{or_where_statement})"
		end

		if or_where_statement != ""
			or_where_statement.set_sql_where_type("or")
			@wheres.push or_where_statement
		end
	end

	return self
end

#order(field, direction = nil) ⇒ Object 



513
514
515
516
517
518
519
520
521
 
# File 'lib/kbam.rb', line 513

def order(field, direction = nil)
	if direction == nil
		direction = 'ASC'
	end

	@orders.push "#{field_sanatize(field)} #{sort_sanatize(direction)}"

	return self
end

#query(string, *value) ⇒ Object

where API



186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/kbam.rb', line 186

def query(string, *value)

	values = *value.to_a

	query_statement = replace(string, values)

	if query_statement != ""
		@query = query_statement
	end

	return self
end

#replace(string, values) ⇒ Object

takes only an array of values



874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
 
# File 'lib/kbam.rb', line 874

def replace(string, values)

	warning "DEPRECATED: use class method instead"

	i = 0
	last_value = nil

	replaced_string = string.to_s.gsub(/\?/) do |match|

		if i > 0
			if values[i] != nil
				last_value = replacement = self.class.sanatize values[i]
			else
				replacement = last_value
			end
		else
			unless values[i] != nil
				puts "missing argument!"
			else
				last_value = replacement  = self.class.sanatize values[i]
			end
		end

		i += 1

		replacement
	end

	return replaced_string

end

#run ⇒ Object 



305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
 
# File 'lib/kbam.rb', line 305

def run
	if @query_type === INSERT
		# execute insert query
		@query = "#{@into} #{@insert}"
		execute

		# return the insert_id
		@query = "SELECT  LAST_INSERT_ID() AS insert_id;"
		query_result = execute

		insert_id = query_result.first["insert_id"]
		return insert_id
	elsif @query_type === UPDATE
		# execute insert query
		@query = "#{@into} #{@update} #{compose_where}"
		execute

		return true
	end
end

#select(*fields) ⇒ Object

FIXME: check nil / empty



211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/kbam.rb', line 211

def select(*fields)
	fields = *fields.to_a

	fields.each do |field|

		#remove preceeding and trailing whitespaces and comma
		cleaned_select = field.to_s.gsub(/\s*\n\s*/, ' ').to_s.sub(/\A\s*,?\s*/, '').sub(/\s*,?\s*\Z/, '')#.gsub(/\s*,\s*/, ", ")

		#back-quote fieldnames
		# before NO '?<field>''
		cleaned_select.gsub! /(\w+)(?=\s+AS\s+)/ do |match|
			"#{field_sanatize($1)}" #before 'field' was '$1'
		end

		@selects.push(cleaned_select)
	end

	return self
end

#sort_sanatize(sort) ⇒ Object

FIXME: change to class method senatize sort e.g. asc / desc



696
697
698
699
700
701
702
703
704
705
706
 
# File 'lib/kbam.rb', line 696

def sort_sanatize(sort)

	sort = sort.to_s.strip.upcase

	#if it matches DESC or DSC
	if sort =~ /\ADE?SC\Z/
		return 'DESC'
	else
		return 'ASC'
	end
end

#sql ⇒ Object Also known as: to_s, to_str, to_sql

FIXME: raw sql conflict and if not composed yet…



648
649
650
651
652
653
654
 
# File 'lib/kbam.rb', line 648

def sql
	if @query == ""
		return compose_query
	else
		return @query
	end
end

#to_json(result) ⇒ Object

REVIEW: using << instead of += –> faster



546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
 
# File 'lib/kbam.rb', line 546

def to_json(result)

	json = "{"

	# loop through rows
	i = 0
	result.each do |row|

		#add comma after each row
		if i > 0 then json << ", " end

		#puts row

		# use id as json keys
		json << "#{row["id"]}: {"

			# remove id
			row.delete("id")

			#use rest of fields as json value
			k = 0
			row.each do |key, value|
				if k > 0 then json << ", " end
				json << "#{key}: '#{value}'"
				k += 1
			end

		json << "}"

		i += 1
	end

	json << "}"

	return json
end

#total ⇒ Object 



639
640
641
642
643
644
645
 
# File 'lib/kbam.rb', line 639

def total
	if @result != nil && ! @no_count
		@@client.query("SELECT FOUND_ROWS() AS count").first["count"]
	else
		warning "Can't count total for empty result. Or no count error."
	end
end

#update(value_pair) ⇒ Object 



271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/kbam.rb', line 271

def update(value_pair)

	@query_type = UPDATE

	@update = "SET "
	column_count = 0

	value_pair.each do |key, value|

		if column_count != 0
			@update += ", "
		end

		@update += "#{field_sanatize(key.to_s)} = #{self.class.sanatize(value)}"

		column_count += 1

	end

	return self

end

#warning(message) ⇒ Object

creates warning continues execution



846
847
848
849
850
851
852
 
# File 'lib/kbam.rb', line 846

def warning(message)
	warning_message = " #{caller[0]} WARNING: #{message}! "
	warning_length = [warning_message.length, 80].min
	puts ("="*warning_length).colorize( :color => :yellow, :background => :white )
	puts warning_message.colorize( :color => :yellow, :background => :white )
	puts ("="*warning_length).colorize( :color => :yellow, :background => :white )
end

#where(string, *value) ⇒ Object Also known as: and

where API



358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
 
# File 'lib/kbam.rb', line 358

def where(string, *value)

	values = *value.to_a

	#log string.class.name, "and log"

	if string.class.name == "Kbam"

		where_string = ""

		i = 0
		string.get_wheres.each do |w|
			if i > 0
				if w.sql_where_type == "or"
					where_string += " OR "
				else
					where_string += " AND "
				end
			end
			where_string += "#{w}\n   "
			i += 1
		end

		#puts "NESTED WHERE: #{where_string}"

		if where_string =~ /\s+OR\s+/i

			where_string	= "(#{where_string})"
		end
		where_string.set_sql_where_type("and")
		@wheres.push where_string
		string.clear

	else
		#puts "WHERE public input: #{value}"
		if string.respond_to?(:sql_prop) && (string.sql_prop != nil && string.sql_value != nil)
			where_statement = "`#{self.class.escape string.to_s}` #{string.sql_prop} #{self.class.sanatize string.sql_value}"
		elsif string !~ /\?/ && values.length == 1
			where_statement = "`#{self.class.escape string.to_s}` = #{self.class.sanatize values[0]}"

		else
			where_statement = replace(string, values)
			if string =~ /\s+or\s+/i
				where_statement	= "(#{where_statement})"
			end
		end

		if where_statement != ""
			where_statement.set_sql_where_type("and")
			@wheres.push where_statement
		end
	end

	#puts "WHERE after public input: #{where_statement}"

	return self

end