[sqlite] Banking calender (was: Question about floating point)

[Hick Gunter]

> ...Even bankers, ever counting pennies, approximate to compute interest and 
> averages.  Little known fact: sometimes they compute interest on the basis of 
> a 360-day year.
>
>--jkl
>

Bankers calculate interest for 30 days per month, independent of the actual 
number of days, leaving 5 days of "no interest" per year. A similar practice 
was also seen in the Maya Haab (civil) Calendar, which has 18 months of 20 days 
each, plus a nineteenth "month" of 5 "bad hair days" (actually "bad luck 
days"). The background of the Maya Tzolkin (ritual) Calender with ist prominent 
bases 13 (months/year) and 20 (days/month and all multiples of years) is 
unclear.


___
 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

Re: [sqlite] SQLite Application Question

[Chris Locke]

What application are you using to build your application?  You mentioned
Visual Studio, so .NET?  If so, are you using the SQLite library from
system.data.sqlite.org?  Are you using c# or vb?

My settings table is a lot simpler.  id, setting and value.  3 columns.
Possibly 4, adding a 'code' column. The 'setting' column holds the full
setting you want to store, eg, 'main form height', or 'main form
windowstate'.  I can have user settings in this via 'chris/main form
height'.  I can then store that setting name as a constant in my
application, so its accessible via Intellisense.  Doing a series of quick
database lookups is relatively cheap.  You can also group the settings if
need be, so 'main form/height' and 'main form/windowstate' so you could
pull out a group of settings with one database query.
Happy to link you to a sample if needed.  A simple (although bloaty!)
database class can be used for the mundane database work - reading,
creating, editing and deleting records.  I tend to ensure my databases have
unique rowIds, and use these for the glue for relationships.


Thanks,
Chris

On Thu, Dec 20, 2018 at 3:37 AM Roger Schlueter  wrote:

> On 12/19/2018 10:02, Jens Alfke wrote:
> >> On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> >>
> >> I am starting work on a prototype application so this might be an
> excellent opportunity to use SQLite for my application file format.  Part
> of this would be the saving and restoring of GUI elements such as window
> positions and sizes, control states, themes, etc.
> > IMHO something like JSON is a good format for such config/preference
> data, instead of having a table with a column for every pref. During
> development you’ll often be adding new prefs, and it’s a pain to have to
> update a CREATE TABLE statement every time you add one. It’s even more of a
> pain to have to handle a schema change with ALTER TABLE in an app upgrade
> that adds a new pref. If you use JSON you just have to come up with a new
> string to use as the key for each pref. It’s also easy to have structured
> values like arrays or nested objects. (FWIW, his is essentially the way
> that Apple OS’s manage app prefs via the NSUserDefaults class.)
>
> JSON or XML: Two sides of the same coin.  If I wanted to go the
> separate file approach, I'd just use the settings class of Visual
> Studio since all the required plumbing is already in place.
>
> More importantly, as I noted this is a prototype (read: test)
> application so it is a good opportunity for me to get my feet wet
> with SQLite since I'm a n00b with it.
>
> > Of course you can save the JSON in the database file. Just create a
> ‘prefs’ table with one blob column for the JSON.
> >
> > A related solution is to store each named pref as a row in the ‘prefs’
> table, identified by a ‘key’ column.
>
> In fact, this statement makes the concerns you raised in the first
> paragraph moot.  A simple table with four columns:
>
>  1. Window name
>  2. Control name
>  3. Control property
>  4. Property value
>
> covers all the possibilities, no ALTER table necessary.  If I want
> to enable per user values, I'd just add a User column.
>
> In short, the design part is easy IMO.  I'm still hoping to see some
> examples since, surely, I'm not the first person to go this route.
> >
> > —Jens
> > ___
> > 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] SQLite Application Question

[Roger Schlueter]

On 12/19/2018 10:02, Jens Alfke wrote:

On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:

I am starting work on a prototype application so this might be an excellent 
opportunity to use SQLite for my application file format.  Part of this would 
be the saving and restoring of GUI elements such as window positions and sizes, 
control states, themes, etc.

