SqlServer - Verificare lo stato degli indici

Query di comodità per verificare lo stato di deframmentazione degli indici.

L'ultima colonna contiene il comando di rebuild dell'indice.

SqlServer 2014 - Spring Oauth2 Sql Table Script

Script per la creazione delle tabelle di Spring Security per la gestione del Oauth2 sul db di SQLServer 2014

https://gist.github.com/marcoberri/b02d5c523c0e511bdd18bda18ee5eb38

Oracle - Query - Paginated result with row_number()

Query per eseguire un subset delle row su oracle senza intaccare i parametri di orderby


SELECT *
FROM
  (SELECT FIELDA,
    FIELDB,
    FIELDC,
    ROW_NUMBER() OVER (ORDER BY FIELDC) R
  FROM TABLE_NAME
  WHERE FIELDA = 10
  )
WHERE R >= 10
AND R   <= 15;

From Freedb.org to OrientDB - #4

#1 - #2 - #3

Download a big file about 170 Mb.
Uncompress  ... about 614 Mb and 2630481 file...

to speed up the importation are processed only 1000 files per folder.

sample porting schema from FreeDB to OrientDB:

import code

import com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx;
import com.orientechnologies.orient.core.metadata.schema.OProperty.INDEX_TYPE;
import com.orientechnologies.orient.core.metadata.schema.OType;
import com.orientechnologies.orient.core.record.impl.ODocument;
import com.orientechnologies.orient.core.sql.query.OSQLSynchQuery;
import com.orientechnologies.orient.server.OServer;
import com.orientechnologies.orient.server.OServerMain;
import java.io.File;
import java.io.FileFilter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import org.apache.commons.io.FileUtils;

private String base = "/Users/marco/orientdb/";
private String root_dir = base + "/file_freedb/freedb-complete-20090101/";
private HashMap cache_year = new HashMap();

