LLBLGenPro Lite with PostgreSQL and .Net

In this example I used:

LLBLGenPro Lite Version 5.1 (5.1.2) RTM - LITE  Build Date 24-Jan-2017, Licensee: LITE user
Npgsql -Version 3.1.10
CsvHelper -Version 2.16.3
log4net -Version 2.0.7
Visual Studio 2012 with Package Manager Console Host Version 2.8.60318.667

Few tips:

1. Generic part of adapter source code and DB Specific part ( Projects LLBLGenProLite51DBSpecific & LLBLGenProLite51Generic) were created following "LLBLGen Pro Runtime Framework v5.1 Documentation" documentation at llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/index.htm
bitbucket.org/hemantup/orm/src/HEAD/LLBLGenProLite51/LLBLGenProLite51.llblgenproj is the saved project of LLBLGenPro.exe which may be used to regenerate the classes later, if need be.
llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/Tutorials%20And%20Examples/tutorial_createproject.htm

2. The only tweak I had to do to generate the source code (on the top the instructions as mentioned in online help guide "LLBLGen Pro Runtime Framework v5.1 Documentation):
a) Put Npgsql.dll in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v5.1\
b) Some of you might also have to put DbProviderFactories entry under LLBLGenPro_x86.exe.config.
Otherwise you may get error:
Exception details:
=====================
Message: Failed to find or load the registered .Net Framework Data Provider.
Source: System.Data
Stack trace: 
   at System.Data.Common.DbProviderFactories.GetFactory(DataRow providerRow)
   at SD.LLBLGen.Pro.DBDriverCore.DBDriverBase.GetDbProviderFactory()
   at SD.LLBLGen.Pro.DBDriverCore.ConnectionDataBase.get_FactoryName()
   at SD.LLBLGen.Pro.Gui.Controls.WizardPages.MetaDataRetrievalWizard_Step_ConnectionData.<LoadDriverAndConnectionControl>b__16_1()
   at SD.LLBLGen.Pro.Gui.Classes.ApplicationIdleDispatcher.PerformWork()
   at SD.LLBLGen.Pro.Gui.Classes.ApplicationIdleDispatcherSingleton.Application_Idle(Object sender, EventArgs e)
   at System.Windows.Forms.Application.ThreadContext.System.Windows.Forms.UnsafeNativeMethods.IMsoComponent.FDoIdle(Int32 grfidlef)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   at SD.LLBLGen.Pro.Gui.Classes.GuiController.PerformAddMetaDataFromDatabaseAction()
   at SD.LLBLGen.Pro.Gui.Controls.ProjectExplorer._mainBarManager_ItemClick(Object sender, ItemClickEventArgs e)
   at DevExpress.XtraBars.BarItem.OnClick(BarItemLink link)
   at DevExpress.XtraBars.BarButtonItem.OnClick(BarItemLink link)
   at DevExpress.XtraBars.BarItemLink.OnLinkClick()
   at DevExpress.XtraBars.BarButtonItemLink.OnLinkAction(BarLinkAction action, Object actionArgs)
   at DevExpress.XtraBars.ViewInfo.BarSelectionInfo.UnPressLink(BarItemLink link)
   at DevExpress.XtraBars.Controls.CustomLinksControl.OnMouseUp(MouseEventArgs e)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at DevExpress.XtraBars.Controls.CustomControl.WndProc(Message& msg)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Inner exception: <null>

3. In the Console project I created, I had to make DbProviderFactories entry in app.config in addition to installing Npgsql.

For other details, you may refer to comments in bitbucket.org/hemantup/orm/src/HEAD/LLBLGenProLite51/LLBLGenProLite51Console/Program.cs in sample code.



Working sample code is available at bitbucket.org/hemantup/orm/src/HEAD/LLBLGenProLite51/






This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Symbiotic_Data_Provider_PostgreSql_x64 with PostgreSQL and .Net

