working on it ...

Filters

Explore Public Snippets

Sort by

Found 1,039 snippets

    public by yourfriendcaspian  312  0  4  0

    How to install MySQL with Python and Django Debian/Derivatives

    #Install Mysql with Python and Django Debian/Derivatives 
    
    #To install we need to have some dependencies in the system for now we will show on
    #Debian and Derivatives.
    
    #But first install updates and MySQL
    
    ```
    $ sudo apt-get update
    $ sudo apt-get upgrade
    ```	
    
    # ** NOTE: ** 
    #Each System has its commands for updating, if your machine is not derived from Debian ** look for them **
    
    ##Install MySQL
    
    ```
    $ sudo apt-get install mysql-server mysql-client
    Passwd for 'root' user: <password>
    ```
    
    #At the end we execute this command to give more security to our BD
    
    ```
    $ mysql_secure_installation
    ```
    
    #Check carefully the changes that will be made, the first question is the passwd for root,
    #IF you want to keep or change it, and continue with other security questions.
    
    ##Create a database and a user for the DB
    
    #Now we will create the DB that will be connected to DJango and a User with Passwd to access it.
    #There are two ways to do this:
    
    ```
    echo "CREATE DATABASE <DATABASENAME>;" | mysql -u root -p
    echo "CREATE USER '<DATABASEUSER>'@'localhost' IDENTIFIED BY '<PASSWORD>';" | mysql -u root -p
    echo "GRANT ALL PRIVILEGES ON <DATABASENAME>.* TO '<DATABASEUSER>'@'localhost';" | mysql -u root -p
    echo "FLUSH PRIVILEGES;" | mysql -u root -p
    ```
    
    #So they should put their passwd of mysql in each line or
    #they can also do it of the following way
    
    ```
    $ mysql -u root -p
    ```
    
    #Enter your passwd and then do the following.
    
    ```
    CREATE DATABASE <DATABASENAME>;
    CREATE USER '<DATABASEUSER>'@localhost IDENTIFIED BY '<PASSWORD>';
    GRANT ALL PRIVILEGES ON <DATABASENAME>.* TO '<DATABASEUSER>'@localhost;
    FLUSH PRIVILEGES;
    exit
    ```
    
    #We Check Dependencies
    #There are only a few dependencies but you have to be sure
    
    ```
    $ sudo apt-get install libmysqlclient-dev python-dev
    ```
    
    #So far it's all just proceed to install pip in our virtual environment or globally
    
    ```
    $ sudo -H pip install mysql-python
    ```
    
    #As you can see now you can create DB and Users for each Django Project

    public by yourfriendcaspian  331  0  4  0

    Calculate MySQL Database Size

    Calculate MySQL Database Size: calc_mysql_db_size.txt
    SELECT table_schema "Database_Name", SUM( data_length + index_length) / 1024 / 1024 
    "Database_Size_in_MB" FROM information_schema.TABLES GROUP BY table_schema;
    
    						

    public by sebastian.piskorski  3670  3  5  2

    [MySQL] Calculate Database size

    [MySQL] Calculate Database size using simple single query.
    SELECT table_schema "Data Base Name",
        sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
        sum( data_free )/ 1024 / 1024 "Free Space in MB"
    FROM information_schema.TABLES
    GROUP BY table_schema ;

    public by p.kontalis  99038  2  5  0

    VTiger Extraction Customers' Data by City

    Extract from Vtiger Customers' List with address and to whom they have been assigned
    SET @city = '[CITY NAME]'; #Replace [CITY NAME] with your preferable city name
    SET @db_name='[DATABASE NAME]'; #Replace [DATABASE NAME] with your database name
    SET @myquery = CONCAT('
    SELECT	', @db_name, '.vtiger_account.accountid AS \'Company Code\',
    		', @db_name, '.vtiger_account.accountname AS \'Company\', 
    		', @db_name, '.vtiger_account.phone AS \'Phone 1\', 
    		', @db_name, '.vtiger_account.otherphone AS \'Phone 2\', 
    		', @db_name, '.vtiger_account.email1 AS \'Email 1\', 
    		', @db_name, '.vtiger_account.email2 AS \'Email 2\', 
    		', @db_name, '.vtiger_account.website AS \'Website\', 
    		', @db_name, '.vtiger_account.fax AS \'Fax\',
    		', @db_name, '.vtiger_accountbillads.bill_city AS \'City\',
    		', @db_name, '.vtiger_accountbillads.bill_code AS \'TK\',
    		', @db_name, '.vtiger_accountbillads.bill_country AS \'Country\',
    		', @db_name, '.vtiger_accountbillads.bill_state AS \'State\',
    		', @db_name, '.vtiger_accountbillads.bill_street AS \'Street\',
    		', @db_name, '.vtiger_accountbillads.bill_pobox AS \'TK\', 
    		', @db_name, '.vtiger_users.last_name AS \'Αssignment το\'
    FROM ', @db_name, '.vtiger_account
    INNER JOIN ', @db_name, '.vtiger_accountbillads 
    ON ', @db_name, '.vtiger_account.accountid = ', @db_name, '.vtiger_accountbillads.accountaddressid
    INNER JOIN ', @db_name, '.vtiger_crmentity
    ON ', @db_name, '.vtiger_account.accountid = ', @db_name, '.vtiger_crmentity.crmid	
    LEFT JOIN ', @db_name, '.vtiger_users
    ON ', @db_name, '.vtiger_crmentity.smownerid = ', @db_name, '.vtiger_users.id
    WHERE ', @db_name, '.vtiger_accountbillads.bill_city=@city
    AND ', @db_name, '.vtiger_crmentity.setype=\'Accounts\' 
    ');
    PREPARE stmt FROM @myquery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
                
    

    public by p.kontalis  2082  0  5  0

    SQL Join with Static Columns

    Identifies the origin of each row after the join of two tables.
    DROP TABLE IF EXISTS signed_A;
    DROP TABLE IF EXISTS signed_B;
    CREATE TEMPORARY TABLE signed_A AS (SELECT 'TableA' AS STATUS, A.col1, A.col2 FROM A);
    CREATE TEMPORARY TABLE signed_B AS (SELECT 'TableB' AS STATUS, hostings.col3 FROM B);
    SELECT * FROM A LEFT JOIN B ON A.col2=B.col3;

    public by p.kontalis  1598  0  5  0

    Move Cell Values from a Table Column to another

    ---Take a look of the join tables before the move
    SELECT * 
    FROM vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    WHERE vtiger_accountshipads.ship_street !="" AND vtiger_accountbillads.bill_street=""
    ---Copy the Values
    UPDATE vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    SET vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street
    WHERE vtiger_accountbillads.bill_street="" AND vtiger_accountshipads.ship_street != "";
    ---Erase the copied values  (or the values are the same in both cells)
    UPDATE vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    SET vtiger_accountshipads.ship_street=""
    WHERE vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street;
    ---Validation of all transfers
    SELECT * #vtiger_accountbillads.accountaddressid,vtiger_accountbillads.bill_code,vtiger_accountshipads.ship_code
    FROM vtigercrm600.vtiger_accountbillads
    LEFT JOIN vtigercrm600.vtiger_accountshipads
    ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
    WHERE (vtiger_accountshipads.ship_city != "" AND vtiger_accountbillads.bill_city="")
    OR (vtiger_accountshipads.ship_code != "" AND vtiger_accountbillads.bill_code="")
    OR (vtiger_accountshipads.ship_country != "" AND vtiger_accountbillads.bill_country="")
    OR (vtiger_accountshipads.ship_state != "" AND vtiger_accountbillads.bill_state="")
    OR (vtiger_accountshipads.ship_pobox != "" AND vtiger_accountbillads.bill_pobox="")
    OR (vtiger_accountshipads.ship_street != "" AND vtiger_accountbillads.bill_street="");
    

    public by p.kontalis  1951  2  5  0

    Contacts Export from VTiger to Google Contacts

    SELECT 
    REPLACE (REPLACE (accountname, '"', '_'), ',', '_') AS 'Name',
    REPLACE (REPLACE (accountname, '"', '_'), ',', '_') AS 'Given Name',
    '' AS 'Additional Name',
    '' AS 'Family Name',
    '' AS 'Yomi Name',
    '' AS 'Given Name Yomi',
    '' AS 'Additional Name Yomi',
    '' AS 'Family Name Yomi',
    '' AS 'Name Prefix',
    '' AS 'Name Suffix',
    '' AS 'Initials',
    '' AS 'Nickname',
    '' AS 'Short Name',
    '' AS 'Maiden Name',
    '' AS 'Birthday',
    '' AS 'Gender',
    '' AS 'Location',
    '' AS 'Billing Information',
    '' AS 'Directory Server',
    '' AS 'Mileage',
    '' AS 'Occupation',
    '' AS 'Hobby',
    '' AS 'Sensitivity',
    '' AS 'Priority',
    '' AS 'Subject',
    '' AS 'Notes',
    '* My Contacts' AS 'Group Membership',
    '* Work' AS 'E-mail 1 - Type',
    REPLACE (REPLACE (email1, '"', '_'), ',', '_') AS 'E-mail 1 - Value',
    'Εργασία 2' AS 'E-mail 2 - Type',
    REPLACE (REPLACE (email2, '"', '_'), ',', '_') AS 'E-mail 2 - Value',
    'Εργασία 2' AS 'Phone 1 - Type',
    REPLACE (REPLACE (phone, '"', '_'), ',', '_') AS 'Phone 1 - Value',
    'Work' AS 'Phone 2 - Type',
    REPLACE (REPLACE (otherphone, '"', '_'), ',', '_') AS 'Phone 2 - Value',
    'Fax' AS 'Phone3 - Type',
    REPLACE (REPLACE (fax, '"', '_'), ',', '_') AS 'Phone 3 - Value',
    'Home' AS 'Address 1 - Type',
    CONCAT('"',bill_street,'\n',bill_city,bill_state,' ',bill_pobox,'\n',bill_country,'"') AS 'Address 1 - Formatted',
    REPLACE (REPLACE (bill_street, '"', '_'), ',', '_') AS 'Address 1 - Street',
    REPLACE (REPLACE (bill_city, '"', '_'), ',', '_') AS 'Address 1 - City',
    '' AS 'Address 1 - PO Box',
    REPLACE (REPLACE (bill_state, '"', '_'), ',', '_') AS 'Address 1 - Region',
    REPLACE (REPLACE (bill_pobox, '"', '_'), ',', '_') AS 'Address 1 - Postal Code',
    REPLACE (REPLACE (bill_country, '"', '_'), ',', '_') AS 'Address 1 - Country',
    '' AS 'Address 1 - Extended Address',
    'Home Page' AS 'Website 1 - Type',
    REPLACE (REPLACE (website, '"', '_'), ',', '_') AS 'Website 1 - Value'
    FROM vtigercrm600.vtiger_account
    LEFT JOIN vtigercrm600.vtiger_accountbillads
    ON vtiger_account.accountid = vtiger_accountbillads.accountaddressid;
    # NOTE: - After the execution click on export button and save it as csv
    # NOTE: The " and , have been replaced by _    
    # NOTE: If you want to use this snippet, consider to change the "vtigercrm600" with your database name. ;)
    

    public by JKCPR  2296  0  5  0

    Relational Operators

    These are primarily used to compare numeric and date/time types.
    // < less than
    // <= less than or equal to
    // > greater than
    // >= greater than or equal to
    
    SELECT <columns> FROM <table> WHERE <column name> < <value>;
    
    SELECT <columns> FROM <table> WHERE <column name> <= <value>;
    
    SELECT <columns> FROM <table> WHERE <column name> > <value>;
    
    SELECT <columns> FROM <table> WHERE <column name> >= <value>;

    public by JKCPR  2385  0  5  1

    SQL query SELECT * WHERE clause AND / OR compare conditionally

    Compare multiple values in a WHERE condition. Test that both conditions are true using the AND keyword, alternatively whether either conditions are true using the OR keyword.
    SELECT <columns> FROM <table> WHERE <condition 1> AND <condition 2> ...;
    SELECT <columns> FROM <table> WHERE <condition 1> OR <condition 2> ...;
    
    //Examples:
    SELECT username FROM users WHERE last_name = "Kong" AND first_name = "King";
    
    SELECT * FROM products WHERE category = "Insects" AND price < 100;
    
    SELECT * FROM movies WHERE title = "The Matrix" OR title = "The Blues Brothers" OR title = "2 IDs 1 Row";
    
    SELECT country FROM countries WHERE population < 3 OR population > 1;

    public by p.kontalis  2689  4  6  0

    Mysql user privileges

    mysql grant user privileges
    CREATE USER 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';
    GRANT ALL PRIVILEGES ON `DATABASE`.* TO 'USERNAME'@'IP';
    FLUSH PRIVILEGES;
    
    # USERNAME: Username
    # IP: The Address for remote access OR localhost for local access. Can be used the % symbol as wildcard
    # PASSWORD: Password without encription
    # DATABASE: The database that we need the user
    • Public Snippets
    • Channels Snippets