private HashMap cache_genre = new HashMap();

  private void import_data() {

        try {

            OServer server = OServerMain.create();
            server.startup(new File(base + "/file/conf.xml"));

            ODatabaseDocumentTx db = new ODatabaseDocumentTx("local:" + base + "/freedb");
            if (!db.exists()) {
                db.create();
                System.out.println("create new DB");
            } else {
                db.delete();
                db.create();
                System.out.println("delete and create new DB");
            }


            FileFilter directoryFilter = new FileFilter() {

                public boolean accept(File file) {
                    return file.isDirectory();
                }
            };

            //default index on odocument
            db.begin();


            ODocument oArtist = new ODocument(db, "artist");
            oArtist.field("name", "Various", OType.STRING);
            oArtist.save();

            db.getMetadata().getSchema().getClass("artist").createProperty("name", OType.STRING).createIndex(INDEX_TYPE.FULLTEXT);
            db.getMetadata().getSchema().save();


            ODocument oTrack = new ODocument(db, "track");
            oTrack.field("title", "Various", OType.STRING);
            oTrack.save();

            db.getMetadata().getSchema().getClass("track").createProperty("title", OType.STRING).createIndex(INDEX_TYPE.FULLTEXT);
            db.getMetadata().getSchema().save();


            ODocument oGendr = new ODocument(db, "genre");
            oGendr.field("name", "Various", OType.STRING);
            oGendr.save();

            db.getMetadata().getSchema().getClass("genre").createProperty("name", OType.STRING).createIndex(INDEX_TYPE.UNIQUE);
            db.getMetadata().getSchema().save();


            ODocument oYear = new ODocument(db, "year");
            oYear.field("data", "19000101", OType.DATE);
            oYear.save();

            db.getMetadata().getSchema().getClass("year").createProperty("data", OType.DATE).createIndex(INDEX_TYPE.UNIQUE);
            db.getMetadata().getSchema().save();


            db.commit();


            File[] dirs = new File(root_dir).listFiles(directoryFilter);
            int i = 0;
            for (File dir : dirs) {

                if (!dir.isDirectory()) {
                    continue;
                }

                System.out.println("" + dir);

                int max_file_for_debug = 0;
                Collection files = FileUtils.listFiles(dir, null, true);
                for (File file : files) {
                    if (file.getName().startsWith(".")) {
                        continue;
                    }


                    try {
                        List lines = FileUtils.readLines(file);

                        db.begin();
                        ODocument oDisk = new ODocument(db, "disk");

                        ArrayList tracks = new ArrayList();

                        String titles = "";
                        String extd = "";
                        for (String line : lines) {

                            if (line.startsWith("# Disc length:")) {
                                String length = line.replaceAll("# Disc length:", "").replaceAll("seconds", "").replaceAll("secs", "").trim();

                                oDisk.field("Disc Length", length, OType.INTEGER);
                            }

                            if (line.startsWith("# Revision:")) {
                                String revision = line.replaceAll("# Revision:", "").trim();


                                oDisk.field("revision", revision, OType.INTEGER);
                            }


                            if (line.startsWith("#")) {
                                continue;
                            }

                            String ele[] = line.split("=");

                            if (ele == null || ele.length == 1) {
                                continue;
                            }

                            String key = ele[0];
                            String value = ele[1];


                            if (key.equals("DISKID")) {
                                oDisk.field(key.toLowerCase(), value, OType.STRING);
                            }

                            if (key.equals("DYEAR")) {
                                oDisk.field("year", check_and_create_year(value + "0101", db), OType.LINK);
                            }

                            if (key.equals("DGENRE")) {

                                oDisk.field("genre", check_and_create_genre(value, db), OType.LINK);
                            }

                            //concatenate multiple title lines
                            if (key.equals("DTITLE")) {
                                titles += value;
                            }

                            if (key.equals("EXTD")) {
                                extd += value;
                            }



                            //tracks list
                            if (key.startsWith("TTITLE")) {
                                oTrack = new ODocument(db, "track");
                                oTrack.field("n", key.replaceAll("TTITLE", ""), OType.INTEGER);

                                String tartist = "";


                                oTrack.field("title", getTitle(value));

                                tartist = getAuthor(value);

                                if (!tartist.equals("")) {

                                    oTrack.field("artist", check_and_create_artist(tartist, db), OType.LINK);
                                } else {
                                    oTrack.field("artist");
                                }


                                oTrack.save();
                                tracks.add(oTrack);

                            }

                        }

                        //add track_list
                        if (!tracks.isEmpty()) {
                            oDisk.field("tracks", tracks, OType.EMBEDDEDLIST);
                        }

                        //title and artist disk
                        if (!titles.equals("")) {

                            oDisk.field("title", getTitle(titles));
                            oDisk.field("artist", check_and_create_artist(getAuthor(titles), db), OType.LINK);

                        }

                        //title and artist disk
                        if (!extd.equals("")) {
                            oDisk.field("extd", extd);
                        }

                        oDisk.save();
                        db.commit();

                    } catch (IOException ex) {
                        System.out.println("ex (1):" + ex.getMessage() + ex.getStackTrace().toString());

                        for (StackTraceElement s : ex.getStackTrace()) {
                            System.out.println("" + s);
                        }

                        db.rollback();
                        continue;

                    }


                    i++;
                    if ((i >= 1000) && (i % 1000) == 1) {
                        System.out.println("\t" + file);
                        for (String s : db.getClusterNames()) {
                            System.out.println("cluster: " + s + " - " + db.countClusterElements(s));

                        }

                        //for debug max 1000 file for folder
                        break;
                    }

                }

            }


            db.close();

            //server
            server.shutdown();
        } catch (Exception ex) {
            System.out.println("ex (2):" + ex.getMessage() + ex.getStackTrace().toString());


            for (StackTraceElement s : ex.getStackTrace()) {
                System.out.println("" + s);
            }

        }

    }

    private ODocument check_and_create_artist(String name, ODatabaseDocumentTx db) {


        if (name.equals("")) {
            return null;
        }


        OSQLSynchQuery query = new OSQLSynchQuery("select from artist where name = ?");
        List result = db.command(query).execute(name);


        if (!result.isEmpty()) {
            return (ODocument) result.get(0);

        } else {

            ODocument oArtist = new ODocument(db, "artist");
            oArtist.field("name", /*a*/ name, OType.STRING);
            oArtist.save();

            return oArtist;
        }


    }

    private ODocument check_and_create_year(String year, ODatabaseDocumentTx db) {

        if (cache_year.containsKey(year)) {
            return cache_year.get(year);
        }


        OSQLSynchQuery query = new OSQLSynchQuery("select from year where data = ?");
        List result = db.command(query).execute(year);


        if (!result.isEmpty()) {
            cache_year.put(year, (ODocument) result.get(0));
            return (ODocument) result.get(0);

        } else {

            ODocument oYear = new ODocument(db, "year");
            oYear.field("data", year, OType.DATE);
            oYear.save();
            cache_year.put(year, oYear);

            return oYear;
        }


    }

    private ODocument check_and_create_genre(String genre, ODatabaseDocumentTx db) {

        if (cache_genre.containsKey(genre)) {
            return cache_genre.get(genre);
        }

        OSQLSynchQuery query = new OSQLSynchQuery("select from genre where name = ?");
        List result = db.command(query).execute(genre);

        if (!result.isEmpty()) {
            cache_genre.put(genre, (ODocument) result.get(0));
            return (ODocument) result.get(0);

        } else {

            ODocument oGenre = new ODocument(db, "genre");
            oGenre.field("name", genre, OType.STRING);
            oGenre.save();
            cache_genre.put(genre, oGenre);
            return oGenre;
        }


    }

    private String getTitle(String value) {


        if (value.indexOf("/") == -1) {
            return escape(value);
        }

        try {
            return escape(value.split("/")[0]);
        } catch (Exception e) {
            return "";
        }

    }

    private String getAuthor(String value) {

        if (value.indexOf("/") == -1) {
            return "";
        }

        if (value.split("/").length == 0) {
            return "";
        }

        try {
            return escape(value.split("/")[1]);
        } catch (Exception e) {
            return "";
        }


    }

    private String escape(String s) {
        if (s == null) {
            return s;
        }
        return s.trim().replaceAll("\\[", "").replaceAll("\\]", "").replaceAll("'", "\\\\'");
    }