In this example I used:
Symbiotic_x64 -Version 4.0.4.1
Symbiotic_Data_Provider_PostgreSql_x64 -Version 2.0.0
Npgsql -Version 3.1.10
CsvHelper -Version 2.16.3
log4net -Version 2.0.7
test_table.cs using Symbiotic Code Generator

Working sample code is available at bitbucket.org/hemantup/orm/src/HEAD/SymbioticDataProviderPostgreSql2/

This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Uni.ORM with PostgreSQL and .Net

Uni.ORM with PostgreSQL and .Net

In this example I used:

Npgsql -Version 3.1.10

CsvHelper -Version 2.16.3

log4net -Version 2.0.7

Uni.Extensions -Version 1.1.8 (  if you use Uni.Extensions 1.1.9, along with Uni.ORM 1.4.4, it will give error Field not found: 'Uni.Extensions.UniExtensions.stringType'.)

Uni.ORM -Version 1.4.4

Working sample code is available at

bitbucket.org/hemantup/orm/src/HEAD/UniORM144/
bitbucket.org/hemantup/orm/src/HEAD/Scripts/apps/10/



Probably you need to be careful about the active sessions with this ORM:







I got below mentioned error, if used more than 20 execution cycles with above said custom utility


Npgsql.NpgsqlException was unhandled
  HResult=-2147467259
  Message=The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)
  Source=Npgsql
  ErrorCode=-2147467259
  StackTrace:
       at Npgsql.ConnectorPool.WaitForTask(Task task, NpgsqlTimeout timeout)
       at Npgsql.ConnectorPool.Allocate(NpgsqlConnection conn, NpgsqlTimeout timeout)
       at Npgsql.NpgsqlConnection.OpenInternal()
       at Npgsql.NpgsqlConnection.Open()
       at Uni.Orm.UniOrm.NewConnection()
       at Uni.Orm.UniOrm.ExecuteScalar(CommandType commandType, String schema, String package, String commandText, Options options, Object[] args)
       at Uni.Orm.UniOrm.ExecuteScalar[T](CommandType commandType, String schema, String package, String commandText, Options options, Object[] args)
       at Uni.Orm.UniOrm.TryInvokeMember(InvokeMemberBinder binder, Object[] args, Object& result)
       at CallSite.Target(Closure , CallSite , Object , String , String , String , Options , Object[] )
       at Uni.Orm.UniOrm.Count(String schema, String table, String where, Options options, Object[] args)
       at UniORM144.UniORM144Test.Main(String[] args) in c:\891\orm\orm\UniORM144\Program.cs:line 114
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

i-nercya EntityLite with PostgreSQL and .Net

In this example I used:
Npgsql -Version 3.1.10
EntityLite -Version 1.12.1
DataLayer.cs  from hemantrohtak.blogspot.com/2017/02/generate-datalayer-for-i-nercya.html
CsvHelper -Version 2.16.3
log4net -Version 2.0.7

Working sample code is available at bitbucket.org/hemantup/orm/src/HEAD/InercyaEntityLite1121/





This is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Generate datalayer for i-nercya EntityLite 1.12.1 with PostgreSQL and .Net

I tried to install EntityLite 1.12.1 and Npgsql 3.1.9 for this. [ in .Net 4.5 targeted console app] But ended up with error:

Error 2 Running transformation: System.ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.
   at System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName)
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.get_Factory()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.OpenConnection()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.SetViewsByEntity()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.DataLayerGeneration.Initialize()
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.Render(DataLayerGeneration generation)
   at Microsoft.VisualStudio.TextTemplatingC12866A572DD11EF15A986313BFF838D4E56BEC87A6A4FDE0CFFA72BBCA7470835F0A9B363151B6BF2A47F79BBBA8A3506666063B8252F87E39E5DFC255B20C6.GeneratedTextTransformation.TransformText() C:\891\i_nercya_EntityLite_1121_generate\DataLayer.tt 1 1 i_nercya_EntityLite_1121_generate

