Archive for the ‘Projects’ Category

Let me start by stating :

It Works!

I choose, at times to set before myself some interesting goals. In this case it is the task of migrating a Microsoft Web Access Application to something with a bit more longevity. With Microsoft deciding that the Web Access platform is no longer useful to them they dropped it like a hot potato and ran, leaving those of us who decided to use the platform with very few options.

This blog post is not meant to be a “How do I do this – Please hold my hand.” kind of thing. Rather it is more of a framework for future projects I may have with this platform. If however, you do need a little hand holding, drop a comment, and I’ll see what I can do for you.

What I used:

Ubuntu Server 16.04.3 (Installed on dedicated hardware):

https://www.ubuntu.com/download/server

SQL Server 2017 installed on Ubuntu Server:

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Directions for SQL Server Ubuntu install:

https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu

Ruby and Rails:

https://gorails.com/setup/ubuntu/16.04

my versions:

Ruby 2.4.1p111 (2017-03-22 revision 58053)
Rails 5.1.4

Database Connectivity for SQL Server:

TinyTDS

https://github.com/rails-sqlserver/tiny_tds

activerecord-sqlserver-adapter

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter

Additional tools:

 schema_to_scaffold

https://github.com/frenesim/schema_to_scaffold

 

Server Connectivity and Development Tools

I am not a huge command line fan, but it is necessary to get all this working, here are the tools I use:

Putty

http://www.putty.org/

Webmin

http://www.webmin.com/

Notepad++

https://notepad-plus-plus.org/

 

Some Initial Gotchas:

  • You may be tempted to build a RoR environment in windows, don’t. Ruby and thus Rails were developed in a Linux world. If you don’t want to do this in Linux, look elsewhere for your development environment.
  • It is probably best with any NEW application, to build your DB in one of the three default DB engines. (SQLite, MySQL, or PostgreSQL) That being said, I had an existing DB and back office applications that were sitting on MS Access and needed to find a solution that easily meshed with that. The compromise was Microsoft’s SQL Server 2017 (installed on Linux)
  • If you are working with an existing DB, there are a few things you will need to be aware of:
    • RoR reserves the field name “type“. So, if you have a field in you DB named “type” you will need to address that before you do any importing or conversions. Alternately, if you cannot change the field name, you will need to add this line to “model” for any tables that include a field “type.”
      self.inheritance_column = "inheritance_type"

      as mentioned here:
      https://mattconnolly.wordpress.com/2012/06/01/rails-beware-a-column-named-type/

    • If you, as I do, use CamelCase (ex: tMyTable or fFirstName) in your DB entity naming, you will not be able to use the automated process for scaffold building described in this article. The automated process will actually change CamelCase to snake_case “fFirstName” to “f_first_name” when creating the scaffold structure. I was able to go back and change these to all lowercase (ex: fFirstName to ffirstname) not as readable, but it works. The alternative is of course to build the scaffold by hand, but that will make the conversion much more difficult. Note: this applies to all naming in your DB including Index’s, and foreign keys.
    • Index names are automatically created in Access. You will probably want to go back and modify/simplify them for better automation in the connection and scaffold building process.
    • Rails is designed to build a DB from the ground-up, it has it’s own set of default functionality that will probably not mesh well with a pre built DB.
      • Rails likes to do all the heavy DB lifting, it wants to keep track or even manage all of your indexes, associations, primary keys, foreign keys, etc. It sees a DB as a dumb data store, with all the logic being cared for in you Rails models.
      • Rails assumes your Primary Key is a field labeled “id”
      • Rails assumes your Foreign Keys look like “mytable_id”, where “mytable” is the name of the table which defines the foreign key field(s), however this can be overridden.

Now the Fun!

After you have installed everything and created your new Rails application, you will need to be in your application’s root directory for Rails Commands to work.

railsuser@myserver:~$ cd railsapp

You will need to edit a couple of files to tell the application to use your SQL Server DB.