IMHO something like JSON is a good format for such config/preference data, 
instead of having a table with a column for every pref. During development 
you’ll often be adding new prefs, and it’s a pain to have to update a CREATE 
TABLE statement every time you add one. It’s even more of a pain to have to 
handle a schema change with ALTER TABLE in an app upgrade that adds a new pref. 
If you use JSON you just have to come up with a new string to use as the key 
for each pref. It’s also easy to have structured values like arrays or nested 
objects. (FWIW, his is essentially the way that Apple OS’s manage app prefs via 
the NSUserDefaults class.)


   JSON or XML: Two sides of the same coin.  If I wanted to go the
   separate file approach, I'd just use the settings class of Visual
   Studio since all the required plumbing is already in place.

   More importantly, as I noted this is a prototype (read: test)
   application so it is a good opportunity for me to get my feet wet
   with SQLite since I'm a n00b with it.


Of course you can save the JSON in the database file. Just create a ‘prefs’ 
table with one blob column for the JSON.

A related solution is to store each named pref as a row in the ‘prefs’ table, 
identified by a ‘key’ column.


   In fact, this statement makes the concerns you raised in the first
   paragraph moot.  A simple table with four columns:

1. Window name
2. Control name
3. Control property
4. Property value

   covers all the possibilities, no ALTER table necessary.  If I want
   to enable per user values, I'd just add a User column.

In short, the design part is easy IMO.  I'm still hoping to see some 
examples since, surely, I'm not the first person to go this route.


—Jens
___
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] Question about floating point

[James K. Lowden]

On Tue, 18 Dec 2018 17:34:29 -0500
Dennis Clarke  wrote:

> some serious reading and experiments are needed to get a good
> handle on why numerical computation is as much art as it is science.
> If we wander into the problem without sufficient study and VERY
> careful consideration then we are doomed to repeat the errors of the
> past. 

I think perhaps you left out "Numerical Methods for Scientists and
Engineers", by Richard Hamming.  :-)  

But when you boil it down, the answer is there is no answer, is there?
The best advice is to understand where things can go wrong, and stay
away from them.  

The truth is that any system for representing numbers is forced to
represent some numbers approximately.  

We think "pen and paper" and the good old decimal system is the gold
standard, but what of ? ?  Even bankers, ever counting pennies,
approximate to compute interest and averages.  Little known fact:
sometimes they compute interest on the basis of a 360-day year.  

--jkl


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

sqlite is not immune to wandering through bad pointers, because code
coverage tests don't test for malicious data... I found a null pointer
crash in sqlite earlier this year. I could see Mallory crafting a database
that had carefully corrupted structures in it that smashed the stack.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Wed, Dec 19, 2018 at 4:57 PM Keith Medcalf  wrote:

>
>
> >All I meant was that with a decimal exponent, the units could be
> >dollars,
> >and additions and subtractions of cents would be exact (assuming the
> >mantissa has enough bits), with no worries about rounding. Which is
> >the
> >basis for this whole discussion.
>
> This is called fixed point.  All that is required is that you keep track
> of the decimal point yourself...sort of like using a slide rule.
>

Er, no. Not at all.

It's called floating point with a decimal exponent. Calculations in cents
come out exact*, calculations in mils come out exact*, and the machine
keeps track of the decimal point.

* Assuming that the input numbers are integer cents (exponent -2) or mils
(exponent -3)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Keith Medcalf]

>All I meant was that with a decimal exponent, the units could be
>dollars,
>and additions and subtractions of cents would be exact (assuming the
>mantissa has enough bits), with no worries about rounding. Which is
>the
>basis for this whole discussion.

This is called fixed point.  All that is required is that you keep track of the 
decimal point yourself...sort of like using a slide rule.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Gerry Snyder]

On Mon, Dec 17, 2018 at 10:04 AM Larry Brasfield 
wrote:

> Gerry Snyder wrote:
> < I don't think anyone has pointed out that the "evil" is not floating
> point, it is the binary exponent.
>
> Disregarding the “evil” appellation, the fundamental fact is that, with
> modern floating point hardware (implementing the IEEE-754 standard), only
> that subset of rational numbers having a denominator which is a power of 2
> can be represented.  If that is what you were trying to say, I would point
> out that it is not the representation of the exponent (binary or otherwise)
> that creates the mismatch with (many) rational numbers having a denominator
> which is a power of 10; it is that many such denominators cannot be
> represented at all when the interpretation of the exponent Ne is as 2 ^ Ne.


All I meant was that with a decimal exponent, the units could be dollars,
and additions and subtractions of cents would be exact (assuming the
mantissa has enough bits), with no worries about rounding. Which is the
basis for this whole discussion.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "make test" fails on Mageia Linux v7 x86-64

[Dan Kennedy]

On 12/19/2018 12:55 AM, Shlomi Fish wrote:

Hi,

With the attached bash script on Mageia Linux v7 x86-64 I consistently get the
test failures here:

https://www.shlomifish.org/Files/files/text/sqlite-mga7-rpm-build.txt.xz

this is with sqlite 3.26.0.

This affects our rpm %check phase.

Can you help?


What is your Tcl version (run the "testfixture" binary and type "set 
tcl_patchLevel" to find out)?


Upgrading Tcl will most likely fix this.

Dan.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Simon Slavin]

On 19 Dec 2018, at 6:19pm, Jens Alfke  wrote:

> 2. Mallory uses something like the ’sqlite3’ tool to open the database and 
> execute a CREATE TRIGGER statement whose trigger SQL exploits a vulnerability 
> to do something nasty like remote code execution.

I'm not sure how you would do that purely inside a trigger.  You can't just 
specially craft a BLOB with bad content.  I think it would need participation 
from the software making the call to the API.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Jens Alfke]

> On Dec 18, 2018, at 2:13 PM, Richard Hipp  wrote:
> 
> I am not aware of any other applications that deliberately run SQL
> from anonymous sources

In applications that use SQLite databases as a file format, couldn’t a 
malicious document be created that uses a trigger to run SQL that triggers an 
exploit when the document/database is edited? In other words:

1. Mallory creates or obtains some innocuous document.
2. Mallory uses something like the ’sqlite3’ tool to open the database and 
execute a CREATE TRIGGER statement whose trigger SQL exploits a vulnerability 
to do something nasty like remote code execution.
3. Mallory passes the document to Alice.
4. Alice opens the document and makes a change that causes SQLite to update a 
table in a way that activates the trigger.
5. The malicious SQL runs in the application process on Alice’s computer and 
does its business.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Simon Slavin]

On 19 Dec 2018, at 6:02pm, Jens Alfke  wrote:

> Of course you can save the JSON in the database file. Just create a ‘prefs’ 
> table with one blob column for the JSON.
> 
> A related solution is to store each named pref as a row in the ‘prefs’ table, 
> identified by a ‘key’ column.

Or you could use SQLite's JSON Extension:



Settings and preferences do tend to lend themselves to a nesting JSON 
dictionary.  I've never tried using SQLite for that, through I stopped writing 
that sort of software years ago.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Application Question

[Jens Alfke]

> On Dec 18, 2018, at 7:46 PM, Roger Schlueter  wrote:
> 
> I am starting work on a prototype application so this might be an excellent 
> opportunity to use SQLite for my application file format.  Part of this would 
> be the saving and restoring of GUI elements such as window positions and 
> sizes, control states, themes, etc.

IMHO something like JSON is a good format for such config/preference data, 
instead of having a table with a column for every pref. During development 
you’ll often be adding new prefs, and it’s a pain to have to update a CREATE 
TABLE statement every time you add one. It’s even more of a pain to have to 
handle a schema change with ALTER TABLE in an app upgrade that adds a new pref. 
If you use JSON you just have to come up with a new string to use as the key 
for each pref. It’s also easy to have structured values like arrays or nested 
objects. (FWIW, his is essentially the way that Apple OS’s manage app prefs via 
the NSUserDefaults class.)

Of course you can save the JSON in the database file. Just create a ‘prefs’ 
table with one blob column for the JSON.