In case of other ORM's similar error was resolved by DbProviderFactories entry in app.config of the same project. But in case of  EntityLite 1.12.1, I had to put   Npgsql 3.1.9 dll in gac and make DbProviderFactories entry in devenv.exe.config.

But still my datalayer was not generated. Now I got the error:

Error 2 Running transformation: System.InvalidCastException: Specified cast is not valid.
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.GetSchema(String tableOrView, DbConnection cn)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.GetSchemaUnion(EntitySetting entitySetting, DbConnection cn)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.SetFieldsMetadataByEntity()
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.DataLayerGeneration.Initialize()
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.Render(DataLayerGeneration generation)
at Microsoft.VisualStudio.TextTemplating7F3203888EE90D7643162256DEB8BCA2F8341541B1C705EDCF3B6214F0C0F85E31AB17E0A0D48688C13A80D08802FC9F390D40D33018DFC341958870E4AB73A1.GeneratedTextTransformation.TransformText() C:\891\inercyaEntityLite1121\DataLayer.tt 1 1 inercyaEntityLite1121

EntityLite 1.12.1 was released back in September 15, 2015 and targeting .NETFramework 3.5 Client. This could have been the culprit for Specified cast is not valid. So I removed previously installed dll's in GAC and DbProviderFactories entries in devenv.exe.config. If you follow step by step actions mentioned below, you may avoid such errors I faced as mentioned above:


0. create a database ,a table as mentioned in comment section of bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/Program.cs


1. Create a project targeting .Net 3.5:


2. In this project if you try to install npgsql anything above 2.2.7, you get error like as mentioned below:

PM> Install-Package Npgsql -Version 3.0.0-beta0001 -Pre
Installing 'Npgsql 3.0.0-beta0001'.
Successfully installed 'Npgsql 3.0.0-beta0001'.
Adding 'Npgsql 3.0.0-beta0001' to i_nercya_EntityLite_1121_generate.
Uninstalling 'Npgsql 3.0.0-beta0001'.
Successfully uninstalled 'Npgsql 3.0.0-beta0001'.
Install failed. Rolling back...
Install-Package : Could not install package 'Npgsql 3.0.0-beta0001'. You are trying to install this package into a project that targets '.NETFramework,Version=v3.5', but the package does not
contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.At line:1 char:1
+ Install-Package Npgsql -Version 3.0.0-beta0001 -Pre
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Install-Package], InvalidOperationException
    + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand


So you have to go for Npgsql 2.2.7

PM> Install-Package Npgsql -Version 2.2.7
Installing 'Npgsql 2.2.7'.
Successfully installed 'Npgsql 2.2.7'.
Adding 'Npgsql 2.2.7' to i_nercya_EntityLite_1121_generate.
Successfully added 'Npgsql 2.2.7' to i_nercya_EntityLite_1121_generate.

3. Now go for EntityLite 1.12.1 ( dependencies are resolved here):

PM> install-Package EntityLite -Version 1.12.1
Attempting to resolve dependency 'EntityLite.Core (= 1.12.1)'.
Attempting to resolve dependency 'NLog (= 2.1.0)'.
Attempting to resolve dependency 'Microsoft.SqlServer.Types (= 10.50.1600.1)'.
Installing 'NLog 2.1.0'.
Successfully installed 'NLog 2.1.0'.
Installing 'Microsoft.SqlServer.Types 10.50.1600.1'.
Successfully installed 'Microsoft.SqlServer.Types 10.50.1600.1'.
Installing 'EntityLite.Core 1.12.1'.
Successfully installed 'EntityLite.Core 1.12.1'.
Installing 'EntityLite 1.12.1'.
Successfully installed 'EntityLite 1.12.1'.
Adding 'NLog 2.1.0' to i_nercya_EntityLite_1121_generate.
Successfully added 'NLog 2.1.0' to i_nercya_EntityLite_1121_generate.
Adding 'Microsoft.SqlServer.Types 10.50.1600.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'Microsoft.SqlServer.Types 10.50.1600.1' to i_nercya_EntityLite_1121_generate.
Adding 'EntityLite.Core 1.12.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'EntityLite.Core 1.12.1' to i_nercya_EntityLite_1121_generate.
Adding 'EntityLite 1.12.1' to i_nercya_EntityLite_1121_generate.
Successfully added 'EntityLite 1.12.1' to i_nercya_EntityLite_1121_generate.

