Coldbox and VueJS untangled

Tag: qb

Dynamic datasources part 2: quick

Using dynamic datasources in a cfml ORM system can be hard. I am working on some project full of cform, and as mentioned in my post on dynamic datasources in qb I have a project with a lot of legacy code, full of cfquery, queryExecute, some qb queries and… tons of cform entities. And for all this code I should be able to change my datasource on the fly, based on URL or the authenticated user. I already had all solutions in place for qb and queryExecute, but cform is a showstopper. Back in CF9 when cform was introduced you could only work with ONE datasource, the default datasource. Since default datasources where introduced in CF9 I suspect this was only done to accommodate for the lack of datasource awareness of the cf9.0 orm entities. In CF9.01 this became slightly better when we could specify some datasource property, but it has always been a pain to get this right. And unfortunately we often worked with a multidatasource setup. When we switched from Lucee 4 to 5 it took several years before a very important multi datasource bug was fixed. And now we even needed dynamic datasources, which is a real NO! in cform. So how about quick?

Continue reading

Dynamic datasources part 1: QB

Recently I was working on a project where we wanted to reuse our code for multiple customers. The only difference: they all have their own database where their data was stored. We can do this in two different ways

  • deploy a new application for every customer. For a smal amount of customers that might be acceptable.
  • Based on the url we can detect which datasource we need for a customer. If we would be using the same url for all customers, we can have some central database which keeps all kind of information including datasource name for each individual customer. We could store the datasource name in a private request collection variable called mainDatasource (or put it in some user specific cache or session variable), so each customer has his own datasource.

Since our project was existing code, I have a few hundred references to <cfquery> and queryExecute and many queryBuilder (qb) calls for newer code.

Continue reading

List all foreign keys in SQL server

This post is not very cfml specific, but I am using this a lot with cfmigrations, so it might be worthwhile sharing. Recently I was doing some major restructuring of a database, and most of the time I try to script this using cfmigrations or commandbox-migrations. Recently I had to drop some tables and thought this would be easy with cfmigrations. So I fired up commandbox, and executed

migrate create dropOldTables

which creates a cfc named yyyy-mm-dd_dropOldtables.cfc in my resources/database/migrations map. If you didn’t use commandbox-migrations before, you have to install and init first, as described here. The migration command should be trivial, something like:

function up( schema, query ) {
   schema.drop("ipnumbers");
}
Continue reading

qb: Autodetecting your bind variables in SQL

In an ideal world, everyone is using qb or quick, and you really don’t know what a bind variable is. Before you discovered this ideal world, maybe you were using queryExecute and were executing queries like this one.

var q =queryExecute("Select * from users where userId = #url.UserId#");

This kind of code ( don’t do this in production ! ) is wide open for sql injection attacks. This post is not about sql injection, so we assume you were already so smart to use queryparams, so something similar to this.

var q = queryExecute(
  sql="Select * from users where userId = :myId",
  params =  { myId: { value = rc.UserId, cfsqlType = "integer" }
)
Continue reading

Order of elements in a qb array of structs

Recently someone asked in the coldbox Slack channel if the keys of a struct are always accessed in the same order. The answer is usually no , but if you know which keys are present it is quite simple to access them in the right order. But if you really want the keys in a fixed order you can create your structs this way:

MyOrderedStruct= structNew( "ordered"); 

Although the key order is seldom important we encountered such a case. We were building a control panel in VueJS where we displayed a list of DNS records based on a JSON array of structs.
These records can be exported to a comma delimited file, but since we were doing the export in some standard Vue component we were limited in the output format of the file. Keys showed up in the same order as in the JSON structs, which was undesirable for our clients.

Our output was based on a qb (QueryBuilder) query were the results were returned as an array of structs. Let’s say we were querying for id and name in our query, e.g:

return qb.from("records")
  .select("id,name")
  .get()
//wen serializing to json the result might look like this
//but we want id,name order instead of name,id
"data": [
      {
          "name": "SomeName",
          "id": "297A83FD-1715-416F-801B-44BE1743443A",
      },
      {
          "name": "Another item",
          "id": "892FECBC-84FA-4C5E-B494-525C3712F6A2"
      }
] 

If we want to change the order of the struct keys we could do some mapping on the array, e.q.

return items.map(function(item){
  var newitem = structNew( "ordered" );
  newitem.id = item.id;
  newitem.name = item.name;
  return newitem;
});

but this is quite inefficient for larger datasets. Qb is converting all queries to an array of structs, and we are creating structs again based on the qb results. Fortunately qb has a handy feature where you can define your own output format. In this case the queries in qb are directly converted to an array of ordered structs. The following code snippet was suggested by qb author Eric Peterson. Thanks Eric!

// CF2016+ and/or Lucee 5+
moduleSettings = {
    qb = {
        returnFormat = function( q ) {
            return queryReduce( arguments.q, function( acc, row ) {
                var rowStruct = structNew( "ordered" );
                for ( var columnName in q.columnList ) {
                    rowStruct[ columnName ] = row[ columnName ];
                }
                acc.append( rowStruct );
                return acc;
            }, [] );
        }
    }
}

Inserting NULL values in qb

I can highly recommend the qb library for all kind of database manipulations, for querying and data definitions. Qb hides a lot of complexities for database actions, and abstracts away differences between database engines.

Although very powerful, some options are really well hidden in the documentation or the source code. Recently I wanted to update a database table with null values. The normal syntax for updating values in a database table is:

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

If you have more complex requirements like inserting date values in the correct formats, you can specify query param options, e.g

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );

This query param syntax comes in handy when inserting NULL values. As we all know older versions of Adobe Coldfusion and Lucee can’t handle null values very well:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )

If you are using Lucee with full Null support you can leave out the null parameter, e.g

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

© 2021 ShiftInsert.nl

Theme by Anders NorenUp ↑