how to write sql using 'not'

[chengyi]

Hi,
I want to write write sql like:
select * from table1 where role != 'usb'

but how can i write using mql

i check the parser code, it has the not operator.
but i can't find how to use it.

please give me a hand.

[klihub]

I skimmed through the lexical analyzer and parser code to refresh my memory and it looks like NOT (!) is a unary prefix operator for values. Looking at the bison rules, I think it should be parsed as part of the where_clause as

where cond_expr
  -> cond_expr 
    -> rel_expr
      -> val rel_op val
         ->  val == ! val

So try something like:

select * from table1 where role == ! 'usb'

I think that should work...

[chengyi]

@klihub I try it. but it doesn't work. i am using > val and < val, but it doesn't work too
how can I using logical operator.

[chengyi]

@klihub
I find the not operator only returns 0 or 1: because:

static int cond_unary_logicop(mqi_operator_t op, cond_stack_t *v)
{
    cond_data_t *d = &v->data;

    if (v->precedence >= PRECEDENCE_DATA && op == mqi_not) {
        switch (d->type) {
        case mqi_varchar:  return d->v.varchar && d->v.varchar[0] ? 0 : 1;
        case mqi_integer:  return d->v.integer ? 0 : 1;
        case mqi_unsignd:  return d->v.unsignd ? 0 : 1;
        default:           return 0;
        }
    }

    return 0;
}

so I think the answer about this issue is:
select * from table1 where role <'usb' | role> 'usb'

however, thanks.

[klihub]

@chengyi You're right. While the above is accepted by the parser it only works as expected when used in a single unary test.

Therefore I rolled a quick patch for you, implementing the NOT_EQUAL (!=) binary operator. It can be used to tests for inequality in where clauses. You can cherry-pick it to your tree from #25 . Here is a sample test using the daemon with the stock sample config and murphy-console to query the DB:

klitkey1-mobl murphy $ ./src/murphy-console 
murphy> db
murphy db> eval show tables
A: application_classes audio_playback_owner audio_playback_users audio_recording_owner audio_recording_users
V: video_playback_owner video_playback_users video_recording_owner video_recording_users
Z: zones

murphy db> eval select * from application_classes
name                       priority
-----------------------------------
implicit                          0
player                            1
game                              2
phone                             3
navigator                         4
interrupt                        99

murphy db> eval select * from application_classes where name = 'player'
name                       priority
-----------------------------------
player                            1

murphy db> eval select * from application_classes where name != 'player'
name                       priority
-----------------------------------
implicit                          0
game                              2
phone                             3
navigator                         4
interrupt                        99

murphy db> 

I also fixed a few other issues, mostly related to building, I noticed when building on recent distros. I've files separate PR's for those. You might want to take a look at them and cherry-pick any as you need.

[chengyi]

@klihub thank you! happy new year!