A related solution is to store each named pref as a row in the ‘prefs’ table, 
identified by a ‘key’ column.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

[Chris Locke]

> Then add foreign key constraints so the relations between the tables
> are explicit...

On the GitHub page for the database, it states that, "RowIds, Foreign keys,
secondary keys, defaults and cascade have not been ported."
Most of the tools to create a 'proper' database...
But otherwise, an interesting concept.

"The data was generated, and as such there are inconsistencies and subtle
problems. Rather than removing them, we decided to leave the contents
untouched, and use these issues as data cleaning exercises."


Chris


On Wed, Dec 19, 2018 at 12:16 PM Shawn Wagner 
wrote:

> I'd start by making the employees table a normal rowid one with an INTEGER
> PRIMARY KEY (*Not* INT) column, and change all those VARCHAR, CHAR and DATE
> column types to TEXT (or NUMERIC for the dates depending on the values they
> hold).
>
> Then add foreign key constraints so the relations between the tables are
> explicit...
>
> On Wed, Dec 19, 2018, 4:06 AM Arun - Siara Logics (cc)  wrote:
>
> > Hi Chris,
> >
> > I don't own the MySQL side of the db, but its easy for me to change
> > anything on Sqlite side.  To me the data looks decent for testing and
> > creating applications for demo or learning.
> >
> > I am giving below the script and I will incorporate any other suggestions
> > you may come up with:
> >
> > CREATE TABLE employees (
> > emp_no  INT NOT NULL,
> > birth_date  DATENOT NULL,
> > first_name  VARCHAR(14) NOT NULL,
> > last_name   VARCHAR(16) NOT NULL,
> > gender  CHAR(1) NOT NULL,
> > hire_date   DATENOT NULL,
> > PRIMARY KEY (emp_no)
> > ) without rowid;
> > CREATE TABLE departments (
> > dept_no CHAR(4) NOT NULL,
> > dept_name   VARCHAR(40) NOT NULL,
> > PRIMARY KEY (dept_no)
> > ) without rowid;
> > CREATE TABLE dept_manager (
> >dept_no  CHAR(4) NOT NULL,
> >emp_no   INT NOT NULL,
> >from_dateDATENOT NULL,
> >to_date  DATENOT NULL,
> >PRIMARY KEY  (emp_no, dept_no)
> > ) without rowid;
> > CREATE TABLE dept_emp (
> > emp_no  INT NOT NULL,
> > dept_no CHAR(4) NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATENOT NULL,
> > PRIMARY KEY (emp_no,dept_no)
> > ) without rowid;
> > CREATE TABLE titles (
> > emp_no  INT NOT NULL,
> > title   VARCHAR(50) NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATE,
> > PRIMARY KEY (emp_no,title, from_date)
> > ) without rowid;
> > CREATE TABLE salaries (
> > emp_no  INT NOT NULL,
> > salary  INT NOT NULL,
> > from_date   DATENOT NULL,
> > to_date DATENOT NULL,
> > PRIMARY KEY (emp_no, from_date)
> > ) without rowid;
> > CREATE INDEX emp_first_name on employees (first_name);
> > CREATE INDEX emp_last_name on employees (last_name);
> >
> > Regards
> > Arun
> >
> >   On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke <
> > sql...@chrisjlocke.co.uk> wrote 
> >  > The scheme (for me) is like nails on a chalkboard.  'dept_no' but
> > defined
> >  > as a 'CHAR', then 'emp_no' as an INT.
> >  > Fields with '_no' are read as 'number' and so should be a number.  OK,
> > that
> >  > doesn't always work for 'telephone_no' (they usually start with a 0
> ...
> >  > well, they do in the UK where I am...)
> >  > But I digress..
> >  >
> >  >
> >  > Chris
> >  >
> >  >
> >  > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc)
>  > >
> >  > wrote:
> >  >
> >  > > This project (https://github.com/siara-cc/employee_db) hosts the
> > Sqlite3
> >  > > db file ported from mysql test_db found at
> >  > > https://github.com/datacharmer/test_db. It can be used to test your
> >  > > applications and database servers. To use this project, download
> >  > > employees.db.bz2, unzip and open using sqlite3 command line tool.
> >  > >
> >  > > The original data was created by Fusheng Wang and Carlo Zaniolo at
> > Siemens
> >  > > Corporate Research. The data is in XML format.
> >  > > http://timecenter.cs.aau.dk/software.htm
> >  > >
> >  > > Giuseppe Maxia made the relational schema and Patrick Crews exported
> > the
> >  > > data in relational format.
> >  > >
> >  > > The database contains about 300,000 employee records with 2.8
> million
> >  > > salary entries. The export data is 167 MB, which is not huge, but
> > heavy
> >  > > enough to be non-trivial for testing.
> >  > >
> >  > > A picture of the schema can be found at:
> >  > >
> >
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true
> >  > >
> >  > > Regards
> >  > > Arun - Siara Logics (cc)
> >  > >
> >  > >
> >  > > ___
> >  > > sqlite-users mailing list
> >  > > sqlite-users@mailinglists.sqlite.org
> >  > >
> 

Re: [sqlite] [EXTERNAL] Re: Sample Employee database ported to SQLite from MySQL

[Hick Gunter]

Perhaps it is designed to be "somewhat nonstandard" in order to enable edge 
cases in testing.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Chris Locke
Gesendet: Mittwoch, 19. Dezember 2018 11:55
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Sample Employee database ported to SQLite from 
MySQL

The scheme (for me) is like nails on a chalkboard.  'dept_no' but defined as a 
'CHAR', then 'emp_no' as an INT.
Fields with '_no' are read as 'number' and so should be a number.  OK, that 
doesn't always work for 'telephone_no' (they usually start with a 0 ...
well, they do in the UK where I am...)
But I digress..


