Re: [sqlite] DATA RACE on sqlite3GlobalConfig.isInit
On 3/21/19 7:43 AM, Плотников Павел wrote: > Hello, > > The race relates to the usage of sqlite3GlobalConfig.isInit variable within > the sqlite3_initialize routine. > > As for 3.27.2 source code, the lines to be highlighted are > > sqlite3.c:153852: if( sqlite3GlobalConfig.isInit ) return SQLITE_OK; > and > sqlite3.c:153938: sqlite3GlobalConfig.isInit = 1; > > The race is that there are no obstacles for the code from these lines to be > executed simultaneously: the if-expression gets evaluated with none of > synchronization applied. That really isn't a data-race in the sense I am used to seeing it. At least not at that level, there is perhaps one at a higher level using that. I presume isInit is in a variable that is appropriately sized so that the fetch and set are atomic operations (if not, then there MIGHT be a race possible). Yes, the test might give either a true or false answer depending on the exact timing of the execution (does the test see the value before or after the set), but that by itself isn't a race, as the mere fact that you have parrallel execution says that both answers are allowed unless something had been done to enforce a particular order. And in particular, both answers would be possible even if you did add a mutual exclusion between those two operations alone so they can't occur 'at the same time', as it just depends on which one happened first. Now, if the first line after seeing that isInit was false, does an initialization that would be improper because it had already been done, then THAT would be a data race, but its scope is bigger than just those two lines. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving CSV import
Or, better yet .header n where n=0 <--no header n>0 <-- number of header lines If .header is not specified then it defaults to zero so breaking backwards is not a concern. On 3/21/2019 19:04, Shawn Wagner wrote: I thought about suggesting that, but I can see it breaking backwards compatibility with existing scripts. I set .header on in my ~/.sqliterc for example, and have things that don't change it before importing csv files and would thus miss a row. (I also have a handy perl script that does all this stuff and more, but it's nice to keep dependencies to a minimum.) On Thu, Mar 21, 2019 at 6:48 PM D Burgess wrote: Agree with all that. A way to skip a header row when the table exists would be useful. How about .header on/off ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving CSV import
I thought about suggesting that, but I can see it breaking backwards compatibility with existing scripts. I set .header on in my ~/.sqliterc for example, and have things that don't change it before importing csv files and would thus miss a row. (I also have a handy perl script that does all this stuff and more, but it's nice to keep dependencies to a minimum.) On Thu, Mar 21, 2019 at 6:48 PM D Burgess wrote: > Agree with all that. > > > A way to skip a header row when the table exists would be useful. > > > How about > > .header on/off > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving CSV import
Agree with all that. > A way to skip a header row when the table exists would be useful. > How about > .header on/off ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Improving CSV import
Importing a CSV file in the command line shell has two different behaviors: if the table to import to doesn't exist, treat the first row of the file as a header with column names to use when creating the table. If the table does exist, treat all rows as data. A way to skip a header row when the table exists would be useful. Sometimes it's awkward to make a copy of a file minus the first line before doing the import. Alternatively, allow .import to pipe from the output of a command, not just a file: .import "tail -n +2 foo.csv |" mytable More work to implement, but a lot more flexible, and it would match the behavior of .output and .once. If the devs are willing to accept user contributed patches to the shell, I'd happy to look into implementing that. Also something I've run into that would be useful: a way to strip leading and trailing whitespace from unquoted fields before inserting them. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] filling a key/value table
Simon Slavin wrote: > I wanted to speak against including a BLOB field in a compound PRIMARY KEY. That depends on the size of the blob. If it contains 'normal'-sized values, it's just as efficient as other types. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] filling a key/value table
On 21 Mar 2019, at 4:04pm, Hick Gunter wrote: > CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary > key (keyid, value, location) ) WITHOUT ROWID; Can't answer the question you posed and I'm happy to see Igor could. But I wanted to speak against including a BLOB field in a compound PRIMARY KEY. The PRIMARY KEY gets used a lot when SQLite does anything with a row, and having to match a whole BLOB and then compound it is going to take some time. I think it would be faster to let SQLite do its normal rowid thing and define the above PRIMARY KEY as a UNIQUE INDEX instead. Of course, it may be faster or more convenient to you in some other way in which case I stand corrected. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] filling a key/value table
On 3/21/2019 12:04 PM, Hick Gunter wrote:
I have an external data store that is accessible to sqlite as a virtual table.
The equivalent SQL declaration is similar to:
CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size INTEGER,
...);
I would like to create an index as a native SQLite table declared like:
CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key
(keyid, value, location) ) WITHOUT ROWID;
The fields of interest are stored in a config table:
CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT );
INSERT INTO logkey(name) VALUES ('type'),('name'),('size');
The naive method of inserting values is thus:
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.type,l.location from
logkey k, logfile l where k.name = 'type'AND l.type IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.name,l.location from
logkey k, logfile l where k.name = 'name'AND l.name IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.size,l.location from
logkey k, logfile l where k.name = 'size'AND l.size IS NOT NULL;
This hast he disadvantage of requiring a complete scan of the virtual logidx
table for each kind of entry.
Any ideas on how to create all the tuples with only one pass of the logidx
table?
Something like this:
INSERT INTO logidx(keyid,value,location)
SELECT k.id,
(case k.name when 'type' then l.type when 'name' then l.name when 'size' then
l.size else NULL end) AS val,
l.location
FROM logfile l, logkey k WHERE val IS NOT NULL;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] filling a key/value table
I have an external data store that is accessible to sqlite as a virtual table.
The equivalent SQL declaration is similar to:
CREATE TABLE logfile (timestamp INTEGER, type INTEGER, name TEXT, size INTEGER,
...);
I would like to create an index as a native SQLite table declared like:
CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary key
(keyid, value, location) ) WITHOUT ROWID;
The fields of interest are stored in a config table:
CREATE TABLE logkey( ID INTEGER PRIMARY KEY, name TEXT );
INSERT INTO logkey(name) VALUES ('type'),('name'),('size');
The naive method of inserting values is thus:
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.type,l.location from
logkey k, logfile l where k.name = 'type'AND l.type IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.name,l.location from
logkey k, logfile l where k.name = 'name'AND l.name IS NOT NULL;
INSERT INTO logidx(keyid,value,location) SELECT k.id,l.size,l.location from
logkey k, logfile l where k.name = 'size'AND l.size IS NOT NULL;
This hast he disadvantage of requiring a complete scan of the virtual logidx
table for each kind of entry.
Any ideas on how to create all the tuples with only one pass of the logidx
table?
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DATA RACE on sqlite3GlobalConfig.isInit
Hello, The race relates to the usage of sqlite3GlobalConfig.isInit variable within the sqlite3_initialize routine. As for 3.27.2 source code, the lines to be highlighted are sqlite3.c:153852: if( sqlite3GlobalConfig.isInit ) return SQLITE_OK; and sqlite3.c:153938: sqlite3GlobalConfig.isInit = 1; The race is that there are no obstacles for the code from these lines to be executed simultaneously: the if-expression gets evaluated with none of synchronization applied. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] proposal: make the archive option -Au to update only newer files. /Patch included/
Yes, timestamps only.
Sqlar does not has a column for checksum or sha.
Here comes the patch:
Index: src/shell.c.in
==
--- src/shell.c.in
+++ src/shell.c.in
@@ -5820,10 +5820,30 @@
" mtime,\n"
" data\n"
" FROM fsdir(%Q,%Q)\n"
" WHERE lsmode(mode) NOT LIKE '?%%';"
};
+
+ // updates only newer files
+ const char * zInsertFmtNewer =
+"REPLACE INTO %s(name,mode,mtime,sz,data)\n"
+ " SELECT\n"
+ " %s,\n"
+ " fs.mode,\n"
+ " fs.mtime,\n"
+ " CASE substr(lsmode(fs.mode),1,1)\n"
+ " WHEN '-' THEN length(fs.data)\n"
+ " WHEN 'd' THEN 0\n"
+ " ELSE -1 END,\n"
+ " sqlar_compress(fs.data)\n"
+ " FROM fsdir(%Q,%Q) as fs\n"
+"left join sqlar on sqlar.name = fs.name \n"
+ " WHERE lsmode(fs.mode) NOT LIKE '?%%' \n"
+"and (fs.mtime > sqlar.mtime or sqlar.name isnull) \n"
+";\n";
+
+
int i; /* For iterating through azFile[] */
int rc; /* Return code */
const char *zTab = 0; /* SQL table into which to insert */
char *zSql;
char zTemp[50];
@@ -5856,13 +5876,23 @@
if( rc!=SQLITE_OK ) goto end_ar_transaction;
}
rc = arExecSql(pAr, zCreate);
}
for(i=0; inArg && rc==SQLITE_OK; i++){
-char *zSql2 = sqlite3_mprintf(zInsertFmt[pAr->bZip], zTab,
-pAr->bVerbose ? "shell_putsnl(name)" : "name",
+
+char *zSql2;
+if( bUpdate!=0 && !pAr->bZip) {
+// updates only newer files
+zSql2 = sqlite3_mprintf(zInsertFmtNewer, zTab,
+pAr->bVerbose ? "shell_putsnl(fs.name)" : "fs.name",
pAr->azArg[i], pAr->zDir);
+} else {
+zSql2 = sqlite3_mprintf(zInsertFmt[pAr->bZip], zTab,
+pAr->bVerbose ? "shell_putsnl(name)" : "name",
+pAr->azArg[i], pAr->zDir);
+
+}
rc = arExecSql(pAr, zSql2);
sqlite3_free(zSql2);
}
end_ar_transaction:
if( rc!=SQLITE_OK ){
On Wed, 20 Mar 2019, 18:18 Dominique Devienne, wrote:
> On Wed, Mar 20, 2019 at 5:13 PM Захар Малиновский <
> zakhar.malinovs...@gmail.com> wrote:
>
> > Here I include patch file with the changes: fossil diff --from trunk
> .\src\
> > shell.c.in > patch-updateOnlyNewer.patch
> > (see patch file patch-updateOnlyNewer.patch)
> >
>
> Copy/paste it inline to your message. Attachments are stripped on this ML.
> --DD
>
> PS: based on timestamps only? Using a checksum (MD5, SHA1, SHA3, whatever)
> would be "safer", if a little slower.
> Of course, that assumes SQLar uses a checksum. I don't recall, I thought
> not.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
