SQL server pour les Gros Nuls

Créer une database

create database "db-test";
CREATE LOGIN "user-test" WITH PASSWORD = 'mypassword';
GO
USE "db-test";
CREATE USER "user-test" FOR LOGIN "user-test" WITH DEFAULT_SCHEMA = dbo;
EXEC sp_addrolemember N'db_datawriter', N'user-test'
EXEC sp_addrolemember N'db_datareader', N'user-test'
-- If you want a case sensistive database
ALTER DATABASE "db-test" COLLATE Latin1_General_CS_AS; 

Change ownership of a database

ALTER AUTHORIZATION DATABASE: to

Déplacer les fichiers d'une base de données pour libérer de l'espace

 

• Récupperer les infos sur l'endroit ou se trouvent les fichier de log et de données en allant sur l'onglet propriété--> File de la base de donnée

• Détacher la db

sp_detach_db my_db 

• Copier les fichiers

• Rattacher la db

EXEC sp_attach_db @dbname = N'corete_mja',  
    @Core TE =  
N'F:\data\Core TE_mja1.mdf',  
    @Core TE_log =  
N'F:\data\Core TE_log_mja1.ldf'; 
 
Ligne de commande
Faire un backup
Ligne de commande

BACKUP DATABASE "dbname" TO DISK='D:\data\act\dumps\finename.BAK'

Interactive client
Se placer sur la db et lancer un backup ar le client SQL Server Management Studio 

Restorer une database 

Ligne de commande

USE [master];
GO
CREATE DATABASE dbint;
RESTORE DATABASE dbint  FROM DISK = 'd:\youbackup-int.bak' with replace;

ALTER AUTHORIZATION ON DATABASE::dbint TO existingloginfordbint

Parfois vous pouveez être bloqué dans la restauration parce que les chemins du serveurs de destination ne correspondent pas au serveur source. Dans ce cas il faut utiliser l'opérateur move.

RESTORE DATABASE actint  FROM DISK = 'f:\share\dbint.bak' WITH REPLACE,
 MOVE 'dbint' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER1\MSSQL\DATA\dbint.mdf',
MOVE 'dbint_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER1\MSSQL\DATA\dbint.ldf' ;

Pour lister le nom des data et journaux utiliser la commande FILELISTONLY

RESTORE FILELISTONLY FROM DISK = 'f:\share\act-readonly.bak';

Create a SQL script

Pour créer un script SQL et récupper le contenu d'un table:

Etudier les performances

SELECT  creation_time
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC; 

Gérer les fichiers

Reduire un fichier de log

DBCC SHRINKFILE (DBWV1Y6101_log,20)

Déplacer la base de donnée temporaire

ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');  

ALTER DATABASE tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');  
 

Restore in amazon

exec msdb.dbo.rds_restore_database
@restore_db_name='mydb',
@s3_arn_to_restore_from='arn:aws:s3:::mys3bucket/mydimp.bak';

Load from a script

sqlcmd -S localhost -i "D:\data\Core kpi\dump\script.sql" -o "D:\data\Core kpi\dump\script.txt" 

sqlcmd -S server,port -r1 -Uusername -Ppassword -b -ddatabase -iinputfile

Remove dead code from your application

The problem:
Code coverage estimation is mostly based on Automated Unit Testing and software like JUnit. Doing like this is okay to increase youy coverage rate, but sometimes has a pernicious effect. Because it does not guarantee that the code you cover is really necessary to your application, and so you may have some portions of code covered but unrelevant for the actual application business.

Unecessary code detected with UCDetector:
In order to avoid this unecessary code, you have several alternatives. The first is to use the excellent eclipse plugin UCDetector (standing for Unecessary Code Detector) downloadable at http://www.ucdetector.org/. This detects in a friendly way the code that is not referenced elsewhere in the project.

This plugin must be applied to relevant package for application business. Indeed,  service packages and dao packages are good candidates. The result comes in the markers tabs of eclipse. You can then decide weither or not, those method shall be kept or not. 

Unecessary code detected with Tomcat and code coverage:
The below method can only apply to the low layer in a webapp, because you cannot check the code accessed throught JSP. For an in deep investigation of the actual code used, you may use cobertura and tomcat together.