Chris


On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) 
wrote:

> This project (https://github.com/siara-cc/employee_db) hosts the
> Sqlite3 db file ported from mysql test_db found at
> https://github.com/datacharmer/test_db. It can be used to test your
> applications and database servers. To use this project, download
> employees.db.bz2, unzip and open using sqlite3 command line tool.
>
> The original data was created by Fusheng Wang and Carlo Zaniolo at
> Siemens Corporate Research. The data is in XML format.
> http://timecenter.cs.aau.dk/software.htm
>
> Giuseppe Maxia made the relational schema and Patrick Crews exported
> the data in relational format.
>
> The database contains about 300,000 employee records with 2.8 million
> salary entries. The export data is 167 MB, which is not huge, but
> heavy enough to be non-trivial for testing.
>
> A picture of the schema can be found at:
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.p
> ng?raw=true
>
> Regards
> Arun - Siara Logics (cc)
>
>
> ___
> 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


___
 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

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

[Shawn Wagner]

I'd start by making the employees table a normal rowid one with an INTEGER
PRIMARY KEY (*Not* INT) column, and change all those VARCHAR, CHAR and DATE
column types to TEXT (or NUMERIC for the dates depending on the values they
hold).

Then add foreign key constraints so the relations between the tables are
explicit...

On Wed, Dec 19, 2018, 4:06 AM Arun - Siara Logics (cc)  Hi Chris,
>
> I don't own the MySQL side of the db, but its easy for me to change
> anything on Sqlite side.  To me the data looks decent for testing and
> creating applications for demo or learning.
>
> I am giving below the script and I will incorporate any other suggestions
> you may come up with:
>
> CREATE TABLE employees (
> emp_no  INT NOT NULL,
> birth_date  DATENOT NULL,
> first_name  VARCHAR(14) NOT NULL,
> last_name   VARCHAR(16) NOT NULL,
> gender  CHAR(1) NOT NULL,
> hire_date   DATENOT NULL,
> PRIMARY KEY (emp_no)
> ) without rowid;
> CREATE TABLE departments (
> dept_no CHAR(4) NOT NULL,
> dept_name   VARCHAR(40) NOT NULL,
> PRIMARY KEY (dept_no)
> ) without rowid;
> CREATE TABLE dept_manager (
>dept_no  CHAR(4) NOT NULL,
>emp_no   INT NOT NULL,
>from_dateDATENOT NULL,
>to_date  DATENOT NULL,
>PRIMARY KEY  (emp_no, dept_no)
> ) without rowid;
> CREATE TABLE dept_emp (
> emp_no  INT NOT NULL,
> dept_no CHAR(4) NOT NULL,
> from_date   DATENOT NULL,
> to_date DATENOT NULL,
> PRIMARY KEY (emp_no,dept_no)
> ) without rowid;
> CREATE TABLE titles (
> emp_no  INT NOT NULL,
> title   VARCHAR(50) NOT NULL,
> from_date   DATENOT NULL,
> to_date DATE,
> PRIMARY KEY (emp_no,title, from_date)
> ) without rowid;
> CREATE TABLE salaries (
> emp_no  INT NOT NULL,
> salary  INT NOT NULL,
> from_date   DATENOT NULL,
> to_date DATENOT NULL,
> PRIMARY KEY (emp_no, from_date)
> ) without rowid;
> CREATE INDEX emp_first_name on employees (first_name);
> CREATE INDEX emp_last_name on employees (last_name);
>
> Regards
> Arun
>
>   On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke <
> sql...@chrisjlocke.co.uk> wrote 
>  > The scheme (for me) is like nails on a chalkboard.  'dept_no' but
> defined
>  > as a 'CHAR', then 'emp_no' as an INT.
>  > Fields with '_no' are read as 'number' and so should be a number.  OK,
> that
>  > doesn't always work for 'telephone_no' (they usually start with a 0 ...
>  > well, they do in the UK where I am...)
>  > But I digress..
>  >
>  >
>  > Chris
>  >
>  >
>  > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc)  >
>  > wrote:
>  >
>  > > This project (https://github.com/siara-cc/employee_db) hosts the
> Sqlite3
>  > > db file ported from mysql test_db found at
>  > > https://github.com/datacharmer/test_db. It can be used to test your
>  > > applications and database servers. To use this project, download
>  > > employees.db.bz2, unzip and open using sqlite3 command line tool.
>  > >
>  > > The original data was created by Fusheng Wang and Carlo Zaniolo at
> Siemens
>  > > Corporate Research. The data is in XML format.
>  > > http://timecenter.cs.aau.dk/software.htm
>  > >
>  > > Giuseppe Maxia made the relational schema and Patrick Crews exported
> the
>  > > data in relational format.
>  > >
>  > > The database contains about 300,000 employee records with 2.8 million
>  > > salary entries. The export data is 167 MB, which is not huge, but
> heavy
>  > > enough to be non-trivial for testing.
>  > >
>  > > A picture of the schema can be found at:
>  > >
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true
>  > >
>  > > Regards
>  > > Arun - Siara Logics (cc)
>  > >
>  > >
>  > > ___
>  > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

[Arun - Siara Logics (cc)]

Hi Chris,

I don't own the MySQL side of the db, but its easy for me to change anything on 
Sqlite side.  To me the data looks decent for testing and creating applications 
for demo or learning.

I am giving below the script and I will incorporate any other suggestions you 
may come up with:

CREATE TABLE employees (
emp_no  INT NOT NULL,
birth_date  DATENOT NULL,
first_name  VARCHAR(14) NOT NULL,
last_name   VARCHAR(16) NOT NULL,
gender  CHAR(1) NOT NULL,
hire_date   DATENOT NULL,
PRIMARY KEY (emp_no)
) without rowid;
CREATE TABLE departments (
dept_no CHAR(4) NOT NULL,
dept_name   VARCHAR(40) NOT NULL,
PRIMARY KEY (dept_no)
) without rowid;
CREATE TABLE dept_manager (
   dept_no  CHAR(4) NOT NULL,
   emp_no   INT NOT NULL,
   from_dateDATENOT NULL,
   to_date  DATENOT NULL,
   PRIMARY KEY  (emp_no, dept_no)
) without rowid;
CREATE TABLE dept_emp (
emp_no  INT NOT NULL,
dept_no CHAR(4) NOT NULL,
from_date   DATENOT NULL,
to_date DATENOT NULL,
PRIMARY KEY (emp_no,dept_no)
) without rowid;
CREATE TABLE titles (
emp_no  INT NOT NULL,
title   VARCHAR(50) NOT NULL,
from_date   DATENOT NULL,
to_date DATE,
PRIMARY KEY (emp_no,title, from_date)
) without rowid;
CREATE TABLE salaries (
emp_no  INT NOT NULL,
salary  INT NOT NULL,
from_date   DATENOT NULL,
to_date DATENOT NULL,
PRIMARY KEY (emp_no, from_date)
) without rowid;
CREATE INDEX emp_first_name on employees (first_name);
CREATE INDEX emp_last_name on employees (last_name);

Regards
Arun

  On Wed, 19 Dec 2018 16:25:11 +0530 Chris Locke  
wrote  
 > The scheme (for me) is like nails on a chalkboard.  'dept_no' but defined
 > as a 'CHAR', then 'emp_no' as an INT.
 > Fields with '_no' are read as 'number' and so should be a number.  OK, that
 > doesn't always work for 'telephone_no' (they usually start with a 0 ...
 > well, they do in the UK where I am...)
 > But I digress..
 > 
 > 
 > Chris
 > 
 > 
 > On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) 
 > wrote:
 > 
 > > This project (https://github.com/siara-cc/employee_db) hosts the Sqlite3
 > > db file ported from mysql test_db found at
 > > https://github.com/datacharmer/test_db. It can be used to test your
 > > applications and database servers. To use this project, download
 > > employees.db.bz2, unzip and open using sqlite3 command line tool.
 > >
 > > The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens
 > > Corporate Research. The data is in XML format.
 > > http://timecenter.cs.aau.dk/software.htm
 > >
 > > Giuseppe Maxia made the relational schema and Patrick Crews exported the
 > > data in relational format.
 > >
 > > The database contains about 300,000 employee records with 2.8 million
 > > salary entries. The export data is 167 MB, which is not huge, but heavy
 > > enough to be non-trivial for testing.
 > >
 > > A picture of the schema can be found at:
 > > https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true
 > >
 > > Regards
 > > Arun - Siara Logics (cc)
 > >
 > >
 > > ___
 > > 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] Sample Employee database ported to SQLite from MySQL

