All Things Techie With Huge, Unstructured, Intuitive Leaps

MySQL - Getting a List of Users by who logged in last

I have a list of users.  I collect their activity. I want to get a list of users, but I want to order it by who logged in last.  Some users haven't logged in in months and don't appear in the login table.  There are two tables.  One is a users table and the other is a loginTable.  The login time is recorded by a timestamp.  Here is one way of getting the list and ordering by the last login.


SELECT users.id, MAX(loginTable.timeIn), loginTable.userId  FROM  loginTable
right join users on loginTable.userId = users.id
ORDER BY MAX(loginTable.timeIn) DESC ;

The right join insures that we get all of the users, even those who don't appear in the loginTable.  This is a slow query for a large user table, but since we are collecting metrics it doesn't matter.

Hopes this help someone.

Smart Road JSON or XML Template For Messaging and Data Transmission and Receiving



With the Internet of Everything here among us, and GPS is ubiquitous in vehicles, and cars will have full internet capability, it will not be long before we have Smart Roads.  Each road will have an IP address.

I began wondering what the data package would look like for Smart Roads, and then I was struck with the axiom that the best way to predict the future is to invent it.

So without further ado, I cracked together and XML file of what the Smart Road dataset would look like.  Here is my first crack at creating a Smart Road Data Standard:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Smart Road Markup Language version 1.0-->

<smart_road>  
<!-- Main Element.  It consists of a header, data and trailer elements. -->


    <header>
      <!-- Header Element.  -->

      <ref_number></ref_number> <!-- This could be a database Primary Key Identifier.  -->
      <country></country>
      <state></state>
      <province></province>
      <county></county>
      <township></township>
      <ip6_address></ip6_address> <!-- Each Smart Road will have their own IP address.  -->
      <start_gps></start_gps>
      <classification></classification> <!-- There could be subclassifications like carriageways etc.  -->
      <end_gps></end_gps>
      <length_kms></length_kms>
      <maximum_speed_limit></maximum_speed_limit>
      <options></options> <!-- This could be a control element for suppressing some header info on subsequent exchanges.  -->

     </header>

  <data> <!-- Message Section.  -->
  <data_sent>
     <general_data> <!-- This section contains general data sent to the vehicles  -->
            <alerts>
                   <current_alerts></current_alerts>
                   <upcoming_alerts></upcoming_alerts>
             </alerts>
            <flags></flags>
           <messages>
              <alerts></alerts>
              <construction></construction>
              <law_enforcement></law_enforcement>
              <traffic></traffic>
              <weather></weather>
             <misc></misc>
            <user_specific>
                 <destination_address></destination_address>
                 <ack_flag> </ack_flag>
                <message_payload></message_payload>
                <delivery_receipt></delivery_receipt>
            </user_specific>
         </messages>
    </general_data> 
      <location_specific_data><!-- This section contains location-specific sent to the vehicles  -->
          <current_gps_marker>
               <current_maximum_allowable_speed></current_maximum_allowable_speed>
                <alerts>
                      <current_alerts></current_alerts>
                      <upcoming_alerts></upcoming_alerts>
                </alerts>
                <flags></flags>
               <messages>
               <alerts></alerts>
               <construction></construction>
               <law_enforcement></law_enforcement>
               <traffic></traffic>
               <weather></weather>
              <misc></misc>
               <user_specific><!-- It is anticipated that if a vehicle's onboard messaging is not working, one can billboard messages -->
               <!-- This can also be used to send violation notices and service-related messages to the vehicle -->
                     <destination_address></destination_address>
                     <ack_flag> </ack_flag>
                    <message_payload></message_payload>
                    <delivery_receipt></delivery_receipt>
               </user_specific>
              </messages>
       </current_gps_marker>
      </location_specific_data>
   </data_sent>
   <data_received>
        <vehicle>
           <type></type>
           <description></description>
           <direction></direction>
           <velocity></velocity>
           <timestamp></timestamp>
        </vehicle>
   </data_received>
  </data>

  <meta-data>
  <!-- Big Data meta data on usage stats etc.-->
  </meta-data>
  <trailer>
    <protocols_supported> </protocols_supported><!-- Various devices will have their own native Smart Road protocols -->
    <device_types></device_types>
    <end></end>
    
  </trailer>
</smart_road>

This is just a first iteration.  It needs to be tested and validated in real time.  It is anticipated that the vehicle will send its GPS coordinates to the database defined by the IP address, and the location specific data will be returned.

Java - How to calculate and find out how many seconds to an event in the future

Java lesson.  Let's suppose that you have an event happening in the future.  The date and time of that event is stored in a database and you want to calculate how many seconds there are to that event. Once you get seconds, you can divide by 60 to get minutes, and again by 60 to get hours and then by 24 to get days etc.

Here is a code snippet that lets you do that:

Date now = new Date();
Timestamp eventStart = rs.getTimestamp("eventStart");
GregorianCalendar gc = new GregorianCalendar();
gc.setTime(eventStart);
Date endInterval = gc.getTime();
long difference = Math.abs(endInterval.getTime() - now.getTime());
long secondsToEvent = difference / (1000);

Date is java.util.Date. Timestamp is java.sql.Timestamp.

Hope this helps someone.

How to find and show line numbers in Wordpad



I had an exception, an error that was being thrown from a compiled .jsp file.  Strangely, the console would not give me the line number of the uncompiled jsp, but rather it gave me the line that was causing the exception in the compiled jsp file that was in the work directory.

I didn't feel like opening the file in text editor in Eclipse to locate the line, so I opened the jsp_java file with Wordpad.  The trouble is that Wordpad doesn't show line numbers.  I found the line numbers in Wordpad with this hack:

1) Select the entire text with Select All (CRTL +A will do it).

2) Expand the bullets menu item and click on the numbered list eg 1. 2. 3. etc

3) Every single new line will now be numbered and you scroll down to the offending line.

Hope this helps someone.

Code Snippet for Creating Secure Token in Java



Tokens can be used for many things.  Among them are Single Sign On, or saving an identification number without exposing it to the web.  Here is a quick code snippet to create a 16 character token

First you need an import:

import java.security.SecureRandom;

here is the code snippet:

String token1 = null;
SecureRandom secureRandom = new SecureRandom();
String secString = new BigInteger(130, secureRandom).toString(32);
token1 = secString.substring(0, 15);  //creates a 16 character token

Alter the substring end value to change the size of the token.

Hope this helps someone.