Supposing you have a maven project. Here is the way to do it.
First, build your package :
- mvn package

The project is built in the target/myproject.war archive.

- mvn cobertura:instrument

This creates a folder named
- target\generated-classes\cobertura containing the instrumented classes.

This also creates a file named :

- target\cobertura.ser this files is a database storing the coverage information.

We have to build an instrumented war.

- Unzip the myproject.war file in the folder myproject
- Copy the cobertura.jar file in the myproject/WEB-INF/lib folder
- Replace the classes in folder myproject/WEB-INF/classes by the one produced in target\generated-classes\cobertura

Now it is time to prepare your tomcat:
Copy the cobertura.ser file in the folder where you will launch tomcat. If you are lauching tomcat through the windows explorer,  this folder is the /bin/ directory. If your are lucnhing it through a console, the folder is the current working directory.

Now you can run your tomcat.

Finally produce your report using the command

cobertura-report.bat --datafile ./cobertura.ser --destination ../report --format html ../sources/

Specials if you have your project distibuted in several modules^, you may first merge your cobertura information into a single file before launching tomcat :

cobertura-merge.bat --datafile target/cobertura_final.ser ../myproject-service/target/cobertura/cobertura.ser ../myproject-webapp/target/cobertura/cobertura.ser

Dynamic image upload like in facebook

Doing like facebook for uploading an image will make your web site instinctive. The main difficulty for making it is that you cannot post any multimedia object using AJAX, you must submit the image from a form within the web page. That is why you have to declare a "virtual" form (not accessible directly for input): To do it in java, here is the tip: In your JSP page add the virtual form:

<div class="row-fluid">
    <div class="span2 vertical-align">
    <img id="pictureImg" class="hidden"    src="showPicture.action" />
</div>

In your JSP page add the virtual form:

<!-- Div to upload image (replace original) -->
<div id="uploadDiv">
    <s:form target="upload_target"
        action="UploadPicture"
        enctype="multipart/form-data" id="pictureForm">
        <ul class="error">
            <li id="fileUploadErrorExtension" class="hidden"><s:text
                    name="picture.error.extension" /></li>
            <li id="fileUploadErrorSize" class="hidden"><s:text
                    name="picture.error.size" /></li>
        </ul>
        <div class="picture" id="picture">
            <input type="file" id="pictureFile"
                name="pictureFile" />
            <div class="message hidden" id="pictureMessage">
                <s:text name="picture.change" />
            </div>
        </div>
    </s:form>
    <iframe id="upload_target" name="upload_target" src="#"
        style="width: 0; height: 0; border: 0px solid #fff;"></iframe>
</div>

In your page JS, you must add

<script language="javascript" type="text/javascript">
    window.top.window.etlb.Utils.upload.callback();
</script>

This binds HTML with JAVA script

"use strict";
// At window loading the variables of JS are initialized. 
$(window).load(function() {
        Global.Utils.upload.url = 'displayPicture.action';
        // Binds the pictureImg to the variable in JS file.
        Global.Utils.upload.img = $('#pictureImg');
        Global.Utils.upload.initUpload();
});