[Chris Locke]

The scheme (for me) is like nails on a chalkboard.  'dept_no' but defined
as a 'CHAR', then 'emp_no' as an INT.
Fields with '_no' are read as 'number' and so should be a number.  OK, that
doesn't always work for 'telephone_no' (they usually start with a 0 ...
well, they do in the UK where I am...)
But I digress..


Chris


On Wed, Dec 19, 2018 at 9:05 AM Arun - Siara Logics (cc) 
wrote:

> This project (https://github.com/siara-cc/employee_db) hosts the Sqlite3
> db file ported from mysql test_db found at
> https://github.com/datacharmer/test_db. It can be used to test your
> applications and database servers. To use this project, download
> employees.db.bz2, unzip and open using sqlite3 command line tool.
>
> The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens
> Corporate Research. The data is in XML format.
> http://timecenter.cs.aau.dk/software.htm
>
> Giuseppe Maxia made the relational schema and Patrick Crews exported the
> data in relational format.
>
> The database contains about 300,000 employee records with 2.8 million
> salary entries. The export data is 167 MB, which is not huge, but heavy
> enough to be non-trivial for testing.
>
> A picture of the schema can be found at:
> https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true
>
> Regards
> Arun - Siara Logics (cc)
>
>
> ___
> 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] Claimed vulnerability in SQLite: Info or Intox?