4. Retrieve the full name of dll's:
C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Mono.Security.dll
C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Npgsql.dll
Code for retrieving name is in bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/Program.cs

5. Install these two dll's in gac:
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>gacutil -i "C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Mono.Security.dll"
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>gacutil -i "C:\YourProjectPath\packages\Npgsql.2.2.7\lib\net35\Npgsql.dll"

6.1 In C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\devenv.exe.config ( or the respective config of devenv.exe, as per your installation, you may get path for devenv.exe by viewing the properties of VS shortcut.) make DbProviderFactories entry like:
 <system.data>
        <DbProviderFactories>
            .
.
.
<remove invariant="Npgsql" />
.
.
.
 <add name="PostgreSQL Data Provider"
           invariant="Npgsql"
           description=".Net Data Provider for PostgreSQL"
           type="Npgsql.NpgsqlFactory, Npgsql, Version=2.2.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
        </DbProviderFactories>
    </system.data>

Just make sure you use correct assembly fullname as retrieved in step 4
6.2 Restart Visual studio

7. Copy paste code similar to as mentioned in the attached code's bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/DataLayer.tt. Right click on this tt file and click "Run Custom Tool".
Save DataLayer.cs generated for later use in a separate folder for future use.
8.1 Close Visual studio.
8.2 Remove DbProviderFactories customization you did in 6.1
8.3 Remve the two dll's as in step 5, from gac
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>Gacutil -u "Npgsql, Version=2.2.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC>Gacutil -u "Mono.Security, Version=2.0.0.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756"


 So, bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/DataLayer.cs generated in step 7 is your output of this activity.

Full code available at:
bitbucket.org/hemantup/orm/src/HEAD/i_nercya_EntityLite_1121_generate/

LINQ to PostgreSQL with PostgreSQL and .Net

n this example I used,
(in order)
Npgsql -Version 3.1.10
linq2db.PostgreSQL -Version 1.7.6
log4net -Version 2.0.7
CsvHelper -Version 2.16.3

Working sample code is available at bitbucket.org/hemantup/orm/src/HEAD/LINQtoPostgreSQL175/




This post is related to : https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html
 

MicroLite with PostgreSQL and .Net

In this example I used,

MicroLite -Version 6.3.2
Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3

Working sample code is available at bitbucket.org/hemantup/orm/src/HEAD/MicroLite6/





This post is linked to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

FluentData with PostgreSQL 9.6 and .Net 4.5

In this example I used,

Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3
FluentData -Version 3.0.1.0

Working sample code is available at:

 bitbucket.org/hemantup/orm/src/HEAD/FluentData3/










This post is related to https://hemantrohtak.blogspot.com/2017/02/entityframework6npgsql-with-postgresql.html

NHibernate with PostgreSQL and .Net

In this example I used,

NHibernate -Version 4.1.1.4000
Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3

1. Since I am using Sequence for Id, its worth noting that even though insert doesn't fire query to insert record immediately, but retrieves sequence.
2. Also, transaction will not be able to control rollback for sequences.
3. Actual SQL queries fired are available for review only in debug mode: setings.LogSqlInConsole = true;

For full version of code please visit: bitbucket.org/hemantup/orm/src/HEAD/NHibernate4/








This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

EntityFramework6.Npgsql with with PostgreSQL 9.6 and .Net 4.5