var Global = {
    Utils : {
        upload : {
            // global var to store the image, url to get the new img
            url : null,
            img : null,
            callback : function() {
                // callback of server : reinit upload the random prevent from caching the image.
                Global.Utils.upload.img.attr('src', Global.Utils.upload.url
                        + '?random=' + Math.random());
                Global.Utils.upload.img.bind("load",function(){
                Global.Utils.upload.initUpload();
                });
            },

            initUpload : function() {
                // Replace initial image with a div background by the upload div and hides the image
                Global.Utils.upload.img.addClass('hidden');
                Global.Utils.upload.img.after($('#uploadDiv'));                                                            
                $('#picture').css(
                        'background-image',
                        'url(\'' + etlb.Utils.upload.img.attr('src')+ '\')');
                // When the mouse enters the image, the change image label is displayed
                $('#picture').mouseenter(function() {
                    $('#pictureMessage').removeClass('hidden');
                });
                // When the mouse enters the image, the change image label is hidden
                $('#picture').mouseleave(function() {
                    $('#pictureMessage').addClass('hidden');
                });

                var extension = [ 'jpg', 'jpeg', 'png', 'bmp', 'gif' ];
                $('#fileUploadErrorExtension').text(
                        'Error Uploading');
                var validFileExtension = function(fileName) {
                    for ( var i in extension) {
                        if (fileName.toLowerCase().indexOf(
                                '.' + extension[i].toLowerCase()) != -1) {
                            return true;
                        }
                    }
                    return false;
                };
                var validFileSize = function(size) {
                    return size <= 1024 * 1000;
                };
                // When the picture file is changed you upload the image by sumbitting the form.
                
                $('#pictureFile').change(function(e) {
                    var input = this;
                    var fileName = '';
                    var fileSize = '';
                    if ($.browser.msie && e.target.value) {
                        fileName = e.target.value;
                        fileSize = '1000';
                    } else if (input.files && input.files[0]) {
                        fileName = input.files[0].name;
                        fileSize = input.files[0].size;
                    }
                    if (!validFileExtension(fileName) && fileName != '') {
                        $('#fileUploadErrorExtension').removeClass('hidden');
                        return;
                    }
                    if (fileName == '') {
                        $('#fileUploadErrorExtension').addClass('hidden');
                    }

                    if (!validFileSize(fileSize)) {
                        $('#fileUploadErrorSize').removeClass('hidden');
                        return;
                    }

                    $('#fileUploadErrorExtension').addClass('hidden');
                    $('#fileUploadErrorSize').addClass('hidden');
                    
                    // Send
                    $('#pictureForm').submit();

                });

            }
        }
    }
};

Javascript for the rotten old timers

What is Javascript

It is a scripting language. To perform UI interaction, it should be combined with CSS.
In Javascript, you use either the id or the class of an HTML element and you bind it with the $character.

Good place to start

Modern Javascript programming starts with the callbacks. Callbacks writing had been drastically improved with the comming of JQuery. So, it is the right place to start beginning by learning simple JQuery.
  
http://www.w3schools.com/jquery/

Debugging JAVASCRIPT

Add traces to debug : console.log();


Shortcut for Firefox
Install the Firebug plugin
Ctrl+Maj+I

Shortcut for Chrome
Ctrl+Maj+I

Shortcut for Internet Explorer
F12

GIT for rotten old timers

If you have not slept in a cave for the last few years, you may have heard of GIT. You may have tried it and thought it does not worth a penny, because it is too complicated for for doing same things that SVN !

I agree with you, most of GIT is useless for enterprise development. In a few words, GIT is a super patching tool for coding all over the world and exchanging with you geek friends. The idea behind the bush is that in a respectable cool and open minded community, no central referential should prevail. GIT is all about cloning, retrieving things from others and pushing it it to some friends.

Let's say things in the sharpest way, as compared to SVN, GIT is mainly a political fight. 

But just in order to be cool and fashnable, a set of command to blindly apply.

Checkout a remote repository (now you call it clone)

$ git clone

Create a branch locally (before you start to work)
$ git branch

Push the branch on github to let the other know you started a branch. This is an optional step to avoid conflict, since a pranch is automatically pushed when you push a commit.

$ git push origin

Switch your new branch
 $ git checkout

Add tracked files to a commit
$ git add files

Commit locally
$ git commit files


Commit remotely
$ git push origin name_of_your_new_branch

Define the MIDI output to VST on Sibelius First

It seems that the score editing is still far from being user friendly since doing a basic stuff, like changing the default MIDI output to VST instruments requires to be somehow a hacker. So, here is a "how to" for the dummies.

First, you must go in the play menu and click in the parameters little button.

Then, you should see a list of your ouput, something like this :

If you have not done anything to your first installation, you should only see the configuration using default windows MIDI mapper. But, you may add a new configuration by clicking new.

To set a VST instrument as the midi output, just click on the audio engine property (red arrow above) and you shoud see another window.

In this window, you can finally define your VST instrument folder by clicking on VST folder button (red arrow above)

You can then add or remove folders in that windows. For me, the simplest trick I found was just to copy my VST2 plugins file into the folder predefined i.e. copy C:\program filles\Steinberg\Cubase 7\VstPlugins\*.dll to C:\program Files\Avid\VSTPlugin folder.


You will need to restart Sibelius in order to see the change.

That's all folks. Hope it helped.