[Dominique Devienne]

On Wed, Dec 19, 2018 at 11:14 AM Richard Hipp  wrote:

> > Could there be a way to make shadow tables off-limit to arbitrary SQL?
>
> That is one of the things that the new SQLITE_DBCONFIG_DEFENSIVE
> option does - it makes shadow tables read-only so that they cannot be
> corrupted by SQL.
>

May I please know how SQLite knows a table is a shadow table?
This is not something I've run across yet. Good to know BTW, of course.
Thanks.

Not knowing how this is done/known, I wonder whether this applies to other
"bits" of information about tables, notably "custom" ones from a given
application.
(answers seems to be no, given what's below).

Ah, I googled SQLITE_DBCONFIG_DEFENSIVE and found the 3.26 release notes,
which pointed me to [1] and then [2]. I had missed [2]. Sending anyway,
might be
useful to someone else too.

[1]
https://www.sqlite.org/draft/c3ref/c_dbconfig_defensive.html#sqlitedbconfigdefensive
[2] https://www.sqlite.org/draft/vtab.html#xshadowname


> However, it is off by default, since some application make use of the
> ability to write directly into shadow tables.  For example, when you
> restore a database using the output of the ".dump" command, that
> requires writing directly into shadow tables.
>

Good point. Didn't think of that. Thanks again, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Richard Hipp]

>
> Could there be a way to make shadow tables off-limit to arbitrary SQL?

That is one of the things that the new SQLITE_DBCONFIG_DEFENSIVE
option does - it makes shadow tables read-only so that they cannot be
corrupted by SQL.

However, it is off by default, since some application make use of the
ability to write directly into shadow tables.  For example, when you
restore a database using the output of the ".dump" command, that
requires writing directly into shadow tables.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Dominique Devienne]