Lib:

• commons-io-2.0.1.jar
• orient-commons-1.0rc2-SNAPSHOT.jar
• orientdb-client-1.0rc2-SNAPSHOT.jar
• orientdb-core-1.0rc2-SNAPSHOT.jar
• orientdb-enterprise-1.0rc2-SNAPSHOT.jar
• orientdb-server-1.0rc2-SNAPSHOT.jar
• orientdb-tools-1.0rc2-SNAPSHOT.jar
• persistence-api-1.0.jar

after several hours...

cluster: internal - 3
cluster: index - 882
cluster: default - 0
cluster: orole - 3
cluster: ouser - 3
cluster: artist - 43767
cluster: track - 155602
cluster: genre - 995
cluster: year - 114
cluster: disk - 11001

about 4GB of db...

Test Query:


first time:
query:select from artist name like 'Pink%' tot time:25692 ms
next:
query:select from artist name like 'Pink%' tot time:1646 ms

first time:
query:select from disk where artist.name like 'Pink%' tot time: 13388 ms
next:
query:select from disk where artist.name like 'Pink%' tot time: 4714 ms

first time:
query:select from disk where tracks contains ( artist.name like 'Pink%' ) tot time: 1628 ms
next:
query:select from disk where tracks contains ( artist.name like 'Pink%' ) tot time: 1481 ms

first/next time:
query:select from disk where year.data = '19780101' tot time: 906

download code

#1 - #2 - #3

postgresql : eseguire una query da console

..mai che mi ricordo questo comodissimo comando da console

psql <nomedb> -c "select * from..." > file.txt

oppure
<nomedb> -c "update..."

Prestashop - Eliminare i dati test per portare online

TRUNCATE TABLE ps_customer;
TRUNCATE TABLE ps_customer_group;
TRUNCATE TABLE ps_address;
TRUNCATE TABLE ps_orders;
TRUNCATE TABLE ps_order_detail;
TRUNCATE TABLE ps_order_discount;
TRUNCATE TABLE ps_order_history;
TRUNCATE TABLE ps_message;
TRUNCATE TABLE ps_cart;
TRUNCATE TABLE ps_cart_product;
TRUNCATE TABLE ps_cart_discount;
ALTER TABLE ps_customer AUTO_INCREMENT = 0;
ALTER TABLE ps_address AUTO_INCREMENT = 0;
ALTER TABLE ps_orders AUTO_INCREMENT = 0;
ALTER TABLE ps_order_detail AUTO_INCREMENT = 0;
ALTER TABLE ps_order_discount AUTO_INCREMENT = 0;
ALTER TABLE ps_order_history AUTO_INCREMENT = 0;
ALTER TABLE ps_message AUTO_INCREMENT = 0;
ALTER TABLE ps_cart AUTO_INCREMENT = 0;
ALTER TABLE ps_cart_product AUTO_INCREMENT = 0;
ALTER TABLE ps_cart_discount AUTO_INCREMENT = 0;



ps
truncate = delete from

fonte: http://www.prestashop.com/forums/viewthread/9045/installation_configuration___upgrade/solved_how_to_reset_or_delete_all_orders_and_customers

Sql uso di if/case nelle query

 select (case when replace(business,',','')='si' then '2' when replace(business,',','')='no' then '1' else '1'end) from cliente where status='abilitato';

postgresql : database and table size via sql

- questa query può essere utile per eseguire un monitor dello spazio su disco dei db/tabelle.

psql

postgres=# select * from pg_size_pretty(pg_database_size('nomedeldb'));


pg_size_pretty
----------------
1345 MB
(1 row)

size delle tabelle:

SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC;

SELECT relname, (relpages * 8) / 1024 AS size_mb FROM pg_class ORDER BY relpages DESC;

postgres : performace query

su una tabella con circa 94000 righe


EXPLAIN analyze select max(tnode_id) from tnes2.tnode;
Total runtime: 0.071 ms


EXPLAIN analyze select tnode_id from tnes2.tnode order by tnode_id desc limit 1;
Total runtime: 0.053 ms