Showing entries 1 to 10 of 30
10 Older Entries »
Displaying posts with tag: SQLite (reset)
2019 Open Source Database Report: Top Databases, Public Cloud vs. On-Premise, Polyglot Persistence

Ready to transition from a commercial database to open source, and want to know which databases are most popular in 2019? Wondering whether an on-premise vs. public cloud vs. hybrid cloud infrastructure is best for your database strategy? Or, considering adding a new database to your application and want to see which combinations are most popular? We found all the answers you need at the Percona Live event last month, and broke down the insights into the following free trends reports:

[Read more]
Sudoku Recursive Common Table Expression Solver

In this blog post, we’ll look at a solving Sudoku using MySQL 8.0 recursive common table expression.

Vadim was recently having a little Saturday morning fun solving Sudoku using MySQL 8. The whole idea comes from SQLite, where Richard Hipp has come up with some outlandish recursive query examplesWITH clause.

The SQLite query:

WITH RECURSIVE
 input(sud) AS (
   VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
 ),
 digits(z, lp) AS (
   VALUES('1', 1)
   UNION ALL SELECT
   CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
 ),
 x(s, ind) AS (
   SELECT sud, instr(sud, '.') FROM input
   UNION ALL …
[Read more]
Complete Megalist: 25 Helpful Tools For Back-End Developers

 

The website or mobile app is the storefront for participating in the modern digital era. It’s your portal for inviting users to come and survey your products and services. Much attention focuses on front-end development; this is where the HMTL5, CSS, and JavaScript are coded to develop the landing page that everyone sees when they visit your site.

 

But the real magic happens on the backend. This is the ecosystem that really powers your website. One writer has articulated this point very nicely as follows:

 

The technology and programming that “power” a site—what your end user doesn’t see but what makes the site run—is called the back end. Consisting of the server, the database, and the server-side applications, it’s the behind-the-scenes functionality—the brain of a site. …

[Read more]
Using Active Record migrations beyond SQLite

SQLite is really a good tool to set up quick proof of concepts and small applications; however it’s not the most robust solution on the market for working with relational databases. In the open source community two databases take the top of the list: PostgreSQL and MySQL.

I did a small project for my studies. I was using SQLite as I didn’t need much out of it. Curious, I decided to see how the application would behave on other databases and decided to try PostgreSQL and MySQL. I had two problems to solve, and this post is about the first one: how to deal with the migrations. They were as follows:

Active Record automatically put the field id in all of its tables, that’s why it is omitted on the migrations.

In PostgreSQL it …

[Read more]
ProxySQL Admin Interface Is Not Your Typical MySQL Server!

In this blog post, I’ll look at how ProxySQL Admin behaves in some unusual and unexpected ways from a MySQL perspective.

ProxySQL allows you to connect to its admin interface using the MySQL protocol and use familiar tools, like the MySQL command line client, to manage its configuration as a set of configuration tables. This ability may trick you into thinking that you’re working with a stripped-down MySQL server – and expect it to behave like MySQL. 

It would be a mistake to think this! In fact, ProxySQL embeds the SQLite database to store its configuration. As such, it behaves much closer to SQLite!

Below, I’ll show you a few things that confused me at first. All of these are as of ProxySQL 1.3.6 (in case behavior changes in the future).

Fake support for Use command

[Read more]
Some observations on MySQL to sqlite migration & compatibility

I'm experimenting with sqlite as backend database for orchestrator. While orchestrator manages MySQL replication topologies, it also uses MySQL as backend. For some deployments, and I'm looking into such one, having MySQL as backend is a considerable overhead.

This sent me to the route of looking into a self contained orchestrator binary + backend DB. I would have orchestrator spawn up its own backend database instead of connecting to an external one.

Why even relational?

Can't orchestrator just use a key-value backend?

Maybe it could. But frankly I enjoy the power of relational databases, and the versatility they offer has proven itself multiple times with orchestrator, being able to answer …

[Read more]
When simple SQL can be complex

I think SQL is a very simple language, but ofcourse I'm biased.

But even a simple statement might have more complexity to it than you might think.

Do you know what the result is of this statement?

SELECT FALSE = FALSE = TRUE;

scroll down for the answer.



























The answer is: it depends.

You might expect it to return false because the 3 items in the comparison are not equal. But that's not the case.

In PostgreSQL this is the result:

postgres=# SELECT FALSE = FALSE = TRUE;
?column?
----------
t
(1 row)

So it compares FALSE against FALSE, which results in TRUE …

[Read more]
SQLite vs MySQL

Today I got asked from a friend about the differences between SQLite and MySQL and in which cases it is better to use the first or the latter one. You can’t really compare these two, I will try to explain why this is the case and when it is better to use the first or the latter.

SQLite is a single-file based database which is useful for testing and for embedding in applications. This means that all the information is stored in a single file on a file system and you use a SQLite library to open this file to read and write your data.

MySQL is a database server which got different forms to store its data (engines) and will server the data to request comming from applications who connect to it.

SQLite:

  • Fast setup. You only need to include a library into your app.
  • Embedded. No need to think about …
[Read more]
Simple MySQL: Converting ANSI SQL to SQLite3

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

#!/bin/sh
####
# NAME: convert-mysql-to-sqlite3.sh
# AUTHOR: Matt Reid
# DATE: 2011-03-22
# LICENSE: BSD
####
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
   exit 
fi 
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on …
[Read more]
NIST::NVD::Store::SQLite3 1.00.00

It’s been released. Use this with NIST::NVD 1.00.00 and you will be able to perform immediate look-ups of CVE and CWE data given a CPE URN. For instance:

cjac@foxtrot:/usr/src/git/f5/NIST-NVD-Store-SQLite3$ perl Makefile.PL ; make ; make test ; cjac@foxtrot:/usr/src/git/f5/NIST-NVD-Store-SQLite3$ perl -MNIST::NVD::Query -MData::Dumper -e '
$q = NIST::NVD::Query->new(store    => q{SQLite3},database => q{t/data/nvdcve-2.0.db});
$cve_list = $q->cve_for_cpe( cpe => q{cpe:/a:microsoft:ie:7.0.5730.11} );
print Data::Dumper::Dumper { cve_list => $cve_list, first_cvss => $q->cve( cve_id => $cve_list->[0] )->{q{vuln:cvss}} }
'
$VAR1 = {
          'cve_list' => [
                          'CVE-2002-2435',
                          'CVE-2010-5071'
                        ],
          'first_cvss' => {
                            'cvss:base_metrics' => { …
[Read more]
Showing entries 1 to 10 of 30
10 Older Entries »