Re: [sqlite] creating a table
Thank you! John On 11/22/2016 01:21 PM, R Smith wrote: On 2016/11/22 6:00 PM, John R. Sowden wrote: That was a throw back to years ago. I was trying to protect against y2k by making each dbf for 1 calendar year. Also, these files are about 800k in size, so I was worried about storage and search time. Storage is not an issue anymore. I will know about search time after learning about sql databases. Sometimes over the last 45 years I have changed the dbf structure. This way I only change starting at the year in question, then change the program to work with the structure modification. SQLite (and indeed all modern SQL engines) can have tables many gigabytes (and even terabytes) in size with extremely fast lookups via simple indexing. I suggest you only create one file with one table and simply add a field to distinguish which year you are logging for (considering the size you've mentioned, this would be child's play for any DB engine - plus very fast). It is not uncommon to find people here with terabyte multi-volume DBs with billions of rows of data (literally) - so any division of data sets is usually superfluous and should be reserved for the extreme case. We always encourage experimentation with your specific data and hardware. Speed should be real fast, and speed differences between a table with 1 year's data and 10 years worth of data should be negligible (considering your current year dataset totals 800k). Further, the advantages of having your data in a single table (or at a minimum, a single DB) means you can query relationally between different years and get multi-year statistics without resorting to expensive joins and/or attaching external DBs. Good luck! Ryan ___ 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] creating a table
On 2016/11/22 6:00 PM, John R. Sowden wrote: That was a throw back to years ago. I was trying to protect against y2k by making each dbf for 1 calendar year. Also, these files are about 800k in size, so I was worried about storage and search time. Storage is not an issue anymore. I will know about search time after learning about sql databases. Sometimes over the last 45 years I have changed the dbf structure. This way I only change starting at the year in question, then change the program to work with the structure modification. SQLite (and indeed all modern SQL engines) can have tables many gigabytes (and even terabytes) in size with extremely fast lookups via simple indexing. I suggest you only create one file with one table and simply add a field to distinguish which year you are logging for (considering the size you've mentioned, this would be child's play for any DB engine - plus very fast). It is not uncommon to find people here with terabyte multi-volume DBs with billions of rows of data (literally) - so any division of data sets is usually superfluous and should be reserved for the extreme case. We always encourage experimentation with your specific data and hardware. Speed should be real fast, and speed differences between a table with 1 year's data and 10 years worth of data should be negligible (considering your current year dataset totals 800k). Further, the advantages of having your data in a single table (or at a minimum, a single DB) means you can query relationally between different years and get multi-year statistics without resorting to expensive joins and/or attaching external DBs. Good luck! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
On 22 Nov 2016, at 5:03pm, John R. Sowden wrote: > Thank you, but I am currently in the early learning phase of sql databases > and the sql language. I am starting with Sqlite due to its relative > simplicity, and moving on to H2 to integrate it into Libre Office. It's > commands like analyze that I have never heard of and need to become familiar > with. In the mean time I am reading the Sqlite mail list, understanding > about 40% of it. There seems to be a lot of sql tutorials on the web. > W3schools seems to be popular, so I am trying it. W3Schools is an excellent resource for learning stuff and trying to remember how to do some little thing. At this stage in your learning remember each time you encounter information to figure out if this is SQL -- which is likely to work in all SQL engines -- or SQLite-only. Each SQL engine has its own weird things which work only in that one version of SQL. For instance the "SELECT" command is for all implementations of SQL, but the "ANALYZE" command works only in SQLite, and the "ALTER INDEX" command works only in SQL SERVER. You can learn lots about how to use SQLite from a web site intended for some other implementation of SQL. But you may also learn a couple of things that just don't work in SQLite. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
Thank you, but I am currently in the early learning phase of sql databases and the sql language. I am starting with Sqlite due to its relative simplicity, and moving on to H2 to integrate it into Libre Office. It's commands like analyze that I have never heard of and need to become familiar with. In the mean time I am reading the Sqlite mail list, understanding about 40% of it. There seems to be a lot of sql tutorials on the web. W3schools seems to be popular, so I am trying it. John On 11/22/2016 08:30 AM, Igor Korot wrote: John, On Tue, Nov 22, 2016 at 11:00 AM, John R. Sowden wrote: That was a throw back to years ago. I was trying to protect against y2k by making each dbf for 1 calendar year. Also, these files are about 800k in size, so I was worried about storage and search time. Storage is not an issue anymore. I will know about search time after learning about sql databases. Sometimes over the last 45 years I have changed the dbf structure. This way I only change starting at the year in question, then change the program to work with the structure modification. Speed is simple: Just create a table and then create an index as appropriate. Shows us what query you want to run and we will help with the index creation. Then you will need to run ANALYZE against the query and EXPLAIN QUERY PLAN. Simple. ;-) Thank you. John On 11/22/2016 12:33 AM, Christoph P.U. Kukulies wrote: Just a thought about your TABLE named "log16": I'm not at all a database expert but from the idea what a table is, I would take a more general approach. CREATE a TABLE log with columns year, logtext, timestamp a la: CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY) Just my 2cts. Christoph Am 21.11.2016 um 19:08 schrieb John R. Sowden: Thank you all for your answers and direction for further information. Hopefully, I will not bring these subjects up again. :) John On 11/21/2016 09:29 AM, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. Neither one allows me to set the length of the text fields in the table creation process. How does the Sqlite know how long each record should be, same with integers. No help found in the documentation on the Sqlite web site. John ___ 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] creating a table
John, On Tue, Nov 22, 2016 at 11:00 AM, John R. Sowden wrote: > That was a throw back to years ago. I was trying to protect against y2k by > making each dbf for 1 calendar year. Also, these files are about 800k in > size, so I was worried about storage and search time. Storage is not an > issue anymore. I will know about search time after learning about sql > databases. Sometimes over the last 45 years I have changed the dbf > structure. This way I only change starting at the year in question, then > change the program to work with the structure modification. Speed is simple: Just create a table and then create an index as appropriate. Shows us what query you want to run and we will help with the index creation. Then you will need to run ANALYZE against the query and EXPLAIN QUERY PLAN. Simple. ;-) Thank you. > > John > > > > > On 11/22/2016 12:33 AM, Christoph P.U. Kukulies wrote: >> >> Just a thought about your TABLE named "log16": >> >> I'm not at all a database expert but from the idea what a table is, I >> would take a more general approach. CREATE a TABLE log with >> columns year, logtext, timestamp a la: >> >> CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY) >> >> Just my 2cts. >> >> Christoph >> >> >> Am 21.11.2016 um 19:08 schrieb John R. Sowden: >>> >>> Thank you all for your answers and direction for further information. >>> Hopefully, I will not bring these subjects up again. :) >>> >>> John >>> >>> >>> On 11/21/2016 09:29 AM, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. Neither one allows me to set the length of the text fields in the table creation process. How does the Sqlite know how long each record should be, same with integers. No help found in the documentation on the Sqlite web site. John >> >> ___ >> 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] creating a table
That was a throw back to years ago. I was trying to protect against y2k by making each dbf for 1 calendar year. Also, these files are about 800k in size, so I was worried about storage and search time. Storage is not an issue anymore. I will know about search time after learning about sql databases. Sometimes over the last 45 years I have changed the dbf structure. This way I only change starting at the year in question, then change the program to work with the structure modification. John On 11/22/2016 12:33 AM, Christoph P.U. Kukulies wrote: Just a thought about your TABLE named "log16": I'm not at all a database expert but from the idea what a table is, I would take a more general approach. CREATE a TABLE log with columns year, logtext, timestamp a la: CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY) Just my 2cts. Christoph Am 21.11.2016 um 19:08 schrieb John R. Sowden: Thank you all for your answers and direction for further information. Hopefully, I will not bring these subjects up again. :) John On 11/21/2016 09:29 AM, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. Neither one allows me to set the length of the text fields in the table creation process. How does the Sqlite know how long each record should be, same with integers. No help found in the documentation on the Sqlite web site. John ___ 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] creating a table
Just a thought about your TABLE named "log16": I'm not at all a database expert but from the idea what a table is, I would take a more general approach. CREATE a TABLE log with columns year, logtext, timestamp a la: CREATE TABLE LOG (logtext TEXT, timestamp TEXT, year INTEGER PRIMARY KEY) Just my 2cts. Christoph Am 21.11.2016 um 19:08 schrieb John R. Sowden: Thank you all for your answers and direction for further information. Hopefully, I will not bring these subjects up again. :) John On 11/21/2016 09:29 AM, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. Neither one allows me to set the length of the text fields in the table creation process. How does the Sqlite know how long each record should be, same with integers. No help found in the documentation on the Sqlite web site. John ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
On 21 Nov 2016, at 21:55, Igor Korot wrote: > You are of course correct. It does depend on an application. > However, I tried to explain the SQLite and its paradigm in terms of > the dBase/FoxPro. You were correct also, Igor, and gave good advice. Best regards, Niall ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
Hi, Niall, On Mon, Nov 21, 2016 at 12:52 PM, Niall O'Reilly wrote: > On 21 Nov 2016, at 17:29, John R. Sowden wrote: > >> First of all, I come from the dBASE/Foxpro world. There is no distinction >> between a table and a database. I understand that with Sqlite a database >> includes tables and other items. The scenario that I do not understand, is: >> say I have a log file with about 7 fields totaling about 80 characters per >> record. How do I name the database and table. Currently I say log16 for >> the year 2016. > > > There's no one true way to do this. > > I've read Igor Korot's reply, and what he suggests may well be what you > need. You are of course correct. It does depend on an application. However, I tried to explain the SQLite and its paradigm in terms of the dBase/FoxPro. Thank you. > > In a previous job, I had an application where it made sense to use a > different > database file for each time period, and always to call the table > 'LOGENTRY'. > This was because I had to deal with hundreds of thousands of records a > day, > and seldom had queries whose scope was broader than a single calendar day. > > I hope this helps a little. > > Best regards, > Niall O'Reilly > > ___ > 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] creating a table
> On Nov 21, 2016, at 10:08 AM, John R. Sowden > wrote: > > Thank you all for your answers and direction for further information. > Hopefully, I will not bring these subjects up again. :) Some of what you’re asking applies to any SQL database. The SQLite docs do include a reference to its SQL syntax (since no two databases speak exactly the same dialect of SQL) but they're not intended to teach you how to use SQL, or relational databases. So if you’re coming from a very different type of database like dBase, there’s a lot of basic stuff to [re]learn that the SQLite website sort of assumes you already know! I suggest looking for a book or a tutorial website to learn the basics of relational databases and SQL. If creating tables was a roadblock, I guarantee you’re going to be perplexed by things like joins. I learned SQLite ten+ years ago via an O’Reilly book, which might still be in print. I’m sure there are others. If you use a general relational-database book, be aware that the main difference between SQLite and other SQL databases is that it largely ignores column types and widths: you can put any type of value into any column (sort of like how in Python or JavaScript you can put any type of value into any variable), and you don’t need to specify a column width because SQLite can store any length of string into a column (again, like strings in most modern languages.) Oh, also, if you don’t already know about it, the `sqlite3` command-line tool is invaluable for learning to use SQLite, since it lets you try out commands and queries interactively. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
Thank you all for your answers and direction for further information. Hopefully, I will not bring these subjects up again. :) John On 11/21/2016 09:29 AM, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. Neither one allows me to set the length of the text fields in the table creation process. How does the Sqlite know how long each record should be, same with integers. No help found in the documentation on the Sqlite web site. John ___ 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] creating a table
On 21 Nov 2016, at 17:29, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. There's no one true way to do this. I've read Igor Korot's reply, and what he suggests may well be what you need. In a previous job, I had an application where it made sense to use a different database file for each time period, and always to call the table 'LOGENTRY'. This was because I had to deal with hundreds of thousands of records a day, and seldom had queries whose scope was broader than a single calendar day. I hope this helps a little. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
Hi, John, On Mon, Nov 21, 2016 at 12:29 PM, John R. Sowden wrote: > First of all, I come from the dBASE/Foxpro world. There is no distinction > between a table and a database. I understand that with Sqlite a database > includes tables and other items. The scenario that I do not understand, is: > say I have a log file with about 7 fields totaling about 80 characters per > record. How do I name the database and table. Currently I say log16 for > the year 2016. If I may: When you reference the SQLite DB you have a one big database file which contains all the information: tables, indexes, data, etc. If you look at it from the dBase/FoxPro POV, the database is the directory where all you tables are. However, it is just a 1 file. A table in the SQLite DB is what it is - a table. You can call the DB "logs.db" and inside create a table with the name "log16". Then 1January 2017 will come and you will create a table in that DB called "log17". > > Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. > Neither one allows me to set the length of the text fields in the table > creation process. How does the Sqlite know how long each record should be, > same with integers. SQLite doesn't use that standard. Try googling for "SQLite create table" and "SQLite affinity". Hopefully then everything will be clearer. Thank you. > > No help found in the documentation on the Sqlite web site. > > John > > > > ___ > 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] creating a table
John, There is a lot of documentation on the SQLite website. Here's the 'official' docs on creating a table https://www.sqlite.org/lang_createtable.html A Sqlite database consists of many tables. I am unsure if there is an upper limit, if there is, its more tables than I have ever created. You probably need to read up a bit more on SQL in general and SQLite in particular. Its a great SQL database for many uses, though not for every use. Here's a summary https://sqlite.org/whentouse. SQLite does not set limits on text fields. Thats a great strength (some people may disagree), see here https://www.sqlite.org/datatype3.html You can create a table and specify the text length but its there for compatibility and is ignored. You want to drop 20, 200 or 2000 chars in your field, go ahead and do it. SQLite is very flexible and very fast, there's a ton of help and docs out there, the support is direct from the people who write it. Rob On 21 Nov 2016, at 17:29, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. Secondly, I have 2 "front ends" for Sqlite on my Ubuntu 16.04 computer. Neither one allows me to set the length of the text fields in the table creation process. How does the Sqlite know how long each record should be, same with integers. No help found in the documentation on the Sqlite web site. John ___ 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] creating a table with an index
Hi Sam, > I am trying to create a table with two indexes: > > CREATE TABLE favorites ( > cust_id CHAR(32) NOT NULL, > fldoid CHAR(38) NOT NULL, > imgoid CHAR(64) NOT NULL, > PRIMARY KEY (cust_id), > INDEX (fldoid, imgoid)); > > SQLite keeps complaining saying there is an error around INDEX. > What might I be doing wrong? Create the index separately, eg: create index "favorites fldoid imgoid" (fldoid, imgoid); Note that this won't create "two indexes". It creates an index sorted by fldoid and subsorted by imgoid, which is what you want if you'll be searching by fldoid and imgoid combinations. If you instead want two individual indexes, for searching on just one column at a time, then you need to create two, as: create index "favorites fldoid" (fldoid); create index "favorites imgoid" (imgoid); Tom BareFeet -- Naked ADSL2 now in Australia, at the best pricing: http://www.tandb.com.au/broadband/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table with an index
"Sam Carleton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am trying to create a table with two indexes: > > CREATE TABLE favorites ( >cust_id CHAR(32) NOT NULL, >fldoid CHAR(38) NOT NULL, >imgoid CHAR(64) NOT NULL, >PRIMARY KEY (cust_id), >INDEX (fldoid, imgoid)); > > SQLite keeps complaining saying there is an error around INDEX. What > might I be doing wrong? Here's the syntax of CREATE TABLE statement: http://sqlite.org/lang_createtable.html INDEX is not a valid keyword in such a statement. Did you perhaps mean UNIQUE? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users