insert_tb.sim

system sh/stop_dnodes.sh

system sh/ip.sh -i 1 -s up
system sh/deploy.sh -n dnode1 -m 192.168.0.1 -i 192.168.0.1
system sh/cfg.sh -n dnode1 -c commitLog -v 0
system sh/exec.sh -n dnode1 -s start

sleep 3000
sql connect
print ======================== dnode1 start

$dbPrefix = fi_in_db
$tbPrefix = fi_in_tb
$mtPrefix = fi_in_mt
$tbNum = 10
$rowNum = 20
$totalNum = 200

print create_tb test
print =============== set up
$i = 0
$db = $dbPrefix . $i
$mt = $mtPrefix . $i
$tb = $tbPrefix . $i

sql create database $db
sql use $db
sql create table $mt (ts timestamp, col1 int, col2 bigint, col3 float, col4 double, col5 binary(20), col6 bool, col7 smallint, col8 tinyint, col9 nchar(10)) tags (tag1 int)

# case: insert multiple records in a query
print =========== create_tb.sim case: insert multiple records in a query
$ts = 1500000000000
$col1 = 1
$col2 = 1
$col3 = 1.1e3
$col4 = 1.1e3
$col5 = 'Binary'
$col6 = true
$col7 = 1
$col8 = 1
$col9 = 'Nchar'
$tag1 = 1
sql reset query cache
sql create table $tb using $mt tags( $tag1 )
sql insert into $tb values ( $ts , $col1 , $col2 , $col3 , $col4 , $col5 , $col6 , $col7 , $col8 , $col9 ) ( $ts + 1000a, $col1 , $col2 , $col3 , $col4 , $col5 , $col6 , $col7 , $col8 , $col9 )
sql select * from $tb order by ts desc
print rows = $rows
if $rows != 2 then
  return -1
endi
if $data01 != $col1 then
  return -1
endi
#sql drop table $tb

# insert values for specified columns
$col1 = 2
$col3 = 3
$col5 = 5
sql create table $tb using $mt tags( $tag1 )
sql insert into $tb ( ts, col1, col3, col5) values ( $ts + 2000a, $col1 , $col3 , $col5 )
sql select * from $tb order by ts desc
if $rows != 3 then
  return -1
endi
if $data01 != $col1 then 
  return -1
endi
print data03 = $data03
if $data03 != 3.00000 then
  return -1
endi
if $data05 != $col5 then
  return -1
endi

sql insert into $tb (ts, col1, col2, col3, col4, col5, col6, col7, col8, col9) values ( $ts + 3000a, $col1 , $col2 , $col3 , $col4 , $col5 , $col6 , $col7 , $col8 , $col9 ) ( $ts + 4000a, $col1 , $col2 , $col3 , $col4 , $col5 , $col6 , $col7 , $col8 , $col9 )
sql select * from $tb order by ts desc
print rows = $rows
if $rows != 5 then
  return -1
endi

# case: insert records from .csv files
# manually test
#sql insert into $tb file parser/parser_test_data.csv
#sql select * from $tb
#if $rows != 10 then
#  return -1
#endi

sql drop table $tb
sql create table tb1 (ts timestamp, c1 int)
sql create table tb2 (ts timestamp, c1 int)
sql insert into tb1 values(now, 1) tb2 values (now, 2)
sql select count(*) from tb1
if $rows != 1 then
  return -1
endi
sql select count(*) from tb2
if $rows != 1 then
  return -1
endi

sql drop database $db
sleep 1000
sql create database $db
sql use $db
sql create table stb1 (ts timestamp, c1 int) tags(t1 int)
sql create table stb2 (ts timestamp, c1 double, c2 binary(10)) tags(t1 binary(10))
sql create table tb1 using stb1 tags(1)
sql create table tb2 using stb2 tags('tb2')
sql insert into tb1 (ts, c1) values (now-1s, 1) (now, 2) tb2 (ts, c1) values (now-2s, 1) (now-1s, 2) (now, 3)
sql select * from tb1 order by ts asc
if $rows != 2 then
  return -1
endi
if $data01 != 1 then
  return -1
endi
if $data11 != 2 then
  return -1
endi
sql select * from tb2 order by ts desc
if $rows != 3 then
  return -1
endi
if $data01 != 3.000000000 then
  return -1
endi
if $data11 != 2.000000000 then
  return -1
endi
if $data21 != 1.000000000 then
  return -1
endi

sql drop database $db
sleep 1000
sql create database $db
sql use $db
sql create table stb (ts timestamp, c1 int, c2 bigint, c3 float, c4 double, c5 nchar(10), c6 binary(20)) tags(t1 int, t2 bigint, t3 double, t4 float, t5 nchar(10))
sql create table tb0 using stb tags(0, 0, 0, 0, '涛思')
sql create table tb1 using stb tags('1', 1, 1, 1, '涛思')
sql create table tb2 using stb tags(2, '2', 2, 2, '涛思')
sql create table tb3 using stb tags(3, 3, '3', 3, '涛思')
sql create table tb4 using stb tags(4, 4, 4, '4', '涛思')
sql insert into tb0 values ('2018-09-17 09:00:00.000', 1, 1, 1, 1, '涛思nchar', 'none quoted')
sql insert into tb1 values ('2018-09-17 09:00:00.000', '1', 1, 1, 1, '涛思nchar', 'quoted int')
sql insert into tb2 values ('2018-09-17 09:00:00.000', 1, '1', 1, 1, '涛思nchar', 'quoted bigint')
sql insert into tb3 values ('2018-09-17 09:00:00.000', 1, 1, '1', 1, '涛思nchar', 'quoted float')
sql insert into tb4 values ('2018-09-17 09:00:00.000', 1, 1, 1, '1', '涛思nchar', 'quoted double')
sql select * from stb
if $rows != 5 then
  return -1
endi
if $data00 != @18-09-17 09:00:00.000@ then
  return -1
endi
if $data10 != @18-09-17 09:00:00.000@ then
  return -1
endi
if $data20 != @18-09-17 09:00:00.000@ then
  return -1
endi
if $data30 != @18-09-17 09:00:00.000@ then
  return -1
endi
if $data40 != @18-09-17 09:00:00.000@ then
  return -1
endi
if $data01 != 1 then
  return -1
endi
if $data02 != 1 then
  return -1
endi
if $data03 != 1.00000 then
  return -1
endi
if $data04 != 1.000000000 then
  return -1
endi
if $data05 != 涛思nchar then
  return -1
endi
if $data06 != @none quoted@ then
  return -1
endi
if $data13 != 1.00000 then
  return -1
endi
if $data14 != 1.000000000 then
  return -1
endi
if $data15 != 涛思nchar then
  return -1
endi
if $data16 != @quoted int@ then
  return -1
endi
if $data25 != 涛思nchar then
  return -1
endi
if $data26 != @quoted bigint@ then
  return -1
endi
if $data35 != 涛思nchar then
  return -1
endi
if $data36 != @quoted float@ then
  return -1
endi
if $data45 != 涛思nchar then
  return -1
endi
if $data46 != @quoted double@ then
  return -1
endi

# case: support null char of the binary field [TBASE-660]
sql create table nullb (ts timestamp, c1 binary(20), c2 binary(20), c3 float) 
sql insert into nullb values ('2018-09-17 09:00:00.000', '', '', 3.746)
sql select * from nullb
if $rows != 1 then
  return -1
endi

#sql drop database $db
#sql show databases
#if $rows != 0 then 
#  return -1
#endi