On Tue, Dec 18, 2018 at 11:13 PM Richard Hipp  wrote:

> On 12/18/18, Dominique Devienne  wrote:
> > https://blade.tencent.com/magellan/index_en.html
> >
> > Sounds to me it's more related to a "remote callable" program like
> Chrome,
> > than SQLite proper, but I'd like an official stance on SQLite itself
> please.
>
> There was a bug in FTS3 (not in the SQLite core) that allowed an SQL
> Injection to escalate into a Remote Code Execution (RCE).


Thanks. Good to know. Understanding how it happened is as important
as knowing this is fixed in SQLite already (in due time of course, when it's
"safe" to share information). Much appreciated.


> By making malicious changes to the shadow tables that FTS3 uses and then
> running
> FTS3 queries that used those tables, an integer overflow could cause a
> buffer overrun, which if carefully managed might lead to an RCE.

[... more details]

We are hard at work on additional defense-in-depth measures now.
>

Could there be a way to make shadow tables off-limit to arbitrary SQL?
By explicitly taking them as such, and creating them with a "secret"
associated
to them, so that preparing a statement against them also needs that
"secret"?
The goal being to restrict access to those tables to only the
vtable-related code
that is the "front-end" to those shadow tables. Only that code should have
direct
access to those tables, at least on the write-side of them.

I understand that they are persistent, so what would be relatively "easy"
to do
at runtime for the connection, becomes harder w/o changing the file-format
or
introducing new sqlite_... reserved tables older versions of SQLite would
ignore.

I hope you can think of ways to persist more meta-data about tables in
general,
be it that FKs should be enforced by default on them when new connections
are
created by them, or to persist the fact a table is a shadow tables
associated to a
given virtual-table module, such that only that module (somehow) can write
them.

My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sample Employee database ported to SQLite from MySQL

[Arun - Siara Logics (cc)]

This project (https://github.com/siara-cc/employee_db) hosts the Sqlite3 db 
file ported from mysql test_db found at https://github.com/datacharmer/test_db. 
It can be used to test your applications and database servers. To use this 
project, download employees.db.bz2, unzip and open using sqlite3 command line 
tool. 
  
The original data was created by Fusheng Wang and Carlo Zaniolo at Siemens 
Corporate Research. The data is in XML format. 
http://timecenter.cs.aau.dk/software.htm 
 
Giuseppe Maxia made the relational schema and Patrick Crews exported the data 
in relational format. 
 
The database contains about 300,000 employee records with 2.8 million salary 
entries. The export data is 167 MB, which is not huge, but heavy enough to be 
non-trivial for testing. 
 
A picture of the schema can be found at: 
https://github.com/siara-cc/employee_db/blob/master/employees-schema.png?raw=true

Regards
Arun - Siara Logics (cc)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users