Rails DB Settings

Edit the Gemfile to include these two lines:

gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'

and tell your application where to look for the DB by placing this code in your /config/database.yml file:

development:
   adapter: sqlserver
   host: xxx.xxx.xxx.xxx # IP address pointing to the hardware where your SQL Server is located
   port: 1433 
   database: myDatabaseName  # Name of the DB you are using for the Application
   username: DBAdmin
   password: secret

Note: This is specifically marked as the “development:” environment, changes will need to made to the “production:” settings in this file as well when you are ready to migrate to your production environment.

Alternately, you can make these changes to the “default:” settings, this will cause all the associated “*default” environments to use that.

Rails DB Schema Import and Setup

Review: https://codeburst.io/how-to-build-a-rails-app-on-top-of-an-existing-database-baa3fe6384a0

Now you need to get the schema by running this command:

rails db:schema:dump

This will build the /db/schema.rb file. A surefire way of knowing that you installed everything correctly…

Next, to use the schema_to_scaffold gem type the following:

scaffold

You will see something like this:

Looking for schema.rb in /home/railsuser/railsapp
0. /home/deploy/railsapp/db/schema.rb

Select a path to the target schema:

Type:

 0

You will get something like this:

Loaded tables:
0. table01
1. table02

Options are:
4 for table 4; (4..6) for table 4 to 6; [4,6] for tables 4 and 6; * for all Tables

Select a table:

Type:

 *

This will generate scripts for all the table scaffolds. (I found I was unable to use the range options – but that was probably me not typing what it was asking me to?)

Scaffold returns a list of scaffold creation scripts something like this:

rails generate scaffold table01 table02id:integer fieldxx:string --no-migration

rails generate scaffold table02 field01:integer field02:string --no-migration

The “–no-migration” part is important for us. If you create migration files, you are telling rails that you need these DB tables to be created or changed in some way. But we don’t need tables created or modified.

Next we need to run those scripts. If you are using Putty, simply highlight the script for a given table and right click on your mouse. This performs a cut/paste operation. Pressing enter will then run the script and create the scaffold for that table. It’s magic! Running these scaffold generator scripts will create things like “views” (web pages) and their controllers. It will also modify other files, such as /config/routes.rb

Pointing to the Default Web Page

Our next step is to change the default route (Home Page)

place a “root” definition in your /config/routes.rb file just below the “Rails.application.routes.draw do” line similar to this, but use the name of the controller and view you want to use as the root page.

root 'myviews#index'

We are pointing at the controller for the myview views. So the /config/routes.rb file should now start like this:

Rails.application.routes.draw do
  root 'myviews#index'

Now when you enter the url to your page in your browser – http://xxx.xxx.xxx.xxx:3000/ it will show the page you stipulated as your root page.

There is much more to do, but this will get you started.

