-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathgixsql-mid-november-update.html
18 lines (17 loc) · 11.5 KB
/
gixsql-mid-november-update.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<!DOCTYPE html><html lang="en-us"><head><meta charset="utf-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>GixSQL: Mid-November update - mridoni's development blog</title><meta name="description" content="GixSQL is undergoing some work to re-implement two features: autocommit support and updatable cursors. Both of them have been there since the first releases, but due to several reasons (essentially lack of usage and bad assumptions) they were left behind and their functionality, in their current incarnation, is dubious. The new implementation sees these two features being moved mainly from the main library (libgixsql) to the database driver libraries (e.g. libgixsql-pgsql), with only a few…"><meta name="generator" content="Publii Open-Source CMS for Static Site"><link rel="canonical" href="https://mridoni.github.io/gixsql-mid-november-update.html"><link rel="alternate" type="application/atom+xml" href="https://mridoni.github.io/feed.xml"><link rel="alternate" type="application/json" href="https://mridoni.github.io/feed.json"><meta property="og:title" content="GixSQL: Mid-November update"><meta property="og:site_name" content="mridoni's development blog"><meta property="og:description" content="GixSQL is undergoing some work to re-implement two features: autocommit support and updatable cursors. Both of them have been there since the first releases, but due to several reasons (essentially lack of usage and bad assumptions) they were left behind and their functionality, in their current incarnation, is dubious. The new implementation sees these two features being moved mainly from the main library (libgixsql) to the database driver libraries (e.g. libgixsql-pgsql), with only a few…"><meta property="og:url" content="https://mridoni.github.io/gixsql-mid-november-update.html"><meta property="og:type" content="article"><style>:root{--body-font:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Oxygen,Ubuntu,Cantarell,"Fira Sans","Droid Sans","Helvetica Neue",Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol";--heading-font:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Oxygen,Ubuntu,Cantarell,"Fira Sans","Droid Sans","Helvetica Neue",Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol";--logo-font:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Oxygen,Ubuntu,Cantarell,"Fira Sans","Droid Sans","Helvetica Neue",Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol";--menu-font:-apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,Oxygen,Ubuntu,Cantarell,"Fira Sans","Droid Sans","Helvetica Neue",Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol"}</style><link rel="stylesheet" href="https://mridoni.github.io/assets/css/style.css?v=c65e4b699f773de4045874d9f49bbe9c"><script type="application/ld+json">{"@context":"http://schema.org","@type":"Article","mainEntityOfPage":{"@type":"WebPage","@id":"https://mridoni.github.io/gixsql-mid-november-update.html"},"headline":"GixSQL: Mid-November update","datePublished":"2022-11-16T18:14","dateModified":"2022-11-16T19:23","description":"GixSQL is undergoing some work to re-implement two features: autocommit support and updatable cursors. Both of them have been there since the first releases, but due to several reasons (essentially lack of usage and bad assumptions) they were left behind and their functionality, in their current incarnation, is dubious. The new implementation sees these two features being moved mainly from the main library (libgixsql) to the database driver libraries (e.g. libgixsql-pgsql), with only a few…","author":{"@type":"Person","name":"Marco Ridoni","url":"https://mridoni.github.io/authors/marco-ridoni/"},"publisher":{"@type":"Organization","name":"Marco Ridoni"}}</script></head><body><div class="site-container"><header class="top" id="js-header"><a class="logo" href="https://mridoni.github.io/">mridoni's development blog</a></header><main><article class="post"><div class="hero"><header class="hero__content"><div class="wrapper"><div class="post__meta"><time datetime="2022-11-16T18:14">November 16, 2022</time></div><h1>GixSQL: Mid-November update</h1></div></header></div><div class="wrapper post__entry"><p>GixSQL is undergoing some work to re-implement two features: autocommit support and updatable cursors. Both of them have been there since the first releases, but due to several reasons (essentially lack of usage and bad assumptions) they were left behind and their functionality, in their current incarnation, is dubious.</p><p>The new implementation sees these two features being moved mainly from the main library (libgixsql) to the database driver libraries (e.g. libgixsql-pgsql), with only a few parameters passed around. The rationale behind this move is that different DBMSs behave in different ways, and trying to keep a unified approach would only lead to confusion (and bugs, a lot of bugs). What follows is the level of support planned for the various DB drivers.</p><h4>Autocommit</h4><p>Autocommit will basically work in three different modes:</p><ul><li>Native: the connection will start in the default mode used by the database</li><li>On: a <code>COMMIT</code>will be executed after each successful statement</li><li>Off: the connection will start by opening a transaction. A new connection will be started after each <code>COMMIT</code>and <code>ROLLBACK</code></li></ul><p>Driver notes:</p><ul><li>MySQL and ODBC (through its own drivers) directly support autocommit to be turned on or off, so the handling of this feature will be passed entirely to the native driver (e.g. libmysqlclient)</li><li>Oracle has no concept of autocommit, just as PostgreSQL, but the first one always work in transaction mode, while transactions must be explicitly started in PostgreSQL. While autocommit (or "transaction mode" in the case of PostgreSQL) have been - and will further be - tested, these differences (and the ones above) can probably lead to different behaviours when switching a single codebase that makes heavy use of the autocommit feature to a different DB, if precautions are not taken and tests performed.</li></ul><h4>Updatable cursors</h4><p>Updatable cursors will allow the update/deletion of a single cursor row after a fetch, using the clause <code>UPDATE...WHERE CURRENT OF mycursor</code>after a cursor has been opened <code>FOR UPDATE</code>.</p><p>Driver notes:</p><ul><li>Oracle directly supports updatable cursors, so the handling of this feature will be passed entirely to the native driver.</li><li>PostgreSQL directly supports updatable cursors when native cursors are enabled - this is the default - in the GixSQL driver (libgixsql-pgsql); in this case the handling of this feature will be passed entirely to the native driver. If cursors are emulated in PostgreSQL, updatable cursors are not available.</li><li>MySQL and SQLite have updatable cursor emulation: the table on which the cursor is opened must have a unique key that will be used for the update. This has been proven to work, but it is obviously (a lot) slower than native support.</li><li>ODBC will defer updatable cursor handling to its own drivers: it depends on them whether updatable cursors will be available or not. As a side note the ODBC driver for MySQL (MySQL Connector/ODBC) uses the same technique illustrated above to emulate updatable cursors.</li></ul><p>While this is the focus of what will be v1.0.19 (some prereleases will surely be published before that) there will be the usual amount of bug fixes and, where possible, smaller features.</p></div><footer class="wrapper post__footer"><p class="post__last-updated">This article was updated on November 16, 2022</p><div class="post__share"><a href="https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fmridoni.github.io%2Fgixsql-mid-november-update.html" class="js-share facebook" rel="nofollow noopener noreferrer"><svg class="icon" aria-hidden="true" focusable="false"><use xlink:href="https://mridoni.github.io/assets/svg/svg-map.svg#facebook"/></svg> <span>Facebook</span> </a><a href="https://twitter.com/share?url=https%3A%2F%2Fmridoni.github.io%2Fgixsql-mid-november-update.html&via=%40quasicomese&text=GixSQL%3A%20Mid-November%20update" class="js-share twitter" rel="nofollow noopener noreferrer"><svg class="icon" aria-hidden="true" focusable="false"><use xlink:href="https://mridoni.github.io/assets/svg/svg-map.svg#twitter"/></svg> <span>Twitter</span> </a><a href="https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmridoni.github.io%2Fgixsql-mid-november-update.html" class="js-share linkedin" rel="nofollow noopener noreferrer"><svg class="icon" aria-hidden="true" focusable="false"><use xlink:href="https://mridoni.github.io/assets/svg/svg-map.svg#linkedin"/></svg> <span>LinkedIn</span></a></div></footer></article><nav class="post__nav"><div class="post__nav-inner"><div class="post__nav-prev"><svg width="1.041em" height="0.416em" aria-hidden="true"><use xlink:href="https://mridoni.github.io/assets/svg/svg-map.svg#arrow-prev"/></svg> <a href="https://mridoni.github.io/gix-ide-v110dev1-pre-release-is-out.html" class="invert post__nav-link" rel="prev"><span>Previous</span> Gix-IDE v1.1.0dev1 (pre-release) is out</a></div><div class="post__nav-next"><a href="https://mridoni.github.io/gixsql-news-about-v1019.html" class="invert post__nav-link" rel="next"><span>Next</span> GixSQL: news about v1.0.19 </a><svg width="1.041em" height="0.416em" aria-hidden="true"><use xlink:href="https://mridoni.github.io/assets/svg/svg-map.svg#arrow-next"/></svg></div></div></nav><div class="post__related related"><div class="wrapper"><h2 class="h5 related__title">You should also read:</h2><article class="related__item"><div class="feed__meta"><time datetime="2023-01-03T18:11" class="feed__date">January 3, 2023</time></div><h3 class="h1"><a href="https://mridoni.github.io/january-update.html" class="invert">January update</a></h3></article><article class="related__item"><div class="feed__meta"><time datetime="2022-12-08T11:49" class="feed__date">December 8, 2022</time></div><h3 class="h1"><a href="https://mridoni.github.io/gix-ide-early-december-update.html" class="invert">Gix-IDE: December update</a></h3></article><article class="related__item"><div class="feed__meta"><time datetime="2022-11-18T12:25" class="feed__date">November 18, 2022</time></div><h3 class="h1"><a href="https://mridoni.github.io/gixsql-news-about-v1019.html" class="invert">GixSQL: news about v1.0.19</a></h3></article></div></div></main><footer class="footer"><div class="footer__copyright"><p>Powered by Publii</p></div><button class="footer__bttop js-footer__bttop" aria-label="Back to top"><svg><title>Back to top</title><use xlink:href="https://mridoni.github.io/assets/svg/svg-map.svg#toparrow"/></svg></button></footer></div><script>window.publiiThemeMenuConfig = {
mobileMenuMode: 'sidebar',
animationSpeed: 300,
submenuWidth: 'auto',
doubleClickTime: 500,
mobileMenuExpandableSubmenus: true,
relatedContainerForOverlayMenuSelector: '.top',
};</script><script defer="defer" src="https://mridoni.github.io/assets/js/scripts.min.js?v=48e9576b9741cf2a93ab25c5689c9f5d"></script><script>var images = document.querySelectorAll('img[loading]');
for (var i = 0; i < images.length; i++) {
if (images[i].complete) {
images[i].classList.add('is-loaded');
} else {
images[i].addEventListener('load', function () {
this.classList.add('is-loaded');
}, false);
}
}</script></body></html>