This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html
Wednesday, March 8, 2017
Create database and content using psql
This post is related to https://hemantrohtak.blogspot.com/2016/03/is-entity-framework-best-performing.html
Sunday, February 26, 2017
Start/Stop/Get details for Windows Services
If you are busy with multiple assignments and cater needs from multiple projects, or may be own a laptop with very little resources, you may want to manipulate windows services active on your laptop by Windows PowerShell.
For example, you may keep handy a .csv targeting one assignment, when you start working on that assignment simply stop other bulky windows services and activate the one you are interested in.
Some helpful commands:
#Get-Service | select-object status -unique
#Get-Service | where {$_.status -eq "Running"}
# get all windows services running:
Get-Service | select Name, CanPauseAndContinue, CanShutdown, CanStop, DisplayName, MachineName, ServiceName, ServiceHandle, Status, ServiceType, StartType, Site, Container, @{Name=’RequiredServices’;Expression={[string]::join(“;”, ($_.RequiredServices.Name))}} , @{Name=’DependentServices’;Expression={[string]::join(“;”, ($_.DependentServices.Name))}} , @{Name=’ServicesDependedOn’;Expression={[string]::join(“;”, ($_.ServicesDependedOn.Name))}} | Export-Csv c:\gm\servs.csv;
#After analysis of output say, you want to start 3 of them and stop 2, but leave one untouched still be in the csv, create a csv something like:
#(consider the dependencies listed in output above to decide order)
#"Name","Start","Stop"
#"MSSQL$HK2012SQL","TRUE","FALSE"
#"SQLAgent$HK2012SQL","TRUE","FALSE"
#"postgresql-x64-9.5","TRUE","FALSE"
#"wuauserv","FALSE","TRUE"
#"wlidsvc","FALSE","TRUE"
#"RpcSs","FALSE","FALSE"
import-csv "C:\gm\input.csv" | foreach-object { write-host $_.Name; if([bool]::Parse($_.Start)){ Start-Service $_.Name}; if([bool]::Parse($_.Stop)){ Stop-Service $_.Name};}
sample commands at : bitbucket.org/hemantup/orm/src/HEAD/Scripts/services.txt
But if you are more into security and want control over what is happening over your laptop above mentioned might not be sufficient.
You might need to to deep dive into processes like :
Get-Process | Export-csv "c:\gm\pr.csv"
But if you are more into security and want control over what is happening over your laptop above mentioned might not be sufficient.
You might need to to deep dive into processes like :
Get-Process | Export-csv "c:\gm\pr.csv"
Friday, February 17, 2017
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
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
Thursday, February 16, 2017
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
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
Wednesday, February 15, 2017
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
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
Tuesday, February 14, 2017
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
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:
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:
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/
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/
Sunday, February 12, 2017
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
(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
Saturday, February 11, 2017
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
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
Friday, February 10, 2017
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
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
Thursday, February 9, 2017
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
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
Wednesday, February 8, 2017
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
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
Tuesday, February 7, 2017
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
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
Thursday, February 2, 2017
Does Anti-virus software slow down your machine ?
If you are about to disable your Anti-virus and other security related plugins/software on your machine because it slows down your machine, this post is for you.
github.com/beefproject/beef is the The Browser Exploitation Framework(BeEF) available with default installation of Kali Linux. A black hat hacker could refer it's hook.js JavaScript file in any page, which if opened on victim's browser, hacker has the full control on victim's browser to execute commands beyond imagination e.g. getting all the browser cookies, extensions information, control web cam, pop-ups to enter passwords while browsing legitimate websites and so on. For complete list refer to github.com/beefproject/beef/tree/master/modules.
How BeEF tool works:
It keeps on hitting BeEF server from victim's client browser to get updated version of hook.js (interval defined by Config yaml at server) and execute it, when server side Utility of BeEF server attacks, say attacker run command give me webcam, a different version of hook.js is fetched to victim in next periodic call from browser to BeEF server: github.com/beefproject/beef/blob/master/modules/browser/webcam/command.js in this example. And victim client post results to attacker as per new hook.js given to it.
You may verify such actions on client machine using F12 ( Developer Tool) > Network tab in most of the browsers like chrome,IE, Mozilla etc or using fiddler.
How to defeat this tool:
1. If this hook.js periodic hit is blocked on victim's browser and notify victim in popup, its a temporary fix. The best place for this stuff to do could have been a utility running on client machine/browser code itself/extension to browser.
2. You may use unhook code in your browser extension very similar to github.com/beefproject/beef/blob/91cc7ed873f26a4d633f6306b34aa6af06932d49/modules/browser/unhook/command.js ( The tool use the code mentioned to stop watching (unhook) the victim.
3. Another method could be blocking the Attacker's domain altogether as done by
chrome.google.com/webstore/detail/vegan/longcaclchhmdpgcdjicmaghmpbdidlj (Vegan Chrome extension)
4. "utility running on client machine" called anti-virus have updated definition to let the system aware of these type of attacks. For example
Windows default anti-virus is aware of many attack vectors BeEF tool uses like Exploit: JS/Aimesu.A, Trojan: Win32/Spursint.F!cl , Exploit: JS/ShellCode.gen
Bottom line is you must not disable your Anti-virus software, just because it slows down your machine. Above all, you must not open malicious websites which may have been using hooks from plenty of such exploitation tools. Might be, the tool they are using is custom made and no anti-virus software is yet aware of attack vectors and how it works!! Typically, websites which offer you pirated content cost you much more than you could think of.
When it comes to government organizations and financial institutes, there is a reason they block everything else than trusted web addresses and domains. Even the most sophisticated technologies may be vulnerable to Cross-Site Scripting (XSS) attacks and attacker could inject just hooks in supposedly secured websites.
Wednesday, December 14, 2016
Get your location on google maps and find distance from a hosted IP/domain name.
Purpose
A lot of times, I am connected to internet in public place
and don't know from where internet signals are coming, I may visit Get Your
Location in the main navigation and track down internet signal source. This
page in the application takes user's location from browser and gives driving
direction to the location, which is identified by his IP address used to access
internet.
Sometimes, I don't want to open a website from xxxx country,
country name of hosting itself limelight authenticity of a website. I could
visit Search Domain or IP Location in the main navigation to track down hosting
location of an IP/domainName. This page in the application takes user's location
from browser (source) and gives driving direction to the location, which is
identified by IP address given as input / IP address of the domainName
(Destination)
Code
myservice.js: This contains the main angular custom
services built driving controllers in visitorlocation.js , searchip.js and contactus.js
Custom Services in the js file myservice.js -->
geoipService- Talks to external geoIP service to get IP and details like latitude/longitude.
googleService- Talks to external Google map API's , reference for which is added in index.html.
myGeolocation-is used to get latitude and longitude of user's browser.
See comments section (click here) in code for further details.
Custom Services in the js file myservice.js -->
geoipService- Talks to external geoIP service to get IP and details like latitude/longitude.
googleService- Talks to external Google map API's , reference for which is added in index.html.
myGeolocation-is used to get latitude and longitude of user's browser.
See comments section (click here) in code for further details.
index.html: This is the single page, which loads
the entire html based on controller requested. This app may be very well termed
as a single page app containing only one page - index.html which loads required
components. Default load for index.html is introduction.html.
introduction.html: [current html you are viewing] This
view simply welcomes a user. It also list details of the project and its
components. There is no associated controller for this view.
visitorlocation.html: This
view is associated with controller in visitorlocation.js.
Actions in this controller achieved with help of services-
See comments section (click here) in code for further details.
See comments section (click here) in code for further details.
searchip.html: This view is associated with
controller in searchip.js.
User enter a domainName or IP address. This value is processed in the function callTheMagic further on click of search.
Actions in this function achieved with help of services-
See comments section (click here) in code for further details.
User enter a domainName or IP address. This value is processed in the function callTheMagic further on click of search.
Actions in this function achieved with help of services-
See comments section (click here) in code for further details.
contactus.html: This view is associated with
controller in contactus.js.
It gives driving directions to Fort Hays State University.
myapp.js: The routes are defined here.
Weakness
Loading wheels would have been basic requirement not catered here.
Loading wheels would have been basic requirement not catered here.
Languages / Libraries
JQuery (2.1.4), AngularJS (1.4.2) with
Angular-route (1.4.2) for single page app backbone.
freegeoip.net for location by IP.
maps.googleapis.com to get user's current
device/browser location , and detect/display driving directions.
Bootstrap (3.3.5) with sb-admin-2 theme, font-awesome,
metisMenu for desktop/mobile friendly UI.
Demo
Test with saucelabs on Linux + Opera 12: https://youtu.be/5udO_3X9cR0
Test with saucelabs on iOS 10.0 + iPhone 6s: https://youtu.be/VqfjTC6AjJw
Test with saucelabs on iOS 9.3 + iPhone 4s : https://youtu.be/udfi3afwPkY
Tuesday, November 22, 2016
50 Sample Project with Oracle Application Express
OracleFlix Online Media Rentals
If you find it
difficult to understand, please go through links in below mentioned google sheet step by step and
come back to this page:
CUSTOMERS
MOVIES
MEDIA
ACTORS
STAR_BILLINGS
ERD
Script Content
All the
Activities, step 1 through 7, are performed and recorded in this script. This script is uploaded and executed in Oracle Application Express
--Drop view if exist
DROP VIEW title_unavail;
--Drop all tables if they exist
DROP TABLE rental_history PURGE;
DROP TABLE customers PURGE;
DROP TABLE media PURGE;
DROP TABLE star_billings PURGE;
DROP TABLE movies PURGE;
DROP TABLE actors PURGE;
--drop the sequences
DROP SEQUENCE customer_id_seq;
DROP SEQUENCE title_id_seq;
DROP SEQUENCE media_id_seq;
DROP SEQUENCE actor_id_seq;
-- drop the synonym
DROP SYNONYM tu;
--now create tables
--1. Create tables using the attached ERD. AND 2. Creating
and Managing Constraints
CREATE TABLE actors
( actor_id
NUMBER(10,0) CONSTRAINT atr_atr_id_pk PRIMARY KEY ,
stage_name
VARCHAR2(40) CONSTRAINT atr_ste_nae_nn NOT NULL ,
first_name
VARCHAR2(25) CONSTRAINT atr_fit_nae_nn NOT NULL ,
last_name
VARCHAR2(25) CONSTRAINT atr_lat_nae_nn NOT NULL ,
birth_date
DATE CONSTRAINT atr_bih_dae_nn NOT NULL
);
CREATE TABLE movies
( title_id
NUMBER(10,0) CONSTRAINT mie_tte_id_pk PRIMARY KEY ,
title
VARCHAR2(60) CONSTRAINT mie_tte_nn NOT NULL ,
description
VARCHAR2(400) CONSTRAINT mie_dsn_nn NOT NULL ,
--rating
VARCHAR2(4) CONSTRAINT atr_rig_chk CHECK (UPPER(rating) IN ('G', 'PG', 'R',
'PG13')),
rating
VARCHAR2(4) CONSTRAINT mie_rig_chk CHECK ( rating IN ('G', 'PG', 'R', 'PG13')),
category
VARCHAR2(20) CONSTRAINT mie_cey_chk CHECK ( category IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD',
'SCIFI', 'DOCUMENTARY')),
--category
VARCHAR2(20) CONSTRAINT atr_cey_chk CHECK ( UPPER(category) IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD',
'SCIFI', 'DOCUMENTARY')),
release_date
DATE CONSTRAINT mie_rle_dae_nn NOT NULL
);
CREATE TABLE star_billings
( actor_id
NUMBER(10,0) CONSTRAINT str_big_atr_id_fk REFERENCES actors(actor_id) ,
title_id
NUMBER(10,0) CONSTRAINT str_big_tte_id_fk REFERENCES movies(title_id) ,
comments
VARCHAR2(40),
CONSTRAINT
str_big_atr_id_tte_id_pk PRIMARY KEY (actor_id, title_id)
);
CREATE TABLE media
( media_id
NUMBER(10,0) CONSTRAINT mda_mda_id_pk PRIMARY KEY ,
format
VARCHAR2(3) CONSTRAINT mda_fmt_nn NOT NULL ,
title_id
NUMBER(10,0) CONSTRAINT mda_tte_id_nn NOT NULL
,
CONSTRAINT
mda_tte_id_fk FOREIGN KEY(title_id)
REFERENCES movies(title_id)
);
CREATE TABLE customers
( customer_id NUMBER(10,0)
CONSTRAINT ctr_ctr_id_pk PRIMARY KEY ,
last_name
VARCHAR2(25) CONSTRAINT ctr_lat_nae_nn NOT NULL ,
first_name
VARCHAR2(25) CONSTRAINT ctr_fit_nae_nn NOT NULL ,
home_phone
VARCHAR2(12) CONSTRAINT ctr_hoe_phe_nn NOT NULL ,
address
VARCHAR2(100) CONSTRAINT ctr_ads_nn NOT NULL ,
city
VARCHAR2(30) CONSTRAINT ctr_cyy_nn NOT NULL ,
state
VARCHAR2(2) CONSTRAINT ctr_ste_nn NOT NULL ,
email
VARCHAR2(25),
cell_phone
VARCHAR2(12)
);
CREATE TABLE rental_history
( media_id
NUMBER(10,0) CONSTRAINT ral_hty_mda_id_fk REFERENCES media(media_id) ,
rental_date
DATE DEFAULT SYSDATE ,
customer_id
NUMBER(10,0) CONSTRAINT
ral_hty_ctr_id_nn NOT NULL ,
return_date
DATE ,
CONSTRAINT
ral_hty_ctr_id_fk FOREIGN KEY(customer_id) REFERENCES customers(customer_id),
CONSTRAINT
ral_hty_mda_id_ral_dae_pk PRIMARY KEY (media_id, rental_date)
);
--3. Create a view called TITLE_UNAVAIL
CREATE OR REPLACE VIEW title_unavail ("movie
title", "media id") AS
SELECT movies.title,
media.media_id
FROM
rental_history INNER
JOIN media ON rental_history.media_id =
media.media_id INNER JOIN movies ON media.title_id = movies.title_id
WHERE rental_history.return_date IS NULL
WITH READ ONLY;
--4. Create the following sequences to be used for primary
key values
CREATE SEQUENCE customer_id_seq
START WITH 101;
CREATE SEQUENCE title_id_seq;
CREATE SEQUENCE media_id_seq
START WITH 92;
CREATE SEQUENCE actor_id_seq
START WITH 1001;
--5. Add the data to the tables. Be sure to use the
sequences for the PKs
INSERT INTO actors(actor_id, stage_name, first_name,
last_name, birth_date)
VALUES(actor_id_seq.NEXTVAL, 'Brad Pitt', 'William', 'Pitt',
TO_DATE('18-DEC-1963','DD-MON-YYYY'));
INSERT INTO actors(actor_id, stage_name, first_name,
last_name, birth_date)
VALUES(actor_id_seq.NEXTVAL, 'Amitabh Bachchan', 'Amit',
'Srivastav', TO_DATE('11-10-1942','DD-MM-YYYY'));
INSERT INTO actors(actor_id, stage_name, first_name,
last_name, birth_date)
VALUES(actor_id_seq.NEXTVAL, 'Aamir Khan', 'Aamir', 'Hussain
Khan', TO_DATE('14 March 1965','DD Month YYYY'));
INSERT INTO actors(actor_id, stage_name, first_name,
last_name, birth_date)
VALUES(actor_id_seq.NEXTVAL, 'Akshay Kumar', 'Rajiv',
'Bhatia', TO_DATE('09/09/1967','DD/MM/YYYY'));
INSERT INTO movies(title_id, title , description , rating , category , release_date )
VALUES(title_id_seq.NEXTVAL, 'Fight Club', 'An insomniac
office worker, looking for a way to change his life, crosses paths with a
devil-may-care soap maker, forming an underground fight club that evolves into
something much, much more.', 'R', 'DRAMA',
TO_DATE('15 October 1999','DD Month YYYY'));
INSERT INTO movies(title_id, title , description , rating , category , release_date )
VALUES(title_id_seq.NEXTVAL, 'World War Z', 'Former United
Nations employee Gerry Lane traverses the world in a race against time to stop
the Zombie pandemic that is toppling armies and governments, and threatening to
destroy humanity itself.', 'PG13', 'ACTION',
TO_DATE('21 June
2013','DD Month YYYY'));
INSERT INTO movies(title_id, title , description , rating , category , release_date )
VALUES(title_id_seq.NEXTVAL, 'The Curious Case of Benjamin
Button', 'Tells the story of Benjamin Button, a man who starts aging backwards
with bizarre consequences.', 'PG13', 'DRAMA',
TO_DATE('25 December
2008','DD Month YYYY'));
INSERT INTO movies(title_id, title , description , rating , category , release_date )
VALUES(title_id_seq.NEXTVAL, 'The Great Gatsby', 'A writer
and wall street trader, Nick, finds himself drawn to the past and lifestyle of
his millionaire neighbor, Jay Gatsby.', 'PG13', 'DRAMA',
TO_DATE('10 May
2013','DD Month YYYY'));
INSERT INTO movies(title_id, title , description , rating , category , release_date )
VALUES(title_id_seq.NEXTVAL, '3 Idiots', 'Two friends are
searching for their long lost companion. They revisit their college days and
recall the memories of their friend who inspired them to think differently,
even as the rest of the world called them "idiots".', 'PG13',
'DRAMA',
TO_DATE('25 December
2009','DD Month YYYY'));
INSERT INTO movies(title_id, title , description , rating , category , release_date )
VALUES(title_id_seq.NEXTVAL, 'Baby', 'An elite
counter-intelligence unit learns of a plot, masterminded by a maniacal madman.
With the clock ticking, it''s up to them to track the terrorists''
international tentacles and prevent them from striking at the heart of India.',
NULL, 'ACTION',
TO_DATE('23 January
2015','DD Month YYYY'));
INSERT INTO star_billings(actor_id, title_id , comments)
VALUES(1001,1, 'soap salesman named Tyler Durden');
INSERT INTO star_billings(actor_id, title_id , comments)
VALUES(1001,2, 'a former United Nations investigator');
INSERT INTO star_billings(actor_id, title_id , comments)
VALUES(1001,3, NULL);
INSERT INTO star_billings(actor_id, title_id , comments)
VALUES(1002,4, 'a Jewish friend and mentor of
Gatsby''s');
INSERT INTO star_billings(actor_id, title_id , comments)
VALUES(1003,5, 'Phunsukh Wangdu');
INSERT INTO star_billings(actor_id, title_id , comments)
VALUES(1004,6, 'as Ajay Singh Rajput');
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',1);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',1);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',2);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',2);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',3);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',3);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',4);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',4);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',5);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',5);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'DVD',6);
INSERT INTO media(media_id, format , title_id)
VALUES(media_id_seq.NEXTVAL,'VHS',6);
INSERT INTO customers(customer_id, last_name,
first_name, home_phone, address, city,
state, email, cell_phone)
VALUES(customer_id_seq.NEXTVAL,'Palombo', 'Lisa',
'716-270-2669', '123 Main St', 'Buffalo', 'NY', 'palombo@ecc.edu',
'716-555-1212');
INSERT INTO customers(customer_id, last_name,
first_name, home_phone, address, city,
state, email, cell_phone)
VALUES(customer_id_seq.NEXTVAL,'Bee', 'Cole',
'407-555-8234', '123 Main St', 'Orlando', 'FL', 'cbee@edu.edu', NULL);
INSERT INTO customers(customer_id, last_name,
first_name, home_phone, address, city,
state, email, cell_phone)
VALUES(customer_id_seq.NEXTVAL,'Twee', 'Zoe',
'709-867-5309', '1009 Oliver Avenue', 'Boston', 'MA', NULL, NULL);
INSERT INTO customers(customer_id, last_name,
first_name, home_phone, address, city,
state)
VALUES(customer_id_seq.NEXTVAL,'Rampal', 'Amarjeet',
'408-809-1212', '1212 Down Trail', 'Buffalo', 'NY');
INSERT INTO customers(customer_id, last_name,
first_name, home_phone, address, city,
state)
VALUES(customer_id_seq.NEXTVAL,'Tulli', 'Manoj',
'404-200-3011', '3011 Great Rd', 'Atlanta', 'GA');
INSERT INTO customers(customer_id, last_name,
first_name, home_phone, address, city,
state)
VALUES(customer_id_seq.NEXTVAL,'Hare Krishna', 'Goswami',
'415-503-4444', '405 Church St.', 'Lawrenceville', 'GA');
INSERT INTO rental_history(media_id, rental_date,
customer_id, return_date)
VALUES(94, TO_DATE('09/09/1967','DD/MM/YYYY'), 106,
TO_DATE('16/09/1967','DD/MM/YYYY') );
INSERT INTO rental_history(media_id, rental_date,
customer_id, return_date)
VALUES(94, TO_DATE('20/06/1968','DD/MM/YYYY'), 104, NULL );
INSERT INTO rental_history(media_id, rental_date,
customer_id, return_date)
VALUES(92, TO_DATE('19-SEP-2010','DD-MON-YYYY'), 101,
TO_DATE('20-SEP-2010','DD-MON-YYYY') );
INSERT INTO rental_history(media_id, rental_date,
customer_id, return_date)
VALUES(92, TO_DATE('25-SEP-2010','DD-MON-YYYY'), 101, NULL
);
--6. Create an index on the last_name column of the
Customers table.
CREATE INDEX ctr_lat_nae_idx
on
customers(last_name);
--7. Create a synonym called TU for the TITLE_UNAVAIL view.
CREATE SYNONYM tu
FOR title_unavail;
Now each step of script output verification:
Number
|
Elapsed
|
Statement
|
Feedback
|
Rows
|
1
|
0
|
DROP VIEW title_unavail
|
ORA-00942: table or view does not exist
|
|
2
|
0.01
|
DROP TABLE rental_history PURGE
|
ORA-00942: table or view does not exist
|
|
3
|
0
|
DROP TABLE customers PURGE
|
ORA-00942: table or view does not exist
|
|
4
|
0.01
|
DROP TABLE media PURGE
|
ORA-00942: table or view does not exist
|
|
5
|
0
|
DROP TABLE star_billings PURGE
|
ORA-00942: table or view does not exist
|
|
6
|
0.01
|
DROP TABLE movies PURGE
|
ORA-00942: table or view does not exist
|
|
7
|
0
|
DROP TABLE actors PURGE
|
ORA-00942: table or view does not exist
|
|
8
|
0.01
|
DROP SEQUENCE customer_id_seq
|
ORA-02289: sequence does not exist
|
|
9
|
0
|
DROP SEQUENCE title_id_seq
|
ORA-02289: sequence does not exist
|
|
10
|
0
|
DROP SEQUENCE media_id_seq
|
ORA-02289: sequence does not exist
|
|
11
|
0.01
|
DROP SEQUENCE actor_id_seq
|
ORA-02289: sequence does not exist
|
|
12
|
0
|
DROP SYNONYM tu
|
ORA-01434: private synonym to be dropped does not exist
|
|
13
|
0.02
|
CREATE TABLE actors ( actor_id NUMBER(10,0) CONSTRAINT a
|
Table created.
|
0
|
14
|
0.02
|
CREATE TABLE movies ( title_id NUMBER(10,0) CONSTRAINT m
|
Table created.
|
0
|
15
|
0.01
|
CREATE TABLE
star_billings ( actor_id
NUMBER(10,0) CO
|
Table created.
|
0
|
16
|
0.02
|
CREATE TABLE media ( media_id NUMBER(10,0) CONSTRAINT m
|
Table created.
|
0
|
17
|
0.02
|
CREATE TABLE
customers ( customer_id
NUMBER(10,0) CONST
|
Table created.
|
0
|
18
|
0.01
|
CREATE TABLE
rental_history ( media_id
NUMBER(10,0) CON
|
Table created.
|
0
|
19
|
0.01
|
CREATE OR REPLACE VIEW title_unavail ("movie title",
"media
|
View created.
|
0
|
20
|
0.01
|
CREATE SEQUENCE customer_id_seq START WITH 101
|
Sequence created.
|
0
|
21
|
0
|
CREATE SEQUENCE title_id_seq
|
Sequence created.
|
0
|
22
|
0.01
|
CREATE SEQUENCE media_id_seq START WITH 92
|
Sequence created.
|
0
|
23
|
0
|
CREATE SEQUENCE actor_id_seq START WITH 1001
|
Sequence created.
|
0
|
24
|
0.01
|
INSERT INTO actors(actor_id, stage_name, first_name, last_na
|
1 row(s) inserted.
|
1
|
25
|
0
|
INSERT INTO actors(actor_id, stage_name, first_name, last_na
|
1 row(s) inserted.
|
1
|
26
|
0
|
INSERT INTO actors(actor_id, stage_name, first_name, last_na
|
1 row(s) inserted.
|
1
|
27
|
0.01
|
INSERT INTO actors(actor_id, stage_name, first_name, last_na
|
1 row(s) inserted.
|
1
|
28
|
0
|
INSERT INTO movies(title_id, title , description , rating ,
|
1 row(s) inserted.
|
1
|
29
|
0.01
|
INSERT INTO movies(title_id, title , description , rating ,
|
1 row(s) inserted.
|
1
|
30
|
0
|
INSERT INTO movies(title_id, title , description , rating ,
|
1 row(s) inserted.
|
1
|
31
|
0
|
INSERT INTO movies(title_id, title , description , rating ,
|
1 row(s) inserted.
|
1
|
32
|
0
|
INSERT INTO movies(title_id, title , description , rating ,
|
1 row(s) inserted.
|
1
|
33
|
0
|
INSERT INTO movies(title_id, title , description , rating ,
|
1 row(s) inserted.
|
1
|
34
|
0
|
INSERT INTO star_billings(actor_id, title_id , comments) VAL
|
1 row(s) inserted.
|
1
|
35
|
0.01
|
INSERT INTO star_billings(actor_id, title_id , comments) VAL
|
1 row(s) inserted.
|
1
|
36
|
0
|
INSERT INTO star_billings(actor_id, title_id , comments) VAL
|
1 row(s) inserted.
|
1
|
37
|
0
|
INSERT INTO star_billings(actor_id, title_id , comments) VAL
|
1 row(s) inserted.
|
1
|
38
|
0
|
INSERT INTO star_billings(actor_id, title_id , comments) VAL
|
1 row(s) inserted.
|
1
|
39
|
0.01
|
INSERT INTO star_billings(actor_id, title_id , comments) VAL
|
1 row(s) inserted.
|
1
|
40
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
41
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
42
|
0.01
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
43
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
44
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
45
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
46
|
0.01
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
47
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
48
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
49
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
50
|
0.01
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
51
|
0
|
INSERT INTO media(media_id, format , title_id) VALUES(media_
|
1 row(s) inserted.
|
1
|
52
|
0
|
INSERT INTO customers(customer_id, last_name, first_name, h
|
1 row(s) inserted.
|
1
|
53
|
0.01
|
INSERT INTO customers(customer_id, last_name, first_name, h
|
1 row(s) inserted.
|
1
|
54
|
0
|
INSERT INTO customers(customer_id, last_name, first_name, h
|
1 row(s) inserted.
|
1
|
55
|
0
|
INSERT INTO customers(customer_id, last_name, first_name, h
|
1 row(s) inserted.
|
1
|
56
|
0
|
INSERT INTO customers(customer_id, last_name, first_name, h
|
1 row(s) inserted.
|
1
|
57
|
0.01
|
INSERT INTO customers(customer_id, last_name, first_name, h
|
1 row(s) inserted.
|
1
|
58
|
0
|
INSERT INTO rental_history(media_id, rental_date, customer_i
|
1 row(s) inserted.
|
1
|
59
|
0
|
INSERT INTO rental_history(media_id, rental_date, customer_i
|
1 row(s) inserted.
|
1
|
60
|
0.01
|
INSERT INTO rental_history(media_id, rental_date, customer_i
|
1 row(s) inserted.
|
1
|
61
|
0
|
INSERT INTO rental_history(media_id, rental_date, customer_i
|
1 row(s) inserted.
|
1
|
62
|
0.01
|
CREATE INDEX ctr_lat_nae_idx
on customers(last_name)
|
Index created.
|
0
|
63
|
0
|
CREATE SYNONYM tu FOR
title_unavail
|
Synonym created.
|
0
|
01 Working with DDL Statements - Output
DESC rental_history;
DESCRIBE customers;
DESCRIBE media;
DESCRIBE star_billings;
DESC movies;
DESC actors;
02_ Creating and Managing Constraints- Output
SELECT chld.table_name "Subject",
chldcols.column_name "Subject Column Name", chld.constraint_name
"constraint_name in Subject",
chld.constraint_type
"constraint_type in Subject", chld.search_condition "search_condition in Subject",
prnt.table_name "Parent of FK",
prntcols.column_name "Parent's Column Name", prnt.constraint_name
"Parent PK"
FROM user_constraints chld LEFT OUTER JOIN user_constraints
prnt ON chld.r_constraint_name =
prnt.constraint_name
LEFT OUTER JOIN user_cons_columns chldcols ON
chld.constraint_name = chldcols.constraint_name
LEFT OUTER JOIN user_cons_columns prntcols ON
prnt.constraint_name = prntcols.constraint_name
WHERE chld.table_name = UPPER('table_name');
ACTORS:
MOVIES:
STAR_BILLINGS:
MEDIA:
CUSTOMERS:
RENTAL_HISTORY:
03_ Creating and Managing Views-Output
(After 5.
Add the data to the tables.)
SELECT * FROM title_unavail;
04_ Working with Sequences (Indexes and Synonyms)-Output
Output of SELECT * FROM user_sequences WHERE
sequence_name = UPPER('squence_name');
·
Use a sequence to generate PKs for CUSTOMER_ID
in CUSTOMERS table Begin at 101 and increment by 1
CREATE SEQUENCE customer_id_seq
START WITH 101;
·
Use a sequence to generate PKs for TITLE_ID in
MOVIES table Begin at 1 and increment by 1
CREATE SEQUENCE title_id_seq;
·
Use a sequence to generate PKs for MEDIA_ID in
MEDIA table Begin at 92 and increment by 1
CREATE SEQUENCE media_id_seq
START WITH 92;
·
Use a sequence to generate PKs for ACTOR_ID in
ACTOR table Begin at 1001 and increment by 1
CREATE SEQUENCE actor_id_seq
START WITH 1001;
5. Add the data to the tables. Be sure to use the sequences for the PKs.
SELECT * FROM actors;
SELECT * FROM movies;
SELECT * FROM star_billings;
SELECT * FROM media;
SELECT * FROM customers;
SELECT * FROM rental_history;
6. Create an index on the last_name column of the Customers table.
SELECT ucm.index_name, ucm.column_name, ucm.column_position,
uix.uniqueness
FROM user_indexes
uix INNER JOIN user_ind_columns
ucm ON uix.index_name = ucm.index_name
WHERE ucm.table_name = 'CUSTOMERS' AND ucm.column_name =
'LAST_NAME' ;
7. Create a synonym called TU for the TITLE_UNAVAIL view.
Note: I didn’t have privileges to create public synonym:
ORA-01031: insufficient privileges
So I created private
SELECT * FROM tu;
SELECT * FROM user_synonyms WHERE table_NAME = UPPER('title_unavail');
Subscribe to:
Posts (Atom)