In this example I used,

Npgsql -Version 3.1.10
log4net -Version 2.0.7
CsvHelper -Version 2.16.3
EntityFramework6.Npgsql -Version 3.1.1


Two things worth noting here,
1. If I would have installed directly EntityFramework6.Npgsql -Version 3.1.1, it resolve dependency to  Npgsql -Version 3.1.0, which didn't work in my case, so I chose to go with Npgsql -Version 3.1.10

2. I had to make manual entry for DbProviderFactories in app.config to get it work.

If you get stuck somewhere, delete your bin, obj and packages folder . Then try the sample code mentioned below again.

Working sample code is available at:

 bitbucket.org/hemantup/orm/src/HEAD/EntityFramework6/

This post is linked to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

OrmLite with PostgreSQL and .Net

In this example I used:
Npgsql -Version 3.1.10
ServiceStack.OrmLite.PostgreSQL -Version 4.5.6
CsvHelper -Version 2.16.3
log4net -Version 2.0.7


Sample code below gives an idea how to use OrmLite with PostgreSQL and .Net. PostgreSQL server is installed on localhost in the example below:
Visual Studio Project along with full version of code:

bitbucket.org/hemantup/orm/src/HEAD/OrmLite/





This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html

Create/Edit/Delete a Post Sample in PHP MYSQL ANGULARJS

Code available at:
Project2

How to make a user admin:
Register any user in the deployed version of your application, and in the DB mark isadmin to 1 for that user in users table.




Important:
1. Admin can do view edit and delete to all posts. He can also create new post.
2. Non admin user can view all posts. He can edit and delete his own post. He can create new post.
3. Anonymous user can view all posts , he can not do create edit or delete.



Known issues:
1. In the post title description and body only a-z, A-Z, 0-9, . and space are allowed.
2. If user is on one post, to navigate to next post, he must click all posts tab and go to the desired post.
3. Password is not encrypted in DB.
4. Password is visible on register page unlike login page.
5. In firstname, lastname and password only a-z, A-Z, 0-9 allowed.
6. Password length is 5 to 50 characters.
7. Email is standard 254 characters.
8. Firstname and last name are 3 to 100 characters
9. Register doesn't implement captcha like the first project.



Highlights:
1. When user login, only then links like create post are available. Otherwise he gets register link instead of create post.
2. Angular is used to achieve single page structure.

Web Analytics

Web Analytics constitutes the techniques used to collect/analyze usage information of a website or related interfaces to improvise usability and customer satisfaction as a goal. Say, an organization just launched a new product and they want to see how successful there marketing had been to drive users to new product information page, web analytics is the answer to such scenarios. Typically a successful web analytics solutions will be helpful to formulize online strategies with least efforts and cost involved.

Typically, all the web analytics tools available today in market either study the server logs or require special JavaScript tags to be added in each and every page and some service is called on each event. In either case, owner of the application may be interested in location, interest, clicks, flow used to reach target page, time duration of the visit etc. by the visitor of the application. These are the inputs that may be used to improvise the application. An intelligent and successful web analytics solution must go one step ahead and based on above observations, will be giving suggestions to the owner of website, how to use this information in better manner.

A web developer must be keep up to date with available options, which he may suggest while the application is being designed. Based on budget and man power available to monitor application, right strategy must be used and implemented. If the stakeholders go for a different web analytics solution at later stages of development, this might involve a lot of rework and put extra pressure on timelines and budget of the project.

Web Analytics Solutions

Free Analytics Tools

• Google Analytics
• Google Website Optimizer
• Piwik
• Open Web Analytics
• Site Meter
• Stat Counter
• Webalizer
• Analog
• Awstats
• Prosper 202

Cheap Paid Analytics Tools

• Clicky
• Mint
• WordTracker's Strategizer
• Adblock Analytics
• Screaming Frog

Affordable Business Analytics Tools