(Please forgive me, but the posting and examples to follow rely heavily on Windows and Internet Explorer. (You can substitute Apple and Linux applications for most.)

Your web browser is a very powerful tool. It enables you to view many different types of files from within the framework of the browser.

For an example:

You can go to www.weuntangle.com/index.html and view my home page. This is expected, as it is a web page after all.

Now go here: www.weuntangle.com/examples/test.xls you will find that as long as you have Excel installed on your computer and are using Internet Explorer, you will be able to view this document “In Browser”

Your browser is capable of some terrific things. But it’s most primitive function is to show web pages, with all their links and pictures. Here is your most basic of web pages:

<html>
<head> </head>
<body> </body>
</html>

This bit of HTML scripting is very simple. Here are the components:

<html> </html>

This “tag” tells your browser that it is displaying an “html” document.

<head></head>

This tag is where bits of scripting and other tags go that pertain to background information for the browser. For instance you can place a <title> </title> tag in the <head> section and put a title at the top of the browser window.

<body> </body>

This section is where all the content of your page goes.
Now, the fun part. Open “Notepad” and type whatever comes to mind, once completed, save the document on your desk top. Name it “mypage.txt”

Close Notepad and return to you desk top. You should see you new document sitting there. If your desktop looks like mine, you may need to hunt for it.

Now that you see it, double click on it to open it again. What application is used? Notepad of course. Now close Notepad again. This time right click on mypage.txt and select rename on the menu that appears. In the box below your file rename it mypage.htm and hit enter. Windows will try to cover it’s backside by warning you that you could make this file unusable by doing this, but it’s ok. Click “Yes” and lets move on with our experiment.

With that done you should see the Icon that represents your file change to look like your default browser’s icon – that’s what we want, give it the old double click and watch what happens….

WOW! You just created a web page (OK an extremely mal-formed web page.) and without the taggings and sections we looked at earlier. This is because windows has associated the .htm (and .html) extensions to your browser and your browser is so nice that it is going to try and display the file no matter what. Remember that .xls file you opened earlier? Try this:

www.weuntangle.com/examples/test.htm (with Firefox it may complain before opening it)

Ugh! What a mess. What you just open is actually the Excel spreadsheet renamed with the .htm extension, but your browser tried very hard to display the file, simply because you told it to.

We are going to use the browsers ability to render pages to our advantage. The browser is very forgiving when it comes to webpage content, so it will try to render whatever is passed to it if it is named with an .htm or .html extension.

All this being said, Please take the time to learn html (or better yet xhtml) it will serve you well as you take on this serverless intranet project with me.

Note: you may belong to a company that has also locked down the types of files you can attach to emails. If this is the case you can sometimes rename your html and javascript files something like this:

Html file name:
mypage.htm

Renamed for emailing:
mypage.txt

When you later detach the file you will need to change the name back so you can use it.

Next up Javascript 101

Extremely locked down is what we are looking at here. I came up with this topic when I was working with a small department of a large company. They have limited web access, no ability to upload files via thumb drive, and CDs/DVDs are out of the question. So how do you share information, the latest department news etc. without sending emails to everyone? I began to wonder if I could put together an Intranet site for them without access to servers or even external web sites like Google or facebook.

The short answer is: Yes I could. The longer answer is: Yes, you can too, and I am going to help you do it.

What makes this sort of Intranet possible is that your browser (we are going to look mainly at Internet Explorer) is capable of doing some amazing stuff thanks to the power of a Netscape creation called Javascript, and Microsoft’s browser extensions called Active X controls. Most of the work will be done with Javascript, but for the DB access we are going to need Active X.

OK I hear you now. “But I thought you said no servers?” Remember, we are building this Intranet for a small department or Business, and true to my word, NO servers will be used in the building of our website.

The DB that we will be using (and only if you want a more interactive site) is MS Access. If you have it installed on your PC you’re ahead of the game. If not, we will need to get a little more creative, but it is still very doable. (You will not need to go out and get MS Access if you do not have it.)

For an example of what can be done,  zip on over to Joshua Faulkenberry’s site and see what he has created. Come right back though, OK?

Here are some things you will need to make this work.

  • First and foremost you will need a shared location for your files. The company mentioned above has a networked file share mapped as the I: drive on all the departments computers. So if I drop an Excel spreadsheet called “mysheet.xls” on this drive, everyone in the department can get to it by going to: I:\mysheet.xls
  • Next you will need to have some knowledge of, or at least a desire to learn some HTML, CSS, Javascript, and a little SQL. I am going to help you here, so don’t run off just yet if you don’t know a lick of web stuff. I hope to make this a gentle learning curve. For you HTML experts, stick around too, I hope to make this a learning experience we all can enjoy.
  • I would suggest you also become familiar with your company’s technology policies as well. Please don’t do this if you think you will run afoul of these rules and regulations.

As we go along we may run into more things we need. but for now, that’s it. Come back next time for our first look at what an Intranet is for and your first web page.