• Weblog Expert
• Clicktale
• Crazy Egg
• Visual Website Optimizer

SEO Update Tracking

• Not Provided Count
• Chart Intelligence
• Panguin Tool
• Sistrix Google Updates
• Algo update history

General Search Marketshare Stats

• StatCounter
• Clicky
• Experian / Hitwise
• NetMarketshare
• comScore & Compete.com also offer marketshare stats

Usability Feedback

• User Testing
• Feedback Army
• Silverback
• Users Think
• UX Project Checklist

Expensive / Enterprise Premium Tools

• Google Analytics Premium
• Adobe Marketing Cloud
• WebTrends
• comScore's Digital Analytix Suite
• IBM's Coremetrics

Recent Advancements

• Google Accelerated Mobile Pages (AMP):  In October 2015, Google introduced AMP, which may help better serve the mobile users and improve Google ranking.
• Instant Articles by Facebook: This allows content owners to upload entire stories, and users to read them, without leaving the Facebook site.
• Transparency by Government in the Analytics: Government online practices are improving now and they are releasing even more behind the scene data using analytics tools.
• NewsLynx: With launch of this tool, burden of manually collecting and aggregating analytics may be off loaded to this tools and faster growth & innovation is expected.
•  Google Calculated Metrics: Rather than downloading offline usage data and manipulating using custom formulas, with improvements in Google analytics, this may be done with less efforts using Calculated Metrics.
• Need to track social platforms: There was a time when Google was the only giant being considered to drive traffic, but these day various social platforms are being equally important. So the need to improve and track traffic from social platforms is ever increasing since last few years.
• Single page Applications: There was a time when visiting one page to another was the only way to fetch content from server in a website, but with advancement like angularJS, single page applications are becoming more popular, so the way page visits are recorded have changed with time.
• Video Analytics: With better bandwidth and internet speed, now more ideas may be floated around the web using video content, so the need and ability to track video content has grown these days.

References

Kanik, A. (2015, December 23). A look back at this year's biggest advances in analytics. Retrieved April 24, 2016, from https://ijnet.org/en/blog/look-back-years-biggest-advances-analytics

Losowsky, A. (2015, October 16). What makes us different. Retrieved April 24, 2016, from https://coralproject.net/makes-us-different/

SEO Book. (2013). Web Analytics Tools. Retrieved April 24, 2016, from http://tools.seobook.com/analytics-tools/

Zheng, J. G., & Peltsverger, S. (2015). Web Analytics Overview. Retrieved April 24, 2016, from https://www.researchgate.net/publication/272815693_Web_Analytics_Overview

a simple PHP mySQL example

Important: All the bulleted points’ headings are hyperlink to code) Common code link

1.      .htaccess :   This is used to set home page of site something else than index.* Also, any page opened like *.htm is interpreted as *.php  This is done do hide that its  a php site

2.     img Folder:   files in this folder serve as random backgrounds for captcha.

3.     connect.php :  This code loads db details and creates a connection to db.

4.     dbHandler.php: This code provides common methods which are intermediate functionalities specific to application and use connect.php to talk to DB.

5.     dbdetails.php:  When code is deployed at new server, these DB details will change.

6.     captcha.php: This is the code to generate captcha as an image output to browser. Treat this file as an image . As per config in htaccess , you can refer it as captcha.htm also. This will generate two random numbers and save the output in session for future input verification. Captcha puzzle is printed on random location in a random image chosen.

7.     hkproject1.sql : This is to generate a table in db with required fields to save opt ins.

8.     register.php : This is the main input form where user registers. Major php code for this form is in registersubmit.php

9.     sale.php: This is the basic landing page with link to signup ( register.htm /register.php)

10.  thankyou.php: This page is opened after successful submission on register.php and simply shows a success message based on query string parameters.

1. MySQL : One mistake I did here was, I didn’t pay much attention to environment I used for dev and production. I developed on local using latest xampp ( Windows based)  and deployed on goDaddy linux based  localhost MySQL.  On dev box root was used as user and blank as password, lucky me that I kept configurations in separate file, so edits while deployment were easy.  One more thing, after creation of new DB on goDaddy, it takes a while , before you could actually connect to the DB, for me connection started working after 3 hrs, during these three hrs I was doing a lot of hook and crook to connect to db on production. Obviously, turned a waste of time after three hrs, since it was only first time issue to be faced after DB creation.

One more thing here, in the table we store opt ins, I created an extra column of type timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , so that it may be tracked when user actually signed up, just in case, this info is required in future. Also , ALTER TABLE `signups`  ADD PRIMARY KEY (`email`(256)); The email was kept primary key here, to avoid multiple signups by same user again and again at DB level itself.

2. DB access:  In PHP , which methods I use to connect to MySQL greatly depend on what version of PHP I am using on both devand production environment. So be extra careful here to use similar dev box as production.  For some fields, I wanted to allow deliberately some characters like ‘ in the field, so I had to do $firstname = $this->conn->real_escape_string($firstname);  before putting value in the query.

3. General :

·         At many places, while development, I actually wanted to see errors and warning which were being suppressed by framework, so  had to use : error_reporting(E_ALL);

·         At one place session start code was required to be moved to top of the php file, before any kind of html even, this seems to be weird behavior of php

·         I thought, before actually starting session, it was always wise to check existing one like :

if ((function_exists('session_status') && session_status() !== PHP_SESSION_ACTIVE) || !session_id()) {

    session_start();

}

·          On PHP side, it was easier to validate email format like :

if (!filter_var($email, FILTER_VALIDATE_EMAIL) === false) {

        $email = strtolower($email);

        $emailValid = true;

    } 

Here I specifically converted email to lower, because I wanted to keep this field as primary, and you never know end users behaving as monkeys who sometime use same email with some characters upper case.

·         On success of submission of data, I took user to thankyou page with variables in query string. But since I was trying to use query string I made sure to use urlencode  and decode  on sender and consumer side.

·         It is always advisable to hide page extensions to make it difficult to hackers, so all my pages in my deployed version look like *.htm  This I achieved using .htaccess   entry : RewriteRule ^(.*)\.htm$ $1.php [NC]

·         Default page in my application was different than index.*, so I used RewriteEngine on  ( enter) DirectoryIndex brrr.php in .htaccess

4. Custom Captcha:  For mathematical captcha, I used sessions to validate user input. I had thousands of images in a fixed folder. I picked a random image from this folder using scandir($dir);  $randomImage  =  $dir . "/" . $images[rand(2, sizeof($images)-1)];

On the top of this image , using imagestring, I printed the string, whose answer is saved in session. $im =imagecreatefrompng($randomImage); $textcolor = imagecolorallocate($im, 0, 0, 0); $randX = rand(0,100); $randY = rand(0,200); imagestring($im, 5, $randX, $randY , $displayText , $textcolor);

header('Content-Type: image/png'); imagepng($im); imagedestroy($im);

Please note that text is also printed at random location on image.

On UI I also gave a refresh button, using Jquery code above mentioned php was reloaded and new answer is saved in session with altogether new captcha to display:

  $(document).ready(function () { $("#reloadCaptcha").click(function () {                $("#myCaptchaImage").attr("src", "captcha.htm?randomToRefresh=" + (Math.random() * 10));

                $("#answer").val("");                ApplycssValidation()

            });

        });

Please don’t get puzzled with Math.random in javascript code, which is just to avoid caching of captcha image content on client side.

5. CSS / html / UI / look and feel and validations on client side:   Even though this assignment was specifically concentrated on PHP / MySQL, but moral responsibility of a developer is to make sure, it is usable.  So I reverse engineered manywordpress templates to make something good looking , jquery/javascript validation obviously had to be form specific, but styles are easy to copy paste , so I did .